Sql-Server

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

  • March 18, 2020

我有以下

$$ 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