★ FEATURED ARTICLE 很多人在写SQL的时候只要看到两张表关联脑子里蹦出来的第一个念头就是用JOIN觉得JOIN是关系型数据库的正统写法嵌套子查询天然就矮一头。这个直觉在大部分场景下确实没错但如果你把它当成铁律迟早会在某个慢查询上栽跟头。我见过不少线上事故追到最后发现是开发同学把一个本该用嵌套子查询过滤的场景硬写成了JOIN结果笛卡尔积放大、临时表爆掉、执行计划走偏一条查询从几十毫秒拖到几秒。也见过反过来的情况明明一个EXISTS就能搞定的事非要套三层子查询优化器直接放弃治疗。所以MySQL where嵌套和join谁效率高这个问题真不是一句看情况就能打发的。它背后牵扯的是优化器的执行路径选择、驱动表与被驱动表的顺序、索引的可用性、结果集的大小估算以及MySQL版本演进带来的策略变化。我打算把这个问题拆开揉碎讲清楚从执行计划的底层逻辑讲到具体场景的选型判断再给几组可以直接对照的实测结论。不管你是刚接触SQL的新手还是写过几年业务查询的老手看完应该都能对什么时候该用哪种写法有一个清晰的判断标准而不是继续凭感觉拍脑袋。1. 先把两种写法的执行路径摊开看要判断效率高低得先搞清楚MySQL拿到这两种SQL之后到底在内部做了什么。很多人比较效率的方式是跑一下看谁快但数据量、索引、缓存状态一变结论就翻车。真正靠谱的做法是理解执行路径知道优化器在每种写法下有哪些选择、哪些限制。1.1 JOIN的物理执行驱动表决定一切MySQL的JOIN在物理层面基本只有一种核心算法值得关注就是嵌套循环连接Nested Loop JoinNLJ。它的逻辑非常朴素先选一张表作为驱动表从驱动表里读出一行然后拿着这行的关联字段去被驱动表里找匹配的行找到就拼成结果找不到就跳过接着读驱动表的下一行。整个过程就像两层for循环。这个模型直接推导出一个关键结论驱动表的结果集大小决定了整个查询的工作量。如果驱动表扫出10万行被驱动表每次关联都要走一次索引查找那就是10万次索引查找如果驱动表只有100行那就是100次。差距是三个数量级。那驱动表是谁选的MySQL优化器会根据统计信息估算各表的过滤后行数通常倾向于把小结果集的表放在前面当驱动表。但这里有个坑优化器的估算依赖统计信息而统计信息可能是过期的、采样不准确的。尤其是当WHERE条件里用了函数、类型隐式转换、或者范围条件跨度很大时估算值可能离谱到把大表当成小表直接导致执行计划灾难。-- 假设 orders 有 500 万行customers 有 10 万行 -- 优化器大概率选 customers 做驱动表 SELECT o.id, o.amount, c.name FROM orders o JOIN customers c ON o.customer_id c.id WHERE c.city 某城市;上面这条如果c.city的过滤性很好比如只剩几百个客户那驱动表就是几百行被驱动表走orders.customer_id的索引几百次查找很快。但如果c.city几乎覆盖全表驱动表变成10万行那就是10万次索引查找性能立刻变脸。所以JOIN的效率高度依赖驱动表够不够小这个前提。1.2 WHERE嵌套子查询的两种命运半连接还是物化嵌套子查询的情况更复杂因为MySQL对它的处理策略不止一种。以最常见的IN子查询为例SELECT id, amount FROM orders WHERE customer_id IN ( SELECT id FROM customers WHERE city 某城市 );MySQL 5.6之后引入了半连接Semi-join优化优化器会把这种IN子查询转换成多种等价形式来执行常见的有物化Materialization先把子查询结果算出来存进一张临时表可能还会给临时表建索引然后外层查询拿customer_id去临时表里查。这种策略适合子查询结果集不大、且外层能利用临时表索引的情况。半连接转换把子查询拉平成JOIN的形式然后按NLJ执行本质上和手写JOIN殊途同归。EXISTS转换把IN改写成EXISTS的语义逐行判断子查询是否有匹配。关键在于这些策略是优化器自动选的你写的是嵌套跑出来的可能是JOIN。这就是为什么很多人做对比测试时发现嵌套和JOIN差不多快——因为优化器已经把嵌套改写成了JOIN。真正拉开差距的是那些优化器无法有效改写的场景比如子查询里带了聚合、LIMIT、或者关联了外层字段相关子查询。1.3 相关子查询优化器的噩梦相关子查询Correlated Subquery指的是子查询内部引用了外层的列导致子查询不能独立执行一次而是外层每读一行就要执行一次子查询。这种写法在语义上非常自然但在执行上往往是性能杀手。-- 相关子查询外层每行都要执行一次子查询 SELECT o.id, o.amount FROM orders o WHERE o.amount ( SELECT AVG(o2.amount) FROM orders o2 WHERE o2.customer_id o.customer_id );上面这条查询外层orders每读一行内层就要对该客户的所有订单求一次平均值。如果外层有100万行内层平均每个客户50单那就是5000万次聚合计算。这种场景下用JOIN改写先按客户分组算出平均值再关联回订单表通常能快一到两个数量级因为JOIN版本只需要对数据扫一遍做分组而不是逐行触发子查询。提示判断一个子查询是不是相关子查询看子查询的WHERE里有没有引用外层的表别名。有就是相关的执行代价通常随外层行数线性甚至超线性增长。2. 索引可用性才是效率的真正分水岭聊到这儿其实已经能看出一个规律写法本身不决定效率写法能否让索引发挥作用才决定效率。JOIN和嵌套子查询在索引利用上的差异才是选型的核心依据。这一节我把几种典型情况拆开讲。2.1 JOIN的索引要求被驱动表的关联字段必须有索引NLJ算法下被驱动表每次关联都要做一次查找。如果关联字段上有索引这次查找是B树定位代价是O(log n)如果没有索引那就是全表扫描代价是O(n)。驱动表有m行总代价就是m×log n还是m×n的区别天壤之别。-- 被驱动表 orders 的 customer_id 必须有索引 SELECT c.name, o.amount FROM customers c JOIN orders o ON o.customer_id c.id WHERE c.city 某城市;如果orders.customer_id没索引MySQL会退化成对被驱动表做全表扫描而且这个扫描是每读一行驱动表就扫一遍。500万行的orders表驱动表几百行那就是几百次全表扫描基本等于把数据库打死。所以JOIN写法的第一铁律是被驱动表的关联列必须建索引。反过来驱动表的过滤列如果有索引能快速缩小驱动结果集也是加分项。但驱动表即使走全表扫描只要结果集不大代价也可控。所以JOIN的索引优化重点永远在被驱动表。2.2 嵌套子查询的索引要求子查询的过滤列和关联列嵌套子查询的索引需求要看优化器选了哪条路。如果走物化策略子查询内部的WHERE过滤列需要索引来快速算出结果集如果走半连接转换那要求就和JOIN一样被驱动表原外层表的关联列要有索引。-- 子查询内部 customers.city 有索引能快速算出结果集 SELECT id, amount FROM orders WHERE customer_id IN ( SELECT id FROM customers WHERE city 某城市 );这里有个容易被忽略的点物化策略下临时表本身可能没有索引。MySQL 5.6之后会给物化临时表自动建索引如果判断有必要但这个判断不一定总对。如果子查询结果集很大临时表没索引外层拿customer_id去临时表里查就是全表扫描性能反而比JOIN差。这也是为什么有些场景下嵌套写法跑不过JOIN——不是嵌套本身慢是物化临时表拖了后腿。2.3 一张对照表看清索引依赖差异维度JOIN写法WHERE嵌套子查询核心索引要求被驱动表关联列必须有索引子查询过滤列需索引物化时临时表索引由优化器决定驱动表选择优化器按统计信息选可能选错半连接转换后同JOIN物化时外层表为驱动索引缺失时的退化被驱动表全表扫描×驱动行数灾难性物化临时表全表扫描或子查询重复执行索引利用可控性高建对索引即可中部分依赖优化器策略选择典型风险驱动表估算错误导致大表驱动相关子查询逐行执行、物化临时表无索引从这张表能看出来JOIN的索引依赖更明确、更可控你只要保证被驱动表关联列有索引基本不会出大问题。嵌套子查询的索引利用则多了一层优化器策略的不确定性可控性稍差。这也是很多DBA在评审SQL时更倾向JOIN写法的原因——不是JOIN天生快是JOIN的行为更可预测。3. 分场景判断什么时候嵌套反而更快前面讲了不少JOIN的优势但如果你就此认定永远用JOIN那就掉进另一个坑了。有几类场景嵌套子查询尤其是EXISTS和IN反而更优甚至JOIN根本写不出等价的高效查询。这一节我把这些场景列清楚。3.1 只需要判断存在性时EXISTS往往胜过JOIN业务里有一类高频需求查有订单的客户或者没下过单的客户。用JOIN写有订单的客户如果客户有多个订单结果会出现重复行得加DISTINCT去重而DISTINCT本身有排序或哈希去重的开销。用EXISTS写语义就是存在即返回找到第一条匹配就短路不产生重复行也不需要去重。-- JOIN写法需要DISTINCT去重 SELECT DISTINCT c.id, c.name FROM customers c JOIN orders o ON o.customer_id c.id; -- EXISTS写法语义清晰无重复可短路 SELECT c.id, c.name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.id );在orders.customer_id有索引的前提下EXISTS对每个客户做一次索引探测找到就停代价是客户数×log(订单数)。JOIN版本则要把所有匹配的订单行都读出来再去重如果平均每个客户几十单读的行数就是客户数×几十再去重。数据量越大EXISTS的优势越明显。这类存在性判断是嵌套写法的主场。3.2 子查询结果集极小且能走覆盖索引时如果子查询能通过覆盖索引快速算出一个很小的结果集比如几十上百行物化策略的代价极低外层再拿这个结果集去过滤整体可能比JOIN还快。因为JOIN即使驱动表小也要处理连接逻辑和可能的排序而物化一次算完外层直接拿临时表做等值查找路径更短。-- 假设某城市只有几十个VIP客户且(city, id)有联合索引 SELECT id, amount FROM orders WHERE customer_id IN ( SELECT id FROM customers WHERE city 某城市 AND level VIP );这种场景下子查询走(city, level)的覆盖索引几十行结果物化成临时表外层orders.customer_id有索引整体非常轻快。硬改成JOIN也能跑但没必要嵌套写法可读性更好性能也不吃亏。3.3 需要反连接语义时NOT EXISTS比LEFT JOIN更安全查没下过单的客户是经典的反连接场景。用LEFT JOIN ... WHERE 右表列 IS NULL能实现但有几个坑一是IS NULL的判断依赖右表关联列非空如果关联列本身允许NULL逻辑会出错二是优化器对LEFT JOIN IS NULL的优化不如NOT EXISTS直接三是可读性差容易写错。-- LEFT JOIN反连接注意关联列不能为NULL SELECT c.id, c.name FROM customers c LEFT JOIN orders o ON o.customer_id c.id WHERE o.customer_id IS NULL; -- NOT EXISTS反连接语义直接优化器处理更成熟 SELECT c.id, c.name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.id );MySQL对NOT EXISTS有专门的反连接优化Anti-join能高效执行。而LEFT JOIN IS NULL虽然也能被优化器识别成反连接但在复杂查询里识别失败的概率更高。所以反连接场景我一般优先推荐NOT EXISTS。3.4 场景选型速查表业务语义推荐写法理由两表关联取明细字段JOIN索引可控行为可预测判断存在性有/无关联记录EXISTS / NOT EXISTS可短路无重复行免去重子查询结果集极小且走覆盖索引IN子查询物化代价低可读性好反连接查无关联记录NOT EXISTS优化器支持成熟避免NULL陷阱子查询含聚合且需关联外层改写为JOIN分组避免相关子查询逐行执行多表关联且过滤条件复杂JOIN为主谨慎用嵌套优化器对JOIN的路径选择更丰富这张表不是让你死记而是提供一个判断起点。真正落地时还是要结合执行计划验证。4. 用EXPLAIN把结论落到实处前面讲的都是原理和场景但SQL优化这件事永远不能只靠推理必须看执行计划。同一句SQL在不同数据分布、不同索引、不同MySQL版本下执行计划可能完全不同。这一节我讲怎么用EXPLAIN读懂两种写法的真实执行路径以及几个关键的判断信号。4.1 重点看哪几列EXPLAIN输出列很多但判断JOIN和嵌套的效率重点盯这几列type访问类型从好到坏大致是system const eq_ref ref range index ALL。出现ALL就是全表扫描要警惕。key实际使用的索引。如果是NULL说明没走索引。rows优化器估算的扫描行数。这个值是估算不一定准但数量级有参考价值。Extra额外信息。Using temporary用临时表、Using filesort文件排序、Using join buffer用连接缓冲说明被驱动表没走索引都是需要关注的信号。EXPLAIN SELECT c.name, o.amount FROM customers c JOIN orders o ON o.customer_id c.id WHERE c.city 某城市;跑出来如果被驱动表orders那行的type是ref、key是customer_id索引说明走的是索引查找健康。如果type是ALL、Extra里有Using join buffer说明被驱动表没走索引正在用连接缓冲做块嵌套循环性能堪忧得赶紧补索引。4.2 嵌套子查询的EXPLAIN要看物化痕迹嵌套子查询的EXPLAIN输出里如果看到select_type是MATERIALIZED说明优化器选了物化策略子查询结果被存进了临时表。这时候要关注临时表那行的rows估算以及外层查询是否用上了临时表的索引。EXPLAIN SELECT id, amount FROM orders WHERE customer_id IN ( SELECT id FROM customers WHERE city 某城市 );如果输出里出现subquery2这样的表名且select_type是MATERIALIZED就是物化路径。如果select_type是SIMPLE且两表直接关联说明优化器把IN改写成了半连接JOIN这时候就按JOIN的标准看索引。提示MySQL 8.0可以用EXPLAIN ANALYZE看实际执行耗时和真实行数比传统EXPLAIN的估算值靠谱得多。排查慢查询时优先用它。4.3 一个真实的排查链路我遇到过一个典型案例某业务查询用嵌套子查询写平时几十毫秒某天数据量涨上来后突然变成3秒。排查过程是这样的先跑EXPLAIN发现子查询走了物化临时表rows估算有80万行外层查询对临时表的访问type是ALL也就是全表扫描临时表。分析原因子查询的过滤条件过滤性变差了结果集从几千涨到80万物化临时表太大优化器没给临时表建索引或者建了但没用上外层每次查找都扫临时表。改写方案把嵌套改成JOIN让orders.customer_id索引直接发挥作用避免物化临时表这个中间环节。改写后EXPLAIN显示被驱动表走ref索引耗时回到几十毫秒。这个案例说明嵌套写法的风险点往往在物化临时表。当子查询结果集不可控地变大时物化策略会从优势变成劣势。而JOIN写法没有这个中间环节索引直接作用在基表上更稳。4.4 优化器提示的谨慎使用MySQL提供了STRAIGHT_JOIN、JOIN_ORDER提示、/* ... */优化器提示等工具可以强制指定驱动表或连接顺序。有些同学发现优化器选错驱动表后直接加提示硬掰。我的建议是慎用因为提示是写死的数据分布一变原本正确的提示可能变成新的性能瓶颈。优先通过更新统计信息ANALYZE TABLE、调整索引、改写SQL来引导优化器实在不行再用提示兜底并且要在注释里写清楚为什么加、什么条件下该复查。5. 几个容易踩的坑和实操心得原理和工具都讲完了最后分享几个我在实际工作中反复遇到的坑。这些点教科书上不一定写但踩过一次就忘不掉。5.1 IN子查询里的NULL陷阱NOT IN子查询如果子查询结果里包含NULL整个查询会返回空结果集。这是SQL的三值逻辑导致的x NOT IN (1, 2, NULL)等价于x ! 1 AND x ! 2 AND x ! NULL而x ! NULL的结果是UNKNOWN整个条件永远不成立。这个坑极其隐蔽因为数据里偶尔混进一条NULL查询就静默地返回空业务方还以为没数据。-- 如果子查询的id列可能为NULL这条查询可能返回空 SELECT * FROM orders WHERE customer_id NOT IN (SELECT id FROM customers WHERE city 某城市);规避方法有两个一是子查询里加WHERE id IS NOT NULL二是改用NOT EXISTS它对NULL不敏感语义也更清晰。我现在的习惯是只要写反连接一律用NOT EXISTS从源头上绕开这个坑。5.2 子查询里的LIMIT和ORDER BY容易被忽略如果子查询里带了ORDER BY但没带LIMITMySQL优化器通常会忽略这个排序因为对IN/EXISTS的语义没影响白白浪费一次排序开销。而如果带了LIMIT子查询就变成了非确定性子查询优化器无法把它改写成半连接只能老老实实物化或者逐行执行性能可能骤降。-- ORDER BY无LIMIT排序被忽略白费开销 SELECT * FROM orders WHERE customer_id IN ( SELECT id FROM customers WHERE city 某城市 ORDER BY id ); -- 带LIMIT无法半连接改写只能物化 SELECT * FROM orders WHERE customer_id IN ( SELECT id FROM customers WHERE city 某城市 ORDER BY id LIMIT 100 );所以子查询里没事别加ORDER BY除非你确实需要LIMIT配合。需要取TopN再关联的场景老老实实用JOIN或者派生表别硬塞进IN子查询。5.3 统计信息过期是执行计划跑偏的头号元凶优化器选驱动表、选物化还是半连接全靠统计信息估算。如果表数据变动频繁但没及时ANALYZE TABLE统计信息严重滞后优化器可能把10万行估成100行把大表当小表驱动执行计划直接崩。我处理过的慢查询里相当一部分不是SQL写法问题而是统计信息过期。实操建议对数据量变化大的表定期跑ANALYZE TABLEMySQL 8.0可以调整information_schema_stats_expiry控制统计信息缓存时长开启innodb_stats_persistent让统计信息持久化避免重启后重新采样导致计划突变。这些配置层面的东西往往比纠结JOIN还是嵌套更能解决实际问题。5.4 别在SELECT列表里写子查询有一种写法比WHERE嵌套更隐蔽把子查询放在SELECT列表里。-- 每行都执行一次子查询性能杀手 SELECT o.id, o.amount, (SELECT c.name FROM customers c WHERE c.id o.customer_id) AS customer_name FROM orders o;这种写法外层每读一行就要执行一次子查询。虽然关联列有索引时单次代价不高但行数一多就是灾难。正确做法是用JOIN把customers关联进来一次连接搞定。这个坑在ORM框架自动生成的SQL里特别常见排查慢查询时一定要留意SELECT列表里有没有藏子查询。5.5 实测对比的正确姿势最后说下怎么做靠谱的对比测试。很多人对比两种写法直接在生产库上各跑一次看耗时这不可靠因为缓存状态、并发负载、数据分布都在变。我的做法是在测试环境用相同的数据快照保证数据分布一致。每种写法跑之前先FLUSH STATUS或重启实例清缓存或者至少跑三次取稳定值。用EXPLAIN ANALYZE看真实执行行数和耗时而不是只看总耗时。分别测试小结果集和大结果集两种数据分布因为结论可能相反。记录Handler_read_*状态变量的变化看实际读了多少行比耗时更能反映本质。只有控制住这些变量对比结论才有意义。否则你测出来的JOIN快或嵌套快可能只是缓存碰巧命中了。6. 我的选型判断顺序讲了这么多最后把我自己的判断顺序梳理一下供参考。拿到一个查询需求我一般按这个顺序走先看业务语义。如果是取两表关联的明细字段直接JOIN别犹豫索引建好就行。如果是判断存在性或反连接优先EXISTS/NOT EXISTS语义清晰且能短路。如果是子查询结果集极小且能走覆盖索引IN子查询完全可以用可读性还更好。再看数据量和索引。子查询结果集可能很大时警惕物化临时表膨胀倾向改JOIN。被驱动表关联列没索引时先补索引而不是换写法——没索引的情况下换什么写法都救不了。相关子查询一律考虑改写成JOIN分组除非数据量确实很小。最后看执行计划。写完SQL必须EXPLAIN验证重点看type有没有ALL、Extra有没有Using temporary和Using join buffer。计划不对就调索引、更新统计信息、改写SQL实在不行再考虑优化器提示。整个过程里写法只是手段让索引高效发挥作用才是目的。说到底嵌套和JOIN谁效率高这个问题没有标准答案但有标准判断方法看语义、看索引、看执行计划。把这三步走扎实比记住任何一条XX写法更快的结论都管用。我在实际项目里见过太多因为迷信某一种写法而翻车的案例也见过把两种写法用对场景后性能提升十几倍的情况。SQL优化这件事永远是具体问题具体分析原理吃透了判断自然就准了。
阅读完成 · 觉得有帮助？
