EXPLAIN QUERY PLAN w SQLite: jak czytać plan i znal…

Q: Czym różni się SCAN od SEARCH w wyniku?

SCAN oznacza, że SQLite czyta każdy wiersz tabeli lub indeksu: w małych tabelach to nie problem, w dużych jest kosztowne. SEARCH oznacza, że przeskakuje bezpośrednio do pasujących wierszy dzięki indeksowi lub kluczowi głównemu. W dużej tabeli prawie zawsze chcesz widzieć SEARCH dla kolumn, po których filtrujesz.

Q: Jak sprawdzić, czy moje zapytanie używa indeksu?

Uruchom EXPLAIN QUERY PLAN dla zapytania i poszukaj w wyniku USING INDEX albo USING COVERING INDEX . Jeśli widzisz tylko SCAN bez wzmianki o indeksie, zapytanie przeszukuje całą tabelę i indeks najpewniej by pomógł.

Q: Jaka jest różnica między EXPLAIN a EXPLAIN QUERY PLAN?

EXPLAIN pokazuje niskopoziomowy kod bajtowy generowany przez maszynę wirtualną SQLite: przydatny przy badaniu wnętrza silnika, rzadko przy strojeniu zapytań. EXPLAIN QUERY PLAN pokazuje czytelne podsumowanie dostępu do tabel i użycia indeksów. Przy pracy nad wydajnością prawie zawsze potrzebujesz EXPLAIN QUERY PLAN.

EXPLAIN QUERY PLAN pokazuje, jak zostanie wykonane zapytanie

Zanim zaczniesz stroić wolne zapytanie, musisz wiedzieć, co SQLite faktycznie robi. EXPLAIN QUERY PLAN wypisuje krótkie podsumowanie strategii wybranej przez planer: których tabel dotyka, w jakiej kolejności i jakich indeksów używa (o ile w ogóle). Samo zapytanie się nie wykonuje, dostajesz tylko plan.

Wystarczy dopisać te słowa kluczowe przed dowolną instrukcją:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    country TEXT
);

INSERT INTO users (email, country) VALUES
    ('a@x.com', 'US'),
    ('b@x.com', 'UK'),
    ('c@x.com', 'US');

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'a@x.com';

Wynik wygląda mniej więcej tak:

