当前位置：首页 > news >正文

深入理解 APPLY：写出更可读、更高性能的相关子查询

news 2025/9/6 15:18:03

1. 为什么需要 `APPLY`？

传统 JOIN 的右侧必须是“静态可独立求值”的表/视图/子查询；一旦右侧表达式需要引用左表的列并进行逐行计算（如 Top-N、行级聚合、表值函数 TVF、JSON 拆解），写起来就会变得别扭甚至无法表达。
APPLY 恰好解决了这个“每行驱动右侧子查询”的问题：

CROSS APPLY：类似 INNER JOIN，右侧没结果则丢弃左行。
OUTER APPLY：类似 LEFT JOIN，右侧没结果保留左行、以 NULL 补位。

明确观点：当你需要“左表逐行 → 右表动态求值”时，用 APPLY 往往比“嵌套子查询 + 聚合 + 复杂 JOIN”更直观、更易优化。

2. 基本语法与语义

-- 行级（依赖左表列）右侧表达式
SELECT ...
FROM left_table lt
CROSS APPLY ( right_table_expression ) r   -- 有结果才保留
-- 或
OUTER APPLY ( right_table_expression ) r   -- 无结果也保留（右侧列为 NULL）

右侧的 right_table_expression 可为：

相关子查询（可引用 lt 的列）
表值函数（TVF）
内联派生表 / VALUES / JSON 拆解结果

3. 典型场景与高质量示例

3.1 每组 Top-N（如每店最新一单）

需求：对每个店铺取“按下单时间倒序的第 1 条订单”。

-- 关键：右侧 TOP 1 能利用索引 (shop_id, order_time DESC)
SELECT s.shop_id, o.order_id, o.order_time, o.amount
FROM shops s
OUTER APPLY (SELECT TOP 1 order_id, order_time, amountFROM ordersWHERE orders.shop_id = s.shop_idORDER BY order_time DESC
) o;

索引建议：

CREATE INDEX ix_orders_shop_time_desc
ON orders (shop_id, order_time DESC)
INCLUDE (amount);

观点：Top-N per group 用 APPLY + 合适复合索引，通常比窗口函数加 ROW_NUMBER() 再过滤更容易被优化为 索引有序扫描，避免全局排序。

3.2 行级聚合（动态窗口）

需求：对每个店铺动态计算近 1/7/30 天销量。

DECLARE @d1  DATETIME = DATEADD(DAY, -1,  GETDATE());
DECLARE @d7  DATETIME = DATEADD(DAY, -7,  GETDATE());
DECLARE @d30 DATETIME = DATEADD(DAY, -30, GETDATE());SELECT s.shop_id,COALESCE(sa.total,0) AS sale_total,COALESCE(sa.d1,0)    AS d1,COALESCE(sa.d7,0)    AS d7,COALESCE(sa.d30,0)   AS d30
FROM shops s
OUTER APPLY (SELECT SUM(qty) AS total,SUM(CASE WHEN order_time >= @d1  THEN qty ELSE 0 END) AS d1,SUM(CASE WHEN order_time >= @d7  THEN qty ELSE 0 END) AS d7,SUM(CASE WHEN order_time >= @d30 THEN qty ELSE 0 END) AS d30FROM salesWHERE sales.shop_id = s.shop_id
) sa;

观点：把时间边界提前参数化，避免 WHERE order_time >= DATEADD(...) 造成非 SARGable，便于范围索引命中。

3.3 表值函数（TVF）调用（每行动态取明细）

-- 以客户为左表，右侧 TVF 依赖客户 ID
SELECT c.customer_id, o.order_id, o.amount
FROM customers c
CROSS APPLY dbo.fn_recent_orders(c.customer_id) o; -- 返回该客户最近订单集合

经验判断：

内联 TVF（单语句返回查询）往往可被优化器内联，性能更好；
多语句 TVF 往往有较差的行数估计，需谨慎（可考虑临时表中转或重写为内联/派生表）。

3.4 拆分 JSON/字符串并回连（电商属性、标签）

-- JSON 属性拆解为行，并与主维度回连
SELECT p.product_id, j.[key] AS attr_key, j.value AS attr_value
FROM products p
CROSS APPLY OPENJSON(p.ext_json) WITH ([key] NVARCHAR(100) '$.k', value NVARCHAR(MAX) '$.v') j;

或 STRING_SPLIT：

SELECT u.user_id, s.value AS tag
FROM users u
CROSS APPLY STRING_SPLIT(u.tags_csv, ',') s;

观点：APPLY + OPENJSON/STRING_SPLIT 是结构化存储与半结构化字段之间的黄金桥梁。

3.5 `CROSS APPLY (VALUES ...)` 生成派生列/行

SELECT o.order_id, v.gross, v.net
FROM orders o
CROSS APPLY (VALUES (o.amount, o.amount - o.discount)) AS v(gross, net);

观点：用 VALUES 写多派生列更整洁，且避免重复表达式计算。

3.6 以“全集驱动”的销售+库存（替代 FULL JOIN）

贴近电商/SaaS：给定 product_code，输出所有涉及店铺的销量与库存。

