🍋子查询的初步使用

  • 企业常用的子查询示例？
  • 有哪些必须用子查询的情况，有哪些常见的改join/with提高性能的例子？
  • Hash Join、Semi Join、宽表、FilterProject、SemiJoin 、HashBuilder这些词什么意思

下面把“必须用子查询”和“可以改 JOIN / WITH 提速”这两种场景一次性拆开讲透。

每条都给出 Trino（Presto）语法示例，并注明 为什么必须/可以改写

一、4 种“必须用子查询”的场景

（改写 JOIN 会语义错误或更慢）

场景 必须用子查询的原因 Trino 示例
1. 聚合后回查原表（“找高于部门平均”） 聚合值与明细行逐行比较，JOIN 先聚合再回查=自己和自己连，只能用相关子查询或窗口函数 SELECT * FROM employee e WHERE salary > (SELECT AVG(salary) FROM employee WHERE dept=e.dept)
2. EXISTS / NOT EXISTS 语义是“有无匹配”，JOIN 会放大行数；NOT IN 对 NULL 敏感 SELECT * FROM customer c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id=c.id)
3. 行号/ Top-N 子查询（每组取最新 1 条） 需要先排序再过滤，JOIN 写不出“每组”限制 SELECT * FROM (SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY login_dt DESC) rn FROM login_log) t WHERE rn=1
4. 测量数据质量（重复主键、空值率） 只关心统计结果，不需要展开明细；JOIN 会强行拉明细 SELECT order_id, COUNT() FROM orders GROUP BY order_id HAVING COUNT()>1

二、6 个“常见可改写”例子

（子查询 → JOIN / WITH，性能 2-10 倍提升）

1. 相关子查询 → JOIN

原写法（慢）

1
2
3
SELECT emp_id, emp_name,
       (SELECT dept_name FROM department d WHERE d.dept_id = e.dept_id) AS dept_name
FROM employee e;

改写（快）

1
2
3
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employee e
JOIN department d ON d.dept_id = e.dept_id;

提速点：把 每行一次索引回表 改成 一次 Hash Join。

2. 相关聚合子查询 → WITH + JOIN

原写法（O(N²)）

1
2
3
SELECT emp_id, salary,
       salary - (SELECT AVG(salary) FROM employee WHERE dept_id = e.dept_id) AS diff
FROM employee e;

改写（O(N)）

1
2
3
4
5
6
7
8
WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employee
    GROUP BY dept_id
)
SELECT e.emp_id, e.salary, e.salary - d.avg_salary AS diff
FROM employee e
JOIN dept_avg d ON d.dept_id = e.dept_id;

提速点：聚合只算一次，复用结果。

3. IN (子查询) → Semi Join

原写法

1
2
SELECT * FROM customer
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_amt > 10000);

改写（Trino 自动做，也可显式写）

1
2
3
SELECT c.*
FROM customer c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.order_amt > 10000);

提速点：Semi Join 可在 Hash Build 阶段直接过滤，不放大右表。

4. 子查询当“临时宽表” → WITH 展开后多次复用

场景：同一张中间结果要被选两次

1
2
3
4
5
6
7
8
9
WITH high_val_customer AS (
    SELECT customer_id, SUM(order_amt) AS total
    FROM orders
    GROUP BY customer_id
    HAVING SUM(order_amt) > 100000
)
SELECT h.customer_id, h.total, a.address
FROM high_val_customer h
JOIN address a ON a.customer_id = h.customer_id;

提速点：只扫描一次 orders，CTE 结果默认物化（Trino 358+ 支持 CTE Reuse）。

5. 多层嵌套子查询 → 一步 JOIN

原写法

1
2
3
4
SELECT *
FROM sku s
WHERE category_id = (SELECT category_id FROM category WHERE category_name = '手机')
  AND brand_id    = (SELECT brand_id   FROM brand    WHERE brand_name    = '华为');

改写

1
2
3
4
SELECT s.*
FROM sku s
JOIN category c ON c.category_id = s.category_id AND c.category_name = '手机'
JOIN brand    b ON b.brand_id    = s.brand_id    AND b.brand_name    = '华为';

提速点：两次相关子查询 → 一次多表 Hash Join，且可以利用 复合索引。

6. 子查询里再子查询 → 扁平化 JOIN + 窗口函数

原写法

1
2
3
4
5
6
SELECT *
FROM (
    SELECT *, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS cnt
    FROM customer c
) t
WHERE cnt >= 5;

改写

