第七期：查询优化器与执行计划 —— 如何读懂与干预

### 第七期：查询优化器与执行计划 —— 如何读懂与干预

#### 1. 查询优化器的工作流程

SQL Server 执行一条查询的完整路径：

T-SQL 语句

    ↓

【解析】语法检查 → 生成解析树

    ↓

【绑定】代数化（Algebrizer）→ 绑定到对象（表、视图、列），生成逻辑树（查询树）

    ↓

【优化】Query Optimizer → 基于代价生成多个候选计划 → 选择代价最低的

    ↓

【生成】执行计划 → 存入计划缓存

    ↓

【执行】Query Executor + Storage Engine → 返回结果

**优化器的核心任务**：在有限时间内找到“足够好”的执行计划（不是绝对最优，否则优化本身成本太高）。

**三个阶段**：

| 阶段 | 策略 | 时间 | 搜索范围 |

|------|------|------|----------|

| 阶段0 | 事务处理（Trivial Plan） | 极快 | 只生成最简单的计划（如行数=1时直接扫描） |

| 阶段1 | 探索阶段（Exploration） | 快 | 搜索部分有希望的变换规则 |

| 阶段2 | 完全优化（Full Optimization） | 慢 | 搜索全部变换规则（并行计划也在此生成） |

**现象解释**：

- 为什么简单查询几乎瞬间返回执行计划？因为命中了阶段0，直接生成Trivial Plan。

- 为什么复杂查询第一次编译时间长？因为优化器需要搜索多个候选计划（阶段2）。

#### 2. 执行计划基础：读懂图形化计划

**三种获取执行计划的方式**：

| 方式 | 命令 | 特点 |

|------|------|------|

| 估计执行计划 | SET SHOWPLAN\_XML ON | 不执行查询，基于统计信息估算 |

| 实际执行计划 | SET STATISTICS XML ON | 执行查询，包含实际行数、运行时信息 |

| 实时执行计划 | sp\_WhoIsActive 或 DMV | 查看正在执行的计划的实时进度（2016+） |

**核心运算符（从数据流向角度理解）**：

| 运算符 | 作用 | 图标特征 |

|--------|------|----------|

| **Clustered Index Seek** | 在聚集索引中精确查找 | 黄色箭头+放大镜 |

| **Clustered Index Scan** | 扫描整个聚集索引（表扫描） | 黄色箭头+表格 |

| **Index Seek** | 在非聚集索引中精确查找 | 绿色箭头+放大镜 |

| **Index Scan** | 扫描整个非聚集索引 | 绿色箭头+表格 |

| **Key Lookup（Bookmark Lookup）** | 从非聚集索引回到聚集索引/堆取行 | 小书签图标 |

| **Nested Loops** | 循环嵌套连接（适用于小表驱动大表） | 两个圆套在一起 |

| **Hash Match** | 哈希连接（适用于大表无索引） | 两个圆相交 |

| **Merge Join** | 合并连接（适用于两个有序输入） | 齿轮状 |

| **Sort** | 显式排序 | 带A-Z的箭头 |

| **Filter** | 筛选行 | 漏斗 |

| **Compute Scalar** | 计算标量表达式 | 计算器 |

**读取顺序**：**从右到左、从下到上**（执行顺序：子节点先执行，结果返回父节点）。

**关键属性**：

- **估计行数**：优化器根据统计信息预估的行数

- **实际行数**：实际执行返回的行数（实际计划中才有）

- **估计I/O/CPU代价**：相对单位，用于比较计划

- **估计子树代价**：该节点及以下的总代价（优化器选计划的核心依据）

**现象解释**：

- 为什么估计行数和实际行数相差很大？统计信息过期或采样不准，优化器基于错误数据选了错误计划。

#### 3. 统计信息：优化器的“眼睛”

**统计信息的内容**：

- 密度向量（Density）：列上重复值的分布（=1/唯一值数）

- 直方图（Histogram）：最多200步，记录列值范围和行数分布

- 字符串摘要（String Summary）：用于LIKE查询的估算

**统计信息自动维护**：

- 自动创建（Auto Create）：当查询引用无统计信息的列时

- 自动更新（Auto Update）：当行数变化超过阈值时

- 行数 < 500：变化超过500行触发

- 行数 > 500：变化超过 500 + 20% 行数触发

**手动管理统计信息**：

\-- 查看统计信息

DBCC SHOW\_STATISTICS ('Orders', 'IX\_Orders\_OrderDate');



\-- 更新统计信息（全表扫描）

UPDATE STATISTICS Orders IX\_Orders\_OrderDate WITH FULLSCAN;



\-- 更新表上所有统计信息

UPDATE STATISTICS Orders WITH FULLSCAN;



\-- 更新数据库所有统计信息

EXEC sp\_updatestats;

**统计信息问题现象**：

| 现象 | 统计信息问题 |

|------|--------------|

| 实际行数远小于估计行数 | 统计信息过高估计 |

| 实际行数远大于估计行数 | 统计信息过低估计 |

| 参数嗅探导致错误计划 | 统计信息准确但优化器基于特定参数值做了错误决策 |

| 查询突然变慢，重建统计后恢复 | 统计信息过期 |

#### 4. 计划缓存与重用

**计划缓存位置**：内存中（属于缓冲池之外的计划缓存区域）

**计划缓存的优势**：

- 节省编译时间（复杂查询可节省70%~90%编译开销）

- 减少CPU使用

