罗罗编程学习记录
本页内容 展开查看

05.4.2.1 查询处理与执行计划

所属:全栈开发面试题库 / 05 数据库与存储 / 05.4 SQL Server / 05.4.2 T-SQL 与执行计划

覆盖范围

  • T-SQL 定位与一条 SELECT 的处理流程
  • 预估计划与实际计划、图形/XML/文本计划
  • 常见算子:Scan/Seek、三种 JOIN、聚合、排序、键查找、并行
  • 估算行数与实际行数、STATISTICS IO/TIME、谓词与成本
  • 隐式转换、计划缓存、重编译、Hint、MAXDOP
  • 与 MySQL EXPLAIN、PG EXPLAIN ANALYZE 对照

高频题速览

编号 题目 难度 频率
05.4.2.1-002 一条 SELECT 的处理流程 进阶 ★★★★★
05.4.2.1-003 执行计划形态与查看方式 进阶 ★★★★★
05.4.2.1-005 常见访问与 JOIN 算子 高级 ★★★★★
05.4.2.1-007 键查找出现的原因与消除 进阶 ★★★★☆
05.4.2.1-010 STATISTICS IO / TIME 解读 进阶 ★★★★★
05.4.2.1-013 扫描类型判断 进阶 ★★★★☆
05.4.2.1-017 慢查询排查完整流程 高级 ★★★★★
05.4.2.1-018 查询 Hint 的使用与风险 高级 ★★★★☆

查询处理链路

SQL 文本
  → 解析 Parse(词法/语法检查,生成解析树)
  → 绑定 Bind(对象/列/类型解析,权限校验)
  → 优化 Optimize(基于统计信息选路径,生成候选计划,取成本最低)
  → 执行 Execute(按计划算子树运行,返回结果)
执行计划:算子树(Scan/Seek → Join → Aggregate/Sort → 输出)

05.4.2.1-001 T-SQL 是什么?与标准 SQL、其他方言什么关系?

频率 ★★☆☆☆ | 难度 基础

一句话结论

T-SQL(Transact-SQL)是 SQL Server 的过程化 SQL 方言,在标准 SQL 基础上扩展了变量、流程控制、存储过程、函数与错误处理。

概念解释

  • 标准 SQL:声明式查询语法;T-SQL 在此之上增加过程能力。
  • 方言差异:MySQL 用其自有函数与语法,PL/pgSQL 是 PG 过程方言,Oracle 为 PL/SQL。
  • 迁移跨库时函数、分页、日期处理、错误处理最易出差异。

原理流程

  • T-SQL 批处理由一批语句组成,GO 是客户端批处理分隔符,不是 T-SQL 语句。

代码 / 命令

DECLARE @Msg NVARCHAR(50) = N'Hello';
IF @Msg IS NOT NULL
    PRINT @Msg;
GO  -- 批处理结束标记

常见误区

  • 误区:GO 是 T-SQL 执行语句。正解:它是 SSMS/sqlcmd 的批处理分隔符,发送给引擎前被拆分。
  • 误区:标准 SQL 写法在各库行为完全一致。正解:函数类型、分页、日期与隐式转换各方言不同。

追问

  • 问:T-SQL 支持存储过程与触发器吗?答:支持,是过程化扩展的一部分。
  • 问:迁移 Oracle PL/SQL 到 T-SQL 主要改什么?答:变量声明、异常块、分页、字符串/日期函数与游标写法。

05.4.2.1-002 一条 SELECT 在 SQL Server 内部经历哪些步骤?

频率 ★★★★★ | 难度 进阶

一句话结论

依次经过解析(语法)、绑定(对象与类型)、优化(生成并选择计划)、执行(按算子树运行)四步。

概念解释

  • 解析:词法/语法检查,生成解析树;语法错误在此报错。
  • 绑定(规范化/名称解析):解析表名、列名、类型,校验权限。
  • 优化:依据统计信息估算成本,选择访问路径与 JOIN 顺序,输出执行计划。
  • 执行:执行器按计划调用存储引擎取数、计算并返回。

原理流程

