SQL Server 性能优化实战（第三期）：执行计划深度解析——让 SQL Server 告诉你慢在哪里

绩隐金
0 阅读9分钟

SQL Server 性能优化实战（第三期）：执行计划深度解析——让 SQL Server 告诉你慢在哪里

前两期我们深入讲解了索引的原理与设计。但现实中，你加了一个自认为完美的索引，查询却依然慢如蜗牛——问题出在哪？答案是：执行计划。SQL Server 会把你的 T-SQL 语句转换成一套实际执行方案，就像一张地图，清晰地展示了每一步操作。学会看懂这张地图，你就不再是“猜”哪里慢，而是“看”哪里慢。

一、什么是执行计划？

执行计划是 SQL Server 查询优化器为 SQL 语句生成的操作步骤清单。它告诉你：

  • 数据从哪个表/索引读取
  • 用了 Seek 还是 Scan
  • 表之间如何 JOIN（Nested Loops、Hash Match、Merge Join）
  • 数据在哪里排序、聚合、过滤
  • 每一步的预估成本和实际行数

1.1 如何获取执行计划？

方式命令/操作适用场景
估计执行计划SSMS 工具栏按钮快速预览，无需执行
实际执行计划SSMS 工具栏按钮真实运行，获取实际行数
SET STATISTICS PROFILE ONT-SQL 命令脚本化分析
SET STATISTICS XML ONT-SQL 命令获取 XML 格式
查询存储Query Store历史趋势分析

最常用：在 SSMS 中点击 “包括实际执行计划” 按钮（Ctrl+M），然后执行查询。

二、读懂执行计划的图标

2.1 核心图标速查表

图标操作名含义性能影响
🔍Index Seek利用索引精确定位✅ 好
📄Index Scan扫描整个索引⚠️ 一般
📑Table Scan扫描整个堆表❌ 差
🔗Key Lookup回表查缺失列⚠️ 需避免
🔗RID Lookup堆表回表⚠️ 需避免
🔀Nested Loops循环嵌套 JOIN✅ 适合小数据
🧩Hash Match哈希匹配 JOIN✅ 适合大数据
📊Merge Join合并 JOIN✅ 适合已排序
📐Sort显式排序⚠️ 消耗内存
🧮Hash Match (Agg)哈希聚合⚠️ 消耗内存
🧹Filter过滤条件一般
🧩Spool假脱机（临时存储）⚠️ 可能有问题

2.2 如何阅读执行计划

核心原则：从右往左、从下往上读。

  • 右端：数据源（表、索引）
  • 向左流动：每一步对数据进行变换
  • 最左端：最终结果返回给客户端

关键指标：

  • Estimated Subtree Cost：整个操作的预估成本，越低越好
  • Actual Number of Rows：实际处理的行数
  • Estimated Number of Rows：预估行数（关键对比点）

2.3 实战示例

-- 创建测试数据
DROP TABLE IF EXISTS Orders;
CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    Amount DECIMAL(10,2)
);

INSERT INTO Orders (CustomerID, OrderDate, Amount)
SELECT TOP 100000 
    ABS(CHECKSUM(NEWID())) % 10000,
    DATEADD(day, ABS(CHECKSUM(NEWID())) % 3650, '2020-01-01'),
    ROUND(RAND(CHECKSUM(NEWID())) * 10000, 2)
FROM sys.all_columns a CROSS JOIN sys.all_columns b;

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID ON Orders(CustomerID);
GO

-- 执行查询并查看执行计划
SELECT OrderID, CustomerID, OrderDate, Amount
FROM Orders
WHERE CustomerID = 1234
ORDER BY OrderDate DESC;

观察执行计划：

  1. 右端：Index Seek (IX_Orders_CustomerID) → 找到匹配 CustomerID 的行
  2. 中间：Key Lookup → 回到聚集索引取 OrderDate、Amount
  3. 左端：Sort → 对 OrderDate 排序

发现性能问题：

  • Key Lookup：说明索引不覆盖，需要回表
  • Sort：ORDER BY 无法利用索引

优化方案：

-- 创建覆盖索引，同时支持排序
CREATE INDEX IX_Orders_CustomerDate 
ON Orders(CustomerID, OrderDate) 
INCLUDE (Amount);

三、三大性能杀手算子

3.1 Key Lookup（书签查找）—— 最常遇到的杀手

出现原因：非聚集索引不包含查询所需的所有列。

危害：每行数据需要额外一次随机 I/O，数据量大时性能急剧下降。

解决方案：

  1. 覆盖索引：使用 INCLUDE 添加 SELECT 列
  2. 聚集索引：如果查询条件频繁，考虑调整聚集索引
  3. 宽索引：将常用列加入索引键（但注意顺序）
