当前位置：首页 > news >正文

男做基视频网站郑州网站排名优化公司

news 2025/7/23 6:24:04

男做基视频网站,郑州网站排名优化公司,wordpress炫酷博客,吴忠网站建设提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档文章目录案例分析生产背景死锁日志表结构执行计划 EXPLAN为什么会用 index_merge（索引合并）为什么用了 index_merge就死锁了解决方案注：M…

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

文章目录

案例分析
- 生产背景
- 死锁日志
- 表结构
- 执行计划 EXPLAN
- 为什么会用 index_merge（索引合并）
- 为什么用了 index_merge就死锁了
- 解决方案
- - - 注：MySQL官方已经确认了此bug：==77209==

案例分析

生产背景

生产环境出现死锁流水，通过查看死锁日志，看到造成死锁的是两条一样的update语句（只有where条件中的值不同），如下：

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)

简要分析下上边的死锁日志：

1、第一块内容（第1行到第9行）中，第6行为事务(1)执行的SQL语句，第7和第8行意思为事务(1)在等待 idx_status 索引上的X锁；
2、第二块内容（第11行到第19行）中，第16行为事务(2)执行的SQL语句，第17和第18行意思为事务(2)持有 idx_status 索引上的X锁；
3、第三块内容（第21行到第23行）的意思为，事务(2)在等待 PRIMARY 索引上的X锁。（but not gap指不是间隙锁）
4、最后一句的意思即为，MySQL将事务(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

主键索引： id
唯一索引： trans_id
普通索引：status

InnoDB引擎中有两种索引：

聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。
辅助索引：辅助索引叶子节点存储的是主键值，也就是聚簇索引的键值。

主键索引 PRIMARY 就是聚簇索引，叶子节点中会保存行数据。 uniq_trans_id 索引和 idx_status 索引为辅助索引，叶子节点中保存的是主键值，也就是id列值。

当我们通过辅助索引查找行数据时，先通过辅助索引找到主键id，再通过主键索引进行二次查找（也叫回表），最终找到行数据。

执行计划 EXPLAN

在这里插入图片描述

通过看执行计划，可以发现，update语句用到了index merge(索引合并)，也就是这条语句既用到了 uniq_trans_id 索引，又用到了 idx_status 索引，

Using intersect(uniq_trans_id,idx_status) 的意思是通过两个索引获取交集。

为什么会用 index_merge（索引合并）

MySQL5.0之前，一个表一次只能使用一个索引，无法同时使用多个索引分别进行条件扫描。但是从5.1开始，引入了 index merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描。

如执行计划中的语句：

UPDATE test_table SET `status` = 1 WHERE `trans_id` = '38' AND `status` = 0 ;

MySQL会根据 trans_id = ‘38’ 这个条件，利用 uniq_trans_id 索引找到叶子节点中保存的id值；同时会根据 status = 0 这个条件，利用 idx_status 索引找到叶子节点中保存的id值；然后将找到的两组id值取交集，最终通过交集后的id回表，也就是通过 PRIMARY 索引找到叶子节点中保存的行数据。

这里可能很多人会有疑问了，uniq_trans_id 已经是一个唯一索引了，通过这个索引最终只能找到最多一条数据，那MySQL优化器为啥还要用两个索引取交集，再回表进行查询呢，这样不是多了一次 idx_status 索引查找的过程么。我们来分析一下这两种情况执行过程。

在这里插入图片描述