DECLARE @product_code VARCHAR(50) = 'P001';
DECLARE @d30 DATETIME = DATEADD(DAY, -30, GETDATE());WITH shop AS (SELECT DISTINCT shop_code FROM sales WHERE product_code=@product_codeUNIONSELECT DISTINCT warehouse_code FROM stock WHERE product_code=@product_code AND is_primary = 1
)
SELECT s.shop_code,COALESCE(sa.total,0)  AS sale_total_30d,COALESCE(st.qty,0)    AS stock_qty
FROM shop s
OUTER APPLY (SELECT SUM(qty) AS totalFROM salesWHERE sales.product_code=@product_codeAND sales.shop_code=s.shop_codeAND sales.order_time >= @d30
) sa
OUTER APPLY (SELECT SUM(qty) AS qtyFROM stockWHERE stock.product_code=@product_codeAND stock.warehouse_code=s.shop_codeAND stock.is_primary=1
) st;

观点：先构造“店铺全集”，再用 APPLY 逐店计算，结构清晰，避免 FULL JOIN 的代价与可读性问题。

4. 性能工程（你真正会用到的部分）

让右侧子查询“可走索引”
- Top-N：索引前缀匹配 + 排序列同索引顺序（见 3.1）。
- 范围聚合： (dimension, time) 复合索引，并参数化时间边界。
小左表驱动大右表
- 逻辑是“左表每行执行一次右侧表达式”。让左侧尽量精简（预过滤、去重、预聚合），显著降低右侧重复扫描。
预聚合/临时表/物化
- 明细超大（亿级）时，把“近 30 天 × 门店 × 商品”的聚合预计算到汇总表；查询直接扫汇总，APPLY 用在最后一公里的拼装。
避免非 SARGable
- 把 DATEADD(...) 等调用移到变量，或通过参数传入；必要时使用计算列 + 持久化索引。
TVF 选择
- 优先内联 TVF；若必须多语句 TVF，控制其输出规模，并在调用处尽量收窄左侧数据集。
并发与阻塞
- APPLY 自身不改变事务语义；但右侧高频聚合/排序易触发大内存授予或 TempDB 压力。可配合 TOP + 合适索引、批量页（Batch Mode on Rowstore/列存）优化。

5. 可移植性与对照表

目标	写法
SQL Server	`CROSS APPLY` / `OUTER APPLY`
PostgreSQL	`JOIN LATERAL`（`LEFT JOIN LATERAL` ≈ OUTER，`CROSS JOIN LATERAL` ≈ CROSS）
Oracle 12c+	`LATERAL`（语义等价）
MySQL	无 `APPLY`/`LATERAL`；需改写为派生表 + JOIN 或窗口函数方案

示例（Top1 per group）

PostgreSQL：

SELECT s.shop_id, o.order_id
FROM shops s
LEFT JOIN LATERAL (SELECT order_idFROM orders oWHERE o.shop_id = s.shop_idORDER BY order_time DESCLIMIT 1
) o ON TRUE;

MySQL（8.0）可用窗口函数替代：

WITH ranked AS (SELECT o.*, ROW_NUMBER() OVER(PARTITION BY shop_id ORDER BY order_time DESC) AS rnFROM orders o
)
SELECT s.shop_id, r.order_id
FROM shops s
LEFT JOIN ranked r ON r.shop_id = s.shop_id AND r.rn = 1;

观点：跨库迁移时，优先寻找 LATERAL 等价语法；若没有，就用 窗口函数/派生表 重写，保证索引仍然可用。

6. 误用与反例（避坑清单）

能用简单 JOIN/窗口函数解决的，不滥用 APPLY：
例如全局聚合、等值关联 + 汇总，不需要逐行右侧求值。
左表过大、右侧未索引：
每行触发一次“全表或大范围扫描”，放大 N 倍 IO/CPU。
右侧产生“多行 × 多行”乘积：
APPLY 本质是“连接”，右侧返回多行会行数膨胀；必要时加 TOP/聚合/去重。
多语句 TVF 大量输出：
行数估计差，常导致糟糕计划；能内联就内联，不能则考虑临时表中转。

7. 选型与落地：决策树

是否需要右表依赖左表列的逐行计算？

否 → 用普通 JOIN/窗口函数。
是 → 进入 (2)。

右表是否能被索引高效定位（Top-N 或范围）？

能 → APPLY 是首选。
不能 → 先做预聚合/临时表，再 APPLY。

输出是否要求“无匹配也保留左行”？

是 → OUTER APPLY。
否 → CROSS APPLY。

8. 附：可执行最小示例（SQL Server）

-- 示例数据
CREATE TABLE shops (shop_id INT PRIMARY KEY, name NVARCHAR(50));
CREATE TABLE orders (order_id INT PRIMARY KEY,shop_id  INT,order_time DATETIME,amount DECIMAL(18,2)
);INSERT INTO shops VALUES (1,'A'),(2,'B'),(3,'C');
INSERT INTO orders VALUES
(101,1,'2025-09-01',100),(102,1,'2025-09-03',80),
(201,2,'2025-08-30',50);-- 索引：Top-N 利用
CREATE INDEX ix_orders_shop_time_desc ON orders(shop_id, order_time DESC) INCLUDE (amount);-- 每店最新一单（OUTER APPLY）
SELECT s.shop_id, s.name, o.order_id, o.order_time, o.amount
FROM shops s
OUTER APPLY (SELECT TOP 1 order_id, order_time, amountFROM ordersWHERE orders.shop_id = s.shop_idORDER BY order_time DESC
) o;