LIMIT分页查询为什么后面页面的查询会很慢?

后面页面慢，根因只有一句话：**`LIMIT offset, size` 会先扫描并丢掉 `offset` 行，再取 `size` 行**。`offset` 越大，白干活越多，所以越慢。再细化到执行层面： # 为什么会慢（机制层） 1. **跳过不是“跳过去”，而是“扫过去”** B+Tree 只能按“键值”跳转，**按“第 N 行”没有直接定位能力**。所以必须从当前游标/范围开始一行行前进，跳过 `offset` 行。 2. **回表/随机 IO**（InnoDB） 常见 `ORDER BY xxx LIMIT offset,size`： * 若不是**覆盖索引**，先按二级索引扫到主键，再**回表**取行，随机读多、页缓存命中差；`offset` 大时耗时线性增长。 3. **排序/临时表** * `ORDER BY` 不能走索引时会 `filesort` + 临时表，数据量越大、`offset` 越靠后，排序与丢弃代价越高（`sort_buffer_size`、`tmp_table_size` 撑不住还会落盘）。 4. **多条件过滤** * `WHERE` + `ORDER BY` 组合没用好联合索引，先全扫再筛选再丢弃，叠加代价更高。 # 常见慢的 SQL 形态 * `SELECT ... FROM t ORDER BY create_time DESC LIMIT 100000, 20;` 即便有 `create_time` 索引，也会**遍历并跳过**前 10 万条；若非覆盖，还要回表 10 万次。 * `SELECT ... FROM t WHERE status=1 ORDER BY id LIMIT 500000, 50;` 条件过滤+大偏移，代价叠加。 # 优化思路（按“无侵入 → 侵入式重写”排序） ## A. 让“扫描与排序”尽量在索引里完成（减回表/减排序） * **建立匹配的联合覆盖索引**：`WHERE` 条件列在前，`ORDER BY` 列在后，最后加上**返回列**以覆盖查询。 例如： ```sql -- 业务常用过滤：status, type；排序：create_time DESC；返回列只要 (id, title, create_time) CREATE INDEX idx_t_status_type_ctime_id_title ON t(status, type, create_time DESC, id, title); ``` 让 `EXPLAIN` 里 `Extra` 变成 `Using index`、没有 `Using filesort/temporary`。 但注意：**再大 offset 仍然线性变慢**，只是常数项更小。 ## B. 子查询预取主键再回表（“两段式分页”） * 先用**覆盖索引**只取主键（跳过快很多），再回表取整行： ```sql SELECT t.* FROM t JOIN ( SELECT id FROM t FORCE INDEX(idx_status_type_ctime) WHERE status=1 AND type=2 ORDER BY create_time DESC, id DESC LIMIT 100000, 20 ) x ON x.id = t.id ORDER BY t.create_time DESC, t.id DESC; ``` 子查询只在索引上走，**避免大规模回表**；最终仅回表 20 行。适合“必须跳到第 N 页”的场景。 ## C. **Keyset/Seek 分页（强烈推荐）**：用“上次最后一条”的游标翻页（不靠 offset） * 思想：用排序键作为游标，**WHERE 继续向后/向前查**，复杂度与页码无关。 ```sql -- 第1页 SELECT * FROM t WHERE status=1 AND type=2 ORDER BY create_time DESC, id DESC LIMIT 20; -- 下一页（拿到上页最后一行的 (create_time, id) 作为游标） SELECT * FROM t WHERE status=1 AND type=2 AND (create_time, id) < (?, ?) -- 严格小于上一页最后一条的复合键 ORDER BY create_time DESC, id DESC LIMIT 20; ``` 要点： * 为 `WHERE + ORDER BY` 建**同序的联合索引**：`(status, type, create_time DESC, id DESC)`。 * **超快且稳定**：不随页码变慢。 * 代价：不能直接“跳到第 N 页”，需要“游标式”翻页；前端保存 last key。 ## D. 业务层“伪分页” * 若强需求“跳转第 N 页”，但 N 很大、实时性要求不高： * 预先做**离线/缓存**的“索引表”只存主键与排序键，按页切片；或在 Redis 保存每页主键列表；页面点开再批量回表。 * 适用热点榜单、固定筛选条件场景。 ## E. 其他技巧 * **限制最大页码/偏移**：后端参数校验，超过阈值拒绝或改用 keyset。 * **合理的返回列**：只返回必要列（能覆盖就覆盖）。 * **稳定排序**：为避免同键并列导致翻页重复/遗漏，排序键加 `id` 作为**第二排序键**。 * **EXPLAIN 检查**：避免 `Using filesort`/`Using temporary`；看 `rows` 预估是否巨大。 * **统计页数**：`COUNT(*)` 代价大时可以用**估算/延迟计算**（例如仅在用户真正跳转到末页时再算）。 # 选型建议 * **需要“上一页/下一页”体验** → **Keyset/Seek 分页**（首选，性能最稳）。 * **必须跳到第 N 页** → “子查询只取主键 + 回表”方案，配合覆盖索引，或业务层缓存页索引。 * **仅偶尔翻到很后面** → 先把索引建好；仍慢时考虑上面两种改造。 一句话：**大 offset 天生慢**。尽量换成**基于排序键的游标分页**，或用**两段式：索引取主键 → 再回表**，就能把后面页面的性能拉回到可接受甚至很快的水平。