MySQL 深分页实测：LIMIT 500000,20 要 0.8 秒，游标分页 0.3ms；但 (created_at, id) > (?, ?) 这种写法照样全扫

最简单的情况：SELECT * FROM orders ORDER BY id LIMIT M,20，按主键顺序扫。EXPLAIN ANALYZE 里 Index scan on orders using PRIMARY 的 actual rows 恰好是 M+20，SHOW SESSION STATUS 里 Handler_read_next 也是 M+19。也就是说，偏移量多大，就读多少行，再由 Limit/Offset 节点丢掉。

主键是聚簇索引，读一行就是读到完整的行，所以这已经是最便宜的深分页了：90 万行 179–192ms，大约每 10 万行 20ms。

业务里更常见的是 WHERE status = 1 ORDER BY created_at LIMIT 500000,20。EXPLAIN 显示 type=ref、key=idx_status_created、Extra 为空（没有 filesort），看起来很健康；EXPLAIN ANALYZE 才看出代价：Index lookup on orders using idx_status_created (status=1) actual rows=500020，三次 825 / 716 / 868ms。

同样读 50 万行，比主键顺序（约 100ms）慢 7–8 倍。原因是 SELECT * 要的列不在二级索引里，InnoDB 每从 idx_status_created 读一行，都要拿 id 回聚簇索引取整行，而这 500000 次回表的结果最后全被丢掉。

我先怀疑优化器选错了索引，加了 FORCE INDEX(idx_status_created)，计划一模一样，626 / 709ms，说明不是选索引的问题。偏移量小的时候完全看不出来：LIMIT 1000,20 只要 1.39 / 1.65ms，这也是很多列表页上线时没问题、数据量涨上来后被人一路翻到很后面才暴露的原因。

写法：SELECT o.* FROM orders o JOIN (SELECT id FROM orders WHERE status = 1 ORDER BY created_at, id LIMIT 500000, 20) t ON t.id = o.id ORDER BY o.created_at, o.id。子查询只要 id，而 InnoDB 二级索引的叶子节点本来就带主键，所以计划里是 Covering index lookup，不回表；外层只对这 20 个 id 做 Single-row index lookup on o using PRIMARY（loops=20）。

结果 71.6–77.1ms，比原来快 10 倍左右，但它仍然读了 500020 行索引项（Handler_read_next 500019），页数越往后还是越慢，只是每行变便宜了。我的取舍（经验判断）：前端必须支持「跳到第 N 页」、又不能改接口时，用延迟关联；能改成「下一页」的，直接上游标。

注意外层要再写一遍 ORDER BY：派生表 JOIN 回来的顺序没有保证，本次计划里外层多了一个 Sort（20 行，可以忽略）。

游标分页的思路是：前端把上一页最后一行的 (created_at, id) 带回来，下一页从它后面开始读 20 行，不管翻到第几页都只读 20 行。我先写的是最简洁的行构造器：WHERE status = 1 AND (created_at, id) > ('2026-01-03 21:31:04', 535661) ORDER BY created_at, id LIMIT 20。

结果 678 / 719ms，脚本复跑 705ms。EXPLAIN ANALYZE 里是 Index lookup on orders using idx_status_created (status=1)，外面套一层 Filter: ((orders.created_at, orders.id) > (...))，actual rows=500020：只用上了 status = 1 这一个等值前缀，然后从头一行行读、一行行过滤，直到攒够 20 行。

MySQL 8.0 官方文档《Row Constructor Expression Optimization》写的正是这种情况：行构造器没有覆盖索引前缀时（这里前面还有个 status = 1），优化器只会用到前面的列，建议改写成等价的非行构造器表达式。改成 created_at > ? OR (created_at = ? AND id > ?) 之后，计划变成 Index range scan on orders using idx_status_created over (status = 1 AND created_at = … AND 535661 < id) OR (status = 1 AND … < created_at)，只读 20 行，0.28–0.30ms。展开写法取到的 20 个 id 和 LIMIT 500000,20 取到的完全一致（两边 md5 相同）。

这一点和 PostgreSQL 不同：我在同一台机器的 PostgreSQL 17.11 测试库上建 (created_at, id) 索引后验证，(created_at, id) > (…) 直接成为 Index Cond，Index Scan 只读 20 行、0.18ms。从 PostgreSQL 迁到 MySQL、或者两边共用一套 ORM 分页代码的，最容易踩到。

另一种常见写法是游标只记时间：WHERE status = 1 AND created_at > ? ORDER BY created_at LIMIT 20。问题在于排序列有重复：如果一页正好在某一秒的中间截断，这一秒剩下的行在下一页会因为 created_at > ? 被整体跳过。

我写了两个存储过程，从头到尾把 status = 1 的 599996 行翻一遍、数一数拿到多少行。每页 20 行时只拿到 589810 行（漏 10186 行），每页 100 行时拿到 590051 行（漏 9945 行）；游标带上 id、用上面的展开写法，每页 20 行和 100 行都正好拿到 599996 行。本次 status=1 平均每个时间值约 2.5 行，真实业务里批量导入、定时任务写入的同一时间戳更多，漏得会更多。

排序列本身唯一（比如自增 id、雪花 id）时不需要再加列；否则就把主键放在排序和游标的最后一列，索引 (status, created_at) 后面隐含的就是主键 id，不用额外建索引。

只读 SQL 4 段，只查 performance_schema 和 information_schema。第 1 段在 events_statements_summary_by_digest 里找带偏移量的 LIMIT，按 SUM_ROWS_EXAMINED 排序，并算 examined_per_sent（读的行数 ÷ 返回的行数）；第 2 段找用行构造器写游标条件的语句；第 3 段列出每张表的索引列顺序；第 4 段列表的大概行数和体积。

测试库上的输出：LIMIT 500000,20 那条语句 rows_examined 500020、rows_sent 20、examined_per_sent 25001；行构造器那条 examined_per_sent 同样是 25001，avg 573ms。

踩到的小坑：digest 会把 LIMIT 500000, 20 规整成 LIMIT ?, ...，偏移量的大小在 digest 文本里看不到，只能看 examined_per_sent；另外把深分页包在派生表里（例如延迟关联、SELECT … FROM (SELECT … LIMIT 500000,20) a）时，本次 digest 记的 rows_examined 只有外层的 20 或 60 行，子查询读的 50 万行没算进去，这类语句要看 avg_ms，不能只看 examined_per_sent。