大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！
写SQL写久了就会发现，同样的业务需求，不同的人写出来，执行效率可能差几十倍。尤其是在复杂统计、排名分组这些场景，选错写法，查询能从毫秒级变成分钟级。以前做运营的时候，我只需要看懂Excel里的公式；转行后才明白，SQL优化的本质是“用更少的扫描、更少的临时表完成同样的事”。今天就把子查询、CTE、窗口函数这三者的性能差异彻底讲透。
这两年MySQL 8.0普及之后，CTE和窗口函数不再是“新特性”，而是每个写SQL的人必须掌握的技能。很多以前只能用子查询或临时表硬扛的需求，现在有了更优雅高效的解法。理解这三种写法的性能特性，能让你在面对复杂报表时少走弯路。
先花一分钟搞懂三个概念
- 子查询：把一个查询的结果作为另一个查询的条件或数据源。优点是直观，缺点是相关子查询（外层每行执行一次内层）性能极差。
- CTE（公共表表达式）：可以理解为一个命名的临时结果集，只在当前查询中有效。提升可读性，还能支持递归查询（比如组织架构树）。
- 窗口函数：在保留原行数据的基础上，对一组行进行聚合或排名计算。不会像GROUP BY那样压缩行数，非常适合“每行后面加一个汇总值”的场景。
实测对比：每种写法最适合什么场景
| 场景 | 推荐写法 | 原因 |
|---|---|---|
| 简单过滤（查某个用户的最新订单） | 窗口函数 或 CTE+JOIN | 一次扫描完成，效率高 |
| 多步复杂逻辑，需要分步写 | CTE | 可读性最好，便于调试 |
| 每行后面跟一个汇总值 | 窗口函数 | 不改变行数，一次扫描 |
| 递归查询（树形、BOM） | 递归CTE | 唯一可行的标准写法 |
| EXISTS / IN | 半连接子查询 | 优化器能处理好 |
| 相关子查询 | 改写成JOIN或窗口函数 | 相关子查询通常较慢 |
真实数据：1000万行表，查每个客户的最新订单
环境：MySQL 8.0.32，8C32G，表orders有索引(order_date, customer_id)
❌ 相关子查询：耗时12.5秒
SELECT * FROM orders o1
WHERE order_date = (SELECT MAX(order_date) FROM orders o2 WHERE o2.customer_id = o1.customer_id);
✅ 窗口函数：耗时3.8秒
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as rn
FROM orders
) t WHERE rn = 1;
✅ CTE+JOIN：耗时4.2秒
WITH latest AS (
SELECT customer_id, MAX(order_date) as max_date
FROM orders GROUP BY customer_id
)
SELECT o.* FROM orders o JOIN latest l ON o.customer_id = l.customer_id AND o.order_date = l.max_date;
结论：相关子查询最慢，窗口函数和CTE+JOIN都在4秒左右。以后遇到“每组取最值”的需求，优先用窗口函数。
排名统计场景：按部门计算员工工资排名
10万行员工表，按部门内工资排名。
窗口函数（RANK）：0.9秒，代码一行
SELECT *, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as rnk FROM emp;
自连接旧写法：7.2秒，SQL复杂难懂
SELECT e1.*, COUNT(DISTINCT e2.salary) as rnk
FROM emp e1 LEFT JOIN emp e2 ON e1.dept_id = e2.dept_id AND e1.salary <= e2.salary
GROUP BY e1.id;
窗口函数在排名、累计、移动平均等场景下，性能和简洁度都是碾压级别。
递归CTE实际运用：查询组织架构树
需求：部门表dept(id, parent_id, name)，查询id=1及其所有下级。
WITH RECURSIVE dept_tree AS (
SELECT id, parent_id, name FROM dept WHERE id = 1
UNION ALL
SELECT d.id, d.parent_id, d.name FROM dept d
INNER JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree;
一次查询搞定，这是MySQL 8.0之前很难优雅实现的。
一点体会
从运营转行做DBA之后，我最深的感受是：写SQL和做运营数据分析本质上都是“从数据里找答案”，但SQL优化要求你更懂底层机制。窗口函数和CTE不是新语法，它们是工具包里最趁手的两把扳手。遇到复杂统计，第一反应应该是“能不能用窗口函数”，而不是“先写个子查询凑合用”。掌握这三者，不是为了炫技，而是让查询跑得更快、代码更容易维护。
小耶在手，SQL 不愁
还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~
