InnoDB非主键索引小于查询为何使用Next-Key Lock而非间隙锁？

环境信息 #

• 内核版本：5.15.133.1-microsoft-standard-WSL2
• MySQL服务器版本：8.0.33-0ubuntu0.22.04.2
• 事务隔离级别：REPEATABLE-READ（可重复读）

表结构（DDL） #

CREATE TABLE `test` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `uk` bigint DEFAULT NULL,
  `index` bigint DEFAULT NULL,
  `indexless` bigint DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk` (`uk`) USING BTREE,
  KEY `index` (`index`) USING BTREE
) ENGINE=InnoDB;

测试数据 #

mysql> select * from test;
+----+------+-------+-----------+
| id | uk   | index | indexless |
+----+------+-------+-----------+
|  1 |    1 |     1 |         1 |
|  5 |    5 |     5 |         5 |
| 10 |   10 |    10 |        10 |
| 15 |   15 |    15 |        15 |
+----+------+-------+-----------+

插入语句：

INSERT INTO `test`.`test`(`id`, `uk`, `index`, `indexless`) VALUES (1, 1, 1, 1);
INSERT INTO `test`.`test`(`id`, `uk`, `index`, `indexless`) VALUES (5, 5, 5, 5);
INSERT INTO `test`.`test`(`id`, `uk`, `index`, `indexless`) VALUES (10, 10, 10, 10);
INSERT INTO `test`.`test`(`id`, `uk`, `index`, `indexless`) VALUES (15, 15, 15, 15);

测试步骤与锁结果 #

1. 非主键索引查询 #

执行：

begin; SELECT * FROM test WHERE `index` < 6 FOR SHARE;

或

begin; SELECT * FROM test WHERE uk < 6 FOR SHARE;

锁信息：

mysql> select LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------+---------------+-------------+-----------+
| LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------+---------------+-------------+-----------+
| TABLE     | IS            | GRANTED     | NULL      |
| RECORD    | S             | GRANTED     | 1, 1      |
| RECORD    | S             | GRANTED     | 5, 5      |
| RECORD    | S             | GRANTED     | 10, 10    |
| RECORD    | S,REC_NOT_GAP | GRANTED     | 1         |
| RECORD    | S,REC_NOT_GAP | GRANTED     | 5         |
+-----------+---------------+-------------+-----------+

2. 主键索引查询 #

执行：

begin; SELECT * FROM test WHERE id < 6 FOR SHARE;

锁信息：

mysql> select LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------+-----------+-------------+-----------+
| LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+-----------+-------------+-----------+
| TABLE     | IS        | GRANTED     | NULL      |
| RECORD    | S         | GRANTED     | 1         |
| RECORD    | S         | GRANTED     | 5         |
| RECORD    | S,GAP     | GRANTED     | 10        |
+-----------+-----------+-------------+-----------+

其中5-10范围被间隙锁锁定。

问题解答 #

在REPEATABLE-READ隔离级别下，InnoDB默认用**Next-Key Lock（记录锁+间隙锁）**防止幻读，但主键索引和非主键索引的锁优化逻辑存在差异：

1. 主键索引的间隙锁优化 #

主键是聚簇索引，且具备唯一性。当执行id <6这类范围查询时：

• InnoDB明确知道最后一个匹配的主键是5，下一个主键是10
• 主键的唯一性决定了，新插入的id即使落在5-10之间，也不会被当前事务的重复查询读到（RR级别的快照读基于事务快照，加锁读仅锁定存在的匹配记录）

因此InnoDB会把Next-Key Lock降级为间隙锁（仅锁5-10的间隙，不锁10这条记录），对应锁信息里的S,GAP模式。

2. 非主键索引的Next-Key Lock逻辑 #

不管是普通非主键索引（index）还是唯一非主键索引（uk），InnoDB的处理逻辑是：

• 非主键索引的叶子节点存储「索引值+主键值」（比如index索引的叶子节点是(index值, id)），用来唯一标识索引条目
• 执行index <6或uk <6这类范围查询时，InnoDB需要先通过非主键索引定位记录，再回表到主键索引获取完整数据。为彻底防止幻读，不仅要锁定匹配的索引条目（1,1和5,5），还要锁定到下一个索引条目（10,10）的整个范围——这就是Next-Key Lock
• 锁信息里的LOCK_DATA:10,10对应下一个索引条目，InnoDB会对这个条目加S锁（包含记录本身和前面的间隙），避免其他事务修改该索引条目或在5-10的间隙插入新的索引值（比如index=6），导致当前事务再次查询时出现幻读

简单来说，非主键索引需要维护索引与主键的关联关系，必须通过完整的Next-Key Lock保证范围查询的一致性；而主键索引因为是聚簇且唯一，可优化为间隙锁。

内容的提问来源于stack exchange，提问作者ender