mysql使用了index merge（索引合并）造成死锁分析总结

最新推荐文章于 2023-12-23 15:38:32 发布

阿钟小哥

最新推荐文章于 2023-12-23 15:38:32 发布

阅读量3.6k

点赞数 9

分类专栏： java基础 MySQL 文章标签： java mysql 数据库

本文链接：https://blog.csdn.net/zhong_shu_li/article/details/118361498

版权

java基础同时被 2 个专栏收录

9 篇文章 1 订阅

订阅专栏

MySQL

1 篇文章 0 订阅

订阅专栏

文章目录

背景
表结构
执行计划
为什么会用到 index_merge（索引合并）？
为什么用 index_merge 就造成了死锁？
解决方案
知识拓展：MySQL 锁

背景

生产环境出现数据库更新异常，查询日志发现是数据库死锁，异常信息如下：

nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

于是在本地建了一张test_table表来复现和分析死锁问题。

表结构

CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `request_no` varchar(32) NOT NULL COMMENT '请求流水',
  `status` int(2) NOT NULL COMMENT '状态',
  `request_system` varchar(128) NOT NULL COMMENT '请求系统',
  `request_date` datetime NOT NULL COMMENT '请求时间',
  `accept_id` varchar(32) DEFAULT NULL COMMENT '受理编号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_accept_id` (`accept_id`) USING BTREE,
  KEY `idx_request` (`request_no`,`request_system`,`request_date`) USING BTREE,
  KEY `idx_status` (`status`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

通过表结构可以看出，id列为主键索引，accept_id 列上有一个唯一索引 uk_accept_id，status 列上有一个普通索引idx_status，idx_request是（request_no，request_system，request_date）联合索引。我们知道， InnoDB引擎中有两种索引：

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

主键索引 PRIMARY 就是聚簇索引，叶子节点中会保存行数据。uk_accept_id、idx_status、idx_request为辅助索引，叶子节点中保存的是主键值，也就是id列值。当我们通过辅助索引查找行数据时，先通过辅助索引找到主键id，再通过主键索引进行二次查找（也叫回表），最终找到行数据。

执行计划

在这里插入图片描述
通过查看执行计划发现，update语句用到了索引合并，也就是这条语句既用到了 uk_accept_id 索引，又用到了idx_status索引，Using intersect() 的意思是通过两个索引获取交集。

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

1、MySQL5.0之前，一个表一次只能使用一个索引，无法同时使用多个索引分别进行条件扫描。但是从5.1开始，引入了 index merge 优化技术，对同一个表可以使用多个索引（一般是2个）分别进行条件扫描。简单的说，index merge 技术其实就是：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并，合并方式分为三种：union, intersection, 以及它们的组合sort_union(先内部intersect然后在外部union)；
2、在Oracle中intersect是关键字，mysql中没有intersect语法，但是explain使用到了索引交集的算法，估计是mysql优化器的一些优化手段，这个可以通过一个开关进行控制。

查看数据库是否开启了index_merge：show variables like ‘%optimizer_switch%’;
关闭index_merge（重启才能生效）：set @@global.optimizer_switch = ‘index_merge=off’ ；
开启index_merge（重启才能生效）：set @@global.optimizer_switch = ‘index_merge=on’ ；

例如执行计划中的sql: UPDATE test_table SET status = 0 WHERE status = 1 AND accept_id = '1492091112255' ;
MySQL会根据 accept_id = '1492091112255' 这个条件，利用 uk_accept_id 索引找到叶子节点中保存的id值；同时会根据 status = 1 这个条件，利用 idx_status 索引找到叶子节点中保存的id值；然后将找到的两组id值取交集，最终通过交集后的id回表，也就是通过 PRIMARY 索引找到叶子节点中保存的行数据。
这时可能很多人会有疑问了， uk_accept_id 已经是唯一索引了，通过这个索引最多只能找到一条数据，那MySQL优化器为啥还要用两个索引取交集，再回表进行查询呢，这样不是多了一次 idx_status 索引查找的过程么？接下来我们一起分析一下以下两种情况执行过程：
第一种只用uk_accept_id索引：

根据 accept_id = ‘1492091112255’ 查询条件，利用uk_accept_id索引找到叶子节点中保存的id值；
通过找到的id值，利用PRIMARY索引找到叶子节点中保存的行数据；
再通过 status = 1 条件对找到的行数据进行过滤。

第二种用到索引合并 Using intersect(uk_accept_id,idx_status) ：

根据 accept_id = ‘1492091112255’ 查询条件，利用uk_accept_id索引找到叶子节点中保存的id值；
根据 status = 1 查询条件，利用 idx_status 索引找到叶子节点中保存的id值；
将1、2步中找到的id值取交集，然后利用PRIMARY索引找到叶子节点中保存的行数据

上边两种情况的主要区别在于，第一种是先通过一个索引把数据找到后，再用其它查询条件进行过滤；第二种是先通过两个索引查出的id值取交集，如果取交集后还存在id值，则再去回表将数据取出来。当优化器认为第二种情况执行成本比第一种要小时，就会出现索引合并。（我的测试表中 status = 1 的数据非常少，这也是优化器考虑用第二种情况的原因之一）。

为什么用 index_merge 就造成了死锁？

在这里插入图片描述
上面简要画了一下两个update事务加锁的过程，从图中可以看到，在 idx_status 索引和 PRIMARY （聚簇索引）上都存在重合交叉的部分，这样就为死锁造成了条件。如，当遇到以下时序时，就会出现死锁：

事务一(update1)	事务二(update2)
锁住 uk_accept_id索引中 accept_id 为111的索引项
	锁住 uk_accept_id索引中 accept_id 为222的索引项
回表锁住 PRIMARY索引中 id 为1的索引项
	回表锁住 PRIMARY索引中 id 为2的索引项
锁住 idx_status 索引中 status 为1的索引项
	试图锁住 idx_status 索引中 status为1的索引项，发现已经被事务一锁住，等待事务一释放锁
试图回表锁住 PRIMARY 索引中 id 为1和2的索引项，发现 id 为2的索引项已经被事务二锁住，等待事务二释放锁

事务一等待事务二释放锁，事务二等待事务一释放锁，这样就造成了死锁。

注意： 如果update语句操作了非主键索引，mysql会先锁住非主键索引，再锁定主键索引，当有多个事务进行更新操作时就很容易造成死锁。MySQL检测到死锁后，会自动回滚代价更低的那个事务。

解决方案

一、从代码层面

where 查询条件中，只使用uk_accept_id ，将数据查询出来后，在代码层面判断 status 状态是否为1；
使用 force index(uk_accept_id) 强制查询语句使用uk_accept_id 索引；
where 查询条件后边直接用 id 字段，通过主键去更新(推荐)。

二、从MySQL层面

删除 idx_status 索引或者建一个包含这俩列的联合索引；
将MySQL优化器的index merge优化关闭。

注：

1、一般情况下，数据量越大 index merge开启的优化效果会越明显，所以没有特别要求不用关闭，默认是开启。
2、MySQL官方已经确认了此bug：传送门：Bug #77209

知识拓展：MySQL 锁

mysql的锁级别

MySQL有三种锁的级别：页级锁、表级锁、行级锁。
MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。
MySQL这3种锁的特性可大致归纳如下：
- 页级锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
- 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
- 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

注意： 行级锁并不是直接锁记录，而是锁索引，如果一条SQL语句用到了主键索引，mysql会锁住主键索引；如果一条语句操作了非主键索引，mysql会先锁住非主键索引，再锁定主键索引。

innoDB的锁模式

在InnoDB存储引擎中，锁的类型通常分为共享锁和排他锁。
- 共享锁(shared lock)：也叫读锁(read lock)，指多个请求在同一时刻可以同时读取同一个资源，互相不干扰（其他事务可以读，但不能写）。
- 排他锁(exclusive lock)：也叫写锁(write lock)，一个写锁会阻塞其他的写锁和读锁，这样能确保一定的时间内，只有一个请求能执行写入操作，防止其他请求读取或写入同一资源（其他事务不能读取，也不能写）。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

锁模式的兼容情况：
如果一个事务请求的锁模式与当前的锁兼容， InnoDB 就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。
在这里插入图片描述

InnoDB加锁方法

意向锁是 InnoDB 自动加的，不需用户干预。
对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB 会自动给涉及数据集加排他锁（X)；
-对于普通 SELECT 语句，InnoDB 不会加任何锁；
事务可以通过以下语句显式给记录集加共享锁或排他锁（显式锁定）：
- 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。其他 session 仍然可以查询，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
- 排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁。
InnoDB在事务执行过程中，使用两阶段锁协议（隐式锁定）：
- 随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁；
- 锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。

