MYSQL锁学习笔记

眯眯眼的猫头鹰

发布于 2020-05-11 17:32:59

8200

发布于 2020-05-11 17:32:59

前言

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

本文主要包括

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

MYSQL基础架构

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

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

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

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

支持事务及ACID
提供行锁/表锁
MVCC能力

MyISAM的特性如下：

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

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

SQL语句执行顺序

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

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

那么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

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

FROM
ON
JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
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分析语句时可以按照如下思路进行分析：

查看possible_keys和keys列，判断是否充分利用了主键/唯一键/索引
查看key_len，判断关键字长度是否过长
接着查看ref列，判断是否能够往const优化
去除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个隔离级别：

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

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

锁

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

锁的类型

锁可以分为以下四类：

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

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