SQL Server索引查找退化为索引扫描的典型场景与排查方法-育师

简介：这份PDF资料聚焦SQL Server查询优化中的典型性能问题，系统梳理了执行计划从索引查找（Index Seek）退化为索引扫描（Index Scan）的多种成因，适合数据库开发、DBA及性能调优人员参考。内容结合AdventureWorks2014等具体场景展开测试与归纳，涵盖隐式转换、非SARG谓词、选择性低的谓词、统计信息不准确、连接操作、排序分组、索引覆盖不足、索引碎片、并行计划、资源限制以及参数嗅探等十余类情况，并给出避免隐式转换的规范措施与从执行计划中检索隐式转换SQL的脚本思路。资源包为1个PDF文件，大小约415KB，篇幅紧凑、便于随时查阅。目前已有331人学习，可作为排查索引扫描问题、优化索引设计与查询语句的实用参考手册。

1. 一次慢查询复盘：为什么索引查找会变成索引扫描

上周帮同事看一个慢查询，表上明明建了索引，执行计划里却是 Index Scan 而不是 Index Seek，逻辑读直接飙到几万。这个场景在 SQL Server 里太常见了——你建了索引，写了 WHERE 条件，优化器却选择扫描整棵索引树。问题往往不在索引本身，而在于查询写法、数据类型、统计信息这些细节把优化器逼到了另一条路上。

这份资料围绕 SQL Server 中索引查找（Index Seek）退化为索引扫描（Index Scan）的几类典型场景展开，结合具体测试用例做了归纳。它适合已经会看执行计划、但遇到「索引明明在却用不上」这类问题找不到头绪的开发和运维人员。下面我把资料里的测试场景拆开，补上参数说明和排查路径，让你能直接在自己的库上复现和验证。

2. 隐式转换：数据类型不匹配如何把 Seek 逼成 Scan

2.1 隐式转换的触发条件与执行计划变化

SQL Server 允许不同数据类型之间做比较，但代价是运行时自动做类型转换。当转换发生在索引列这一侧时，索引的有序性就被破坏了，优化器无法再用二分查找定位，只能退化为全索引扫描。

资料里给的例子很典型：HumanResources.Employee表的NationalIDNumber字段是NVARCHAR类型，查询写成WHERE NationalIDNumber = 112457891，右边是整型字面量。SQL Server 会把整型转成NVARCHAR再比较，但转换发生在列上，导致索引失效。

-- 翻车写法：整型字面量 vs NVARCHAR 列，触发隐式转换 SELECT NationalIDNumber, LoginID FROM HumanResources.Employee WHERE NationalIDNumber = 112457891; -- 执行计划：Index Scan -- 修正写法：显式加 N 前缀，保持类型一致 SELECT NationalIDNumber, LoginID FROM HumanResources.Employee WHERE NationalIDNumber = N'112457891'; -- 执行计划：Index Seek

逻辑说明：N'112457891'是 Unicode 字符串字面量，类型为NVARCHAR，与列类型完全一致，比较时不需要对列做任何转换，索引可以正常定位。参数上要注意，N前缀不能省，尤其在列定义为NVARCHAR或NCHAR时。

注意：并不是所有隐式转换都会导致扫描。INT转BIGINT这类数值类型之间的转换通常不影响 Seek，真正致命的是字符串与数值、VARCHAR与NVARCHAR之间的转换。

2.2 用缓存计划反查隐式转换 SQL

