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

文章目录 一、为什么深分页（大偏移量）查询慢？二、优化方案1. 覆盖索引优化（避免回表）2. 基于游标的分页（记录上次位置）3. 延迟关联（减少回表次数）4. 业务层优化（限制深分页）5. 使用分区表或分库分表 三、对比总结四、示例：电商订单分页优化五、优化建议

---

---

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

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

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

核心问题：

大量无效 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. 使用分区表或分库分表

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

实现方法：

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

---

三、对比总结 优化方案适用场景优点缺点覆盖索引优化查询字段可被索引覆盖显著减少 I/O 和回表次数需设计合适的覆盖索引游标分页有序连续的分页完全避免 OFFSET 扫描需记录游标，不支持随机跳页延迟关联必须回表查询完整数据减少回表次数需要联合索引支持业务层限制深分页用户无需访问过深数据简单易实现功能受限分区表/分库分表数据量极大缩小单次查询范围架构复杂，维护成本高

---

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

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

优化步骤：

使用覆盖索引：ALTER TABLE orders ADD INDEX idx_create_time_status (create_time, status); 延迟关联查询：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; 业务层限制：前端仅允许翻页到前 1000 页。

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

---

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