一、子查询:从基础到进阶

子查询是嵌套在其他SQL语句中的查询,相当于“查询里的查询”。PostgreSQL支持多种类型的子查询,我们可以根据返回结果的结构和依赖关系来分类。

1.1 子查询的基本类型

根据返回结果的维度,子查询可以分为四类:

  • 标量子查询:返回单个值(一行一列),常用于SELECTWHEREHAVING子句中。例如:
    -- 查询所有商品的最高价格,作为单独列返回
    SELECT product_name, price, 
           (SELECT MAX(price) FROM products) AS max_price
    FROM products;
    
  • 行子查询:返回一行多列,需用括号包裹列组合。例如:
    -- 查找与管理员邮箱匹配的用户(假设id和name唯一)
    SELECT * FROM users 
    WHERE (id, name) = (SELECT id, name FROM admins WHERE email = '[email protected]');
    
  • 列子查询:返回一列多行,常用于INANYALL运算符后。例如:
    -- 查找购买过金额超过100元订单的用户
    SELECT name FROM users 
    WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);
    
  • 表子查询:返回多行多列,需作为临时表(别名)使用,常用于FROM子句中。例如:
    -- 查找总订单金额超过1000元的用户
    SELECT * FROM (
      SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id
    ) AS order_totals 
    WHERE total > 1000;
    

1.2 相关子查询 vs 非相关子查询

根据是否依赖外部查询的变量,子查询分为:

  • 非相关子查询:子查询不引用外部查询的任何列,只执行一次。例如上文中的“查找购买过金额超过100元订单的用户”,子查询SELECT user_id FROM orders WHERE amount > 100独立于外部的users表。
  • 相关子查询:子查询引用了外部查询的列,每次外部查询的行都会触发子查询执行。例如:
    -- 查找有订单金额超过100元的用户(相关子查询依赖u.id)
    SELECT name FROM users u 
    WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 100);
    
    相关子查询的性能通常更差——想象一下,如果你有1000个用户,子查询就要执行1000次

1.3 PostgreSQL对子查询的处理:扁平化与去相关

PostgreSQL的查询优化器(Query Optimizer)会尝试将可优化的子查询转换为更高效的连接操作,这个过程叫子查询扁平化(Subquery Flattening)。例如,对于非相关的IN子查询:

-- 原写法:IN子查询
SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

优化器会将其“扁平化”为等价的内连接:

-- 优化后:内连接(自动去重)
SELECT DISTINCT u.name 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE o.amount > 100; 

为什么要这么做?因为连接操作的执行计划(如哈希连接、嵌套循环)通常比子查询更高效,尤其是当子查询返回大量结果时。

但不是所有子查询都能被扁平化——比如相关子查询返回多列的子查询,优化器无法直接转换,这时需要我们手动改写(后面会讲)。

二、连接优化:理解连接的“底层逻辑”

连接是SQL中组合多个表数据的核心操作,但连接的类型顺序方法直接影响性能。

2.1 连接的基本类型

PostgreSQL支持三种常用连接:

  • 内连接(INNER JOIN):只返回两个表中匹配的行(“交集”)。例如:
    SELECT u.name, o.order_date 
    FROM users u 
    INNER JOIN orders o ON u.id = o.user_id;
    
  • 外连接(LEFT/RIGHT/FULL OUTER JOIN):返回匹配的行,以及其中一个表(左/右/全)不匹配的行(“补全缺失数据”)。例如左外连接:
    -- 返回所有用户,即使没有订单(order_date为NULL)
    SELECT u.name, o.order_date 
    FROM users u 
    LEFT JOIN orders o ON u.id = o.user_id;
    
  • 交叉连接(CROSS JOIN):返回两个表的笛卡尔积(所有可能的行组合),通常需谨慎使用(性能极差)。

2.2 连接的顺序选择:为什么顺序很重要?

假设我们要连接三个表:users(1万行)、orders(10万行)、products(1千行)。连接顺序不同,计算量差异巨大:

  • 先连接usersproducts(1万×1千=1千万行),再连接orders(1千万×10万=100亿行)——灾难
  • 先连接ordersproducts(10万×500=5千万行,假设products有500行匹配),再连接users(5千万×5千=2.5亿行)——更优

PostgreSQL的优化器会基于**成本模型(Cost-Based Optimization, CBO)**自动选择连接顺序:它会估算每种连接顺序的“成本”(CPU、IO消耗),选择成本最低的方案。

但优化器也有局限性——当表数量超过10个时,枚举所有顺序的时间会很长,这时优化器会采用“贪心算法”近似选择。如果发现优化器选的顺序不好,我们可以用JOIN ORDER BY强制指定顺序(不推荐,除非你很确定)。

