MySQL中子查询不能直接用LIMIT分页，需用派生表套一层；总数须用标量子查询、JOIN或窗口函数（MySQL 8.0+）实现，且内外WHERE条件必须一致，ORDER BY须在子查询内，否则分页不可靠。
子查询里不能直接用 LIMIT 做分页，但可以套一层
MySQL 8.0 以前不支持窗口函数时，想在嵌套查询里同时返回数据和总数，得靠两层子查询：外层取分页数据，内层或同级子查询算总数。常见错误是把 LIMIT 和 COUNT(*) 放在同一级 SELECT 里——SQL 会报错，因为聚合和非聚合字段混用且没 GROUP BY。
正确做法是把原始查询包进一个派生表（即子查询），再在外层加 LIMIT 和 OFFSET；总数单独用一个标量子查询或 JOIN 补上：
SELECT t.*, (SELECT COUNT(*) FROM orders WHERE status = 'shipped') AS total_count FROM (SELECT id, user_id, amount FROM orders WHERE status = 'shipped' ORDER BY created_at DESC) AS t LIMIT 20 OFFSET 40;
- 标量子查询
(SELECT COUNT(*) ...)简单，但每次返回一行都会执行一次，大数据量时性能差 - 如果总数逻辑复杂（比如带相同
WHERE条件），建议用JOIN或 CTE（MySQL 8.0+）避免重复计算 -
ORDER BY必须出现在子查询里，否则LIMIT结果不可靠——没有排序的分页等于随机抽样
MySQL 8.0+ 推荐用窗口函数替代嵌套子查询
用 COUNT(*) OVER() 能在不分组的前提下直接附加总数列，语义清晰、执行计划更优，还省去子查询嵌套层级。但要注意：窗口函数不能和 LIMIT 同级共存于同一 SELECT，否则 MySQL 报错 Window function is not allowed in this context。
解决办法是把窗口查询作为内层，再套一层做分页：
SELECT id, user_id, amount, total_count
FROM (
SELECT id, user_id, amount,
COUNT(*) OVER() AS total_count
FROM orders
WHERE status = 'shipped'
ORDER BY created_at DESC
) AS t
LIMIT 20 OFFSET 40;
- 窗口函数里的
OVER()不带参数，才表示全结果集计数；加了PARTITION BY就变成分组计数，不符合“总条数”需求 - 如果
WHERE条件动态变化（比如来自应用层参数），确保内外层过滤条件完全一致，否则total_count和实际返回行数对不上 - PostgreSQL 和 SQL Server 对窗口函数 +
LIMIT的限制更松，但 MySQL 严格要求分层，别图省事压成一层
分页偏移量大时，子查询总数计算变慢怎么办
当 OFFSET 达到几十万，即使只查 20 条，MySQL 仍要扫描前面所有行来跳过——而标量子查询又额外执行一遍全表/索引扫描，双重开销。这不是语法问题，是底层执行机制决定的。
- 用覆盖索引加速总数查询：比如
WHERE status = 'shipped'，就在status字段建索引，让COUNT(*)走索引统计而非全表扫 - 业务能接受近似总数？可查
information_schema.TABLES中的TABLE_ROWS（MyISAM 准确，InnoDB 是估算值） - 真要精确且大数据量分页，放弃
OFFSET，改用游标分页（WHERE id > ? ORDER BY id LIMIT 20），总数单独缓存或异步更新
ORM 框架里写这种嵌套容易漏掉排序或别名
比如 Django 的 Subquery 或 SQLAlchemy 的 select().subquery()，生成嵌套 SQL 时默认不带 ORDER BY，导致分页结果乱序；或者子查询没给别名，MySQL 报错 Every derived table must have its own alias。
- Django 中显式加
.order_by('-created_at')到子查询，且用.alias('t')指定别名 - SQLAlchemy 写
subq = select(...).subquery('t')，然后select(...).select_from(subq)，漏掉't'就失败 - 别名不是可选的——MySQL 强制要求，PostgreSQL 宽松些，但跨数据库迁移时必须统一处理
最常被忽略的是：总数子查询的 WHERE 条件和主查询不一致，比如主查加了时间范围，总数却没加，前端显示“共 1000 条”，实际只返回 20 条且翻不到最后一页。这种 bug 很难测出来，得靠 SQL 日志比对条件。
