mysql分页查询如何优化_mysql大数据分页方案

OFFSET 越大越慢是因为 mysql 必须顺序扫描前 offset + size 行并丢弃，InnoDB 无跳过能力；推荐用游标分页（如 WHERE id

为什么 OFFSET 越大越慢？

MySQL 的 LIMIT offset, size 在 offset 很大时（比如几百万），会强制扫描前 offset + size 行再丢弃，即使只取 20 条，也可能触发全表扫描或大量索引遍历。InnoDB 引擎没有“跳过 N 行”的物理能力，只能顺序读取。

常见错误现象：select * FROM orders ORDER BY id DESC LIMIT 1000000, 20 执行时间从几毫秒飙升到数秒甚至超时；EXPLaiN 显示 rows 高得离谱，且 Extra 出现 using filesort 或 Using index condition 但实际效率极低。

• 根本原因不是数据量大，而是 MySQL 缺乏游标式分页原语
• ORDER BY 字段必须有高效索引（最好是主键或覆盖索引），否则排序成本本身就会放大 offset 问题
• 如果 WHERE 条件过滤后结果集仍很大，先用子查询或 JOIN 压缩范围，再分页

用主键/有序字段做游标分页（推荐）

替代 LIMIT offset, size，改用上一页最后一条记录的排序字段值作为下一页起点，例如：已查完第 1–20 条（id 从 10000 到 9981），下一页就查 WHERE id 。这叫“keyset pagination”或“cursor-based pagination”。

适用场景：用户持续下拉浏览、后台任务分批处理、API 接口（如 graphql 的 after 参数）。

• 要求排序字段严格唯一且非空（主键最稳妥；若用时间戳需加第二排序字段防重复）
• 不能跳页（不支持“直接跳到第 100 页”），但绝大多数真实业务不需要跳页
• SQL 示例：SELECT * FROM logs WHERE created_at
• 性能稳定，无论翻到第几万页，执行时间基本不变

大数据量下如何安全地支持跳页？

真需要跳页（比如后台管理系统的页码输入框），不能硬扛 OFFSET，得靠预计算或缓存。

常见做法是把“页号 → 对应主键范围”映射提前算好并存入辅助表或 redis：

• 用定时任务或写入时触发，按固定步长（如每 1000 行）记录最小/最大主键值，生成页索引表 page_index(table_name, page_num, min_id, max_id, row_count)
• 查第 N 页时，先查 page_index 拿到 min_id 和 max_id，再查 WHERE id BETWEEN ? AND ? 并用 LIMIT 截断
• 或者用 elasticsearch / clickhouse 做分页代理层，MySQL 只存原始数据，分页逻辑下沉
• 避免在事务中嵌套大 offset 查询，尤其不要在 UPDATE ... LIMIT offset, 1 这类语句里用大 offset

哪些“优化技巧”其实没用甚至有害？

网上流传的一些“小技巧”，在真实大数据分页场景下往往失效或引入新问题。

• SELECT id FROM table ORDER BY id LIMIT 1000000, 1 再 JOIN 主表 —— 看似减少数据传输，但子查询依然要扫 100w 行，IO 和 CPU 没省多少
• 给 ORDER BY 字段加前缀索引（如 INDEX(title(10))）—— 导致排序无法走索引，反而更慢
• 用 SQL_CALC_FOUND_ROWS 获取总行数 —— 在 InnoDB 中会强制扫描全表或全索引，比两次查询还慢，且已被 MySQL 8.0.17+ 废弃
• 把大表按时间/ID 拆成多个物理表（sharding）却不改分页逻辑 —— 问题只是转移，没解决

真正关键的是：明确业务是否真的需要跳页，然后选择游标分页或带索引的页码映射。其他所有“绕开 offset”的尝试，若没改变数据访问模式，大概率只是掩耳盗铃。