企业官网建设流程全解析

简介：这份PDF资料聚焦SQL Server查询优化中的典型性能问题，系统梳理了执行计划从索引查找（Index Seek）退化为索引扫描（Index Scan）的多种成因，适合数据库开发、DBA及性能调优人员参考。内容结合AdventureWorks2014等具体场景，逐一测试并归纳了隐式转换、非SARG谓词、选择性低的谓词、统计信息不准确、连接操作、排序分组、索引覆盖不足、索引碎片、并行计划、资源限制及参数嗅探等十余种情况，并给出避免隐式转换的代码规范、脚本排查方法等实用思路。资源包为1个PDF文件，大小约415KB，轻量便携，便于随时查阅。目前已有331人学习，读者可借此理解索引查找与扫描的差异，掌握从执行计划中定位问题、调整索引设计与查询语句、定期维护统计信息的具体方法，从而减少不必要的全索引扫描，提升SQL Server查询性能。

1. 索引查找为什么会在 SQL Server 里悄悄变成索引扫描

线上告警响了，一条平时 20ms 出结果的查询突然跑到 3 秒，执行计划里原本的 Index Seek 变成了 Index Scan，CPU 直接飙到 90%。这不是玄学，是 SQL Server 里最经典的一类性能翻车：你明明建了索引，优化器却选择全扫。很多做后端的同学第一反应是“索引失效了”，其实索引没坏，是优化器在特定代价估算下主动放弃了查找。这个标题要解决的就是：为什么查找会退化成扫描、怎么定位、怎么改回来。适合已经会看执行计划、写过非聚集索引，但被“索引明明在却不用”卡住的一线开发和 DBA。下面按“先看懂再动手”的顺序拆开讲，每一步都能在你自己的库上复现。

2. 先搞懂 Index Seek 与 Index Scan 的代价分界

2.1 两种访问方式的本质差别

Index Seek 是 B 树上的定位操作：从根页往下，按键值二分找到目标区间，只读命中页。Index Scan 是从索引叶子层第一页开始顺序读到底，或者读到满足条件为止。很多人以为 Scan 一定比 Seek 慢，这不对。当查询要返回的行占表比例很高时，Scan 的顺序 IO 反而比大量随机 Seek 更便宜，优化器选 Scan 是理性的。问题出在“该 Seek 却 Scan”的场景：返回行很少，却因为统计信息、隐式转换、参数嗅探等原因，让优化器估错了行数，误判 Scan 更划算。

理解这一点很关键：Seek 和 Scan 的切换不是布尔开关，而是基于代价模型（Cost-Based Optimizer）的估算结果。估算的输入包括统计信息直方图、谓词选择性、参数值、索引列顺序。任何一个输入失真，输出就可能从 Seek 翻成 Scan。

2.2 用执行计划确认到底是哪种访问

先学会读计划，别凭感觉。打开实际执行计划，看运算符属性里的Physical Operation和Logical Operation。下面这段查缓存的 DMV 能快速定位扫描大户：

-- 找出缓存计划里含扫描且逻辑读高的语句 SELECT TOP 20 qs.execution_count, qs.total_logical_reads, qs.total_logical_reads / qs.execution_count AS avg_logical_reads, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) 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 qp.query_plan.exist('//RelOp[@PhysicalOp="Index Scan"]') = 1 ORDER BY qs.total_logical_reads DESC;

逻辑说明：sys.dm_exec_query_stats提供聚合执行统计，query_plan.exist()用 XQuery 过滤出含 Index Scan 的计划。参数说明：total_logical_reads高说明扫描代价大；avg_logical_reads能看出单次执行的平均读页数。注意这个查询本身有开销，别在高频业务库上反复跑。

2.3 估算行数与实际行数的偏差是核心线索

在实际执行计划里，把鼠标悬停在扫描运算符上，对比Estimated Number of Rows和Actual Number of Rows。如果估算 1 行、实际 50 万行，优化器就是被统计信息骗了。这种偏差通常来自三类原因：统计信息过期、参数嗅探、隐式类型转换。记住这个判断顺序，后面排查章节会逐一展开。

3. 让查找退化成扫描的四类典型诱因与复现

