mysql分页问题
MySQL 的分页问题通常表现为 数据量较大时，使用 LIMIT offset, rows 方式分页性能急剧下降，尤其是当 offset 值非常大时（如第 1000 页）。
一、问题根源：OFFSET 的工作原理
-
低效的扫描过程：
-
SELECT ... LIMIT 100000, 20并不是直接跳到第 100000 条记录。 -
MySQL 需要先 扫描前
100000 + 20条记录，然后丢弃前 100000 条，只返回最后的 20 条。 -
OFFSET越大，需要扫描和丢弃的数据就越多，性能越差（时间复杂度接近O(n)）。
- 额外开销：
-
大
OFFSET会导致大量无效的磁盘 I/O 和 CPU 消耗。 -
如果查询包含
ORDER BY，还需对所有满足条件的数据进行排序（可能用到临时表或文件排序）。
二、核心优化方案
-
延迟关联（Deferred Join）— 最推荐
-
先通过子查询快速定位到主键 ID，再用主键关联回原表获取完整数据。避免对大结果集排序和扫描。
SELECT t1.* FROM your_table t1 JOIN ( SELECT id -- 只查询主键 FROM your_table WHERE ... -- 可选：查询条件 ORDER BY create_time DESC -- 排序字段 LIMIT 100000, 20 -- 分页参数) t2 ON t1.id = t2.id; -- 通过主键关联ORDER BY t1.create_time DESC; -- 保持排序
-
子查询只需扫描索引（覆盖索引最佳），避免回表。
-
外层查询仅需 20 次主键回表，效率极高。
-
适合单表或多表关联时对大表分页。
-
游标分页（Cursor-based Pagination）
-
用上一页最后一条记录的有序唯一值（如自增 ID、时间戳）作为起点，替代
OFFSET。 -
-- 第一页（按时间倒序） SELECT * FROM your_table WHERE ... -- 查询条件 ORDER BY create_time DESC, id DESC -- 排序（确保唯一性） LIMIT 20;
-- 下一页（假设上一页最后一条记录的 create_time='2023-10-01 12:00:00', id=100） SELECT * FROM your_table WHERE (create_time < '2023-10-01 12:00:00') OR (create_time = '2023-10-01 12:00:00' AND id < 100) -- 处理相同时间的情况 ORDER BY create_time DESC, id DESCLIMIT 20;
-
优势：
-
性能稳定（
O(1)），不受页码影响。 -
无
OFFSET，直接通过索引定位起点。 -
限制：
-
只能顺序翻页（上一页/下一页），不能跳页。
-
排序字段必须唯一且有序（可组合多个字段）。
-
覆盖索引（Covering Index）
-
-- 创建覆盖索引（包含查询字段和排序字段） ALTER TABLE your_table ADD INDEX idx_cover (status, create_time, id, name, title); -- 查询直接走索引 SELECT id, name, title -- 只查询索引包含的字段 FROM your_table WHERE status = 1 ORDER BY create_time DESC LIMIT 100000, 20;
-
优势：
-
无需回表，性能显著提升。
-
适合查询字段固定的场景。
-
限制：
-
索引可能较大，占用存储。
-
无法解决
OFFSET扫描问题（仍需优化为大表方案 1）。
场景
推荐方案
深度分页（如第 1000 页）
✅ 延迟关联（方案 1）
无限滚动（如新闻流）
✅ 游标分页（方案 2）
固定条件筛选+分页
✅ 覆盖索引（方案 3）
需要跳页（如第 1 页→第 5 页）
⚠️ 延迟关联 + 业务层妥协
上面的都不能解决这个问题的时候可以考虑其它数据存储方案
发表评论
所有评论