Dokumentacja SQLite z przykładami: Indeksy częściowe

Q: Czy indeks częściowy może wymuszać unikalność?

Tak. CREATE UNIQUE INDEX ... WHERE ... wymusza unikalność tylko w wierszach pasujących do filtra. Klasyczne zastosowanie to 'jeden aktywny rekord na użytkownika': wiersze usunięte miękko są wykluczone, więc możesz mieć kilka usuniętych wpisów z tym samym kluczem, ale tylko jeden aktywny.

Indeks częściowy obejmuje tylko część wierszy

Zwykły indeks ma wpis dla każdego wiersza tabeli. Indeks częściowy ma wpisy tylko dla wierszy pasujących do klauzuli WHERE, którą podajesz przy jego tworzeniu. Mniejszy indeks, mniej stron do przejrzenia i mniej pracy przy każdym wstawieniu i aktualizacji, które nie dotyczą indeksowanego wycinka.

Składnia to zwykłe CREATE INDEX z dopisanym WHERE:

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

CREATE INDEX idx_orders_pending
    ON orders(created_at)
    WHERE status = 'pending';

idx_orders_pending zawiera wpisy tylko dla wierszy, w których status = 'pending'. Zamówień wysłanych, anulowanych i zwróconych w ogóle w nim nie ma. Jeśli 95% tabeli orders to historia, a pytasz głównie o otwarte zamówienia, dostajesz indeks 20× mniejszy przy tej samej szybkości zapytań.

Kiedy planer faktycznie go użyje

Indeksu częściowego można użyć tylko wtedy, gdy SQLite potrafi udowodnić, że zapytanie ogranicza się do tych samych wierszy, które obejmuje indeks. Najprościej powtórzyć w zapytaniu klauzulę WHERE indeksu:

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

CREATE INDEX idx_orders_pending
    ON orders(created_at)
    WHERE status = 'pending';

INSERT INTO orders(customer_id, status, total, created_at) VALUES
    (1, 'pending',  42.0, '2026-01-10'),
    (2, 'shipped',  99.0, '2026-01-11'),
    (3, 'pending', 120.0, '2026-01-12');

EXPLAIN QUERY PLAN
SELECT id FROM orders
WHERE status = 'pending'
ORDER BY created_at;

Plan powinien wspominać USING INDEX idx_orders_pending. Usuń status = 'pending' z zapytania, a planer wróci do pełnego skanu tabeli: nie ma jak się dowiedzieć, że zapytanie nie wychodzi poza indeksowany podzbiór.

Praktyczna zasada: WHERE zapytania musi implikować WHERE indeksu. Równość na tej samej kolumnie i wartości to bezpieczny, oczywisty przypadek. Nierówności i OR są bardziej zawiłe, więc sprawdzaj je przez EXPLAIN QUERY PLAN.

Po co to wszystko: trzy korzyści

Trzy konkretne powody, dla których indeksy częściowe się opłacają:

Mniej miejsca na dysku. Zapisywane są tylko pasujące wiersze. Gdy "gorący" jest 1% tabeli, indeks zajmuje mniej więcej 1% pełnego indeksu.
Tańsze zapisy. Wstawienia i aktualizacje dotykają indeksu tylko wtedy, gdy wiersz pasuje do filtra. Wstawienie wiersza z status = 'shipped' do powyższej tabeli w ogóle nie rusza idx_orders_pending.
Ta sama szybkość wyszukiwania. Wyszukiwanie w B-drzewie jest logarytmiczne względem rozmiaru indeksu. Mniejszy indeks to odrobinę szybsze wyszukiwanie, ale większy zysk jest dookoła: mniej chybień w pamięci podręcznej i mniej operacji I/O.

Jeśli rozkład wartości w kolumnie jest mocno nierówny (większość wierszy ma jedną wartość, a interesują cię tylko rzadkie pozostałe), to podręcznikowy przypadek dla indeksu częściowego.