3.1 隐式转换：列类型和参数类型不一致

这是最高频的诱因。假设有一张订单表，OrderNo是VARCHAR(20)，你传参时用了NVARCHAR，SQL Server 会把列转成NVARCHAR再比较，索引直接失效。

-- 建一张模拟表 CREATE TABLE dbo.Orders ( OrderId INT IDENTITY PRIMARY KEY, OrderNo VARCHAR(20) NOT NULL, CustomerId INT NOT NULL, CreatedAt DATETIME2 NOT NULL ); CREATE NONCLUSTERED INDEX IX_Orders_OrderNo ON dbo.Orders(OrderNo); -- 插入 10 万行模拟数据 INSERT INTO dbo.Orders(OrderNo, CustomerId, CreatedAt) SELECT 'ORD' + RIGHT('00000000' + CAST(n AS VARCHAR(8)), 8), n % 5000, DATEADD(MINUTE, -n, SYSDATETIME()) FROM (SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects a CROSS JOIN sys.all_objects b) t;

逻辑说明：OrderNo定义为VARCHAR，索引建在它上面。参数说明：RIGHT(...)保证编号定长，n % 5000制造重复的客户分布，方便观察选择性。

现在跑两条查询对比：

-- 查询 A：参数类型匹配，走 Seek DECLARE @no VARCHAR(20) = 'ORD00000001'; SELECT * FROM dbo.Orders WHERE OrderNo = @no; -- 查询 B：参数类型不匹配，走 Scan DECLARE @no2 NVARCHAR(20) = N'ORD00000001'; SELECT * FROM dbo.Orders WHERE OrderNo = @no2;

查询 B 里VARCHAR列被提升为NVARCHAR，索引无法用于查找。看计划里会出现CONVERT_IMPLICIT警告。解决办法是让参数类型和列类型严格一致，或者用CAST显式转换参数而不是列。

3.2 统计信息过期或采样率过低

统计信息是优化器估算行数的唯一依据。当表数据大量变更后没更新统计，直方图还停留在旧分布，估算就会离谱。

-- 查看统计信息的最后更新时间和采样行数 SELECT s.name AS stat_name, sp.last_updated, sp.rows, sp.rows_sampled, sp.steps FROM sys.stats s CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp WHERE s.object_id = OBJECT_ID('dbo.Orders');

逻辑说明：sys.dm_db_stats_properties返回统计的元数据。参数说明：rows_sampled / rows是采样率，低于 30% 时估算容易偏；last_updated距今太久就要考虑更新。手动更新用：

UPDATE STATISTICS dbo.Orders IX_Orders_OrderNo WITH FULLSCAN;

FULLSCAN全表采样最准但最慢，大表上建议放在低峰期。日常可以依赖自动更新，但自动更新的触发阈值是20% + 500 行，大表上往往触发太晚，这就是为什么很多大表统计长期失真。

3.3 参数嗅探：第一次编译的参数不代表大多数

存储过程第一次执行时，优化器用传入的参数值生成计划并缓存。如果第一次传的是返回 50 万行的值，计划就按大结果集优化成 Scan，后续传小结果集也复用这个计划。

CREATE PROCEDURE dbo.GetOrdersByCustomer @CustomerId INT AS BEGIN SELECT OrderId, OrderNo, CreatedAt FROM dbo.Orders WHERE CustomerId = @CustomerId; END;

逻辑说明：CustomerId上有分布倾斜，某些客户几万单，某些客户几单。参数说明：先执行EXEC dbo.GetOrdersByCustomer @CustomerId = 1;（假设是大客户），再执行@CustomerId = 4999（小客户），第二次大概率复用 Scan 计划。验证方法是查计划缓存：

SELECT qs.execution_count, qs.total_logical_reads, qp.query_plan FROM sys.dm_exec_procedure_stats ps CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) qp JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = ps.plan_handle WHERE ps.object_id = OBJECT_ID('dbo.GetOrdersByCustomer');

3.4 索引列顺序与查询谓词不匹配

复合索引(A, B)能支持WHERE A = ?和WHERE A = ? AND B = ?，但不支持单独WHERE B = ?的查找。如果查询只过滤 B，优化器只能扫整个索引或走其他路径。