-- 坏：有 Lookup
SELECT OrderID, CustomerID, Amount FROM Orders WHERE CustomerID = 123;

-- 好：无 Lookup
CREATE INDEX IX_Customer_Cover ON Orders(CustomerID) INCLUDE (OrderID, Amount);

3.2 Sort（排序）—— 内存消耗大户

出现原因：ORDER BY、GROUP BY、DISTINCT、UNION 等需要排序的操作。

危害：

  • 消耗 tempdb 和内存
  • 数据量大时可能溢出到磁盘
  • 阻塞后续操作

解决方案：

  • 在索引中包含排序列，且顺序与 ORDER BY 一致
  • 考虑是否真的需要排序（比如前端排序）
-- 查询：按 OrderDate 倒序
SELECT * FROM Orders WHERE CustomerID = 123 ORDER BY OrderDate DESC;

-- 优化：索引 (CustomerID, OrderDate DESC)
-- 注意：SQL Server 2016+ 支持降序索引
CREATE INDEX IX_Cust_Date_Desc ON Orders(CustomerID, OrderDate DESC);

3.3 Spool（假脱机）—— 隐式临时表

出现原因：执行计划需要多次读取同一个数据集（如子查询多次引用、窗口函数等）。

危害：

  • 消耗 tempdb 空间
  • 增加 I/O 和内存压力
  • 通常意味着执行计划不够优化

解决方案：

  • 使用 CTE 或临时表显式控制
  • 重写查询避免重复引用
-- 可能产生 Spool 的查询
SELECT * FROM Orders 
WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE Region = 'North')
   OR OrderDate IN (SELECT OrderDate FROM BigTable WHERE Condition = 1);

四、预估 vs 实际——统计信息的秘密

4.1 为什么预估会不准？

统计信息是 SQL Server 用来估算行数的数据分布图。当统计信息过期或不准确时，优化器可能选择错误的执行计划。

典型症状：

  • 执行计划显示预估行数 = 1，实际行数 = 10000
  • 查询突然变慢，重建索引/更新统计信息后恢复

4.2 查看统计信息

-- 查看表的统计信息
DBCC SHOW_STATISTICS ('Orders', 'IX_Orders_CustomerID');

-- 更新统计信息
UPDATE STATISTICS Orders;
-- 或全库更新
EXEC sp_updatestats;

4.3 统计信息的关键指标

列名含义何时注意
Rows表总行数基数估算基础
Rows Sampled采样行数小于 100% 可能不准
Density密度（1/唯一值数）判断选择性
Average Key Length平均键长度索引大小参考
Histogram直方图数据分布细节

4.4 统计信息更新策略

-- 全表扫描更新（最准确，但耗时）
UPDATE STATISTICS Orders WITH FULLSCAN;

-- 采样更新（默认）
UPDATE STATISTICS Orders WITH SAMPLE 20 PERCENT;

-- 仅更新特定索引的统计信息
UPDATE STATISTICS Orders IX_Orders_CustomerID;

-- 异步更新（避免阻塞，但可能使用旧统计）
ALTER DATABASE YourDB SET AUTO_UPDATE_STATISTICS_ASYNC ON;

五、实战：从慢查询到执行计划到优化

场景复现

业务需求：查询 2024 年某客户的订单，按金额降序取前 100 条。

原始查询：

SELECT TOP 100 
    o.OrderID, 
    o.OrderDate, 
    c.CustomerName,
    o.Amount
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.CustomerID = 12345
  AND o.OrderDate >= '2024-01-01'
  AND o.OrderDate < '2025-01-01'
ORDER BY o.Amount DESC;

Step 1：获取执行计划

在 SSMS 中按 Ctrl+M，执行查询，观察执行计划。

发现的问题：

  1. ❌ Table Scan on Customers（或者 Index Scan）
  2. ❌ Key Lookup on Orders
  3. ❌ Sort（因为 ORDER BY Amount DESC，但索引是 OrderDate）
  4. ❌ Hash Join（两表都较大）

Step 2：分析瓶颈

查看执行计划中各操作的占比（将鼠标悬停在图标上）：

  • 如果 Key Lookup 占 70% → 覆盖索引问题
  • 如果 Sort 占 60% → 索引排序问题
  • 如果 Hash Join 占 50% → JOIN 策略或索引问题

Step 3：逐步优化

优化1：为 Orders 创建覆盖索引

-- 查询中 CustomerID 是等值 + OrderDate 是范围 + ORDER BY Amount
-- 索引键：(CustomerID, OrderDate) 还是 (CustomerID, Amount)？
-- 注意：ORDER BY Amount 如果放在索引键中，可以消除 Sort

CREATE INDEX IX_Orders_Opt 
ON Orders(CustomerID, Amount DESC, OrderDate) 
INCLUDE (OrderID);
-- 因为 OrderDate 是范围查询，放在最后
-- Amount 用于排序，放在第二列

