索引定義 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 權衡每個的預期成本並選擇“更便宜”的一個。

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