使用 IN() 提高查詢性能

我有以下 SQL 查詢：

SELECT
 Event.ID,
 Event.IATA,
 Device.Name,
 EventType.Description,
 Event.Data1,
 Event.Data2
 Event.PLCTimeStamp,
 Event.EventTypeID
FROM
 Event
INNER JOIN EventType ON EventType.ID = Event.EventTypeID
INNER JOIN Device ON Device.ID = Event.DeviceID
WHERE
 Event.EventTypeID IN (3, 30, 40, 41, 42, 46, 49, 50)
 AND Event.PLCTimeStamp BETWEEN '2011-01-28' AND '2011-01-29'
 AND Event.IATA LIKE '%0005836217%'
ORDER BY Event.ID;

Event我在該列的表上也有一個索引TimeStamp。我的理解是，由於IN()聲明的原因，沒有使用這個索引。所以我的問題是有沒有辦法為這個特定的IN()語句建立一個索引來加速這個查詢？

我還嘗試添加Event.EventTypeID IN (2, 5, 7, 8, 9, 14)作為索引的過濾器 on TimeStamp，但是在查看執行計劃時，它似乎沒有使用該索引。對此的任何建議或見解將不勝感激。

下面是圖形計劃：

這是.sqlplan 文件的連結。

給定以下一般形式的表格：

CREATE TABLE Device 
(
   ID integer PRIMARY KEY
);

CREATE TABLE EventType
(
   ID integer PRIMARY KEY, 
   Name nvarchar(50) NOT NULL
);

CREATE TABLE [Event]
(
   ID integer PRIMARY KEY, 
   [TimeStamp] datetime NOT NULL, 
   EventTypeID integer NOT NULL REFERENCES EventType, 
   DeviceID integer NOT NULL REFERENCES Device
);

以下索引很有用：

CREATE INDEX f1 
ON [Event] ([TimeStamp], EventTypeID) 
INCLUDE (DeviceID)
WHERE EventTypeID IN (2, 5, 7, 8, 9, 14);

對於查詢：

SELECT
 [Event].ID,
 [Event].[TimeStamp],
 EventType.Name,
 Device.ID
FROM
 [Event]
INNER JOIN EventType ON EventType.ID = [Event].EventTypeID
INNER JOIN Device ON Device.ID = [Event].DeviceID
WHERE
 [Event].[TimeStamp] BETWEEN '2011-01-28' AND '2011-01-29'
 AND Event.EventTypeID IN (2, 5, 7, 8, 9, 14);

過濾器滿足AND子句要求，索引的第一個鍵允許[TimeStamp]對已過濾的列進行查找，EventTypeIDs並且包含DeviceID列使索引覆蓋（因為DeviceID連接到Device表是必需的）。

索引的第二個鍵 -EventTypeID不是嚴格要求的（它也可以是一INCLUDEd列）；出於此處所述的原因，我已將其包含在密鑰中。一般來說，我建議人們至少從過濾索引子句中獲取列。INCLUDE``WHERE

---

根據問題中更新的查詢和執行計劃，我同意 SSMS 建議的更通用的索引可能是這裡更好的選擇，除非過濾列表EventTypeIDs是靜態的，因為 Aaron 在他的回答中也提到：

CREATE TABLE Device 
(
   ID integer PRIMARY KEY,
   Name nvarchar(50) NOT NULL UNIQUE
);

CREATE TABLE EventType
(
   ID integer PRIMARY KEY, 
   Name nvarchar(20) NOT NULL UNIQUE,
   [Description] nvarchar(100) NOT NULL
);

CREATE TABLE [Event]
(
   ID integer PRIMARY KEY, 
   PLCTimeStamp datetime NOT NULL,
   EventTypeID integer NOT NULL REFERENCES EventType, 
   DeviceID integer NOT NULL REFERENCES Device,
   IATA varchar(50) NOT NULL,
   Data1 integer NULL,
   Data2 integer NULL,
);

建議的索引（如果合適，聲明它是唯一的）：

CREATE UNIQUE INDEX uq1
ON [Event]
   (EventTypeID, PLCTimeStamp)
INCLUDE 
   (DeviceID, IATA, Data1, Data2, ID);

執行計劃中的基數資訊（未記錄的語法，不要在生產系統中使用）：

UPDATE STATISTICS dbo.Event WITH ROWCOUNT = 4042700, PAGECOUNT = 400000;
UPDATE STATISTICS dbo.EventType WITH ROWCOUNT = 22, PAGECOUNT = 1;
UPDATE STATISTICS dbo.Device WITH ROWCOUNT = 2806, PAGECOUNT = 28;

更新的查詢（重複表的IN列表EventType有助於優化器在這種特定情況下）：

SELECT
 Event.ID,
 Event.IATA,
 Device.Name,
 EventType.Description,
 Event.Data1,
 Event.Data2,
 Event.PLCTimeStamp,
 Event.EventTypeID
FROM
 Event
INNER JOIN EventType ON EventType.ID = Event.EventTypeID
INNER JOIN Device ON Device.ID = Event.DeviceID
WHERE
 Event.EventTypeID IN (3, 30, 40, 41, 42, 46, 49, 50)
 AND EventType.ID IN (3, 30, 40, 41, 42, 46, 49, 50)
 AND Event.PLCTimeStamp BETWEEN '2011-01-28' AND '2011-01-29'
 AND Event.IATA LIKE '%0005836217%'
ORDER BY Event.ID;

預計執行計劃：

你得到的計劃可能會有所不同，因為我使用的是猜測的統計數據。總的一點是盡可能多地給優化器提供資訊，並在 400 萬行的[Event]表上提供高效的訪問方法（索引）。

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