為什麼使用相關子查詢的不正確 JOIN 會慢得多
我正在做一些相當輕量級的數據按摩/清理，並遇到了一個問題，其中一個版本的 JOIN 使用相關子查詢（可能是錯誤的）執行速度比我認為正確的要慢得多*。我不是在問如何進行查詢*（我相信我現在已經正確了），但我想知道為什麼慢版本如此慢。
問題
該域是一個相當簡單的數據庫，用於管理彩票集團（記錄會員支付、玩過的遊戲和獲勝）。在遷移到新引擎（SQLite）時，我正在嘗試清理數據並改進表的結構。
現有
_Winnings表記錄了贏得的金額和日期以及“遊戲類型”（可以玩多個遊戲）：CREATE TABLE [_Winnings]( [ID] integer primary key not null, [WinDate] date, [Amount] integer, [GameType] integer references _Games(ID) ); CREATE INDEX [_WinningsIndex] on _Winnings(GameType) ;主要問題是與實際玩的遊戲沒有聯繫（除了獲胜日期）。這些記錄已經遷移，現在保存在一個
EventHistory表中：CREATE TABLE [EventHistory]( [ID] integer primary key not null, [EventType] integer references Events(ID), [GameType] integer references Games(ID), [EventDate] date ); CREATE INDEX [EventHistoryEventIndex] on EventHistory(EventType) ; CREATE INDEX [EventHistoryGameIndex] on EventHistory(GameType) ; CREATE INDEX [EventHistoryDateIndex] on EventHistory(EventDate) ;這三個表
_Games，Games並Events保存遊戲/事件的“類型”，基本上具有以下內容：_Games Games Events ID GameType ID GameType ID Name -- --------- -- --------- -- ---------- 1 GameName1 1 GameName1 5 Dispersal 2 GameName2 1 GameName2 6 Withdrawal 3 GameName3 1 GameName3 7 GamePlayed 4 GameName4 1 GameName4 8 MissingGame 5 Dispersal 6 Withdrawal新表將“真實”和“偽”遊戲類型拆分為自己的表。
顯示遷移過程要求的範例數據是：
_Winnings ID WinDate Amount GameType (Notes) --- ---------- ------ -------- ------------------------------- 123 2016-04-20 1234 1 A. Ideal match to "game played" record 167 2017-08-20 1000 1 B. "Missing" game 189 2018-12-20 990 1 C. Matches two games 199 2019-02-01 -2000 6 D. A non-game event (withdrawal) EventHistory ID EventType GameType EventDate (Notes) --- --------- -------- --------- ------------------------------- 111 7 (Game) 1 2016-04-20 Perfect match for (A) 222 7 (Game) 1 2017-08-15 \ No entry matches (B) 223 7 (Game) 1 2017-08-25 / 333 7 (Game) 1 2018-12-20 \ Two matches for (C) 334 7 (Game) 1 2018-12-20 /情況 (A) 是“正常”情況：已經進行了一場比賽，並且取得了勝利。我希望新
Winnings條目直接引用匹配的事件記錄。案例 (B) 將表明數據中有一些錯誤（可能是錯誤輸入的獲胜日期，我想稍後通過在
EventHistory.情況（C）有效，代表當日復式。將任一記錄匹配
EventHistory到新記錄是Winnings可以接受的。案例 (D) 是一種“偽”遊戲：獎金要麼被提取，要麼被用來購買額外的線。無論 中是否存在現有的匹配日期條目
EventHistory，都將創建新的事件記錄。我第一次嘗試查找匹配項在日期上使用了左連接（左連接，因為不能保證是日期匹配），但沒有考慮到 (C) 之類的情況：
EventHistorygive中的多個匹配條目上升到 的重複值_Winnings.ID，這是我不能有的。select W.*, EH.ID as EID, G.ID as GID from _Winnings as W left join EventHistory as EH on W.WinDate = EH.EventDate left join Games as G on W.GameType = G.ID因此，我將其更改為使用相關子查詢，以確保只使用一條記錄（哪
EventHistory條記錄並不重要）。在我的第一次嘗試中，我錯誤地留下了對主選擇別名 ( ) 的引用：EH.EventDateselect W.*, EH.ID as EID, G.ID as GID from _Winnings as W left join EventHistory as EH on EH.ID = ( select min(ID) from EventHistory where W.WinDate = EH.EventDate ) left join Games as G on W.GameType = G.ID這似乎有效，但非常緩慢。用完整的表名 (
EventHistory.EventDate) 替換別名：select W.*, EH.ID as EID, G.ID as GID from _Winnings as W left join EventHistory as EH on EH.ID = ( select min(ID) from EventHistory where W.WinDate = EventHistory.EventDate ) left join Games as G on W.GameType = G.ID顯著提高了速度。在 365 條記錄中
_Winnings，從 494 條記錄開始EventHistory（隨著一些新記錄的添加上升到 581 條），整體速度（包括執行一些插入）從 3 多分鐘下降到 3 秒左右。“快速”查詢計劃：
QUERY PLAN |--SCAN TABLE _Winnings AS W |--SEARCH TABLE EventHistory AS EH USING INTEGER PRIMARY KEY (rowid=?) |--CORRELATED SCALAR SUBQUERY 1 | `--SEARCH TABLE EventHistory USING COVERING INDEX EventHistoryDateIndex (EventDate=?) `--SEARCH TABLE Games AS G USING INTEGER PRIMARY KEY (rowid=?)“慢”查詢計劃
QUERY PLAN |--SCAN TABLE _Winnings AS W |--SCAN TABLE EventHistory AS EH USING COVERING INDEX EventHistoryDateIndex |--CORRELATED SCALAR SUBQUERY 1 | `--SEARCH TABLE EventHistory `--SEARCH TABLE Games AS G USING INTEGER PRIMARY KEY (rowid=?)顯然，這些是不同的，但我沒有能力理解他們告訴我的內容。
我實際上在做的是處理查詢返回的每一行，有時在表中創建一條新記錄
EventHistory（並且總是在遷移的表中創建一行Winnings）。大致流程是：foreach row returned by the query if EID or GID is empty -- either there isn't an exact date match (EID="") or -- the "game-type" is a "pseudo" game (GID=""). In either -- case, I want to insert a new row in EventHistory. insert new row in EventHistory table endif insert new row in Winnings table endfor我最初認為插入
EventHistory會影響速度，因為當我只對原始查詢進行計時（對結果不做任何事情）時，兩個版本之間的速度沒有明顯差異。但是，鑑於CL. 的回答，其中包括“您在表中插入新行對速度沒有影響”，我進一步調查，似乎使用的 SQLite 版本可能是速度差異。
我正在使用Tcl編寫更新過程（包括插入）的腳本，這就是我最初看到查詢的兩個版本之間速度上的巨大差異的地方。Tcl 有自己的 SQLite 版本，在我的情況下它有點舊（2014 年 10 月的 3.8.7.1）。
但是，當我第一次只為查詢計時時，我使用了新下載的獨立 SQLite shell 版本（2019 年 2 月的 3.27.2）。在這個版本中，兩個查詢以基本相同的速度執行（亞秒級）。
當我使用舊版本的 SQLite 在 Tcl 中重複“僅查詢”測試時，速度上的差異再次顯著：根據 Tcl 的
time功能，8 毫秒對 2 分鐘。我的結論是：
這兩個值是恆定的（就子查詢而言），因此表的所有行都匹配，或者不匹配。但是查詢優化器不夠聰明，無法辨識這一點，因此它會遍歷表的所有行並每次都評估 WHERE 子句。
來自 CL 的答案確實適用於 SQLite 3.8.7.1，但不再適用於 SQLite 3.27.2。
（
explain query plan每個查詢的輸出在兩個版本的 SQLite 中保持相同，但顯示的 VDBE 步驟explain在 SQLite 版本之間確實不同）。
不同之處在於相關子查詢如何進行搜尋。
快速子查詢如下所示：
select min(ID) from EventHistory where EventHistory.EventDate = ? -- SEARCH TABLE EventHistory USING COVERING INDEX EventHistoryDateIndex (EventDate=?)上有一個索引
EventDate，因此數據庫可以在該索引中查找匹配的行，然後記住並僅返回最小值ID。慢子查詢如下所示：
select min(ID) from EventHistory where ? = ? -- SEARCH TABLE EventHistory這兩個值是恆定的（就子查詢而言），因此表的所有行都匹配，或者不匹配。但是查詢優化器不夠聰明，無法辨識這一點，因此它會遍歷表的所有行並每次都評估 WHERE 子句。
（有MIN/MAX 優化，但只有在沒有 WHERE 子句時才有效。）
您在表中插入新行對速度沒有影響。但是，如果可能，SQLite 會按需計算結果行，因此在讀取表時修改表可能會導致結果不一致。您應該首先閱讀查詢的所有結果，或者使用臨時表。