MySQL多列唯一键带OR条件查询为何触发next-key锁

InnoDB 只有在唯一索引做单点等值匹配、能明确确认最多只返回1条唯一记录的场景下，才会触发「仅加记录锁、不加间隙锁」的优化，这个优化的前提非常严格：必须是唯一索引的所有索引列都用AND连接做常量等值匹配，查询条件可以直接在索引上定位到唯一的一个点，不需要做范围扫描。

两个测试场景的加锁逻辑差异 #

• 场景1：c1=1 and c2=2
  该条件是联合唯一键unique_key_1的全列等值精确匹配，优化器可以直接在索引上做单点查询，明确知道最多只有1条符合条件的记录，完全满足官方文档描述的优化前提，因此只给匹配到的行加记录锁，不会加任何间隙锁，自然不会出现supremum pseudo-record相关的锁记录。
• 场景2：c1=1 and (c2=1 or c2=2)
  该条件中用OR连接c2列的两个等值条件，InnoDB的锁逻辑不会做语义拆分——不会把这个条件拆成两个独立的唯一键单点查询分别加锁，而是会将其识别为「c1=1前提下c2列的范围扫描」：扫描从c2的最小值开始，一直向后遍历直到找到第一个不满足条件的记录才会停止。
  由于测试表中c1=1的记录最大c2值为2，扫描遍历完(1,2)这条记录后，还需要继续向后检查是否存在其他符合条件的记录，会一直走到索引末尾的虚拟记录supremum pseudo-record才会停止，因此会按照范围扫描的加锁规则，给整个扫描路径覆盖的区间加next-key锁，最终就会观测到supremum pseudo-record上的锁。

补充说明：哪怕你把OR条件改写为c2 IN (1,2)，最终加锁结果也完全一致。InnoDB的加锁是跟着实际索引扫描路径走的，不会根据查询结果的语义做优化——只要查询条件不能直接定位到唯一索引上的单个点，哪怕逻辑上能确定所有匹配结果都是唯一记录，也会按照范围扫描的规则加next-key锁，覆盖扫描路径上的所有间隙。

完整测试SQL #

DROP TABLE `t`;

CREATE TABLE `t` (
  `c1` int,
  `c2` int,
  UNIQUE KEY `unique_key_1` (`c1`, `c2`)
) ENGINE=InnoDB;


insert into t (c1,c2) values (1,1);
insert into t (c1,c2) values (1,2);



-- 场景1
start transaction;

select * from t where c1=1 and c2=2 LOCK IN SHARE MODE;

SELECT 
    OBJECT_NAME, INDEX_NAME, LOCK_DATA
FROM
    performance_schema.data_locks
WHERE
    OBJECT_NAME = 't'
        AND INDEX_NAME = 'unique_key_1'
        AND LOCK_DATA = 'supremum pseudo-record';

commit;


-- 场景2
start transaction;

select * from t where c1=1 and (c2=1 or c2=2) LOCK IN SHARE MODE;

SELECT 
    OBJECT_NAME, INDEX_NAME, LOCK_DATA
FROM
    performance_schema.data_locks
WHERE
    OBJECT_NAME = 't'
        AND INDEX_NAME = 'unique_key_1'
        AND LOCK_DATA = 'supremum pseudo-record';

commit;

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