简介对正在排查 SQL Server 慢查询的开发者和 DBA 来说执行计划中索引查找Index Seek意外退化为索引扫描Index Scan往往是性能下降的关键信号。这份 PDF 围绕这一现象结合具体测试场景与执行计划示例归纳了隐式转换、非 SARG 谓词、统计信息不准确、连接与排序操作、索引覆盖不足、索引碎片、参数嗅探等常见诱因并在每类场景后说明判断要点与规避方法如统一数据类型、使用显式转换、更新统计信息、创建覆盖索引和定期维护索引。文中还给出从缓存执行计划中搜索隐式转换 SQL 的脚本可直接用于日常巡检和代码评审。压缩包共 1 个 PDF 文档大小约 415KB已有 331 人学习下载。对需要理解执行计划机制并快速定位低效查询的读者这是一份实用的问题分析笔记。1. 索引查找变成索引扫描一条让 SQL Server 调优者警觉的执行计划信号在 SQL Server 上做索引查找变成索引扫描的问题分析是很多慢查询调优故事的第一幕。明明表上有索引、统计信息也新鲜执行计划里却清一色 Index Scan这个反常信号意味着查询写法、参数形态或索引设计里藏着一处隐蔽问题。这篇博客想讲清楚的是Seek 退化到 Scan 的根本原因有哪几类如何定位以及怎样用最少操作让索引重新被高效利用。它适合一线 DBA、大数据量下的后端开发者以及一切被“有索引却跑不快”反复折磨的从业者。目标是让你看完后能顺着排查路径自己动手复现并解决同类问题。2. 读懂 Seek 与 Scan 的成本分岔优化器凭什么放弃索引查找2.1 Seek 是定位Scan 是遍历先认清两种算子的行为差异Index Seek 不是“用了索引”Index Scan 也不是“没走索引”。两者的本质差异在于导航方式Seek 从索引根节点一路下探到匹配的叶子页只返回满足条件的局部数据Scan 则遍历整棵索引树的叶子层把全部或大部分数据过一遍。执行计划里看到 Index Scan 时如果表上确实有可用索引多半是优化器做了成本比较后认为 Scan 更便宜或者查询条件根本没法被表达成可用的定位条件。在 SSMS 中按 CtrlM 开启“包含实际执行计划”跑完查询就能看到算子图标。Seek 算子旁的“谓词”属性会显示定位条件Scan 算子则只有“扫描谓词”或没有谓词。还有一个容易被忽略的细节是“残余谓词”它的存在意味着 Seek 虽然下探到了索引页但索引本身没有完整记录该条件还需要在定位后的每一行上再过滤一次这种半吊子 Seek 的性能往往比直接 Scan 好不到哪去。有不少人把非聚集索引 Scan 误认为“至少走了索引”。实际上非聚集索引 Scan 同样会把所有叶子页读一遍如果查询列没有被索引覆盖每次命中一行还要做一次回表成本甚至高于连续读聚集索引的表扫描。遇到这类计划先看索引的键列和 INCLUDE 列有没有覆盖查询列再谈 Seek 退化的问题。2.2 成本估算的临界点预估行数如何决定走向优化器选择 Seek 还是 Scan说到底是一次成本估算。当预估返回行数占表总行数的比例上升时Seek 的优势就衰减因为 Seek 意味着大量单页随机 I/O而 Scan 是顺序 I/O。两者交织处大约在总行数的 20% 到 30% 附近具体取决于页数、缓冲池命中率和索引深度。这个临界点不是固定值而是优化器根据统计信息算出来的一个分界。影响这个估算的核心变量是统计信息里的密度和直方图。优化器不是真实执行它靠统计信息猜行数。如果统计信息过期预估就失真临界点会被错误地跨过计划里就会看到 Scan。这也是为什么做完大表更新后DBA 的第一反应是更新统计信息而不是重建索引——只有先让优化器“看见”真实数据分布Seek 才有机会被纳入考虑。决定分界点的还有两个隐藏变量索引深度和每个页能装多少行。索引深度决定了每次 Seek 的定位代价深度 4 的索引比深度 2 的索引贵页密度高则 Scan 一次读到的有效行更多。这就是为什么同样 20% 的返回比例在深度 3、页密度高的表上优化器可能选 Seek在深度 5、页密度低的表上却选 Scan。你不需要背公式只要知道 Seek 和 Scan 不是二选一的宝座而是一条滑动的成本曲线。来看一段快速查看成本评估的 SQL用 SHOWPLAN 文本模式输出计划不会真正执行查询但是排查 Seek 退化时很好用SET SHOWPLAN_ALL ON; GO SELECT SalesOrderID, OrderDate FROM Sales.SalesOrderDetail WHERE ProductID 897; GO SET SHOWPLAN_ALL OFF; GO这段 SQL 的作用是把执行计划以文本形式打印出来。看输出里的“Estimated Number of Rows”和“Estimated Operator Cost”两列这是理解 Seek/Scan 走向的两把钥匙。如果预估行数和实际行数偏差一个数量级先别急着改索引回去查统计信息。参数说明SHOWPLAN_ALL 是会话级设置用完必须关掉否则后续所有查询都只出计划不执行。2.3 查询参数化的副作用常量消失后优化器开始盲猜很多业务系统把 SQL 写成存储过程参数化查询好处是计划重用坏处是优化器在编译时拿不到参数的实际值只能用统计信息里的密度估算选择性。一个典型场景WHERE Status Status一个常量的选择性可能是 1%适合 Seek另一个常量的选择性是 60%适合 Scan。参数化后优化器只能按平均密度猜最终选了一个中庸计划。这个“中庸”往往表现为 Scan因为它对单次执行都不算最优但由于计划被缓存它成了所有参数共享的唯一方案。这种情况的常规解法有 OPTION(RECOMPILE)、OPTION(OPTIMIZE FOR UNKNOWN)以及把高频参数拆成独立查询。这些会在第四章展开细说这里先把机制点透Seek 变 Scan 不一定是查询写坏了有时是参数化让索引失去了被精准利用的外部信息。3. 非 SARG 写法哪些查询条件把索引查找活活逼成索引扫描SARGable即 Search Argument Able指的是查询条件能否借助索引快速定位。不能 SARG 的写法通常让索引列被函数、运算符或类型转换包裹优化器无法把条件折叠成 Seek 的起点和终点只能退而求其次走 Scan。这一章列的三种写法是生产环境里最常见的翻车来源。3.1 对索引列做函数运算YEAR(OrderDate) 的连环代价先看一个我反复见到的现场。报表中心跑月初统计一条 SQL 把三千万行的订单表扫了个遍-- 翻车写法索引列被 YEAR() 包裹 SELECT OrderID, CustomerID, TotalAmount FROM Sales.Orders WHERE YEAR(OrderDate) 2024;现象是执行计划显示 Index Scan逻辑读几十万而这个表在 OrderDate 上明明有非聚集索引。原因是 YEAR(OrderDate) 把 OrderDate 列的原始值在进入索引搜索前改写了索引键里存的是原始日期值不是年份值优化器无法把“年份等于 2024”换算成索引键上的一个范围于是放弃 Seek。改写方式是把条件变成半开区间让优化器能看到明确的起点和终点SELECT OrderID, CustomerID, TotalAmount FROM Sales.Orders WHERE OrderDate 2024-01-01 AND OrderDate 2025-01-01;改写后的条件能直接映射到索引键逻辑读能从几十万降到几百。注意不仅是函数列上的算术运算也一样比如 ColumnA 1 10。把运算挪到等号另一边改成 ColumnA 9Seek 才能生效。这个原则叫“保持索引列裸奔”。3.2 隐式转换列和参数类型不一致导致的看不见的 Scan隐式转换比函数更隐蔽因为 SQL Server 不报错。最常见的场景是手机号字段用了 varchar查询参数传了 int-- 翻车写法参数类型与列类型不一致 SELECT CustomerID, PhoneNumber FROM Sales.Customers WHERE PhoneNumber 13800138000;PhoneNumber 在表里是 varchar(20)参数 13800138000 是 int 常量。SQL Server 的隐式类型转换规则是向高优先级方向转int 优先级比 varchar 高实际发生的是 CONVERT(varchar, PhoneNumber) 13800138000索引列被转换函数包住Seek 条件失效计划落到 Scan。正确写法是让参数类型与列类型一致SELECT CustomerID, PhoneNumber FROM Sales.Customers WHERE PhoneNumber 13800138000;把参数写成字符串字面量或者程序传参时声明为 varchar。判断方法是在执行计划里找 CONVERT_IMPLICIT 警告黄色的感叹号很醒目它一般出现在谓词表达式里。一看到它先查类型别先查索引。注意强制 CAST 时也要小心。CAST(PhoneNumber AS varchar(20)) 13800138000 依然会导致 Scan正确做法是 CAST(13800138000 AS varchar(20)) PhoneNumber保持列不被包裹。3.3 OR 与 IN 的分岔为什么 IN 能走多个 SeekOR 却经常变成 Scan后端开发最容易踩的是 OR 与 IN 的差异。两者表面是同一语义但执行计划走向可能完全不同-- 翻车写法两个条件用 OR 连接 SELECT OrderID, Status, CustomerID FROM Sales.Orders WHERE Status Paid OR CustomerID 10086;即使 Status 和 CustomerID 各有单列索引优化器也并非一定会做索引交集。它可能计算后发现合并代价高直接改成 Scan。即使两边各走 Seek最终还要做串联和去重这步操作在成本模型里经常被高估优化器宁可一次 Scan。更稳定的写法是拆成 UNION ALL让每个分支各自命中索引SELECT OrderID, Status, CustomerID FROM Sales.Orders WHERE Status Paid UNION ALL SELECT OrderID, Status, CustomerID FROM Sales.Orders WHERE CustomerID 10086 AND Status Paid;第二个分支要加 Status Paid否则两段结果可能出现同一行UNION ALL 不去重业务数据会翻倍。而 IN 的情况通常好很多WHERE Status IN (Paid, Pending, Refunded) 这类写法优化器倾向于拆成多个 Seek 再用 Concatenation 拼接是 Seek 友好的。所以能写成 IN 就别写 OR如果必须用 OR用 UNION ALL 拆路。3.4 前导通配符% 开头的 LIKE 天生无法被索引搜索利用LIKE %ABC% 因为通配符在前优化器确定不了起始键索引无法下探执行计划只能 Scan。LIKE ABC% 则是 SARGable 的因为字符串排序规则让优化器知道起点是 ABC终点是 ABD可以映射到一个索引键范围。解决方案是业务上改存倒排文本或者考虑全文索引没有通用的小改动能将前导通配符变成 Seek。这里有一个容易被忽略的点LIKE ABC% 虽然能走 Seek但如果查询列没有被索引覆盖Seek 之后同样会触发回表行数多时优化器仍可能退化到 Scan。因此处理前导通配符时要连同 SELECT 列一起检查必要时加 INCLUDE 列补齐覆盖。4. 统计信息与参数嗅探优化器记错账导致的索引退化索引本身没问题、查询也符合 SARG 原则但执行计划里依然是 Index Scan这时问题多半出在优化器做判断前拿到的数据上。统计信息失真和参数嗅探是两大头号嫌疑排查顺序应该排在建索引之前。4.1 统计信息过期密度与直方图跟不上数据变化统计信息是优化器的视力。它记录每个索引键的直方图即值的分布。当表上插入、删除、更新的行数占比超过阈值大约 20% 的行数变化时统计信息会标记为过期。此时直方图可能还停留在表只有 10000 行的状态而实际表已经有 1000000 行原本预估返回 100 行的 Seek被错估成返回 1000 行跨过临界点后优化器就选了 Scan。先查看统计信息最后更新时间SELECT name, stats_date(object_id, stats_id) AS last_update FROM sys.stats WHERE object_id OBJECT_ID(Sales.Orders);手动更新统计信息UPDATE STATISTICS Sales.Orders; -- 精确更新某索引全扫描方式 UPDATE STATISTICS Sales.Orders IX_Orders_OrderDate WITH FULLSCAN;第一条不带选项按采样方式更新速度快但精度一般第二条使用 FULLSCAN 扫描全部分发数据精度最高、耗时最长适合在维护窗口对大表执行。日常自动更新够用就不用每天手动更新。如果更新后计划仍不变说明问题不在统计信息去看参数嗅探或强制计划。4.2 参数嗅探首次调用的参数值决定后续所有会话参数嗅探是所有走存储过程或参数化查询的系统都躲不开的机制。SQL Server 编译时会把首次传入的参数值放进成本估算并将该计划存入计划缓存。之后即使参数变化只要查询文本不变、统计信息没变计划就沿用旧版本。假设 Orders 表有 1024 万行Status 列的值分布是 Paid 占 99%Draft 占 0.5%。存储过程第一次执行时恰好有人传了 Draft优化器看到返回行数很小生成 Seek 计划并缓存。之后业务高峰期所有会话都传 PaidSeek 计划被复用于高频参数等于用随机 I/O 查一百万行比 Scan 更慢。反过来如果第一次传 Paid 缓存了 Scan 计划后续小范围的 Draft 查询也沿用 Scan这正是从 Seek 变 Scan 的一条具体线索。排查参数嗅探先看计划缓存里有哪些候选计划SELECT plan_handle, creation_time, last_execution_time FROM sys.dm_exec_query_stats ORDER BY last_execution_time DESC;拿到对应的 plan_handle 后清掉那个特定计划DBCC FREEPROCCACHE(plan_handle);DBCC FREEPROCCACHE() 清全部带参数只清指定计划。生产环境慎用全局清缓存否则所有查询都要重新编译可能引起 CPU 尖峰。常见修复方案是给存储过程加 OPTION(RECOMPILE)让每次执行都重新编译避免嗅探绑定代价是 CPU 开销增加。注意对低频重查询RECOMPILE 几乎无感对高频轻查询每次多几十毫秒编译时间可能得不偿失。中间路线是 OPTION(OPTIMIZE FOR UNKNOWN)不取参数具体值按统计信息密度生成一个中庸计划对分布均匀的列友好。4.3 覆盖索引缺失与列顺序Seek 成功也兜不住回表的账有时计划里确实出现过 Seek但随后紧跟着大量 Key Lookup。当回表行数多优化器算总账发现 Seek 加回表的成本已经高于直接 Scan它会从 Seek 退化到 Scan。这种问题不能说优化器傻了而是两个选择都贵它选了看起来更平滑的那个。缓解思路是让索引覆盖查询。比如下面这条查询SELECT SalesOrderID, OrderDate, TotalDue FROM Sales.Orders WHERE CustomerID 11000;如果只有一个单列索引 IX_Orders_CustomerIDSeek 能定位 CustomerID11000 的指针但 OrderDate 和 TotalDue 得回聚集索引取。返回 500 行就要 500 次随机 I/O。给这个查询设计一个覆盖索引CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_Inc ON Sales.Orders (CustomerID) INCLUDE (OrderDate, TotalDue);INCLUDE 列不参与排序和定位只用来存叶子页里的附加列。这样 Seek 之后可以直接从索引页取数Key Lookup 消失Scan 的诱因也随之消失。复合索引列顺序的要点是等值谓词列放在最前范围谓词放后面。比如 WHERE CustomerID 11000 AND OrderDate 2024-01-01索引键顺序 (CustomerID, OrderDate) 才能让 OrderDate 的范围定位生效。顺序写反OrderDate 上的范围就没法被利用只能变成 Seek 后的残余过滤或干脆走 Scan。5. 避坑记录五个让索引查找变成索引扫描的真实翻车现场这一章按现象、原因、解决的格式整理五条我亲手排查过的坑。它们都在生产环境真实发生过并且都有执行计划佐证。5.1 现象存储过程里的局部变量让 Seek 失效现象是某个存储过程内部先声明了局部变量DECLARE start datetime DATEADD(day, -30, GETDATE())然后写 WHERE CreatedAt start。CreatedAt 上有索引但执行计划还是 Index Scan。原因是局部变量在编译时被当作 UNKNOWN不参与参数嗅探。优化器对它的选择性只能按密度“猜”猜出来的比例高于 Seek 临界点就选 Scan。解决方法是把日期计算挪到查询外面让 SQL Server 看见一个裸常量或者把逻辑改成存储过程入参由调用方传入。局部变量这个坑的本质是优化器看不见值而不是缺少统计信息光更新统计信息解决不了。5.2 现象统计信息天天更新但分区表计划还是 Scan有一张分区表每天凌晨 ETL 写入上百万行。DBA 更新了全局统计信息也重建过索引白天查询还是 Scan。原因是全局统计信息已更新但分区级统计信息没跟上。查询里带了分区裁剪条件优化器取的是分区级直方图全局直方图根本派不上用场。解决方法是按分区更新统计信息对每个分区执行 UPDATE STATISTICS 时带 RESAMPLE 选项让采样比例继承分区现有配置。更彻底的做法是在维护窗口对整表做一次 FULLSCAN 更新之后重建索引让分区级和全局级统计信息保持一致。从那以后我把分区表的统计信息维护单独列进作业不再和普通表混在一起。5.3 现象JOIN 条件上的隐式转换引爆全表扫描一条存储过程里Orders.OrderID 是 varchar(20)Products.ID 是 intJOIN 条件直接写等号。两表各自的索引都有但执行计划显示连接时扫描了订单主表。原因是 varchar 与 int 比较时varchar 列被隐式转换OrderID 的索引失效。解决方法是统一两列的数据类型或者在 JOIN 时把参数显式 CAST 成目标类型。关键点在于转换参数而不是转换列写成 p.ID CAST(OrderID AS int)而不是 CAST(p.ID AS int) OrderID。排查这类问题时直接看执行计划里连接运算符下方的警告图标有黄色感叹号就先查类型往往比调整 JOIN 顺序更有效。5.4 现象索引碎片率一路涨Seek 也跟着变 Scan索引整理之前查询的预估行数没问题实际返回行数也不多但 Seek 还是退化成了 Scan。原因是索引叶子页碎片率高页与页之间的逻辑顺序和物理顺序错位严重。一次 Seek 本来要读几十个页现在要读上百个页随机 I/O 成本被放大优化器宁愿选择顺序 Scan。先查碎片率再决定用什么级别整理SELECT OBJECT_NAME(ips.object_id) AS table_name, i.name AS index_name, ips.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(Sales.Orders), NULL, NULL, LIMITED) ips JOIN sys.indexes i ON ips.object_id i.object_id AND ips.index_id i.index_id WHERE ips.avg_fragmentation_in_percent 30;碎片率在 5% 到 30% 之间用 ALTER INDEX ... REORGANIZE 做逻辑重排锁开销小碎片率超过 30%用 ALTER INDEX ... REBUILD WITH (ONLINE ON) 重建索引联机方式能减少阻塞。重建完索引后再看执行计划Seek 经常自己就回来了。5.5 现象加了 OPTION(RECOMPILE) 后 Seek 回来了但 CPU 涨了现象是存储过程线上慢我加了 RECOMPILE执行计划恢复 Seek但监控显示 CPU 整体上涨。原因是这个存储过程每分钟被调用上千次RECOMPILE 让每次调用重新编译编译开销超过了查询收益。解决方法是撤掉 RECOMPILE改成 OPTION(OPTIMIZE FOR(StatusS))为这个存储过程指定典型参数让参数嗅探退化为固定参数提示计划稳定且编译次数少。后来生产验证 CPU 回落查询保持 Seek。从这里我学到一个习惯任何强制手段都要考虑调用频率高频低耗时查询最怕编译开销低频高耗时查询才适合 RECOMPILE。6. 回归验证如何确认索引查找真的回来了改完查询或索引后不能只看执行计划图形就收工要量化“Seek 回来”和“整体变快”。我的验证工序分三层。6.1 第一层用 SET STATISTICS IO 量化逻辑读打开统计信息输出SET STATISTICS IO ON; SET STATISTICS TIME ON;跑一遍修正前的 SQL 和修正后的 SQL对比逻辑读、物理读与 CPU 耗时。逻辑读从几万降到几百比看执行计划更直观。这里注意缓存命中率高会让物理读很低所以要以逻辑读为主。如果逻辑读降了但耗时没降下一步就要看是不是阻塞或编译时间占了主导。6.2 第二层清理并观察计划缓存里的旧计划有时候修改了存储过程但缓存里还是老计划执行计划看着像 Scan其实是旧缓存没清。我的习惯是先拿到 plan_handle 再清单个或者直接 DBCC FREEPROCCACHE() 在测试库上去除干扰。生产环境不要随便全局清缓存如果动完代码后计划还是老样子优先怀疑计划缓存复用问题而不是索引设计问题。6.3 第三层用极端参数探测参数嗅探是否解除验证参数嗅探是否真的解除了方法是用两个极端参数连续调用EXEC dbo.GetOrders Draft; EXEC dbo.GetOrders Paid;分别抓出两次的执行计划。如果两者都按各自参数生成了合理路线说明方案有效如果两者恒定都是 Scan说明问题不仅是嗅探还在写法或索引设计。这一步能分辨是优化器不选 Seek还是 Seek 根本没条件可用。检查项上游方法通过标准逻辑读SET STATISTICS IO ON修正后逻辑读较之前下降一个数量级执行计划形状实际执行计划关键路径出现 Index Seek无 Scan参数稳定性两个极端参数分别执行两次计划均存在 Seek 且路由合理排查时还有一个诊断技巧用 WITH (INDEX(IX_Orders_OrderDate)) 索引提示强制走 Seek能立刻分辨是优化器不选 Seek还是 Seek 根本没条件可用。如果强制后计划还是 Scan多半是条件写坏了如果强制后 Seek 回来了且逻辑读低那不用长期用提示回头修统计信息或参数嗅探就好。我现在的习惯是任何一次 Seek 退化排查都先抓住两样东西一个实际执行计划、一份统计信息 IO 输出。没有这两样后面所有推断都是猜。诊断完成后再动索引或查询改完再做一次三方对比确认逻辑读、执行计划形状、极端参数三关都过了才收工。希望帮到你。本文还有配套的精品资源点击获取