死锁的产生和避免

产生死锁的原因主要有：
（1）系统资源不足。
（2）进程运行推进的顺序不合适。
（3）资源分配不当等。
如果系统资源充足，进程的资源请求都能够得到满足，死锁出现的可能性就很低，否则就会因争夺有限的资源而陷入死锁。其次，进程运行推进顺序与速度不同，也可能产生死锁。
产生死锁的四个必要条件：
（1）互斥条件：一个资源每次只能被一个进程使用。
（2）请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
（3）不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。
（4）循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。
这四个条件是死锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生死锁。
避免死锁的产生：（通过破除死锁四个必要条件之一，来防止死锁产生）
1、避免使用子查询，尽量使用主键更新数据，防止使用非聚簇索引回表时和使用聚簇索引冲突造成死锁。
2、避免长(大)事务，将事务拆解为短(小)事务，尽快提交事务，减少持有锁的时间。
3、设置锁超时等待innodb_lock_wait_timeout。
4、除非必须，否则查询时不要显示加锁。
5、为表设计合理的索引，并尽量使用索引访问数据，使加锁更精确，如果不走索引将会为表的每一行记录加锁，死锁的概率会大大增加。
6、以固定的顺序访问表和行。比如两个更新数据的事务，事务A 更新的顺序为1，2；事务B更新的顺序为2，1，这样更容易造成死锁。
7、对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。
8、尽量使用较低的隔离级别；

