索引定義 Order 和 ORDER BY 子句
所以我在早上閱讀部落格時偶然發現了這個有趣的練習:
https://www.erikdarlingdata.com/sql-server/lets-design-an-index-together-part-3/
這是文章中的問題和他提出的索引。
SELECT TOP (5000) p.LastActivityDate, p.PostTypeId, p.Score, p.ViewCount FROM dbo.Posts AS p WHERE p.PostTypeId = 1 AND p.LastActivityDate >= '20110101' ORDER BY p.Score DESC; CREATE INDEX whatever ON dbo.Posts(PostTypeId, Score DESC, LastActivityDate) INCLUDE(ViewCount) WITH (DROP_EXISTING = ON);
非常有趣的建構和索引,並嘗試相應地調整它。但是,我之前可能誤解了索引鍵順序很重要,並且當索引鍵順序與查詢不匹配時,某些 WHERE 子句可能不使用某些索引。意思是,我對列出的特定場景缺乏經驗,我假設這個查詢不會使用這個索引,因為 Score 位於索引鍵定義的中間,但不在查詢的 where 子句中。
當優化器決定使用什麼索引時,ORDER BY 列是否會被評估,只要 WHERE 子句列和 ORDER by 列在索引定義中,它就會使用它?
我想我的問題更多是關於優化器如何評估關於 WHERE 子句和 ORDER BY 子句的索引。
鍵的順序絕對重要。這兩個建議的索引採用不同的方法來解決它。
讓我們考慮一下當索引打開時查詢是如何執行的
(PostTypeID, LastActivityDate)
,考慮手動操作。我們可以很容易地找到與 WHERE 子句匹配的所有行 - PostTypeID 1 並且足夠近。但是我們需要按分數對它們進行排序以找到前 5000 名。如果我們有很多行要排序,這可能會很昂貴。
或者,使用 上的索引
(PostTypeID, Score DESC)
,我們只能過濾到 PostTypeID,但是我們可以按正確的順序遍歷該數據。是的,我們必鬚根據 LastActivityDate 拒絕任何不夠新的行,但是一旦我們找到了我們關心的 5000 行,我們就可以停止。我們不必做那種昂貴的事情。但是我們正在查看比我們關心的更多的行。順便說一句,我認為在鍵列中包含 LastActivityDate 沒有多大價值 - 它同樣適合包含的列,因為它不參與 Seek 謂詞。Seek 運算符只是按分數順序返回該 PostTypeID 值的所有行。查詢優化器知道可以按照索引中指定的順序從索引中提取數據,這可以在許多方面受益。也許它有助於 Merge Join、Stream Aggregate 或 ORDER BY 子句。
如果我們認為 PostTypeID 被過濾為單個值,則一個索引位於 Score 上以避免排序,而另一個索引位於 LastActivityDate 以收緊 Seek 範圍。QO 權衡每個的預期成本並選擇“更便宜”的一個。