深分页与索引优化
列表页翻到几百页之后接口突然变慢，是后端最常见的性能问题之一。根因往往不是“数据量大”，而是 LIMIT 1000000, 20 这类写法迫使数据库先扫描并丢弃前一百万行。本章讲清深分页的成因、四种优化写法、联合索引的最左前缀规则，以及索引失效与 EXPLAIN 的排查清单。
深分页的代价：扫描并丢弃
LIMIT offset, n 的执行过程是：先定位满足条件的行，逐行取出（可能回表），丢弃前 offset 行，最后返回 n 行。offset 越大，白扫的行越多，代价近似线性增长。
-- 慢：扫描约 1000020 行，丢弃 1000000 行，只返回 20 行
SELECT id, title, created_at FROM article ORDER BY id LIMIT 1000000, 20;
执行量拆解
├─ 扫描行数 ≈ 1000020
├─ 回表次数 ≈ 1000020（若走的是二级索引）
├─ 返回行数 = 20
└─ 有效利用率 ≈ 0.002%
优化方向只有两个：减少扫描行数，或减少回表次数。
优化一：延迟关联
先用覆盖索引只查主键（不回表），取出 20 个 id 后再回表拿详情：
-- 快：子查询在索引上完成，最后只回表 20 次
SELECT a.id, a.title, a.created_at
FROM article a
JOIN (SELECT id FROM article ORDER BY id LIMIT 1000000, 20) t ON a.id = t.id;
前提是排序字段上有索引；ORDER BY 的列没有索引时，子查询自己就要全表扫描并排序。
优化二：游标分页
让客户端记住上一页最后一条的游标值，下一页用它做范围查询，offset 恒为 0：
-- 第一页
SELECT id, title FROM article ORDER BY id DESC LIMIT 20;
-- 后续页：lastId 取上一页最后一条的 id
SELECT id, title FROM article WHERE id < 1000000 ORDER BY id DESC LIMIT 20;
代价是不能随机跳页，适合“下拉加载更多”。若排序列不唯一（例如按创建时间），用组合游标保证不重不漏：
SELECT id, title FROM article
WHERE (created_at, id) < ('2024-05-01 10:00:00', 1000000)
ORDER BY created_at DESC, id DESC LIMIT 20;
优化三：覆盖索引
查询涉及的列全部包含在索引中，就无需回表，“扫一百万行”的成本会低一个数量级：
ALTER TABLE article ADD INDEX idx_status_id (status, id);
EXPLAIN SELECT id FROM article WHERE status = 1 ORDER BY id LIMIT 1000000, 20;
-- 输出 Extra: Using where; Using index （Using index 即覆盖索引，不回表）
不要为了覆盖把大字段塞进索引，索引过宽会拖慢写入并占用更多内存。
优化四：业务层限制
- 限制最大翻页数（如只允许翻到第 100 页），超出引导用户增加筛选条件。
- 搜索场景做深分页降级：超过阈值只返回前 N 页，并提示收窄条件。
- 冷数据统计与导出走离线任务或搜索引擎，不要压在线库。
联合索引与最左前缀
联合索引 (a, b, c) 相当于先按 a、再按 b、再按 c 排序，因此条件必须从最左列开始连续匹配。
ALTER TABLE orders ADD INDEX idx_uid_status_ctime (user_id, status, created_at);
| 查询条件 | 能否用上索引 | 说明 |
|---|---|---|
WHERE user_id=? | 能 | 命中第 1 段 |
WHERE user_id=? AND status=? | 能 | 连续命中前 2 段 |
WHERE status=? | 不能 | 跳过最左列 |
WHERE user_id=? ORDER BY status | 能 | 排序同样遵循最左前缀 |
WHERE user_id=? AND created_at>? | 部分 | user_id 用于定位，created_at 无法继续剪枝 |
范围条件会中断后续列的索引使用，因此通用写法是：等值条件放前面，范围条件放后面。
索引失效清单
| 写法 | 是否失效 | 原因与改法 |
|---|---|---|
WHERE DATE(created_at)='2024-05-01' | 失效 | 列上有函数，改成 created_at>='2024-05-01' AND created_at<'2024-05-02' |
varchar 列写 WHERE user_id = 1001 | 失效 | 隐式类型转换等价于对列做函数，入参类型要与列一致 |
WHERE name LIKE '%张' | 失效 | 前导通配符无法利用有序性，尽量改成 LIKE '张%' |
WHERE a=1 OR b=2（b 无索引） | 可能失效 | OR 两侧都要能走索引，否则退化为全表扫描 |
WHERE status != 0 | 通常失效 | 区分度低时优化器直接选择全表扫描 |
WHERE age + 1 > 18 | 失效 | 列参与运算，改成 age > 17 |
WHERE user_id IN (SELECT ...) | 视优化器而定 | 可能走不上索引，改写为 JOIN 更稳定 |
判断标准一句话：索引列是否以“裸列”形式出现在可比较的位置上。
EXPLAIN 关键列解读
EXPLAIN SELECT id, title FROM article WHERE status = 1 ORDER BY id LIMIT 20;
| 列 | 关注点 |
|---|---|
| type | 访问类型：const > eq_ref > ref > range > index > ALL，出现 ALL 基本是全表扫描 |
| key | 实际使用的索引，为 NULL 表示没走索引 |
| rows | 预估扫描行数，深分页时这里会是几十万甚至上百万 |
| filtered | 过滤后剩余比例，过低说明条件区分度差 |
| Extra | Using index 覆盖索引；Using filesort 额外排序；Using temporary 用到临时表 |
深分页的判断口诀：rows 很大 + key 只用到主键 + Extra 里没有 Using index，基本可确定是“扫描丢弃”造成的。
小结：深分页的本质是扫描量而非数据量，优先改用游标分页，其次用延迟关联与覆盖索引；再配合最左前缀规则与失效清单排查索引问题，最后用 EXPLAIN 的 type、rows、Extra 三列做确认。