大数跨境
首页
>
子查询总拖慢查询？把它变成连接就能解决？
>
0
0

子查询总拖慢查询？把它变成连接就能解决？

子查询总拖慢查询？把它变成连接就能解决？ Lisa聊外贸
2025-10-21
14
导读：子查询是嵌套在其他SQL语句中的查询，分为标量、行、列和表子查询。相关子查询依赖外部查询变量，性能较差，而非相关子查询独立执行。PostgreSQL优化器通过子查询扁平化将子查询转换为更高效的连接操作

扫描二维码关注或者微信搜一搜：编程智域 前端至全栈交流与成长

发现1000+提升效率与开发的AI工具和实用程序：https://tools.cmdragon.cn/

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

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

1.1 子查询的基本类型

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

  • • 标量子查询：返回单个值（一行一列），常用于SELECT、WHERE或HAVING子句中。例如： 
    -- 查询所有商品的最高价格，作为单独列返回
    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 = 'admin@example.com');
  • • 列子查询：返回一列多行，常用于IN、ANY或ALL运算符后。例如： 
    -- 查找购买过金额超过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千行）。连接顺序不同，计算量差异巨大：

  • • 先连接users和products（1万×1千=1千万行），再连接orders（1千万×10万=100亿行）——灾难！
  • • 先连接orders和products（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_id和orders.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（连接users和orders），然后GroupAggregate（分组计算），总成本降低70%以上！

五、课后Quiz：巩固与思考

  1. 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. 问题2：当子查询返回大量重复值时，用IN和EXISTS哪个更高效？为什么？
    答案：EXISTS更高效。因为IN需要先对子查询结果去重（额外开销），再匹配外部表；而EXISTS采用“短路逻辑”——只要找到一个匹配的值就停止子查询，避免了去重和全量扫描。参考：https://www.postgresql.org/docs/17/functions-subquery.html#FUNCTIONS-SUBQUERY-EXISTS。
  3. 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;
参考：https://www.postgresql.org/docs/17/queries-table-expressions.html#QUERIES-LATERAL。

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

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

参考链接

  1. 1. PostgreSQL LATERAL连接：https://www.postgresql.org/docs/17/queries-table-expressions.html#QUERIES-LATERAL
  2. 2. PostgreSQL查询计划：https://www.postgresql.org/docs/17/using-explain.html
  3. 3. PostgreSQL子查询函数：https://www.postgresql.org/docs/17/functions-subquery.html

【声明】内容源于网络
0
0
Lisa聊外贸
跨境分享吧 | 长期输出优质内容
内容 44193
粉丝 1
Lisa聊外贸 跨境分享吧 | 长期输出优质内容
总阅读232.1k
粉丝1
内容44.2k