如何通过优化SQL范围查询和索引设计，有效避免MySQL中Range查询引发的间隙锁冲突？

本文共计998个文字，预计阅读时间需要4分钟。

MySQL在可重复读（RR）隔离级别下，使用`SELECT ... FOR UPDATE`或带有范围条件的`UPDATE`语句时，InnoDB不仅锁定目标记录，还会锁定间隔（gap）——即尚未存在于记录中的索引区间。例如，主键是`id`，现有记录为`9, 10, 15, 20, 25`，执行`UPDATE t SET x=1 WHERE id BETWEEN 10 AND 20`，InnoDB实际加锁区间为`(9, 25)`。其中，`21`这个不存在于记录中的值就会落在间隔中，被锁定，导致其他事务插入`id=21`的记录被阻塞。

用 SELECT ... LOCK IN SHARE MODE 能避免间隙锁吗？

不能。只要语句走的是**范围扫描 + 当前行锁模式开启**（如 RR 隔离级），InnoDB 就会加间隙锁或 next-key 锁，和你是 FOR UPDATE 还是 LOCK IN SHARE MODE 无关。唯一能绕过间隙锁的方式是：让查询退化为**唯一等值查找**，且该值存在。

• ✅ 安全： SELECT * FROM t WHERE id = 15 FOR UPDATE —— 只锁单行（record lock）
• ❌ 不安全： SELECT * FROM t WHERE id >= 15 AND id —— 看似等值，但优化器可能走范围扫描，仍加 next-key 锁
• ✅ 可控： 加 FORCE INDEX 强制走唯一索引，并确保 EXPLAIN 显示 type=const 或 type=eq_ref

如何让 BETWEEN 查询不锁间隙？

核心思路是：**把范围查询拆成多个确定存在的等值点，再用 IN 替代**。前提是这些值能提前查出来、数量可控（一般 ≤ 几百），且业务允许“先查后更”两阶段操作。

例如：想更新所有 status = 'pending' 且 created_at 在某时间范围内的订单，但又不想锁住未来可能插入的时间间隙：

SELECT id FROM orders WHERE status = 'pending' AND created_at BETWEEN '2024-01-01' AND '2024-01-31' ORDER BY id;

拿到结果集后，再用 UPDATE orders SET status='processing' WHERE id IN (101,102,105,...) —— 此时每个 id 都是明确存在的主键值，只加 record lock，无间隙锁。

• ⚠️ 注意：如果 IN 列表超 1000 项，MySQL 可能放弃使用索引，改用全表扫描，反而更慢更重
• ⚠️ 必须确保两次查询之间数据未被删改，否则出现「幻读」或更新丢失，需配合应用层幂等或重试逻辑
• ? 更稳妥的做法：在 created_at 上建联合索引 (status, created_at, id)，让范围扫描尽可能窄，缩小 gap 锁影响范围

哪些索引设计会让间隙锁更“痛”？

间隙锁的粒度由**索引结构和扫描方式**决定，不是由 WHERE 条件本身决定的。最典型的陷阱是：在非唯一、非覆盖的二级索引上做范围查询。

• ❌ 危险组合：INDEX (category)（非唯一），执行 UPDATE t SET price=99 WHERE category = 'book' —— 会锁住整个 category='book' 对应的所有索引间隙，包括之后插入的新 book 记录
• ✅ 改进：改为 INDEX (category, id)，并显式带上主键等值条件：WHERE category = 'book' AND id > 1000，让优化器能利用索引有序性，减少扫描宽度
• ✅ 更优：若业务常按 category 批量更新，考虑加 INDEX (category, updated_at) 并用 updated_at 做分页条件，避免全索引扫描

真正难调的不是 SQL 写法，而是你不知道哪条看似简单的 UPDATE 正在悄悄锁住下一个小时要插入的 500 行——得靠 SELECT * FROM performance_schema.data_locks 结合 INFORMATION_SCHEMA.INNODB_TRX 实时看锁住了什么。