CREATE NONCLUSTERED INDEX IX_Orders_Customer_Created ON dbo.Orders(CustomerId, CreatedAt); -- 能 Seek：前导列命中 SELECT * FROM dbo.Orders WHERE CustomerId = 100; -- 只能 Scan：跳过前导列 SELECT * FROM dbo.Orders WHERE CreatedAt > '2024-01-01';

逻辑说明：第二条查询缺少CustomerId谓词，索引前导列无法定位，只能全索引扫描。参数说明：如果业务里单独按CreatedAt过滤很频繁，就要单独建(CreatedAt)索引，或者调整复合索引顺序，但调整顺序会影响原有查询，需要权衡。

4. 把扫描改回查找的排查与修复流程

4.1 一套可复用的定位顺序

遇到 Seek 变 Scan，我一般按这个顺序查，避免瞎猜：

1. 看实际执行计划，确认是 Scan 还是 Seek，记录估算行数和实际行数。
2. 检查谓词列和参数的数据类型是否一致，找CONVERT_IMPLICIT。
3. 查统计信息的更新时间和采样率。
4. 如果是存储过程，检查是否存在参数嗅探，对比不同参数的计划。
5. 检查索引列顺序是否匹配查询谓词。
6. 检查是否有函数包裹列、LIKE '%xx'、OR跨列等导致不可 SARGable 的写法。

4.2 针对隐式转换的修复

最彻底的办法是统一类型。如果改不了表结构，就在参数侧显式转换：

-- 不推荐：列被转换 SELECT * FROM dbo.Orders WHERE OrderNo = @nvarcharParam; -- 推荐：参数被转换，列保持原样 SELECT * FROM dbo.Orders WHERE OrderNo = CAST(@nvarcharParam AS VARCHAR(20));

逻辑说明：把转换放在参数侧，列保持VARCHAR，索引仍可用于 Seek。参数说明：CAST的目标类型必须和列定义完全一致，包括长度，否则仍可能触发转换。

4.3 针对参数嗅探的三种处理

第一种，在存储过程里用OPTION (RECOMPILE)，每次执行重新编译，代价是编译开销：

SELECT OrderId, OrderNo FROM dbo.Orders WHERE CustomerId = @CustomerId OPTION (RECOMPILE);

第二种，用局部变量屏蔽参数嗅探，但会失去参数值的选择性信息，估算变成平均值：

DECLARE @LocalId INT = @CustomerId; SELECT OrderId, OrderNo FROM dbo.Orders WHERE CustomerId = @LocalId;

第三种，SQL Server 2016 及以上可以用OPTION (OPTIMIZE FOR UNKNOWN)，效果类似局部变量但更明确。选择哪种取决于查询频率和分布倾斜程度：高频且倾斜严重用 RECOMPILE，低频用局部变量。

4.4 用查询存储对比修复前后

SQL Server 2016 起自带 Query Store，是验证修复效果的好工具：

-- 开启查询存储（如果未开） ALTER DATABASE YourDb SET QUERY_STORE = ON; ALTER DATABASE YourDb SET QUERY_STORE (OPERATION_MODE = READ_WRITE); -- 查某条语句的计划变化 SELECT q.query_id, p.plan_id, p.avg_duration, p.avg_logical_io_reads, p.count_executions FROM sys.query_store_query q JOIN sys.query_store_plan p ON p.query_id = q.query_id WHERE q.query_text_id = ( SELECT query_text_id FROM sys.query_store_query_text WHERE query_sql_text LIKE '%GetOrdersByCustomer%' ) ORDER BY p.avg_logical_io_reads DESC;

逻辑说明：Query Store 记录每个计划的平均耗时和逻辑读，修复后对比avg_logical_io_reads是否下降。参数说明：OPERATION_MODE = READ_WRITE才会采集，只读模式不记录新数据。

5. 避坑：索引扫描排查里最容易踩的五个坑

5.1 看到 Scan 就强制加 WITH (INDEX=...)