**计划不能被重用的情况**：

- 查询未被参数化（Ad-hoc查询使用文字常量）

- 参数值差异大导致嗅探问题（强制重新编译）

- 表结构变化（索引增删）

- 统计信息更新

- OPTION (RECOMPILE) 显式指定

**管理计划缓存**：

\-- 查看缓存计划

SELECT usecounts, cacheobjtype, objtype, size\_in\_bytes, sql\_handle, plan\_handle

FROM sys.dm\_exec\_cached\_plans;



\-- 清除所有缓存计划（谨慎）

DBCC FREEPROCCACHE;



\-- 清除特定计划

DBCC FREEPROCCACHE (plan\_handle);



\-- 清除特定数据库的计划

DBCC FLUSHPROCINDB (db\_id);

**参数嗅探（Parameter Sniffing）**：

- **定义**：优化器在第一次编译时使用传入的参数值生成计划，后续复用时即使参数不同仍用同一计划。

- **示例**：

\-- 第一次执行：@Status = 'Active'（有10000行，优化器选表扫描）

\-- 第二次执行：@Status = 'Archived'（只有10行，但沿用扫描计划 → 慢！）

CREATE PROC GetOrders @Status VARCHAR(20) AS

SELECT \* FROM Orders WHERE Status = @Status;

- **解决**：OPTION (RECOMPILE)、OPTION (OPTIMIZE FOR UNKNOWN)、使用本地变量或OPTION (OPTIMIZE FOR (@Status='Active'))。

#### 5. 常见执行计划问题与优化

| 问题 | 执行计划特征 | 根本原因 | 解决方案 |

|------|--------------|----------|----------|

| 全表扫描（死）| Clustered Index Scan 代价极高 | 无合适索引，或查询不SARG | 添加索引，重写查询 |

| 书签查找过多 | Key Lookup 占总代价80%+ | 非聚集索引未覆盖查询列 | 添加INCLUDE列，或使用覆盖索引 |

| 隐士转换 | Scan + CONVERT_IMPLICIT | 参数类型与列类型不匹配 | 统一参数类型（如传入'123'而非123） |

| 排序代价高 | Sort 占大量代价 | 无索引支持ORDER BY/GROUP BY | 在排序列上建索引 |

| 连接顺序错 | Hash Join 驱动表选错 | 统计信息不准 | 更新统计信息，或使用OPTION(FORCE ORDER) |

| 并行计划过多 | 多个并行运算符 | 查询复杂但数据量小 | 降低MAXDOP，或加OPTION(MAXDOP 1) |

**SARGable查询原则**（Search ARGument able）：

\-- 坏（非SARG）：函数包裹列

WHERE YEAR(OrderDate) = 2024



\-- 好（SARGable）：列单独出现

WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'



\-- 坏（非SARG）：列在表达式中

WHERE Price \* 1.1 > 100



\-- 好（SARGable）

WHERE Price > 100 / 1.1

#### 6. 干预优化器的方法

| 方法 | 语法 | 适用场景 |

|------|------|----------|

| 查询提示 | WITH (INDEX=IX\_Name) | 强制使用特定索引 |

| 连接提示 | OPTION (HASH JOIN, MERGE JOIN) | 强制连接算法 |

| 强制顺序 | OPTION (FORCE ORDER) | 按FROM顺序执行连接 |

| 重编译 | OPTION (RECOMPILE) | 防止参数嗅探，或每次参数差异大 |

| 优化未知 | OPTION (OPTIMIZE FOR UNKNOWN) | 参数嗅探，使用平均值估算 |

| 指定值 | OPTION (OPTIMIZE FOR (@Status='Active')) | 让优化器基于特定值优化 |

| MAXDOP | OPTION (MAXDOP 2) | 限制并行度 |

| 表提示 | WITH (NOLOCK, READPAST) | 控制锁行为 |

**提示使用原则**：

- 先验证问题（确认优化器选错了计划）

- 最小化干预（优先改查询/索引/统计信息）

- 测试所有场景（防止一个查询改好，其他查询变差）

- 代码注释原因（为什么强制这个提示）

#### 7. 执行计划分析实战步骤

1\. 获取实际执行计划（SET STATISTICS XML ON）

2\. 找出代价最高的运算符（通常占80%+代价）

3\. 检查估计行数 vs 实际行数差异

   - 差异大 → 更新统计信息

   - 差异小但计划仍差 → 索引或查询结构问题

4\. 检查是否有表扫描/索引扫描（如果是大表且非必要）

5\. 检查是否有大量书签查找（考虑覆盖索引）

6\. 检查连接算法（循环嵌套适用于小表驱动大表）

7\. 检查排序/Sort开销（考虑索引支持）

8\. 检查隐式转换（CONVERT\_IMPLICIT）

9\. 设计改进方案（索引/查询/统计信息）

10\. 验证改进效果（对比代价和解锁时间）

---

**第七期小结**

查询优化器基于代价在有限时间内选择“足够好”的执行计划，其决策质量高度依赖统计信息的准确性。读懂执行计划要学会从右到左、从下到上，找出高代价运算符，并判断根因（索引缺失/统计信息过期/参数嗅探）。干预优化器应遵循“先优化结构，后使用提示”的原则。核心目标：消除扫描、减少书签查找、确保连接顺序合理。

**下期预告**：存储引擎深度（四）—— 事务与并发控制的内部实现，深入理解行版本、日志序列号（LSN）和恢复过程。