在這種情況下將使用哪個索引？

SQL Server 2014 標準版

我需要找到特定月份往返特定城市的航班數量。例如

select count(*) 
from flights 
where flightTo_AirportCode = 'aaaa' 
and flightFrom_Airportcode = 'bbbb' 
and flightdate < '2016-04-01' 
and flightdate > '2016-02-28' ;

表架構如下。

我試圖估計索引模型A 或索引模型B（如下）是否更可取（建構索引需要很多小時，並且磁碟空間一次只允許存在一個，所以我試圖在跳躍之前查看）。

根據我的經驗，任何一個索引都可以。我對嗎？

 create index [modelA] on flights (flightTo_AirportCode, flightFrom_AirportCode, flightDate)

 create index [modelB] on flights (flightDate, flightTo_AirportCode, flightFrom_AirportCode)

（或者，更好的是，我可以使用二進制索引或高級機制來解決這個問題嗎？）

CREATE TABLE [dbo].[flights](
   [flightId] [uniqueidentifier] NOT NULL,
   [accountId] [uniqueidentifier] NULL,
   [flightDate] [datetime] NULL,
   [flightTo_AirportCode] [nvarchar](30) NULL,
   [flightFrom_AirportCode] [nvarchar](30) NULL,
   -- ... 45 more fields
   CONSTRAINT [PK_flight] PRIMARY KEY CLUSTERED 
(
   [flightId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

索引 A 更適合此查詢。當除在列上WHERE使用範圍條件或IN運算符的條件之外的所有條件都是相等檢查時，那麼最後一列應該是索引中的最後一列，在所有具有相等檢查的列之後。

這允許優化器使用索引查找與條件匹配的第一行，然後遍歷索引，直到找到不匹配的行。中間的所有行也是匹配的。

因此，此查詢的最佳索引將是(to, from, date)（您的模型 A）或(from, to, date).

模型 B 索引首先具有日期，因此它不是最好的，儘管它仍然是查詢的覆蓋索引。如果使用它，查詢計劃將幾乎相同。索引尋找與範圍條件 ( date > '2016-02-28') 匹配的第一行，然後遍歷索引，直到找到與範圍條件不匹配的行date < '2016-04-01'。但是中間的所有行不一定與其他兩個條件匹配，因此必鬚根據這些條件檢查它們並且（可能其中許多）被拒絕。

因此，雖然計劃相似，但模型 A 計劃必須只通過索引中包含所有需要的行並且只有它們的部分，而模型 B 計劃將通過（可能更多）更大的部分指數。

---

• 最好使用 100% 安全的日期格式 ( YYYYMMDD)。
• 如果您想要 3 月的日期，則應使用包含獨占檢查：

AND flightdate >= '20160301' AND flightdate < '20160401' 

保證可以使用日期和日期時間類型。您目前的查詢還將包括任何具有'2016-02-28'但時間不同的行'00:00:00'（您能保證沒有任何行嗎？）我假設您不想要。inclusive-exclusive 方法也適用於閏年（提醒 2016 年是閏年，因此您的查詢也會返回 2 月 29 日）。

另請閱讀 Aaron Bertrand 的這些部落格文章：

• BETWEEN 和魔鬼有什麼共同點？
• 要改掉的壞習慣：錯誤處理日期/範圍查詢

引用自：https://dba.stackexchange.com/questions/140425