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

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

UPDATE a SET a.data = a.data - 1  WHERE  index1_id = xxx AND index2_id = xxx

首先出现这种情况是由于前一个事务一直没有释放所占用的资源，导致第二个事务无法获取资源，但其实两个事务之间在业务上没有关系，也不应该出现冲突，通过explain分析在type字段出现index_merge，表示对全表进行了查询，可能就是在将index1和index2索引取交集的时候锁定了index2的表，导致其他事务无法进行。可以使用show engine innodb status\G;来查看最近一次的死锁信息。至于mysql优化器为什么要合并索引，优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少，扫描行数并不是唯一的判断标准,优化器还会结合是否使用临时表、是否排序等因素进行综合判断。我们可以通过explain看一下扫描行数。执行原SQL语句，扫描行数为1，因为优化器觉得通过两个索引可以唯一确定数据

+----+-------------+----------+------------+-------------+---------------------------------+---------------------------------+---------+------+------+----------+---------------------------------------------------------------+
 id  select_type  table     partitions  type         possible_keys                    key                              key_len  ref   rows  filtered  Extra                                                         
+----+-------------+----------+------------+-------------+---------------------------------+---------------------------------+---------+------+------+----------+---------------------------------------------------------------+
  1  UPDATE       a  NULL        index_merge  index1,index2  index1,index2  4,4      NULL     1    100.00  Using intersect(index1,index2); Using where 
+----+-------------+----------+------------+-------------+---------------------------------+---------------------------------+---------+------+------+----------+---------------------------------------------------------------+

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

+----+-------------+----------+------------+-------+-----------------+-----------------+---------+-------+------+----------+-------------+
 id  select_type  table     partitions  type   possible_keys    key              key_len  ref    rows  filtered  Extra       
+----+-------------+----------+------------+-------+-----------------+-----------------+---------+-------+------+----------+-------------+
  1  UPDATE       recharge  NULL        range  index1  index1  4        const     3    100.00  Using where 
+----+-------------+----------+------------+-------+-----------------+-----------------+---------+-------+------+----------+-------------+

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

+----+-------------+----------+------------+-------+-----------------+-----------------+---------+-------+------+----------+-------------+
 id  select_type  table     partitions  type   possible_keys    key              key_len  ref    rows  filtered  Extra       
+----+-------------+----------+------------+-------+-----------------+-----------------+---------+-------+------+----------+-------------+
  1  UPDATE       recharge  NULL        range  index2  index2  4        const   114    100.00  Using where 
+----+-------------+----------+------------+-------+-----------------+-----------------+---------+-------+------+----------+-------------+

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

解决方案：

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

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