简介这份PDF资料聚焦SQL Server查询优化中的典型问题执行计划为何会从高效的索引查找Index Seek退化为全表遍历式的索引扫描Index Scan。内容面向数据库开发与运维人员尤其是需要排查慢查询、优化执行计划的中高级从业者。作者结合AdventureWorks2014等实际场景系统梳理了隐式转换、非SARG谓词、选择性低的谓词、统计信息不准确、连接操作、排序分组、索引覆盖不足、索引碎片、并行计划、资源限制及参数嗅探等十余类诱因并给出避免隐式转换的代码规范、显式转换写法以及从执行计划中检索隐式转换SQL的脚本。资源包为单个PDF文件大小约415KB轻量便于随时查阅。目前已有331人学习适合希望深入理解索引查找与索引扫描差异、掌握执行计划分析与索引设计调优思路的读者参考。1. 索引查找变索引扫描一个让查询从毫秒跌到秒级的隐形陷阱你写了一条看似完美的查询WHERE 条件里明明有索引列执行计划里却赫然写着「Index Scan」而不是「Index Seek」。更诡异的是数据量小的时候一切正常上线三个月后查询突然从 20ms 涨到 3 秒。这不是玄学这是 SQL Server 里最经典也最容易被忽视的性能翻车场景之一。索引查找Index Seek意味着引擎精准定位到目标行像图书馆按索书号直接走到书架前索引扫描Index Scan则是从第一页开始逐页翻直到找到所有匹配行。两者在 I/O 开销上的差距在百万级表上可能是几十倍甚至上百倍。这篇文章面向已经会写 T-SQL、能看懂执行计划的开发者和 DBA把「为什么 Seek 会退化成 Scan」这件事拆开揉碎给出可复现的排查步骤、参数配置和避坑清单。读完你至少能做到拿到一条慢查询十分钟内判断它是不是栽在隐式转换或统计信息上并知道怎么改。2. 先搞懂优化器为什么放弃索引查找从谓词到隐式转换的完整链路2.1 索引查找和索引扫描在存储引擎层到底差在哪SQL Server 的索引是一棵 B 树。Index Seek 的代价大致等于树的高度通常 3 到 4 层加上叶子层连续读取的页数Index Scan 的代价则是整棵索引的叶子页总数。假设一张表有 500 万行索引叶子层占 12 万页每页 8KB那么一次全扫描要读将近 1GB 数据。如果 WHERE 条件能命中 0.1% 的行Seek 只需要读 120 页左右。这个差距就是为什么优化器对 Seek 有天然偏好。但优化器不是永远选 Seek。当它估算出「需要返回的行数占总行数比例过高」时会主动选择 Scan因为此时随机 I/O 加书签查找Key Lookup的总代价可能超过顺序扫描。这个阈值通常在 25% 到 30% 之间浮动取决于表宽度、索引包含列和统计信息。所以看到 Scan 不一定是坏事关键要看估算行数和实际行数是否吻合。真正的问题出在「本该 Seek 却变成 Scan」的情况。这通常意味着优化器对谓词的选择性估算出了大偏差或者谓词本身无法被下推到索引查找操作中。下面几节逐一拆解。2.2 隐式转换最常见的 Seek 杀手先看一个我实际遇到过的案例。某业务表Orders上有一个OrderNo列类型是VARCHAR(20)建了非聚集索引。查询这样写-- 注意orderNo 参数在应用层被定义为 NVARCHAR DECLARE orderNo NVARCHAR(20) NSO20240101001; SELECT OrderId, OrderNo, Amount FROM Orders WHERE OrderNo orderNo;执行计划里出现的是 Index Scan而不是预期的 Index Seek。原因在于VARCHAR和NVARCHAR比较时SQL Server 按照数据类型优先级规则将VARCHAR列隐式转换为NVARCHAR。这个转换发生在列上而不是参数上导致索引无法直接用于查找。用SET STATISTICS XML ON或者直接看执行计划的 XML能找到这样的警告Warnings PlanAffectingConvert ConvertIssueSeek Plan ExpressionCONVERT_IMPLICIT(nvarchar(20),[OrderNo],0)/ /WarningsPlanAffectingConvert加上ConvertIssueSeek Plan就是铁证。解决办法有两个方向一是改参数类型让应用层传VARCHAR二是改列类型但这在大表上代价很高。我一般优先改参数因为改动面小、回归风险低。-- 方案一参数类型对齐 DECLARE orderNo VARCHAR(20) SO20240101001; SELECT OrderId, OrderNo, Amount FROM Orders WHERE OrderNo orderNo;提示在存储过程或参数化查询中参数类型必须和列类型完全一致包括长度。VARCHAR(20)和VARCHAR(50)之间不会触发隐式转换但VARCHAR和NVARCHAR之间一定会。2.3 函数包裹列另一个让索引失效的经典操作比隐式转换更隐蔽的是在 WHERE 条件里对索引列使用函数。比如-- 错误写法对列使用函数 SELECT OrderId, OrderNo, Amount FROM Orders WHERE LEFT(OrderNo, 10) SO20240101; -- 错误写法对列做运算 SELECT OrderId, OrderNo, Amount FROM Orders WHERE OrderId 1 10001;这两种写法都会让优化器无法使用索引查找因为 B 树是按列原始值排序的不是按LEFT(OrderNo, 10)或OrderId 1排序的。优化器只能退化成扫描逐行计算函数值再比较。正确做法是把函数或运算移到参数侧-- 正确写法用范围查询替代 LEFT SELECT OrderId, OrderNo, Amount FROM Orders WHERE OrderNo SO20240101 AND OrderNo SO20240102; -- 正确写法把运算移到参数侧 SELECT OrderId, OrderNo, Amount FROM Orders WHERE OrderId 10001 - 1;范围查询能走 Seek 的前提是OrderNo上的索引支持范围扫描这通常没问题。但要注意如果OrderNo的排序规则Collation和查询参数的排序规则不一致也可能导致 Seek 失效。这种情况在跨库查询或临时表关联时偶有发生排查方法和隐式转换类似看执行计划 XML 里有没有PlanAffectingConvert。2.4 统计信息过期优化器估算偏差的根源即使谓词写得完全正确统计信息过期也会让优化器做出错误判断。SQL Server 依赖统计信息里的直方图来估算谓词选择性。如果直方图还是三个月前的数据而这段时间数据分布发生了剧烈变化优化器可能认为「返回 30% 的行」于是选择 Scan而实际只返回 0.5%。排查方法很简单-- 查看统计信息的最后更新时间 SELECT OBJECT_NAME(s.object_id) AS TableName, s.name AS StatName, sp.last_updated, sp.rows, sp.rows_sampled, sp.modification_counter FROM sys.stats s CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp WHERE OBJECT_NAME(s.object_id) Orders;如果last_updated是很久以前或者modification_counter很大表示自上次更新以来修改了很多行就需要手动更新-- 更新单张表的统计信息采样率设为 100% 以获取最准的直方图 UPDATE STATISTICS Orders WITH FULLSCAN; -- 或者只更新特定统计信息 UPDATE STATISTICS Orders IX_Orders_OrderNo WITH FULLSCAN;FULLSCAN在大表上可能很慢但它是唯一能保证直方图完全准确的方式。折中方案是用SAMPLE 50 PERCENT但采样率越低估算偏差风险越大。我一般对核心业务表在业务低峰期做FULLSCAN对日志类表用默认采样。注意SQL Server 2016 之后有「统计信息自动更新阈值」的改进但大表上仍然可能滞后。如果表每天新增超过 20% 的行自动更新根本追不上数据变化速度。3. 动手复现用最小实验环境验证 Seek 退化成 Scan 的四种场景3.1 搭建测试表和索引先建一张模拟订单表插入 100 万行数据制造出足够的数据分布差异-- 建表 CREATE TABLE dbo.OrdersTest ( OrderId INT IDENTITY(1,1) PRIMARY KEY, OrderNo VARCHAR(20) NOT NULL, CustomerId INT NOT NULL, Amount DECIMAL(10,2) NOT NULL, CreatedAt DATETIME2 NOT NULL DEFAULT SYSDATETIME() ); -- 建非聚集索引 CREATE NONCLUSTERED INDEX IX_OrdersTest_OrderNo ON dbo.OrdersTest (OrderNo); CREATE NONCLUSTERED INDEX IX_OrdersTest_CustomerId ON dbo.OrdersTest (CustomerId); -- 插入 100 万行OrderNo 前缀分布不均 WITH Nums AS ( SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_columns a CROSS JOIN sys.all_columns b ) INSERT INTO dbo.OrdersTest (OrderNo, CustomerId, Amount, CreatedAt) SELECT SO RIGHT(00000000 CAST(n % 100000 AS VARCHAR(8)), 8), n % 5000, CAST(RAND(CHECKSUM(NEWID())) * 10000 AS DECIMAL(10,2)), DATEADD(SECOND, -n, SYSDATETIME()) FROM Nums;这段代码用sys.all_columns自交叉生成 100 万行OrderNo只有 10 万个不同值每个值重复 10 次。CustomerId有 5000 个不同值每个值重复 200 次。这种分布能很好地模拟真实业务中「索引列选择性不高」的情况。3.2 场景一隐式转换导致 Scan-- 开启执行计划捕获 SET STATISTICS IO ON; SET STATISTICS TIME ON; -- 场景一NVARCHAR 参数查 VARCHAR 列 DECLARE p1 NVARCHAR(20) NSO00000001; SELECT OrderId, OrderNo, Amount FROM dbo.OrdersTest WHERE OrderNo p1;执行后看消息面板逻辑读会非常高几万页说明走了 Scan。再看执行计划应该有PlanAffectingConvert警告。改成VARCHAR参数后逻辑读会降到个位数。3.3 场景二函数包裹列导致 Scan-- 场景二LEFT 函数包裹索引列 SELECT OrderId, OrderNo, Amount FROM dbo.OrdersTest WHERE LEFT(OrderNo, 10) SO00000001;这条查询的逻辑读同样会很高。改成范围查询后-- 改写为范围查询 SELECT OrderId, OrderNo, Amount FROM dbo.OrdersTest WHERE OrderNo SO00000001 AND OrderNo SO00000002;逻辑读会从几万降到几十。注意范围的上界要用「下一个值」而不是同一个值否则会漏掉SO00000001后面带后缀的行如果有的话。3.4 场景三统计信息过期导致估算偏差先手动把统计信息改成过期状态模拟数据剧烈变化后的情况-- 关闭自动更新统计信息仅测试环境 ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS OFF; -- 插入大量新数据改变数据分布 INSERT INTO dbo.OrdersTest (OrderNo, CustomerId, Amount) SELECT TOP (500000) SO RIGHT(00000000 CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 100000 AS VARCHAR(8)), 8), ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 5000, 100.00 FROM sys.all_columns a CROSS JOIN sys.all_columns b; -- 不更新统计信息直接查询 SELECT OrderId, OrderNo, Amount FROM dbo.OrdersTest WHERE CustomerId 1234;由于统计信息还是基于原来的 100 万行优化器估算返回 200 行实际返回 300 行左右偏差不大。但如果数据分布倾斜严重比如某个 CustomerId 突然占了 50% 的行估算就会严重偏低优化器可能仍然选 Seek但实际执行时因为书签查找太多而变慢。反过来如果估算偏高优化器会选 Scan。更新统计信息后再查UPDATE STATISTICS dbo.OrdersTest WITH FULLSCAN; SELECT OrderId, OrderNo, Amount FROM dbo.OrdersTest WHERE CustomerId 1234;对比两次的执行计划和逻辑读就能看到统计信息对选择 Seek 还是 Scan 的决定性影响。3.5 场景四参数嗅探导致计划复用错误参数嗅探是存储过程中最常见的问题。创建一个存储过程CREATE OR ALTER PROCEDURE dbo.GetOrdersByCustomer CustomerId INT AS BEGIN SELECT OrderId, OrderNo, Amount FROM dbo.OrdersTest WHERE CustomerId CustomerId; END;先传入一个返回大量行的参数让缓存里生成 Scan 计划-- 第一次执行传入选择性差的参数 EXEC dbo.GetOrdersByCustomer CustomerId 1;然后传入一个只返回少量行的参数-- 第二次执行传入选择性好的参数但复用了 Scan 计划 EXEC dbo.GetOrdersByCustomer CustomerId 4999;查看缓存计划SELECT qs.execution_count, qs.total_logical_reads / qs.execution_count AS avg_logical_reads, st.text AS query_text, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE st.text LIKE %GetOrdersByCustomer%;你会看到两次执行复用了同一个计划第二次的逻辑读远高于预期。解决办法有几种用OPTION (RECOMPILE)让每次执行重新编译用OPTIMIZE FOR指定一个代表性参数值或者在 SQL Server 2022 上用OPTION (USE HINT(DISABLE_PARAMETER_SNIFFING))。我一般优先用RECOMPILE因为它在大多数场景下最省心代价是编译开销。4. 避坑与排查索引查找退化场景的五个血泪教训4.1 坑一只看图形执行计划忽略 XML 里的警告现象图形执行计划看起来很正常就是一个简单的 Index Scan没有红色警告。但查询就是慢。原因图形计划默认不显示所有警告信息PlanAffectingConvert这类关键提示藏在 XML 里。解决用SET STATISTICS XML ON或者直接查sys.dm_exec_query_plan的 XML 内容搜索PlanAffectingConvert、Warnings、ConvertIssue这几个关键词。养成看 XML 的习惯图形计划只用来快速定位大算子。4.2 坑二在索引列上使用 LIKE %xxx%现象WHERE OrderNo LIKE %202401%走的是 Scan逻辑读很高。原因前置通配符无法利用 B 树的有序性只能逐行匹配。解决如果业务确实需要模糊搜索考虑全文索引Full-Text Index或者把搜索需求拆成前缀匹配LIKE SO2024%。前缀匹配是可以走 Seek 的因为 B 树能定位到前缀的起始位置。如果必须用前后通配符那就接受 Scan但要把表放在 SSD 上并确保内存足够缓存整个索引。4.3 坑三索引列参与计算但没意识到现象WHERE OrderId * 2 20002走 Scan。原因对列做了乘法运算索引失效。解决把运算移到参数侧写成WHERE OrderId 20002 / 2。注意整数除法可能丢精度必要时用CAST显式转换。这个坑在报表查询里特别常见因为报表经常做各种聚合和换算。4.4 坑四统计信息采样率过低导致直方图失真现象手动更新了统计信息但执行计划还是不对。原因默认采样率在大表上可能只采了几万行直方图不能反映真实分布。解决用UPDATE STATISTICS ... WITH FULLSCAN强制全表采样。如果表太大至少用SAMPLE 50 PERCENT并在业务低峰期执行。更新后清空计划缓存再测试-- 清空特定对象的计划缓存SQL Server 2016 ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;4.5 坑五参数嗅探在 SQL Server 2022 上的新表现现象升级到 SQL Server 2022 后某些存储过程的计划反而变差了。原因SQL Server 2022 引入了「参数敏感计划优化」PSP在某些查询上会自动生成多个计划。但如果查询被标记为不适合 PSP仍然会走旧的参数嗅探逻辑。解决查sys.dm_exec_query_stats里的plan_handle和query_plan_hash看是否有多个计划。如果有说明 PSP 生效了如果没有考虑加OPTION (RECOMPILE)或OPTION (OPTIMIZE FOR UNKNOWN)。OPTIMIZE FOR UNKNOWN会让优化器用平均密度而不是直方图来估算适合参数值分布均匀的场景。5. 进阶技巧用查询存储和扩展事件锁定退化根因5.1 开启查询存储自动捕获计划变化查询存储Query Store是 SQL Server 2016 之后最实用的性能诊断工具。它自动记录每个查询的历史计划、执行次数、逻辑读和持续时间。开启方法ALTER DATABASE YourDatabase SET QUERY_STORE ON; ALTER DATABASE YourDatabase SET QUERY_STORE ( OPERATION_MODE READ_WRITE, CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS 30), DATA_FLUSH_INTERVAL_SECONDS 900, INTERVAL_LENGTH_MINUTES 60, MAX_STORAGE_SIZE_MB 1024, QUERY_CAPTURE_MODE AUTO );开启后用这个查询找出「计划发生回退」的语句SELECT q.query_id, qt.query_sql_text, p.plan_id, p.is_forced_plan, rs.avg_logical_io_reads, rs.avg_duration, rs.count_executions FROM sys.query_store_query q JOIN sys.query_store_query_text qt ON q.query_text_id qt.query_text_id JOIN sys.query_store_plan p ON q.query_id p.query_id JOIN sys.query_store_runtime_stats rs ON p.plan_id rs.plan_id WHERE q.query_id IN ( -- 找出有多个计划的查询 SELECT query_id FROM sys.query_store_plan GROUP BY query_id HAVING COUNT(DISTINCT plan_id) 1 ) ORDER BY rs.avg_logical_io_reads DESC;如果发现某个查询有两个计划一个逻辑读很低Seek一个很高Scan可以用sp_query_store_force_plan强制使用好的计划EXEC sp_query_store_force_plan query_id 123, plan_id 456;注意强制计划不是万能药。如果数据分布后续发生变化被强制的计划可能不再最优。建议配合查询存储的「计划回退」报告定期审查。5.2 用扩展事件捕获 PlanAffectingConvert 事件扩展事件Extended Events可以实时捕获隐式转换事件。创建一个会话CREATE EVENT SESSION [CapturePlanAffectingConvert] ON SERVER ADD EVENT sqlserver.plan_affecting_convert ( ACTION ( sqlserver.sql_text, sqlserver.database_name, sqlserver.session_id ) ) ADD TARGET package0.ring_buffer WITH (MAX_MEMORY 4096 KB, EVENT_RETENTION_MODE ALLOW_SINGLE_EVENT_LOSS); ALTER EVENT SESSION [CapturePlanAffectingConvert] ON SERVER STATE START;运行一段时间后读取 ring bufferSELECT CAST(xet.target_data AS XML) AS event_data FROM sys.dm_xe_session_targets xet JOIN sys.dm_xe_sessions xe ON xe.address xet.event_session_address WHERE xe.name CapturePlanAffectingConvert;XML 里会包含具体的 SQL 文本和转换表达式。这个方法比事后翻执行计划更主动适合在上线前做一轮全量扫描。5.3 一个我常用的快速判断习惯每次拿到一条慢查询我会按这个顺序过一遍先看执行计划里有没有PlanAffectingConvert警告再看统计信息的modification_counter和last_updated然后检查 WHERE 条件里有没有函数包裹或运算最后看是不是参数嗅探导致的计划复用。这四步走完九成以上的 Seek 退化问题都能定位到根因。剩下那一成通常是索引本身设计有问题比如索引列顺序不对、缺少包含列导致书签查找代价过高或者过滤索引的 WHERE 条件不匹配。这些属于索引设计层面的问题需要结合具体业务查询模式来调整。希望帮到你。本文还有配套的精品资源点击获取