MySQL深分页优化：解决LIMIT大偏移量性能瓶颈

问题分析：为什么 LIMIT 越大越慢？

通常的分页 SQL 如下所示：

SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 100, 10;

或者不带过滤条件的简单分页：

SELECT * FROM `t1` ORDER BY id DESC LIMIT 100, 10;

在 InnoDB 引擎中，LIMIT offset, N 的含义是：扫描前 offset + N 条记录，抛弃前 offset 条，只返回最后 N 条。如果 offset 值很大（例如 935500），即使最终只需要 10 条数据，数据库也必须遍历过百万行数据才能完成定位。这种全索引扫描加上回表操作（如果未使用覆盖索引），开销是巨大的。

通过 EXPLAIN 可以看到，随着 offset 增加，执行计划中的 rows 字段数值显著变大，耗时也随之线性甚至非线性增长：

SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935500, 10;
-- Execution time: 2.39 sec

核心思路：缩小扫描范围

优化的核心在于减少需要扫描的记录数和避免不必要的回表操作。具体策略分为两步：

1. 利用覆盖索引确定起始位置：仅在索引树中查找满足条件的排序后的 ID，避免回表读取完整行数据。
2. 根据 ID 精准回表：拿到需要的 ID 列表后，再通过主键索引快速获取完整记录。

这可以通过子查询或INNER JOIN两种写法实现。

第一种方案：子查询优化

通过子查询先找到第 offset 条记录的 ID，然后筛选出大于该 ID 的前 N 条记录。注意，为了保持倒序输出的正确性，外层还需要再进行一次排序。

第1步：构建子查询逻辑

假设我们要取 ftype=1 的第 935510 条之后的 10 条数据：

SELECT * FROM (
    SELECT * FROM t1 
    WHERE id > ( 
        SELECT id FROM t1 
        WHERE ftype=1 
        ORDER BY id DESC 
        LIMIT 935510, 1
    ) 
    LIMIT 10
) t 
ORDER BY id DESC;

关键点说明：

• 内层子查询 SELECT id FROM ... LIMIT 935510, 1 只查主键 id，触发了Using index（覆盖索引），无需回表，扫描行数虽然仍是 97万+，但代价远小于回表。
• 外层查询利用 id > (inner_result) 限制范围，直接通过主键索引定位后续 10 条记录。
• 由于内层是降序取中间值，外层取的是比它小的 ID 且需升序展示（因为原查询是 DESC，这里逻辑较为绕，建议理解为先定位边界，再截取范围）。上述写法经过两次倒序，确保最终结果与原 SQL 一致。

第二种方案：INNER JOIN 优化（推荐）

这种方法逻辑更清晰：先用子查询生成一个包含目标 ID 的小临时表，再与原表进行连接查询。

第1步：编写 JOIN 语句

SELECT t1.* FROM t1 
INNER JOIN (
    SELECT id FROM t1 
    WHERE ftype=1 
    ORDER BY id DESC 
    LIMIT 935500, 10
) t2 
USING (id);

原理优势：

• 内层子查询依然利用主键索引获取 ID 列表（覆盖索引）。
• 外层 JOIN 时，驱动表为这个小 ID 集合，被驱动表为原表 t1。
• 因为是基于主键 id 连接，访问类型变为 eq_ref，每次只需通过主键索引查找 1 行，彻底避免了大范围扫描和随机 I/O。

性能对比与结论

通过对相同数据量的测试，我们对比了原始 SQL、子查询优化和 INNER JOIN 优化的执行时间。

1. 带 WHERE 条件的深分页 (offset=935500)

• 原始 SQL: ~2.39 秒
• 子查询优化: ~1.86 秒 (提升约 22%)
• INNER JOIN 优化: ~1.83 秒 (提升约 23%)

2. 无过滤条件的深分页 (offset=935500)

• 原始 SQL: ~2.22 秒
• 子查询优化: ~2.01 秒 (提升约 9%)
• INNER JOIN 优化: ~1.70 秒 (提升约 23%)

尽管在极端深分页下绝对提升看似有限，但在中等偏移量或复杂查询条件下，这种方式能带来数量级的性能改善。特别是在大数据量表上，将 LIMIT offset 转换为 WHERE id > last_seen_id 的模式，是标准最佳实践。

以下是不同场景下的效率提升比例对比：

总结

针对 MySQL 的大偏移量分页问题，优先推荐使用 INNER JOIN 方式。它利用覆盖索引快速定位 ID，再通过主键高效回表，既保证了 SQL 的可读性，又在执行计划上实现了最优的访问路径。对于无限滚动加载（Infinite Scroll）等场景，还可以进一步改为使用 WHERE id < last_id LIMIT N 的方式，完全消除 OFFSET 带来的性能损耗。

以上就是 MySQL 中分页优化的详细内容，更多关于 MySQL 性能调优的资料请关注本站其它相关文章！