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，得靠预计算或缓存。

MvMmall 网店系统

免费的开源程序长期以来，为中国的网上交易提供免费开源的网上商店系统一直是我们的初衷和努力奋斗的目标，希望大家一起把MvMmall网上商店系统的免费开源进行到底。2高效的执行效率由资深的开发团队设计，从系统架构,数据库优化，配以通过W3C验证的面页模板，全面提升页面显示速度和提高程序负载能力。3灵活的模板系统MvMmall网店系统程序代码与网页界面分离，灵活的模板方案，完全自定义模板，官方提供免费模

下载

常见做法是把“页号 → 对应主键范围”映射提前算好并存入辅助表或 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”的尝试，若没改变数据访问模式，大概率只是掩耳盗铃。