后台列表页翻到第 500 页突然卡住，接口从几十毫秒变成几秒，第一反应往往是"数据库慢了"。真正的原因通常是 LIMIT 10000, 20 这行 SQL：它要求 MySQL 先读出前 10020 行、丢掉前 10000 行，再返回 20 行。翻得越深，丢得越多，成本与 offset 成正比。这篇把深分页、排序执行路径和总数统计三条线拆开。以下按 MySQL 8.0 + InnoDB 核对。
1. LIMIT offset, size 到底做了什么
-- 朴素写法：offset 越大，扫描与丢弃的行越多
SELECT id, user_id, amount, created_at
FROM t_order
WHERE status = 1
ORDER BY id DESC
LIMIT 100000, 20;
EXPLAIN SELECT id FROM t_order WHERE status = 1 ORDER BY id DESC LIMIT 100000, 20;
-- type=ref key=idx_status rows≈N Extra=Using index; Using where (8.0 会显示 Backward index scan)
执行过程分三步：按索引顺序取出满足条件的行 → 逐行丢弃前 offset 行 → 返回剩下的 size 行。被丢弃的行如果不在覆盖索引里，还要为每一行做一次回表，等于做了十万次无用的随机读。
| 分页方式 | 每页需要处理的行 | 能否跳页 | 结果一致性 |
|---|---|---|---|
LIMIT offset, size |
offset + size | 能，任意页 | 翻页期间新数据插入会导致行漂移 |
| 延迟关联（覆盖索引先取主键） | offset + size（但只扫索引） | 能 | 同上 |
游标 / search_after |
size | 不能，只能上一页/下一页 | 稳定，锚点之后不重不漏 |
| 限制最大页数 | 有上限 | 能，但只有前 N 页 | 同上 |
2. 三种优化：延迟关联、游标、限制页数
2.1 延迟关联：让丢弃发生在索引里
先只在覆盖索引上翻页拿到主键，再回表取这 20 行的完整字段：
SELECT o.id, o.user_id, o.amount, o.created_at
FROM t_order o
JOIN (
SELECT id FROM t_order
WHERE status = 1
ORDER BY id DESC
LIMIT 100000, 20
) t ON o.id = t.id;
子查询走 idx_status (status, id) 或主键，Extra 应为 Using index，十万次丢弃都发生在索引页上，只有最终 20 行回表。代价是 SQL 变复杂，且要求排序列在主键或某个索引里能形成有序前缀。
2.2 游标分页：把 offset 换成"锚点"
-- 第一页
SELECT id, created_at, amount FROM t_order
WHERE status = 1
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- 后续页：上一页最后一行的 (created_at, id) 作为锚点
SELECT id, created_at, amount FROM t_order
WHERE status = 1
AND (created_at, id) < ('2026-11-01 10:00:00', 88421)
ORDER BY created_at DESC, id DESC
LIMIT 20;
前提有三个：排序键唯一且稳定（用 (created_at, id) 这种组合保证不重不漏）、必须有索引支撑这个有序扫描（(status, created_at, id)）、业务允许不能跳页。第三点常被忽略——后台运营常需要"跳到第 30 页"，这时游标就不适用了。
2.3 限制最大页数
电商前台、日志列表这类场景，用户翻到第 1000 页本身没有意义。把 offset 上限卡在比如 5000，超出返回"请用筛选条件缩小范围"。这不是技术优化，是产品约束，但它比任何 SQL 改写都有效。
3. 排序的两条执行路径
ORDER BY 只有两种命运：顺着索引读，或者把结果集取出来自己排（Using filesort）。
EXPLAIN SELECT id, amount FROM t_order
WHERE status = 1 ORDER BY created_at DESC;
-- Extra: Using filesort → created_at 不在 idx_status 的有序前缀里
ALTER TABLE t_order ADD KEY idx_status_created (status, created_at);
EXPLAIN SELECT id, amount FROM t_order
WHERE status = 1 ORDER BY created_at DESC;
-- key=idx_status_created Extra=NULL → 顺索引读，无需排序
Using filesort 不一定用磁盘，它是"额外排序"的统称。内部有两种策略：
| 策略 | 触发条件 | 数据在哪 | 特点 |
|---|---|---|---|
| 全字段排序（原表模式） | 需要返回的字段总长度小于 max_length_for_sort_data |
sort_buffer |
一次排序即得完整行，内存占用大 |
| rowid 排序（优先队列） | 行宽超过阈值 | sort_buffer 存 (排序键, rowid) |
排完还要按 rowid 回表取字段，多一轮随机读 |
sort_buffer_size 不够时，排序会落盘做归并：把数据切成若干块分别排好写临时文件，再多路归并。SHOW STATUS LIKE 'Sort%' 里的 Sort_merge_passes 持续增长，就是落盘的信号：
SHOW VARIABLES LIKE 'sort_buffer_size';
SHOW VARIABLES LIKE 'max_length_for_sort_data';
SHOW STATUS LIKE 'Sort_%';
-- Sort_scan / Sort_range / Sort_rows / Sort_merge_passes
调大 sort_buffer_size 是每连接分配，几百个并发连接同时排序会把内存吃光，这不是一个可以随手调大的参数。
4. ORDER BY 用上索引的三个条件
| 条件 | 说明 | 反例 |
|---|---|---|
| 顺序一致 | WHERE 的等值列必须是索引最左前缀 |
ORDER BY b 而索引是 (a, b) 且 a 无等值条件 |
| 方向一致 | 多列排序的升降方向要同向，或与 8.0 的降序索引匹配 | ORDER BY a ASC, b DESC 配普通 (a, b) |
| 字段是索引前缀 | 排序字段要构成索引的连续前缀 | ORDER BY amount 而索引是 (status, created_at) |
ORDER BY 子句 |
索引 (status, created_at, id)，WHERE status=1 |
是否 filesort |
|---|---|---|
created_at DESC |
前缀匹配 | 否 |
created_at DESC, id DESC |
双列同向 | 否 |
created_at ASC, id DESC |
方向混合 | 是（除非建降序索引） |
amount DESC |
不在索引里 | 是 |
id DESC（无 status 条件） |
缺最左等值列 | 是，走主键反向扫则否 |
8.0 支持 KEY idx (a ASC, b DESC) 这类降序索引，MySQL 5.7 只能靠反向扫描整体颠倒，混合方向排序会退化成 filesort。需要混合方向排序时，要么建降序索引，要么在 SQL 层统一方向再在应用里反转。
5. 随机取 N 条：四种写法与代价
-- 1. 直接随机排序：全表算一遍随机数再排序
SELECT id FROM t_order ORDER BY RAND() LIMIT 10;
-- 2. 主键区间随机：要求 id 连续且无大空洞
SELECT id FROM t_order
WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM t_order)))
ORDER BY id LIMIT 10;
-- 3. 先 COUNT 再随机 offset：offset 深了照样慢
SELECT id FROM t_order LIMIT FLOOR(RAND() * (SELECT COUNT(*) FROM t_order)), 10;
-- 4. 应用层取随机 id 列表再批量查（推荐给"每日一题"这类场景）
SELECT id FROM t_order WHERE id IN (?, ?, ?, ...);
| 写法 | 扫描量 | 随机性 | 适用 |
|---|---|---|---|
ORDER BY RAND() |
全表 + 临时表排序 | 均匀 | 小表、离线任务 |
| 主键区间 | 索引范围扫 10 行 | 有空洞时偏斜 | id 连续且删除少的表 |
| COUNT + 随机 offset | 一次 COUNT + 深偏移 | 均匀 | 中小表 |
| 应用层随机 id | N 次主键等值 | 取决于取 id 的方式 | 高频接口 |
6. 分页总数：COUNT(*) 与它的替代品
列表页要显示"共 12,345 条"，就得算总数。COUNT(*) 在 InnoDB 上必须扫一个最小的二级索引（没有则扫聚簇索引），无法像 MyISAM 那样直接读元数据。
-- 精确值：与列表查询同条件，最贵但最准
SELECT COUNT(*) FROM t_order WHERE status = 1 AND created_at >= '2026-01-01';
-- 估算值：EXPLAIN 的 rows 是采样估算，误差可能很大
EXPLAIN SELECT * FROM t_order WHERE status = 1;
-- 近似值：SHOW TABLE STATUS 的 Rows 也是估算
SHOW TABLE STATUS LIKE 't_order';
| 取法 | 精度 | 成本 | 适用 |
|---|---|---|---|
COUNT(*) 同条件 |
精确 | 随扫描行数线性增长 | 数据量可控、要求准 |
COUNT(*) 加缓存 |
精确（有滞后） | 一次计算 + 缓存读 | 列表页高频、允许分钟级滞后 |
EXPLAIN 的 rows |
估算，偏差可能数倍 | 极低 | 只用于"共约 N 条"文案 |
| 单独维护计数表 | 精确 | 写入时维护 | 状态组合少、写少读多 |
总数缓存的坑在于失效条件与列表查询不一致：缓存 key 只带了 status，但列表还按 created_at 过滤，数字就对不上；或者缓存 TTL 是 5 分钟，用户点进第二页看到的总数变了。稳妥做法是缓存 key 与查询条件一一对应，并在写入路径上主动失效，而不是只靠 TTL。
7. 排错：五种分页与排序的异常
| 现象 | 原因 | 处理 |
|---|---|---|
| 前几页快、后面越来越慢 | offset 线性扫描 + 回表 |
延迟关联或游标分页，或限制最大页数 |
Extra=Using filesort 且 Sort_merge_passes 涨 |
排序字段不在索引有序前缀 | 建 (等值列, 排序列) 联合索引 |
| 分页结果出现重复或漏行 | 排序键不唯一，翻页期间数据变动 | 排序键补主键；改游标分页 |
COUNT(*) 与列表条数不一致 |
两者条件不同（少了过滤条件或软删标记） | 抽出同一段 where 条件复用 |
| 导出接口把库拖垮 | 用 LIMIT offset 循环导出全表 |
改按主键区间切批，WHERE id > ? ORDER BY id LIMIT N |
ORDER BY RAND() 打满 CPU |
全表随机排序 | 改应用层随机 id 或主键区间 |
8. 边界：什么情况该换思路
| 场景 | 更合适的做法 | 理由 |
|---|---|---|
| 需要任意跳页 + 数据量大 | 搜索引擎（ES search_after / 快照分页） |
关系库的 offset 成本无法消除 |
| 全量导出 | 主键区间分批 + 流式读取 | 避免长事务与大 offset |
| 万亿级列表 | 预计算游标、物化视图 | 实时排序代价过高 |
| 多字段复杂排序 + 高并发 | 冗余排序列 / 搜索索引 | 索引组合爆炸 |
| 只关心总数趋势 | 近似统计 + 定时任务 | 精确 COUNT 成本高 |
9. 自测
LIMIT 100000, 20的成本为什么与 offset 成正比？被丢弃的行有没有可能不产生 IO？- 延迟关联把丢弃操作放到了哪里？它为什么要求排序列在索引里？
- 游标分页为什么必须用
(created_at, id)这种组合键而不是单个created_at？ Using filesort的两种策略分别什么时候触发？Sort_merge_passes增长说明什么？ORDER BY a ASC, b DESC配普通联合索引为什么排序失效？8.0 怎么解决？- 总数缓存与列表查询条件不一致时，会出现什么现象？怎么保证一致？