LIMIT offset, size 在大数据量下变慢是因为需扫描前 offset 行；子查询通过覆盖索引跳过扫描，提升性能5–50倍；但需确保子查询单行返回，推荐物化中间结果；游标分页更稳定高效，适用于生产环境。
为什么 LIMIT offset, size 在大数据量下越来越慢
因为 MySQL（尤其是 InnoDB）执行 LIMIT 100000, 20 时，仍要扫描前 100000 行才能定位到结果起点——这些行不返回，但全得走一遍索引或主键 B+ 树，CPU 和 I/O 成本都涨得快。
子查询在这里不是“多此一举”，而是用来跳过扫描过程：先用覆盖索引快速定位第 N 条记录的主键值，再以该值为边界做二次查询。
- 适用场景：主键或唯一有序字段（如
id、created_at）可作为游标 - 不适用场景：排序字段含大量重复值（如
status），或ORDER BY涉及多列且无联合索引支持 - 性能差异明显：10 万偏移量下，子查询方案常比
LIMIT快 5–50 倍，取决于索引选择性和数据分布
SELECT * FROM t WHERE id > (SELECT id FROM t ORDER BY id LIMIT 100000, 1) ORDER BY id LIMIT 20 的陷阱
这个写法看似简洁，但实际会出错：Subquery returns more than 1 row —— 因为 LIMIT 100000, 1 在没有 ORDER BY 确保唯一顺序时，可能因索引松散或优化器行为返回非确定结果；更常见的是，当 id 有间隙或被删过，子查询可能查不到第 100001 条的 id，导致外层查不到任何数据。
- 必须确保子查询只返回一行：加
ORDER BY id+LIMIT 1是基础，但还不够 - 推荐改用
(SELECT MIN(id) FROM (SELECT id FROM t ORDER BY id LIMIT 100001) AS tmp)，强制物化中间结果，避免优化器绕过排序 - 如果排序字段不是主键（比如按
created_at分页），记得给该字段建索引，否则子查询本身就会全表扫
用游标替代偏移量才是生产环境主流做法
所谓“游标分页”，就是不再依赖数字偏移量，而是记住上一页最后一条记录的排序字段值（例如上页最后的 id = 123456），下一页查 WHERE id > 123456 ORDER BY id LIMIT 20。子查询只在初始化游标（比如跳转到第 100 页）时才需要。
- 优点：每次查询都是范围索引查找，复杂度稳定 O(log n)，不受总数据量影响
- 缺点：无法直接跳转任意页码（如“去第 876 页”），需先查出对应游标值；前端需隐藏传统页码，改用“下一页/上一页”或“加载更多”
- 兼容性注意：MySQL 5.7+ 支持，但某些 ORM（如旧版 Django ORM）默认生成
LIMIT offset，需手动重写查询或切换分页插件
PostgreSQL 和 MySQL 的子查询写法差异
MySQL 要求子查询带别名（哪怕只是 AS tmp），否则报错 Every derived table must have its own alias；而 PostgreSQL 允许省略，但用 LATERAL 更清晰。更重要的是，MySQL 不支持在子查询中引用外层表字段（相关子查询受限），所以游标值必须由应用层传入，不能靠子查询动态关联。
- MySQL 安全写法：
SELECT * FROM t WHERE id > (SELECT id FROM (SELECT id FROM t ORDER BY id LIMIT 100001) AS tmp ORDER BY id DESC LIMIT 1) ORDER BY id LIMIT 20 - PostgreSQL 可简写：
SELECT * FROM t WHERE id > (SELECT id FROM t ORDER BY id OFFSET 100000 LIMIT 1) ORDER BY id LIMIT 20 - 别漏掉
ORDER BY：子查询和外层都必须显式声明，否则结果不可预测
真正难的不是写出子查询，而是判断什么时候该放弃“页码思维”——只要用户不硬要输“第 N 页”，游标分页几乎总是更稳、更快、更易扩展。偏移量分页只适合后台管理类低频、小数据场景。
