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 |
+----+-------------+----------+------------+-------+-----------------+-----------------+---------+-------+------+----------+-------------+

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

解决方案：

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

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