文本 → 解析树 → 绑定后的查询树 → 优化器候选计划 → 选定计划 → 执行流

代码 / 命令

-- 开启实际计划后执行,查看算子树
SET STATISTICS XML ON;
SELECT Id, Amount FROM dbo.Orders WHERE Status = 1;
SET STATISTICS XML OFF;

常见误区

  • 误区:优化器总是找到最优计划。正解:它在有限候选中按成本模型选较优,统计不准时会偏差。
  • 误区:绑定阶段就决定索引。正解:索引选择发生在优化阶段。

追问

  • 问:权限校验在哪一步?答:绑定/编译阶段校验对象与列权限。
  • 问:语句重编译跳过后哪几步?答:重编译重新走优化与执行;解析/绑定程度视触发原因而定。

05.4.2.1-003 执行计划有哪些查看形态?

频率 ★★★★★ | 难度 进阶

一句话结论

执行计划分图形计划(SSMS 图形界面)、XML 计划(可保存对比)与文本计划(SHOWPLAN_TEXT/ALL);可在 SSMS 中开启"包括实际执行计划"。

概念解释

  • 图形计划:算子以图标和连线展示,标注成本与行数。
  • XML 计划:结构化 XML,可存档、diff、纳入 Query Store 分析。
  • 文本计划:缩进文本表示算子树,适合无图形界面环境。

原理流程

预估计划:编译阶段生成,未实际执行
实际计划:执行后补充实际行数、实际重估偏差与运行告警

代码 / 命令

-- SSMS:Ctrl+M 开启实际执行计划
SET SHOWPLAN_TEXT ON;   -- 只输出文本计划,不执行
SELECT * FROM dbo.Orders WHERE Id = 1;
SET SHOWPLAN_TEXT OFF;

SET STATISTICS XML ON;  -- 执行并返回 XML 实际计划

常见误区

  • 误区:预估计划能看到实际行数偏差。正解:预估计划只有估算值;实际计划才有 Actual Rows。
  • 误区:XML 计划只能在 SSMS 看。正解:XML 可导出存档,用文本 diff 或工具对比两次计划。

追问

  • 问:SHOWPLAN_TEXT ON 会执行语句吗?答:不执行,只生成预估计划。
  • 问:计划里算子从上到下怎么读?答:按数据流方向,通常从最右侧叶子算子向上逐层输出。

05.4.2.1-004 预估计划和实际计划有什么区别?

频率 ★★★★☆ | 难度 进阶

一句话结论

预估计划基于编译时统计信息估算,不运行语句;实际计划在执行后回填真实行数、耗时与告警,能暴露估算偏差与溢出。

概念解释

  • 预估行数(Estimate Rows):优化器按统计信息推算。
  • 实际行数(Actual Rows):运行时真实输出行数。
  • 偏差:两者差距大,常指向统计过期、参数嗅探或数据倾斜。

原理流程

编译 → 预估计划(估算)
执行 → 实际计划(实际行数 + 运行告警,如 spill、行计数偏差)

代码 / 命令

-- 实际计划中核对 Estimate Rows vs Actual Rows
SET STATISTICS XML ON;
SELECT CustomerId, COUNT(*) FROM dbo.Orders GROUP BY CustomerId;

常见误区

  • 误区:预估计划走了索引就说明执行快。正解:需实际计划与 IO/时间统计确认。
  • 误区:行数偏差不影响计划选择。正解:大偏差可能导致 JOIN 顺序与算法选错。

追问

  • 问:实际计划在哪里看 spill 告警?答:Sort/Hash Match 算子的溢出属性与计划警告。
  • 问:怎么保存实际计划?答:STATISTICS XML 结果另存 .sqlplan 或导出 XML。

05.4.2.1-005 常见访问算子和 JOIN 算子有哪些?

频率 ★★★★★ | 难度 高级

一句话结论

访问算子分 Scan 与 Seek;JOIN 算子有嵌套循环、哈希连接、合并连接,优化器按行数与索引选择。

概念解释

