Mysql--基础知识点--94.1--嵌套子查询转关联查询

什么是嵌套子查询为什么要改成关联查询嵌套子查询Non-correlated subquery是指子查询独立于外层查询不引用外层的列。它先执行子查询得到结果集然后外层查询利用这个结果集进行过滤或计算。关联子查询Correlated subquery是指子查询引用了外层查询的列对外层每一行都要执行一次子查询通常可以利用索引快速判断。在某些场景下将嵌套子查询改写成关联查询如EXISTS或JOIN可以大幅提升性能避免子查询产生巨大的中间结果集或者避免NULL带来的逻辑陷阱。典型改写场景IN→EXISTS原始嵌套子查询使用IN-- 找出所有下过至少一单的客户SELECT*FROMcustomersWHEREcustomer_idIN(SELECTcustomer_idFROMorders-- 子查询独立不依赖外层);执行逻辑先完整执行子查询从orders表中取出所有customer_id可能几十万行。将结果集去重并构建哈希表。对customers每一行检查customer_id是否在哈希表中。问题如果orders表非常大几百万行子查询结果集巨大会消耗大量内存和 I/O。改写为关联查询使用EXISTSSELECT*FROMcustomers cWHEREEXISTS(SELECT1FROMorders oWHEREo.customer_idc.customer_id-- 关联条件);执行逻辑实际优化器会做半连接遍历customers表假设只有几千行。对每个客户在orders表的索引上快速查找是否存在该客户的订单找到一行就立即返回TRUE不再继续扫描。整个过程无需物化庞大的订单 ID 集合。优势当外表小、内表大时EXISTS通常远快于IN。避免了大结果集的物化内存压力小。可以利用orders.customer_id上的索引。另一种改写嵌套子查询 →JOIN关联查询-- 同样查询有订单的客户使用 JOIN注意去重SELECTDISTINCTc.*FROMcustomers cJOINorders oONc.customer_ido.customer_id;注意JOIN可能导致客户重复一个客户有多笔订单所以需要DISTINCT。如果customers.customer_id是主键DISTINCT的开销通常可接受。性能特点数据库优化器可能将IN或EXISTS转换为类似的JOIN执行计划。但显式JOIN有时更灵活可以同时获取订单表的其他字段。什么时候不应该改写子查询结果集很小如几十行且不经常执行IN写法更直观性能差异可忽略。需要判断NOT IN且子查询无NULL但最好还是用NOT EXISTS或LEFT JOIN避免NULL陷阱。子查询是标量查询返回单个值例如SELECT ... WHERE salary (SELECT AVG(salary) FROM employees)这种无法简单改成关联查询因为子查询只需执行一次。总结对照表写法子查询类型执行次数适用场景IN (SELECT ...)嵌套非关联子查询执行1次子查询结果集小且外表大EXISTS (SELECT ... WHERE 关联)关联外表每行执行1次但可提前终止外表小内表大且内表有索引JOIN ... ON 关联关联一次连接操作需要同时获取两表数据注意去重核心建议默认优先考虑语义清晰但遇到性能问题时把嵌套子查询特别是IN和NOT IN改写成关联查询EXISTS或JOIN是非常有效的优化手段。使用EXPLAIN观察执行计划确认数据库是否自动做了优化。