1. 为什么会出现 MySQL 深度分页问题？
在 MySQL 中，通常使用 LIMIT 和 OFFSET 进行分页查询，例如：
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10 OFFSET 10000;
问题分析
-
OFFSET 10000需要 MySQL 先扫描前 10000 行，然后丢弃，再返回 后 10 行。 -
扫描+丢弃的行数越多，性能越差，即 深度分页（页数越大，查询越慢）。
-
底层原因：
- MySQL 无法直接跳转到 OFFSET 位置，必须先读取和丢弃前面的数据行。
- 索引无法完全优化 OFFSET，即使
ORDER BY字段有索引，也无法避免全表扫描。
2. MySQL 深度分页的优化方案
方案 1：索引优化分页（推荐）
核心思路：
- 避免使用 OFFSET，直接利用索引获取目标数据。
- 利用主键（或索引列）记录上一次查询的最大值，用
WHERE过滤，而不是OFFSET。
示例：
SELECT * FROM orders
WHERE created_at < (SELECT created_at FROM orders ORDER BY created_at DESC LIMIT 1 OFFSET 10000)
ORDER BY created_at DESC
LIMIT 10;
优化点
✅ WHERE 条件 直接跳转到需要的行，而不是扫描所有数据。
✅ 索引生效，大幅提高查询性能。
更好的写法：
SELECT * FROM orders
WHERE created_at < '2024-02-08 12:00:00' -- 上一页最后一条数据的 created_at
ORDER BY created_at DESC
LIMIT 10;
👉 前端只需记住上一页的最后一条 created_at，然后请求下一页的数据。
方案 2：使用覆盖索引（减少回表）
如果 created_at 有索引，但查询 SELECT * 可能导致回表，可以 只查询索引列：
SELECT id FROM orders ORDER BY created_at DESC LIMIT 10 OFFSET 10000;
然后再基于 id 进行查询：
SELECT * FROM orders WHERE id IN (
SELECT id FROM orders ORDER BY created_at DESC LIMIT 10 OFFSET 10000
);
✅ 减少回表查询，提升性能。
🚫 仍需 OFFSET，适用于小数据分页。
方案 3：使用 SQL 物化表缓存分页结果
如果深度分页请求频繁，可以 缓存分页查询的 ID 结果，然后查询：
- 定期预计算分页数据并缓存：
CREATE TABLE paged_orders AS
SELECT id FROM orders ORDER BY created_at DESC LIMIT 100000;
- 查询时，直接从
paged_orders取数据：
SELECT * FROM orders WHERE id IN (
SELECT id FROM paged_orders LIMIT 10 OFFSET 10000
);
✅ 适用于大规模数据查询，提升查询效率。
🚫 占用存储，数据可能滞后。
方案 4：使用 NoSQL（如 Elasticsearch）优化分页
MySQL 不擅长深度分页，Elasticsearch 更适合搜索型分页，可用 scroll 或 search_after：
search_after避免 OFFSET，基于上次查询的最后一个文档 ID 获取下一页数据。scroll批量读取数据，适用于日志等大数据查询。
3. 方案对比
| 方案 | 适用场景 | 优势 | 劣势 |
|---|---|---|---|
索引优化分页 (WHERE created_at < ?) | 适用于大部分分页 | 高效，索引优化，避免 OFFSET | 需要前端记住上一页最后一条数据 |
| 覆盖索引 | 查询大数据表时 | 减少回表，提高查询速度 | 仍需 OFFSET，适用于小数据 |
| SQL 物化表缓存 | 高并发深度分页 | 预计算，提高查询效率 | 占用存储，数据可能滞后 |
| Elasticsearch | 搜索型深度分页 | 性能极高 | 需要额外的 ES 部署 |
4. 最佳实践
- 优先使用索引优化分页 (
WHERE id < ?或WHERE created_at < ?)。 - 如果数据量 超大，使用 覆盖索引 或 物化表 提前计算结果。
- 海量数据+复杂搜索，使用 Elasticsearch 进行分页优化。
总结
✅ 避免 OFFSET 过大，利用索引跳过不必要的扫描，必要时使用缓存或 NoSQL！ 🚀
