為什麼當我索引列時這個 sqlite 查詢要慢得多？

我有一個帶有兩個表的 sqlite 數據庫，每個表有 50,000 行，其中包含（假）人的姓名。我建構了一個簡單的查詢來找出兩個表共有的名字（名字、中間名、姓氏）有多少：

select count(*) from fakenames_uk inner join fakenames_usa on fakenames_uk.givenname=fakenames_usa.givenname and fakenames_uk.surname=fakenames_usa.surname and fakenames_uk.middleinitial=fakenames_usa.middleinitial;

當除了主鍵（與此查詢無關）之外沒有索引時，它會快速執行：

[james@marlon Downloads] $ time sqlite3 generic_data_no_indexes.sqlite "select count(*) from fakenames_uk inner join fakenames_usa on fakenames_uk.givenname=fakenames_usa.givenname and fakenames_uk.surname=fakenames_usa.surname and fakenames_uk.middleinitial=fakenames_usa.middleinitial;"
131

real    0m0.115s
user    0m0.111s
sys     0m0.004s

但是，如果我為每個表的三列添加索引（總共六個索引）：

CREATE INDEX `idx_uk_givenname` ON `fakenames_uk` (`givenname` )
//etc.

然後它痛苦地緩慢地執行：

[james@marlon Downloads] $ time sqlite3 generic_data.sqlite "select count(*) from fakenames_uk inner join fakenames_usa on fakenames_uk.givenname=fakenames_usa.givenname and fakenames_uk.surname=fakenames_usa.surname and fakenames_uk.middleinitial=fakenames_usa.middleinitial;"
131

real    1m43.102s
user    0m52.397s
sys     0m50.696s

這有什麼押韻或理由嗎？

這是EXPLAIN QUERY PLAN沒有索引的版本的結果：

0|0|0|SCAN TABLE fakenames_uk
0|1|1|SEARCH TABLE fakenames_usa USING AUTOMATIC COVERING INDEX (middleinitial=? AND surname=? AND givenname=?)

這是與索引：

0|0|0|SCAN TABLE fakenames_uk
0|1|1|SEARCH TABLE fakenames_usa USING INDEX idx_us_middleinitial (middleinitial=?)

在 SQLite 中，連接是作為嵌套循環連接執行的，即數據庫遍歷一個表，並且對於每一行，從另一個表中搜尋匹配的行。

如果有索引，數據庫可以快速查找索引中的任何匹配項，然後轉到相應的表行以獲取所需的任何其他列的值。

在這種情況下，存在三個可能的索引。如果沒有任何統計資訊（將通過執行ANALYZE創建），數據庫會選擇最小的一個，以減少 I/O。但是，middleinitial索引是沒有用的，因為它並沒有大大減少需要獲取的表行數；並且通過索引的額外步驟實際上增加了所需的 I/O，因為不再按順序讀取表行，而是隨機讀取。

如果沒有索引，則查找匹配行需要對第一個表的每一行的第二個表進行完整的表掃描。這太糟糕了，以至於數據庫估計值得為這個查詢創建然後刪除一個臨時索引。這個臨時（“AUTOMATIC”）索引是在用於搜尋的所有列上創建的。COUNT(*) 操作不需要來自任何其他列的值，所以這個索引恰好是一個覆蓋索引，這意味著不必實際查找與索引條目對應的表行，這樣更節省了我/O。

為了加快這個查詢，永久創建這個索引，這樣就不再需要建構一個臨時索引：

CREATE INDEX uk_all_names ON fakenames_uk(surname, givenname, middleinitial);

EXPLAIN QUERY PLAN
SELECT count(*)
FROM fakenames_uk
JOIN fakenames_usa USING (givenname, middleinitial, surname);

0|0|1|SCAN TABLE fakenames_usa
0|1|0|SEARCH TABLE fakenames_uk USING COVERING INDEX uk_all_names (surname=? AND givenname=? AND middleinitial=?)

不再需要索引 on surname，因為三列索引可用於此列的任何查找。

如果您只在此列上進行查找，則索引givenname可能會很有用。

上的索引middleinitial總是毫無價值的：如果只掃描整個表，搜尋 26 個可能值之一的查詢會更快。

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