SQL Server日期范围查询复合索引字段顺序设计探讨

结论先行：你同事提出的[CurrencyId]-[PurchaseDate]索引结构性能更优，你之前秉持的「区分度更高的字段放在复合索引前列」的结论存在适用边界，没有考虑查询谓词的类型差异，在这个场景下不成立。

复合索引字段排序的核心优先级 #

复合索引是按从左到右的字段顺序构建多层有序B+树结构，字段排序的判断优先级从来不是单纯看区分度，而是按以下规则从高到低判断：

• 第一优先级：等值匹配的字段（=、IN匹配）放前面，范围匹配的字段（>=、<=、BETWEEN、前缀模糊匹配）放后面
• 第二优先级：同是等值匹配的字段，区分度高的放前面
• 第三优先级：区分度接近的情况下，按字段长度从小到大排，减少索引体积

核心原因是：B+树的有序查找在遇到第一个范围匹配条件后，后续字段的有序性就无法继续用于二分定位，只能对范围命中的结果集做逐行扫描过滤，会产生大量无效IO。

两种索引方案的实际执行效果对比 #

结合你给出的业务数据：表总记录200万，当前2种货币（后续新增到4种），单种货币对应50万_{100万条记录；查询条件为5个月的日期范围，按业务量级估算该时间段约有12万}18万条采购记录。

你的方案：[PurchaseDate]-[CurrencyId] #

索引先按采购日期排序，同日期下再按货币ID排序，执行逻辑如下：

1. 首先通过二分查找定位到2022-01-01的索引起始位置，连续扫描到2022-06-01的索引结束位置，这一步会把该时间段内所有货币的共12万~18万条索引条目全部加载
2. 再在加载的索引条目中逐行过滤CurrencyId=1的记录，最终仅命中6万~9万条（2种货币场景），近一半的索引扫描是无效操作。后续货币新增到4种时，无效扫描占比会升到75%。

同事的方案：[CurrencyId]-[PurchaseDate] #

索引先按货币ID排序，同货币下再按采购日期排序，执行逻辑如下：

1. 首先通过二分查找直接定位到CurrencyId=1的索引分段，直接过滤掉其他所有货币的记录，仅保留对应货币的50万~100万条索引分段
2. 再在该分段内通过二分查找直接定位2022-01-01到2022-06-01的连续索引区间，直接命中目标记录，全程无多余扫描。
   哪怕后续货币种类增加到10种以上，这个结构的效率依然高于把日期范围放前面的方案。

认知误区纠正 #

「高区分度字段放复合索引前列」的规则，仅在所有查询字段都是等值匹配的场景下生效。比如查询条件为WHERE CurrencyId = ? AND PurchaseDate = ?时，把区分度更高的PurchaseDate放前面确实能减少扫描量；但只要存在范围查询条件，范围字段后置的规则优先级远高于区分度规则，这是很多开发者设计索引时容易踩的坑。

额外优化建议 #

当前你的查询写的是SELECT *，如果业务不需要取表中所有字段，可以把需要查询的字段添加为索引的包含列，构建覆盖索引避免回表查询，性能还能得到进一步提升。

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