12.MySQL深度分页优化：从原理到实战-优快云博客

MySQL深度分页优化：从原理到实战

1. 问题分析：原始SQL为何慢？

原SQL：

SELECT * FROM aa ORDER BY create_time DESC LIMIT 10000, 100;

核心性能瓶颈：

回表与偏移量成本：数据库需要先排序并扫描 10000 + 100 条记录，然后丢弃前 10000 条。如果查询需要 * 所有字段，则必须进行回表操作，这在大偏移量时成本极高。
总之就是，1.要查偏移前垃圾数据–游标法解决 2.垃圾数据还要回表–延迟关联法解决。

2. 核心优化方案一：游标法（基于游标的分页）

此方法通过记录上一页最后一条记录的位置（游标）来完全避免使用 OFFSET。

适用场景：顺序翻页（如“下一页”）、无限滚动。

2.1 已知精确游标

如果能找到一个与排序方向同序的唯一标识（如自增主键 id），可以直接用它作为查询条件。

SELECT * FROM aa WHERE id < 415423 ORDER BY create_time DESC LIMIT 100;

优势：直接定位到数据起始点，无需扫描和跳过任何记录，性能最佳（时间复杂度 O(1)）。

2.2 子查询定位游标

当排序字段 (create_time) 与游标字段 (id) 同序时，可以先通过子查询快速找到游标。

SELECT * FROM aa WHERE id <= (
    SELECT id FROM aa ORDER BY create_time DESC LIMIT 10000, 1
) ORDER BY create_time DESC LIMIT 100;

3. 核心优化方案二：延迟关联

此方法的核心是利用覆盖索引减少回表数据量。它先快速定位到需要的主键ID，再通过关联查询获取完整数据。

适用场景：需要跳页、排序字段有索引但无法使用游标法的情况。

实现方式：

SELECT t.* FROM aa t
INNER JOIN (
    SELECT id FROM aa
    ORDER BY create_time DESC
    LIMIT 10000, 100
) AS tmp ON t.id = tmp.id
ORDER BY t.create_time DESC;

原理：内层的子查询只查询 id 和 create_time（如果建立了 (create_time, id) 的覆盖索引，则子查询可以完全在索引中完成，避免回表）。外层查询再通过主键ID快速关联获取所有字段。这样，虽然仍要处理 OFFSET 10000，但需要操作的数据量（仅在索引中）远小于原SQL的回表操作。

4. 方案对比与索引选择

4.1 性能对比

方案	性能	支持跳页	实现复杂度	推荐度
游标法	⭐⭐⭐⭐⭐ (最优)	❌	⭐⭐	⭐⭐⭐⭐⭐ (顺序翻页首选)
延迟关联	⭐⭐⭐⭐ (较好)	✅	⭐⭐⭐	⭐⭐⭐⭐ (需跳页时首选)
原始 LIMIT OFFSET	⭐ (差)	✅	⭐	⭐ (不推荐用于深分页)

结论：游标法 (2) > 延迟关联 (3) > 原SQL (1)。

4.2 索引策略精讲

问题一：`create_time` 需要索引吗？

游标法 (方案2)：需要。
游标法过程： (结果排序/额外排序)
先根据id 找主键索引，然后再把结果集按 creat_time排序。
如果后者数据量很大，那就用索引。
延迟关联法 (方案3)：必须。这是该方案高效的基础，子查询需要利用索引来快速定位和排序。

问题二：有必要创建联合索引 `(create_time, id)` 吗？

对于游标法：几乎无用。游标法两个字段分开查，优先使用 id 进行定位，联合索引在此优势不大。
对于延迟关联法：非常有用。创建 (create_time, id) 联合索引可以实现覆盖索引。子查询的 SELECT id ... ORDER BY create_time 可以完全在这个索引上完成，无需回表，性能得到最大提升。

5. 深度思考与最佳实践

理解问题本质：百万/千万级数据后，OFFSET 值越大，SQL执行越慢。LIMIT offset, size 比 LIMIT size 慢得多。
反思业务需求：遇到深度分页需求，应首先思考其合理性。很多时候，产品层面可以通过优化搜索、筛选或改为“加载更多”的方式来避免深度分页。
技术选型原则：如果数据量小（如千级别）且增长缓慢，传统的 LIMIT offset, size “整挺好，能用就行”，无需过度优化。对于大数据量，应优先考虑游标法或延迟关联。