很多人写分页 SQL 时，直觉上会把 LIMIT/OFFSET 直接塞进子查询里，但在 MySQL 中，这种写法既有版本限制，也有结果稳定性和性能上的坑。下面按“能不能用、该怎么写、为什么不推荐滥用”三条线展开，帮你快速判断什么时候能用派生表分页，什么时候应该换方案。
MySQL 子查询里直接写 LIMIT，什么时候会报错？
会，而且这是 MySQL 中非常常见的一类限制。
在 MySQL 5.7 及更早版本里，如果你在 IN、ALL、ANY、SOME 这类子查询中直接使用 LIMIT，系统会报错：
This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
到了 MySQL 8.0+，情况有所放宽，但也不是“任何子查询都能分页”。只有当子查询出现在 FROM 子句中、作为派生表（derived table）使用时，LIMIT 才是允许的，而且必须显式写别名。
根本原因在于：当子查询作为表达式参与逻辑判断时，例如 IN、=、EXISTS，MySQL 不接受带有不确定截断行为的结果集；只有把它当成一个中间结果表放进 FROM，语义才成立。
-- 正确写法
SELECT *
FROM (
SELECT id, name FROM users ORDER BY id LIMIT 10 OFFSET 20
) AS tmp;
-- 错误写法
SELECT *
FROM orders
WHERE user_id IN (
SELECT id FROM users LIMIT 10
);
这里还有一个容易被忽略的点：OFFSET 在子查询中必须配合 ORDER BY 使用，否则“第 21 到第 30 条”本身就没有稳定定义。
用子查询实现分页，兼容性更稳的写法是什么？
最稳妥的思路，不是把分页逻辑硬塞进表达式子查询，而是先把子查询变成派生表，再由外层查询继续筛选或分页。也可以理解为“先生成中间结果，再对中间结果分页”。
这种写法在复杂查询里尤其有用，比如“先找出每个分类下销量前 3 的商品，再把这些商品作为整体结果继续分页”。
先在子查询里完成排序和编号
SELECT category_id, product_id, sales,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rn
FROM products;
这一步的重点不是分页，而是先把业务规则算清楚。像 ROW_NUMBER() 这种窗口函数，必须在子查询内部完成排序和编号。
再包成派生表做筛选
SELECT *
FROM (
SELECT category_id, product_id, sales,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rn
FROM products
) t
WHERE t.rn <= 3;
这一步相当于先拿到“每个分类销量前 3”的中间结果集，再继续使用它。
最后由外层查询统一分页
SELECT *
FROM (
SELECT category_id, product_id, sales,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rn
FROM products
) AS ranked
WHERE rn <= 3
ORDER BY category_id, sales DESC
LIMIT 10 OFFSET 20;
这样写的好处是语义清晰：先得到稳定的业务结果，再明确分页范围。不要把窗口函数的排序和编号留到外层，否则 rn 的含义会被打乱，结果也就不再可靠。
为什么子查询分页常常不是首选？
问题不在“能不能写”，而在“代价通常偏高”。
子查询分页本质上经常是先构造出一个较大的中间结果，再由外层 LIMIT/OFFSET 截取目标页。例如下面这种查询：
SELECT *
FROM (
SELECT ... FROM huge_table
) t
LIMIT 10 OFFSET 10000;
即使最终只返回 10 行，MySQL 往往仍要先扫描前 10010 行才能跳过偏移量。这意味着 OFFSET 越大，I/O 和 CPU 开销越高，而且通常不能像主键范围分页那样直接利用索引跳过数据。
如果把方案按优先级排序，通常可以这样理解：
- 覆盖索引 + 主键范围分页：
WHERE id > last_seen_id LIMIT 10 JOIN预过滤后再分页- 子查询派生表分页
如果业务上确实必须使用子查询分页，那么至少要保证子查询本身带有足够强的过滤条件，例如：
WHERE status = 'active'
这样可以尽量避免把全表都卷进中间结果。
另外，PostgreSQL 在这类场景里通常比 MySQL 更灵活，例如支持 LATERAL 和更友好的 OFFSET 下推；但在 MySQL 中，不能按同样的优化预期来理解执行过程。
子查询里漏掉 ORDER BY，会让分页结果失真
这是分页查询里最容易被低估的问题之一：没有 ORDER BY，就没有可重复、可验证的页边界。
MySQL 不保证无 ORDER BY 的子查询结果顺序，即使表上已经有主键或索引，也不能把物理存储顺序当成稳定排序依据。
例如下面这条 SQL：
SELECT *
FROM (
SELECT id, name FROM users LIMIT 10
) AS t
ORDER BY id;
看起来外层已经按 id 排序，但问题在于：子查询先取出的那 10 条记录本身没有确定顺序。也就是说，外层只是把“不确定拿到的 10 条”重新排了序，并不能保证每次拿到的是同一批数据。这样做很容易导致分页重复、遗漏，甚至同一页多次请求结果不同。
-- 必须写
SELECT *
FROM (
SELECT id, name FROM users ORDER BY id LIMIT 10 OFFSET 20
) AS t;
-- 危险写法
SELECT *
FROM (
SELECT id, name FROM users LIMIT 10 OFFSET 20
) AS t
ORDER BY id;
还有一个兼容性提醒：ORDER BY 使用的字段最好出现在子查询的 SELECT 列表中；除非它本身就是主键且当前引擎、版本支持相关优化，否则某些版本可能直接报错。
归根结底，分页是否可靠，不取决于外层有没有再排序一次，而取决于子查询内部是否先稳定地产出了有序结果集。
实操结论：什么时候该用，什么时候该换方案？
如果你的目标只是普通列表分页，优先考虑基于索引的范围分页，尤其是在大表和高页码场景下，性能会明显更稳定。
如果你的目标是“先算出一个复杂结果，再对结果分页”，例如分组排名、聚合筛选、窗口函数编号，那么派生表是合理选择，但要同时满足几个条件：子查询写在 FROM 中、显式声明别名、内部先写清楚 ORDER BY，再决定是否加 LIMIT/OFFSET。
一句话总结这类问题：MySQL 允许的不是“任意子查询分页”，而是“对有明确定义的派生表结果再分页”。只要把这个边界想清楚，很多语法报错、结果漂移和性能问题都能提前避开。
