数据库 · 性能优化

MySQL 千万级数据深分页优化：从 10s 到 0.1s

JJokerChou 2026.06.15 约 12 分钟

一张 3000 万行的订单表，LIMIT 1000000, 20 居然要 10 秒。用户翻到第 50 页就卡死。本文复盘这次真实优化：从看懂执行计划，到延迟关联、游标分页，最终把深翻页压到 0.1 秒以内——并且讲清楚每种方案适用的边界。

1为什么深分页这么慢？

先看"慢"的 SQL：

SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 1000000, 20;   -- 翻到第 50 万页

MySQL 的执行逻辑是：

先在 created_at 索引上排序定位；
然后回表读取前 100 万 + 20 行的完整数据；
最后丢弃前 100 万行，只返回 20 行。

⚠️ 关键瓶颈：OFFSET 1000000 意味着 MySQL 必须扫描并丢弃 100 万行。翻得越深，丢弃越多，这是线性变慢，不是偶发。

2先用 EXPLAIN 看清真相

EXPLAIN SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000000, 20;

重点关注三列：

字段	含义	本次现象
type	访问类型	index（走了索引但仍是全索引扫）
rows	预估扫描行数	≈ 1000020（要扫百万行）
Extra	额外信息	Using filesort（还要排序）

结论：虽然走了索引，但 OFFSET 让"丢弃"成本无法避免。索引救不了深翻页，方案要从"怎么跳过前 N 行"入手。

3方案 A：延迟关联（子查询先取 ID）

核心思想：先只查索引拿 ID，再回表取需要的 20 行，避免回表百万行。

SELECT o.* FROM orders o
JOIN (
  SELECT id FROM orders
  ORDER BY created_at DESC
  LIMIT 1000000, 20        -- 覆盖索引里完成，只拿 id
) t ON o.id = t.id;

如果 (created_at, id) 是联合索引，子查询几乎只在索引里完成，回表仅 20 次。实测从 10s → 0.3s。

💡 前提：排序字段要有覆盖索引。延迟关联本质是"把回表成本从百万级降到常量级"。

4方案 B：游标分页（Cursor / Seek 分页）⭐

深翻页最好的解法往往是不翻页——用上一页最后一条记录的游标，改成"WHERE 游标之后"：

-- 上一页最后一条的 created_at 与 id
SELECT * FROM orders
WHERE (created_at < '2026-06-01 12:00:00')
     OR (created_at = '2026-06-01 12:00:00' AND id < 8842231)
ORDER BY created_at DESC, id DESC
LIMIT 20;

这把"跳过 100 万行"变成"从游标直接定位"，实测 0.05 ~ 0.1s。代价是：不支持"跳到第 50 页"，只能"上一页/下一页"。

💡 绝大多数业务（信息流、订单列表）只需要"加载更多"，游标分页完全够用，且性能与深度无关。

5方案 C：分区表 + 时间裁剪

若数据天然按时间分布，用分区表把范围查询收敛到单个分区：

ALTER TABLE orders
PARTITION BY RANGE (TO_DAYS(created_at)) (...);

-- 查询自动只在对应分区扫，跳过无关历史
SELECT * FROM orders
WHERE created_at >= '2026-06-01'
ORDER BY created_at DESC LIMIT 20;

6三种方案怎么选

方案	是否支持跳页	深翻性能	适用场景
延迟关联	✅ 支持	0.3s 级	后台需精确跳页
游标分页	❌ 仅前后翻	0.1s 级	前端信息流、移动端
分区表	✅ 配合	视分区	数据强时间分布

7顺手做的几件小事

只 SELECT 必要字段，避免 SELECT * 放大回表；
覆盖索引：(created_at, id) 让排序与定位都在索引内；
缓存热门前几页：90% 用户只看前 3 页；
count 异步化：总条数用缓存/估算，别每次实时 COUNT。

8小结

深分页的本质是"丢弃成本"。能避免丢弃（游标），就避免；不得不跳页（延迟关联），就缩小回表范围。优化的第一性原理永远是：先 EXPLAIN 看清扫描行数，再决定怎么砍掉不该扫的数据。