算子 含义 适用
Index Seek 索引定位键范围 小范围等值/范围查询
Index Scan 遍历索引页 覆盖索引扫描、小表
Clustered Index Scan 遍历聚集索引 全表扫描类
Table Scan 堆表全扫 堆表全量访问
Nested Loops 外层逐行找内层 外层结果集小、内层有索引
Hash Join 构建哈希表探测 大表连接、无合适索引
Merge Join 两输入有序合并 两输入都按键有序

原理流程

小结果集 + 内层索引 → Nested Loops
大结果集 + 无序 → Hash Join
两输入按键有序 → Merge Join

代码 / 命令

-- 计划中观察 JOIN 算子类型与两侧行数
SELECT o.Id, c.Name
FROM dbo.Orders o JOIN dbo.Customers c ON o.CustomerId = c.Id
WHERE o.Status = 1;

常见误区

  • 误区:Nested Loops 一定最快。正解:外层行数大时逐行回表成本高,Hash Join 可能更优。
  • 误区:Hash Join 说明缺索引就该骂。正解:大表分析型连接用哈希连接合理。

追问

  • 问:Merge Join 要求什么前提?答:两侧输入按连接键有序(走索引或排序)。
  • 问:怎么强制 JOIN 算法?答:LOOP/HASH/MERGE 提示,仅在确认计划问题时短期使用。

05.4.2.1-006 聚合与排序算子长什么样?

频率 ★★★☆☆ | 难度 进阶

一句话结论

聚合分 Hash Aggregate(哈希分组)与 Stream Aggregate(有序流式分组);排序由 Sort 算子完成,数据量大时可能 spill 到 tempdb。

概念解释

  • Stream Aggregate:输入已按分组键有序,逐行流式聚合。
  • Hash Aggregate:构建哈希表分组,适合未排序的大结果集。
  • Sort:按表达式排序;内存不足时落盘 spill。

原理流程

GROUP BY 键有序 → Stream Aggregate
GROUP BY 无序 → Hash Aggregate
ORDER BY 无索引序 → Sort(可能 spill)

代码 / 命令

SELECT CustomerId, COUNT(*) AS cnt, SUM(Amount) AS total
FROM dbo.Orders
GROUP BY CustomerId
ORDER BY total DESC;

常见误区

  • 误区:出现 Sort 就一定要加索引消除。正解:小结果集排序成本可接受;大结果集才需评估索引或改写。
  • 误区:Hash Aggregate 比 Stream Aggregate 慢。正解:各有适用数据分布,不能一概而论。

追问

  • 问:怎么让 GROUP BY 走 Stream Aggregate?答:分组键有对应索引序,避免临时排序/哈希。
  • 问:Sort spill 怎么识别?答:实际计划算子上的溢出明细与 tempdb 读写。

05.4.2.1-007 计划中的 Key Lookup 为什么出现?怎么消除?

频率 ★★★★☆ | 难度 进阶

一句话结论

非聚集索引未覆盖查询列时,计划用 Key Lookup 按聚集键回表取整行;命中行数多时成本高,可用包含列建成覆盖索引消除。

概念解释

  • 与 05.4.1.2-005 同一机制:计划算子层面表现为 Index Seek + Key Lookup 嵌套循环。
  • 消除方式:把返回列纳入 INCLUDE,形成覆盖索引;或改写只查索引列。

原理流程

Index Seek(非聚集)→ 每行 Key Lookup → 取整行
覆盖索引:Seek 直接返回所需列,无 Lookup

代码 / 命令

-- 计划出现 Key Lookup 的查询
SELECT Id, Status, Amount, CreateTime
FROM dbo.Orders WHERE Status = 1;

-- 覆盖化改造
CREATE NONCLUSTERED INDEX IX_Status_Incl
ON dbo.Orders (Status) INCLUDE (Amount, CreateTime);

常见误区

  • 误区:Lookup 算子颜色/图标直接说明好坏。正解:看命中行数与回表成本,少量回表可接受。
  • 误区:覆盖索引加得越多越好。正解:写放大与索引体积需权衡。

