数据库查询优化：子查询与CTE对比
数据库查询优化是提升系统性能的关键环节。在SQL中，子查询和CTE（公用表表达式）是两种常见的写法，但它们的效率差异常让开发者困惑。本文通过对比分析，揭示何时选择子查询、何时使用CTE更能优化查询性能。
子查询：灵活但可能拖慢性能
子查询是将一个查询嵌套在另一个查询的WHERE、FROM或SELECT子句中。例如，查找订单金额超过平均值的客户时，子查询会先计算平均值，再过滤主查询。这种写法直观，但数据库优化器可能无法高效处理嵌套逻辑。
当子查询返回大量数据时，性能问题尤为明显。比如在IN子句中使用子查询，数据库需要反复执行内部查询，导致资源消耗飙升。数据库查询优化建议：对于简单过滤或关联子查询，可尝试改写为JOIN以减少嵌套层级。
子查询在数据量较小时表现尚可，但面对复杂业务场景，嵌套层次过深会导致执行计划难以优化，甚至引发全表扫描。此时，CTE可能成为更优选择。
CTE：可读性与性能的平衡点
CTE使用WITH子句定义临时结果集，语法更接近人类逻辑。例如，递归查询层级结构（如组织架构）时，CTE能清晰表达“从根节点逐层展开”的流程。数据库查询优化中，CTE的优势在于可复用：同一临时结果集可在后续查询中多次引用，避免重复计算。
性能方面，CTE通常被数据库视为一次性视图。现代数据库优化器（如PostgreSQL、SQL Server）能对CTE进行优化，仅在需要时执行具体逻辑。但需注意：CTE不会自动创建索引，若临时结果集过大，仍需配合其他优化手段。
对于多步骤查询，CTE能将复杂逻辑拆解为独立模块，既提升可维护性，又为优化器提供更多调整空间。例如，先过滤数据再聚合，避免主查询处理冗余行。
场景对比：何时选择子查询或CTE？
简单过滤：子查询更直接
如果只需一次计算（如“查询价格高于平均值的商品”），子查询写法紧凑，数据库优化器能快速生成执行计划。此时CTE反而增加语法开销。
递归或多次引用：CTE更高效
处理树形数据（如分类层级、员工汇报链）时，递归CTE是标准方案。子查询无法直接实现递归，需借助临时表或游标，性能远不如CTE。另外，若同一临时结果需在多个地方使用（如先计算部门总销售额，再关联员工和产品表），CTE能避免重复计算。
大数据量复杂查询：CTE配合索引优化
当子查询嵌套超过两层，或主查询与子查询关联字段无索引时，CTE的可拆解性有助于定位瓶颈。例如，将耗时的子查询单独定义为CTE，再对CTE结果创建临时索引（某些数据库支持），可显著加速后续操作。
数据库查询优化的关键：理解执行计划
无论选择子查询还是CTE，最终性能取决于数据库的查询计划。使用EXPLAIN命令分析查询：若子查询被优化为JOIN或半连接，性能可能不输CTE；若CTE被多次物化（如MySQL早期版本），则可能不如子查询。数据库查询优化建议：在真实数据集上测试，观察扫描行数、内存使用和IO开销。
另外，避免在WHERE子句中使用函数包裹字段（如WHERE DATE(create_time)='2023-01-01'），这会阻止索引使用。同时，为关联字段建立复合索引，能同时提升子查询和CTE的性能。
总结来说，子查询适合简单一次性过滤，CTE适合递归、多步骤或需要复用临时结果的场景。数据库查询优化的核心不是选型，而是理解数据特征和优化器行为。通过分析执行计划、合理设计索引，并权衡可读性与性能，才能在真实业务中做出高效决策。