前几天线上收到一条告警邮件,生产环境MySQL操作发生了死锁,邮件告警的提炼出来的SQL大致如下。
update pe_order_product_info_test
set end_time = '2021-04-30 23:59:59'
where order_no = '111111111'
and product_id = 123456
and status in (1,2);
update pe_order_product_info_test
set end_time = '2021-04-30 23:59:59'
where order_no = '222222222'
and product_id = 123456
and status in (1,2);
是一条Update语句,定位了它的调用情况,发现Update的调用方只有一处,并且在Cat中看到一个小时的调用次数只有700多次,这个调用量基本与并发Update引起死锁无关了。
当时猜测了几种情况,这里Update进行操作时有其他业务方调用Select相关的接口,但是排查了那个时间点发生死锁应用的调用链,发现好像并没有其他会影响到Update的调用。
为了更进一步了解当时的情况,就联系了DBA老师,要了当时死锁发生时的日志,准备拿到日志之后大干一场,好好分析一下问题,结果...
![](https://i-blog.csdnimg.cn/blog_migrate/fe0e5538eaf6dcb399ddeff073b44188.png)
DBA老师看了死锁日志直接点出了问题要害——index_merge
索引合并。
1. 什么是索引合并
这是MySQL在5.1引入的优化技术,再此之前,一个表仅仅只能使用一个索引,但索引合并的引入,可以对同一张表使用多个索引分别进行条件扫描。
如果要拿索引合并index_merge与只使用一个索引做比较,那么拿上面那个update语句来做演示。
update pe_order_product_info_test
set end_time = '2021-04-30 23:59:59'
where order_no = '111111111'
and product_id = 123456
and status in (1,2);
只是用一个索引时,MySQL会选择一个最优的索引来使用,比如使用index_order_no,拿它来找出所有order_no为111111111的索引记录,从该索引上找到它的PRIMARY
索引的id
,然后回表找到对应的行数据,最后在内存中根据剩下的product_id和status条件来进行过滤。
但如果MySQL优化器觉得你如果只是用一个索引,拿出大量记录,然后再在内存中使用product_id和status过滤(并且符合该条件的记录值很少),这个第二步效率可能不高时,他就会使用索引合并进行优化。
如果使用索引合并去判断where条件时,那么它就会先通过index_order_no索引去找到PRIMARY
索引的id
,再通过index_product_id索引去找到PRIMARY
索引的id
,最后将两个id集合求交集,再回表找到行数据。(索引合并使用索引的顺序是不确定的)
2. 场景复现
在MySQL的Bug反馈文档中也有记录一个Bug #77209的记录,标注了索引合并引发死锁的情况。但是我按照它给出的repeat并不能重现索引合并的场景,在它的实例中早了600万随机数,我猜测可能是MySQL调高了索引合并的条件,将数据量增加到了1000万。
先来带大家复现一下当时的情况。
环境:MySQL 5.6.24
-
创建一张测试表
CREATE TABLE `a` ( `ID` int AUTO_INCREMENT PRIMARY KEY, `NAME` varchar(21), `STATUS` int, KEY `NAME` (`NAME`), KEY `STATUS` (`STATUS`) ) engine = innodb;
-
导入数据,为了方便导入一些随机数据,需要先开启一个兼容性配置。
set global show_compatibility_56=on;
开始导入随机数据。
set @N=0; insert into a(ID,NAME,STATUS) select @N:=@N+1, @N%1600000, floor(rand()*4) from information_schema.global_variables a, information_schema.global_variables b, information_schema.global_variables c LIMIT 10000000;
-
测试
update a set status=5 where rand() < 0.005 limit 1; explain UPDATE a SET STATUS = 2 WHERE NAME = '1000000' AND STATUS = 5;
![](https://i-blog.csdnimg.cn/blog_migrate/080a9194c242894076eab21863d88a0d.png)
3. 为什么发生了死锁
直接上一副图,以及两个update事务的加锁流程。
![](https://i-blog.csdnimg.cn/blog_migrate/fb8fd67ab9cf7ec11289a63d1786f0d8.png)
可以看到在订单与产品这个模型中,Update事务一和Update事物二在product_id索引和primary索引上都存在交叉重合,这就导致了死锁的发生。
步数 | 事务一 | 事务二 |
---|---|---|
1 | 锁住index_order_no 索引树上order_no为2222的索引项 | |
2 | 锁住index_order_no 索引树上order_no为3333的索引项 | |
3 | 回表锁住 PRIMARY 索引中 id 为 11 的索引项 | |
4 | 回表锁住 PRIMARY 索引中 id 为 12 的索引项 | |
5 | 锁住index_product_id 索引树上product_id为2000的四个索引项 | |
6 | 尝试去锁住index_product_id 索引树上product_id为2000的四个索引项,但是已经被事务一锁住,等待事务一释放 在index_product_id 上的锁 | |
7 | 试图回表锁住 PRIMARY 索引中 id 为10,11,12,13的索引项,发现id为12的索引项在第4步 已经被事务二锁住,等待事务二释放 在 |
这就是本次死锁发生的原因所在了,解决方案有很多种,可以根据具体场景选择。
- 删除某一个索引,这当然不是一个好办法
- 关闭index_merge优化
- 为查询条件增加联合索引,在本例中是product_id和order_no。
4. 最后
当然最后这些都是我个人的分析,DBA老师给的建议是直接上联合索引,网上关于索引合并的资料实在太少了,除了官方文档简单扯了扯,剩下的都是转载来转载去的博客,内容都一模一样,DBA老师也不写博客,所以我就只能按我上述这个思路理解了,如果网友有什么问题欢迎指出~