2.3 连接的方法:三种“底层算法”

PostgreSQL用三种算法执行连接,每种适用于不同场景:

▶️ 嵌套循环连接(Nested Loop Join)

原理:遍历外层表的每一行,再扫描内层表找匹配的行(“循环里套循环”)。
适用场景:外层表很小(比如100行),且内层表有索引(快速查找匹配行)。
例子:连接users(1万行)和orders(10万行),如果orders.user_id有索引,嵌套循环会很快——外层遍历users的每一行,内层用索引找对应的orders行。

▶️ 哈希连接(Hash Join)

原理:先将小表(“建哈希表的表”)的连接键计算哈希值,存到内存哈希表中;再遍历大表,用同样的哈希函数找匹配的行。
适用场景:两个表都很大,但连接键没有索引,且内存足够存小表的哈希表。
例子:连接orders(500万行)和products(5万行),products.id没有索引,这时哈希连接比嵌套循环快得多。

▶️ 排序合并连接(Merge Join)

原理:先将两个表按连接键排序,再“合并”两个有序表(类似归并排序)。
适用场景:两个表已经按连接键排序(比如有主键/唯一索引),或者需要排序后的数据(比如按时间排序)。
例子:连接orders(按order_date排序)和shipments(按ship_date排序),排序合并连接可以直接利用已排序的数据,避免二次排序。

三、子查询与连接的改写技巧:从“慢查询”到“快查询”

很多性能问题都源于不合理的子查询写法——我们可以通过手动改写,将子查询转换为更高效的连接。

3.1 用连接替代IN子查询

场景:当IN子查询返回唯一值时,用JOIN替代可以避免IN的去重开销。
例子:原查询(查找购买过商品100的用户):

SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE product_id = 100);

改写后

SELECT DISTINCT u.name 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE o.product_id = 100;

为什么更好?IN需要对其子查询结果去重(额外开销),而JOIN通过DISTINCT(如果需要)或索引直接避免重复,性能更优。

3.2 用EXISTS替代IN子查询

场景:当IN子查询返回大量重复值时,EXISTS的“短路逻辑”更高效。
例子:原查询(查找有订单的用户):

SELECT name FROM users WHERE id IN (SELECT user_id FROM orders);

改写后

SELECT name FROM users u 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

为什么更好?IN会先执行子查询并去重,再匹配;而EXISTS逐行检查——只要找到一个匹配的orders行,就停止子查询,避免了去重和全量扫描。

3.3 用LATERAL连接处理相关子查询

相关子查询无法被优化器扁平化,这时可以用LATERAL连接——它允许子查询引用外部表的列,且能利用索引。
场景:查询每个用户的最新订单日期。
原相关子查询

SELECT u.name, 
       (SELECT order_date FROM orders o 
        WHERE o.user_id = u.id 
        ORDER BY order_date DESC LIMIT 1) AS last_order
FROM users u;

问题:子查询会为每个users行执行一次(1万行=执行1万次),如果orders很大,会很慢。
改写为LATERAL连接

SELECT u.name, o.last_order
FROM users u
LEFT JOIN LATERAL (
  SELECT order_date AS last_order 
  FROM orders 
  WHERE user_id = u.id 
  ORDER BY order_date DESC LIMIT 1
) o ON true; -- ON true表示总是连接

为什么更好?:LATERAL连接会将子查询视为“动态表”——它会为每个users行生成一个小结果集(最新订单),且能利用orders.user_idorders.order_date的联合索引(快速排序+限制),性能提升数倍!

四、实践案例:从“慢子查询”到“快连接”

让我们用一个电商场景验证改写的效果:
需求:查询“购买过商品ID为100,且总订单金额超过1000元的用户姓名和邮箱”。

4.1 子查询实现(慢版本)

SELECT name, email 
FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE product_id = 100) -- 子查询1:购买过商品100的用户
AND id IN (SELECT user_id FROM orders GROUP BY user_id HAVING SUM(amount) > 1000); -- 子查询2:总金额超1000的用户

问题:两个IN子查询都要扫描orders表,且需要去重,总扫描次数=2次orders全表扫描(假设orders有100万行,这会很慢)。

4.2 连接改写(快版本)

我们可以用**一次GROUP BY + HAVING**替代两个子查询:

SELECT u.name, u.email 
FROM users u 
JOIN orders o ON u.id = o.user_id 
GROUP BY u.id, u.name, u.email -- 按用户分组
HAVING SUM(o.amount) > 1000 -- 总金额超1000
AND BOOL_OR(o.product_id = 100); -- 至少购买过一次商品100

