SQL Server不同查询场景下索引选择切换逻辑咨询

这事儿得从SQL Server查询优化器的核心逻辑——成本估算说起，它选索引的本质是找「执行成本最低」的方案，咱们结合你的场景一步步拆解：

先明确两个非聚集索引的结构 #

先把两个索引的列顺序列出来，方便对比：

• Provider0：fedid ASC → provid ASC → entityid ASC → fullname ASC → status ASC
• XIE3Provider：fedid ASC → entityid ASC → provtype ASC → fullname ASC → provid ASC

两个索引都以fedid作为首列，所以对WHERE FedId = '123'的过滤条件，都能触发索引查找（而非效率更低的索引扫描/表扫描），这是两者都能成为候选的基础。

为什么查询1选XIE3Provider？ #

查询1的语句是：

SELECT provid FROM provider WHERE FedId = '123'

它只需要返回provid列，咱们看两个索引的覆盖能力和成本：

1. 覆盖性验证：两个索引都显式包含provid列，而且因为provid是聚集索引主键，哪怕索引里没显式加，非聚集索引的叶节点也会自动包含聚集索引键作为行定位器——所以两个索引都是覆盖索引，都不需要回表查原表。
2. 成本差异核心：索引大小：优化器会对比两个索引的物理大小。假设status列的宽度比provtype宽（比如status是varchar(50)，provtype是char(2)），那Provider0的单索引行宽度会更大，存储相同行数需要的索引页更多，IO成本更高。而XIE3Provider的行更紧凑，IO成本更低，所以优化器会选它。

为什么查询2切换到Provider0？ #

查询2的语句是：

SELECT provid, status FROM provider WHERE FedId = '123'

这次需要返回provid和status两个列，此时两个索引的覆盖能力出现了本质差异：

1. Provider0是覆盖索引：它的列里直接包含status，用这个索引查找出符合FedId='123'的行后，直接就能从索引叶节点里拿到status，不需要额外操作。
2. XIE3Provider需要回表：它的列里没有status，如果用它的话，找到符合条件的行后，必须通过provid（聚集索引键）做一次键查找（书签查找）去原表取status，这会额外增加IO和CPU成本。

优化器估算后发现，用Provider0的覆盖索引方案，整体成本远低于XIE3Provider加回表的方案，所以就切换了索引选择。

补充几个关键前提 #

• 优化器的所有决策都基于统计信息（比如索引的行数、列的分布、页面数量等），如果统计信息过时，可能会出现不符合预期的索引选择。
• 非聚集索引的行定位器默认是聚集索引键，所以即使索引里没显式包含主键，也能通过行定位器拿到主键值，但显式包含会避免一些潜在的额外开销。

内容的提问来源于stack exchange，提问作者vellaichamy