粉笔教师app题库
183.76MB · 2025-10-22
子查询是嵌套在其他SQL语句中的查询,相当于“查询里的查询”。PostgreSQL支持多种类型的子查询,我们可以根据返回结果的结构和依赖关系来分类。
根据返回结果的维度,子查询可以分为四类:
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 = '[email protected]');
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;
根据是否依赖外部查询的变量,子查询分为:
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次!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中组合多个表数据的核心操作,但连接的类型、顺序和方法直接影响性能。
PostgreSQL支持三种常用连接:
SELECT u.name, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- 返回所有用户,即使没有订单(order_date为NULL)
SELECT u.name, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
假设我们要连接三个表: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
强制指定顺序(不推荐,除非你很确定)。
PostgreSQL用三种算法执行连接,每种适用于不同场景:
原理:遍历外层表的每一行,再扫描内层表找匹配的行(“循环里套循环”)。
适用场景:外层表很小(比如100行),且内层表有索引(快速查找匹配行)。
例子:连接users
(1万行)和orders
(10万行),如果orders.user_id
有索引,嵌套循环会很快——外层遍历users
的每一行,内层用索引找对应的orders
行。
原理:先将小表(“建哈希表的表”)的连接键计算哈希值,存到内存哈希表中;再遍历大表,用同样的哈希函数找匹配的行。
适用场景:两个表都很大,但连接键没有索引,且内存足够存小表的哈希表。
例子:连接orders
(500万行)和products
(5万行),products.id
没有索引,这时哈希连接比嵌套循环快得多。
原理:先将两个表按连接键排序,再“合并”两个有序表(类似归并排序)。
适用场景:两个表已经按连接键排序(比如有主键/唯一索引),或者需要排序后的数据(比如按时间排序)。
例子:连接orders
(按order_date
排序)和shipments
(按ship_date
排序),排序合并连接可以直接利用已排序的数据,避免二次排序。
很多性能问题都源于不合理的子查询写法——我们可以通过手动改写,将子查询转换为更高效的连接。
场景:当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
(如果需要)或索引直接避免重复,性能更优。
场景:当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
行,就停止子查询,避免了去重和全量扫描。
相关子查询无法被优化器扁平化,这时可以用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元的用户姓名和邮箱”。
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万行,这会很慢)。
我们可以用**一次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
的主键索引(快速分组)。我们用EXPLAIN ANALYZE
查看两个查询的执行计划:
Seq Scan on orders
(全表扫描),以及HashAggregate
(去重);Hash Join
(连接users
和orders
),然后GroupAggregate
(分组计算),总成本降低70%以上!问题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:当子查询返回大量重复值时,用IN
和EXISTS
哪个更高效?为什么?
答案:EXISTS
更高效。因为IN
需要先对子查询结果去重(额外开销),再匹配外部表;而EXISTS
采用“短路逻辑”——只要找到一个匹配的值就停止子查询,避免了去重和全量扫描。参考:www.postgresql.org/docs/17/fun…
问题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;
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 *
)。
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…
ERROR: could not choose a join order for three-way join
原因:连接的表太多(超过10个),优化器无法在合理时间内枚举所有顺序。
解决:简化查询(拆分成多个小查询),或用JOIN ORDER BY
强制指定顺序(不推荐,除非你很确定)。