為什麼是小號eemingliy_小號和和米一世nG一世是Seemingly未在帶有 OR 的 LEFT JOIN 上使用的合適索引

我有以下

$$ fairly meaningless, just for the purpose of demonstration $$在 StackOverflow 數據庫中查詢：

SELECT  *
FROM    Users u
       LEFT JOIN Comments c
           ON u.Id = c.UserId OR
              u.Id = c.PostId
WHERE   u.DisplayName = 'alex'

表上唯一的索引Users是 ID 上的聚集索引。

該Comments表具有以下非聚集索引以及 ID 上的聚集索引：

CREATE INDEX IX_UserID ON Comments
(
   UserID,
   PostID
)

CREATE INDEX IX_PostID ON Comments
(
   PostID,
   UserID
)

查詢的估計計劃在這裡：

我可以看到優化器要做的第一件事是對 users 表執行 CI 掃描以僅過濾那些使用者 where DisplayName = Alex，有效地這樣做：

SELECT  *
FROM    Users u
WHERE   u.DisplayName = 'alex'
ORDER BY Id

並像這樣檢索結果：

然後它會掃描評論 CI 並且對於每一行，看看該行是否滿足謂詞

u.Id = c.UserId OR u.Id = c.PostId

儘管有兩個索引，但仍會執行此 CI 掃描。

如果優化器對上面 Comments 表中的每個索引進行單獨的查找並將它們連接在一起，那不是更有效嗎？

如果我想像那會是什麼樣子，在上面的螢幕截圖中，我們可以看到使用者 CI 掃描的第一個結果是 ID 420

我可以IX_UserID使用視覺化索引的樣子

SELECT      UserID,
           PostID
FROM        Comments
ORDER BY    UserID,
           PostID

所以如果我尋找使用者 ID 420 的行作為索引尋找將：

對於 的每一行UserID = 420，我可以查看 u.Id = c.UserId OR u.Id = c.PostId它們是否都匹配u.Id = c.UserId我們謂詞的部分，

所以對於我們索引搜尋的第二部分，我們可以通過我們的索引IX_PostID進行搜尋，可以如下所示：

SELECT      PostID,
           UserID
FROM        Comments
ORDER BY    PostID,
           UserID 

如果我尋求發布 ID 420，我看不到任何內容：

所以我們然後返回 CI 掃描的結果，移動到下一行 (userId 447) 並重複該過程。

我上面描述的行為可以在WHERE子句中使用：

SELECT      UserID,
           PostID
FROM        Comments
WHERE       UserID = 420 OR PostID = 420
ORDER BY    UserID,
           PostID

在這裡計劃

因此，我的問題是，為什麼子句OR中的條件JOIN不能對適當的索引執行索引查找？

我不會專注於如何改進這樣的查詢，這是其他答案正在做的事情，我將嘗試回答被問到的問題：為什麼優化器不產生像你描述的那樣的計劃（掃描使用者表，然後查找評論表上的兩個索引）。

這是您的原始查詢（請注意，我MAXDOP 2只是用來模擬我在您的執行計劃中看到的內容）：

SELECT  *
FROM    Users u
       LEFT JOIN Comments c
           ON u.Id = c.UserId OR
              u.Id = c.PostId
WHERE   u.DisplayName = 'alex'
OPTION (MAXDOP 2);

和計劃：

• 使用殘差謂詞掃描dbo.Users以獲取“alex”使用者
• 對於這些使用者中的每一個，掃描dbo.Comments表並在連接運算符中過濾匹配項
• 估計成本：293.161 個優化器單元

獲得您想要的計劃的一種嘗試是嘗試在桌面上強制搜尋：dbo.Comments

SELECT  *
FROM    Users u
       LEFT JOIN Comments c WITH (FORCESEEK)
           ON u.Id = c.UserId OR
              u.Id = c.PostId
WHERE   u.DisplayName = 'alex'
OPTION (MAXDOP 2);

計劃如下所示：

• 掃描dbo.Users表（使用殘差謂詞僅獲取名為“alex”的使用者），
• 搜尋兩個索引中的每一個以獲取請求的 Id 值（聯合在一起）
• 隨後是一個鍵查找以獲取其餘列（因為我們選擇了 *）
• 估計成本：5.98731 個優化器單元

所以答案是優化器絕對有能力產生這樣的計劃。而且這似乎不是基於成本的決定（尋找計劃看起來要便宜得多）。

我最好的猜測是，這只是優化器探索過程中的某種限制——它似乎不贊成將帶有 or 子句的左連接轉換為應用。在這種特殊情況下，這真的很不幸，因為掃描計劃（在我的機器上查詢需要 45 秒）與應用計劃（不到 1 秒）相比，性能很差。

旁注：您可以使用未記錄的跟踪標誌 8726 覆蓋不利於索引聯合計劃的啟發式方法。有關這方面的更多詳細資訊，請參見https://dba.stackexchange.com/a/23779！

正如 Rob Farley 有用地指出的那樣，APPLY直接使用（也可能與 a 一起使用UNION）是獲得您正在尋找的計劃的更好方法 - 這兩種方法都會產生該計劃的“更好”版本（FORCESEEK版本）。我會說“ ORin a JOIN”是一種已知的反模式，應該避免，因為優化器似乎並不直接支持這種類型的查詢。

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