MySQL-执行计划与锁

一、执行计划介绍

MySQL逻辑分层

 

简单来说, 在一条查询语句被执行前, 会经过MySQL 服务层的SQL优化器, SQL优化器会优化我们的SQL, 然后产生了执行计划.

该执行计划即为引擎层会采取的读取数据的策略. 包括但不限于 使用哪个索引、索引长度、索引类型、SQL执行顺序、是否使用覆盖索引/回表查询/索引条件下推.

 

二、SQL逻辑执行过程 与 思考

SELECT DISTINCT
< select_list >
FROM
< left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
< where_condition >
GROUP BY
< group_by_list >
HAVING
< having_condition >
ORDER BY
< order_by_condition >
LIMIT < limit_number >

 

对于上面的SQL的逻辑执行过程如下

 

FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number> 

 

注意区别SQL的物理执行过程.

 

1、FROM: 首先left_table 与 right_table做笛卡尔积, 产生虚拟表V1-J1.

2、ON: left_table 与 right_table根据连接条件进行过滤, 产生虚拟表V1-J2.

3、JOIN: 若是左连接则,  则left_table的不符合条件的数据也添加进来, 产生虚拟表V1-J3. 右连接同理. 如果是内连接则无需产生变更, 上一步on时已经将两表过滤了. 全连接则将两表不符合on过滤条件的数据都添加进来. 

4、WHERE: 根据where后的条件和虚拟表V1, 一步步过滤不符合的数据, 产生临时表V2.

5、GROUP BY: 将V2表进行分组聚合, 产生临时表V3, 此步骤改变了表的引用, 后续步骤能操作的字段变少, 大都需要使用聚合函数.

6、HAVING: 对分组后的V3表进行过滤, 满足HAVING条件的数据产生临时表V4.

7、SELECT DISTINCT: 查询需要的字段, 生成V5表

8、ORDER BY: 根据指定字段进行排序, 生成V6表

9、LIMIT: 从V6表指定位置选出指定条记录.

 

 

我们知道命中索引的SQL在查询时会先从索引树中查询对应的索引, 然后再回到主表来查询.

 

思考:

1、SELECT后才是ORDER BY, 如果SELECT与ORDER BY的字段一致且是索引字段, 就可以直接在索引表中进行查找而不排序.

2、GROUP BY改变了表的引用, 且很大概率会产生一张新表(extra: using temporary), 如果GROUP BY与前面的where查询的索引字段相同, 则不需要临时表.

在两表关联查询时就产生了意义: call_instance 1 : n 于 test1_index

eg: EXPLAIN SELECT a.* FROM `test1` a JOIN `test1_index` b on a.`test_id` = b.`test_id`

where a.`test_id` = 45842 and b.`word_name` in ('你好', '慢') GROUP BY a.`test_id`

 

3、GROUP BY后对表的引用产生了变更, 后续HAVING、SELECT、ORDER BY的字段必须是聚合函数下生成的字段 或者 group by的字段. 当然某些步骤也可以直接取表字段, 但不严谨.

4、根据这个执行过程, 在优化SQL时在越前面过滤越多数据, SQL执行性能会越好.

 

三、执行计划解析

EXPLAIN SELECT a.* FROM `msg` a JOIN `msg` b on a.`id` = b.`id` where b.`send_time` > '2020-07-01'

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	b		range	PRIMARY,idx_send_time	idx_send_time	5		17860096	100	Using where; Using index
1	SIMPLE	a		eq_ref	PRIMARY	PRIMARY	8	test.b.id	1	100

 

id: 执行id

越大的先越执行, 如果id值相同, 则从上往下执行.

 

select_type: 查询类型, 主要有如下四类

PRIMARY: 包含子查询SQL中的主查询(最外层).
SUBQUERY: 包含子查询SQL中的子查询(非最外层).
SIMPLE: 简单查询 (不包含子查询、union).
DERIVED: 衍生查询 (使用了临时表, 1: 在from子查询中只有一张表 2: 在from子查询中, 如果有table1 union table2, 那么table1就是derived) 

 

 

type: 索引类型

索引类型比较多, 下面列举几个常见的类型
system > const > eq_ref > ref > range > index > all
其中越大的性能越好

system: 系统表, 只有一条数据的表, 基本开发时碰不到.

 

const:  主键索引和唯一索引的查询, 查询条件只匹配一行.

