有什麼方法可以測試哪個聚集索引更適合特定查詢？

我們一直在查詢和處理系統中不斷增長的事件表，目前還沒有特定的索引。但是，我們注意到性能有所下降，並且想知道我們可以做些什麼來改進它。

我創建了一些測試表來測試不同的索引：

SELECT TOP 1000000 * INTO IndexTest1
FROM Events

SELECT TOP 1000000 * INTO IndexTest2
FROM Events

SELECT TOP 1000000 * INTO IndexTest3
FROM Events

接下來，我在經常用於查詢事件或處理事件的列上添加了不同的聚集索引。大多數時候我們使用Timestamp或EventType。

CREATE CLUSTERED INDEX TimestampEventType
ON IndexTest1 (Timestamp, EventType)

CREATE CLUSTERED INDEX Timestamp
ON IndexTest2 (Timestamp)

CREATE CLUSTERED INDEX EventType
ON IndexTest3 (EventType)

接下來我測試了兩種不同類型的查詢。但是，我目前無法注意到哪個表現最好。

SELECT * FROM IndexTest1
WHERE EventType = 'String'

SELECT * FROM IndexTest2
WHERE EventType = 'String'

SELECT * FROM IndexTest3
WHERE EventType = 'String'

SELECT * FROM IndexTest1
WHERE Timestamp >= '2018-03-14' AND Timestamp <= '2018-03-20' AND EventType = 'String'

SELECT * FROM IndexTest2
WHERE Timestamp >= '2018-03-14' AND Timestamp <= '2018-03-20' AND EventType = 'String'

SELECT * FROM IndexTest3
WHERE Timestamp >= '2018-03-14' AND Timestamp <= '2018-03-20' AND EventType = 'String'

我所期望的： 我希望 IndexTest1 對這兩個查詢都表現最好，因為它是唯一一個同時包含EventType和Timestamp在其聚集索引中的查詢。

結果第二個查詢集： 打開實時查詢統計資訊時，我得到所有查詢的不同執行時間，範圍在 8 到 20 秒之間。

在查看執行計劃時，我確實注意到 IndexTest1 對於第二組查詢的 Estimated I/O Cost 和 Estimated Operator Cost 比 IndexTest2 和 IndexTest3 低得多。差異比較大，IndexTest1 的 I/O 成本約為 0.003，而 IndexTest2 和 IndexTest3 分別約為 25 和 40。

**問題：**我可以得出結論，對於第二個查詢集 IndexTest1 是最佳索引嗎？

**結果第一組查詢：**在查看第一組查詢的執行計劃時，IndexTest3 實際上具有最佳性能（儘管勉強）。它們的 I/O 成本都在 40 左右，IndexTest2 最差，IndexTest 3 最好。

**Questiong：**這是否意味著我添加的索引對第一個查詢集沒有實際影響？如果是這樣，如果EventType包含在 IndexTest1 的聚集索引中，為什麼會這樣？

我在測試索引和創建索引方面經驗很少。有沒有其他方法可以測試哪個索引最適合上述查詢？或者還有什麼我應該嘗試的嗎？

或者是否有人可以將我連結到有關索引和測試它們的更多資訊的任何閱讀材料？

聚集索引應該用一個窄且不斷增加的值來定義（這就是為什麼這麼多使用自動遞增整數的原因）。

以前使用過像這樣的大型表，最好的辦法是繼續使用自動遞增的主鍵作為聚集索引的定義。然後創建支持您的查詢的非聚集索引，同時了解其他臨時查詢可能會執行得非常糟糕。您將獲得額外的好處，即大表上的索引永遠不需要碎片整理。

鑑於此，在 Timestamp 上創建非聚集索引，EventType 應該提供足夠的性能。您通常希望首先引用最具體的列，並且時間戳是很好的候選者，特別是如果您總是（或幾乎總是）將它包含在查詢中。

非聚集索引的優點當然是它們通常比它們的基表窄得多，因此需要的記憶體少得多。

CREATE NONCLUSTERED INDEX TimestampEventType
ON IndexTest1 (Timestamp, EventType)

您還可以從轉換為正確的數據類型中受益（我假設 DATETIME）

SELECT * FROM IndexTest1
WHERE Timestamp >= CAST('2018-03-14' AS DATETIME) AND Timestamp <= CAST('2018-03-20' AS DATETIME) AND EventType = 'String'

最後，檢查您的查詢的統計資訊是否是最新的，並且執行計​​劃實際上正在執行索引查找或範圍掃描，並通過鍵查找回主表。

您可能無法從測試中得出任何結論，因為您在測試和生產之間引入了太多差異。您僅使用表中的一百萬行進行測試，查詢可能與生產中執行的查詢不匹配（您真的總是選擇所有列），並在 SSMS 中顯示實時查詢統計視圖。在 SSMS 中顯示實時查詢統計視圖和結果集需要時間。最可靠的測試方法是針對完整大小的數據副本使用您關心的生產查詢。這將減少不確定性，因為諸如“此聚集索引適用於 100 萬行但如何適用於更大的表？”之類的問題。不再相關。

你說有“性能下降”。那怎麼量化呢？換句話說，為什麼性能下降很重要？應用程序的哪些部分受到性能下降的影響？例如，可能有一個最終使用者執行的重要查詢過去需要 1 秒，但現在需要 5 秒。如果是這樣，請使用聚群索引的不同選項測試該查詢的性能。您已經量化了影響，現在有了可以衡量的東西。目前你正在做的是一個抽象的測試，你正試圖將這些結果應用到你的應用程序中，但毫不奇怪，這很難做到。

關於聚集索引如何工作的主題，您不能僅在聚集索引的第二列上獲得索引搜尋謂詞。您也無法使用第一個鍵上的範圍謂詞和第二個謂詞上的相等鍵來獲取索引查找謂詞。按順序瀏覽您的查詢：

SELECT * FROM IndexTest1
WHERE EventType = 'String';

這將導致聚集索引掃描，因為EventType它是索引的第二個鍵。

SELECT * FROM IndexTest2
WHERE EventType = 'String';

這將導致聚集索引掃描，因為EventType它不是鍵列。

SELECT * FROM IndexTest3
WHERE EventType = 'String';

這將導致聚集索引查找，因為EventType它是索引中的第一個鍵。您應該期望此查詢在集合中表現最好。

SELECT * FROM IndexTest1
WHERE Timestamp >= '2018-03-14' AND Timestamp <= '2018-03-20' AND EventType = 'String';

這將導致僅在列上進行聚集索引查找，因為由於第一個鍵列上的不等謂詞而無法使用Timestamp相等謂詞。EventType

SELECT * FROM IndexTest2
WHERE Timestamp >= '2018-03-14' AND Timestamp <= '2018-03-20' AND EventType = 'String';

這將導致在Timestamp鍵列上進行聚集索引查找。

SELECT * FROM IndexTest3
WHERE Timestamp >= '2018-03-14' AND Timestamp <= '2018-03-20' AND EventType = 'String';

這將導致聚集索引在EventType.

在這組中執行最好的查詢取決於列的選擇性。

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