资料里给了一段从计划缓存中搜索隐式转换的脚本，思路是解析sys.dm_exec_cached_plans里的query_planXML，找出所有带Implicit="1"属性的Convert节点。这个脚本在排查存量 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 stmt_text, t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') AS sch, t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') AS tbl, t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') AS col, 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[@Implicit="1"]') 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[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1;

逻辑说明：sys.dm_exec_cached_plans存的是当前实例的计划缓存，CROSS APPLY把每个计划的 XML 展开，nodes('.//Convert[@Implicit="1"]')筛选出所有隐式转换节点。JOIN INFORMATION_SCHEMA.COLUMNS是为了拿到列的原始类型和长度，方便判断转换方向。参数上，@dbname限定当前数据库，[@Schema!="[sys]"]排除系统对象。跑出来的结果里，如果ConvertFrom和ConvertTo不一致，且列上有索引，基本就是嫌疑对象。

3. 非 SARG 谓词：函数、运算和 LIKE 通配符的边界

3.1 索引列上使用函数或运算

SARG（Searchable Argument）的核心要求是：谓词必须能直接映射到索引键的有序范围。一旦在索引列上套了函数或做了运算，优化器就没法用索引定位了。

-- 翻车写法一：索引列上套函数 SELECT NationalIDNumber, LoginID FROM HumanResources.Employee WHERE SUBSTRING(NationalIDNumber, 1, 3) = '112'; -- 执行计划：Index Scan -- 翻车写法二：索引列参与运算 SELECT * FROM Person.Person WHERE BusinessEntityID + 10 < 260; -- 执行计划：Index Scan -- 修正写法：把运算移到常量侧 SELECT * FROM Person.Person WHERE BusinessEntityID < 250; -- 执行计划：Index Seek

逻辑说明：SUBSTRING对列做了计算，索引里存的是原始值，不是子串，优化器无法用索引树定位。BusinessEntityID + 10 < 260等价于BusinessEntityID < 250，但前者把运算加在了列上，后者把运算留在了常量侧。参数上，改写时要注意边界值是否包含等号，< 260和+ 10 < 260在整数场景下等价于< 250，但浮点数场景要小心精度。

3.2 LIKE 通配符的位置决定一切

LIKE是否属于 SARG，完全取决于通配符的位置。前缀匹配LIKE 'Ma%'可以利用索引的有序性做范围查找，而LIKE '%Ma%'或LIKE '%Ma'因为左侧不确定，只能扫描。

-- SARG：前缀匹配，走 Seek SELECT * FROM Person.Person WHERE LastName LIKE 'Ma%'; -- 非 SARG：前置通配符，走 Scan SELECT * FROM Person.Person WHERE LastName LIKE '%Ma%';

逻辑说明：索引按LastName的字母顺序排列，'Ma%'能确定一个起始点，优化器可以定位到Ma开头的第一条记录然后顺序读。'%Ma%'没有确定的起点，只能逐行检查。参数上，如果业务确实需要中间匹配，常见做法是用全文索引替代，或者把LastName反转后建索引再查'aM%'。

注意：NOT、!=、<>、NOT IN、NOT EXISTS、NOT LIKE这些否定操作符同样属于非 SARG，优化器通常不会用它们做索引定位。

4. 临界点与统计信息：优化器为什么主动放弃 Seek

4.1 临界点（Tipping Point）的触发条件

临界点是非覆盖非聚集索引的一个特性：当查询返回的行数超过某个比例时，优化器认为书签查找的随机 I/O 成本已经超过全表扫描的顺序 I/O，于是主动放弃索引查找。资料里的测试很直观：一万行的表，查OBJECT_ID = 1只有一行时走 Seek，把两千行更新成OBJECT_ID = 1后，占比达到 20%，执行计划变成 Table Scan。

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 只有一行，走 Index Seek SELECT * FROM TEST WHERE OBJECT_ID = 1; -- 手工把 2000 行改成 OBJECT_ID=1，占比 20% UPDATE TEST SET OBJECT_ID = 1 WHERE OBJECT_ID <= 2000; UPDATE STATISTICS TEST WITH FULLSCAN; -- 此时走 Table Scan SELECT * FROM TEST WHERE OBJECT_ID = 1;

逻辑说明：临界点只与非覆盖、非聚集索引有关。覆盖索引因为不需要回表，不存在书签查找成本，所以没有这个问题。参数上，临界点的具体阈值不是固定的 20%，它受行宽、统计信息、硬件 I/O 特性影响，但经验值通常在 20% 到 30% 之间。排查时如果发现执行计划突然从 Seek 变 Scan，先看返回行数占比。

4.2 统计信息缺失或过时的影响

统计信息是优化器估算行数的依据。如果统计信息缺失或过时，优化器可能高估或低估返回行数，从而选错计划。资料里提到这个场景构造案例比较难，但实际排查中很常见。

-- 查看统计信息的最后更新时间 SELECT OBJECT_NAME(s.object_id) AS table_name, s.name AS stat_name, sp.last_updated, sp.rows, sp.rows_sampled, sp.modification_counter FROM sys.stats AS s CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp WHERE OBJECT_NAME(s.object_id) = 'TEST';

逻辑说明：sys.dm_db_stats_properties返回统计信息的最后更新时间、采样行数和修改计数器。如果modification_counter很大而last_updated很久以前，说明统计信息已经过时。参数上，rows_sampled远小于rows时，统计信息的准确性也值得怀疑。常见做法是开启自动更新统计信息，或者对大表在业务低峰期手动UPDATE STATISTICS ... WITH FULLSCAN。

5. 联合索引与谓词顺序：第一列不是过滤条件会怎样

5.1 联合索引的最左前缀原则

联合索引(SalesOrderID, SalesOrderDetailID)的索引树先按SalesOrderID排序，再按SalesOrderDetailID排序。如果查询只过滤SalesOrderDetailID，优化器无法利用索引的有序性定位，只能扫描。

SELECT * INTO Sales.SalesOrderDetail_Tmp FROM Sales.SalesOrderDetail; CREATE INDEX PK_SalesOrderDetail_Tmp ON Sales.SalesOrderDetail_Tmp(SalesOrderID, SalesOrderDetailID); UPDATE STATISTICS Sales.SalesOrderDetail_Tmp WITH FULLSCAN; -- 走 Seek：谓词包含联合索引第一列 SELECT * FROM Sales.SalesOrderDetail_Tmp WHERE SalesOrderID = 43659 AND SalesOrderDetailID < 10; -- 走 Scan：谓词只有第二列 SELECT * FROM Sales.SalesOrderDetail_Tmp WHERE SalesOrderDetailID < 10;

逻辑说明：第一条 SQL 里SalesOrderID = 43659能定位到索引的一个区段，SalesOrderDetailID < 10在这个区段内继续定位。第二条 SQL 缺少SalesOrderID条件，SalesOrderDetailID在整个索引里是乱序的，只能全扫。参数上，如果业务确实需要单独按第二列查，常见做法是再建一个以该列为第一列的索引，或者调整联合索引的列顺序。

5.2 覆盖索引为什么能绕过临界点

覆盖索引包含查询需要的所有列，不需要回表做书签查找，因此没有临界点问题。资料里强调「覆盖索引没有这个问题」，这是性能调优里最值得投入的方向之一。

-- 非覆盖索引：需要回表，有临界点 CREATE INDEX IX_NonCover ON TEST(OBJECT_ID); -- 覆盖索引：包含 NAME 列，不需要回表 CREATE INDEX IX_Cover ON TEST(OBJECT_ID) INCLUDE (NAME); -- 同样的查询，覆盖索引下更容易保持 Seek SELECT OBJECT_ID, NAME FROM TEST WHERE OBJECT_ID = 1;

逻辑说明：INCLUDE子句把非键列加到索引的叶子节点，查询只需要访问索引页就能拿到全部数据。参数上，INCLUDE列不计入索引键的长度限制，适合放那些只出现在 SELECT 列表里、不出现在 WHERE 里的列。代价是索引占用空间变大，写入维护成本增加，需要在查询性能和存储成本之间权衡。

6. 排查清单与一个验证习惯

把上面几类场景串起来，实际排查时我一般按这个顺序走：先看执行计划里是 Seek 还是 Scan，如果是 Scan，检查 WHERE 子句里索引列有没有被函数或运算包住；再看数据类型是否一致，特别是NVARCHAR和VARCHAR混用；然后看返回行数占比，判断是不是临界点；接着查统计信息的更新时间和修改计数器；最后确认联合索引的谓词是否包含第一列。

验证方法上，我习惯在改完 SQL 或索引后，用SET STATISTICS IO ON和SET STATISTICS TIME ON对比逻辑读和 CPU 时间，而不是只看执行计划里的 Seek/Scan 图标。因为有时候优化器选了 Seek，但实际逻辑读反而更高，这种情况在参数嗅探或统计信息偏差时会出现。

SET STATISTICS IO ON; SET STATISTICS TIME ON; -- 改前改后各跑一次，对比 logical reads 和 CPU time SELECT NationalIDNumber, LoginID FROM HumanResources.Employee WHERE NationalIDNumber = N'112457891'; SET STATISTICS IO OFF; SET STATISTICS TIME OFF;

逻辑说明：SET STATISTICS IO ON输出每个表的扫描次数、逻辑读、物理读等信息，SET STATISTICS TIME ON输出解析、编译、执行各阶段耗时。参数上，逻辑读是衡量查询代价最稳定的指标，受缓存影响小。对比时要在同一会话、同一数据状态下跑，避免缓存和并发干扰。