MYSQL锁学习笔记

前言

MYSQL是在大小公司中使用率极高的开源的关系型数据库，以其良好的易用性和在分布式场景下的高性能而著称，也是所有新手在数据库入门时的产品首选。最近因为听了公司的一位师兄关于MYSQL InnoDB锁的讲座，收获很多，所以将MYSQL锁相关的必备知识在此进行梳理。这些知识不仅可以帮助面试，也可以在日常开发进行性能优化或死锁问题排查时派上用场。当然，最重要的是，在对数据进行上锁时，就能够梳理出相应的上锁流程，从而避免真正走到故障时再去排查。

本文主要包括

• MYSQL基础架构
• 语句执行顺序
• ACID原则
• 事务分类
• 事务隔离级别
• 行锁/表锁/意向锁

MYSQL基础架构

MYSQL主要分为客户端和服务端，其中客户端负责对服务端进行连接，服务端主要包含两个部分，其中存储引擎层（Storage Engines）决定数据在磁盘上具体的存储形式，典型的存储引擎包括InnoDb和MyISAM，而目前MYSQL甚至支持混合存储引擎，即可能一张表一半存储在InnoDb上，一半存储在MyISAM。

除此以外的其它服务端组建则不关心数据用什么形式存储，主要负责执行具体的SQL语句，

1. 链接池（Connections/Thread handling）组件负责管理客户端和服务端建立的所有连接，
2. 解析器（Parser）负责解析并校验SQL语句
3. 查询缓存（Query Cache）负责对执行过的SQL语句结果进行缓存，当发现有类似的查询请求命中缓存时，则会直接返回缓存中的查询结果。但是，因为缓存的维护存在一定的开销，比如数据更新时需要同时去更新缓存，因此有些线上环境的DB会将这个功能关闭
4. 优化器（Optimizer）负责对解析后的SQL语句进行优化，如缓存数据优化，执行计划优化。这个阶段还会对用户的权限进行校验
5. 元数据缓存（Table Metadata Cache）表单/DB等的元数据信息的缓存

这里简单比较一下InnoDB和MyISAM这两个存储引擎。
InnoDB的特性如下：

1. 支持事务及ACID
2. 提供行锁/表锁
3. MVCC能力

MyISAM的特性如下：

1. 非事务型引擎
2. 支持全文检索（目前最新的InnoDB也支持）
3. 只提供表锁

本文主要基于InnoDB对锁的特性进行介绍。

SQL语句执行顺序

一个查询请求在整个MYSQL服务端的链路如下：

1. 在链接池处创建链接
2. 前往查询缓存（若开启）判断是否有相似的SQL的查询结果可以直接命中
3. 通过解析器对SQL语句进行解析和校验，并为SQL生成sql_id
4. 优化器对SQL语句进行优化，生成执行计划
5. 前往存储引擎执行并获取数据

那么SQL语句在经过解析器和优化器时是什么样的一个链路呢？
一个标准的Select SQL语句包含以下几个部分：

select t1.column1 as column1, t2,column2 as column2... 
from TABLE t1, TABLE t2 ... 
WHERE condition1 
GROUP BY condition2 
HAVING condition3 
ORDER BY column1
LIMIT N

而这条语句的标准逻辑执行顺序如下：

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. HAVING
7. SELECT
8. DISTINCT
9. ORDER BY

这里有一点需要注意，select语句是在group by和having之后执行，因此select中as出来的列名在group by和having中是不可以引用的，但是order by中是可以引用的。

但是真正的的执行顺序和标准逻辑执行顺序并不一定相同，因为优化器会对SQL的执行顺序进行变更，从而尽可能提高SQL的执行效率。比如：

select * from table1 t1 join table2 t2 on t1.id = t2.id where t1.count > 10 and t2.count > 100

标准的执行顺序会先将表格t1和t2进行join操作，再对join后的结果针对where语句进行筛选。而优化器可能会变化一下执行顺序，先根据where t1.count > 10 and t2.count > 100筛选出t1表和t2表中符合条件的数据，再执行join。

那么有没有办法看到SQL在真实执行的时候的执行计划呢？这就需要Explain语法。

Explain

Explain关键字的使用方法很简单，只要将其加在具体的SELECT语句之前就可以，Explain也只能解析SELECT语句。通过Explain关键字可以观察表的索引是否合理，语句的真实执行顺序是否符合预期。Explain执行后生成的数据如下：

列名	含义
id	SELECT语句的SQL_ID，它是指这个语句在查询中的第n条语句，如果两个id相同，则代表按照顺序执行从上到下执行，id值越大，优先级越高，越先被执行
select_type	SELECT语句类型, 如SIMPLE是指不使用UNION或子查询
table	输出行所属的表格，derivex是指从第x步生成的衍生表
type	访问类型，说明表是如何关联的
possible keys	可选择的索引
key	真正选择的索引。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
key_len	选中索引的长度，显示的是索引字段的最大可能长度，是根据表定义得来，而非表内检索
ref	哪些列或常量被用来查找索引列上的值
rows	预估需要扫描的行数
filtered	预计多少比例的行数会被过滤出来

其中访问类型（type）按照从好到坏包括

