為什麼是小號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
版本)。我會說“OR
in aJOIN
”是一種已知的反模式,應該避免,因為優化器似乎並不直接支持這種類型的查詢。