1
2
3
4
5
SELECT c.*, t.cnt
FROM customer c
JOIN (SELECT customer_id, COUNT(*) AS cnt FROM orders GROUP BY customer_id) t
  ON t.customer_id = c.customer_id
WHERE t.cnt >= 5;

提速点：把 相关子查询 变成 非相关聚合，再 Hash Join。

三、1 张决策表，10 秒判断“能不能改”

检查项 子查询必须保留 可改写 JOIN/WITH
是否 EXISTS / NOT EXISTS ✅ ❌
是否“聚合后回查明细” ✅（或改窗口） ❌
是否“每组 Top-N” ✅（需窗口） ❌
是否只拿聚合指标 ❌ ✅（先 WITH 聚合）
是否相关子查询且无聚合 ❌ ✅（直接 JOIN）
是否 IN (子查询) ❌ ✅（Semi Join）

四、小结口诀

“聚合回查、存在性、Top-N 必须子查；其余一律拉出来做 JOIN/WITH。”

拿不准执行计划时，EXPLAIN (TYPE DISTRIBUTED) 看有没有 FilterProject+SemiJoin 或 HashBuilder 即可。

高频黑话

  1. Hash Join（哈希连接）

  • 定义：把两表里连接键先算成哈希表，再一次性“对号入座”做等值匹配。

  • 类比：老师拿两张名单，先给 A 班每人发一个“学号-座位号”小纸条（建哈希表），再让 B 班学生按纸条找座位，O(1) 定位。

  • Trino 计划里：

    1
    2
    3
    └─ InnerJoin[hash = (user_id)]  
       ├─ Scan[user_id, …]  ←  Build 端（通常选小表）  
       └─ Scan[user_id, …]  ←  Probe 端
  1. Semi Join（半连接）

  • 定义：只关心左表记录是否在右表存在匹配，不返回右表列，也不去重左表。

  • 类比：安检门只看你是否在“黑名单”里，查到就拦下，不会把黑名单详细信息打印出来。

  • Trino 自动把 EXISTS / IN (子查询) 改写成 SemiJoin：

    1
    2
    3
    └─ SemiJoin[hash = (user_id)]  
       ├─ Scan…  ← 左表（旅客）  
       └─ Scan…  ← 右表（黑名单）
  1. HashBuilder（哈希建造者）
  • 定义：Trino 执行计划里的一个运行时算子，负责把 Build 端数据全部读进内存并建成哈希表。
  • 类比：安检人员先把黑名单一次性录入扫描枪（HashBuilder），后面每过来一个旅客就扫一下枪（Probe）。
  • 计划关键字：HashBuilder[hash = (key)] 常出现在 SemiJoin 或 HashJoin 的右分支。
  1. FilterProject（过滤+投影）

  • 定义：Trino 里最轻量的算子，先过滤行（Filter）再裁剪列（Project），无 I/O 开销。

  • 类比：超市收银台：先把你购物车里的过期商品扔掉（Filter），再把不需要的小票副联撕掉（Project）。

  • 计划示例：

    1
    2
    └─ FilterProject[predicate = (status = 'PAID')]  
       └─ Scan…
  1. 宽表（Wide Table）
  • 定义：把多张维度表字段冗余到一张事实表里，减少 Join，牺牲存储换查询速度。
  • 类比：原本要点 5 份外卖（5 张表 Join），现在直接买一份“全家桶”（宽表），一口吃到所有菜。
  • 在 Trino 里：宽表通常以 Hive/Iceberg Parquet/ORC 形式存在，列式存储 + 分区，配合 列裁剪 扫描极快。
  1. 总结
术语 口诀
Hash Join 小表先变哈希，大表逐行探。
Semi Join 只问“有没有”，不拿右表数据。
HashBuilder 造哈希表的阶段，看右支。
FilterProject 先过滤行，再裁剪列。
宽表 把字段先拼好，省掉 Join。

🍋子查询其他

LATERAL横向子查询

  • 横向子查询是什么
  • 这效率很不高吧
  • 那我为什么不用普通 JOIN
  • ON true什么意思

“横向子查询”是 LATERAL子查询 的直译，也有人叫 横向派生表、横向连接。 它的核心作用只有一句话：

让右侧的子查询能够引用左侧已经列出的表/别名，实现“逐行计算”。

  1. 出现位置 只能出现在 FROM 或 JOIN 子句里，且前面必须带关键字 LATERAL（PostgreSQL、SQL Server、Oracle、MySQL 8.0+ 都支持）。
  2. 与普通子查询的区别
