直接在JOIN后加LIMIT分页会导致性能断崖式下降，因为数据库需先生成完整关联结果集（可能含大量冗余行），再扫描并丢弃前N行；正确做法是先用子查询或CTE对驱动表分页取ID，再关联详情，确保关联仅作用于目标行。
为什么不能直接在JOIN后加LIMIT做分页
因为 JOIN 会先生成笛卡尔积（或满足ON条件的中间结果集），再执行 LIMIT。当关联大表时，即使最终只要10条，MySQL仍可能扫描数万行驱动表+被驱动表的组合，I/O和CPU开销巨大。常见现象是：分页越靠后（如 LIMIT 10000, 20），查询越慢，甚至触发 Using temporary; Using filesort。
如何让驱动表先LIMIT再JOIN
核心思路是把驱动表的分页逻辑“提前固化”——用子查询或CTE先把驱动表要的ID/主键范围取出来，再拿这些ID去关联合并。这样被驱动表只被访问对应行数，而非全表扫描。
- 推荐写法（适用于驱动表有主键/唯一索引）：
SELECT a.*, b.name FROM ( SELECT id FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100 ) AS a JOIN users b ON a.user_id = b.id;
- 如果驱动表排序字段无索引，
ORDER BY created_at本身就会变慢，必须确保该字段有索引 - 避免用
SELECT *在子查询里——只选id或必要字段，减少临时表体积 - MySQL 8.0+ 可用 CTE 替代内联子查询，语义更清晰，但执行计划通常一致：
WITH page_ids AS ( SELECT id FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100 ) SELECT o.*, u.name FROM page_ids p JOIN orders o ON p.id = o.id JOIN users u ON o.user_id = u.id;
什么时候这个优化会失效
不是所有 JOIN 都适合套这层子查询。关键看驱动表能否高效完成分页——即 ORDER BY + LIMIT 是否能走索引。
- 如果驱动表
ORDER BY字段没索引，子查询本身就要全表扫描+排序，优化就白做了 - 关联条件中用了函数或表达式（如
ON YEAR(o.created_at) = u.join_year），会导致无法使用被驱动表索引 - 被驱动表连接字段缺失索引（如
users.id是主键没问题，但users.email没索引还拿来JOIN，就会回表或全表扫） - 子查询返回的ID集合过大（比如
LIMIT 100000, 100），内存中暂存ID可能引发性能抖动，此时应考虑游标分页（WHERE created_at ）
对比：LEFT JOIN场景下怎么处理
LEFT JOIN 的驱动表固定为左表，所以“先LIMIT再JOIN”的逻辑依然成立，但要注意 NULL 值对结果行数的影响——子查询只取左表ID，JOIN后可能因右表无匹配而产生NULL字段，行数不会变少，但数据完整性需业务侧确认。
- 安全写法：
SELECT o.*, u.name FROM ( SELECT id, user_id FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100 ) AS o LEFT JOIN users u ON o.user_id = u.id;
- 别在子查询里
SELECT *：LEFT JOIN 不保证右表字段非空，但左表字段必须精简，尤其避免带大文本字段（如order_detailJSON） - 若业务要求“必须有用户信息”，那就该用 INNER JOIN，而不是强行 LEFT 后过滤
u.id IS NOT NULL—— 这会让优化器放弃索引下推
实际执行前务必用 EXPLAIN 对比原始SQL和优化后SQL的 rows、type 和 Extra 字段。最容易被忽略的是：子查询看似简单，但如果驱动表数据倾斜严重（比如某 user_id 占了80%订单），被驱动表的单次JOIN仍可能查出大量重复数据，这时候需要结合业务做冗余字段或物化路径预计算。
