SQL Server索引优化实战：设计原则、失效场景与维护

做后端开发和数据库运维这些年我越来越觉得索引是数据库里最容易被低估、也最容易被高估的东西。低估的人建表之后从不加索引等线上一条慢查询把数据库拖死才开始慌高估的人遇到慢查询第一反应就是加索引结果索引比数据还多写入慢得离谱还不自知。SQL Server 的索引这个话题我在好几个项目里踩过深浅不一的坑今天想把经验梳理成一篇能直接用的笔记。这篇文章我会从索引的基础原理讲起重点放在聚集索引与非聚集索引的取舍、主键索引与唯一索引的区别、如何设计一个真正能落地的索引以及那些让索引失效的经典 SQL 场景。还会分享用 DMV 做日常索引维护的方法。无论你是刚开始接触 SQL Server 的新手还是已经被线上慢查询折磨过几轮的同学应该都能从这里找到自己需要的答案。1. 索引的本质先搞清楚它到底在干什么1.1 索引解决的问题和它背后的代价很多刚接触数据库的朋友会把索引理解成一本书的目录这个类比大体上对但不够准确。索引其实是一个独立的、额外的存储结构它按照你指定的列排序并保存着指向真实数据行的指针。查询时如果优化器决定走索引就可以沿着索引树快速定位到数据所在的页而不是从第一页开始把整张表从头翻到尾。我在实际工作中碰到过太多这样的例子一张几千万行的订单表没有任何索引的时候一条简单的 where 查询跑好几秒甚至十几秒建了合适的索引之后压到几十毫秒。这种体验上的差距很容易让人误以为索引是万能的于是开始拼命加索引。但索引真不是免费的午餐。每一次 insert、update、delete除了维护表数据本身数据库还要同步维护索引结构索引越多写入的代价就越大事务日志也会涨得飞快。这个矛盾决定了索引设计本质上是一个权衡问题。你需要在查询响应速度和写入吞吐量之间找平衡点而不是无脑堆索引。在我的团队里有一条不成文的规矩任何加索引的操作都要先回答三个问题——这张表的核心查询是什么这个索引能否覆盖最贵的几个查询加了它之后对现有写入链路的影响有多大想不清楚这三个答案索引就先不要加。1.2 聚集索引与非聚集索引选对才是关键我记得面试的时候经常有人把聚集索引和非聚集索引的区别背成一个只能建一个另一个可以建很多个但背后的原理很多人说不清楚。聚集索引的特殊之处在于它决定了表数据的物理存储顺序。SQL Server 的存储单位是页一张表的数据页会按照聚集索引键的顺序串成一个链表结构。你把一条新记录插进去SQL Server 会把这个记录放到它按聚集键排序后该待的位置上。正因为物理数据只有一份所以一张表只能有一个聚集索引。非聚集索引则完全不是一回事它相当于在另一个地方单独放了一份排序好的键值列表叶子节点保存的是指向真实数据行的定位符。在 SQL Server 里这个定位符分两种情况如果表上有聚集索引非聚集索引的叶子节点存储的是聚集索引键值如果表没有聚集索引也就是堆表叶子节点存储的是行标识符也就是 RID。这里有一个非常关键的性能点非聚集索引查到指针之后还要根据指针再去数据页里取一次数据这个过程叫回表。在 SQL Server 的图形化执行计划里你会看到 Bookmark Lookup 或 RID Lookup 操作一旦出现这个就意味着查询有额外的随机 IO。如果查询需要的所有列都已经包含在非聚集索引里了回表就可以省掉这种索引叫覆盖索引。覆盖索引对于高并发查询的性能提升是立竿见影的后面我会专门讲怎么用包含列来实现。还要提醒一个经常被忽略的事实聚集索引键最好不要用随机性太高的列。比如用 newid() 生成的 GUID 做主键数据插入的位置是随机的页分裂会非常频繁、碎片率蹭蹭往上涨。比较理想的是自增列或者顺序递增的列插入永远发生在末尾碎片少写入效率高。如果因为业务原因只能用 GUID至少要给聚集键换个顺序性强一点的列哪怕多建一个非聚集唯一索引来保护主键唯一性也比存储性能烂掉要好。2. 主键索引与唯一索引别再傻傻分不清2.1 主键索引在 SQL Server 里的特殊地位主键约束在 SQL Server 里默认会创建一个唯一索引而且建表时如果没有特别指定这个唯一索引默认就是聚集索引。这里有个先后关系很多人弄反了应该是创建 PRIMARY KEY 约束时如果表上还没有聚集索引SQL Server 默认生成聚集的唯一索引如果表上已经有聚集索引了那主键约束就生成非聚集的唯一索引。主键还有一个硬性规则不允许 NULL。这一点和唯一索引不同唯一索引虽然也不接受重复值但它是允许 NULL 的。当然在 SQL Server 里唯一索引对 NULL 的处理跟 MySQL 不太一样后面我会说。这个差异在实践中意味着如果你需要一个可空的唯一列比如某些用户确实没有填写身份证号那你就不能用主键来实现只能建一个唯一非聚集索引并且要想清楚 NULL 只允许出现一次的这个坑。另外主键作为聚集索引还有一个隐藏优点因为它是表的物理存储顺序按主键范围查询时连续 IO 的效率极高。最常见的例子就是分页查询按 id 排序走主键索引比走其它任何索引都稳。但反过来如果你把主键设计成一个没有业务意义的自增 id那按业务条件带主键查询的场景其实是很少的主键索引更多的时候是充当其它非聚集索引的行指针。2.2 唯一索引约束和提速的一体两面唯一索引的价值不只在于提升查询速度它更重要的角色是保证数据在逻辑上的唯一。业务上常见的场景包括订单号唯一、手机号唯一、身份证号唯一、用户在某次活动里只能参与一次。这个约束能力是很多数据一致性方案的兜底不能省。创建唯一非聚集索引时有一个必须知道的细节在 SQL Server 里NULL 会被当作一个普通值来对待所以唯一索引的列中只能存一个 NULL第二个就会被当作重复值拒绝。这跟 MySQL 的行为完全不一样MySQL 允许列里有任意多个 NULL。跨数据库迁移或者设计新表的时候这个差异特别容易踩坑。另一个常见误区是把唯一索引当成普通的加速索引来用。确实唯一索引因为结构上不允许重复查询性能通常不错但它的约束能力是附带的你不能反过来依赖它加速一张已经有大量重复数据的表。给一张存量数据有重复的表加唯一索引创建会直接失败报错信息里会告诉你找到重复的键。所以在建唯一索引之前最好先跑一条 group by ... having count(*) 1 的语句确认目标列有没有重复别等到创建索引的语句执行了十几分钟然后一把梭失败线上窗口又被浪费。2.3 一张表看清主键索引和唯一索引的差异对比项主键索引唯一索引每张表数量最多一个可以多个是否允许 NULL不允许允许SQL Server 中只允许一个 NULL默认类型通常为聚集索引也可指定非聚集默认非聚集也可建为聚集核心作用标识每一行 物理排序保证唯一性 加速查询兼容业务场景每行必须有唯一标识业务上要求列值不能重复但可能缺失创建失败原因少见存量重复数据会导致失败这个表我在培训新人的时候总会贴一遍。很多人觉得主键索引和唯一索引长相差不多实际上它们的语义完全不同。主键是表的核心身份标识建议永远存在唯一索引是业务约束的一种手段按需使用。把它们混在一起谈会在很多具体设计决策上犯糊涂。3. 索引设计实操从需求到 CREATE INDEX3.1 三条核心原则帮你想清楚建什么索引第一条原则高选择性的列放前面。选择性是指这一列不同值的比例性别列只有两个值区分度极低拿这种列做非聚集索引的前导列索引树可能很快就扫到大量重复值还不如直接扫表。订单号、用户 ID、手机号这类列区分度极高适合放在复合索引的最前面。第二条原则让索引的前导列匹配最常用的过滤条件。一个复合索引不管建了多少列优化器通常只会利用前导列做精准定位后面的列更多用于减少回表次数。所以 where 条件里最常出现的列尽量放在复合索引的第一位。如果业务里同时有 where a1 和 where a1 and b2 两种查询一个 (a, b) 的复合索引可以同时覆盖两种场景因为 a 作为前缀可以被独立使用。第三条原则尽量做成覆盖索引。查询里 select 出来的列如果能全部塞进索引的键列和包含列里查询就不需要回表这个收益在高并发场景下极为可观。一个典型的例子是报表查询只关注订单号和状态那 (status, create_time) 作为键列、order_no 作为包含列查询效率会非常稳。这三原则是我实际项目里反复总结出来的不算什么高深理论但能挡住 80% 的无效索引设计。设计索引之前先把你最关心的五条慢 SQL 拿出来逐条分析它的 where、order by、select 三部分再决定索引长什么样。千万不要没有根据地在每个列上都挂一个单列索引那种做法不仅浪费空间还让优化器陷入多索引组合的纠结里。3.2 用包含列和过滤索引解决回表和大索引问题包含列是我最喜欢的 SQL Server 索引特性之一。它的语法很简单在加号 INCLUDE 里把那些「查询要输出、但不需要参与排序和过滤」的列放进去。这些列不会出现在索引键里不会影响索引的排序结构但会被存到索引叶子节点中。这样一来查询就能拿到完整的数据不需要回表了。举个例子订单表经常要按状态查订单列表查询语句是 select status, order_no, amount from orders where status PAID order by create_time desc。如果建一个覆盖索引 create_time desc 加上包含列 status、order_no、amount这个查询几乎可以直接从非聚集索引里把所有结果读出来。注意 status 虽然在 where 里但它区分度不高没必要放在键列第一位放包含列里完全够用而且还能让键列结构更紧凑。过滤索引则是另一个减少冗余的利器。很多业务表的删除标记或者状态列绝大多数行都是同一个值比如 orders 表里 90% 的行 statusDONE 是历史归档线上查询永远只看 statusNEW。这个时候直接建一个带 where status NEW 的过滤索引索引体积会缩小到原来的十分之一写入维护成本也大幅下降查询走这个索引定位到的数据量也少。过滤索引对磁盘空间紧张、写入压力大的表价值极高。3.3 一条真实业务的索引创建 SQL 拆解我拿一个真实项目里的场景来演示。一张支付流水表 pay_record日增几十万行查询主要两类按用户查最近流水、按时段查订单汇总。刚开始这张表在主键聚集之外只有一两个单列索引查询经常超时。后来我们改成这样设计CREATE NONCLUSTERED INDEX IX_PayRecord_User_Time ON dbo.PayRecord (UserID, PayTime DESC) INCLUDE (OrderNo, Amount, Status) WITH (ONLINE ON, FILLFACTOR 90);这个索引的前导列是 UserID高选择性直接命中用户查询键列第二列是 PayTime支持排序INCLUDE 里的字段保证 select 出来的列都在索引里不用回表。FILLFACTOR 设为 90意思是让索引页预留 10% 的空间给后续插入避免页分裂这对高频插入的流水表很重要。再配一个汇总查询的索引CREATE NONCLUSTERED INDEX IX_PayRecord_Time_Status ON dbo.PayRecord (PayTime, Status) INCLUDE (Amount) WHERE Status IN (SUCCESS, FAILED);注意这里用了过滤索引只把线上关心的状态放到索引里历史状态不进索引体积小、效率高。两个索引建完之后两条核心查询的执行计划里都看不到 Key Lookup查询耗时从几秒降到了百毫秒以内。建索引的时候我还遇到过 ONLINEON 不生效的情况。联机重建索引不是所有版本都支持开发版和企业版没问题Standard 版是从 SQL Server 2016 SP1 开始才支持联机索引操作的。如果版本不支持重建索引期间会锁表对生产环境影响很大脚本里最好加上版本判断或者选在维护窗口执行。4. 索引失效的经典场景与优化改写4.1 对索引列做函数运算字符串转数字的典型陷阱搜索词里有人提到 sqlserver 字符串转数字这个场景和索引失效的关系非常密切。最常见的是日期列被转成字符串以后再去比较-- 这种写法会让 create_time 上的索引失效 SELECT * FROM orders WHERE CONVERT(varchar(20), create_time, 120) 2024-01-15;这条 SQL 的问题是为了比较数据库必须对每一行的 create_time 都执行一次 CONVERT然后再做匹配。索引树里存的是原始时间类型不是转换后的字符串所以索引没法直接用来定位。更麻烦的是这个例子中索引失效后表里所有行都要做一次转换CPU 压力直接上来。正确的做法是反过来把查询条件改造成范围谓词SELECT * FROM orders WHERE create_time 2024-01-15 00:00:00 AND create_time 2024-01-16 00:00:00;这样 create_time 列本身不被函数包裹索引可以正常走而且结果和上面的写法完全一致。数字列转字符串也一样比如把订单号存成了 varchar却用 where order_no 10086 去查SQL Server 会隐式把 order_no 转换成数字再比较索引失效。正确写法是 where order_no 10086让参数类型和列类型一致。判断一条 SQL 有没有对索引列做函数运算最简单的方法就是看执行计划里有没有 Index Scan。Index Scan 意味着把索引整棵扫一遍和全表扫描的差别只是扫的是索引还是表本质上都没有充分利用索引的查找能力。真正想要的是 Index Seek那才是沿着 B 树快速定位。4.2 隐式类型转换SQL Server 里 varchar 和 int 的较量隐式转换是一个特别容易忽略的坑因为 SQL 写起来完全正常语法上也不会报错但性能就是上不去。SQL Server 在比较两个不同类型的数据时会根据数据类型优先级做隐式转换。数值类型的优先级比字符串高所以当 varchar 列和 int 参数比较的时候数据库会优先把列转成 int而不是把参数转成字符串。这个转换一发生索引就废了。比如手机号、身份证号这类因为长度、前导零而必须用 varchar 存储的列查询时如果直接传数字参数执行计划里几乎必然出现 CONVERT_IMPLICIT 操作列上的索引完全派不上用场。这也是为什么我经常跟团队强调写 SQL 时参数类型要和列类型严格一致不要偷懒。检查隐式转换的另一个实用技巧是看执行计划的属性面板选择那个 Scan 操作符在谓词一栏里能看到类似 CONVERT_IMPLICIT(orders.phone, int) 的字样。一旦看到这种提示就要回头改 SQL 了。生产环境的慢查询里很大一部分问题不在索引而在这一行 SQL 的参数传递方式上把参数包装成正确的类型索引可能立刻就从失效变成生效。4.3 LIKE、OR、不等号那些容易翻车的查询写法LIKE 关键字对索引的态度非常挑剔。like ABC% 是可以走索引的因为前缀是固定的索引树可以按前缀定位但 like %ABC 这种前置通配符数据库无法利用索引的有序性快速定位只能把所有键值拿出来逐个比对。业务上如果确实需要查后缀匹配一种方案是新建一列专门存倒序后的文本并为它建索引查询时把参数也倒过来匹配代价是额外空间和写入开销但查询确实能跑快。OR 条件也有类似的问题。一个 where 条件里只有部分列有索引SQL Server 优化器通常不会对其中一个分支走索引、另一个分支扫表而是倾向于生成一个全表扫描的简单计划。解决办法是把 OR 拆成两个查询用 UNION ALL 合并让每个分支各自走自己的索引。这样做前提是业务上确实需要或的逻辑本质上是把复杂的谓词拆成优化器更容易处理的简单谓词。不等于、NOT IN、NOT LIKE 这些否定类条件通常索引选择性都较差因为数据库无法直接从索引树里定位不等于某值的区间。遇到这类查询先确认业务是否真的需要这种写法有时候把否定改成范围条件会更高效比如 a 0 改成 a 0 or a 0在优化器眼里可能就是完全不同的计划。4.4 参数嗅探索引明明存在却不走的隐性问题有些时候索引就在那儿统计信息也是新的但查询就是不走索引这很可能是参数嗅探Parameter Sniffing在作怪。SQL Server 第一次编译存储过程时会根据当时传入的参数生成执行计划并缓存。如果这个参数碰巧是一个低选择性的值优化器可能选择全表扫描。后续调用存储过程时哪怕传入的参数选择性很高也会继续使用缓存里的旧计划。我在一个报表系统里遇到过这种问题同样的存储过程输入某天的日期参数很快输入另一天的日期参数就慢到超时。排查后发现首次执行时输入的日期范围覆盖了大量数据优化器判断扫表更快于是缓存了扫表计划后面所有会话都被这个计划拖累。解决参数嗅探有好几种手段。最简单的做法是在存储过程里加 WITH RECOMPILE让每次执行都重新编译代价是略微增加编译开销也可以给关键语句加 OPTION (RECOMPILE)只让这一条语句重新编译还可以用 OPTION (OPTIMIZE FOR UNKNOWN)让优化器依赖统计信息而不是具体参数值做决策。我自己的经验是数据分布均匀且查询模式固定的场景用 OPTION (OPTIMIZE FOR UNKNOWN) 最省心参数范围跳变很大的场景直接 OPTION (RECOMPILE) 更干脆毕竟编译一次的开销远小于一次全表扫描。5. 索引日常维护与性能排查5.1 碎片率怎么看什么时候重建什么时候重组索引用久了碎片几乎是不可避免的。碎片是指索引页上物理存储顺序和逻辑顺序不一致以及页内空间利用率下降。这会导致查找时要读更多的页、IO 次数上升。我见过很多表数据量明明不大但因为频繁更新和删除碎片率高达百分之五六十查询慢得离谱。查看碎片率用系统函数 sys.dm_db_index_physical_stats一条典型的查询长这样SELECT OBJECT_NAME(ips.object_id) AS TableName, i.name AS IndexName, ips.avg_fragmentation_in_percent, ips.avg_page_space_used_in_percent, ips.page_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, LIMITED) ips JOIN sys.indexes i ON ips.object_id i.object_id AND ips.index_id i.index_id WHERE ips.page_count 128 ORDER BY ips.avg_fragmentation_in_percent DESC;这里限制了页数大于 128因为太小的索引碎片影响有限不值得投入维护时间。碎片率在 5% 到 30% 之间一般用 ALTER INDEX ... REORGANIZE 做碎片重组超过 30%则需要 REBUILD 重建索引。重建是会锁表和占用大量 IO 的操作生产环境要考虑维护窗口以及能否开启 ONLINE 选项。页分裂是碎片产生的主要来源前面提到的 FILLFACTOR 就是预防手段。如果一个索引的写入模式是高频随机插入建议把填充因子调低一些留出余地如果是只读报表表直接用 100 也没问题。这个参数的平衡需要在实践中慢慢调没有一劳永逸的值。5.2 统计信息过期会让执行计划跑偏统计信息是优化器做决策的依据它记录着索引列的数据分布情况。如果统计信息过期优化器对行数的估算可能会偏差极大明明该走索引的查询走了全表扫描明明 return 20 行的查询被估算成返回 2 万行。我在优化线上查询时第一步不是看索引而是确认统计信息的新鲜度。统计信息的自动更新阈值在 SQL Server 里有一套机制行数变化达到一定比例时会自动触发更新SQL Server 2016 之后引入了自适应阈值对大数据量表更友好。但自动更新不是万能的批量导入大量数据、数据分布发生剧烈变化时统计信息经常来不及更新。手动更新统计信息的命令很简单UPDATE STATISTICS 表名 索引名。也可以在数据库属性里勾选自动更新统计信息和自动创建统计信息平时基本够用。我个人的习惯是大表批量任务跑完之后在后续的维护窗口里手动更新一次核心表统计信息再观察执行计划的变化。不要小看这一步很多莫名其妙的性能反弹最后都查到统计信息头上。5.3 用 DMV 快速定位缺失索引和无用索引SQL Server 提供了一组非常实用的动态管理视图能自动统计哪些查询因为缺少索引而走了扫描。核心的是 sys.dm_db_missing_index_details 和 sys.dm_db_missing_index_group_stats前者告诉你建议的索引列组合后者告诉你如果加上这个索引能省多少 IO 和查询成本。一条常用的定位 SQL 长这样SELECT TOP 10 migs.avg_user_scans, migs.avg_user_impact, mid.statement AS TableName, CREATE INDEX IX_Missing On mid.statement ( ISNULL(mid.equality_columns, ) ISNULL(, mid.inequality_columns, ) ) Include ( ISNULL(mid.included_columns, ) ) AS SuggestedIndex FROM sys.dm_db_missing_index_group_stats migs JOIN sys.dm_db_missing_index_groups mig ON migs.group_handle mig.index_group_handle JOIN sys.dm_db_missing_index_details mid ON mig.index_handle mid.index_handle ORDER BY migs.avg_user_impact DESC;这个查询会给出系统建议的索引脚本非常方便。但要注意DMV 给的是缺失索引建议不等于应该立刻加这个索引。它会忽略查询中一些复杂谓词的影响而且不会考虑索引对写入的负面影响。正确姿势是把建议抄下来结合业务分析再插入前面提到的三条设计原则过滤一遍最后在测试环境验证执行计划再上生产。反过来判断哪些索引常年没人用可以查 sys.dm_db_index_usage_stats。这张表记录了索引的 seek、scan、lookup 次数。如果一个索引长时间 user_seeks 为零或者接近于零说明它基本没被查询利用属于冗余索引可以评估删除。我在一个项目里靠这个视图清理掉了将近三分之一的多余索引写入性能明显回升。6. 常见问题排查速查表现象可能原因处理办法加了索引查询还是慢索引列顺序不对 / 查询有回表 / 统计信息过期用执行计划确认是否 Index Seek设计覆盖索引更新统计信息索引建了但执行计划不走对列做了函数或隐式转换改造查询为范围谓词统一参数类型写入操作明显变慢索引数量过多 / FILLFACTOR 不合理 / 页分裂频繁清理无用索引调整填充因子碎片维护ORDER BY 深度分页越来越慢OFFSET 之前要排序和丢弃大量行为排序列建索引考虑键集分页前后端传游标同一条 SQL 时快时慢参数嗅探 / 统计信息过期OPTION (RECOMPILE) 或 OPTIMIZE FOR UNKNOWN更新统计信息重建索引卡住或锁表版本不支持 ONLINE 或未指定 ONLINE在维护窗口操作确认版本后开启 ONLINEONOFFSET 后再 TOP 查到不确定行SQL Server 先 OFFSET 丢弃前 N 行再取 TOP如果语句里顺序不清晰结果可能和直觉不符分页时只用 OFFSET...FETCH NEXT或把 TOP 写在子查询外并明确排序这个表里的每一个问题我都在真实项目里挨个遇到过。其中最想再提醒一句的是 OFFSET 分页的问题。SQL Server 2012 之后支持 OFFSET...FETCH NEXT语法上很漂亮但它的实现是先把前 N 行全部物化跳过再从后面取结果。页码越深跳过的行越多查询也就越慢。这就是为什么大表分页不建议用 OFFSET 一直往后翻而应该用 where id 上次最大值 order by id 的方式做键集分页。最后再分享一个我个人的操作习惯。每次接手一个新的数据库系统我做的第一件事永远不是看代码而是把 sys.dm_db_index_physical_stats、sys.dm_db_index_usage_stats、sys.dm_db_missing_index_group_stats 三张视图拉出来看一遍配合慢查询日志找出 TOP N 的 SQL。这个动作能帮我在半个小时之内建立起对系统全貌的判断哪些表现核心查询、哪些索引是垃圾、哪些查询在裸奔。维护索引这件事说到底不是建了就完而是一个持续观察、持续调整的过程。你在一个项目里的索引设计方案放到另一个项目里可能完全不适用所以掌握判断方法比记住结论重要得多。