索引合并导致锁超时
May 7, 2023
本文示例数据表版本：mysql8.0.32。隔离级别：读已提交。
什么是索引合并？ #
索引合并指将多个索引的扫描结果合并起来，作为最终的扫描结果。 下面举例说明：
初始化表：
CREATE TABLE `t8` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`a` int NOT NULL DEFAULT '0',
`b` int NOT NULL DEFAULT '0',
`c` int NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_a` (`a`),
KEY `idx_b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
初始化数据：
INSERT INTO `rclx`.`t8`(`id`, `a`, `b`, `c`) VALUES (1, 1, 1, 1);
INSERT INTO `rclx`.`t8`(`id`, `a`, `b`, `c`) VALUES (2, 1, 2, 1);
INSERT INTO `rclx`.`t8`(`id`, `a`, `b`, `c`) VALUES (3, 1, 3, 1);
INSERT INTO `rclx`.`t8`(`id`, `a`, `b`, `c`) VALUES (4, 2, 1, 2);
INSERT INTO `rclx`.`t8`(`id`, `a`, `b`, `c`) VALUES (5, 2, 2, 3);
INSERT INTO `rclx`.`t8`(`id`, `a`, `b`, `c`) VALUES (6, 2, 3, 4);
INSERT INTO `rclx`.`t8`(`id`, `a`, `b`, `c`) VALUES (7, 3, 1, 1);
INSERT INTO `rclx`.`t8`(`id`, `a`, `b`, `c`) VALUES (8, 3, 2, 2);
INSERT INTO `rclx`.`t8`(`id`, `a`, `b`, `c`) VALUES (9, 3, 3, 3);
执行如下语句：
explain update t8 set c=11 where a = 1 and b=2\G;
结果如下：
*************************** 1. row ***************************
id: 1
select_type: UPDATE
table: t8
partitions: NULL
type: index_merge
possible_keys: idx_a,idx_b
key: idx_a,idx_b
key_len: 4,4
ref: NULL
rows: 1
filtered: 100.00
Extra: Using intersect(idx_a,idx_b); Using where
1 row in set, 1 warning (0.00 sec)
可以看到其同时使用了index_a,index_b两个索引。
索引合并导致死锁示例 #
当update语句使用到索引合并时，有可能会导致锁超时，甚至死锁。下面举个简单例子。 表结构及数据：如上。
事务1：
begin;
update t8 set c=11 where a = 1 and b=2;
事务2：
begin;
update t8 set c=12 where b=2 and a=3;
从表面上看，这两个事务并没有冲突，但实际上会发生阻塞。
下面来查看一下相关锁信息。
select * from performance_schema.data_locks\G;
结果如下：
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4859925960:1246:4454376392
ENGINE_TRANSACTION_ID: 1023931
THREAD_ID: 55
EVENT_ID: 59
OBJECT_SCHEMA: rclx
OBJECT_NAME: t8
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 4454376392
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4859925960:90:5:8:5369132056
ENGINE_TRANSACTION_ID: 1023931
THREAD_ID: 55
EVENT_ID: 59
OBJECT_SCHEMA: rclx
OBJECT_NAME: t8
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: idx_a
OBJECT_INSTANCE_BEGIN: 5369132056
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 3, 7
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4859925960:90:4:8:5369132400
ENGINE_TRANSACTION_ID: 1023931
THREAD_ID: 55
EVENT_ID: 59
OBJECT_SCHEMA: rclx
OBJECT_NAME: t8
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5369132400
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 7
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4859925960:90:6:3:5369133088
ENGINE_TRANSACTION_ID: 1023931
THREAD_ID: 55
EVENT_ID: 60
OBJECT_SCHEMA: rclx
OBJECT_NAME: t8
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: idx_b
OBJECT_INSTANCE_BEGIN: 5369133088
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: WAITING
LOCK_DATA: 2, 2
*************************** 5. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4859923584:1246:4454373320
ENGINE_TRANSACTION_ID: 1023930
THREAD_ID: 49
EVENT_ID: 86
OBJECT_SCHEMA: rclx
OBJECT_NAME: t8
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 4454373320
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 6. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4859923584:90:5:3:5369114136
ENGINE_TRANSACTION_ID: 1023930
THREAD_ID: 49
EVENT_ID: 86
OBJECT_SCHEMA: rclx
OBJECT_NAME: t8
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: idx_a
OBJECT_INSTANCE_BEGIN: 5369114136
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 1, 2
*************************** 7. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4859923584:90:4:3:5369114480
ENGINE_TRANSACTION_ID: 1023930
THREAD_ID: 49
EVENT_ID: 86
OBJECT_SCHEMA: rclx
OBJECT_NAME: t8
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5369114480
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 2
*************************** 8. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4859923584:90:6:3:5369114824
ENGINE_TRANSACTION_ID: 1023930
THREAD_ID: 49
EVENT_ID: 86
OBJECT_SCHEMA: rclx
OBJECT_NAME: t8
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: idx_b
OBJECT_INSTANCE_BEGIN: 5369114824
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 2, 2
8 rows in set (0.00 sec)
我们可以看到，事务2（3931）在等待index_b上的锁。
总结 #
虽然索引合并在某些情况下可以提高查询效率，但是在实际使用中，也会带来一些问题，比如死锁、锁等待超时等。所以，还是建议把update语句 where条件涉及到的字段都放到一个联合索引中，这样可以避免索引合并带来的问题。