mysql自动合并索引(index merge)查询导致死锁问题

By | 2021年3月17日

mysql自动合并索引(index merge)查询导致死锁问题

虽然在生产环境上加了分布式锁,但还是会出现某一个事务未结束,而下一个事务进入来修改数据,这时就会陷入等待,最后等待超时,事务进行了回滚,在运行几个月后第一次出现这种情况,发生死锁的是两条update语句,当sql语句的where语句中使用两个索引时,mysql的优化器可能会对这两个索引进行合并,使用explain分析会显示Using intersect(index1,index2); 表示将index1和index2合并来查询。该表中只有index1,index2两个索引。

首先出现这种情况是由于前一个事务一直没有释放所占用的资源,导致第二个事务无法获取资源,但其实两个事务之间在业务上没有关系,也不应该出现冲突,通过explain分析在type字段出现index_merge,表示对全表进行了查询,可能就是在将index1和index2索引取交集的时候锁定了index2的表,导致其他事务无法进行。

可以使用show engine innodb status\G;来查看最近一次的死锁信息。

至于mysql优化器为什么要合并索引,优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。在数据库里面,扫描行数是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的 CPU 资源越少,扫描行数并不是唯一的判断标准,优化器还会结合是否使用临时表、是否排序等因素进行综合判断。我们可以通过explain看一下扫描行数。

执行原SQL语句,扫描行数为1,因为优化器觉得通过两个索引可以唯一确定数据

使用force index() 来分别指定索引,先使用force index(index1) ,扫描行数为3

使用force index(index2) ,扫描行数为114

所以优化器合并两个索引是有道理的,但没想到会造成其他事务的阻塞

解决方案:

  1. 在SQL语句使用force index()来指定要使用的索引,但在实际开发中不太方便
  2. 将优化器的index merge优化关闭
  3. 删除掉index2或者index1的索引,这样也可以解决问题,让优化器只能使用一个索引
  4. 添加一个联合索引,包含index1+index2的,优化器会直接使用这个索引

添加合理的索引是最合适的办法。

发表评论

电子邮件地址不会被公开。 必填项已用*标注