MySql 知识点之事务、索引、锁原理与用法解析

2023-08-13 04:30:03 64

本文实例讲述了MySql知识点之事务、索引、锁原理与用法。分享给大家供大家参考，具体如下：

事务

事务就是一组原子性的SQL查询，或者说一个独立的工作单元。如果数据库引擎执行一组操作语句，那么久执行所有的操作，如果其中有任何一条崩溃或其他原因无法执行，所有语句将不会执行。也就是说事务内的语句，要么全部执行成功，要么全部执行失败。

索引是存储引擎用户快速找到记录的一种数据结构，举例

SELECTuserNameFROMuserWHEREuserId=1;

如果在userId列上加上索引，则MySQL将使用该索引找到userId的行，也就是说，MySQL先在索引上按值进行查找，然后返回所有包含该值的数据行。

索引方式
- B-Tree索引
使用B-Tree数据结构来存储数据，大多MySQL引擎都支持该索引。B-Tree索引可以加快访问数据的速度，因为B-Tree对索引列顺序组织存储，范围查找快。
- hash索引
哈希索引基本哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码值较小。哈希索引将所有的哈希码存储在索引中，同时在哈斯表中保存指向每个数据行的指针。MySQL中只有Memory引擎显示支持哈希索引。

在这里我们主要讨论下行级锁

引擎MyISAM，可以理解为锁整张表，可以同时读，不可以同时写。在锁定期间，其它进程无法对该表进行写操作，如果是写锁，则其它进程则不允许读。

引擎INNODB，单独一行记录加锁，可以同时读，不可同时写。行级锁开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

由于InnoDB预设是Row-LevelLock，所以只有[明确]的指定主键，MySQL才会执行Rowlock，否则MySQL将会执行TableLock

例1：（明确指定主键，并且有此记录，RowLock）

SELECT*FROMproductsWHEREid='3'FORUPDATE;
SELECT*FROMproductsWHEREid='3'andtype=1FORUPDATE;

例2:(明确指定主键，若查无此记录，无lock)

SELECT*FROMproductsWHEREid='-1'FORUPDATE;

例3:(无主键，tablelock)

SELECT*FROMproductsWHEREname='Mouse'FORUPDATE;

例4:(主键不明确，tablelock)

SELECT*FROMproductsWHEREid<>'3'FORUPDATE;

例5:(主键不明确，tablelock)

SELECT*FROMproductsWHEREidLIKE'3'FORUPDATE;

注1:FORUPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。
注2:要测试锁定的状况，可以利用MySQL的CommandMode，开二个视窗来做测试。

更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL索引操作技巧汇总》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》

希望本文所述对大家MySQL数据库计有所帮助。