SQL Server 开发系列（第四期）：连接与子查询——JOIN 的底层逻辑与性能调优SQL Server 开发系列（

SQL Server 开发系列（第四期）：连接与子查询——JOIN 的底层逻辑与性能调优

上一期我们深入了 DML 语句。这一期，我们来到 SQL 开发中最核心也最容易出问题的领域——连接（JOIN）和子查询。你是否遇到过：明明两个表都有索引，JOIN 却慢如蜗牛？IN 和 EXISTS 到底哪个更快？LEFT JOIN 为什么比 INNER JOIN 慢那么多？这些问题的答案，都藏在 JOIN 的三种底层实现算法里。理解它们，你就能从“写对 SQL”进阶到“写出高效的 SQL”。

一、三种 JOIN 算法的底层原理

SQL Server 使用三种物理连接算法：Nested Loops、Hash Match 和 Merge Join。优化器会根据数据量、索引、排序情况自动选择。

1.1 Nested Loops（嵌套循环）

原理：对外表（驱动表）的每一行，在内表中查找匹配行。

for each row in outer_table:
    for each row in inner_table:
        if match_condition:
            output row

复杂度：O(N × M)，N 是外表行数，M 是内表匹配次数。

适用场景：

外表很小（如 < 1000 行）
内表在连接列上有索引（可快速定位）
典型应用：主表查少量数据，关联明细表

性能特征：

✅ 外表极小时性能极佳
❌ 外表大时性能崩溃
需要内表有索引

-- 强制使用 Nested Loops（通常不需要）
SELECT * FROM Orders o
INNER LOOP JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderID = 100;

1.2 Hash Match（哈希匹配）

原理：

构建阶段：读取小表（构建表），在内存中建立哈希表
探测阶段：读取大表（探测表），对每一行计算哈希值，在哈希表中查找匹配

适用场景：

两表都很大，且无索引
等值连接（=）
数据未排序

性能特征：

✅ 适合大表 + 大表
✅ 不需要索引
❌ 消耗内存（可能溢出到 tempdb）
❌ 不适用于非等值连接（>、<）

-- 强制使用 Hash Match
SELECT * FROM LargeTable1 l1
INNER HASH JOIN LargeTable2 l2 ON l1.Key = l2.Key;

1.3 Merge Join（合并连接）

原理：

两表按连接列排序
像拉链一样，同时遍历两表，匹配行

适用场景：

两表都已按连接列排序（如有聚集索引或已排序）
等值连接或非等值连接（>、<）

性能特征：

✅ 无需索引，但需要排序
✅ 内存消耗小
✅ 适合大数据量
❌ 排序开销大（若无预排序）

-- 强制使用 Merge Join
SELECT * FROM Orders o
INNER MERGE JOIN OrderDetails d ON o.OrderID = d.OrderID
ORDER BY o.OrderID;  -- 有 ORDER BY 可能触发 Merge Join

1.4 三种算法对比总结

维度	Nested Loops	Hash Match	Merge Join
时间复杂度	O(N × M)	O(N + M)	O(N + M)
内存消耗	极低	高（哈希表）	低
索引需求	内表需要	不需要	需要排序
外表大小	小	任意	任意
连接类型	任意	仅等值	等值/非等值
典型场景	主查从	大表对大表	已排序数据

二、如何查看 JOIN 算法

-- 执行查询，查看执行计划
SET SHOWPLAN_XML ON;  -- 或使用 SSMS 的"显示实际执行计划"

SELECT o.OrderID, c.CustomerName
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate >= '2024-01-01';

-- 在执行计划中查找：Nested Loops、Hash Match、Merge Join

![执行计划中的 JOIN 图标描述]

三、INNER JOIN vs LEFT JOIN 的性能陷阱

3.1 语义不同，性能不同

-- INNER JOIN：只返回匹配的行
SELECT o.OrderID, c.CustomerName
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID;
-- 如果 Customers 表有 200 万行，Orders 有 100 万行匹配，返回 100 万行

-- LEFT JOIN：返回左表所有行
SELECT o.OrderID, c.CustomerName
FROM Orders o
LEFT JOIN Customers c ON o.CustomerID = c.CustomerID;
-- 返回 Orders 的所有 5000 万行，即使 Customer 不存在

3.2 LEFT JOIN 的常见误区

-- ❌ 错误：WHERE 条件抵消了 LEFT JOIN
SELECT o.OrderID, c.CustomerName
FROM Orders o
LEFT JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE c.City = 'Beijing';  -- 这会把 LEFT JOIN 变成 INNER JOIN！
-- 因为 NULL 的 City 被 WHERE 过滤掉了

-- ✅ 正确：将条件放在 ON 子句中
SELECT o.OrderID, c.CustomerName
FROM Orders o
LEFT JOIN Customers c ON o.CustomerID = c.CustomerID AND c.City = 'Beijing';

3.3 什么时候 LEFT JOIN 比 INNER JOIN 慢？

-- 场景：Orders（5000万行） LEFT JOIN Customers（200万行）
-- 即使没有匹配，SQL Server 也要处理 Orders 的每一行
-- 比 INNER JOIN 多处理了所有不匹配的行

