什么是correlated subqueries?为何应尽可能避免使用这类子查询?
普通非相关子查询是完全独立于外层查询的逻辑单元,数据库引擎会一次性执行完所有子查询逻辑,把结果作为固定常量/结果集传给外层查询使用,和外层的逐行遍历没有依赖关系。
而相关子查询的核心特征是:内层子查询的执行条件、取值逻辑,依赖外层查询当前遍历行的字段值,根本无法脱离外层查询独立执行。
举个典型的相关子查询代码示例:
-- 典型的相关子查询写法 SELECT e1.emp_id, e1.dept_id FROM employees e1 WHERE e1.salary > ( -- 内层子查询直接引用了外层e1表的dept_id字段,无法单独执行 SELECT AVG(e2.salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id )
上述SQL执行时,引擎会逐行扫描e1的记录,每读一行,就取当前行的dept_id值,向内层子查询传入这个值,计算对应部门的平均薪资,再和当前行的薪资做大小判断,决定这行是否符合WHERE条件。
关于“相关子查询运行速度慢”的认知求证
你之前了解到的“数据库查询要尽可能规避相关子查询、原因是其运行速度慢”的认知是完全正确的,但这个结论的本质不是相关子查询的语法本身有性能缺陷,而是这类写法在绝大多数开发场景下,会带来无意义的、远高于常规写法的性能开销,甚至很多时候是开发者无意中写出的低效逻辑。
相关子查询带来性能损耗的核心技术原因
- 执行次数的无意义放大:相关子查询的执行次数和外层查询的扫描行数完全绑定,外层扫多少行,内层逻辑就要执行多少次。如果外层表是百万行级别的大表,相关子查询就会被触发上百万次,哪怕每次内层执行可以走索引,累计的CPU调度、磁盘IO开销也远高于“一次性算完全部内层结果、再和外层做一次关联”的非相关写法。比如刚才的部门平均薪资例子,非相关的CTE写法只需要对员工表做一次全扫描算出所有部门的平均,再和员工表做一次等值关联,总共2次表扫描;换成相关子查询写法的话,在没有完美索引兜底的场景下,累计执行的IO量会比前者高几个数量级。
- 优化器的改写局限性:虽然当前主流商业、开源数据库的优化器已经支持自动把部分简单相关子查询改写为等价的JOIN/聚合逻辑,但这种改写不是100%覆盖的——尤其是嵌套多层、带复杂聚合、带EXISTS/NOT EXISTS判断的相关子查询,很多时候优化器无法识别到最优执行路径,会退化成逐行判断的嵌套循环模式,直接拉低整体查询性能。
- 性能问题的高排查成本:很多刚接触SQL的开发者写相关子查询时,根本没意识到内层逻辑会被逐行重复触发,比如在内层逻辑里加了全表扫描、无索引函数计算这类重操作,最后导致查询响应时间从百毫秒级掉到几十秒甚至超时,排查定位问题的时间成本远高于一开始就用更可控的CTE/JOIN写法。
补充说明:相关子查询不是需要完全禁止的写法,比如数据库能正确识别的EXISTS半连接场景,用相关子查询的写法性能和显式JOIN没有差异,甚至逻辑更简洁。大家所说的“尽可能规避”,特指不要把本来可以一次性计算完成的聚合、关联逻辑,硬写成依赖外层逐行触发的相关子查询,这类写法带来的性能损耗是完全没有必要的。
内容的提问来源于stack exchange,提问作者throwawayeconboi
相关产品推荐
相关产品推荐