Częściowe indeksy unikalne (najmocniejsza funkcja)

Zwykłe ograniczenia UNIQUE dotyczą każdego wiersza. To problem od momentu, gdy wprowadzasz miękkie usuwanie:

-- Błąd: są dwa wiersze z email = 'a@x.com', choć jeden jest usunięty.
CREATE UNIQUE INDEX idx_users_email ON users(email);

Częściowy indeks unikalny pozwala wymusić unikalność tylko w wierszach, które mają znaczenie:

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

CREATE UNIQUE INDEX idx_users_email_active
    ON users(email)
    WHERE deleted_at IS NULL;

INSERT INTO users(email, deleted_at) VALUES
    ('a@x.com', '2026-01-01'),  -- usunięty miękko
    ('a@x.com', '2026-01-05'),  -- znów usunięty miękko
    ('a@x.com', NULL);          -- jeden aktywny wiersz, dozwolony

SELECT email, deleted_at FROM users;

Trzy wiersze, ten sam e-mail i żadnego naruszenia ograniczenia, bo w sprawdzaniu unikalności bierze udział tylko wiersz z deleted_at IS NULL. Spróbuj wstawić drugi aktywny wiersz z tym samym e-mailem, a SQLite zgłosi UNIQUE constraint failed.

Ten wzorzec pojawia się wszędzie: jedna aktywna subskrypcja na klienta, jeden główny adres na użytkownika, jedna otwarta faktura na zamówienie. Częściowe indeksy unikalne wyrażają go wprost.

Indeksowanie z pominięciem NULL

NULL dziwnie współgra z indeksami. Częsty cel to "całkowicie zignoruj NULL": na przykład masz rzadko wypełnioną kolumnę external_id, w której większość wierszy to NULL, ale wypełnione wartości muszą być unikalne:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    external_id TEXT
);

CREATE UNIQUE INDEX idx_products_external_id
    ON products(external_id)
    WHERE external_id IS NOT NULL;

INSERT INTO products(name, external_id) VALUES
    ('Widżet',  'EXT-001'),
    ('Gadżet',  NULL),
    ('Dynks', NULL),
    ('Zębatka', 'EXT-002');

SELECT name, external_id FROM products;

Dwa NULL spokojnie współistnieją, a wiersze EXT-001 i EXT-002 mają gwarancję unikalności. Indeks jest też mniejszy, bo wiersze z NULL w ogóle nie są w nim zapisywane, więc wyszukiwanie po external_id jest szybkie nawet wtedy, gdy tabela rośnie.

Do czego może odwoływać się filtr

Klauzula WHERE indeksu częściowego ma ograniczenia. Może odwoływać się do:

Kolumn indeksowanej tabeli.
Stałych literałów.
Niewielkiego zestawu deterministycznych funkcji wbudowanych.

Nie może odwoływać się do:

Innych tabel.
Podzapytań.
Funkcji niedeterministycznych, takich jak random() czy CURRENT_TIMESTAMP.
Parametrów ani zmiennych.

To ma sens: SQLite musi obliczać filtr przy każdym wstawieniu i aktualizacji wiersza, a wynik musi być stały. Dlatego to działa:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_type TEXT NOT NULL,
    priority INTEGER NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_events_high_priority
    ON events(created_at)
    WHERE priority >= 5 AND event_type IN ('error', 'critical');

Ale WHERE created_at > date('now') już nie, bo date('now') zmienia się w czasie, więc zbiór indeksowanych wierszy przesuwałby się SQLite pod nogami.

Sprawdzanie krok po kroku

Gdy dodajesz indeks częściowy, przejdź przez trzy sprawdzenia:

CREATE TABLE jobs (
    id INTEGER PRIMARY KEY,
    queue TEXT NOT NULL,
    status TEXT NOT NULL,
    run_at TEXT NOT NULL
);

CREATE INDEX idx_jobs_runnable
    ON jobs(run_at)
    WHERE status = 'queued';