追问

  • 问:堆表上对应算子叫什么?答:RID Lookup。
  • 问:覆盖后计划还会回表吗?答:所需列都在索引中时不再回表。

05.4.2.1-008 什么是并行计划?MAXDOP 怎么控制?

频率 ★★★☆☆ | 难度 高级

一句话结论

大查询可由多个线程并行执行,计划出现 Parallelism(Gather/Repartition)算子;并行度由服务器、数据库、查询级 MAXDOP 控制。

概念解释

  • 并行计划:片段(分区)分派给多个线程,结果在 Gather 算子汇总。
  • 并行阈值:查询成本超过服务器配置的并行度阈值才考虑并行。
  • MAXDOP:限制单查询最大并行线程数。

原理流程

成本 > 并行阈值 → 生成并行计划 → 多线程分片执行 → Gather 汇总

代码 / 命令

-- 查询级限制并行度
SELECT * FROM dbo.BigTable OPTION (MAXDOP 4);

-- 查看服务器级 MAXDOP 与并行阈值
SELECT  value_in_use FROM sys.configurations
WHERE name IN ('max degree of parallelism', 'cost threshold for parallelism');

常见误区

  • 误区:并行一定比串行快。正解:并行有调度与汇总开销,小查询并行反而更慢。
  • 误区:MAXDOP 设得越大越好。正解:高并发下过多并行线程会争抢 CPU,需按核数与负载调优。

追问

  • 问:Parallelism 算子的输入/输出交换是什么?答:Repartition 按行分发,Gather 汇总多线程结果。
  • 问:OLTP 高并发库适合高 MAXDOP 吗?答:通常限制较小并行度,避免线程暴涨。

05.4.2.1-009 怎么看估算行数与实际行数的偏差?

频率 ★★★★☆ | 难度 高级

一句话结论

实际计划对比算子的 Estimate Rows 与 Actual Rows,数量级偏差说明统计信息过期、参数嗅探或数据倾斜,会影响 JOIN 算法与内存授予。

概念解释

  • 估算偏差:优化器按直方图/密度推算,与真实分布不符。
  • 影响:选错 JOIN 算法、内存授予不足 spill、回表评估偏低。

原理流程

统计过期/倾斜 → 估算行数偏低 → 选 Nested Loops → 实际大量回表变慢

代码 / 命令

-- 实际计划中逐算子核对行数;先更新统计再对比
UPDATE STATISTICS dbo.Orders;

常见误区

  • 误区:偏差只影响显示,不影响性能。正解:成本模型基于估算,偏差会传导到计划选择。
  • 误区:所有偏差都靠 UPDATE STATISTICS 解决。正解:倾斜与参数嗅探需要单独处理(见 05.4.2.2)。

追问

  • 问:偏差多少算明显?答:数量级差距(如估 10 行实际 10 万行)即需关注。
  • 问:统计更新后偏差消失说明什么?答:根因是统计过期;仍偏差则检查倾斜与参数。

05.4.2.1-010 SET STATISTICS IO 和 STATISTICS TIME 输出怎么读?

频率 ★★★★★ | 难度 进阶

一句话结论

IO 统计看逻辑读/物理读/预读页数,时间统计看 CPU 与耗时;逻辑读高说明扫描/回表多,物理读高说明缓存未命中。

概念解释

  • 逻辑读:从 Buffer Pool 读取的页数;主要反映扫描量。
  • 物理读:从磁盘读取的页数;缓存未命中。
  • CPU 时间 / 耗时:CPU 计算与总等待时间(含等待)。

原理流程

优化前:逻辑读数十万 → 覆盖索引/索引调整后逻辑读显著下降

代码 / 命令

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT CustomerId, Amount FROM dbo.Orders WHERE Status = 1;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

常见误区

  • 误区:逻辑读高就一定是缺索引。正解:也可能是宽行、回表或结果集本身大,需结合计划判断。
  • 误区:物理读为 0 就说明计划最优。正解:缓存命中只说明数据在内存,扫描量仍可能过大。

