结论：避免分页查询加锁的关键是绕过深度扫描，用主键游标替代offset——因rr级别下limit配合where和order by会触发大范围next-key lock，而游标分页通过where id
直接说结论：避免分页查询加锁导致大范围行锁阻塞，核心不是“怎么少锁”，而是“根本别走深度扫描路径”——
LIMIT offset, size本身不加锁，但配合WHERE和ORDER BY后，InnoDB 在 RR 隔离级别下会按扫描范围上Next-key Lock，offset越大、索引越非唯一，锁住的间隙越多，其他事务更新 nearby 记录时就被堵死。为什么
LIMIT分页在 RR 级别下容易锁大片不是
LIMIT本身的问题，是 InnoDB 执行器在满足排序+过滤条件时的扫描逻辑触发了范围锁：
SELECT * FROM orders WHERE status = 1 ORDER BY created_at DESC LIMIT 10 OFFSET 200000—— 若status是非唯一索引，InnoDB 会扫描所有status = 1的索引项，并对每个匹配项及其右侧间隙加Next-key Lock- 即使只取 10 行，
OFFSET 200000意味着它得先定位到第 200001 条匹配记录，途中扫过的每一条、每一个间隙都被锁住- 主键排序也一样危险：
ORDER BY id DESC LIMIT 10 OFFSET 1000000会锁住从最小匹配 id 到目标位置之间整段主键区间- 没
ORDER BY或排序字段无索引？可能退化为全表扫描 +filesort，直接升级成表级锁风险用主键游标替代
OFFSET是最可靠解法把“跳过前 N 行”改成“从上次最后 ID 开始往后查”，彻底绕开深度扫描，自然避开大部分锁：
- 首次查：
SELECT * FROM orders ORDER BY id DESC LIMIT 10，拿到第 10 条的id（比如8000001）- 下一页查：
SELECT * FROM orders WHERE id —— 注意方向一致，<code>DESC就用，<code>ASC就用>- 必须确保排序字段是主键或带唯一索引，否则会出现漏数据或重复；若业务允许，可用
(id, created_at)复合唯一条件兜底- 前端/客户端需透传上一页末尾
id，不能靠页码反算 —— 页码是逻辑概念，id才是物理锚点批量更新分页也别用
OFFSET，改用主键区间切片很多人以为只有
SELECT ... FOR UPDATE才锁，其实UPDATE在范围条件匹配时同样触发Next-key Lock，且更隐蔽：
- 错误写法：
UPDATE orders SET status = 2 WHERE status = 1 LIMIT 5000—— 每次都从头扫，锁范围不可控，事务越跑越慢- 正确写法：
UPDATE orders SET status = 2 WHERE id > 10000 AND id —— 明确边界，仅锁该区间内涉及的索引节点和间隙- 推进方式：执行后查
SELECT MAX(id) FROM orders WHERE id > 10000 AND id ，下一轮用这个值作为新起点- 严禁在
WHERE中混用函数，如DATE(created_at)或UPPER(name)，索引失效后直接全表间隙锁临时降级隔离级别可快速缓解，但要清楚代价
READ COMMITTED下间隙锁基本不启用（仅外键/唯一约束检查时保留），对分页类操作更友好，但不是万能开关：
- 确认当前级别：
SELECT @@transaction_isolation- 会话级切换（推荐）：
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED- 不要改全局
innodb_default_isolation，影响面太大；只在明确接受“不可重复读”的场景下使用，比如后台批量状态翻转- 副作用真实存在：同一事务中两次查同个范围，第二次可能看到新插入的行 —— 对强一致性要求高的业务（如资金流水核对）不可用
真正难的不是换写法，而是让业务逻辑接受“游标分页”带来的弱一致性：新插入记录可能插在游标中间，导致“跳过”或“重复”。这不是 bug，是 B+ 树索引驱动查询的天然代价。如果业务必须支持任意跳页且强一致，那就得接受子查询延迟关联 + 覆盖索引的组合方案，而不是硬扛
OFFSET。
怎样在MySQL中避免分页查询加锁导致大范围行锁阻塞
胖涛吖_6275
2026-09-28
150人浏览
原创
