如何在SQL中用子查询实现高效的分页与总行数统计-SQL-PHP中文网

子查询分页比offset/limit更难控制，因其依赖严格唯一、非空、有索引的游标字段（如id或(created_at,id)），且order by顺序必须与游标条件一致；mysql 5.7需用变量或派生表兼容，而“先count再limit”存在数据一致性问题。

如何在sql中用子查询实现高效的分页与总行数统计

子查询分页为什么比 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 条，体验断裂。

真正可控的解法只有两个方向：