mysql 什么时候加锁呢

前言

相信大家对mysql 语法有个简单的运用了，知道了事务特性ACID，知道事务隔离级别，知道MVVC，知道了索引结构。但是知道了这些应该还是不知道为什么查询这么慢，为什么产生了死锁，为什么索引没有起作用吧。

介绍锁

关于锁，其实有好多，不同存储引擎支持的锁还不一样。

MyISAM：

注：不支持事务操作、不支持外键

1）表级别：

MySQL表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。什么意思呢，就是说对MyISAM表进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写操作；而对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作。

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

InnoDB：

1）实现了以下两种类型的行锁：

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
• 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

2）为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

锁模式的兼容情况：

3)  行锁

InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！

4）间隙锁（gap lock）

• 当我们用范围条件而不是相等条件时，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；即范围锁
• 当我们插入一条记录时，会锁住当前插入索引节点的左右索引节点所围起来的开区间，如果左边或者右边没有索引节点，mysql 就会从自己定义的最大或者最小的值进行锁住。

5）Next-Key

由行锁+间隙锁组成的锁成为 Next-Key 锁，可理解为闭区间锁。

 

锁的使用场景

1. 显示加锁

SELECT ... FOR UPDATE   手动加排它锁

SELECT ... LOCK IN SHARE MODE   手动加共享锁

2. 隐示加锁

隐示加锁，自动加锁，不需要手动指定。根据事务隔离级别不同，而展现的不同。

读未提交、提交读、串行：只有行锁。

可重复读：有行锁、间隙锁、Next-Key 锁，可重复读也就是通过间隙锁、Next-Key 锁来防止幻读的。

 

解读SQL 的执行情况

我们理解了锁的原理还是不足够来处理分析SQL执行慢的情况，这就要需要我详情了解SQL执行情况，走的是什么索引，复杂SQL时走的是什么索引，条件长度不同时走的是什么样的索引，大量数据的时候走的是什么索引。

常用的应该是通过 explain 命令来看SQL的执行情况

我们来解读下重要的字段

1）type ：这个字段是我们优化要重点关注的字段，这个字段直接反映我们SQL的性能是否高效。性能由好到差依次为：system>const>eq_ref>ref>range>index>all。

2）possible_keys：显示可能应用在这张表中的索引，但不一定被查询实际使用。

3）key：实际使用的索引。

4）key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。一般来说，索引长度越长表示精度越高，效率偏低；长度越短，效率高，但精度就偏低。并不是真正使用索引的长度，是个预估值。

5）rows：大致找到所需记录需要读取的行数。

6）filter：表示选取的行和读取的行的百分比，100表示选取了100%，80表示读取了80%。

7）extra：一些重要的额外信息，用于查看排序分组很有用。

• Using filesort：使用外部的索引排序，而不是按照表内的索引顺序进行读取。（一般需要优化）
• Using temporary：使用了临时表保存中间结果。常见于排序order by和分组查询group by（最好优化）
• Using index：表示select语句中使用了覆盖索引，直接冲索引中取值，而不需要回行（从磁盘中取数据）
• Using where：使用了where过滤
• Using index condition：5.6之后新增的，表示查询的列有非索引的列，先判断索引的条件，以减少磁盘的IO
• Using join buffer：使用了连接缓存
• impossible where：where子句的值总是false
  还有一些，基本上很少遇到，就不作说明了。

 

参考：

https://zhuanlan.zhihu.com/p/29150809

https://www.jianshu.com/p/be1c86303c80