追问

  • 问:READ_AHEAD 预读页数说明什么?答:顺序扫描时预取,辅助判断扫描范围。
  • 问:耗时远大于 CPU 时间说明什么?答:时间花在等待(IO/锁/内存授予),需查等待类型。

05.4.2.1-011 计划里的成本、子树成本是什么意思?

频率 ★★☆☆☆ | 难度 高级

一句话结论

成本是优化器按 CPU/IO/内存开销估算的相对单位;算子成本为自身开销,子树成本含自身与所有下游算子。

概念解释

  • 算子成本:该步骤估算开销。
  • 子树成本(Subtree Cost):该算子及其下游全部成本之和,计划顶端即整查询成本。
  • 成本是相对估算,不直接等于秒数。

原理流程

叶子算子成本 → 向上累加 → 顶端子树成本 = 整查询估算总成本

代码 / 命令

-- 图形计划悬停算子查看 Cost 与 Subtree Cost;XML 计划含 Cost 字段
SET STATISTICS XML ON; SELECT TOP (100) * FROM dbo.Orders;

常见误区

  • 误区:成本数字可以直接对比不同服务器的真实耗时。正解:成本是模型相对值,硬件与配置不同不可直接横比。
  • 误区:子树成本低的算子就是瓶颈。正解:瓶颈要看实际耗时、行数与等待。

追问

  • 问:优化器按什么选计划?答:选子树总成本最低的候选计划。
  • 问:成本模型估算不准的根源是什么?答:统计信息与硬件假设与实际偏差。

05.4.2.1-012 Seek Predicate 和 Predicate 有什么区别?

频率 ★★★☆☆ | 难度 进阶

一句话结论

Seek Predicate 是用于在索引上定位/范围扫描的条件;Predicate 是索引定位后对返回行再过滤的条件。

概念解释

  • Seek Predicate:参与索引键定位,缩小扫描区间。
  • Predicate:无法用于定位,在已扫描行上过滤。
  • 键列函数包裹、隐式转换常使条件退化为 Predicate。

原理流程

Seek: Status = 1(键定位)
Predicate: Amount > 100(索引找到后逐行过滤)

代码 / 命令

-- 索引 (Status);Amount 条件为后过滤
SELECT * FROM dbo.Orders WHERE Status = 1 AND Amount > 100;

常见误区

  • 误区:出现在计划 Predicate 里的条件都走了索引。正解:只有 Seek Predicate 参与定位。
  • 误区:Predicate 过滤代价可以忽略。正解:扫描大量行后逐行过滤,成本可能很高。

追问

  • 问:怎么把 Predicate 条件纳入定位?答:设计联合索引让该列成为键列(满足最左前缀)。
  • 问:范围条件后面的列属于哪类?答:范围后无序,通常只能作为 Predicate 过滤。

05.4.2.1-013 怎么区分 Table Scan、Clustered Index Scan、Index Scan、Index Seek?

频率 ★★★★☆ | 难度 进阶

一句话结论

按对象类型与访问方式判断:堆全扫是 Table Scan;聚集索引全扫是 Clustered Index Scan;非聚集索引遍历是 Index Scan;按键定位是 Index Seek。

概念解释

算子 对象 访问方式
Table Scan 堆 全表遍历
Clustered Index Scan 聚集索引 遍历聚集索引
Index Scan 非聚集索引 遍历非聚集索引
Index Seek 非聚集/聚集索引 键定位 + 范围扫描

原理流程

  • Scan 通常意味大范围扫描;Seek 表示索引定位。Scan 配合覆盖索引不一定差。

代码 / 命令

-- 计划中观察最左侧叶子算子类型
SELECT Status, Amount FROM dbo.Orders WHERE Status = 1;

常见误区

  • 误区:Scan 一定是坏计划。正解:小表全扫或覆盖索引扫描可能比 Seek 回表更优。
  • 误区:出现 Seek 就一定走了正确索引。正解:还要看 Seek 范围与后续 Lookup 行数。

追问

  • 问:Clustered Index Scan 和 Table Scan 区别?答:前者有聚集索引按序遍历,后者堆表无序。
  • 问:Index Scan 能避免回表吗?答:查询列被索引覆盖时可以。

