简介这份PDF文档聚焦SQL Server中执行计划由索引查找Index Seek演变为索引扫描Index Scan的典型场景适合数据库开发、运维与调优人员参考。内容系统梳理了隐式转换、非SARG谓词、选择性低的谓词、统计信息不准确、连接/排序/分组操作、索引覆盖不足、索引碎片、并行计划、资源限制、参数嗅探等十余类触发原因并结合AdventureWorks2014示例给出测试对比与结论帮助理解优化器在何种条件下倾向于选择扫描路径。文档同时提供了避免隐式转换的编码规范、从缓存执行计划中检索隐式转换SQL的脚本以及定期维护统计信息与索引碎片的相关建议能帮助读者快速定位性能瓶颈、优化索引设计。资源为单个PDF文档压缩包大小约415KB内容结构清晰偏重实战总结已有331人学习下载。对想深入理解SQL Server索引机制、提升查询性能的读者而言是一份简洁可用的技术参考。1. SQL Server执行计划退化索引查找和索引扫描只隔着一行代码线上一条跑了半年的SQL Server查询某天突然从几十毫秒掉到好几秒打开执行计划一看原本的Index Seek变成了Index Scan。这几乎是每个SQL Server DBA都会遇到的场景——优化器不会无故改走全扫它一定是判断全扫的成本更低。原因不外乎几类类型隐式转换、非SARG谓词、临界点、统计信息偏差、索引键列序不对。这篇文章是我整理的一组完整测试记录把SQL Server中索引查找变成索引扫描的几类诱因逐一复现给出复现脚本、改写方案和排查工具。它适合正在做查询优化的开发、维护生产库的DBA也适合准备SQL Server性能调优面试的工程师。2. 隐式转换踩坑类型不匹配让索引查找直接失效2.1 一个典型实验NVARCHAR列等值匹配整数在AdventureWorks2014的HumanResources.Employee表上做实验NationalIDNumber列是NVARCHAR类型。使用整数字面量做等值匹配SELECT NationalIDNumber, LoginID FROM HumanResources.Employee WHERE NationalIDNumber 112457891;执行计划里看到的是Index Scan。原因在于SQL Server的比较运算要求两侧数据类型一致NVARCHAR和INT相遇时根据数据类型的优先级高低INT比NVARCHAR优先级高SQL Server会把列值从NVARCHAR转换成INT再比较。列上被包了一层CONVERT_IMPLICIT操作之后B树索引的键值顺序被打乱优化器无法用索引做二分定位只能逐行扫描整张表。这类问题的第一个修复方式是保证比较双方类型一致字面量加N前缀声明为Unicode字符串SELECT NationalIDNumber, LoginID FROM HumanResources.Employee WHERE NationalIDNumber N112457891;N112457891是NVARCHAR类型和列类型一致比较无需转换执行计划恢复Index Seek。第二个方式是用显式转换从代码层面主动控制转换方向SELECT NationalIDNumber, LoginID FROM HumanResources.Employee WHERE NationalIDNumber CAST(112457891 AS NVARCHAR(20));两个方案效果等价区别在于前者改查询传参习惯后者把转换意图写在SQL里。注意CAST的长度选择要和列定义匹配否则后续仍有隐式转换或截断风险。2.2 不是所有隐式转换都会毁掉索引很多朋友在排查时容易走到另一个极端看到执行计划XML里的CONVERT_IMPLICIT就直接判定SQL有问题。实际上隐式转换是否摧毁索引取决于转换作用在列上还是作用在常量上。SQL Server的数据类型优先级有个固定顺序从高到低大致是int → decimal → float → datetime → varchar → nvarchar。比较时低优先级类型向高优先级类型转换。关键点是如果列本身是低优先级类型而传入的常量是高优先级类型那么转换会发生在列上索引就此失效反过来如果列是高优先级类型常量是低优先级类型转换发生在常量上列仍然保持原样Index Seek照常可用。举一个常见的反向例子同样用AdventureWorks2014的Person.Person表BusinessEntityID列是INTSELECT * FROM Person.Person WHERE BusinessEntityID 1;这个SQL虽然传的是字符串1但INT优先级高于VARCHAR转换发生在常量上列未受影响索引依然可用。所以排查时不能只看“类型不匹配”要看转换具体落在哪一边。判断方法是打开执行计划XML找到CONVERT_IMPLICIT节点看ScalarOperator子节点里Identifier/ColumnReference出现在表达式的位置。注意执行计划XML里出现CONVERT_IMPLICIT并不代表索引一定失效关键看转换是发生在列上还是常量上。2.3 用执行计划XML批量抓隐式转换手工一个一个执行计划去看不现实生产库里有几十个缓存的计划排查效率太低了。我一般直接用下面这段SQL扫描整个内存缓存SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; DECLARE dbname SYSNAME; SET dbname QUOTENAME(DB_NAME()); WITH XMLNAMESPACES (DEFAULT http://schemas.microsoft.com/sqlserver/2004/07/showplan) SELECT stmt.value((StatementText)[1], varchar(max)) AS StatementText, t.value((ScalarOperator/Identifier/ColumnReference/Schema)[1], varchar(128)) AS SchemaName, t.value((ScalarOperator/Identifier/ColumnReference/Table)[1], varchar(128)) AS TableName, t.value((ScalarOperator/Identifier/ColumnReference/Column)[1], varchar(128)) AS ColumnName, ic.DATA_TYPE AS ConvertFrom, ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength, t.value((DataType)[1], varchar(128)) AS ConvertTo, t.value((Length)[1], int) AS ConvertToLength, query_plan FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp CROSS APPLY query_plan.nodes(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple) AS batch(stmt) CROSS APPLY stmt.nodes(.//Convert[Implicit1]) AS n(t) JOIN INFORMATION_SCHEMA.COLUMNS AS ic ON QUOTENAME(ic.TABLE_SCHEMA) t.value((ScalarOperator/Identifier/ColumnReference/Schema)[1], varchar(128)) AND QUOTENAME(ic.TABLE_NAME) t.value((ScalarOperator/Identifier/ColumnReference/Table)[1], varchar(128)) AND ic.COLUMN_NAME t.value((ScalarOperator/Identifier/ColumnReference/Column)[1], varchar(128)) WHERE t.exist(ScalarOperator/Identifier/ColumnReference[Databasesql:variable(dbname)][Schema![sys]]) 1;这段脚本的逻辑是sys.dm_exec_cached_plans把SQL Server内存中所有缓存执行计划句柄取出来dm_exec_query_plan解析成ShowPlanXML格式然后通过nodes()函数逐层定位到StmtSimple节点下的Convert节点并且只筛选Implicit属性等于1的隐式转换最终关联INFORMATION_SCHEMA.COLUMNS拿到列的原始类型和长度把转换前和转换后的类型对照展示出来。几个参数点说明一下SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED让查询不产生锁等待避免排查本身卡住线上业务QUOTENAME(DB_NAME())把库名补齐方括号因为ShowPlanXML节点里的Database属性是[库名]格式WITH XMLNAMESPACES必须声明showplan默认命名空间否则XPath路径直接失效Implicit1排除了显式转换只筛CONVERT_IMPLICIT。注意这个脚本只能扫到当前已缓存的计划如果问题SQL从来没在缓存里出现过是查不到的。生产环境可以先跑一遍核心业务接口让计划进缓存然后再执行这段脚本。转换位置索引是否仍可用典型场景列上发生隐式转换失效Index Seek退化为Index ScanNVARCHAR列 INT常量常量上发生隐式转换不受影响Index Seek保持INT列 VARCHAR常量显式CAST控制方向按转换结果走通常保持SeekCAST(常量 AS NVARCHAR(20))3. 非SARG谓词函数、运算和LIKE是怎么拆掉索引查找的SARG是Searchable Arguments的缩写直译过来叫可搜索参数。判断一个谓词是否SARG有一条很朴素的规则B树索引能不能按照这个谓词在有序的键值空间里定位一个连续的查找范围。能定位就是SARG不能定位就需要逐行判断就是非SARG。理解了这条规则接下来三类问题都是一回事。3.1 索引字段包函数对索引字段套上函数是业务代码里最容易犯的错误。比如下面这个SQLSELECT NationalIDNumber, LoginID FROM HumanResources.Employee WHERE SUBSTRING(NationalIDNumber, 1, 3) 112;SUBSTRING把每一行都截取一遍再去跟前缀112匹配。索引键存储的是完整的NationalIDNumber字符串截取出来的前三字符是无序的优化器无法用B树直接定位执行计划必然退化为Index Scan。改写思路是把“对列做函数计算”转成“对列做范围匹配”。这个场景下可以用LIKE前导匹配代替SUBSTRINGSELECT NationalIDNumber, LoginID FROM HumanResources.Employee WHERE NationalIDNumber LIKE 112%;LIKE 112%在语义上表示前三个字符等于112和SUBSTRING(NationalIDNumber,1,3)112是等价的而且它是SARG谓词优化器可以启动Index Seek。注意这个改写依赖字符串排序规则默认的简体中文排序规则下前导匹配语义成立。3.2 索引字段参与运算业务上常见的翻车写法是列和常量做算术运算SELECT * FROM Person.Person WHERE BusinessEntityID 10 260;索引键保存的是列原始值SQL Server没法在一棵按原始值排序的B树上按“列值10”的结果做二分查找。优化器只能把每行的键值先加上10再和260比较全表逐行判断。对这种谓词最优解是把运算移到常量侧改写为数学等价的形式SELECT * FROM Person.Person WHERE BusinessEntityID 250;BusinessEntityID 10 260和BusinessEntityID 250是严格等价的改写后执行计划从Index Scan恢复成Index Seek。这里给一个边界提醒如果运算的两边都是变量比如BusinessEntityID a b不建议贸然改写成BusinessEntityID b - a除非你明确知道a是常量、数据类型不会溢出、b - a的结果仍然在目标列的类型范围内。在存储过程或参数化SQL里优化器会把a当参数而不是常量来估算这种改写牵扯到参数嗅探改不好反而更糟。3.3 LIKE通配符与SARG的关系LIKE语句的SARG属性取决于通配符放在什么位置。前导通配符的查询比如LIKE %Ma%对B树来说是“任意位置包含”索引里所有键值都无法被排除只能全扫。后置通配符的LIKE Ma%则等价于范围查询所有以Ma开头的字符串在排序上落在[M a, Mb)这样一个连续的半开区间里B树可以从起点开始顺序定位。两个SQL的对比实验SELECT * FROM Person.Person WHERE LastName LIKE Ma%; SELECT * FROM Person.Person WHERE LastName LIKE %Ma%;前者执行计划是Index Seek后者几乎必然是Index Scan。原因不用死记理解B树排序即可——前缀相同的数据在索引里物理相邻可以范围读取任意位置包含匹配的数据散布在整棵索引树各处等于没有范围。识别非SARG谓词有个笨办法拿到一条SQL先看WHERE子句里的列名如果列名被函数、算术运算符、前置通配符包围先按非SARG处理然后对照索引定义看谓词列是否在索引键里。在代码评审阶段用这个规则扫一遍能挡掉大部分索引失效问题。很多人以为查询慢的原因是没建索引实际上建了索引但谓词写法不对的情况在存量系统里更常见——索引创建成本高改SQL写法只需要几分钟。4. Tipping Point临界点返回行数到多少时优化器主动改走扫描4.1 用一万行测试表复现临界点先造一张表把临界点完整复现一遍。以下脚本可以直接在测试库执行SET NOCOUNT ON; DROP TABLE IF EXISTS TEST; CREATE TABLE TEST (OBJECT_ID INT, NAME VARCHAR(8)); CREATE INDEX PK_TEST ON TEST(OBJECT_ID); DECLARE Index INT 1; WHILE Index 10000 BEGIN INSERT INTO TEST SELECT Index, kerry; SET Index Index 1; END UPDATE STATISTICS TEST WITH FULLSCAN;这份脚本创建一张一万行的表OBJECT_ID从1到10000每行一条同时建了一个非聚集索引PK_TEST。UPDATE STATISTICS WITH FULLSCAN把统计信息采样率拉到100%排除统计信息因素干扰。注意这里索引名叫PK_TEST但它不是主键约束只是普通非聚集索引这个命名有误导性实际项目中不要这么建。然后执行单值查询SELECT * FROM TEST WHERE OBJECT_ID 1;因为OBJECT_ID1只有一条数据非聚集索引执行Index Seek之后只需要一次书签查找成本很低执行计划停在Index Seek。接下来手工把前2000行的OBJECT_ID全部改成1模拟数据分布变化UPDATE TEST SET OBJECT_ID 1 WHERE OBJECT_ID 2000; UPDATE STATISTICS TEST WITH FULLSCAN; SELECT * FROM TEST WHERE OBJECT_ID 1;这次执行计划直接变成了Table Scan。同样是一条等值查询索引也存在统计信息也是最新的为什么结果完全不同这就是Tipping Point临界点。4.2 临界点的本质是书签查找成本临界点是指“返回的行数不再足够有选择性”的位置。原来OBJECT_ID1的行加上被改的2000行现在等于2001条。每次Index Seek找到一行后都要回到堆表里取NAME列的数据。TEST表没有聚集索引只能靠行标识符回表一次回表就是一次随机I/O。2001次随机I/O的成本在优化器看来已经超过一次全表顺序扫描于是它放弃非聚集索引改走全表扫描。这里有一个重点需要记住临界点只影响非覆盖的非聚集索引。以TEST表为例SELECT *返回OBJECT_ID和NAME两列PK_TEST索引只有OBJECT_ID一列NAME必须回表取。如果索引把NAME也包进来也就是覆盖索引那么所有数据都在索引页里查询根本不用回表就没有随机I/O的问题临界点自然失效。我见过不少同行在这里栽跟头——表的数据量从十万涨到千万某条查询的返回行数始终不超过几十条但执行计划突然从Index Seek变成Index Scan查来查去发现是索引里没有覆盖查询所需的列回表成本让优化器做了重新选择。排查这类问题最简单的方法是把SELECT的列清单和索引键定义对照一下就能判断是不是覆盖不足。4.3 临界点不是固定百分比网上很多文章说临界点是返回行数超过表数据的20%或30%这个说法只能当参考。实际临界点取决于多个参数表的总行数、索引键宽度、回表要取的数据列宽度、页大小、甚至服务器的随机I/O和顺序I/O的速度差。在OLTP环境里我一般以10%作为警戒线超过这个比例的谓词设计就需要警惕但最终判断以优化器的成本计算为准。要定量测出某个表自己的临界点做法也不复杂在测试库上复制一张生产表分批更新数据每批更新后用同一查询跑一遍同时对比SET STATISTICS IO输出的逻辑读看执行计划从Seek切换成Scan时到底发生在哪个数据量上。我测过一张四百万行的订单表临界点出现在返回行数约占全表的12%的位置。不同表的结论浮动很大所以把临界点当成固定百分比去背是不靠谱的。5. 避坑实录统计信息、联合索引、参数嗅探的五个翻车记录5.1 统计信息过时优化器用的是过期地图现象某条查询在测试环境走Index Seek发布到生产环境却走Index Scan或者同一张表白天走Seek深夜批处理时段走Scan。原因SQL Server优化器做成本估算只看统计信息不直接看表里的真实数据。如果统计信息长时间不更新或者自动更新阈值在大表上没有被触发直方图里的估算行数和实际行数相差好几倍优化器的判断自然失真。尤其是经过大批量UPDATE/DELETE的表统计信息很容易和物理数据脱节。解决先手工更新统计信息再观察执行计划。执行UPDATE STATISTICS 表名 WITH FULLSCAN把采样率提到100%重新编译查询后对比执行计划。如果恢复正常说明问题确实出在统计信息过期。长期方案是建立统计信息维护作业每周对变化频繁的表做一次全量更新同时注意AUTO_UPDATE_STATISTICS和AUTO_UPDATE_STATISTICS_ASYNC这两个数据库选项的配置异步更新在高并发下会更平滑。注意不要把UPDATE STATISTICS和ALTER INDEX REBUILD混淆前者只更新元数据后者重建存储结构。5.2 联合索引的列序用反了现象联合索引建在(SalesOrderID, SalesOrderDetailID)上用SalesOrderID查很快单独用SalesOrderDetailID查执行计划显示Index Scan。原因B树索引的键值顺序优先按第一列排序第一列相同才按第二列排。谓词单独落在第二列上时B树找不到查找的起点只能扫描整颗索引。实测语句对比SELECT * FROM Sales.SalesOrderDetail_Tmp WHERE SalesOrderID 43659 AND SalesOrderDetailID 10; SELECT * FROM Sales.SalesOrderDetail_Tmp WHERE SalesOrderDetailID 10;第一个SQL走Index Seek第二个SQL走Index Scan尽管两者用的是同一个联合索引。原因就是第二个语句的谓词没有包含联合索引的第一列SalesOrderID。解决如果第二列是高频查询列单独为它建一个非聚集索引或者调整联合索引的键列顺序把最常作为等值谓词的列放在第一位。调整前必须评估所有依赖这个索引的查询因为键列序变了原有用法可能退化。5.3 参数嗅探第一个参数值决定了后面所有计划现象存储过程第一次用数据量小的参数执行时走Index Seek后续用数据量大的参数执行计划还是Seek反过来第一次用数据量大的参数编译后面所有参数全部走Scan。两种情况的共同点是后续参数和计划不匹配查询越来越慢。原因SQL Server首次编译存储过程时会把参数值代入成本估算生成执行计划后缓存复用。后续不管传什么参数都用同一份计划。当参数值的数据分布和首次编译时的分布差异很大计划就会明显劣化。最典型的场景是“1月份数据只有100条、2月份数据有100万条”这种明显的不均匀分布。解决三种普遍做法。一是OPTION (RECOMPILE)每次执行重新编译牺牲编译开销换取计划正确性适合低频但参数敏感的查询。二是OPTION (OPTIMIZE FOR (param N高频值))指定一个代表性的参数值用于编译兼顾计划和稳定性。三是对分布稳定的参数列用OPTION (OPTIMIZE FOR UNKNOWN)让优化器采用平均密度而不是具体参数值。选择哪种方案取决于业务形态高并发小查询建议参数化并固定优化的参数值批处理大查询建议RECOMPILE。5.4 索引碎片高导致执行计划漂移现象统计信息更新过了查询条件也符合SARG但执行计划就是不走Index Seek或者走了Index Search也明显比之前慢。原因索引碎片率过高时索引页的物理顺序和逻辑顺序分离页密度下降扫描I/O成本比理论估算高。优化器按成本选计划时如果扫描的估算成本和查找加回表的成本差不了多少就可能选择看起来更稳定的扫描路径。解决先查碎片率用sys.dm_db_index_physical_stats这个DMVSELECT OBJECT_NAME(ips.object_id) AS TableName, i.name AS IndexName, ips.avg_fragmentation_in_percent, ips.page_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, LIMITED) AS ips INNER JOIN sys.indexes AS 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重建。REBUILD是重量级操作在维护窗口做不要在生产高峰期直接跑。5.5 覆盖不足回表数据量越过临界点现象查询返回的列不在索引键里非聚集索引需要回表取值。返回行数不大时执行计划正常数据增长后执行计划突然切到Scan。原因回表一次就是一次随机I/O返回行数一多回表成本越过Tipping Point优化器放弃查找。解决在索引里加INCLUDE列把查询要返回的非键列带进叶子节点做成覆盖索引。创建方式CREATE NONCLUSTERED INDEX IX_TEST_OBJECT_ID_INC_NAME ON TEST(OBJECT_ID) INCLUDE (NAME);这个索引键仍是OBJECT_IDNAME列作为附加列存储在叶子节点上。谓词落在OBJECT_ID上时原查询不再需要回表Index Seek的成本大幅下降。注意覆盖索引也不是越多越好每个INCLUDE列都在写操作时增加维护成本选择高频查询且大结果集的场景才值得做。6. 排查路径固化一张TEST表把五类退化一次测完每次执行计划出现不明原因的Index Seek退化成Index Scan我现在固定按一套顺序处理而不是在SSMS的图形执行计划里对着图标干瞪眼。第一步打开执行计划的XML直接搜CONVERT_IMPLICIT有就按隐式转换处理第二步审查谓词表达式看有没有函数包裹、算术运算、前导LIKE有就按非SARG改写法第三步打开SET STATISTICS IO和SET STATISTICS TIME重跑一遍对比逻辑读数和实际返回行数如果实际行数占表行数比例偏大按临界点处理第四步执行UPDATE STATISTICS WITH FULLSCAN后重新编译排除统计信息因素第五步检查索引键列顺序和覆盖列对照避坑章的两个坑。以TEST表为例完整的验证脚本可以这样组织SET NOCOUNT ON; SET STATISTICS IO ON; SET STATISTICS TIME ON; -- 第一步确认执行计划是否含隐式转换 SET SHOWPLAN_XML ON; SELECT * FROM TEST WHERE OBJECT_ID 1; SET SHOWPLAN_XML OFF; -- 第二步查看统计信息直方图 DBCC SHOW_STATISTICS(TEST, PK_TEST) WITH HISTOGRAM;SET SHOWPLAN_XML ON会把后续SQL的执行计划以XML格式输出方便直接检索CONVERT_IMPLICIT节点DBCC SHOW_STATISTICS可以查看直方图里的RANGE_HI_KEY和EQ_ROWS确认优化器估算行数和实际是否一致。做企业级排查和面试准备建议在本地把这五类场景全部复现一遍整理成一个脚本文件。我现在遇到执行计划退化的SQL已经养成了习惯任何结论都必须先看执行计划XML里的CONVERT_IMPLICIT和估算行数验证再动手改写SQL而不是凭“我记得这个查询以前很快”的主观感觉拍板。希望这几条经验能帮你少走一点弯路。本文还有配套的精品资源点击获取
