sqlite - WHERE EXISTS 的性能非常慢，沒有 EXISTS 上的索引

當我在 EXISTS 子句中的表上沒有索引時，我遇到了一個更新查詢性能（非常）緩慢的奇怪情況。

例如：

UPDATE tbl1
  SET col1 = "Y"
WHERE EXISTS (SELECT NULL FROM tbl2 WHERE tbl1.col2 = tbl2.col2)
  AND tbl1.col3 IN (1,2);

• tbl1 有 ~450 萬行。當上面通過 col3 過濾時，它下降到大約 400k 行。
• tbl2 有 ~100k 行。col2 上的所有行都是唯一的，並且 tbl2 中的每一行在 tbl1 中都有一個匹配項。
• tbl1 在 col2 上有一個索引，在 col3 上有一個索引。
• 我正在使用 sqlite 版本 3.29.0（來自 Python 3.7.2）

當我在 tbl2.col2 上沒有索引的情況下執行此查詢時，查詢永遠不會完成（我讓它執行了幾分鐘）。如果我在 tbl2(col2) 上添加唯一索引，則查詢幾乎立即完成。

這讓我感到困惑，因為我認為查詢應該受到對 tbl1 的查找的限制，因為我使用的是 tbl2 的 EXISTS 查詢（而不是 IN()）。tbl2.col2 上的唯一索引不會添加全表掃描不會提供的任何新資訊，因為 tbl2.col2 中的每一行都是唯一的。

為什麼沒有唯一索引的性能會這麼慢？

tbl2.col2 上的唯一索引沒有添加全表掃描無法提供的任何新資訊

那是真實的; 問題是，你認為有多少表掃描？

SQLite 文件會告訴你，它的優化器只能進行嵌套循環連接，所以無論它是否足夠聰明地將EXISTS子查詢重寫為連接1或者會天真地評估驅動表的每一行的謂詞，你仍然會最終進行 450 萬次表掃描，每次平均檢索 50K 行。

在存在唯一索引的情況下，tbl2.col2使用 B-tree 會產生 450 萬次索引查找，每次只需要 2-3 次邏輯 I/O 操作並只檢索一行，這比 I/O 少 11 個數量級。

---

1 - 無論如何這不會有任何區別，因為它會將該連接重寫為針對兩個表的簡單謂詞的組合。

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