limit 1000000 慢到无法忍受？深分页优化方案

慢查询日志里躺了一条 8 秒的 SQL

DBA 每周会给我们发一份慢查询报表。上周的报表里，我们服务贡献了第一名：一条 8.2 秒的 SQL，平均执行 1200 次/天。

# Time: 2018-03-08T10:22:33.123456Z
# Query_time: 8.203451  Lock_time: 0.000123 Rows_sent: 20  Rows_examined: 1000020
SELECT id, order_no, user_id, amount, create_time
FROM t_order
WHERE status = 1
ORDER BY create_time DESC
LIMIT 1000000, 20;

我盯着 Rows_examined: 1000020 看了很久——就为了拿 20 条数据，扫了 100 万行。

为什么 limit 偏移量越大越慢

我一直以为 LIMIT 1000000, 20 是"直接跳到第 100 万行开始读"。不是的。MySQL 的执行方式是：先把前面 1000020 行全读出来，再扔掉前 1000000 行，返回剩下的 20 行。

而且这里还有个更隐蔽的开销。我们的索引是 idx_status_create_time(status, create_time)，二级索引的叶子节点只存索引列和主键 id，不存完整行。所以流程是：

1. 沿着二级索引扫 1000020 条，每拿到一条就用主键 id 回表去聚簇索引取完整行。
2. 取出来的完整行拿去判断、排序，最后丢掉前 100 万条。

也就是说发生了 100 万次回表，这 100 万次里绝大部分是白干的。我用 EXPLAIN 验证：

EXPLAIN SELECT id, order_no, user_id, amount, create_time
FROM t_order WHERE status = 1 ORDER BY create_time DESC LIMIT 1000000, 20;

rows 那一列基本就是扫描行数。作为对比，LIMIT 0, 20 时 rows 只有 20，耗时 1.2ms。

方案一：延迟关联（子查询先定位 id）

核心思路：既然回表是罪魁祸首，那就先在二级索引里把 20 个 id 找出来，只回表 20 次。

SELECT o.id, o.order_no, o.user_id, o.amount, o.create_time
FROM t_order o
INNER JOIN (
    SELECT id FROM t_order
    WHERE status = 1
    ORDER BY create_time DESC
    LIMIT 1000000, 20
) t ON o.id = t.id;

子查询里只 SELECT id，MySQL 走覆盖索引（Extra 显示 Using index），不用回表。外层只需要对 20 个 id 做主键关联。

实测：8.2s → 1.35s，提升 6 倍。EXPLAIN 里子查询那行的 rows 依然很大，但因为省掉了回表，实际 IO 少太多了。

方案二：书签记录（记住上一页的位置）

延迟关联还是扫了 100 万条索引。有没有办法跳过它们？有——如果能把 offset 换成一个确定的位置。

-- 第一页
SELECT id, order_no, amount, create_time
FROM t_order
WHERE status = 1
ORDER BY create_time DESC, id DESC
LIMIT 20;

-- 假设最后一条是 create_time='2018-03-01 09:30:11', id=882341
-- 下一页，用上一页的边界值作为起点
SELECT id, order_no, amount, create_time
FROM t_order
WHERE status = 1
  AND (create_time, id) < ('2018-03-01 09:30:11', 882341)
ORDER BY create_time DESC, id DESC
LIMIT 20;

这里用了 MySQL 的行值比较语法 (a, b) < (x, y)，效果和 a < x OR (a = x AND b < y) 一样但更简洁（老版本 MySQL 对这个语法的优化不好，5.7 里已经能正常走索引了）。加上 id 是为了处理 create_time 相同的情况，保证排序稳定、不重不漏。

实测：8.2s → 0.003s。是的，3 毫秒，和第一页一样快。因为这次直接通过索引定位到了起点，扫描行数就是 20。

代价也很明显：不能跳页。用户点"第 5000 页"这种需求就没法满足了，只能一页页往下翻。我们最后和产品沟通，后台订单列表改成了"上一页/下一页"，产品那边也同意了——毕竟真的没人会去点第 5000 页。

方案三：覆盖索引

如果查询的列恰好都在索引里，MySQL 连那 20 次回表都省了。

-- 建一个覆盖所有查询字段的联合索引
ALTER TABLE t_order ADD INDEX idx_cover(status, create_time, id, order_no, amount);

EXPLAIN SELECT id, order_no, amount, create_time
FROM t_order WHERE status = 1 ORDER BY create_time DESC LIMIT 1000000, 20;
-- Extra: Using where; Using index     ← Using index 表示覆盖索引生效

这条 SQL 的耗时从 8.2s 降到 2.1s。看起来不如前两个方案，但这个优化是通用的——它同时让第一页、前几页的查询也变快了。

缺点也实在：索引变宽了，占用的磁盘和内存都涨。我们这张表 2000 万行，加完这个索引多占了 1.4G 空间，写入时也多了维护索引的开销。所以我的原则是覆盖索引优先服务于高频查询，不要为了一条深分页 SQL 把索引搞得又宽又多。

三种方案怎么选

我们的最终方案：后台管理端用书签记录（体验最好，反正也不跳页）；对外开放的 OpenAPI 保留跳页能力，用延迟关联兜底，同时限制 offset 最大 10 万。

最后加一道防线

无论用哪个方案，都该给 offset 设个上限。我们在参数校验里加了：

if (pageNo * pageSize > 100000) {
    throw new BizException("不支持查询超过 10 万条之后的数据，请缩小筛选范围");
}

这一条拦截上线后，慢查询日志里那条 8 秒的 SQL 再没出现过。说到底，深分页性能问题的本质不是 SQL 写得不好，而是产品交互上就不该让用户翻到那么深。

参考

• 官方文档：database-sql
• Baeldung 上的相关实战
• StackOverflow 上的相关讨论