Indeksy w SQLite: CREATE INDEX, kiedy ich używać i dlaczego

Czym naprawdę jest indeks

Indeks to osobna struktura danych, posortowane B-drzewo, która pozwala SQLite znajdować wiersze po wartości kolumny bez przeszukiwania całej tabeli. Bez niego zapytanie w rodzaju WHERE email = 'rosa@example.com' czyta każdy wiersz i sprawdza każdy z osobna. Z indeksem na email SQLite przechodzi przez drzewo w mniej więcej log(n) krokach i trafia prosto w dopasowanie.

To przyspieszenie nie jest za darmo. Indeks to kopia indeksowanej kolumny plus wskaźnik z powrotem do wiersza. Każde INSERT, UPDATE indeksowanej kolumny i DELETE musi zaktualizować także indeks. Zajętość dysku rośnie, a przepustowość zapisów trochę spada. Umowa jest taka: płacisz przy zapisach, a przy odczytach oszczędzasz znacznie więcej.

Tworzenie indeksu

Podstawowa składnia:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    name TEXT,
    created_at TEXT
);

INSERT INTO users (email, name, created_at) VALUES
    ('rosa@example.com', 'Rosa', '2026-01-10'),
    ('boris@example.com', 'Boris', '2026-02-14'),
    ('ada@example.com', 'Ada', '2026-03-01');

CREATE INDEX idx_users_email ON users(email);

SELECT * FROM users WHERE email = 'boris@example.com';

Konwencja nazw: większość zespołów używa idx_<table>_<column>, żeby było jasne, do czego służy indeks. Nazwa musi być unikalna w całej bazie, a nie tylko w tabeli, dlatego zawiera nazwę tabeli.

Żeby usunąć indeks:

DROP INDEX idx_users_email;

Indeksy to czyste rusztowanie wydajnościowe. Usunięcie indeksu nigdy nie wpływa na dane, tylko na szybkość zapytań.

Indeksy unikalne

Indeks unikalny pełni podwójną rolę: przyspiesza wyszukiwanie i pilnuje, żeby żadne dwa wiersze nie miały tej samej wartości indeksowanej.

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_accounts_username ON accounts(username);

INSERT INTO accounts (username) VALUES ('rosa');
INSERT INTO accounts (username) VALUES ('boris');
INSERT INTO accounts (username) VALUES ('rosa');

Trzecie wstawienie kończy się błędem UNIQUE constraint failed: accounts.username. SQLite automatycznie tworzy indeksy unikalne dla kolumn PRIMARY KEY i UNIQUE: zobaczysz je pod nazwami sqlite_autoindex_<table>_<n>. CREATE UNIQUE INDEX musisz pisać tylko wtedy, gdy ograniczenie nie zostało zadeklarowane w samej tabeli.

Co naprawdę robi planer

Dodanie indeksu nie gwarantuje, że SQLite go użyje. Planer zapytań wybiera strategię dla każdego zapytania, a to, co wybrał, zobaczysz przez EXPLAIN QUERY PLAN:

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

INSERT INTO orders (customer_id, total, status) VALUES
    (1, 99.0, 'shipped'), (2, 45.5, 'pending'),
    (1, 12.0, 'shipped'), (3, 200.0, 'pending');

CREATE INDEX idx_orders_customer ON orders(customer_id);

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

Szukaj w wyniku SEARCH ... USING INDEX idx_orders_customer: to znaczy, że indeks jest używany. Jeśli widzisz SCAN orders, planer uznał, że pełny skan tabeli jest tańszy (w malutkich tabelach często słusznie), albo kształt zapytania uniemożliwił mu użycie indeksu. Czytaniu takich planów poświęcona jest osobna strona, która pojawi się dalej.

Kiedy indeks nie zostanie użyty

Indeksy mają kilka dobrze znanych martwych punktów. Każde z poniższych unieważnia indeks na email:

-- Funkcja opakowuje kolumnę
SELECT * FROM users WHERE lower(email) = 'rosa@example.com';

