做数据库开发这些年被问得最多的一个问题就是性能优化的时候子查询到底该不该改成 JOIN我见过不少人干脆一刀切凡是子查询全部改成 JOIN理由是“JOIN 走索引子查询全表扫”。这种说法听着果断实际翻过车的人都知道坑就埋在“凡是”两个字里。今天我用一张两百万行的订单表和一张十万行的用户表把 PostgreSQL 子查询与 JOIN 的性能对比和优化选择彻底聊明白包括执行计划底层逻辑、实测差异、不能拉升的硬骨头以及一套可复用的判断流程。1. 先把手里的牌看明白子查询有三种JOIN 有三套打法1.1 PostgreSQL 里的子查询从来不是同一种东西很多人把“子查询”当成一个统一概念去比较性能但 PostgreSQL 优化器对待不同形态的子查询处理路径完全不同。这也是网上很多结论自相矛盾的根本原因。按出现位置和返回形态我习惯把子查询分成四类标量子查询返回一行一列出现在 SELECT 列表或 WHERE 的右侧比如WHERE amount (SELECT avg(amount) FROM orders)。集合判断子查询出现在IN、NOT IN、EXISTS、ANY后面本质是判断“外层某行是否属于/匹配这个集合”。派生表出现在FROM子句里比如SELECT * FROM (SELECT ...) t JOIN ...。CTEWITH cte AS (...)本质是带名字的派生表PostgreSQL 12 之后默认会尝试内联展开。它们的性能特征差得很远。一个标量子查询可能只执行一次一个相关集合子查询可能被执行两百万次。把它们混在一起讨论“子查询快还是 JOIN 快”等于问“水果好吃还是蔬菜好吃”答案当然要看具体是什么果、什么菜。1.2 JOIN 的三套执行打法Nested Loop、Hash、MergeJOIN 本身也远不是一个“走索引”就能概括的。PostgreSQL 执行连接时主要会选三种策略优化器根据统计信息估算行数后取它认为最便宜的一种。执行策略适用场景关键前提容易踩的坑Nested Loop驱动表行数少内层表走索引内层连接列必须有可用索引驱动表估算偏大时会演变成灾难Hash Join两表行数都较大等值连接内层结果集能放进 work_memwork_mem 不足会临时落盘速度断崖式下降Merge Join两边已经有序或范围型连接连接列可排序最好有索引支持需要额外排序时内存和 CPU 开销不小很多“子查询慢”的案例本质不是子查询本身的语法问题而是优化器最终选择了 Nested Loop 且内层没有索引很多“JOIN 慢”的案例本质也不是 JOIN 本身的错而是 Hash Join 把临时文件写到磁盘上去了。所以先别急着改 SQL先看懂执行计划里到底走了哪一套打法。1.3 优化器不是翻译官它会把子查询“拉升”成 JOINPostgreSQL 里有个非常关键的过程叫做子查询拉升也叫 subquery pullup / flattening。优化器会尝试把很多子查询重写成等价的连接形式然后在同一个计划空间里做代价比较。比如WHERE customer_id IN (SELECT id FROM customers WHERE level gold)如果条件满足执行计划里很可能直接变成Hash Semi Join也就是“半连接”。NOT IN会尝试变成Hash Anti Join也就是“反连接”。EXISTS同样有机会被改写成半连接。你可以把半连接理解为只关心内层“有没有匹配”一旦匹配到就不再继续找第二个。普通 JOIN 则关心“匹配到了谁”会把所有匹配对都列出来。理解了这一点你会发现网上那些“IN 一定比 EXISTS 慢”“子查询一定比 JOIN 慢”的说法放在今天的 PostgreSQL 里基本都过时了。真正需要关心的是哪些子查询被拉升了哪些没有。2. 同一条业务查询的四种写法实测结果完全不同2.1 测试数据的构造思路为了让对比可复现我造了一组尽可能贴近真实业务的模拟数据10 万个用户200 万条订单用户表中约 1% 是“gold”等级。CREATE TABLE customers ( id int PRIMARY KEY, level text NOT NULL, name text ); CREATE TABLE orders ( id bigint PRIMARY KEY, customer_id int NOT NULL REFERENCES customers(id), amount numeric(12,2), created_at date ); INSERT INTO customers SELECT g, CASE WHEN g % 100 0 THEN gold ELSE normal END, user_ || g FROM generate_series(1, 100000) g; INSERT INTO orders SELECT g, 1 (g % 100000), round((random() * 1000)::numeric, 2), date 2023-01-01 (g % 365) FROM generate_series(1, 2000000) g; ANALYZE customers; ANALYZE orders;造数不重要重要的是ANALYZE 一定不能省。统计信息不刷新优化器全凭猜后面所有对比都会失真。另一个经验是如果条件允许尽量用EXPLAIN (ANALYZE, BUFFERS)而不是只看EXPLAIN因为执行计划展示的是“估算”实际行数、实际循环次数和缓冲区消耗才是判断依据。2.2 IN 子查询与 JOIN 的对比看似等价计划却有微妙差别先看最经典的场景统计 gold 用户的订单总量。EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE level gold);EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM orders o JOIN customers c ON c.id o.customer_id WHERE c.level gold;在我的测试里这两个查询最终都走了 Hash 连接IN 版本显示为 Hash Semi JoinJOIN 版本显示为 Hash Join耗时差距很小。原因是customers.id是主键内层不存在重复值普通 JOIN 不会产生重复行半连接和普通连接的代价差异被抹平了。但这里有一个容易忽略的语义陷阱如果内层子查询结果存在重复值IN 会自动去重JOIN 不会。比如子查询返回了重复的用户 idIN的结果仍然是每个订单只计数一次而改成 JOIN 后同一个订单会因为匹配到两行用户数据而重复输出你不得不再加DISTINCT或GROUP BY来弥补。这已经是改写的正确性问题不是性能问题了。所以我的建议是不要看到 IN 就机械地改成 JOIN。只要执行计划里出现了 Semi Join说明优化器已经帮你处理好了真正需要动的是那些没有出现半连接、反而出现 SubPlan 的查询。2.3 相关标量子查询与 LEFT JOIN最容易被“一刀切”误伤的场景真正让“子查询慢”名声变臭的是相关标量子查询。看这个取用户名的最简单写法EXPLAIN (ANALYZE, BUFFERS) SELECT o.id, (SELECT c.name FROM customers c WHERE c.id o.customer_id) AS customer_name FROM orders o;很多时候这个查询会变成对 orders 的全表扫描每读出一行就执行一次针对 customers 的索引查找。EXPLAIN ANALYZE 里你会看到SubPlan节点内层节点的loops值接近 2000000。两百万次循环哪怕每次只要 0.01 毫秒累计起来也是肉眼可见的慢。改成 LEFT JOIN 通常能把它变成一次 Hash Left JoinEXPLAIN (ANALYZE, BUFFERS) SELECT o.id, c.name FROM orders o LEFT JOIN customers c ON c.id o.customer_id;这个改写的好处是把“每行做一次索引探测”改成“两边各扫一遍然后建哈希表一次匹配”。数据量越大这种改写收益越明显。另一个典型是“按客户聚合”的相关子查询SELECT c.id, (SELECT sum(o.amount) FROM orders o WHERE o.customer_id c.id) AS total FROM customers c;这里外层只有 10 万行但每一行都要去 orders 上做一次 sum 聚合。如果 orders.customer_id 有索引每个客户的开销还能接受如果没有索引那就是每次循环全表扫描两百万行直接打到怀疑人生。改成 JOIN GROUP BY 后一次 Hash Join 加一次 GroupAggregate 就能完成SELECT c.id, sum(o.amount) FROM customers c LEFT JOIN orders o ON o.customer_id c.id GROUP BY c.id;但注意改完之后如果你还要 COUNT就必须想清楚重复行问题LEFT JOIN 后一个客户有多条订单COUNT(o.id) 得到的不是客户数而是订单数和子查询版 COUNT 结果不是一回事。所以求和可以放心改计数要谨慎。2.4 EXISTS、IN 与 JOIN 在“判断存在”时的真实差距我还见过不少团队还在坚持“尽量用 EXISTS别用 IN”理由是十几年前的老教程里写了 EXISTS 性能更好。放在今天的 PostgreSQL 上这个说法已经很模糊了。绝大多数情况下IN 和 EXISTS 都会被规划器改写成同类型的半连接不存在谁比谁快一截。真正的差异在语义x IN (子查询)如果子查询结果里混入了 NULL且 x 又不在结果集中整个条件会变成 NULL行被过滤掉结果和你预期可能完全相反。EXISTS只看有没有匹配行NULL 不参与逻辑判断语义更接近“存在”这个意图。所以我在新代码里只要是“判断是否存在”一律推荐写EXISTS。不是为了性能而是为了少踩 NULL 的坑。只有在确实需要“把子查询结果集当成一个值集合”来比较时才用 IN。3. 为什么有些子查询救不了不能拉升的硬骨头3.1 聚合、LIMIT 与窗口函数拉升失败的常见原因优化器不是万能的有几类子查询很难被改写成 JOIN甚至根本不值得改写。第一类是非相关的标量聚合子查询SELECT * FROM orders WHERE amount (SELECT avg(amount) FROM orders);这个子查询不相关优化器会把它当成 InitPlan 执行一次算出平均值后供外层使用。它本身不慢也不需要改成 JOIN因为你很难找到一种 JOIN 写法能表达“和全表平均值比较”的语义强行改成窗口函数avg() OVER ()反而会改变执行方式带来额外排序或聚合开销。第二类是带 LIMIT 的子查询SELECT * FROM orders o WHERE o.customer_id ( SELECT id FROM customers WHERE level gold ORDER BY id LIMIT 1 );LIMIT 阻止了子查询拉升但因为子查询不相关它仍然只执行一次性能上通常可以接受。真正需要关注的不是“没拉升”而是“相关 LIMIT 外层大表”那才会退化。第三类是窗口函数子查询比如经典的“取每个用户最新一笔订单”SELECT * FROM ( SELECT o.*, row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn FROM orders o ) t WHERE rn 1;这种形态很难直接改成普通 JOIN但也不建议把锅甩给子查询。想要改写时可以考虑DISTINCT ON或LATERAL具体选择取决于外层数据规模和索引情况。如果客户数很多、每个客户订单也不少LATERAL配合(customer_id, created_at DESC)索引往往是更可控的方案。3.2 NOT IN 遇到 NULL一个足以把你送走的坑性能对比聊得好好的为什么突然插进来一个 NULL 的坑因为等我讲完下面这个现象你会发现在某些场景下“换成 JOIN”根本救不了你甚至会让你连数据都查不对。假设你要查“没有买过任何东西的用户”SELECT count(*) FROM customers c WHERE c.id NOT IN ( SELECT o.customer_id FROM orders o );如果 orders.customer_id 这一列存在 NULL结果可能让你崩溃。SQL 里NOT IN遇到 NULL 时只要匹配集合里混入一个 NULL所有“不在集合中”的判断结果都会变成 NULL最终一行都查不出来。在 PostgreSQL 里即使执行计划显示的是 Anti Join语义处理也会遵循同样的逻辑。改成 NOT EXISTS 是最稳的SELECT count(*) FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.id );这个改写不只是性能问题更是正确性问题。我的习惯是凡是看到NOT IN (子查询)先默默替换成NOT EXISTS然后再谈优化。NULL 的坑和性能无关但它比性能问题更致命。3.3 ORM 生成的长子查询别急着删先看执行计划形态现在很多项目是 ORM 自动生成 SQL一查就是十几层子查询嵌套。看到这种 SQL第一反应往往是“手写 JOIN 一定会更快”。但我不建议直接删。ORM 生成的WHERE id IN (SELECT id FROM 另一张表 WHERE ...)这种简单形态PostgreSQL 大概率能拉升成半连接性能并不差你手工改写的收益很有限。真正拖垮业务的通常是两种形态相关子查询出现在 SELECT 列表里外层又是大表ORM 把需要关联取值的字段都写成标量子查询子查询里套了分页函数或 DISTINCT导致拉升失败然后外层又做了二次过滤。遇到这类 SQL正确流程是先EXPLAIN (ANALYZE, BUFFERS)看执行计划如果出现了SubPlan和特别大的loops再考虑手工改写。别一看子查询就浑身不舒服要相信优化器已经处理了相当一部分问题。4. 优化选择的判定流程与实战改写技巧4.1 第一步永远不是改写而是看执行计划改 SQL 之前我要求自己先回答三个问题子查询被拉升了吗如果拉升了走的是 Semi Join 还是普通 Join如果没有拉升是 InitPlan 还是 SubPlan循环了多少次建议用这个命令EXPLAIN (ANALYZE, BUFFERS) -- 你的查询然后重点看输出里几类关键词Semi Join/Anti Join子查询已被拉升性能通常不用太担心。InitPlan子查询只执行一次多半不慢。SubPlan子查询被嵌入在下层节点中需要配合后面的loopsN判断它到底被执行了多少次。行数偏差rows是估算actual rows是实际值两者差出几十倍时问题往往出在统计信息或相关列相关性上而不是 SQL 写法。4.2 判断“是否相关”与“循环次数”的细节一个很容易被忽略的细节是同样叫 SubPlan相关子查询的loops可能等于外层行数。比如一个小时前我调试一个报表查询执行计划里内层索引扫描耗时只有 0.03 毫秒但loops是几十万乘一下就是几万毫秒。你只看单次耗时根本发现不了问题。计算总开销的公式很直接内层节点单次实际耗时 × loops ≈ 子查询总耗时如果这个总耗时占整个查询的大头改写就是有意义的。如果子查询只跑了一两次哪怕它是个 SubPlan也不是瓶颈。此外注意actual time第一行代表从节点开始到结束的平均时间第二行代表整体时间。很多新手只看第二行会低估逐行循环的伤害。4.3 可复用的改写套路根据这些年的实践我把常见场景和推荐写法整理成一个速查表你直接抄作业就行。业务场景推荐写法核心原因判断某行是否存在EXISTS 或 IN会被拉升成半连接语义更清晰从关联表补字段LEFT JOIN避免相关标量子查询逐行执行按组做聚合JOIN GROUP BY把多次循环变成一次 Hash Join每组取最新一条DISTINCT ON 或 LATERAL普通 JOIN 难以表达“每组限量”语义UPDATE/DELETE 时判断存在EXISTS 相关子查询JOIN 改写容易导致重复更新或误删非相关且带 LIMIT/聚合保留子查询通常只执行一次没必要强行改写CTE 被重复扫描使用NOT MATERIALIZED控制 CTE 是否物化的一个开关特别注意 UPDATE 或 DELETE 的场景。比如“给 gold 用户的订单打标”UPDATE orders o SET approved true WHERE EXISTS ( SELECT 1 FROM customers c WHERE c.id o.customer_id AND c.level gold );如果你图省事改成UPDATE orders o SET approved true FROM customers c WHERE c.id o.customer_id AND c.level gold;一旦 customers 表存在重复数据或者连接条件写得不严谨同一行订单可能被更新多次虽然最终值一样但语义上已经不可控。这类改写风险远大于收益别轻易动手。4.4 索引与优化器参数的配合改写之后索引设计才是真正决定成败的环节。以订单和客户为例下面这个组合索引能覆盖大多数“按客户查订单”的场景CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC);这个索引既支持“某个客户的所有订单”也能支持“按客户分组取最新订单”比只建一个customer_id单列索引要好用得多。统计信息层面如果发现优化器对某一列的行数估算偏差很大先做一次 ANALYZE。如果还是不准考虑跨列相关性的扩展统计信息比如level和region同时出现在 WHERE 条件里时单列统计无法表达两列相关性。参数层面最常被忽略的是work_mem。Hash Join 在构建哈希表时如果估计内存超过 work_mem会把数据写到临时文件执行计划里会出现Temp File字样查询性能可能从几百毫秒变成几秒甚至几十秒。你可以先针对当前会话调大看看效果SET work_mem 128MB;但要记住这是会话级临时调整用来验证瓶颈方向。全局盲目调大会害死别人因为每个连接都可能分配这么多内存。我不会在没看到Temp File或哈希桶落盘迹象时轻易动它。还有一类错误操作就是直接SET enable_hashjoin off或SET enable_nestloop off。这种“人工干预优化器”的做法短期内可能让某个查询变快但全局真实性会变差甚至让其它查询退化到数据量不大却慢得离谱。优化器参数是最后手段不是第一选项。5. 经验汇总几个值得留存的判断准则5.1 我的快速自检清单现在每次接到慢查询我都会按这套清单走一遍写在这里供你参考子查询是相关的还是非相关的相关意味着可能逐行执行。执行计划里出现 Semi Join 没有出现了说明优化器已经处理。是否出现SubPlan且loops特别大这是最需要改写的信号。外层行数乘内层单次耗时算出来的总耗时占查询多大比例有没有涉及 NOT IN 或可空列有就优先检查 NULL 语义。改写 JOIN 后会不会产生重复行会在语义上怎么办ANALYZE 刷新过统计信息没有行数估算偏差大不大临时文件出现没有要不要临时调大 work_mem 验证这八条看完大部分“子查询 vs JOIN”的性能问题都能定位到具体环节。5.2 我实际遇到过的三次误判第一次误判是有人把一段EXISTS改成了JOIN COUNT(DISTINCT)。理由是“JOIN 看起来更标准”。结果原本 1.2 秒的查询变成 8 秒因为普通 JOIN 把大量重复行喂给 DISTINCT排序代价成倍上涨。其实原查询已经走了半连接再改就是画蛇添足。第二次误判是一个看板查询用了NOT IN返回结果永远为空。当时一群人围着查执行计划和索引最后发现子查询里那一列存在 NULL。把NOT IN改成NOT EXISTS后问题立刻消失。这个问题和性能毫无关系但比性能更坑人。第三次误判是某个 ORM 生成的报表 SQL外表几万行相关子查询里套了一层ORDER BY ... LIMIT 1。执行计划里 SubPlan 的 loops 等于外表行数每次循环都要排序一次。我把这个子查询改写成 LATERAL 子查询并建立了对应的组合索引后查询从十几秒降到几百毫秒。这个案例里真正帮上忙的不是 JOIN而是 LATERAL。5.3 最后一点建议以后别再问“子查询和 JOIN 哪个快”了。这句话问出来就输了因为答案永远是看情况。真正该问的是我的子查询是哪种形态优化器有没有把它拉升如果拉不起来原因是什么瓶颈在循环次数还是在索引缺失还是在哈希表落盘PostgreSQL 是个诚实得有点死板的优化器它把选择权交给你又藏了很多改写逻辑在里面。读执行计划比背口诀有用得多。把上面这套判断流程跑过两三次之后你会发现所谓“性能对比”根本不在于语法长得像谁而在于你到底有没有看清数据是怎么被扫描、连接、循环的。