结论:深分页变慢主因是索引未覆盖查询条件与排序字段,或OFFSET过大导致全量扫描丢弃数据;应建联合索引(WHERE字段+ORDER BY字段+查询字段),改用游标分页等方案替代LIMIT OFFSET。

直接说结论:当 ORDER BY 和 LIMIT 一起用且变慢时,90% 的问题出在没走对索引、或偏移量(OFFSET)过大导致 MySQL 扫描并丢弃大量数据。不是语句写错了,是执行路径错了。
为什么 ORDER BY + LIMIT 会随着页码增大越来越慢
MySQL 不会“跳到第 N 页再取数据”,而是先排序全部匹配行,再跳过前 OFFSET 行,最后取 LIMIT 行。比如 LIMIT 100000, 20,它必须:读取至少 100020 条完整记录 → 排序 → 丢弃前 100000 条 → 返回剩下 20 条。
- 即使
ORDER BY字段有索引,如果查询字段不在该索引里(比如SELECT *),就会触发回表,深分页时随机 I/O 暴增 - 如果
WHERE条件和ORDER BY字段不共用索引,优化器可能放弃索引,改走全表扫描 +Using filesort -
EXPLAIN中看到rows值远大于LIMIT数,就是典型信号
怎么建索引才真正生效
复合索引顺序必须匹配查询模式:先等值条件(WHERE),再排序字段(ORDER BY),最后可加查询字段(覆盖索引)。不能只给 ORDER BY 单独建索引就完事。
安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。
- 错误示例:
WHERE shop_id = 20484 AND status = 4 ORDER BY gmt_create DESC,只建(gmt_create)索引——优化器大概率不用 - 正确做法:建
(shop_id, status, gmt_create),让 WHERE 过滤和排序都在一棵 B+ 树上完成 - 若还需查
buyer_id、seller_id等字段,把它们也加进索引尾部,形成覆盖索引,避免回表 - 注意最左前缀:如果
WHERE中用了shop_id > 1000这类范围条件,那它之后的字段(如status)在该索引中就无法用于排序
替代 OFFSET 的三种实操方案
跳页需求越强,越要放弃 LIMIT offset, size;游标式分页才是深分页的正解。
-
主键游标(推荐,最快):适用于按主键或时间递增排序。第一页查
ORDER BY id DESC LIMIT 20,记住返回的最小id(比如 99980),下一页查WHERE id 。扫描行数恒定,不随页码增长 -
延迟关联(支持跳页):先用子查询只走索引取 ID,再 JOIN 原表补全字段。
SELECT o.* FROM order o INNER JOIN (SELECT id FROM order WHERE ... ORDER BY id DESC LIMIT 100000, 20) t ON o.id = t.id。子查询只读索引页,IO 大幅下降 -
业务层限页 + 搜索兜底:前端禁止输入超大页码(如 > 500),后端校验
OFFSET > 10000时直接报错或引导用搜索/筛选。比硬扛性能更实际
容易被忽略的细节和陷阱
很多优化卡在最后一厘米:索引建了,SQL 改了,但还是慢。往往是因为这些点没关照到。
-
ORDER BY中混用ASC和DESC(如ORDER BY a ASC, b DESC):MySQL 5.7 及更早版本无法用单个索引高效处理,会强制filesort;8.0+ 虽支持,但索引定义也得严格匹配方向 - 在排序字段上用了函数,比如
ORDER BY UPPER(name)或ORDER BY DATE(create_time):索引完全失效 -
LIMIT后面跟了OFFSET但没加WHERE过滤:等于对全表排序,哪怕只有 10 万行,LIMIT 99990, 10也会扫满 - 误信
FORCE INDEX能解决一切:它只是绕过优化器决策,但如果索引本身设计不合理(比如缺失WHERE字段),强制走索引反而更慢

















