SQL性能优化：子查询、CTE与窗口函数对比实战

在数据库开发中，一个普遍存在的现象是：针对相同的业务需求，不同开发者编写的SQL语句，其执行效率可能相差巨大。特别是在处理复杂统计、分组排名等场景时，不同的写法选择可能导致查询耗时从毫秒级骤增至分钟级。这背后的核心在于，如何以更少的资源扫描数据、避免产生不必要的中间结果来完成查询任务。本文将深入对比子查询、公共表表达式（CTE）和窗口函数这三种技术的性能差异及其适用场景。

随着MySQL 8.0的广泛使用，CTE和窗口函数已从“新特性”转变为高效SQL开发的必备技能。许多过去只能依赖嵌套子查询或临时表硬编码实现的复杂需求，如今有了更优雅、更高效的解决方案。理解这三种写法的性能特点，能帮助开发者在构建复杂报表和进行数据分析时，有效规避性能陷阱。

核心概念解析：子查询、CTE与窗口函数

• 子查询：指将一个查询的结果作为另一个查询的条件或数据源。其优点是逻辑直观，易于理解。但“相关子查询”（即外层查询的每一行都需要执行一次内层查询）是其性能瓶颈，通常会导致效率低下。
• CTE（公共表表达式）：可将其视为一个命名的临时结果集，仅在当前查询中有效。它能显著提升复杂查询的可读性和可维护性，并且支持递归查询，这对于处理组织架构树、物料清单（BOM）等层次化数据至关重要。
• 窗口函数：这类函数能在保留原数据行明细的基础上，对一组相关的行进行聚合或排名计算。它不会像GROUP BY那样将多行合并为一行，因此特别适用于“为每一行附加汇总值”或“在分组内进行排序”的场景。

性能对比实测：不同场景下的最佳实践

应用场景	推荐写法	核心原因
简单过滤（如查询用户最新订单）	窗口函数 或 CTE+JOIN	通常只需一次数据扫描，效率高
多步骤复杂逻辑，需分步编写	CTE	可读性最佳，便于分段调试
为每行附加分组汇总值	窗口函数	不改变结果行数，一次扫描完成
递归查询（树形结构、BOM）	递归CTE	标准SQL中最为优雅和可行的写法
使用EXISTS或IN的子查询	半连接子查询	现代查询优化器通常能良好处理
相关子查询	改写成JOIN或窗口函数	相关子查询通常性能较差

实战测试：千万级订单表查询最新订单

为直观对比性能，我们在一个包含1000万行记录的`orders`表上进行测试。测试环境为MySQL 8.0.32，表上已建立`(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;

测试结果清晰表明：传统的相关子查询性能最差，耗时长达12.5秒；而窗口函数和CTE+JOIN的写法效率接近，均在4秒左右完成。因此，在处理“按组取最新或最值”这类需求时，应优先考虑使用窗口函数。

排名统计场景：部门内员工工资排名

再看一个排名场景。在一个10万行的员工表中，需要计算每个部门内部的工资排名。

使用窗口函数（RANK）仅需0.9秒，且代码非常简洁：

SELECT *, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as rnk FROM emp;

若使用旧式的自连接写法，不仅SQL逻辑复杂难懂，耗时也飙升至7.2秒：

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应用：查询组织架构树

对于层次化数据的查询，递归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;

通过递归CTE，可以一次性、清晰地完成树形结构的遍历，这在此前版本的MySQL中难以优雅实现。

总结与SQL优化建议

数据分析与数据库开发的核心是从数据中获取洞察。而SQL优化要求开发者必须深入理解数据库的执行机制。窗口函数和CTE不应被视为炫技的语法，它们更像是工具箱中高效、专业的趁手工具。

面对复杂的统计需求时，良好的习惯是先思考“这个问题能否用窗口函数解决？”，而非下意识地套用子查询。掌握子查询、CTE和窗口函数三者的特性与适用边界，根本目的不是为了写出更复杂的SQL，而是为了让查询执行得更快，让代码更易于理解和维护。在性能至关重要的生产环境中，这种技术选型直接决定了系统的响应速度与用户体验。

参考文献

1. MySQL官方文档：《Window Functions》，《Common Table Expressions》
2. 《高性能MySQL》第4版，第8章