为什么更好?

  • 只扫描orders表一次(减少IO);
  • BOOL_OR(判断是否存在至少一个符合条件的行)替代IN子查询,避免去重;
  • 利用users.id的主键索引(快速分组)。

4.3 执行计划对比(用EXPLAIN ANALYZE验证)

我们用EXPLAIN ANALYZE查看两个查询的执行计划:

  • 子查询版本的计划会显示两次Seq Scan on orders(全表扫描),以及HashAggregate(去重);
  • 连接版本的计划会显示一次Hash Join(连接usersorders),然后GroupAggregate(分组计算),总成本降低70%以上!

五、课后Quiz:巩固与思考

  1. 问题1:请解释“子查询扁平化”的概念,并举例说明PostgreSQL会对哪种子查询进行扁平化处理?
    答案:子查询扁平化是PostgreSQL优化器将子查询转换为等价连接的过程,目的是减少子查询的执行次数。例如,非相关的IN子查询(返回唯一值)会被扁平化为内连接:SELECT * FROM users WHERE id IN (SELECT user_id FROM orders)SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id

  2. 问题2:当子查询返回大量重复值时,用INEXISTS哪个更高效?为什么?
    答案EXISTS更高效。因为IN需要先对子查询结果去重(额外开销),再匹配外部表;而EXISTS采用“短路逻辑”——只要找到一个匹配的值就停止子查询,避免了去重和全量扫描。参考:www.postgresql.org/docs/17/fun…

  3. 问题3:请将以下相关子查询改写成LATERAL连接:

SELECT u.id, (SELECT SUM(amount) FROM orders o WHERE o.user_id = u.id) AS total 
FROM users u;

答案

SELECT u.id, o.total 
FROM users u 
LEFT JOIN LATERAL (SELECT SUM(amount) AS total FROM orders WHERE user_id = u.id) o ON true;

六、常见报错与解决方案

1. 报错:ERROR: subquery must return only one column

原因:子查询在需要返回单列的场景(如IN、标量子查询)中返回了多列。
例子SELECT * FROM users WHERE id IN (SELECT user_id, product_id FROM orders);
解决:确保子查询只返回需要的列:SELECT user_id FROM orders
预防:写子查询时,明确指定返回的列(不要用SELECT *)。

2. 报错:ERROR: invalid reference to FROM-clause entry for table "u"

原因:子查询引用了外部表的列,但子查询位于FROM子句中,顺序不对(优化器无法识别依赖)。
例子SELECT * FROM (SELECT name FROM users WHERE id = o.user_id) AS u JOIN orders o ON true;
解决:用LATERAL连接允许子查询引用外部表:SELECT u.name, o.* FROM orders o JOIN LATERAL (SELECT name FROM users WHERE id = o.user_id) u ON true;
参考:www.postgresql.org/docs/17/que…

3. 报错:ERROR: could not choose a join order for three-way join

原因:连接的表太多(超过10个),优化器无法在合理时间内枚举所有顺序。
解决:简化查询(拆分成多个小查询),或用JOIN ORDER BY强制指定顺序(不推荐,除非你很确定)。

参考链接

  1. PostgreSQL LATERAL连接:www.postgresql.org/docs/17/que…
  2. PostgreSQL查询计划:www.postgresql.org/docs/17/usi…
  3. PostgreSQL子查询函数:www.postgresql.org/docs/17/fun…