优化2：确保 JOIN 列有索引

-- Customers 表的 CustomerID 应该是主键或至少有索引
-- 如果没有，添加
CREATE INDEX IX_Customers_ID ON Customers(CustomerID) INCLUDE (CustomerName);

优化3：重写查询（如果必要）

-- 使用 CTE 先过滤 Orders，再 JOIN
WITH FilteredOrders AS (
    SELECT TOP 100 
        OrderID, CustomerID, OrderDate, Amount
    FROM Orders
    WHERE CustomerID = 12345
      AND OrderDate >= '2024-01-01'
      AND OrderDate < '2025-01-01'
    ORDER BY Amount DESC
)
SELECT f.OrderID, f.OrderDate, c.CustomerName, f.Amount
FROM FilteredOrders f
JOIN Customers c ON f.CustomerID = c.CustomerID;

Step 4：验证优化效果

对比优化前后的：

  • 执行计划：Key Lookup 和 Sort 消失
  • 逻辑读取：使用 SET STATISTICS IO ON 查看
  • 耗时：从秒级降到毫秒级

六、执行计划分析快速检查清单

当遇到慢查询时，按以下顺序检查执行计划：

  • 是否有 Table Scan 或 Index Scan？ → 考虑添加索引
  • 是否有 Key Lookup 或 RID Lookup？ → 改为覆盖索引
  • 是否有 Sort？ → 索引能否消除排序？
  • 是否有 Spool？ → 重写查询，避免重复引用
  • 预估行数 vs 实际行数是否差异巨大（10倍以上）？ → 更新统计信息
  • JOIN 类型是否合理？
    • 小表 + 大表 → Nested Loops 最优
    • 两张大表 → Hash Match 或 Merge Join
  • 是否有隐式类型转换？ → 修复数据类型匹配
  • 是否有函数包裹索引列？ → 如 WHERE YEAR(OrderDate)=2024

七、核心总结

知识点核心要点
获取执行计划Ctrl+M（实际执行计划）是最常用方式
阅读顺序从右往左、从下往上
Key Lookup回表操作，用 INCLUDE 消除
Sort排序消耗，用索引顺序消除
统计信息预估行 ≠ 实际行时，更新统计信息
优化流程获取计划 → 识别瓶颈 → 添加/修改索引 → 验证

一句话记住本期内容：

执行计划是 SQL Server 的体检报告——学会从右往左读，找出 Key Lookup、Sort、Scan 三大杀手，用索引和统计信息对症下药。

动手练习

-- 现有表和查询：
CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    ProductID INT,
    SaleDate DATETIME,
    Quantity INT,
    Revenue DECIMAL(10,2)
);

CREATE INDEX IX_Sales_Product ON Sales(ProductID);

-- 慢查询：
SELECT SaleID, ProductID, SaleDate, Quantity, Revenue
FROM Sales
WHERE ProductID = 100
  AND SaleDate >= '2024-01-01'
  AND SaleDate < '2024-02-01'
ORDER BY Revenue DESC;

问题：

  1. 查看执行计划，会有什么问题？
  2. 应该如何创建新索引？
  3. 更新统计信息用哪个命令？
点击查看参考答案

1. 执行计划问题：

  • Key Lookup（因为索引没有 SaleDate、Revenue、Quantity）
  • Sort（ORDER BY Revenue 无法利用索引）

2. 推荐索引：

-- 方案1：覆盖索引 + 消除排序
CREATE INDEX IX_Sales_Product_Revenue 
ON Sales(ProductID, Revenue DESC) 
INCLUDE (SaleDate, Quantity);
-- 注意：SaleDate 是范围条件，但放在 INCLUDE 中会导致回表？
-- 更优方案：将 SaleDate 放入索引键
CREATE INDEX IX_Sales_Product_Date_Revenue 
ON Sales(ProductID, SaleDate, Revenue DESC) 
INCLUDE (Quantity);
-- ProductID 等值 + SaleDate 范围 + Revenue 排序

3. 更新统计信息：

UPDATE STATISTICS Sales;
-- 或指定索引
UPDATE STATISTICS Sales IX_Sales_Product_Date_Revenue;

下一期预告

等待统计（Wait Stats）——从全局视角定位性能瓶颈

  • 什么是等待统计？为什么它比单个查询更能反映系统问题？
  • 解读常见的等待类型：PAGEIOLATCH、LCK、WRITELOG、CXCONSUMER
  • 如何用 DMV 捕获等待统计并定位瓶颈
  • 实战：从等待统计到根因分析

📌 本文代码已在 SQL Server 2019+ 验证。执行计划图标在不同版本 SSMS 中略有差异，但核心逻辑一致。

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

avatar
文章
阅读
粉丝