05.4.2.1-014 隐式转换为什么会导致索引失效?

频率 ★★★★☆ | 难度 进阶

一句话结论

条件列被隐式类型转换包裹后,索引存的是原值,无法按键定位;应让参数类型与列类型一致。

概念解释

  • 隐式转换:比较两侧类型不同时,引擎按类型优先级转换一侧。
  • 若转换发生在列上,索引键值被函数化,Seek 退化为 Scan。
  • 常见于字符串列与数字比较、排序规则不一致。

原理流程

列 phone(varchar) = 13800000000(数字)
  → 列被转为数字 → 索引原值无法定位 → 扫描

代码 / 命令

-- 反例:字符串列传数字参数
SELECT * FROM dbo.Users WHERE Phone = 13800000000;

-- 正例:类型一致
SELECT * FROM dbo.Users WHERE Phone = N'13800000000';

常见误区

  • 误区:参数类型不匹配只是多一次转换,不影响索引。正解:列侧转换直接破坏 Seek。
  • 误区:所有转换都在参数侧完成。正解:类型优先级决定转换哪一侧,需查计划的转换表达式确认。

追问

  • 问:怎么确认转换发生在哪一侧?答:计划谓词中看 CONVERT_IMPLICIT 作用于列还是参数。
  • 问:JOIN 两列类型不一致会怎样?答:连接键隐式转换,同样影响索引使用。

05.4.2.1-015 计划缓存里存的是什么?

频率 ★★★☆☆ | 难度 高级

一句话结论

计划缓存(Plan Cache)保存编译后的执行计划与上下文,相同文本/参数化语句可复用计划,避免重复编译。

概念解释

  • 编译计划(Compiled Plan):算子树与成本信息。
  • 缓存上下文(Cached Plan / Plan Handle):关联语句、参数与内存。
  • 即席语句文本不同会产生不同缓存项。

原理流程

语句到达 → 查计划缓存 → 命中则复用 → 未命中则编译后入缓存

代码 / 命令

-- 查看缓存计划与使用次数
SELECT  TOP 20 usecounts, size_in_bytes, t.text
FROM    sys.dm_exec_cached_plans cp
OUTER APPLY sys.dm_exec_sql_text(cp.plan_handle) t
ORDER BY usecounts DESC;

常见误区

  • 误区:缓存计划越多越好。正解:即席查询炸缓存会占用大量内存,需参数化或优化。
  • 误区:计划缓存命中就说明计划最优。正解:复用的可能是早期生成的差计划。

追问

  • 问:怎么清空计划缓存?答:DBCC FREEPROCCACHE(谨慎,生产低峰);可按计划句柄清除。
  • 问:什么导致缓存项失效?答:统计更新、架构变更、内存压力、手动清除。

05.4.2.1-016 语句什么时候会触发重编译?

频率 ★★★☆☆ | 难度 高级

一句话结论

对象结构变化、统计信息更新、数据量显著变化、计划过期或 RECOMPILE 提示都会触发重编译;重编译消耗 CPU 并可能改变计划。

概念解释

  • 重编译触发:DDL 变更、统计阈值更新、schema 变更、延迟编译、显式重编译。
  • 开销:重新走优化流程,高并发下累积 CPU 压力。

原理流程

缓存计划失效/过期 → 下次执行重新编译 → 新计划入缓存

代码 / 命令

-- 语句级重编译提示
SELECT * FROM dbo.Orders OPTION (RECOMPILE);

-- 查看重编译相关计数
SELECT  counter_name, cntr_value
FROM    sys.dm_os_performance_counters
WHERE   counter_name LIKE '%Recompil%';

常见误区

  • 误区:每次执行存储过程都重新编译。正解:计划缓存复用,仅失效时重编译。
  • 误区:重编译一定产生更好计划。正解:新计划受当前统计与参数影响,可能更好也可能更差。

追问

  • 问:临时表数据变化会触发重编译吗?答:特定语句引用临时表且行数变化达阈值时可能触发。
  • 问:怎么定位频繁重编译?答:看重编译计数与存储过程/语句执行次数对比。

