有一个刚上线的MySQL检测到死锁了，发生死锁的是一条update语句。开发过来问我，说这条update语句只更新一行，从业务上看，这一行数据绝对不会发生同时更新的，索引也建了，怎么会发生死锁呢？我问索引是怎么建的，他说更新选择条件的每一列都建了索引，MySQL不是有索引合并吗？正常来说不应该发生死锁吧！是不是索引合并没生效。我又问，数据库的隔离级别是什么？他说是可重复读。
到这里问题就清楚了，对这条语句来说，索引合并虽然和联合索引同样过滤出一条数据，但是在事务隔离级别是可重复读时，索引合并要对每个索引扫描的数据加锁，就是索引合并起作用也会造成死锁。这个问题的解决方案有两个：一是根据update的条件建立联合索引，这个方法不仅可以解决死锁问题，语句的执行也更高效，是优选方案；二是将事务的隔离级别改为读提交，这样基本上可以避免死锁，但是性能上还是差点。
索引合并是什么，有什么用途，性能怎么样，怎样对数据进行加锁？这些是下面要说明的问题。
1 什么是索引合并？它有什么用途？
索引合并可能是MySQL数据库独有的特性，据我所知，Oracle和PG都没有这个特性，这个特性是什么？应用于那些场景？看几个例子一下子就明白了。
show indexes from sales;
Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|S
-----+----------+--------+------------+-----------+---------+-----------+-
sales| 1|idx_cust| 1|cust_id |A | 4301|
sales| 1|idx_time| 1|time_id |A | 725|
sale表上cust_id，time_id列上都建立索引，看一下下面这条语句的执行计划：
explain update sales set quantity_sold =2 where cust_id =6394 and time_id = '2019-05-27';
id|select_type|table|partitions|type |possible_keys |key |key_len|ref|rows|filtered|Extra |
--+-----------+-----+----------+-----------+-----------------+-----------------+-------+---+----+--------+-----------------------------------------------+
1|UPDATE |sales| |index_merge|idx_cust,idx_time|idx_cust,idx_time|5,4 | | 1| 100.0|Using intersect(idx_cust,idx_time); Using where|
执行计划中intersect(District,CountryCode)表明MySQL执行了索引合并，使用两个索引扫描到的数据的交集对表city_h进行了范围扫描，大幅度减少了回表的次数，在某些场合上可以替代联合索引。
索引合并不仅只是索引的交集，也支持索引的并集，
explain update sales set quantity_sold =2 where cust_id =6394 or time_id = '2019-05-27';
id|select_type|table|partitions|type |possible_keys |key |key_len|ref|rows|filtered|Extra |
--+-----------+-----+----------+-----------+-----------------+-----------------+-------+---+----+--------+-------------------------------------------+
1|UPDATE |sales| |index_merge|idx_cust,idx_time|idx_cust,idx_time|5,4 | | 35| 100.0|Using union(idx_cust,idx_time); Using where|
语句执行了索引合并，输出执行计划的Extra信息里显示using union（idx_cust,idx_time),将其中一个索引设置不可见，再看一下这条语句的执行计划
alter table sales alter index idx_cust invisible;
id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra |
--+-----------+-----+----------+----+-------------+---+-------+---+----+--------+-----------+
1|UPDATE |sales| |ALL | | | | |9607| 100.0|Using where|
这次执行了全面扫描，效率大幅度降低。在不能使用上面两种合并算法时，索引合并还有第三种，算法不是union，而是索引合并排序并集访问算法，看一下下面语句的执行计划：
explain update sales set quantity_sold =2 where cust_id =6394 or time_id = '2019-05-27' or cust_id=6488;
id|select_type|table|partitions|type |possible_keys |key |key_len|ref|rows|filtered|Extra |
--+-----------+-----+----------+-----------+-----------------+-----------------+-------+---+----+--------+------------------------------------------------+
1|UPDATE |sales| |index_merge|idx_cust,idx_time|idx_cust,idx_time|5,4 | | 41| 100.0|Using sort_union(idx_cust,idx_time); Using where|
这个索引合并使用了第三种算法。extra信息里显示Using sort_union(idx_cust,idx_time),执行效率也远高于使用全表扫描。
2 索引合并和联合索引的对比
2.1 执行效率对比
执行效率的比较再MySQL的默认事务隔离级别(可重复读）下：
show variables like '%isola%';
Variable_name |Value |
---------------------+---------------+
transaction_isolation|REPEATABLE-READ|
a 索引合并
explain analyze select * from sales where cust_id =6394 and time_id = '2019-05-27';
EXPLAIN |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-> Filter: ((sales.time_id = DATE'2019-05-27') and (sales.cust_id = 6394)) (cost=1.99 rows=1) (actual time=0.160..0.175 rows=1 loops=1)¶ -> Index range scan on sales using intersect(idx_cust,idx_time) (cost=1.99 rows=1) (actual time=0.157..0.173 rows|
b 联合索引
sales表的cust_id和time_id联合起来可以创建唯一索引
alter table sales add unique key idx_c_t(cust_id,time_id);
EXPLAIN |
-----------------------------------------------------------------------------------------------------+
-> Rows fetched before execution (cost=0.00..0.00 rows=1) (actual time=0.000..0.000 rows=1 loops=1)¶|
c 分析
对比使用上面两个执行计划，联合索引的执行效率明显高于索引合并，后者毕竟要对两个索引分别进行扫描，如果两个索引返回的数据都比较大，合并前对中间结果的存储的代价也不容忽视。
2.2 使用的灵活性
对比联合索引，索引合并可以应用于更多的场合，比如where 条件不是and 而是or的情况下，这时联合索引是用不上的，而索引合并仍然可以使用，设置在where条件为and和or的稍微复杂的组合的情况下也可以使用。
另一种是以单列为查询条件时，联合索引只支持以最左面列为查询条件，索引合并由于是各列分别建立索引，所以没有这个限制。
3 索引合并和联合索引的加锁分析
3.1 事务隔离级别为可重复读时的加锁
3.1.1 联合索引
关闭自动提交，执行下面的查询语句并检查加锁情况
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from sales where cust_id=164 and time_id='2019-06-29' for update;
+---------+------------+---------------+-------------+
| cust_id | time_id | quantity_sold | amount_sold |
+---------+------------+---------------+-------------+
| 164 | 2019-06-29 | 1 | 28 |
+---------+------------+---------------+-------------+
1 row in set (0.00 sec)
--查询语句的加锁情况
select object_name,index_name,LOCK_DATA,LOCK_MODE from performance_schema.data_locks where object_name='sales';
object_name|index_name |LOCK_DATA |LOCK_MODE |
-----------+---------------+----------------------------+-------------+
sales | | |IX |
sales |idx_c_t |164, 1033949, 0x0000000CB76E|X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB76E |X,REC_NOT_GAP|
语句对sales表加了意向排他锁，对索引中的选中条目（索引键为（164，1033949）加了非GAP X锁，对cluste表选中的条目也加了非GAP X锁，不会锁定其他行）。
3.1.2 索引合并
同样执行上述语句，执行计划显示使用了索引合并，语句的加锁如下：
object_name|index_name |LOCK_DATA |LOCK_MODE |
-----------+---------------+-----------------------+-------------+
sales | | |IX |
sales |idx_cust |164, 0x0000000CB76E |X |
sales |idx_cust |164, 0x0000000CB791 |X |
sales |GEN_CLUST_INDEX|0x0000000CB76C |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB76D |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB76E |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB76F |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB770 |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB771 |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB772 |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB773 |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB774 |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB775 |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB776 |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB777 |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB778 |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB779 |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB77A |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB77B |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB77C |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB77D |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB77E |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB77F |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB780 |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB781 |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB782 |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB783 |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB784 |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB785 |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB786 |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB787 |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB788 |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB789 |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB78A |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB78B |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB791 |X,REC_NOT_GAP|
sales |idx_time |supremum pseudo-record |X |
sales |idx_time |1033949, 0x0000000CB76C|X |
sales |idx_time |1033949, 0x0000000CB76D|X |
sales |idx_time |1033949, 0x0000000CB76E|X |
sales |idx_time |1033949, 0x0000000CB76F|X |
sales |idx_time |1033949, 0x0000000CB770|X |
sales |idx_time |1033949, 0x0000000CB771|X |
sales |idx_time |1033949, 0x0000000CB772|X |
sales |idx_time |1033949, 0x0000000CB773|X |
sales |idx_time |1033949, 0x0000000CB774|X |
sales |idx_time |1033949, 0x0000000CB775|X |
sales |idx_time |1033949, 0x0000000CB776|X |
sales |idx_time |1033949, 0x0000000CB777|X |
sales |idx_time |1033949, 0x0000000CB778|X |
sales |idx_time |1033949, 0x0000000CB779|X |
sales |idx_time |1033949, 0x0000000CB77A|X |
sales |idx_time |1033949, 0x0000000CB77B|X |
sales |idx_time |1033949, 0x0000000CB77C|X |
sales |idx_time |1033949, 0x0000000CB77D|X |
sales |idx_time |1033949, 0x0000000CB77E|X |
sales |idx_time |1033949, 0x0000000CB77F|X |
sales |idx_time |1033949, 0x0000000CB780|X |
sales |idx_time |1033949, 0x0000000CB781|X |
sales |idx_time |1033949, 0x0000000CB782|X |
sales |idx_time |1033949, 0x0000000CB783|X |
sales |idx_time |1033949, 0x0000000CB784|X |
sales |idx_time |1033949, 0x0000000CB785|X |
sales |idx_time |1033949, 0x0000000CB786|X |
sales |idx_time |1033949, 0x0000000CB787|X |
sales |idx_time |1033949, 0x0000000CB788|X |
sales |idx_time |1033949, 0x0000000CB789|X |
sales |idx_time |1033949, 0x0000000CB78A|X |
sales |idx_time |1033949, 0x0000000CB78B|X |
从上面查询的加锁结果来看，除了对sales表加意向排他锁，对索引idx_cust,idx_time复合查询条件的每条数据都加了排他锁，对聚簇索引中复合每个条件的记录也加了非GAP 排他锁。
3.1.3 对比分析
从上面可以看到，在MySQL默认的可重复读隔离级别下，索引合并对符合每个索引查询条件的索引条目和表数据都进行了加了X锁，加锁的实际数目和每个条件筛选出条目的数量有关，在高并发的情况下，很容易造成死锁。
3.2 事务隔离级别为读已提交时的加锁
更改会话的事务隔离级别为读已提交，再看两者的加锁
3.2.1 更改事务隔离级别为读已提交
mysql> show variables like '%isola%'; --显示当前事务隔离级别
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)
mysql> set transaction_isolation='READ-COMMITTED';--设置事务隔离级别为读已提交
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%isola%';--检查更改成功
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)
3.2.2 联合索引时的加锁
--执行查询语句
mysql> select * from sales where cust_id=164 and time_id='2019-06-29' for update;
+---------+------------+---------------+-------------+
| cust_id | time_id | quantity_sold | amount_sold |
+---------+------------+---------------+-------------+
| 164 | 2019-06-29 | 1 | 28 |
+---------+------------+---------------+-------------+
1 row in set (0.00 sec)
--检查加锁情况
object_name|index_name |LOCK_DATA |LOCK_MODE |
-----------+---------------+----------------------------+-------------+
sales | | |IX |
sales |idx_c_t |164, 1033949, 0x0000000CB76E|X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB76E |X,REC_NOT_GAP|
虽然事务隔离级别变了，但是语句的加锁没有改变，依然是对符合联合条件的索引条目和表数据加锁。
3.3.3 索引合并
同样是上面那条索引，索引合并时的加锁如下：
object_name|index_name |LOCK_DATA |LOCK_MODE |
-----------+---------------+-----------------------+-------------+
sales | | |IX |
sales |idx_cust |164, 0x0000000CB76E |X,REC_NOT_GAP|
sales |GEN_CLUST_INDEX|0x0000000CB76E |X,REC_NOT_GAP|
sales |idx_time |1033949, 0x0000000CB76E|X,REC_NOT_GAP|
在读已提交隔离级别下，语句采用索引合并时加锁的情况改善了很多，只对符合联合条件的索引条目和表数据加排他锁，因为使用了多个索引，加锁的数量要比联合索引多，不过，与可重复读相比，造成行锁的可能性大大降低，如不考虑从性能，和联合索引差不多了。
4 简单小结
总起来说，MySQL的索引合并使用场合比较多，在多个查询条件下，性能比联合索引差，MySQL默认的事务隔离级别下，锁住的索引和主表条目数量较大，在事务隔离级别为已提交的情况下，加锁和联合索引差不多。在考虑到性能，索引合并不适合用于数据库有大量并发更新的场合，但对于性能要求不高的场合则非常适用。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