四、EXISTS vs IN vs JOIN 性能对比

4.1 三种写法的语义

-- 方式1：IN
SELECT * FROM Orders 
WHERE CustomerID IN (SELECT CustomerID FROM VIPCustomers);

-- 方式2：EXISTS
SELECT * FROM Orders o
WHERE EXISTS (SELECT 1 FROM VIPCustomers v WHERE v.CustomerID = o.CustomerID);

-- 方式3：INNER JOIN
SELECT o.* 
FROM Orders o
INNER JOIN VIPCustomers v ON o.CustomerID = v.CustomerID;

4.2 性能对比（执行计划）

场景	IN	EXISTS	JOIN
子查询小，外表大	好	好	可能重复行
子查询大，外表小	差（全扫描子查询）	好（相关子查询）	好
子查询无重复	好	好	好
子查询有重复	好（自动去重）	好	❌ 可能产生重复行

4.3 实战：选择正确的写法

-- 场景1：检查订单是否来自 VIP 客户（VIPCustomers 很小）
-- ✅ 推荐：IN 或 EXISTS（执行计划相同）
SELECT * FROM Orders WHERE CustomerID IN (SELECT CustomerID FROM VIPCustomers);

-- 场景2：查询有订单的客户（Orders 很大，Customers 较小）
-- ✅ 推荐：EXISTS（可提前终止）
SELECT * FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID);

-- 场景3：需要返回子查询的列
-- ✅ 推荐：JOIN
SELECT o.*, v.VIPLevel 
FROM Orders o
JOIN VIPCustomers v ON o.CustomerID = v.CustomerID;

-- 场景4：检查不存在的记录（NOT EXISTS vs NOT IN）
-- ✅ 推荐：NOT EXISTS（处理 NULL 更安全）
SELECT * FROM Customers c
WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID);

-- ❌ 危险：NOT IN 遇到 NULL 会返回空结果
SELECT * FROM Customers 
WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders);
-- 如果 Orders.CustomerID 包含 NULL，整个查询返回空！

4.4 NOT EXISTS vs NOT IN vs LEFT JOIN...NULL

-- 三种"不存在"的写法性能对比
-- 1. NOT EXISTS（通常最快）
SELECT * FROM Customers c
WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID);

-- 2. LEFT JOIN...NULL
SELECT c.* 
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL;

-- 3. NOT IN（最危险，遇到 NULL 出错）
SELECT * FROM Customers 
WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders);

五、子查询优化：相关 vs 非相关

5.1 非相关子查询

子查询独立于外部查询，只执行一次。

-- 非相关子查询：子查询先执行一次
SELECT * FROM Orders
WHERE CustomerID IN (SELECT CustomerID FROM VIPCustomers WHERE Level = 'Gold');
-- 执行顺序：
-- 1. 执行子查询，获取 Gold 客户列表（一次）
-- 2. 对 Orders 每一行检查是否在列表中

5.2 相关子查询

子查询依赖外部查询的当前行，每行执行一次。

-- 相关子查询：每行都要执行子查询
SELECT o.*, 
    (SELECT COUNT(*) FROM OrderDetails d WHERE d.OrderID = o.OrderID) AS DetailCount
FROM Orders o;
-- 执行顺序：
-- 1. 取一行 Orders
-- 2. 执行子查询，计算该订单的明细数
-- 3. 重复 5000 万次（性能灾难！）

5.3 将相关子查询改写为 JOIN

-- ❌ 差：相关子查询（5000 万次执行）
SELECT o.OrderID, o.Amount,
    (SELECT SUM(d.Amount) FROM OrderDetails d WHERE d.OrderID = o.OrderID) AS DetailTotal
FROM Orders o;

-- ✅ 好：JOIN + GROUP BY（一次处理）
SELECT o.OrderID, o.Amount, ISNULL(SUM(d.Amount), 0) AS DetailTotal
FROM Orders o
LEFT JOIN OrderDetails d ON o.OrderID = d.OrderID
GROUP BY o.OrderID, o.Amount;

六、实战：优化慢查询

场景：电商报表，查询每个客户的订单总额和最后下单时间

原始慢查询（相关子查询）

-- 耗时：45 秒
SELECT 
    c.CustomerID,
    c.CustomerName,
    (SELECT SUM(Amount) FROM Orders o WHERE o.CustomerID = c.CustomerID) AS TotalAmount,
    (SELECT MAX(OrderDate) FROM Orders o WHERE o.CustomerID = c.CustomerID) AS LastOrderDate
FROM Customers c
WHERE c.Status = 'Active';

优化方案1：JOIN + GROUP BY

-- 耗时：2.5 秒
SELECT 
    c.CustomerID,
    c.CustomerName,
    ISNULL(SUM(o.Amount), 0) AS TotalAmount,
    MAX(o.OrderDate) AS LastOrderDate
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE c.Status = 'Active'
GROUP BY c.CustomerID, c.CustomerName;

优化方案2：使用窗口函数（SQL Server 2012+）

