為什麼這個查詢不使用我的非聚集索引，我該怎麼做？

作為這個關於提高查詢性能的問題的後續行動，我想知道是否有辦法讓我的索引預設使用。

此查詢執行大約 2.5 秒：

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31';

這個執行大約 33 毫秒：

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31' 
ORDER BY [DateEntered], [DeviceID];

有一個聚集索引

$$ ID $$欄位 (pk) 並且有一個非聚集索引$$ DateEntered $$,$$ DeviceID $$. 第一個查詢使用聚集索引，第二個查詢使用我的非聚集索引。我的問題是兩個部分：

• 為什麼，因為這兩個查詢都有一個 WHERE 子句$$ DateEntered $$欄位，伺服器是否在第一個上使用聚集索引，而不是第二個？
• 即使沒有orderby，如何在此查詢中預設使用非聚集索引？（或者我為什麼不想要​​這種行為？）

第一個查詢根據我之前解釋的門檻值進行表掃描：是否可以在具有數百萬行的窄表上提高查詢性能？

（很可能沒有該TOP 1000子句的查詢將返回超過 46k 行。或者在 35k 和 46k 之間。（灰色區域 ;-)）

第二個查詢，必須訂購。由於您的 NC 索引按您想要的順序排序，因此優化器使用該索引更便宜，然後對聚集索引進行書籤查找以獲取失去的列，與執行聚集索引掃描然後需要訂購。

反轉子句中列的順序，ORDER BY您將返回到聚集索引掃描，因為 NC INDEX 是無用的。

編輯忘記了第二個問題的答案，為什麼你不想要這個

使用非聚集非覆蓋索引意味著在 NC 索引中查找 rowID，然後必須在聚集索引中查找失去的列（聚集索引包含表的所有列）。在聚集索引中查找缺失列的 IO 是隨機 IO。

這裡的關鍵是隨機的。因為對於在 NC 索引中找到的每一行，訪問方法都必須在聚集索引中查找新頁面。這是隨機的，因此非常昂貴。

現在，另一方面，優化器也可以進行聚集索引掃描。它可以使用分配映射來查找掃描範圍，然後開始讀取大塊的聚集索引。這是連續的，而且便宜得多。（只要你的表沒有碎片:-)）缺點是，需要讀取整個聚集索引。這對您的緩衝區和潛在的大量 IO 不利。但仍然是順序 IO。

在您的情況下，優化器決定了 35k 到 46k 行之間的某個位置，完全聚集索引掃描的成本較低。是的，這是錯誤的。並且在很多情況下，對於非選擇性WHERE子句或大型表的窄非聚集索引，這會出錯。（你的桌子更糟，因為它也是一張很窄的桌子。）

現在，添加ORDER BY會使掃描完整聚集索引然後對結果進行排序變得更加昂貴。相反，優化器假設使用已排序的 NC 索引更便宜，然後為書籤查找支付隨機 IO 損失。

因此，您的 order by 是一種完美的“查詢提示”解決方案。但是，在某個時刻，一旦您的查詢結果如此之大，書籤查找隨機 IO 的懲罰就會如此之大，它會變得更慢。我假設優化器會在此之前將計劃更改回聚集索引掃描，但您永遠無法確定。

在您的情況下，只要您的插入按entereddate 排序，如聊天和上一個問題（參見連結）中所述，您最好在enteredDate 列上創建聚集索引。

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