MySQL 优化 index merge(索引合并)引起的死锁分析（强烈推荐）
程序员的成长之路
共 4862字，需浏览 10分钟
·
2022-01-23 18:32
阅读本文大概需要 5 分钟。
来自：blog.csdn.net/daidaineteasy/article/details/109266083
背景
UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx1' AND `status` = 0;
UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx2' AND `status` = 0;
死锁日志
*** (1) TRANSACTION:
TRANSACTION 791913819, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 462005230, OS thread handle 0x7f55d5da3700, query id 2621313306 x.x.x.x test_user Searching rows for update
UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx1' AND `status` = 0;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 110 page no 39167 n bits 1056 index `idx_status` of table `test`.`test_table` trx id 791913819 lock_mode X waiting
Record lock, heap no 495 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
*** (2) TRANSACTION:
TRANSACTION 791913818, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999
mysql tables in use 3, locked 3
5 lock struct(s), heap size 1184, 4 row lock(s)
MySQL thread id 462005231, OS thread handle 0x7f55cee63700, query id 2621313305 x.x.x.x test_user Searching rows for update
UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx2' AND `status` = 0;
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 110 page no 39167 n bits 1056 index `idx_status` of table `test`.`test_table` trx id 791913818 lock_mode X
Record lock, heap no 495 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 110 page no 41569 n bits 88 index `PRIMARY` of table `test`.`test_table` trx id 791913818 lock_mode X locks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 30; compact format; info bits 0
*** WE ROLL BACK TRANSACTION (1)
表结构
CREATE TABLE `test_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`trans_id` varchar(21) NOT NULL,
`status` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_trans_id` (`trans_id`) USING BTREE,
KEY `idx_status` (`status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
聚簇索引： 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。 辅助索引： 辅助索引叶子节点存储的是主键值，也就是聚簇索引的键值。
执行计划
uniq_trans_id 索引，又用到了 idx_status 索引，Using intersect(uniq_trans_id,idx_status)的意思是通过两个索引获取交集。为什么会用 index_merge（索引合并）
index merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描。UPDATE test_table SET `status` = 1 WHERE `trans_id` = '38' AND `status` = 0 ;
trans_id = ‘38’这个条件，利用 uniq_trans_id 索引找到叶子节点中保存的id值；同时会根据 status = 0这个条件，利用 idx_status 索引找到叶子节点中保存的id值；然后将找到的两组id值取交集，最终通过交集后的id回表，也就是通过 PRIMARY 索引找到叶子节点中保存的行数据。uniq_trans_id 已经是一个唯一索引了，通过这个索引最终只能找到最多一条数据，那MySQL优化器为啥还要用两个索引取交集，再回表进行查询呢，这样不是多了一次 idx_status 索引查找的过程么。我们来分析一下这两种情况执行过程。根据 trans_id = ‘38’查询条件，利用uniq_trans_id索引找到叶子节点中保存的id值；通过找到的id值，利用PRIMARY索引找到叶子节点中保存的行数据； 再通过 status = 0条件对找到的行数据进行过滤。
Using intersect(uniq_trans_id,idx_status)：根据 trans_id = ‘38’查询条件，利用uniq_trans_id索引找到叶子节点中保存的id值；根据 status = 0查询条件，利用idx_status索引找到叶子节点中保存的id值；将1/2中找到的id值取交集，然后利用PRIMARY索引找到叶子节点中保存的行数据
status = 0 的数据非常少，这也是优化器考虑用第二种情况的原因之一）。index_merge 就死锁了
idx_status 索引和 PRIMARY （聚簇索引） 上都存在重合交叉的部分，这样就为死锁造成了条件。
解决方案
一、从代码层面
where 查询条件中，只传 trans_id，将数据查询出来后，在代码层面判断 status 状态是否为0；使用 force index(uniq_trans_id)强制查询语句使用uniq_trans_id索引；where 查询条件后边直接用 id 字段，通过主键去更新。
二、从MySQL层面
删除 idx_status索引或者建一个包含这俩列的联合索引；将MySQL优化器的 index merge优化关闭。
内容包含Java基础、JavaWeb、MySQL性能优化、JVM、锁、百万并发、消息队列、高性能缓存、反射、Spring全家桶原理、微服务、Zookeeper、数据结构、限流熔断降级......等技术栈！
⬇戳阅读原文领取！ 朕已阅 
评论