SQL Server IdMappings表索引及复合键优化问题咨询

问题1解答 #

• 关于Processed字段的判断：你当前的结论符合你的场景，但不是普适性结论。Processed为bit类型仅2种取值，单独建立索引的筛选效率极低，大部分场景下确实没必要。只有当你需要筛选的取值（比如Processed=0）占全表比例低于5%时，单独建索引才可能有正向收益，你的批量处理逻辑是按Id范围分片扫描，每次仅处理5000行，单独给Processed建索引收益可以忽略。
• 关于RecordType字段：15种取值是否值得建索引，完全取决于你的查询模式，不存在固定的distinct值数量阈值。判断索引是否有效的核心标准是：该字段的过滤条件能不能帮你跳过足够多不需要扫描的行。你当前的查询有RecordType = @someRecordType的等值过滤，只要你每次处理的单一RecordType对应的行数占全表比例低于20%，建索引就能带来明显收益，结合你几乎没有后续写入的背景，哪怕收益不高也完全可以建，没有维护成本。
• 关于索引建立的阈值：行业内没有固定的不同取值数量阈值，核心看过滤后的行占全表的比例，通常如果过滤后剩余行占全表10%以下，索引会有明确的正向收益；如果超过30%，数据库大概率会选择直接扫描全表，索引反而会成为负担。

问题2解答 #

复合索引的字段不管出现在WHERE子句还是JOIN的ON子句，只要符合最左前缀匹配规则，就会有性能优势。
你给出的ix_IdMappings_RecordType_OldId索引是可以带来查询收益的：

1. 索引第一列是RecordType，刚好匹配你WHERE里的等值过滤条件，可以直接跳过其他14种RecordType对应的所有行，把需要扫描的数据量缩减到原来的1/15左右。
2. 索引第二列是OldId，刚好是你JOIN关联的字段，数据库不需要回表查询聚集索引就能拿到OldId的值做关联匹配，减少了回表IO消耗。

针对你的场景的最优索引建议 #

因为你全量写入后几乎没有更新操作，完全可以建立覆盖索引来彻底避免回表，性能提升最明显，推荐索引定义如下：

CREATE INDEX ix_IdMappings_BatchProcess ON IdMappings (RecordType, Id, Processed)
INCLUDE (OldId, NewId);

这个索引完全覆盖了你批量查询和更新的所有字段需求：

• 前导列RecordType等值过滤，快速定位目标类型
• 第二列Id有序，刚好匹配你BETWEEN的范围扫描逻辑
• 第三列Processed直接过滤未处理的行
• INCLUDE的OldId用来做JOIN关联，NewId直接返回查询结果，全程不需要访问聚集索引，IO消耗最低。

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