非聚集索引 - 鍵和非鍵

我只是想確保我在這些概念的正確軌道上，所以任何回饋都將不勝感激。

這是我剛剛優化的查詢的理論，通過反複試驗和閱讀 MSDN 文件的過程。

查詢

DECLARE @pic_id int
SET pic_id = 1

SELECT ROW_NUMBER() OVER (ORDER BY pic_date desc) AS row_num, *
FROM tbl_pics
WHERE deleted = 0 AND map_id = 1 AND (hidden = 0 OR pic_id = @pic_id)

指數

CREATE NONCLUSTERED INDEX [IX_tbl_pics] ON [dbo].[tbl_pics] 
(
   [map_id] ASC,
   [deleted] ASC,
   [pic_date] DESC
)
INCLUDE ( [hidden], [pic_id] )

pic_id 上還有一個 PK 索引

理論

關鍵列之所以如此，是因為它們用於 WHERE 子句（但不用於 OR 情況）或 ORDER BY。

非鍵 (INCLUDE) 列也是如此，因為它們在 WHERE 中使用，但因為它們在 OR 場景中使用，它們不能（不能 = 不會提高性能）成為鍵列。

這些假設正確嗎？如果沒有，我錯過了什麼？

謝謝！

您要求查詢優化器生成一個可以回答查詢的計劃：

SELECT *
FROM tbl_pics
WHERE deleted = 0 AND map_id = 1 AND hidden = 0;

其餘的是絨毛（包括OR pic_id = @pic_id）。這將是一個表掃描，保證，因為所涉及的謂詞的選擇性低（我確定deleted並且hidden是 0/1，map_ip我懷疑它有任何重大影響）。唯一可以保存查詢的謂詞是，pic_id = @pic_id但是通過將其置於 OR 條件中，您就失去了機會。實際上，沒有二級索引可以幫助它。添加 ROW_NUMBER 很可能會進行排序，但真正的損害是掃描。

這是一個失敗的原因。提出切合實際的要求。

主鍵也是聚集索引嗎？如果是這樣，則沒有理由這樣做，INCLUDE (pic_id)因為唯一聚集索引已被用作非聚集索引中的書籤。

就你所說的 OR 而言，這實際上是 WHERE 的第二部分，但你只是依靠第一部分的選擇性來完成大部分工作（同時依靠 INCLUDE 來避免去桌子）。

但是通過在其中添加 *，您可能無論如何都必須走到桌子旁。

另一方面，我可能不會基於單個查詢來設計我的索引，除非這個查詢被大量使用而不考慮更多的負載。並且仍然查看執行計劃不會受到傷害。

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