QUERY PLAN
`--SEARCH users USING INDEX sqlite_autoindex_users_1 (email=?)

Ta jedna linia mówi całkiem dużo: SQLite wykonuje SEARCH (a nie skan) w tabeli users, używając automatycznie utworzonego indeksu unikalnego na email, z email jako kluczem wyszukiwania. Dokładnie tego się spodziewasz.

SCAN a SEARCH: pierwsza rzecz do przeczytania

Każda linia planu zaczyna się od SCAN albo SEARCH. To rozróżnienie jest najważniejszym sygnałem w całym wyniku.

SCAN <table>: SQLite czyta każdy wiersz tabeli (albo każdy wpis indeksu). Koszt rośnie razem z rozmiarem tabeli.
SEARCH <table> USING ...: SQLite przeskakuje bezpośrednio do pasujących wierszy przez indeks lub klucz główny. Koszt rośnie z rozmiarem wyniku, a nie tabeli.

Oto porównanie obok siebie. Jedna kolumna ma indeks, druga nie:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    status TEXT
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

INSERT INTO orders (customer_id, status) VALUES
    (1, 'paid'), (2, 'pending'), (1, 'paid'), (3, 'shipped');

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE status = 'paid';

Pierwszy plan zgłasza SEARCH orders USING INDEX idx_orders_customer. Drugi zgłasza SCAN orders: na status nie ma indeksu, więc SQLite czyta każdy wiersz. W małej tabeli tego nie zauważysz, w tabeli z milionem wierszy to różnica między milisekundami a sekundami.

SCAN nie zawsze jest błędem. W malutkich tabelach słownikowych albo w zapytaniach, które naprawdę zwracają większość wierszy, skanowanie to właściwy plan. Ale w dużej tabeli z selektywnym filtrem SCAN to sygnał, żeby dodać indeks.

Jak potwierdzić, że indeks jest używany

Szukaj frazy USING INDEX <name> (albo USING COVERING INDEX <name>, o tym niżej). Jeśli utworzono indeks z nadzieją, że planer go wybierze, tak to sprawdzisz:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    created_at TEXT
);

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id = 42;

Powinno się pojawić SEARCH events USING INDEX idx_events_user (user_id=?). Jeśli zamiast tego plan mówi SCAN events, coś blokuje planerowi użycie indeksu. Typowe przyczyny to opakowanie kolumny w funkcję (WHERE lower(user_id) = ...), porównywanie różnych typów albo LIKE '%foo%' z symbolem wieloznacznym na początku.

Szybki test:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    kind TEXT
);

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id + 0 = 42;

To + 0 unieważnia indeks: plan wraca do SCAN events. Każde wyrażenie na indeksowanej kolumnie działa tak samo.

Indeksy pokrywające wyglądają inaczej

Gdy indeks zawiera wszystkie kolumny potrzebne zapytaniu, SQLite może odpowiedzieć na nie z samego indeksu, bez zaglądania do tabeli. Plan zgłasza wtedy USING COVERING INDEX:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT,
    name TEXT,
    price REAL
);

CREATE INDEX idx_products_sku_price ON products(sku, price);

EXPLAIN QUERY PLAN
SELECT price FROM products WHERE sku = 'ABC-123';

Plan: SEARCH products USING COVERING INDEX idx_products_sku_price (sku=?). Zapytanie prosi o price, a indeks już przechowuje sku i price, więc SQLite w ogóle nie czyta tabeli. Indeksy pokrywające to najszybszy plan wyszukiwania, jaki można dostać. Warto o nich pamiętać, gdy wybierasz, które kolumny indeksować razem.

Jak czytać plany złączeń

Przy złączeniach plany robią się ciekawe. Każda linia planu odpowiada jednej tabeli w złączeniu, a kolejność linii to kolejność, w jakiej SQLite je odwiedza. Pierwsza tabela to tabela zewnętrzna, kolejne są przeszukiwane raz na każdy wiersz zewnętrzny.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total REAL
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN
SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 7;

Typowy plan:

QUERY PLAN
|--SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH o USING INDEX idx_orders_customer (customer_id=?)

Czytaj od góry: SQLite znajduje jednego klienta po kluczu głównym, a potem dla tego klienta wyszukuje pasujące zamówienia przez indeks na customer_id. Obie linie to SEARCH, bez pełnych skanów, czyli dokładnie to, czego chcesz.

Gdyby w drugiej linii pojawiło się SCAN o, każde wyszukanie klienta wywoływałoby pełne przejście po orders. W dużej tabeli to katastrofa. Rozwiązaniem prawie zawsze jest indeks na kolumnie złączenia.

Zapytania złożone i podzapytania

Plany dla UNION, EXCEPT i podzapytań są zagnieżdżone. Każda gałąź pojawia się z wcięciem pod swoim rodzicem:

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT x FROM a
UNION ALL
SELECT x FROM b;

Zobaczysz dwa wiersze potomne pod nagłówkiem COMPOUND QUERY, po jednym na gałąź. Podzapytania i CTE działają podobnie: każde dostaje własny, wcięty węzeł planu, a każdy z nich czytasz tym samym kryterium SCAN kontra SEARCH.

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT * FROM a
WHERE x IN (SELECT x FROM b WHERE x > 10);

Podzapytanie staje się osobnym węzłem planu („LIST SUBQUERY” lub podobnym) z własną strategią dostępu. Te same kontrole stosuj na każdym poziomie.

EXPLAIN a EXPLAIN QUERY PLAN

To dwie różne rzeczy i często się je myli.

CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT);

EXPLAIN
SELECT * FROM t WHERE id = 1;

EXPLAIN (bez QUERY PLAN) wyrzuca kod bajtowy, który wykona maszyna wirtualna SQLite: dziesiątki niskopoziomowych instrukcji, takich jak OpenRead, SeekRowid, Column, ResultRow. Przydaje się, gdy debugujesz sam silnik. Do strojenia zapytań prawie nigdy.

EXPLAIN QUERY PLAN to czytelne podsumowanie, którego naprawdę potrzebujesz. W razie wątpliwości zawsze sięgaj po EXPLAIN QUERY PLAN.

Sposób pracy z wolnymi zapytaniami

Gdy zapytanie jest wolne, cykl wygląda tak:

Uruchom dla niego EXPLAIN QUERY PLAN.
Dla każdej linii z tabelą zapytaj: to SCAN czy SEARCH? W dużej tabeli podejrzany jest SCAN.
Jeśli SCAN filtruje po jakiejś kolumnie, rozważ indeks na tej kolumnie.
W złączeniach upewnij się, że tabele w pętli wewnętrznej używają SEARCH USING INDEX na kolumnie złączenia.
Po dodaniu indeksu uruchom ponownie EXPLAIN QUERY PLAN. Plan powinien się zmienić. Jeśli się nie zmienił, planer uznał, że indeks nie jest wart użycia, zwykle dlatego, że tabela jest mała albo filtr nie jest wystarczająco selektywny.

Przykład kroku 5:

CREATE TABLE logs (
    id INTEGER PRIMARY KEY,
    level TEXT,
    message TEXT
);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- SCAN logs

CREATE INDEX idx_logs_level ON logs(level);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- SEARCH logs USING INDEX idx_logs_level (level=?)

Plan zmienił się z SCAN na SEARCH. To znak, że indeks spełnia swoje zadanie. (W świeżej, prawie pustej tabeli planer może nadal skanować, bo danych jest za mało, żeby opłacało się sięgać po indeks. Wypełnij tabelę albo uruchom ANALYZE, a wybór często się odwraca.)

Czego plan ci nie powie

EXPLAIN QUERY PLAN opisuje strategię, a nie koszt. Nie powie, że zapytanie trwało 800 ms albo zwróciło 50 000 wierszy. Do tego potrzebujesz pomiaru czasu (.timer on w CLI) i liczby wierszy. Plan i pomiar czasu uzupełniają się: plan mówi, dlaczego zapytanie jest wolne, a timer mówi, czy w ogóle jest.

Warto znać jeszcze dwa ograniczenia:

Plan może się zmieniać wraz ze wzrostem danych. Zapytanie, które bez problemu skanowało tabelę ze 100 wierszami, będzie potrzebować indeksu, gdy tabela dojdzie do miliona wierszy. Sprawdzaj plany na danych o rozmiarze produkcyjnym, a nie na danych testowych z własnego komputera.
Planer korzysta ze statystyk zbieranych przez ANALYZE. Bez nich używa wartości domyślnych, które nie zawsze są dobre. Nieaktualne lub brakujące statystyki to częsta przyczyna zaskakujących planów.

Dalej: ANALYZE i VACUUM

Planer zapytań podejmuje decyzje na podstawie statystyk o tabelach i indeksach. Jeśli tych statystyk brakuje albo są nieaktualne, nawet idealnie zaindeksowany schemat może dać zły plan. ANALYZE pozwala utrzymywać je na bieżąco, a VACUUM to towarzyszące mu polecenie do odzyskiwania miejsca i defragmentacji pliku bazy danych. O tym w następnej części.

Najczęściej zadawane pytania

Co robi EXPLAIN QUERY PLAN w SQLite?

Prosi SQLite o opisanie, jak wykonałby zapytanie, bez faktycznego uruchamiania go. Wynik pokazuje, które tabele są przeszukiwane, jakie indeksy są używane i w jakiej kolejności wykonywane są złączenia. Wystarczy poprzedzić dowolne SELECT, INSERT, UPDATE lub DELETE słowami EXPLAIN QUERY PLAN, żeby zobaczyć plan.

Czym różni się SCAN od SEARCH w wyniku?

SCAN oznacza, że SQLite czyta każdy wiersz tabeli lub indeksu: w małych tabelach to nie problem, w dużych jest kosztowne. SEARCH oznacza, że przeskakuje bezpośrednio do pasujących wierszy dzięki indeksowi lub kluczowi głównemu. W dużej tabeli prawie zawsze chcesz widzieć SEARCH dla kolumn, po których filtrujesz.

Jak sprawdzić, czy moje zapytanie używa indeksu?

Uruchom EXPLAIN QUERY PLAN dla zapytania i poszukaj w wyniku USING INDEX <name> albo USING COVERING INDEX <name>. Jeśli widzisz tylko SCAN <table> bez wzmianki o indeksie, zapytanie przeszukuje całą tabelę i indeks najpewniej by pomógł.

Jaka jest różnica między EXPLAIN a EXPLAIN QUERY PLAN?

EXPLAIN pokazuje niskopoziomowy kod bajtowy generowany przez maszynę wirtualną SQLite: przydatny przy badaniu wnętrza silnika, rzadko przy strojeniu zapytań. EXPLAIN QUERY PLAN pokazuje czytelne podsumowanie dostępu do tabel i użycia indeksów. Przy pracy nad wydajnością prawie zawsze potrzebujesz EXPLAIN QUERY PLAN.

Powiązane zagadnienia