-- 耗时：1.8 秒
WITH OrderStats AS (
    SELECT 
        CustomerID,
        SUM(Amount) AS TotalAmount,
        MAX(OrderDate) AS LastOrderDate
    FROM Orders
    GROUP BY CustomerID
)
SELECT c.CustomerID, c.CustomerName, 
       ISNULL(o.TotalAmount, 0) AS TotalAmount,
       o.LastOrderDate
FROM Customers c
LEFT JOIN OrderStats o ON c.CustomerID = o.CustomerID
WHERE c.Status = 'Active';

优化方案3：索引 + 统计信息

-- 创建覆盖索引
CREATE INDEX IX_Orders_Customer_Amount_Date 
ON Orders (CustomerID) INCLUDE (Amount, OrderDate);

-- 更新统计信息
UPDATE STATISTICS Orders;
UPDATE STATISTICS Customers;

-- 优化后耗时：0.3 秒

七、JOIN 优化检查清单

检查 JOIN 类型：INNER JOIN 还是 LEFT JOIN？能否改为 INNER？
检查连接列是否有索引：外键列通常需要索引
检查数据倾斜：某个值占比过高可能导致算法选择错误
检查隐式转换：连接列类型不一致会导致索引失效
检查 NULL 处理：NOT IN 遇到 NULL 会出错，用 NOT EXISTS
检查重复行：JOIN 前先确保连接列唯一性
检查算法：执行计划中用的是哪种 JOIN？

-- 诊断 JOIN 性能的 DMV 查询
SELECT TOP 10
    qs.total_logical_reads,
    qs.execution_count,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS query
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE '%JOIN%'
ORDER BY qs.total_logical_reads DESC;

八、核心总结

知识点	核心要点
Nested Loops	外表小 + 内表有索引 → 最快
Hash Match	大表对大表 + 等值连接 → 适用
Merge Join	两表已排序 → 高效
IN vs EXISTS	子查询小用 IN，外表小用 EXISTS
NOT EXISTS	比 NOT IN 安全（处理 NULL）
相关子查询	避免在 SELECT 列表中使用，改 JOIN
LEFT JOIN	WHERE 条件会抵消左连接效果
优化方向	索引 + 统计信息 + 正确算法

一句话记住本期内容：

JOIN 的底层是三种算法——小表驱动大表用 Nested Loops，大表对大表用 Hash Match，已排序用 Merge Join；EXISTS 比 IN 安全，JOIN 比相关子查询快。

动手练习

-- 问题1：以下两个查询性能差异巨大，为什么？
-- 查询A（快）
SELECT * FROM Orders o
WHERE EXISTS (SELECT 1 FROM Customers c WHERE c.CustomerID = o.CustomerID AND c.City = 'Beijing');

-- 查询B（慢）
SELECT * FROM Orders o
WHERE o.CustomerID IN (SELECT CustomerID FROM Customers WHERE City = 'Beijing');

-- 问题2：如何优化这个查询？
SELECT 
    o.OrderID,
    (SELECT TOP 1 ProductName FROM OrderDetails d 
     JOIN Products p ON d.ProductID = p.ProductID 
     WHERE d.OrderID = o.OrderID ORDER BY d.Quantity DESC) AS TopProduct
FROM Orders o
WHERE o.OrderDate >= '2024-01-01';

-- 问题3：什么情况下 LEFT JOIN 比 INNER JOIN 慢很多？

点击查看参考答案

问题1：如果 Customers 表很大（如 200 万行）：

查询A（EXISTS）：对 Orders 的每一行，在 Customers 上 Seek（有索引）
查询B（IN）：先执行子查询，将 200 万行结果集物化，再与 Orders 匹配
解决方案：确保 Customers.CustomerID 有索引

问题2 优化：使用窗口函数

WITH RankedProducts AS (
    SELECT 
        d.OrderID,
        p.ProductName,
        ROW_NUMBER() OVER (PARTITION BY d.OrderID ORDER BY d.Quantity DESC) AS rn
    FROM OrderDetails d
    JOIN Products p ON d.ProductID = p.ProductID
)
SELECT o.OrderID, rp.ProductName
FROM Orders o
LEFT JOIN RankedProducts rp ON o.OrderID = rp.OrderID AND rp.rn = 1
WHERE o.OrderDate >= '2024-01-01';

问题3：当左表很大且大部分行在右表中没有匹配时，LEFT JOIN 需要处理所有行，而 INNER JOIN 只处理匹配的行。例如：Orders（1 亿行）LEFT JOIN Returns（退货表，只有 1 万行），返回 1 亿行，而 INNER JOIN 只返回 1 万行。

下一期预告

窗口函数——轻松实现排名、累计、移动计算

ROW_NUMBER、RANK、DENSE_RANK 的区别与应用
累计求和：SUM OVER(ORDER BY ...)
移动平均、同比环比计算
用窗口函数替代自连接和相关子查询
实战：分页、去重、分组 Top N

📌 本文代码已在 SQL Server 2019+ 验证。JOIN 优化是 SQL 性能调优的核心，建议多查看执行计划，理解三种算法的切换条件。

本系列持续更新中，点击关注不错过第五期。