数据库查询优化：子查询与CTE对比
在数据库查询优化的世界里，子查询与公用表表达式（CTE）是两种常用的SQL写法。它们都能处理复杂逻辑，但性能与可读性差异显著。本文将深入对比两者，帮助开发者根据场景选择最优方案。
子查询：传统方案与潜在陷阱
子查询是嵌套在SELECT、FROM或WHERE语句中的查询。例如，要找出订单金额大于平均值的客户，子查询写法为：SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE amount > (SELECT AVG(amount) FROM orders));。这种写法直观，但存在两大问题。
性能瓶颈：重复执行与索引失效
数据库优化器可能对子查询进行重复执行。尤其在WHERE子句中使用相关子查询（子查询依赖外层数据）时，每行外层数据都会触发一次子查询扫描。例如，SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products p2 WHERE p2.category = products.category);会导致逐行计算平均值，索引利用率低下。此外，子查询在嵌套过深时，优化器可能放弃使用索引，转而全表扫描。
可读性困境：嵌套混乱与调试困难
多层嵌套的子查询难以阅读和调试。若业务逻辑包含多个聚合或条件，例如“找出最近30天购买过商品且订单金额排名前10%的用户”，嵌套子查询会变得臃肿：SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE order_date > ... AND user_id IN (SELECT ...))。这种代码在团队协作中容易引发理解错误。
CTE：现代SQL的清晰与性能优势
CTE通过WITH关键字定义临时结果集，将复杂查询分解为模块。例如，上述平均值的查询可改写为：WITH avg_amount AS (SELECT AVG(amount) AS avg_val FROM orders) SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders, avg_amount WHERE orders.amount > avg_amount.avg_val);。CTE的主要优势体现在两方面。
模块化设计：提升可读性与复用性
CTE将逻辑拆分为命名片段，每个片段可独立测试。例如，对于“按产品类别计算销售额排名”的场景，CTE写法清晰：WITH category_sales AS (SELECT category, SUM(sales) AS total FROM products GROUP BY category) SELECT * FROM category_sales ORDER BY total DESC;。这种结构便于后期维护：若需添加过滤条件，只需修改对应CTE块，而非重写整个查询。
性能优化：优化器友好与一次计算
数据库优化器对CTE更友好。同一CTE在查询中多次引用时，优化器通常只计算一次结果（部分数据库如PostgreSQL支持物化CTE），避免重复扫描。例如，在“统计本月新增用户及其订单数”中，CTE写法：WITH new_users AS (SELECT * FROM users WHERE created_at > '2023-10-01') SELECT nu.id, COUNT(o.id) FROM new_users nu LEFT JOIN orders o ON nu.id = o.user_id GROUP BY nu.id;，优化器可先计算new_users并缓存，再连接订单表。相比之下，子查询版本可能每次引用时重新计算，导致性能下降。
关键区别：何时选择子查询或CTE？
简单查询：子查询更轻量
对于单层、无重复引用的过滤条件，子查询更简洁。例如，SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');，子查询仅执行一次，且优化器可高效利用索引。此时CTE会引入额外语法开销，无实际收益。
复杂逻辑：CTE是首选
当查询涉及递归、多层聚合或多次引用临时结果时，CTE优势突出。例如，递归CTE可轻松处理树形结构（如组织架构）：WITH RECURSIVE org_tree AS (SELECT id, name, parent_id FROM employees WHERE parent_id IS NULL UNION ALL SELECT e.id, e.name, e.parent_id FROM employees e JOIN org_tree ot ON e.parent_id = ot.id) SELECT * FROM org_tree;。子查询无法实现递归，且在多处引用时会导致代码冗余。
性能权衡：依赖数据库版本与优化器
不同数据库对CTE的物化策略不同。Oracle默认将CTE视为内联视图，可能多次计算；而SQL Server和PostgreSQL支持物化提示（如MATERIALIZED）。建议在查询优化中测试两种写法：使用EXPLAIN分析执行计划，对比扫描次数与并行度。子查询在简单场景中可能产生更优计划，但CTE在复杂查询中更可控。
总结：根据场景选择优化策略
数据库查询优化没有银弹。子查询适合单次、轻量过滤，代码简洁；CTE则在多步计算、递归和可读性上胜出，尤其适用于报表统计、数据ETL等复杂业务。实际开发中，建议遵循“先可读，后优化”原则：先用CTE构建清晰逻辑，再通过EXPLAIN分析性能。若子查询版本执行计划更优且无重复计算，可保留；否则优先选择CTE。最终，理解优化器行为并针对性调整，才是数据库查询优化的核心。