eg: EXPLAIN SELECT * FROM `test` where number = 'wxid_2tt85umaugqf22'

 

eq_ref: 唯一性索引的查询, 对于每个索引键的查询, 返回匹配唯一行数据 (有且只有一个, 不能0也不能多), 常见于唯一索引和主键索引在两表关联时查询的索引键能匹配另一张表的所有数据.

eg: EXPLAIN SELECT * FROM `msg` where id in (SELECT `id` FROM `msg` where `send_time` > '2020-07-01')

eg: EXPLAIN SELECT a.* FROM `msg` a JOIN `msg` b on a.`id` = b.`id` where b.`send_time` > '2020-07-01'

ref: 非唯一性索引的查询, 对于每个索引键的查询, 返回匹配的所有行(0, 多)

eg: EXPLAIN SELECT * FROM `msg` where `company_id` = 1

range: 非唯一性索引的查询, 检索指定范围的行, where后面是一个范围查询(between, >, < >=,..   特殊: in有时候会失效, 会走ALL)  

eg: EXPLAIN SELECT * FROM `msg` where `send_time` > '2020-07-01'

index: 查询全部索引中数据, 只需要扫描索引表, 不需要所有表中的所有数据

eg: EXPLAIN SELECT employee_number FROM `friend_bind`

all: 不走索引的查询, 查询全部表中的数据 

 

key_length: 索引的长度

主要用于判断复合索引是否被完全使用

char(20)的非空索引字段, 在utf8编码下, key_length=20 * 3 = 60字节
char(20)的可空索引字段, 在utf8编码下, key_length=20 * 3 + 1 = 61字节,  即如果索引字段可以为空, 那么会用1个字节来标识.
如果是varchar(xx), 在utf8编码下, 则key_length = xx * 3 + (可空? 1 : 0) + 2(用两个字节来标识可变长度)

 

Extra: 

using filesort: 性能消耗大, 需要额外一次排序. 常见于order by语句中. 只要查询字段 + 排序字段能走索引, 则大概率不会产生using filesort.

eg:  EXPLAIN SELECT * FROM `msg` where company_id = 1 ORDER BY type desc

company_id是普通索引, 而如果根据type查询的话, 则会产生using filesort, 需要再产生一张表根据type倒序, 然后根据company_id来做关联.

eg: EXPLAIN SELECT * FROM `msg` where company_id = 1 ORDER BY send_time desc

company_id + send_time 是联合索引, 直接查询索引树即可, 无需排序, 所以没有using filesort.

using temporary: 性能消耗大, 需要创建一张临时表, 常见于group by语句中. 需配合SQL执行过程来解释, 如果group by和where索引条件不同, 那么group by中的字段需要创建临时表分组后再回到原查询表中. 如果查询条件where和group by是相同索引字段, 那么就不需要临时表.

eg: EXPLAIN SELECT alias FROM `test`  where alias > 'bczj05' GROUP BY alias

 

using index: 性能提升, 索引覆盖, 不读取原文件, 只从索引文件中获取数据. 只要用到的列全部都在索引中, 就是using index.

eg: EXPLAIN SELECT alias FROM `friend_info` where alias = 'xxxxxx'

我们可以使用using index来做模糊查询, 在数据量大时有比较好的综合表现, 再加上limit则更好, 可以用于 模糊查询客户名称这类, 只展示20个.

eg: 

select * from `friend_info` where alias in ( SELECT alias FROM `friend_info` where alias like '%husl%')

select * from `friend_info` where alias like '%husl%'

 

using index condition: 性能提升, 索引条件下推, 在没有Index Condition Pushdown(ICP)之前, 引擎层根据索引条件将数据查询出来后将数据返回服务层, 服务层自己根据where条件过滤数据. 有了ICP后where中部分索引条件下推到引擎层, 由引擎层来直接进行过滤, 减少了IO次数. ?

eg: EXPLAIN SELECT * FROM `msg` WHERE `company_id` = 1 and send_time > '2020-07-01'

using where: 需要使用where中的条件过滤, 一般是没有走索引导致的. 也有可能是走了索引后, 还有条件需要过滤

eg: EXPLAIN SELECT * FROM `friend_info` where nick_name = 'shusley'

eg: EXPLAIN SELECT * FROM `friend_info` where alias = 'xxxxx' and nick_name = '123'

 

extra显示为空的情况: 使用了索引, 并没有其余的过滤条件