普通派生表 LATERAL 子查询
先整体算出结果，再拿给外层用 对外层每一行都重新计算一次
内部看不到外层的任何列 内部可以直接引用外层“左边”已经出现的列
  • 一眼看懂例子
    • 需求：对每笔订单，找出“该客户”历史订单里金额最高的那一条。
    • orders订单表
1
2
3
4
5
6
7
8
SELECT o.*, top.*
FROM orders AS o
LEFT JOIN LATERAL (
	SELECT * FROM orders o2
    WHERE o2.customer_id = o.customer_id      -- 直接用到外层 o 的列
    ORDER BY o2.amount DESC
    LIMIT 1
) AS top ON true;

典型场景速记

  1. 每组/每行求 Top-N、累积、相邻行、行列转换
  2. 把“相关子查询”改写成 JOIN 形式，方便利用索引、避免重复扫描
  3. 需要“行级函数”效果，又不想写存储过程

🍋子查询的时候用 EXISTS 还是 IN

简单来说：大多数情况下建议优先使用 EXISTS，因为它通常有更好的性能表现，并且能更自然地处理 NULL 值。

1. 核心区别

  • IN： 先执行子查询，将结果集存在内存中，然后对外部查询进行遍历，检查外部表的值是否在子查询的结果集中。
  • EXISTS： 对外部查询进行遍历，对每一行数据都执行一次子查询，如果子查询返回任何记录，则放入结果集（不关心子查询返回的具体内容，只关心是否有返回行）。

2. 性能对比

  • 当子查询表大，外部表小时（EXISTS 胜出）：
    如果子查询返回的数据量很大，使用 IN 可能会消耗较多内存和临时表空间。EXISTS 因为是一种半连接机制，通常可以利用索引更快地找到匹配项。

  • 当子查询表小，外部表大时（不一定）：
    如果子查询很小，IN 可能会更快（因为子查询只需要执行一次）。EXISTS 需要对外部表进行全表扫描，但如果外部表的关联字段有索引，EXISTS 依然很快。

  • 现代数据库的优化：

    在 MySQL 8.0+、PostgreSQL、SQL Server 等现代数据库中，优化器通常会自动将 IN 转换为 SEMI JOIN 或类似 EXISTS 的执行计划，两者性能差异已不大。

    但注意：当子查询无法使用索引时，手动选择仍然重要。

3. NOT IN 与 NOT EXISTS（巨大差异）

  • NOT IN 有风险：
    如果子查询的结果中包含任何一个 NULL，NOT IN 会返回空集（因为 id NOT IN (1, 2, NULL) 等价于 id != 1 AND id != 2 AND id != NULL，而 id != NULL 永远是未知的）。
    因此，如果使用 NOT IN，必须确保子查询的结果集剔除掉 NULL 值。
  • NOT EXISTS 很安全：
    不受 NULL 影响，只要子查询没找到匹配行，就返回真。

4. 使用建议

1. 优先使用 EXISTS 的场景

1
2
3
4
5
-- 检查存在性（相关子查询）
SELECT * FROM A 
WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id);

-- 优势：找到第一个匹配就停止，不会全表扫描B
  • 子查询表 B 很大，主查询表 A 较小
  • 需要判断”是否存在”而非获取具体值
  • 如果做反查询（找不存在的数据），除非能100%确定子查询结果不含 NULL，否则请直接使用 NOT EXISTS。

2. 优先使用 IN 的场景

1
2
3
4
5
6
7
--  固定小集合查询
SELECT * FROM A 
WHERE id IN (1, 2, 3, 4, 5);

--  子查询结果很小且确定
SELECT * FROM A 
WHERE id IN (SELECT id FROM B WHERE status = 'active' LIMIT 10);
  • 子查询结果集 很小且确定（如常量列表）
  • 需要匹配具体值列表

3. 避免使用的场景

1
2
3
4
5
-- ❌ IN + 子查询含 NULL（返回空结果）
SELECT * FROM A WHERE id IN (SELECT id FROM B); -- B.id 含 NULL

-- ❌ IN + 子查询结果集巨大（性能差）
SELECT * FROM A WHERE id IN (SELECT id FROM B); -- B 有百万行

5. 最佳实践总结

场景 推荐 原因
判断存在性 EXISTS 短路求值，性能稳定
小值列表 IN 简洁高效
子查询可能含 NULL EXISTS 或 IN + NOT NULL 过滤 避免逻辑错误
两表都很大 EXISTS 或 JOIN 优化器更易生成高效计划

一句话原则：不确定时，用 EXISTS 更安全；确定子查询结果很小，用 IN 更简洁。