MySQL 深分页速度慢的原因及优化方案

一、为什么深分页（大偏移量）查询慢？

当执行类似 SELECT * FROM table LIMIT 100000, 20 的查询时，MySQL 需要完成以下步骤：

1. 扫描全表或索引：先读取前 100000 + 20 条数据。
2. 丢弃前 N 条：跳过前 100000 条数据，仅保留最后 20 条。
3. 回表查询（如果使用非覆盖索引）：根据索引查找到的主键，再回表查询完整数据。

核心问题：

• 大量无效 I/O：即使只需要 20 条数据，MySQL 仍需扫描并跳过前 10 万条记录。
• 高 CPU 开销：排序、过滤和临时表操作会消耗大量资源。
• 索引失效：深分页时，优化器可能放弃使用索引，选择全表扫描。

二、优化方案

以下是针对不同场景的优化方法：

1. 覆盖索引优化（避免回表）

适用场景：查询的字段可以被索引覆盖（不需要回表查完整行）。

优化方法：

-- 原查询（慢）：
SELECT * FROM orders ORDER BY create_time LIMIT 100000, 20;

-- 优化后：
SELECT * FROM orders 
INNER JOIN (
    SELECT id FROM orders 
    ORDER BY create_time 
    LIMIT 100000, 20
) AS tmp 
ON orders.id = tmp.id;

原理：

• 子查询仅查询 id（使用覆盖索引），减少数据扫描量。
• 外层查询通过主键快速获取完整数据。

2. 基于游标的分页（记录上次位置）

适用场景：有序且连续的分页（如时间线、滚动加载）。

优化方法：

-- 第一页：
SELECT * FROM orders 
ORDER BY create_time, id 
LIMIT 20;

-- 后续页（假设上一页最后一条记录的 create_time=2023-10-01, id=1005）：
SELECT * FROM orders 
WHERE create_time >= '2023-10-01' AND id > 1005
ORDER BY create_time, id 
LIMIT 20;

原理：

• 通过 WHERE 条件直接定位到起始位置，避免 OFFSET 扫描。
• 需保证排序字段唯一（如联合 id 排序），否则可能漏数据。

3. 延迟关联（减少回表次数）

适用场景：必须回表查询完整数据，但希望减少回表次数。

优化方法：

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

原理：

• 子查询通过覆盖索引快速定位目标 id。
• 外层查询仅回表 20 次，而非 100020 次。

4. 业务层优化（限制深分页）

适用场景：用户不需要访问过深的数据（如限制最多翻页到第 100 页）。

实现方法：

• 前端隐藏页码，仅提供“上一页/下一页”按钮。
• 后端缓存热门页数据（如 Redis 缓存前 1000 页的查询结果）。

5. 使用分区表或分库分表

适用场景：数据量极大（如亿级），且业务允许拆分。

实现方法：

• 按时间或范围分区，缩小单次查询的数据量。
• 分库分表后，深分页查询需跨节点聚合（复杂度高，慎用）。

三、对比总结

四、示例：电商订单分页优化

场景：用户需要查询第 5000 页的订单数据（每页 20 条）。

优化步骤：

1. 使用覆盖索引：

   ALTER TABLE orders ADD INDEX idx_create_time_status (create_time, status);
   

2. 延迟关联查询：

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

3. 业务层限制：前端仅允许翻页到前 1000 页。

效果：查询耗时从 2 秒降低到 50 毫秒。

五、优化建议

1. 优先使用游标分页（如基于时间或唯一键）。
2. 合理设计索引：覆盖索引 + 排序字段联合索引。
3. 避免全表扫描：确保 WHERE 条件能命中索引。
4. 数据归档：将历史数据迁移到备份表，减少主表体积。