僅當過濾部分在 JOIN 而不是 WHERE 時才使用過濾索引

我在下面創建了過濾索引，但是當我進一步向下執行 2 個查詢時，該索引僅用於第一個範例中的查找，該範例在 JOIN 中具有 END_DTTM 而不是 where 子句（這是查詢中的唯一區別） . 誰能解釋為什麼會這樣？

索引創建

CREATE NONCLUSTERED INDEX [ix_PATIENT_LIST_BESPOKE_LIST_ID_includes] ON [dbo].[PATIENT_LIST_BESPOKE] 
(
   [LIST_ID] ASC,
   [END_DTTM] ASC
)
WHERE ([END_DTTM] IS NULL)

查詢

DECLARE @LIST_ID INT = 3655

--This one seeks on the index

SELECT  
   PATIENT_LISTS.LIST_ID
FROM    
   DBO.PATIENT_LISTS
   LEFT JOIN DBO.PATIENT_LIST_BESPOKE ON PATIENT_LISTS.LIST_ID = PATIENT_LIST_BESPOKE.LIST_ID  
                                     AND PATIENT_LIST_BESPOKE.END_DTTM IS NULL
WHERE
   PATIENT_LISTS.LIST_ID = @LIST_ID

--This one scans on the index

SELECT  
   PATIENT_LISTS.LIST_ID
FROM    
   DBO.PATIENT_LISTS
   LEFT JOIN DBO.PATIENT_LIST_BESPOKE ON PATIENT_LISTS.LIST_ID = PATIENT_LIST_BESPOKE.LIST_ID  
WHERE   
   PATIENT_LISTS.LIST_ID = @LIST_ID AND
   PATIENT_LIST_BESPOKE.END_DTTM IS NULL   

為了使優化器將謂詞與索引匹配（過濾或其他方式），謂詞必須出現在邏輯查詢樹中的 Get 操作附近。為了促進這一點，謂詞通常在優化開始之前盡可能靠近邏輯樹的葉子。

為了大大簡化，物理索引策略實現是這樣的：

Predicate + Logical Get -> Physical Get (using Index)

您感興趣的查詢以外部連接上方的謂詞開頭：

Predicate on T2 --+-- LOJ -- Get (T1)
                      |
                      +---- Get (T2)

此形狀與索引策略規則不匹配，因為謂詞與 Get 不相鄰。因此，答案的第一部分是過濾索引匹配將失敗，除非謂詞可以推過外連接。

第二部分很簡單，優化器不包含必要的探索規則來將謂詞移動到保留端的外連接，因為轉換很少有效。優化器的一般特性是只實現最常用的規則。

因此，在這種情況下，匹配過濾的索引會失敗。需要明確的是，重寫在您引用的非常具體的情況下是有效的（第二個查詢）。

對於第一個查詢表單（具有不同的語義），謂詞從一開始就與連接相關聯，並且謂詞下推邏輯可以將其移動到 Get 的短距離，因為它不必移動到外部連接，因為上面解釋過。

背景和更多資訊：

• 查詢優化器深入探討（分四部分）

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