MySQL 深度分页如何优化？

什么是深度分页？

深度分页是指，当某张表数据量极大，查询的页码又非常靠后，导致最终分页 SQL 中的 offset 偏移量很大，数据库需要扫描并丢弃大量记录，导致性能急剧下降的查询问题。

比如说有一张订单表，查询页码为 100001 的数据，每页展示 10 条数据，其分页 SQL 如下：

SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 10;

MySQL 为了定位偏移量（极其耗时），需要依次遍历并丢弃前 1000000 条记录，再获取接下来的 10 条记录，返回给客户端。

性能瓶颈在于：OFFSET 偏移量越大，需要扫描和丢弃的无效数据就越多。它本质上是 O(N) 的时间复杂度，而不是很多人以为的 O(1)。

如何优化？

方案一：覆盖索引 + 延迟关联（最通用）

让 LIMIT 操作只在索引上完成，避免回表扫描大量数据行。

什么是回表？

回表就是先查 “电话簿索引” 找到门牌号，再根据门牌号去 “住户详情本” 里查具体信息，相当于多跑了一趟路。

（解释：数据库先通过索引找到主键 ID，再用这个 ID 去主表里读取完整数据行的过程）

-- 优化前（慢）：
SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 10;

-- 优化后（快）：
SELECT * FROM orders AS main
INNER JOIN (
    -- 子查询：利用覆盖索引，快速定位到需要的主键ID
    SELECT id FROM orders
    ORDER BY create_time DESC
    LIMIT 1000000, 10
) AS tmp ON main.id = tmp.id
-- 外层查询：用10个ID回表取完整数据
ORDER BY main.create_time DESC; -- 注意保持顺序

为什么快？

子查询的 SELECT id 只需要扫描 (create_time, id) 的联合索引（覆盖索引），无需回表访问数据行。
子查询的结果集只有 10 个 ID，而不是 1000010 行数据。
外层查询仅用这 10 个 ID 去回表取数据，效率极高。

关键前置条件：必须创建合适的覆盖索引。

-- 最佳索引设计：排序字段在前，主键在后
CREATE INDEX idx_cover ON orders (create_time DESC, id);

为什么上面是最佳索引设计？

因为分页查询的排序条件为 ORDER BY create_time DESC , 根据索引的最左匹配原则，上面索引可以完美匹配，同时，索引中包含 id 字段，而子查询只需查询出此字段，所以无需回表。

方案二：游标分页（连续翻页场景的性能王者）

适用场景：只能 “上一页/下一页”，不能任意跳页（如今日头条新闻流、朋友圈等）。

原理：记录上一页最后一条记录的位置。

-- 第一页
SELECT * FROM orders 
ORDER BY create_time DESC, id DESC  -- 加上id保证排序唯一性
LIMIT 10;

-- 第二页：记住第一页最后一条的 create_time 和 id
-- 假设最后一条是：('2024-01-01 10:00:00', 999)
SELECT * FROM orders 
WHERE (create_time, id) < ('2024-01-01 10:00:00', 999)
ORDER BY create_time DESC, id DESC 
LIMIT 10;

优势：

时间复杂度从 O(N) 降为 O(log N)，利用索引快速定位。
完全避免 OFFSET 带来的性能问题。

索引设计：

CREATE INDEX idx_cursor ON orders (create_time DESC, id DESC);

方案三：业务妥协法（改变产品逻辑）

优化思路：有时候，产品经理提的需求，也不一定都是合理的，此时，最好的优化是改变需求。

限制最大翻页：如电商只允许查看前 100 页，更早的数据通过搜索（Elasticsearch）获取。
分段加载：不显示总页数，只提供 “加载更多” 按钮。
添加强过滤条件：让用户先选择时间范围，大幅缩小数据集。

SELECT * FROM orders 
WHERE create_time > '2024-01-01'  -- 先通过条件筛选
ORDER BY create_time DESC 
LIMIT 1000000, 10; -- 此时偏移量实际处理的数据已大大减少

方案四：预先计算的汇总表（宽表）

针对复杂聚合分页：如按商品销量排行，且需要多表关联，可以设计一张宽表（汇总表），每当子表有数据变更，预先更新到这张宽表里，利用 “空间换时间”，查询时直接查这张汇总表即可。

-- 创建一张定时更新的汇总表
CREATE TABLE product_sales_daily (
	product_id BIGINT PRIMARY KEY,
	sales_count INT,
	rank_position INT, -- 可以预先计算排名
	updated_at TIMESTAMP
);

-- 分页查询直接从汇总表查，速度极快
SELECT * FROM product_sales_daily 
ORDER BY rank_position 
LIMIT 1000000, 10;

方案五：架构升级 – 引入搜索引擎

当数据量达到亿级，以上 SQL 层面的优化可能都力不从心。此时可以使用 “MySQL + Elasticsearch” 的异构架构。

MySQL：负责事务性写入和主键查询。
Elasticsearch：负责复杂查询和深度分页。ES 的 search_after 机制天生适合深度分页。

同步方案：使用 Canal 监听 MySQL Binlog 日志，实时同步增量数据到 ES 中。