后端深分页查询性能问题怎么优化

结论：后端深分页慢的根因是数据库仍要扫描并丢弃 OFFSET 之前的所有行，优化优先级是先用游标分页（WHERE 排序键 > 上一页最后一条），再用延迟关联和覆盖索引，最后才考虑预计算或 Elasticsearch。

深分页为什么越翻越慢？

结论：LIMIT 1000000, 20 的代价与 OFFSET 成正比，InnoDB 要定位到第 1000000 条记录后才取 20 条，实际扫描 1000020 行。

深分页指 OFFSET 很大的分页查询，例如 SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000000, 20。如果 created_at 没有索引，MySQL 还要做 filesort，把全表排序后再截取。即使有索引，二级索引也要逐条回表或扫描索引，OFFSET 越大，丢弃的行越多。用 EXPLAIN 看 rows 字段，通常会接近 offset + limit 的数量。

游标分页怎么做？

结论：游标分页把“第几页”换成“上一页最后一条记录”，查询代价从 O(offset + limit) 降到 O(limit)。

游标分页（键集分页）的第一页正常查：

SELECT id, user_id, amount, created_at
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

返回结果时带上最后一条的 created_at 和 id 作为 next_cursor。下一页查询：

SELECT id, user_id, amount, created_at
FROM orders
WHERE (created_at, id) < ('2024-01-01 00:00:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

需要联合索引 (created_at, id)。如果排序是升序，把 < 换成 >。排序键必须唯一，用 (created_at, id) 兜底，单用 created_at 遇到相同时间会丢数据或重复。游标分页不支持直接跳页，适合 feed 流、订单列表、日志列表。

不能改跳页时怎么优化？

结论：业务必须随机跳页时，用延迟关联把回表次数压到 LIMIT 条数，例如从 20 行回表而不是 1000020 行。

延迟关联（先在索引上分页取主键，再回表）写法：

SELECT o.*
FROM orders o
JOIN (
  SELECT id
  FROM orders
  ORDER BY created_at DESC
  LIMIT 1000000, 20
) t ON o.id = t.id
ORDER BY o.created_at DESC;

子查询只查 id，如果存在 (created_at, id) 覆盖索引，覆盖索引指查询需要的列全部在索引中，不需要回表。这样子查询扫描 1000020 个索引条目，但只回表 20 次。还可以把 SELECT * 改成只取需要的列，减少网络和内存开销。

哪些场景必须换存储或预计算？

结论：OFFSET 达到百万级且要求随机跳页，MySQL 单表直接扛不住，应上预计算分页表或 Elasticsearch。

预计算方案：每 1000 条记录写一行 page_no 到 start_id 的映射表，查询第 1000 页时先查 page_no=1000 拿到 start_id，再用 WHERE id >= start_id ORDER BY id LIMIT 20。Elasticsearch 用 search_after 加 PIT，每页 20 条，把上一页最后一条的 sort 值传给下一页，避免 from + size 超过 10000 的限制。同时限制最大页数，例如只允许 page * size <= 10000，超出后引导用户加时间、状态等筛选条件。

具体排查和落地步骤是什么？

结论：按“看执行计划、补索引、改 SQL、改接口、加监控”五步走。

1. 用 EXPLAIN 看 type、rows、Extra，出现 Using filesort 或 Using temporary 先处理排序。
2. 确认 ORDER BY 字段有联合索引，顺序与排序一致。例如 WHERE status=1 ORDER BY created_at DESC，索引建 (status, created_at, id)。
3. 把 SELECT * 改成明确列名，把 LIMIT 1000000, 20 改成 WHERE id > last_id LIMIT 20。
4. 接口层返回 next_cursor，去掉 page 参数，或设置 page_size 上限 100。
5. 打开慢查询日志，设置 long_query_time=0.1，监控超过 100ms 的分页 SQL。

常见坑：联合索引顺序写错；游标排序键不唯一；用 OFFSET 做全量导出。导出数据用 WHERE id > last_id ORDER BY id LIMIT 1000 循环拉取，每次 1000 条，直到没有数据。