千万级数据下，MySQL 分页优化的正确姿势

在业务系统中，分页查询是最常见的功能之一。但当数据量攀升到千万级时，你会发现传统的 LIMIT offset, pageSize 变得奇慢无比，越往后翻页越慢，甚至拖垮数据库。

本文将带你层层拆解深分页的性能痛点，并给出从原理到实践的终极优化方案。

一、传统分页为什么慢？

一个经典的查询：

sql

SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

当 offset 为 100 万时，MySQL 实际执行了以下步骤：

1. 从聚簇索引中扫描前 100 万零 10 行数据；

2. 丢弃掉前 100 万行；

3. 只返回最后 10 行。

问题就出在那被丢弃的 100 万行。 它们照样要经过索引扫描、读取数据，耗费大量 I/O 和 CPU，却对结果毫无贡献。offset 越大，需要丢弃的行越多，查询呈线性恶化。

结合之前说的回表知识，如果 SELECT * 包含非索引列，这 100 万行还要逐一回表读取完整记录，更加雪上加霜。

二、方案对比总览

下面详细拆解两种最主流的优化方案。

三、方案一：延迟关联（子查询优化）

核心思路

利用覆盖索引，先快速定位到需要的主键，再用主键回表批量取数据。整个过程只对最终 10 条数据做回表，丢弃的 100 万行全是轻量的索引扫描。

原始慢查询

sql

SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

优化后

sql

SELECT o.* 
FROM orders o
INNER JOIN (
    SELECT id FROM orders 
    ORDER BY id 
    LIMIT 1000000, 10
) AS tmp ON o.id = tmp.id;

执行流程：

• 子查询 SELECT id FROM orders ORDER BY id LIMIT 1000000,10 只扫描 id 主键索引，不涉及回表，丢弃 100 万行的代价极低；

• 外层只对最终 10 个 id 进行聚簇索引的回表，取出完整行，性能飞跃。

性能实测（模拟数据）

通过 EXPLAIN 会看到子查询的 Extra 显示 Using index，说明覆盖索引生效。

四、方案二：游标分页（基于索引的下一页）

当产品形态为滚动加载或“上一条/下一条”，不需要随意跳页时，游标分页是最佳选择。

核心思路

记住上一页最后一条数据的游标（通常是排序字段的值），下一页直接从此处开始，彻底消除 offset。

原始慢查询

sql

SELECT * FROM feed WHERE user_id=123 ORDER BY create_time DESC LIMIT 1000000, 10;

优化方案

1. 前端传回上一页最后一条的 create_time 和 id（id 用于排重）；

2. 查询改为：

   sql

   SELECT * FROM feed 
   WHERE user_id = 123 
     AND (create_time < '上次时间' OR (create_time = '上次时间' AND id < '上次id'))
   ORDER BY create_time DESC, id DESC 
   LIMIT 10;

3. 利用复合索引 (user_id, create_time, id)，直接定位到分页起始点，扫描 10 行即返回。

这样做不仅没有 offset 丢弃行，还充分利用索引范围查找，性能恒定在毫秒级，数据量增长几乎无影响。

注意事项

• 必须告知产品不能直接跳页，只能无限下拉或点击“加载更多”。

• 前端需要改版，但换来的是极致的性能和用户体验。

五、方案三：业务法则限制深翻

大部分用户根本不会翻到第 1000 页。我们可以从业务上“截断”深分页：

• 搜索结果最多展示前 100 页；

• 管理后台提供精准的时间范围、状态等筛选条件，快速缩小数据范围后再分页；

• 对于需要深度遍历的场景（如导出），直接用流式查询或离线任务，避免在线暴露深分页。

一条铁律：永远不要信任用户输入的页码，后端要做好边界控制。

六、当 MySQL 无法胜任时

当数据量达到数亿、查询场景极其复杂（全文搜索、多维度筛选排序）时，单纯依赖 MySQL 分页优化已是杯水车薪，此时应考虑：

• Elasticsearch：支持天然分布式的深分页（search_after）和聚合分析。

• TiDB / OceanBase：分布式数据库，天然水平扩展。

• Redis 缓存热点页：例如首页、高访问页直接缓存结果。

七、总结

面对千万级数据分页，核心理念就是一句话：用尽量小的代价定位起始点，只取你需要的那几条数据。

• 能接受无限滚动 -> 游标分页，一劳永逸

• 必须支持跳页 -> 延迟关联+覆盖索引，大幅降低回表损耗

• 业务允许浅分页 -> 直接限制最大页码

理解了聚簇索引、回表和覆盖索引之后，设计出高性能分页方案就是水到渠成的事。希望这篇对你有用，欢迎在实际业务中实践，用 EXPLAIN 对比前后的惊艳差距。