-- Symbol wieloznaczny na początku LIKE
SELECT * FROM users WHERE email LIKE '%@example.com';

-- Niezgodność typów wymusza konwersję
SELECT * FROM users WHERE email = 12345;

B-drzewo jest posortowane według surowej wartości email, więc wszystko, co przekształca kolumnę w trakcie zapytania, wymusza skan. Rozwiązania bywają różne: przechowuj dane już znormalizowane (kolumna email_lower), użyj indeksu na wyrażeniu (CREATE INDEX idx ON users(lower(email))) albo do dopasowania podciągów użyj wyszukiwania pełnotekstowego SQLite.

Indeksy pokrywające

Jeśli indeks zawiera każdą kolumnę potrzebną zapytaniu, SQLite może na nie odpowiedzieć bez zaglądania do tabeli: to indeks pokrywający. Sztuczka polega na dołączeniu dodatkowych kolumn do definicji indeksu:

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

INSERT INTO events (user_id, type, created_at) VALUES
    (1, 'login', '2026-01-01'),
    (1, 'click', '2026-01-02'),
    (2, 'login', '2026-01-03');

CREATE INDEX idx_events_user_type ON events(user_id, type);

EXPLAIN QUERY PLAN
SELECT user_id, type FROM events WHERE user_id = 1;

Ponieważ obie kolumny, o które prosi zapytanie, są w indeksie, SQLite zgłasza USING COVERING INDEX. Nie trzeba pobierać wiersza. Indeksy pokrywające to jedna z najskuteczniejszych optymalizacji często używanych ścieżek odczytu, a ceną jest większy indeks. Indeksy wielokolumnowe to osobny temat, porządnie omówiony na następnej stronie.

Wyświetlanie i sprawdzanie indeksów

Dwa sposoby, żeby zobaczyć, co istnieje:

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

CREATE INDEX idx_products_name ON products(name);

SELECT name, tbl_name, sql FROM sqlite_master WHERE type = 'index';

Dostajesz każdy indeks w bazie razem z jego instrukcją CREATE. Dla pojedynczej tabeli PRAGMA index_list('products'); pokazuje tylko jej indeksy, a PRAGMA index_info('idx_products_name'); pokazuje, które kolumny indeksuje każdy z nich. Wszystko, co zaczyna się od sqlite_autoindex_, zostało utworzone automatycznie dla ograniczenia PRIMARY KEY lub UNIQUE i nie da się tego usunąć.

Kiedy nie dodawać indeksu

Kilka sytuacji, w których dodanie indeksu pogarsza sprawę:

• Malutkie tabele. Kilkaset wierszy skanuje się w mikrosekundach. Planer i tak pewnie zignoruje indeks, a ty bez sensu dokładasz koszt zapisów.
• Kolumny często zapisywane i rzadko odpytywane. Każdy zapis aktualizuje każdy indeks. Indeksowanie kolumny, po której prawie nigdy nie filtrujesz, to czysty koszt.
• Kolumny o małej liczbie różnych wartości, same w sobie. Indeks na kolumnie status z trzema możliwymi wartościami niewiele zawęża. Może pomóc jako druga kolumna indeksu złożonego albo jako indeks częściowy, ale sam często nie jest wart zachodu.
• Już pokryte. Jeśli masz indeks na (a, b), nie potrzebujesz dodatkowo indeksu na (a). SQLite używa początkowych kolumn indeksu złożonego w zapytaniach, które filtrują tylko po a.

Uczciwa odpowiedź na pytanie „czy dodać ten indeks?” prawie zawsze brzmi: spróbuj, uruchom EXPLAIN QUERY PLAN, zmierz na realistycznych danych i zdecyduj.

Dalej: indeksy złożone

Indeks na jednej kolumnie załatwia wiele, ale prawdziwe zapytania często filtrują i sortują po kilku kolumnach naraz. Radzą sobie z tym indeksy złożone, czyli indeksy na (a, b, c), a kolejność kolumn ma w nich większe znaczenie, niż się ludziom wydaje. O tym na następnej stronie.