INSERT INTO jobs(queue, status, run_at) VALUES
    ('default', 'queued',  '2026-01-10 10:00'),
    ('default', 'done',    '2026-01-10 09:00'),
    ('email',   'queued',  '2026-01-10 11:00');

-- 1. Czy planer wybiera indeks?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'queued' ORDER BY run_at LIMIT 10;

-- 2. Czy ignoruje zapytania spoza filtra?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'done' ORDER BY run_at;

-- 3. Czy pomija zapytania, w których w ogóle nie ma filtra?
EXPLAIN QUERY PLAN
SELECT id FROM jobs ORDER BY run_at;

Zapytanie 1 powinno użyć idx_jobs_runnable. Zapytania 2 i 3 powinny wrócić do skanu (albo innego indeksu, jeśli go masz). Jeśli planer wybiera indeks częściowy dla zapytania, którego się nie spodziewasz, przeczytaj filtr jeszcze raz: może być szerszy, niż myślisz.

Kiedy z niego nie korzystać

Indeksy częściowe to ostre narzędzie. Powody, by z nich zrezygnować:

Filtr pasuje do większości tabeli. Jeśli "aktywne" to 90% wierszy, indeks częściowy to zwykły indeks z dodatkowymi krokami. Po prostu zindeksuj kolumnę.
Zapytania nie zawierają filtra dosłownie. Jeśli kod używa ORM, który buduje WHERE status IN (?, ?, ?) albo oblicza filtr dynamicznie, planer często nie rozpozna dopasowania. Testuj przez EXPLAIN QUERY PLAN, nie zakładaj.
Gorący podzbiór zmienia się w czasie. Indeks częściowy na "zamówieniach z ostatnich 30 dni" brzmi kusząco, ale nie da się go wyrazić, bo filtr musi być deterministyczny. Trzeba by przebudowywać indeks albo wybrać inny schemat (osobną tabelę recent_orders lub flagę logiczną archived przestawianą co noc).

Gdy filtr jest stały i pasuje do małego wycinka dużej tabeli, indeksy częściowe są jednym z najskuteczniejszych sposobów strojenia wydajności w SQLite.

Dalej: czytanie planów zapytań

Ta strona w dużej mierze opierała się na EXPLAIN QUERY PLAN, by potwierdzić, że indeks naprawdę został użyty. To narzędzie zasługuje na osobną stronę: jak czytać jego wynik, co znaczą słowa kluczowe i jak odróżnić udane wyszukiwanie w indeksie od podstępnego pełnego skanu. O tym dalej.

Najczęściej zadawane pytania

Czym jest indeks częściowy w SQLite?

Indeks częściowy obejmuje tylko wiersze spełniające klauzulę WHERE podaną przy jego tworzeniu. Piszesz CREATE INDEX name ON table(col) WHERE condition, a SQLite zapisuje wpisy tylko dla wierszy, w których warunek jest prawdziwy. Mniejszy indeks, szybsze zapisy i ta sama szybkość wyszukiwania dla zapytań pasujących do filtra.

Kiedy użyć indeksu częściowego zamiast pełnego?

Gdy raz za razem pytasz o mały wycinek dużej tabeli: oczekujące zamówienia, aktywnych użytkowników, nieprzetworzone zadania. Indeksowanie tylko tego wycinka utrzymuje indeks małym, a zapisy do pozostałych wierszy w ogóle go pomijają. Jeśli zapytania nie zawierają tego samego warunku WHERE co indeks, planer nie może go użyć.

Czy indeks częściowy może wymuszać unikalność?

Tak. CREATE UNIQUE INDEX ... WHERE ... wymusza unikalność tylko w wierszach pasujących do filtra. Klasyczne zastosowanie to 'jeden aktywny rekord na użytkownika': wiersze usunięte miękko są wykluczone, więc możesz mieć kilka usuniętych wpisów z tym samym kluczem, ale tylko jeden aktywny.

Powiązane zagadnienia