高并发下如何对数据库进行更新操作

在高并发的情况下，多个请求对同一条数据进行更新操作，如果没有防重机制做拦截，就会导致数据被更新多次，也容易造成数据库死锁，从而影响更新后程序的后续操作。
常用的解决方案有以下几种：
1、在更新前，加锁(分布式系统用分布式锁、也可用数据库锁等)，查询需要更新的数据是否存在且未被更新，根据查询结果做更新操作，更新后释放锁；
伪代码如下：

/**
 * 适用情况：对数据需要进行多次更新，或多个中间状态都可以对数据进行操作；
 * 方案优势：保证数据更新的一致性；
 * 方案缺点：系统性能消耗大，锁机制影响系统的效率；
 */
 
// 本示例以分布式系统为例，单机系统下直接使用Lock或synchronized锁即可
// 1.用redis加锁
redis.set(key, value); 
// 2.查询要更新的数据
Object obj = select * from table where 条件;
// 3.判断数据是否存在且已经被更新
if(obj != null && obj未被更新){
	update table set XXX=x2 where 条件; 
}
// 4.释放锁
redis.del(key);

2、数据库层面拦截，直接在更新数据时带状态更新数据，根据更新后成功与否做后续操作；
伪代码如下：

/**
 * 适用情况：明确知道更新数据前或后的状态，且不会在更新时多次发生状态变化，保证前后一致性；
 * 方案优势：简单，保证数据更新的一致性，且对效率影响较小；
 * 方案缺点：系统性能消耗大，锁机制影响系统的效率；
 */

// 更新数据时带状态更新
int result = update table set 更新的内容 where 原数据的内容  != 更新的内容; 
//  更新成功
if(result == 1){ 
    // 执行更新成功后的操作    
} else { 
    // 执行更新失败后的操作 
}

3、推荐：根据主键做更新操作（有效预防死锁）。

返回篇头

阿钟小哥

关注

9
点赞
踩
30

收藏

觉得还不错? 一键收藏
打赏
9
评论
mysql使用了index merge（索引合并）造成死锁分析总结

mysql数据库更新时造成死锁，经查看执行计划发现是使用到了index merge索引合并算法，那个这个索引合并是什么呢?为什么会用到索引合并呢？使用index merge为什么会造成死锁呢？怎么避免或解决死锁呢？接下来通过这篇文章带你一探究竟。
复制链接

扫一扫