索引合并导致锁超时

本文示例数据表版本：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条件涉及到的字段都放到一个联合索引中，这样可以避免索引合并带来的问题。

本文由 mdnice 多平台发布