子查询分页比OFFSET/LIMIT更难控制，因其依赖严格唯一、非空、有索引的游标字段（如id或(created_at,id)），且ORDER BY顺序必须与游标条件一致；MySQL 5.7需用变量或派生表兼容，而“先COUNT再LIMIT”存在数据一致性问题。
子查询分页为什么比 OFFSET/LIMIT 更难控制
直接用 OFFSET + LIMIT 分页在大数据量下会变慢，因为数据库仍要扫描跳过的所有行；而用子查询实现“游标式”分页（如基于主键或唯一排序字段）能跳过扫描，但必须确保排序字段严格唯一、非空、有索引。常见错误是拿 created_at 这类可能重复的字段做游标，导致漏行或重复——尤其高并发写入时。
实操建议：
- 优先用自增主键
id或带唯一约束的时间戳+ID组合（如(created_at, id)）作为游标字段 - 分页查询必须带
ORDER BY，且顺序与游标条件严格一致（升序分页就用>，降序就用） - 避免在子查询里用
SELECT *，只查分页所需字段 + 游标字段，减少传输和内存开销
用子查询同时拿到总行数和当前页数据
想一次查出总行数和第 N 页数据，不能简单套两层子查询拼 COUNT(*) 和 LIMIT，因为多数数据库（如 MySQL 5.7、PostgreSQL）不支持在同一个 SELECT 中混用聚合和非聚合字段，除非用 GROUP BY 或窗口函数。更可靠的做法是用 WITH（CTE）或派生表预计算总行数。
示例（PostgreSQL）：
WITH paginated AS ( SELECT id, name, created_at FROM users WHERE id > 1000 ORDER BY id LIMIT 20 ), total AS ( SELECT COUNT(*) AS total_count FROM users ) SELECT p.*, t.total_count FROM paginated p CROSS JOIN total t;
注意：CROSS JOIN 在这里安全，因为 total 只有一行；若用 MySQL 5.7（不支持 CTE），得改用派生表或额外执行一次 SELECT COUNT(*)。
MySQL 5.7 下子查询分页的兼容写法
MySQL 5.7 不支持 CTE 和窗口函数，想在单条语句里兼顾分页和总数，只能靠相关子查询或双重嵌套。但相关子查询（如在 SELECT 列中写 (SELECT COUNT(*) FROM ...)）会导致每行都执行一次全表扫描，性能灾难。
更可行的方案是：先查总数，再用变量模拟游标分页（需确保事务隔离级别足够，避免幻读）：
SELECT @total:=COUNT(*) FROM users; SELECT id, name FROM users WHERE id > (SELECT id FROM users ORDER BY id LIMIT 1 OFFSET 40) ORDER BY id LIMIT 20;
关键点：
- 第一句赋值给用户变量
@total，后续可复用 - 子查询
(SELECT id FROM users ORDER BY id LIMIT 1 OFFSET 40)是获取第 41 行的游标值，但OFFSET在大偏移量时依然慢——所以它只适合中小数据集 - 生产环境更推荐应用层缓存总数，或用近似值（如
SHOW TABLE STATUS的Rows字段）代替精确统计
为什么「先 COUNT 再 LIMIT」不是真高效
很多人以为“先执行 SELECT COUNT(*) FROM t WHERE ...，再执行 SELECT ... LIMIT”就够了，但问题在于：两次查询之间数据可能变更，总数和分页结果不一致。尤其在订单列表、评论流等场景，用户看到“共 128 条”，点下一页却只剩 127 条，体验断裂。
真正可控的解法只有两个方向：
- 用快照隔离（如 PostgreSQL 的
REPEATABLE READ）让两次查询看到同一份数据版本 - 放弃精确总数，改用“是否有下一页”的布尔判断（如查 21 行，只显示前 20，第 21 行存在即表示还有下一页）
后者在无限滚动场景中更实用，也彻底绕开了总数统计的性能与一致性难题。
