Azure SQL Database中日期范围查询的最优索引方案咨询

我在Azure SQL Database中有如下结构的DayStatus表：

CREATE TABLE [dbo].[DayStatus](
    [Date] [DATE] NOT NULL,
    [LocationId] [INT] NOT NULL,
    [TypeId] [INT] NOT NULL,
    [Total] [DECIMAL](15, 5) NULL,
    [Timezone] [NVARCHAR](70) NULL,
    [Currency] [NVARCHAR](3) NOT NULL,

PRIMARY KEY CLUSTERED 
(
    [Date] ASC,
    [LocationId] ASC,
    [TypeId] ASC
)
) ON [PRIMARY]
GO

需要优化以下SELECT语句：

SELECT 
    [Date],
    [LocationId],
    [TypeId],
    [Total],
    [Timezone],
    [Currency]
FROM [dbo].[DayStatus]

WHERE 
Date >= '2022-06-01' and Date <= '2023-01-17'
    and Currency = 'USD'
    and LocationId in (1, 2, 3, 4, 6, 10)
    and TypeId in (1, 2, 3, 5)

我测试了两个索引，未看到明显性能差异，想咨询哪个更优，以及是否有更合适的索引方案：

测试1 #

CREATE NONCLUSTERED INDEX [IX__Test1] ON [dbo].[DayStatus]
(
    [Date] ASC,
    [Currency] ASC,
    [LocationId] ASC,
    [TypeId] ASC
)
GO

测试2 #

CREATE NONCLUSTERED INDEX [IX__Test2] ON [dbo].[DayStatus]
(
    [Currency] ASC,
    [LocationId] ASC,
    [TypeId] ASC
)
INCLUDE([Date],[Timezone],[Total])
GO

补充问题 #

调整WHERE子句条件顺序为以下形式的查询是否性能更优？

SELECT 
    [Date],
    [LocationId],
    [TypeId],
    [Total],
    [Timezone],
    [Currency]
FROM [dbo].[DayStatus]

WHERE 
    Currency = 'USD'
    and LocationId in (1, 2, 3, 4, 6, 10)
    and TypeId in (1, 2, 3, 5)
    and Date >= '2022-06-01' and Date <= '2023-01-17'

一、两个测试索引的优劣对比 #

1. 测试1索引的短板
   这个索引把Date（范围过滤列）放在了键列最前面，在Azure SQL Database里，范围过滤列一旦放在索引键前列，后面的Currency、LocationId、TypeId这些等值/IN过滤列就没法利用索引的有序性快速定位了——只能先按日期范围筛出一批数据，再逐一扫描匹配其他条件，效率很低。而且这个索引没包含Total、Timezone，查询时得回表到聚集索引拿这些字段，额外多了IO开销。

2. 测试2索引的优势
   它把Currency、LocationId、TypeId这些等值/IN过滤列放在索引键前列，数据库能直接利用索引有序性快速定位符合条件的行。再加上INCLUDE了Date、Total、Timezone，查询需要的所有字段都在这个索引里，完全不用回表，避免了键查找的额外开销。虽然Date是范围过滤，但在索引里直接过滤就行，比测试1高效得多。

二、更合适的索引方案 #

结合你的查询场景，推荐优化测试2的索引，把Date加到索引键的最后：

CREATE NONCLUSTERED INDEX [IX_DayStatus_Optimized] ON [dbo].[DayStatus]
(
    [Currency] ASC,
    [LocationId] ASC,
    [TypeId] ASC,
    [Date] ASC
)
INCLUDE([Timezone],[Total])
GO

为啥这么调整？

• 前三个列都是等值/IN过滤，放在前面能最快缩小数据范围；
• Date作为范围列放在键列末尾，数据库定位到前三个条件的行后，能直接利用Date的有序性快速筛选日期范围，比把Date放在INCLUDE里的过滤效率更高；
• INCLUDE只保留需要的Timezone、Total，让索引更紧凑，减少存储和维护成本。

三、WHERE子句顺序的影响 #

完全没影响。Azure SQL的查询优化器会自动分析所有WHERE条件，不管你写的顺序是什么，它都会根据统计信息选择最优的执行逻辑，调整书写顺序不会改变查询性能。

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