现象：有人一看到扫描就加索引提示强制走某个索引，结果查询更快了，但过段时间其他查询变慢。原因：强制索引绕过了优化器的代价评估，当数据分布变化后，被强制的索引可能不再是最优路径。解决：先找根因（类型、统计、参数），根因修不掉再考虑提示，并且要在 Query Store 里持续观察。

5.2 把 UPDATE STATISTICS 当成万能药

现象：每次慢就更新统计，短期有效，几天后又慢。原因：如果根因是隐式转换或参数嗅探，统计更新只是碰巧改变了估算，没解决本质问题。解决：更新统计前先确认估算偏差是不是统计导致的，用DBCC SHOW_STATISTICS看直方图是否真的失真。

5.3 忽略索引碎片但盲目重建

现象：听说碎片高会导致扫描变慢，于是每天重建所有索引。原因：碎片主要影响扫描的顺序 IO 效率，对 Seek 影响有限；而且小表重建毫无收益，反而占用大量日志。解决：只对碎片率超过 30% 且页数超过 1000 的索引重建，其余用REORGANIZE。

SELECT OBJECT_NAME(ips.object_id) AS table_name, i.name AS index_name, ips.avg_fragmentation_in_percent, ips.page_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id WHERE ips.avg_fragmentation_in_percent > 30 AND ips.page_count > 1000;

5.4 在列上套函数还指望走索引

现象：WHERE YEAR(CreatedAt) = 2024走扫描。原因：列被函数包裹后不再 SARGable，优化器无法用索引定位。解决：改写成范围查询WHERE CreatedAt >= '2024-01-01' AND CreatedAt < '2025-01-01'，或者建计算列加索引。

5.5 用 SELECT * 放大扫描代价

现象：明明只需要两列，却因为SELECT *导致回表或键查找，优化器估算代价上升后选了扫描。原因：返回列越多，非覆盖索引的键查找成本越高，超过阈值就翻成扫描。解决：只查需要的列，或者把常用列加进索引的 INCLUDE 里做成覆盖索引。

6. 进阶：用覆盖索引和计划基线把查找稳定住

前面讲的都是“救火”，真正让线上稳定的做法是提前设计。我一般会做两件事：把高频查询做成覆盖索引，再用 Query Store 的计划强制把好计划钉住。

覆盖索引的思路是把查询用到的所有列都放进索引，要么作为键列，要么作为 INCLUDE 列，这样优化器不需要回表，Seek 的代价大幅降低，自然更愿意选 Seek：

-- 针对按客户查订单的查询做覆盖索引 CREATE NONCLUSTERED INDEX IX_Orders_Customer_Cover ON dbo.Orders(CustomerId) INCLUDE (OrderNo, CreatedAt);

逻辑说明：CustomerId作为键列支持 Seek，OrderNo和CreatedAt放进 INCLUDE 避免键查找。参数说明：INCLUDE 列不参与排序，不增加 B 树层级，但会增加索引页大小，列太多会拖慢写入，一般控制在 5 列以内。

计划强制是最后一道保险。当某个查询确认了最优计划，但优化器偶尔抽风选错时，可以用sp_query_store_force_plan钉住：

-- 先找到 query_id 和要强制的好 plan_id SELECT q.query_id, p.plan_id, p.avg_logical_io_reads, p.is_forced_plan FROM sys.query_store_query q JOIN sys.query_store_plan p ON p.query_id = q.query_id WHERE q.object_id = OBJECT_ID('dbo.GetOrdersByCustomer'); -- 强制好计划 EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 7;

逻辑说明：强制后优化器每次都用指定计划，跳过代价评估。参数说明：@query_id和@plan_id从上一条查询结果取。注意强制不是一劳永逸，数据分布发生根本变化时要解除强制重新评估，否则会变成新的性能债。

验证修复效果不能只看一次执行。我的习惯是修复后连续观察 Query Store 三天，确认avg_logical_io_reads和avg_duration稳定在低位，再撤掉临时监控。索引查找变扫描这件事，根因往往不在索引本身，而在类型、统计和参数这三处。把这三处的检查做成脚本固化下来，比每次出事再翻计划靠谱得多。希望帮到你。

本文还有配套的精品资源，点击获取