eg: EXPLAIN SELECT * FROM `friend_info` where alias = 'shusley'

 

四、避免索引失效

 

a. 范围查询(>, <, in) 之后的索引可能会失效, 若是联合索引, 则将其放置到索引的最后一个.

b.尽量不要使用类型转换(显式、隐式), 会使索引失效. 

c.尽量不要使用or, 否则索引失效. 

 

 

五、表锁、行锁介绍

a、myISAM表锁

在执行查询语句时, 会自动给表加读锁.
在执行更新、插入、DML语句时, 会自动给表加写锁.
1、对表的读操作(读锁), 不会阻塞其他会话对同一表的读请求. 但会阻塞对同一表的写请求. 只有当读锁释放后, 才会执行其他会话的写操作.
2、对表的写操作(写锁), 会阻塞其他会话对同一表的读和写操作, 只有当写锁释放后, 才会执行其余会话的读和写操作. 

特点: 表锁消耗性能低, 但并发也低.

 

b、innoDB行锁

innoDB中的行锁都是以索引来实现的, 锁定的也都是索引或者索引区间.

 

行锁的注意事项
a、InnoDB行锁是通过索引上的索引项来实现的, 如果没有索引, 那么行锁会转换为表锁. 

 

特点 : 比表锁消耗性能大, 并发能力强,效率高. 建议在高并发下使用.

六、使用行锁时, 如何尽量避免死锁

a、慎用update .... where column > 1111,  不管column有无索引, 最好情况是 > 1111的数据全部加锁(包括不存在的数据), 最差情况是没走索引, 那么就会锁整张表.

b、降低代码中事务执行时间, 执行时间越长越容易产生死锁.

c、不要使用插入或更新.

七、事务并发问题

1、脏读

又称无效数据读出。一个事务读取另外一个事务还没有提交的数据叫脏读。

例如：事务T1修改了一行数据，但是还没有提交，这时候事务T2读取了被事务T1修改后的数据，之后事务T1因为某种原因Rollback了，那么事务T2读取的就是脏数据。

2、不可重复读

同一个事务中，多次读出的同一数据是不一致的。

例如：事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行检验而再次读取该数据，便得到了不同的结果。

3、幻读

不好表述直接上例子吧：

在仓库管理中，管理员要给刚到的一批商品进入库管理，当然入库之前肯定是要查一下之前有没有入库记录，确保正确性。

管理员A确保库中不存在该商品之后给该商品进行入库操作，假如这时管理员B因为手快将已将该商品进行了入库操作。这时管理员A发现该商品已经在库中。就像刚刚发生了幻读一样，本来不存在的东西，突然之间他就有了。

注：三种问题看似不太好理解，脏读侧重的是数据的正确性。不可重复度侧重的于对数据的修改，幻读侧重于数据的新增和删除。

八、事务隔离级别

隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读已提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
可串行化（serializable）	否	否	否

 

InnoDB有三种行锁的算法：

1，Record Lock：单个行记录上的锁.

2，Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况.

3，Next-Key Lock：Record Lock + Gap Lock的组合.

 

九、RR事务隔离级别分析

让我们回想一下事务的特点, ACID, 原子性、一致性、隔离性、持久性. 不同隔离级别所做的事情都是为了尽可能满足这些条件.

RR事务隔离级别解决了脏读、不可重复读, 是怎么解决的?

 

脏读问题: 是一个事务读取另外一个事务还没有提交的数据叫脏读。很显然不满足事务的隔离性.

不可重复读问题: 同一个事务中，多次读出的同一数据是不一致的。不可重复读问题主要强调对数据的修改(更新). 很显然这不满足事务的一致性.

 

基于这个问题, RR级别下 MySQL会使用一种 MVCC机制, 即多版本并发控制. MVCC最大的好处是读不用加锁, 读写不冲突, 极大增大了系统的并发能力. 

简单来说, 在事务执行过程中, 只有第一次读之前的提交的记录和自己修改的记录可见, 其余的均不可见. 这种方式也称为快照读.

MVCC实现原理: 

 

 

而在RR中使用插入、更新、删除、显式加锁读这类操作, 则会当前读, 区别于上文的快照读.

当前读保证每次获取到当前记录最新版本, 会加锁保证其他事务不能修改当前记录, 直至释放锁.

在RR中对于聚簇索引、唯一索引的增删改查使用Record Lock, 而对于普通索引则使用Next-Key Lock.