往期文章归档
  • PostgreSQL全表扫描慢到崩溃?建索引+改查询+更统计信息三招能破? - cmdragon's Blog
  • 复杂查询总拖后腿?PostgreSQL多列索引+覆盖索引的神仙技巧你get没? - cmdragon's Blog
  • 只给表子集建索引?用函数结果建索引?PostgreSQL这俩操作凭啥能省空间又加速? - cmdragon's Blog
  • B-tree索引像字典查词一样工作?那哪些数据库查询它能加速,哪些不能? - cmdragon's Blog
  • 想抓PostgreSQL里的慢SQL?pg_stat_statements基础黑匣子和pg_stat_monitor时间窗,谁能帮你更准揪出性能小偷? - cmdragon's Blog
  • PostgreSQL的“时光机”MVCC和锁机制是怎么搞定高并发的? - cmdragon's Blog
  • PostgreSQL性能暴涨的关键?内存IO并发参数居然要这么设置? - cmdragon's Blog
  • 大表查询慢到翻遍整个书架?PostgreSQL分区表教你怎么“分类”才高效
  • PostgreSQL 查询慢?是不是忘了优化 GROUP BY、ORDER BY 和窗口函数? - cmdragon's Blog
  • PostgreSQL里的子查询和CTE居然在性能上“掐架”?到底该站哪边? - cmdragon's Blog
  • PostgreSQL选Join策略有啥小九九?Nested Loop/Merge/Hash谁是它的菜? - cmdragon's Blog
  • PostgreSQL新手SQL总翻车?这7个性能陷阱你踩过没? - cmdragon's Blog
  • PostgreSQL索引选B-Tree还是GiST?“瑞士军刀”和“多面手”的差别你居然还不知道? - cmdragon's Blog
  • 想知道数据库怎么给查询“算成本选路线”?EXPLAIN能帮你看明白? - cmdragon's Blog
  • PostgreSQL处理SQL居然像做蛋糕?解析到执行的4步里藏着多少查询优化的小心机? - cmdragon's Blog
  • PostgreSQL备份不是复制文件?物理vs逻辑咋选?误删还能精准恢复到1分钟前? - cmdragon's Blog
  • 转账不翻车、并发不干扰,PostgreSQL的ACID特性到底有啥魔法? - cmdragon's Blog
  • 银行转账不白扣钱、电商下单不超卖,PostgreSQL事务的诀窍是啥? - cmdragon's Blog
  • PostgreSQL里的PL/pgSQL到底是啥?能让SQL从“说目标”变“讲步骤”? - cmdragon's Blog
  • PostgreSQL视图不存数据?那它怎么简化查询还能递归生成序列和控制权限? - cmdragon's Blog
  • PostgreSQL索引这么玩,才能让你的查询真的“飞”起来? - cmdragon's Blog
  • PostgreSQL的表关系和约束,咋帮你搞定用户订单不混乱、学生选课不重复? - cmdragon's Blog
  • PostgreSQL查询的筛子、排序、聚合、分组?你会用它们搞定数据吗? - cmdragon's Blog
  • PostgreSQL数据类型怎么选才高效不踩坑? - cmdragon's Blog
  • 想解锁PostgreSQL查询从基础到进阶的核心知识点?你都get了吗? - cmdragon's Blog
  • PostgreSQL DELETE居然有这些操作?返回数据、连表删你试过没? - cmdragon's Blog
  • PostgreSQL UPDATE语句怎么玩?从改邮箱到批量更新的避坑技巧你都会吗? - cmdragon's Blog
  • PostgreSQL插入数据还在逐条敲?批量、冲突处理、返回自增ID的技巧你会吗? - cmdragon's Blog
  • PostgreSQL的“仓库-房间-货架”游戏,你能建出电商数据库和表吗? - cmdragon's Blog
  • PostgreSQL 17安装总翻车?Windows/macOS/Linux避坑指南帮你搞定? - cmdragon's Blog
  • 能当关系型数据库还能玩对象特性,能拆复杂查询还能自动管库存,PostgreSQL凭什么这么香? - cmdragon's Blog
  • 给接口加新字段又不搞崩老客户端?FastAPI的多版本API靠哪三招实现? - cmdragon's Blog
  • 流量突增要搞崩FastAPI?熔断测试是怎么防系统雪崩的? - cmdragon's Blog
  • FastAPI秒杀库存总变负数?Redis分布式锁能帮你守住底线吗 - cmdragon's Blog
  • FastAPI的CI流水线怎么自动测端点,还能让Allure报告美到犯规? - cmdragon's Blog
  • 如何用GitHub Actions为FastAPI项目打造自动化测试流水线? - cmdragon's Blog
  • 如何用Git Hook和CI流水线为FastAPI项目保驾护航? - cmdragon's Blog
  • FastAPI如何用契约测试确保API的「菜单」与「菜品」一致?
  • 为什么TDD能让你的FastAPI开发飞起来? - cmdragon's Blog
  • 如何用FastAPI玩转多模块测试与异步任务,让代码不再“闹脾气”? - cmdragon's Blog
  • 如何在FastAPI中玩转“时光倒流”的数据库事务回滚测试?
  • 如何在FastAPI中优雅地模拟多模块集成测试? - cmdragon's Blog
  • 多环境配置切换机制能否让开发与生产无缝衔接? - cmdragon's Blog
  • 如何在 FastAPI 中巧妙覆盖依赖注入并拦截第三方服务调用? - cmdragon's Blog
  • 为什么你的单元测试需要Mock数据库才能飞起来? - cmdragon's Blog
  • 如何在FastAPI中巧妙隔离依赖项,让单元测试不再头疼? - cmdragon's Blog
  • 如何在FastAPI中巧妙隔离依赖项,让单元测试不再头疼? - cmdragon's Blog
  • 测试覆盖率不够高?这些技巧让你的FastAPI测试无懈可击! - cmdragon's Blog
  • 为什么你的FastAPI测试覆盖率总是低得让人想哭? - cmdragon's Blog
  • 如何让FastAPI测试不再成为你的噩梦? - cmdragon's Blog