• system：只有一行
• const：表格中最多只有一行匹配的数据，如使用主键进行查询 如select * from user_info where id = 2
• eq_ref: 使用唯一索引，对于每个索引键值只有一条记录匹配，如使用primary key或者unique key作为多表链接的关联条件，即前表的每一个结果，在后表都只能找到一条匹配的记录，只支持等号查询。 SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id
• ref: 针对非唯一或非主键索引，或是使用了最左前缀规则索引的查询，支持非等号查询。如 SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id AND order_info.user_id = 5
• fulltext: 全文检索
• ref_or_null: 除了利用索引之外，MYSQL执行了额外的查询来处理NULL值
• index_merge: 对索引进行多段索引扫描，并且将结果进行合并
• unique_subquery: 适用于IN语句，且IN中查询出得数据唯一 如value IN (SELECT primary_key FROM single_table WHERE some_expr)
• index_subquery: 同上，只是IN中查询数据不唯一
• range：获取特定范围内的数据，使用索引来决定哪些是这个范围内的数据。所有的等值，非等值处理，判空等都可以使用range类型
• index：类似于ALL，只不过全扫描的是索引树。如果只需要扫描索引树，无需访问具体表，则会在Extra列展示Using index。如果查询中使用的索引是某个大索引的其中一部分时，也会使用这种检索类型
• ALL：全表扫描

在知道这些之后，使用Explain分析语句时可以按照如下思路进行分析：

1. 查看possible_keys和keys列，判断是否充分利用了主键/唯一键/索引
2. 查看key_len，判断关键字长度是否过长
3. 接着查看ref列，判断是否能够往const优化
4. 去除type=ALL的全表扫描连接

这里建议看一下参考文章中的Explain实战例子文章来加深通过Explain进行优化的思路

事务特性ACID

ATOMICY原子性：事务要么全部执行，要么全部不执行
CONSISTENCY一致性：事务执行前和执行后数据状态应当一致
ISOLATION隔离性：事务之间不会相互影响
DURABILITY持久性：事务执行完成后结果不会丢失，因此需要能够对数据进行恢复

事务分类

隐式事务：在autocommit为true的情况下，默认每一条语句都会开启一个事务执行，执行完毕后提交事务。因此不在事务上下文中执行select * from user where id = 1 for update语句在语句执行完后就会释放排他锁，这在大多数情况下都是不合理的。
显式事务：每个事务以start transaction开启，以commit或rollback结束。Spring中使用@Transactional或是transactionTemplate包围的代码段

事务隔离级别

事务总共有4个隔离级别：

1. 读未提交，会出现脏读，不可重复度，幻读，
2. 读已提交，会出现不可重复读，幻读
3. 可重复度，InnoDB通过MVCC解决了幻读问题，MVCC全称Multiple Version Concurrency Control，其核心为一个在t0时刻开启的事务只能读到t0时刻以及之前的提交的数据状态
4. 序列化

脏读：一个事务中未提交的语句会被另一个事务察觉
不可重复读：一个事务中提交的update语句会被另一个事务察觉
幻读：一个事务中提交的insert语句会被另一个事务察觉

锁

锁主要分为表锁和行锁。顾名思义，表锁就是指对整张表进行上锁，而行锁则是指针对一行数据进行上锁。表锁通常在服务器层面实现，而行锁往往在存储引擎层实现。行锁并不是只对数据行上锁，还可以对索引/索引区间进行上锁，即强调的是粒度更小的锁。

锁的类型

锁可以分为以下四类：

1. 共享锁（S）可重复获取共享锁，但是不能获取排他锁（select ... lock in share mode)
2. 排他锁（X）不能获取数据行的任何锁 （select ... for update, update, delete)
3. 意向锁（IS/IX）表级别锁，当获得该表/行的共享/排他锁时，会对该表加上意向共享/排他锁。这样别的表级别锁来试图锁表时，可以直接通过意向锁来判断该表中是否存在共享/排他锁，而无需对表中的每一行判断是否有行级锁，降低封锁成本，提高并发性能

意向锁和意向锁之间是兼容的，而意向锁和行锁之间也是兼容。意向锁主要是对表锁的优化。假如现在有一个事务需要对表a加排他锁，如果没有意向锁，就需要对全表进行扫描，直到找到第一个共享/排他锁。而通过判断是否有意向锁，可以极大的提高锁互斥判断的性能。加意向锁是在所有锁（行锁/表锁）之前进行判断和执行的。

行锁

行锁具体有三种实现：

1. record lock 记录锁：锁定索引记录本身
2. gap lock：在索引记录的间隙加锁，锁定范围，不包括记录本身
3. next key：record lock + gap lock

只在可重复度REPEATABLE READ或以上的隔离级别下的特定操作才会取得gap lock或nextkey lock。
读已提交REPEATABLE COMMIT级别下只有record lock
MYSQL默认为RR

因此当判断语句如何加行锁时，需要根据事务隔离级别+是否使用主键/唯一键/索引进行判断。

加锁顺序本质上和索引的查询顺序是一致的
这里有一种最糟糕的情况，即如果where条件中的字段不是主键/索引/唯一索引，则会先对全部索引上排他锁，在找到符合条件的记录后，解锁不满足条件的锁。

参考文献

MYSQL架构
Explain关键字
MYSQL性能优化神器Explain
Explain实战例子
详解 MySql InnoDB 中意向锁的作用
幻读