分页查询易触发大面积行锁，因limit配合where和order by时innodb会使用next-key lock锁定扫描范围；offset越大、索引越非唯一，锁范围越广；主键游标分页可有效规避该问题。
分页查询为什么容易触发大面积行锁
MySQL 的 LIMIT offset, size 分页语句本身不加锁，但一旦和 ORDER BY、WHERE 一起用在高并发更新场景下，InnoDB 就可能按「范围查询」逻辑上 Next-key Lock —— 这才是性能卡顿和死锁的根源。
比如执行：SELECT * FROM orders WHERE status = 1 ORDER BY created_at DESC LIMIT 10 OFFSET 200000，即使你没写 FOR UPDATE，只要该查询走的是非唯一索引（如 status），InnoDB 就会扫描满足 status = 1 的所有索引项，并对每个匹配记录及其右侧间隙加 Next-key Lock。offset 越大，扫描越深，锁住的间隙越多，其他事务更新附近记录时就容易被堵住。
- 聚集索引上的
ORDER BY id+LIMIT：锁范围相对可控，但若OFFSET极大，仍会扫描并锁住大量主键区间 - 普通索引（非唯一）+
WHERE+ORDER BY：最危险，InnoDB 默认对整个扫描路径加 Next-key Lock，哪怕只取 10 行 - 没
ORDER BY或排序字段无索引：可能退化为全表扫描 + 文件排序，锁表风险陡增
用主键游标替代 OFFSET 是最有效的避锁方案
把「跳过前 N 行」改成「从上次最后 ID 开始往后查」，就能绕开深度扫描，自然避开大部分锁。这不是妥协，而是让查询真正符合索引驱动的 B+ 树定位逻辑。
例如原语句：SELECT * FROM orders ORDER BY id DESC LIMIT 10 OFFSET 200000
优化后：SELECT * FROM orders WHERE id （假设上一页最后一条是 <code>id = 8000001）
- 必须确保排序字段是主键或有唯一性保证，否则会出现漏数据或重复
WHERE id 要配合 <code>ORDER BY id DESC，方向必须一致；若用 ASC，则改用id > ?- 首次加载需单独查最大 ID，后续翻页靠客户端/前端透传上一页末尾 ID，不能依赖页码计算
- 注意时序问题：新插入记录可能插在游标中间，导致“跳过”或“重复”，业务上需接受弱一致性，或加时间戳兜底
哪些分页写法会让锁范围失控
不是所有带 LIMIT 的语句都安全。以下写法在并发更新频繁时极易扩大锁粒度，甚至升级成间隙锁风暴：
-
SELECT ... WHERE non_unique_idx_col = ? ORDER BY non_unique_idx_col LIMIT ...：InnoDB 对整段等值匹配区间加 Next-key Lock，哪怕只取 1 行 -
SELECT ... FOR UPDATE LIMIT 10 OFFSET 100000：OFFSET 大时先扫 100010 行再加锁，期间所有被扫描的索引记录和间隙都被锁定 -
SELECT ... WHERE created_at > '2025-01-01' ORDER BY id LIMIT ...：范围条件 + 排序字段无索引 → 文件排序 + 全扫描，锁住临时结果集对应的所有主键行 - 使用
UUID主键且未建二级索引加速排序：B+ 树无法按时间局部性组织，每次分页都接近随机 IO，锁分散且难以预测
验证锁行为：别猜，用 INFORMATION_SCHEMA.INNODB_TRX 和 sys.innodb_lock_waits
线上遇到分页慢或阻塞，光看执行计划没用。得直接看当前谁在持有什么锁、谁在等什么锁。
快速检查命令：
SELECT trx_id, trx_state, trx_started, trx_query FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_state = 'LOCK WAIT';
再关联查等待详情：
SELECT * FROM sys.innodb_lock_waits;
- 重点关注
blocking_trx_id和sql_kill_blocking_connection字段，可快速定位源头 SQL - 若发现大量
trx_query都是类似SELECT ... LIMIT 200000, 20，基本就是分页设计引发的锁竞争 - 用
SHOW ENGINE INNODB STATUS\G查更细的锁信息，但输出杂乱，建议优先用sysschema 视图
真正的难点不在怎么写 SQL，而在于分页背后隐含的「数据移动性」——新老数据混排、删除导致空洞、主键不连续……这些都会让游标方案变脆弱。上线前务必在压测环境模拟真实写入节奏跑一跑分页链路。
