深度分页性能差因offset需全量扫描跳过前n行；优化应改用游标分页，即基于上一页最后排序值查询，要求排序字段唯一且有联合索引，实现稳定毫秒级响应。
sql分页查询看似简单，但面试中常被用来考察对索引、执行计划、数据分布和性能瓶颈的理解。尤其是“深度分页”（如 OFFSET 1000000 LIMIT 20）场景，直接使用 OFFSET 会导致性能断崖式下降——数据库仍需扫描并跳过前100万行。真正有效的优化不是换写法，而是换思路。
为什么 OFFSET 越大越慢？
mysql/postgresql 等主流数据库执行 LIMIT offset, size 时，并不会“直接跳到第N行”，而是：先按排序条件全量扫描、逐行计数，直到累计跳过 offset 行，再取后续 size 行。即使有索引，B+树仍需回表或遍历大量索引节点；若排序字段无索引，还会触发 filesort，复杂度接近 O(N log N)。
常见误区：
– 认为加了主键索引就一定快（错：ORDER BY create_time 却只在 id 上建索引，无效）
– 用子查询“提前 limit”骗优化器（如 select * FROM t WHERE id IN (SELECT id FROM t ORDER BY id LIMIT 1000000,20)），实际可能更慢，且不通用
核心优化策略：游标分页（Cursor-based Pagination）
放弃“第几页”的思维，改用“上一页最后一条的排序值”作为下一页起点。要求：排序字段唯一且有索引（推荐组合：时间 + 主键，如 create_time DESC, id DESC）。
- 第一步：建联合索引
例如分页字段是create_time和id，执行：CREATE INDEX idx_time_id ON orders (create_time DESC, id DESC); - 第二步：查询改写
第一页：SELECT * FROM orders ORDER BY create_time DESC, id DESC LIMIT 20;
后续页（假设上一页最后一条是create_time='2024-05-01 10:20:30', id=88923）：SELECT * FROM orders WHERE (create_time, id) - 优势：索引范围扫描（range），跳过所有无关行，响应时间稳定在毫秒级，与总数据量无关
无法用游标？退而求其次的实用技巧
当业务强制要求“跳转任意页码”（如后台管理查第500页），或排序字段不唯一（如多个订单同秒创建），可结合以下方法缓解：
- 延迟关联（Deferred Join）：先用覆盖索引快速定位ID，再回表取完整数据
SELECT o.* FROM orders o INNER JOIN (SELECT id FROM orders ORDER BY create_time DESC LIMIT 1000000, 20) AS tmp ON o.id = tmp.id;
原理：子查询只走索引，ID体积小，IO大幅降低 - 记录偏移锚点：对超大表预计算“每万行首个ID”，存入缓存（如redis）。查第501万行时，先查锚点得“约500万行处的ID”，再以该ID为起点微调搜索
- 限制前端页码：多数用户不会翻过100页。后端校验
page > 100时直接返回空或提示，避免执行高危SQL
面试加分项：不同数据库的细节差异
MySQL 8.0+ 支持窗口函数，可用 ROW_NUMBER() 配合 CTE 实现逻辑分页（适合小结果集）；PostgreSQL 的 cursor 原生支持服务端游标，内存友好；而 clickhouse 等列存数据库，OFFSET 在大数据量下几乎不可用，必须用分区键+排序键双重过滤。
关键提醒：没有银弹方案。是否用游标，取决于业务能否接受“只能前后翻页、不能跳页”。技术选型前，务必用真实数据量 + 慢查询日志 + EXPLAIN ANALYZE 验证效果。