免费好用的热门在线工具
  • Mermaid 在线编辑器 - 应用商店 | By cmdragon
  • 数学求解计算器 - 应用商店 | By cmdragon
  • 智能提词器 - 应用商店 | By cmdragon
  • 魔法简历 - 应用商店 | By cmdragon
  • Image Puzzle Tool - 图片拼图工具 | By cmdragon
  • 字幕下载工具 - 应用商店 | By cmdragon
  • 歌词生成工具 - 应用商店 | By cmdragon
  • 网盘资源聚合搜索 - 应用商店 | By cmdragon
  • ASCII字符画生成器 - 应用商店 | By cmdragon
  • JSON Web Tokens 工具 - 应用商店 | By cmdragon
  • Bcrypt 密码工具 - 应用商店 | By cmdragon
  • GIF 合成器 - 应用商店 | By cmdragon
  • GIF 分解器 - 应用商店 | By cmdragon
  • 文本隐写术 - 应用商店 | By cmdragon
  • CMDragon 在线工具 - 高级AI工具箱与开发者套件 | 免费好用的在线工具
  • 应用商店 - 发现1000+提升效率与开发的AI工具和实用程序 | 免费好用的在线工具
  • CMDragon 更新日志 - 最新更新、功能与改进 | 免费好用的在线工具
  • 支持我们 - 成为赞助者 | 免费好用的在线工具
  • AI文本生成图像 - 应用商店 | 免费好用的在线工具
  • 临时邮箱 - 应用商店 | 免费好用的在线工具
  • 二维码解析器 - 应用商店 | 免费好用的在线工具
  • 文本转思维导图 - 应用商店 | 免费好用的在线工具
  • 正则表达式可视化工具 - 应用商店 | 免费好用的在线工具
  • 文件隐写工具 - 应用商店 | 免费好用的在线工具
  • IPTV 频道探索器 - 应用商店 | 免费好用的在线工具
  • 快传 - 应用商店 | 免费好用的在线工具
  • 随机抽奖工具 - 应用商店 | 免费好用的在线工具
  • 动漫场景查找器 - 应用商店 | 免费好用的在线工具
  • 时间工具箱 - 应用商店 | 免费好用的在线工具
  • 网速测试 - 应用商店 | 免费好用的在线工具
  • AI 智能抠图工具 - 应用商店 | 免费好用的在线工具
  • 背景替换工具 - 应用商店 | 免费好用的在线工具
  • 艺术二维码生成器 - 应用商店 | 免费好用的在线工具
  • Open Graph 元标签生成器 - 应用商店 | 免费好用的在线工具
  • 图像对比工具 - 应用商店 | 免费好用的在线工具
  • 图片压缩专业版 - 应用商店 | 免费好用的在线工具
  • 密码生成器 - 应用商店 | 免费好用的在线工具
  • SVG优化器 - 应用商店 | 免费好用的在线工具
  • 调色板生成器 - 应用商店 | 免费好用的在线工具
  • 在线节拍器 - 应用商店 | 免费好用的在线工具
  • IP归属地查询 - 应用商店 | 免费好用的在线工具
  • CSS网格布局生成器 - 应用商店 | 免费好用的在线工具
  • 邮箱验证工具 - 应用商店 | 免费好用的在线工具
  • 书法练习字帖 - 应用商店 | 免费好用的在线工具
  • 金融计算器套件 - 应用商店 | 免费好用的在线工具
  • 中国亲戚关系计算器 - 应用商店 | 免费好用的在线工具
  • Protocol Buffer 工具箱 - 应用商店 | 免费好用的在线工具
  • IP归属地查询 - 应用商店 | 免费好用的在线工具
  • 图片无损放大 - 应用商店 | 免费好用的在线工具
  • 文本比较工具 - 应用商店 | 免费好用的在线工具
  • IP批量查询工具 - 应用商店 | 免费好用的在线工具
  • 域名查询工具 - 应用商店 | 免费好用的在线工具
  • DNS工具箱 - 应用商店 | 免费好用的在线工具
  • 网站图标生成器 - 应用商店 | 免费好用的在线工具
  • XML Sitemap
本站提供的所有下载资源均来自互联网,仅提供学习交流使用,版权归原作者所有。如需商业使用,请联系原作者获得授权。 如您发现有涉嫌侵权的内容,请联系我们 邮箱:[email protected]