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

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

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


T-SQL 语句

&#x20;   ↓

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

&#x20;   ↓

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

&#x20;   ↓

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

&#x20;   ↓

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

&#x20;   ↓

【执行】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. 常见执行计划问题与优化

|——|————–|———-|———-|

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 实际行数差异

&#x20;  - 差异大 → 更新统计信息

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

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

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

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

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

8. 检查隐式转换（CONVERT_IMPLICIT）

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

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

第七期小结

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

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