05.4.2.1-017 一条慢 SQL 的完整排查流程是什么?

频率 ★★★★★ | 难度 高级

一句话结论

抓慢语句 → 看实际计划与 IO/时间 → 定位扫描/回表/排序/spill/等待 → 核对统计与索引 → 改写或加索引 → 复测验证。

概念解释

  • 入口:慢查询统计、扩展事件、Query Store、阻塞会话。
  • 分析:实际计划 + STATISTICS IO/TIME + 等待类型。
  • 处置:索引、改写、统计更新、计划干预;复测对比逻辑读与耗时。

原理流程

抓语句 → 实际计划 → 找高成本算子 → 查行数偏差/等待/spill
  → 索引/改写/统计 → 复测 IO 与耗时 → 观察线上趋势

代码 / 命令

-- Query Store 取高耗时查询(启用后)
SELECT  TOP 20 qt.query_sql_text, rs.total_duration / 1000000 AS total_sec,
        rs.count_executions, rs.avg_duration / 1000 AS avg_ms
FROM    sys.query_store_query_text qt
JOIN    sys.query_store_query q ON q.query_text_id = qt.query_text_id
JOIN    sys.query_store_runtime_stats rs ON rs.query_id = q.query_id
ORDER BY rs.total_duration DESC;

常见误区

  • 误区:慢在数据库就只看 SQL。正解:先排除锁等待、CPU/内存压力与参数嗅探。
  • 误区:加索引后不复测就算完成。正解:必须对比优化前后 IO、耗时与计划,确认未引入新问题。

追问

  • 问:慢查询是全表扫描还是回表过多怎么分?答:计划算子类型 + 逻辑读分布 + Lookup 行数。
  • 问:怎么区分 IO 瓶颈与 CPU 瓶颈?答:STATISTICS TIME 的 CPU 时间与耗时差、等待类型。

05.4.2.1-018 常用查询 Hint 有哪些?风险是什么?

频率 ★★★★☆ | 难度 高级

一句话结论

Hint 包括索引提示、JOIN 算法提示、RECOMPILE、MAXDOP、OPTIMIZE FOR 等;用于短期纠正错误计划,但绑定计划、数据分布变化后可能失效,应优先改写 SQL 与调整索引。

概念解释

  • 索引提示:WITH (INDEX(...))、FORCESEEK。
  • JOIN 提示:LOOP/HASH/MERGE JOIN。
  • 计划提示:OPTION (RECOMPILE)、OPTIMIZE FOR、MAXDOP。

原理流程

优化器计划错误 → 短期 Hint 锁定路径 → 长期应修正统计/索引/语句

代码 / 命令

-- 指定索引与连接算法(示例,需结合实际计划)
SELECT o.Id, c.Name
FROM dbo.Orders o WITH (INDEX (IX_Orders_Status))
JOIN dbo.Customers c ON o.CustomerId = c.Id
OPTION (HASH JOIN, MAXDOP 4);

常见误区

  • 误区:Hint 是性能优化的首选手段。正解:它绕过优化器判断,数据增长后容易变成更差计划。
  • 误区:FORCESEEK 对所有查询都安全。正解:选错方向时会强制低效路径。

追问

  • 问:Hint 和计划指南/Query Store 强制计划区别?答:Hint 写在语句内;计划指南/QS 在库侧固定计划,不改语句文本。
  • 问:为什么不建议长期依赖 INDEX 提示?答:索引重建/更名后提示可能失效,且绑定旧路径。

05.4.2.1-019 并行度在服务器、数据库、查询级分别怎么配?

频率 ★★☆☆☆ | 难度 进阶

一句话结论

MAXDOP 可在服务器级、数据库级、查询级逐层覆盖;并行阈值决定多大成本的查询才进入并行候选。

概念解释

  • 服务器级:实例默认最大并行度。
  • 数据库级:覆盖服务器默认(按库设置)。
  • 查询级:OPTION (MAXDOP n) 单次覆盖。

原理流程

