Dlaczego to zapytanie sqlite jest znacznie wolniejsze, gdy indeksuję kolumny?

Mam bazę danych sqlite z dwiema tabelami, każda zawierająca 50 000 wierszy, zawierającymi nazwiska (fałszywych) osób. Zbudowałem proste zapytanie, aby dowiedzieć się, ile jest imion (imię, inicjał środkowy, nazwisko) wspólnych dla obu tabel:

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;

Gdy nie ma żadnych indeksów poza kluczami głównymi (nieistotnymi dla tego zapytania), uruchamia się szybko:

[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

Ale jeśli dodam indeksy do trzech kolumn w każdej tabeli (łącznie sześć indeksów):

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

potem działa boleśnie powoli:

[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

Czy jest w tym jakikolwiek rym lub powód?

Oto wynik EXPLAIN QUERY PLANdla wersji bez indeksów:

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

Jest to z indeksami:

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

— bezpieczeństwo chiastyczne
źródło

Twoje indeksy nie obejmują. Wygląda na to, że indeksujesz każdą kolumnę osobno. Co się stanie, gdy utworzysz indeks obejmujący wszystkie trzy kolumny w indeksie ( middleinitial, surnamei givenname)?

— Randolph West

@Randoph West Rozumiem, co miałeś na myśli, ale nie używasz właściwej terminologii: „indeks obejmujący” to taki, który obejmuje również wybrane kolumny. Na przykład dla zapytania SELECT c FROM t WHERE a=1 AND b=2indeks t(a,b,c)obejmuje, ale t(a,b)nie jest. Zaletą pokrycia indeksów jest to, że cały wynik zapytania można wyciągnąć bezpośrednio z indeksu, podczas gdy niekryjące indeksy szybko znajdują odpowiednie wiersze, ale nadal muszą odwoływać się do danych z głównej tabeli, aby wybrać wartości.

— Arthur Tacca

W SQLite sprzężenia są wykonywane jako połączenia zagnieżdżone, tzn. Baza danych przechodzi przez jedną tabelę i dla każdego wiersza wyszukuje pasujące wiersze z drugiej tabeli.

Jeśli istnieje indeks, baza danych może szybko wyszukać wszystkie dopasowania w indeksie, a następnie przejść do odpowiedniego wiersza tabeli, aby uzyskać wartości innych potrzebnych kolumn.

W takim przypadku istnieją trzy możliwe indeksy. Bez żadnych danych statystycznych (które zostałyby utworzone przez uruchomienie ANALIZY ) baza danych wybiera najmniejszą, aby zredukować liczbę operacji we / wy. Jednak middleinitialindeks jest bezużyteczny, ponieważ nie znacznie zmniejszyć liczbę wierszy tabeli, które muszą być pobrane; a dodatkowy krok przez indeks faktycznie zwiększa potrzebne operacje we / wy, ponieważ wiersze tabeli nie są już odczytywane w kolejności, ale losowo.

Jeśli nie ma indeksu, wyszukiwanie pasujących wierszy wymagałoby pełnego skanowania drugiej tabeli dla każdego wiersza pierwszej tabeli. Byłoby to tak złe, że baza danych ocenia, że warto utworzyć, a następnie upuścić indeks tymczasowy tylko dla tego zapytania. Tymczasowy indeks („AUTOMATYCZNY”) jest tworzony na wszystkich kolumnach używanych do wyszukiwania. Operacja COUNT (*) nie wymaga wartości z żadnych innych kolumn, więc ten indeks bywa indeksem pokrywającym , co oznacza, że nie jest konieczne wyszukiwanie wiersza tabeli odpowiadającego wpisowi indeksu, co oszczędza jeszcze więcej I / O.

Aby przyspieszyć to zapytanie, utwórz ten indeks na stałe, aby nie było już konieczne tworzenie tymczasowego:

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=?)

Indeks włączony surnamenie jest już potrzebny, ponieważ indeks trzykolumnowy może być używany do wyszukiwania w tej kolumnie.
Indeks na givennamemoże być przydatny, jeśli będziesz wyszukiwał tylko w tej kolumnie.
Włączony indeks middleinitialjest zawsze bezwartościowy: zapytanie, które wyszukuje jedną z 26 możliwych wartości, jest szybsze, jeśli skanuje całą tabelę.

— CL.
źródło