覆盖索引+子查询能提速几十倍，因其将“查全字段+跳过n行”拆为两步：先用索引快速定位id（不回表），再用id精准拉取数据；b+树索引只存键值，故select id...limit仅遍历索引页，i/o极小。
LIMIT offset, size 在偏移量超过 10 万后，性能断崖式下跌，不是索引没建好，而是 MySQL 必须扫描并丢弃前 offset 行——这个行为无法靠索引跳过，只能绕开。
为什么覆盖索引 + 子查询能提速几十倍
核心在于把“查全字段 + 跳过 N 行”拆成两步：先用索引快速定位要哪几行的 id（只走索引，不回表），再用这些 id 精准拉取完整数据。B+ 树索引本身不存非键字段，所以 SELECT id FROM t ORDER BY created_at LIMIT 1000000, 20 只需遍历索引页，I/O 极小。
常见错误现象包括：
-
EXPLAIN显示rows高达百万级，但Extra是Using index却依然慢——说明瓶颈不在索引查找，而在后续回表 - 加了
WHERE type = ?后速度无改善，因为排序和偏移逻辑未变
实操建议：
- 子查询中只
SELECT id，且ORDER BY字段必须有索引（如INDEX idx_created_at (created_at)） - 主查询用
JOIN关联原表，确保ON a.id = b.id中的a.id是主键或有索引（主键天然满足） - 绝对不要在子查询里写
SELECT *或其他非索引字段，否则失去覆盖索引意义
游标分页（WHERE + 上一页末值）不适用时才用这招
当业务要求支持任意页码跳转（比如用户手动输入“第 872 页”）、或无法维护上一页最后一条记录的值时，覆盖索引 + 子查询是唯一可行的高性能兜底方案。
使用场景：
- 后台导出任务按批次拉取（如每批 5000 条），offset 固定递增
- 管理后台“跳转到指定页”功能，且数据实时性要求不高
- 无法改造前端交互、又必须保留传统分页控件的老系统
注意：IN 子查询不可用——MySQL 不允许在 IN 里直接写带 LIMIT 的子查询；必须用 JOIN。
实际 SQL 写法与关键细节
假设表 orders 有主键 id，时间字段 created_at 已建索引：
SELECT o.* FROM orders o JOIN ( SELECT id FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 1000000, 20 ) tmp ON o.id = tmp.id;
参数差异与影响：
- 若排序字段是
updated_at且存在重复值，LIMIT 1000000, 20结果可能不稳定（因相同时间戳的行顺序不确定），此时应在ORDER BY中追加id消除歧义：ORDER BY updated_at DESC, id DESC - 子查询中的
WHERE条件必须和主查询一致，否则关联后数据错乱 - 该方案对内存压力略高（临时表存 20 个
id微乎其微），但磁盘 I/O 下降显著——实测从 3.2 秒降到 0.18 秒（500 万数据，offset=100 万）
真正容易被忽略的是：这个优化只对“必须用 offset 跳转”的场景有效；一旦业务允许滚动加载，优先用 WHERE id > ? 游标，它比任何子查询都更轻量、更稳定、也更难出错。