查询级 > 数据库级 > 服务器级(就近覆盖)

代码 / 命令

-- 服务器级
EXEC sys.sp_configure 'max degree of parallelism', 4;
RECONFIGURE;

-- 数据库级
ALTER DATABASE DemoDB SET MAXDOP = 4;

-- 查询级
SELECT * FROM dbo.BigTable OPTION (MAXDOP 2);

常见误区

  • 误区:MAXDOP=1 一定避免并行问题。正解:禁用并行后大查询串行,CPU 耗时可能上升。
  • 误区:数据库级设置对所有语句无条件生效。正解:查询级提示可再覆盖。

追问

  • 问:并行度按核数怎么估算?答:结合 NUMA、核数与并发负载,按微软建议与压测确定,不照搬数值。
  • 问:并行阈值默认多少?答:服务器配置项 cost threshold for parallelism,默认值需在实例上核对。

05.4.2.1-020 执行计划如何保存、冻结与对比?

频率 ★★☆☆☆ | 难度 进阶

一句话结论

实际计划可导出 XML/.sqlplan 存档对比;计划稳定性可用计划指南或 Query Store 强制计划固定。

概念解释

  • 存档对比:优化前后各取 XML,对比算子、行数与成本。
  • 强制计划:Query Store 中把好计划固定,防止计划回归。
  • 计划指南:对指定语句绑定计划/提示(旧方式,QS 优先)。

原理流程

采集计划 → 存 XML → 版本/优化前后 diff → 异常回归用 QS 强制计划

代码 / 命令

-- Query Store 强制计划(示例 plan_id 按实际)
EXEC sys.sp_query_store_force_plan @query_id = 123, @plan_id = 456;

常见误区

  • 误区:强制计划一劳永逸。正解:数据分布变化后旧计划可能变差,需定期复查强制项。
  • 误区:XML 计划包含完整业务数据。正解:计划含算子与估算/行数,不含结果集内容。

追问

  • 问:Query Store 适合替代计划指南吗?答:是现代推荐方式,可观测、可强制、可回归分析。
  • 问:怎么对比两份计划差异?答:对比算子序列、Seek/Scan、JOIN 算法与行数偏差。

05.4.2.1-021 SQL Server 执行计划与 MySQL EXPLAIN、PG EXPLAIN ANALYZE 怎么对照?

频率 ★★★★☆ | 难度 高级

一句话结论

三者都展示访问路径与成本:SQL Server 图形/XML 计划 + STATISTICS IO;MySQL EXPLAIN 看 type/key/rows/Extra;PG EXPLAIN (ANALYZE) 输出实际耗时与行数。

概念解释

维度 SQL Server MySQL PostgreSQL
预估计划 显示预估计划 EXPLAIN EXPLAIN
实际计划 实际计划 + XML EXPLAIN ANALYZE(8.0.18+) EXPLAIN ANALYZE
行数偏差 Estimate vs Actual rows 估算 actual rows / actual time
IO 统计 STATISTICS IO EXPLAIN + profiling buffers 输出

原理流程

  • 对照主线:访问路径 → JOIN 算法 → 估算/实际行数 → IO 与等待,指标名不同但分析思路一致。

代码 / 命令

-- SQL Server
SET STATISTICS IO ON;
SELECT * FROM dbo.Orders WHERE Status = 1;

-- MySQL:EXPLAIN SELECT ...
-- PG:EXPLAIN (ANALYZE, BUFFERS) SELECT ...

常见误区

  • 误区:三者计划术语一一对应。正解:算子命名与估算口径不同,需按各自文档解读。
  • 误区:SQL Server 没有实际执行耗时算子。正解:实际计划 + STATISTICS TIME 可拿到运行耗时。

追问

  • 问:PG EXPLAIN ANALYZE 与 SQL Server 实际计划等价吗?答:思路相近,都执行语句并回填实际值;输出格式不同。
  • 问:MySQL Extra 关键字段对应 SQL Server 什么?答:如 Using index 对应覆盖索引,Using filesort 对应 Sort 算子。

参考来源