mysql分页查询如何用索引_mysql大数据分页方案

offset 越大分页越慢，因mysql需扫描前 offset + size 行再丢弃前 offset 行；应改用游标分页，基于上一页最后排序值（如 id

为什么 OFFSET 越大，分页越慢

MySQL 的 LIMIT offset, size 在 offset 很大时（比如 100 万），会先扫描前 offset + size 行，再丢弃前 offset 行——即使你只想要最后 20 行。这时索引只能加速“找到起点”，但无法跳过中间大量数据，最终变成全索引扫描。

常见错误现象：SELECT * FROM orders ORDER BY id DESC LIMIT 1000000, 20 执行数秒甚至超时，而 EXPLAIN 显示 rows 高达百万级，key 虽用了索引，但 Extra 是 Using filesort 或空（说明没跳过扫描）。

• 必须确保 ORDER BY 字段有索引，且和 LIMIT 的方向一致（如 ORDER BY created_at DESC → 索引要建在 (created_at) 或 (created_at, id) 上）
• 复合索引中，排序字段必须是索引最左前缀；如果还带 WHERE 条件，要把过滤字段放前面，排序字段紧随其后
• 不要依赖 OFFSET 做深分页——它本质是“跳过 N 行”，不是“从第 N 行开始读”

用游标分页（Cursor-based Pagination）替代 OFFSET

核心思路：不记“第几页”，而记“上一页最后一条的排序值”。下一页查询直接从该值之后取数据，避免跳过历史行。

例如，按 id 降序分页，上一页最后一条是 id = 5002341，那么下一页就是：

SELECT * FROM orders 
WHERE id < 5002341 
ORDER BY id DESC 
LIMIT 20;

这个查询能走 id 索引的范围扫描，EXPLAIN 中 rows 通常只有 20 左右。

• 必须使用唯一、有序、非空的字段做游标（推荐主键 id 或带时间戳的 created_at + 主键组合）
• 如果排序字段可能重复（如多个订单同秒创建），必须补一个唯一字段，如 ORDER BY created_at DESC, id DESC，游标传两个值：created_at = '2024-05-01 10:23:45', id = 987654
• 前端需保存并传递游标值，不能靠页码计算；首次请求可不带 WHERE，或用 WHERE id 启动

大数据量下 COUNT(*) 为什么不准又慢

COUNT(*) 在 InnoDB 中需要遍历索引树统计行数（除非是 MyISAM），当表超千万行时，可能耗时数秒，且结果只是快照，未必反映实时总数。更糟的是，加了 WHERE 条件后，优化器可能放弃索引，回表或全表扫描。

citySHOP 多用户商城

citySHOP是一款集CMS、网店、商品、系统，管理更加科学快速；全新Jquery前端引擎；智能缓存、图表化的数据分析，手机短信营销；各种礼包设置、搭配购买、关联等进一步加强用户体验；任何功能及设置都高度自定义；MVC架构模式，代码严禁、规范；商品推荐、促销、礼包、折扣、换购等多种设置模式；商品五级分类，可自由设置分类属性；商品展示页简介大方，清晰，图片自动放大，无需重开页面；商品评价、咨询分开

下载

实际场景中，用户真正需要的往往不是精确总数，而是“是否有下一页”或“大致量级”。

• 去掉 COUNT(*) 分页总数展示，改用“加载更多”按钮，或只查 SELECT ... LIMIT 21，判断第 21 行是否存在来决定是否显示下一页
• 如果必须估算总数，可用 SHOW TABLE STATUS LIKE 'orders' 查 Rows 字段（误差可能达 40%，但极快）
• 对高频查询的聚合需求，考虑异步更新计数表，或用 Redis 缓存近似值（如每写入 100 条更新一次）

覆盖索引 + 延迟关联优化 SELECT *

当分页查询 SELECT * 且表字段多、有大字段（如 TEXT、BLOB）时，即使走了索引，也可能因回表读取整行导致 I/O 暴增。

解决办法是先用覆盖索引查出主键，再用主键二次查所需字段：

SELECT o.* FROM orders o
INNER JOIN (
  SELECT id FROM orders 
  WHERE status = 1 
  ORDER BY created_at DESC 
  LIMIT 20 OFFSET 100000
) AS tmp ON o.id = tmp.id;

子查询只查 id，若 (status, created_at, id) 是覆盖索引，就完全避免回表；外层关联则精准拉取 20 行完整数据。

• 覆盖索引必须包含所有 WHERE、ORDER BY 和 SELECT 中用到的字段；主键自动包含在二级索引中，所以 id 总是可用的
• 该方案对 OFFSET 大的场景仍比纯 LIMIT 快，但不如游标分页；适合无法改造前端、只能改 SQL 的遗留系统
• 注意 MySQL 8.0+ 对这种写法优化更好，5.7 及以前版本建议显式加 STRAIGHT_JOIN 控制连接顺序

游标分页不是银弹——它要求排序字段稳定、不可为空、最好唯一；一旦业务需要按模糊条件（如关键词搜索）分页，就得结合 ES 或预计算方案。最容易被忽略的一点是：游标值本身必须参与业务逻辑校验，比如用户篡改 URL 中的 last_id=123，后端得确认该 ID 确实属于当前用户且满足权限条件，否则会泄露数据。