MySQL深分页的处理方案

 一、问题根源：为什么深分页效率低？

以最常见的 MySQL LIMIT offset, size 为例：

SELECT * FROM orders ORDER BY id DESC LIMIT 10000, 20;

        (1)数据库需要先排序（ORDER BY id DESC）。

        (2)然后跳过前 10000 条记录（OFFSET 10000）。

        (3)最后返回接下来的 20 条记录。

性能瓶颈在于第 2 步：跳过（OFFSET）。
        数据库服务器需要先读取、排序并临时存储 (10000 + 20) 条数据，然后丢弃前 10000 条，只返回最后 20 条。这个“跳过”的过程消耗大量的 CPU 和 I/O 资源，但绝大部分计算成果都被丢弃了，造成了巨大的浪费。

---

二、解决方案

针对不同的场景和数据库类型，有以下几种主流解决方案，从上手难度和效果推荐依次如下：

---

方案一：游标分页/查询【推荐】

        这是解决深分页最常用且高效的方案。其核心思想是：不使用固定的页码（Page），也不使用偏移量（OFFSET），而是使用上一页最后一条记录的唯一标识（如自增ID、时间戳）作为“游标”来获取下一页的数据。

实现方式：

-- 第一页
SELECT * FROM table ORDER BY id LIMIT 10;

-- 第二页（假设上一页最后一条的 id 是 100）
SELECT * FROM table WHERE id > 100 ORDER BY id LIMIT 10;

-- 第三页（上一页最后 id 是 200）
SELECT * FROM table WHERE id > 200 ORDER BY id LIMIT 10;

优点：
        性能极高：无论翻到第几页，查询速度都只和 LIMIT size 有关，与偏移量无关。
        适合无限滚动：非常适合移动端APP的无限下拉滚动场景。

缺点：
        不能直接跳转到指定页码：用户只能一页一页地顺序浏览（下一页/上一页），无法直接跳到第 N 页。
        要求排序字段唯一且连续：通常使用自增主键或唯一索引的时间戳。如果排序字段有大量重复值，需要结合其他字段（如主键）来确保顺序的唯一性。

---

方案二：覆盖索引+ 延迟关联优化

如果查询必须使用 LIMIT offset, size，可以尝试通过覆盖索引来减少回表开销。

优化后的查询：

SELECT * FROM orders 
INNER JOIN (
    SELECT id FROM orders 
    ORDER BY create_time DESC 
    LIMIT 100000, 20
) AS tmp ON orders.id = tmp.id;

        子查询 SELECT id ... 只查询了索引列 id 和 create_time，这个操作可以在覆盖索引上完成，非常快。生成一个只有 20 个 ID 的临时结果集，然后再通过 JOIN 回表查询这 20 条记录的完整数据。大大减少了回表的次数。

优点：
        能在一定程度上缓解深分页问题。

缺点：
        仍然是 OFFSET 方案，跳过的记录越多，子查询扫描索引的范围依然很大，只是比回表全记录要轻量很多。并非根治方案。
        需要建立合适的覆盖索引。

---

方案三：业务层优化（禁止超深分页）

从产品设计层面限制用户的行为。

例如：
        百度/谷歌搜索通常只提供最多 100 页的结果。
        在移动端APP中，更常见的交互是“无限下拉滚动”而非传统的页码跳转（游标分页）。
        后台系统可以限制 offset 的最大值（例如最大只能取 1000），或者当 offset 超过一个阈值（如 1000）时，提示用户“请使用更精确的筛选条件来缩小范围”。

优点：
        简单粗暴，有效防止数据库被慢查询拖垮。

缺点：
        是一种“限制”而非“解决”，功能上做了妥协。

---

方案四：使用专用搜索引擎

如果数据量极其庞大（亿级以上），且分页查询需求复杂（带多种过滤和排序），传统的 RDBMS 会非常吃力。此时应考虑将数据同步到专用的搜索引擎中，如：

Elasticsearch：天然支持深分页，提供了 search_after 参数来实现类似游标分页的功能，比 from + size 的方式高效得多。

优点：
        能处理海量数据的复杂查询和分页。

缺点：
        架构复杂，需要引入和维护新的中间件，有数据同步延迟。