SQL 深度分页的优化方案

深度分页性能差本质是需扫描跳过大量数据，优化核心是避免OFFSET全量扫描，推荐游标分页（基于主键/时间戳）、覆盖索引+子查询、分表归档、分页缓存等方案。

深度分页（比如 OFFSET 1000000 LIMIT 20）在 mysql、postgresql 等主流数据库中性能急剧下降，本质是数据库仍需扫描并跳过前 100 万行。优化核心思路是：避免 OFFSET 的全量扫描，改用基于有序字段的“游标分页”或覆盖索引+子查询等更高效方式。

用主键/唯一有序字段做游标分页（推荐）

适用于数据按主键或时间戳等单调递增字段排序、且前端支持“上一页/下一页”而非跳转任意页的场景。例如按 id 升序分页：

• 第一页查：select * FROM orders ORDER BY id LIMIT 20，记下最后一条的 id = 12345
• 下一页查：SELECT * FROM orders WHERE id > 12345 ORDER BY id LIMIT 20
• 优势：不依赖 OFFSET，走索引范围扫描，响应稳定在毫秒级
• 注意：不能直接跳转第 1000 页；若需跳转，可先用 SELECT id FROM orders ORDER BY id LIMIT 1 OFFSET 999999 获取锚点 ID（仍慢），再用游标查，但仅限低频操作

利用覆盖索引 + 子查询减少回表（MySQL 常用）

当必须用 OFFSET 时，先通过覆盖索引快速定位主键，再关联原表取完整数据：

• 假设查询 SELECT id, name, created_at FROM users ORDER BY created_at LIMIT 20 OFFSET 100000
• 优化写法：SELECT u.* FROM users u INNER JOIN (SELECT id FROM users ORDER BY created_at LIMIT 20 OFFSET 100000) t ON u.id = t.id
• 原理：子查询只查索引字段（如 created_at + id 组合索引），体积小、扫描快；外层 JOIN 避免大量回表
• 前提：必须有 (created_at, id) 这类联合索引，且 id 在索引末尾（保证排序稳定性）

物理分表或归档冷数据

对历史数据占比高、访问集中在近期的业务（如订单、日志），深度分页慢往往是因为单表过大：

• 按时间归档：将 6 个月前订单移入 orders_archive 表，主表保持百万级规模
• 按 ID 分表：如 users_001 ~ users_100，配合路由规则，让单表数据量可控
• 效果：即使保留 OFFSET，因单表变小，100 万 offset 的耗时可能从 5 秒降至 200ms
• 注意：需配套修改应用层分页逻辑，避免跨分片排序分页

缓存分页结果（适合读多写少场景）

对固定条件（如“按销量排行 TOP 10000”）的深度分页，可预计算并缓存结果：

• 后台定时任务生成分页快照：SELECT id FROM products ORDER BY sales DESC，按每页 20 条切分成 redis 中的 zset 或 hash
• 用户请求第 50000 页时，直接查 ZRANGE products_top_sales 999980 999999 拿 ID 列表，再批量查详情
• 适用：排行榜、热门列表等业务；不适用实时性要求高或排序字段频繁变更的场景