join和cte本身不提速，但用对了能避开执行计划爆炸、回表放大、内存溢出三类硬伤；关键在于索引优化、条件下推、物化控制及执行计划验证。
JOIN 和 CTE 本身不提速，但用对了能避开执行计划爆炸、回表放大、内存溢出这三类硬伤。
为什么 JOIN 一多就变慢
数据库优化器面对多个 JOIN 时，可能无法准确估算中间结果集大小，导致选错驱动表或连接算法。尤其当某张表没索引、或 JOIN 条件含函数（如 ON DATE(created_at) = DATE(NOW())），就会触发全表扫描+嵌套循环，100 万行 × 100 万行 = 1 万亿次比较。
- 确保每个
ON字段都有索引，复合索引按“等值条件列在前、范围条件列在后”排列，例如(user_id, created_at) - 避免在
ON或WHERE中对索引列用函数、类型转换、!=或OR（除非所有分支都命中索引） - 大表 JOIN 前先过滤：把
WHERE status = 'active'写进子查询或 CTE，别留到外层 - PostgreSQL/MySQL 8.0+ 可用
EXPLAIN ANALYZE看实际行数 vs 预估行数，偏差超 5 倍就要怀疑统计信息过期
CTE 不是语法糖，它控制物化时机
CTE 在 PostgreSQL 和 SQL Server 中默认不物化（即被内联展开），和写成子查询几乎等价；但在某些场景下加 MATERIALIZED（PostgreSQL 12+）或强制临时表（SQL Server 的 SELECT INTO #tmp）反而更稳——特别是当同一个 CTE 被多次引用，且原始数据量大、过滤强时。
- 不要默认认为 WITH 就比子查询快；先看执行计划里是否出现
CTE Scan或Materialize节点 - MySQL 5.7 不支持 CTE，硬写会报错
ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause - 递归 CTE（如组织树、路径展开）必须有终止条件，否则触发
max_recursive_depth限制报错 - 字段命名要显式：CTE 里写
SELECT id AS user_id，别依赖SELECT *，否则跨数据库迁移时列序错位直接导致JOIN失败
什么时候该用子查询而不是 CTE
当逻辑只用一次、且需要对外层 GROUP BY 或 ORDER BY 提前收口时，内联子查询更可控。CTE 更适合“算一次、用多次”或“分步可读”的场景。
- 子查询适合：聚合后立刻
JOIN，例如(SELECT user_id, COUNT(*) FROM logs GROUP BY user_id) l→ 这种写法让优化器清楚知道中间集很小 - CTE 适合：同一份用户活跃数据，既要关联画像，又要关联订单，还要做留存率计算——三个下游都依赖它
- 别在子查询里写
ORDER BY+LIMIT再JOIN，MySQL 5.7 会报错，PostgreSQL 虽允许但LIMIT可能被忽略（除非加OFFSET 0强制物化） - 如果子查询含
UNION ALL，记得每支都加括号并统一列名，否则ON条件可能绑定错列
真正卡住性能的往往不是语法多复杂，而是某张表缺一个 (status, created_at, user_id) 复合索引，或者 CTE 被展开后让优化器误判了 10 倍数据量。执行计划比代码更诚实，别跳过 EXPLAIN 这一步。
