Исполняемые доки SQLite: Частичные индексы

Q: Можно ли через частичный индекс обеспечить уникальность?

Да. CREATE UNIQUE INDEX ... WHERE ... следит за уникальностью только среди строк, попадающих под фильтр. Классика жанра — «одна активная запись на пользователя»: soft-deleted строки в индекс не попадают, поэтому удалённых записей с одинаковым ключом может быть сколько угодно, а живая — только одна.

Частичный индекс охватывает только часть строк

Обычный индекс хранит запись для каждой строки таблицы. Частичный индекс (partial index) — только для тех строк, которые попадают под условие WHERE, заданное при его создании. Индекс получается компактнее, страниц для обхода меньше, а при вставках и обновлениях, не затрагивающих отфильтрованное подмножество, работы тоже меньше.

Синтаксис — обычный CREATE INDEX с приписанным к нему 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 содержит записи только для строк, где status = 'pending'. Отгруженных, отменённых и возвращённых заказов в нём нет вообще. Если 95% таблицы orders — это история, а запросы у вас в основном по активным заказам, то индекс получится в 20 раз меньше при той же скорости выборки.

Когда планировщик действительно задействует частичный индекс

Частичный индекс пригоден только тогда, когда SQLite может доказать, что запрос ограничен ровно тем подмножеством строк, которое покрывает индекс. Самый надёжный способ — повторить условие 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';

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;

В плане должно появиться USING INDEX idx_orders_pending. Уберите из запроса условие status = 'pending' — и планировщик откатится к полному сканированию таблицы: у него нет способа понять, что запрос всё ещё попадает в проиндексированное подмножество.

Правило простое: WHERE запроса должен следовать из WHERE индекса. Равенство по тому же столбцу и значению — безопасный и очевидный случай. С неравенствами и OR всё запутаннее — проверяйте через EXPLAIN QUERY PLAN.

Зачем это нужно — три выигрыша

Три конкретные причины, почему частичный индекс в SQLite окупается:

Меньше места на диске. Хранятся только подходящие строки. Если «горячими» считаются 1% таблицы, индекс займёт примерно 1% от полного.
Дешевле запись. При вставках и обновлениях индекс трогается, только если строка попадает под фильтр. Вставка со status = 'shipped' в таблицу выше вообще не задевает idx_orders_pending.
Та же скорость поиска. Поиск по B-дереву логарифмичен от размера индекса. Индекс меньше — поиск чуть быстрее, но основной выигрыш в окружении: меньше промахов кэша, меньше I/O.

Если данные в столбце сильно перекошены — у большинства строк одно значение, а вам интересны только редкие остальные — это классический сценарий для частичного индекса.

Частичный уникальный индекс (та самая killer-фича)

Обычное ограничение UNIQUE действует на все строки таблицы. И это становится проблемой, как только вы вводите soft delete:

-- Ошибка: есть две строки с email = 'a@x.com', хотя одна из них удалена.
CREATE UNIQUE INDEX idx_users_email ON users(email);

Частичный уникальный индекс в SQLite позволяет накладывать ограничение уникальности только на те строки, которые действительно важны:

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'),  -- мягко удалён
    ('a@x.com', '2026-01-05'),  -- снова мягко удалён
    ('a@x.com', NULL);          -- одна активная строка, разрешено

SELECT email, deleted_at FROM users;

Три строки, один и тот же email — и никакого нарушения ограничения, потому что в проверке уникальности участвует только строка с deleted_at IS NULL. Попробуйте вставить вторую «живую» строку с таким же email — и SQLite тут же выдаст UNIQUE constraint failed.

Этот приём встречается повсюду: одна активная подписка на клиента, один основной адрес у пользователя, один открытый счёт на заказ. Частичный уникальный индекс в SQLite описывает такую логику напрямую.

Индексирование с учётом NULL

NULL ведёт себя в индексах своеобразно. Часто хочется «вообще игнорировать NULL-ы» — например, есть разрежённая колонка external_id, где большинство строк содержат NULL, но заполненные значения должны быть уникальными:

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
    ('Виджет',  'EXT-001'),
    ('Гаджет',  NULL),
    ('Штуковина', NULL),
    ('Звёздочка', 'EXT-002');

SELECT name, external_id FROM products;

Два значения NULL мирно сосуществуют, а строки EXT-001 и EXT-002 гарантированно уникальны. К тому же сам индекс получается компактнее — строки с NULL в нём вообще не хранятся, — поэтому поиск по external_id остаётся быстрым даже на разросшейся таблице.

Что можно использовать в условии фильтра

Выражение WHERE у частичного индекса довольно строгое. В нём допустимы:

столбцы самой индексируемой таблицы;
литералы (константы);
небольшой набор детерминированных встроенных функций.

А вот что использовать нельзя:

другие таблицы;
подзапросы;
недетерминированные функции вроде random() или CURRENT_TIMESTAMP;
параметры и переменные.

Логика тут понятная: SQLite вычисляет это условие при каждой вставке и обновлении строки, и результат обязан быть стабильным. Поэтому такой вариант сработает:

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');

А вот WHERE created_at > date('now') уже не подойдёт — date('now') меняется со временем, и набор проиндексированных строк будет «уплывать» из-под SQLite.

Как проверить себя: рабочий чек-лист

Когда добавляете частичный индекс, пройдитесь по трём пунктам:

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. Использует ли планировщик индекс?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'queued' ORDER BY run_at LIMIT 10;

-- 2. Игнорирует ли он запросы вне условия фильтра?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'done' ORDER BY run_at;

-- 3. Пропускает ли он запросы, в которых фильтр полностью отсутствует?
EXPLAIN QUERY PLAN
SELECT id FROM jobs ORDER BY run_at;

Запрос 1 должен использовать idx_jobs_runnable. Запросы 2 и 3 должны откатиться к сканированию (или к другому индексу, если он у вас есть). Если планировщик вдруг выбрал частичный индекс там, где вы этого не ждали, перечитайте условие фильтра — возможно, оно шире, чем кажется.

Когда частичный индекс не нужен

Частичный индекс — инструмент острый. Причины пройти мимо:

Условие покрывает большую часть таблицы. Если "активные" — это 90% строк, то частичный индекс превращается в обычный индекс, только с лишними телодвижениями. Проще проиндексировать столбец как есть.
В запросах фильтр не повторяется буквально. Если ваш код использует ORM, который собирает WHERE status IN (?, ?, ?), или вычисляет условие динамически, планировщик чаще всего не распознает совпадение. Проверяйте через EXPLAIN QUERY PLAN, а не на веру.
«Горячая» подмножество меняется со временем. Частичный индекс на "заказы за последние 30 дней" звучит соблазнительно, но так не получится — выражение в фильтре должно быть детерминированным. Придётся либо периодически перестраивать индекс, либо менять схему (завести отдельную таблицу recent_orders или флаг archived, который вы переключаете ночным заданием).

Когда условие фильтра стабильно и отсекает небольшой кусок большой таблицы, частичный индекс — один из самых эффективных приёмов тюнинга в SQLite.

Дальше: читаем план запроса

Почти вся эта страница опиралась на EXPLAIN QUERY PLAN, чтобы убедиться, что индекс действительно сработал. Эта команда заслуживает отдельной статьи: как читать её вывод, что означают ключевые слова и как отличить аккуратный поиск по индексу от притаившегося полного сканирования. Об этом — в следующий раз.

Часто задаваемые вопросы

Что такое частичный индекс в SQLite?

Это индекс, в который попадают только строки, удовлетворяющие условию WHERE, заданному при создании. Пишете CREATE INDEX name ON table(col) WHERE condition — и SQLite хранит записи только для тех строк, где условие истинно. В итоге индекс получается компактнее, запись быстрее, а скорость чтения для подходящих запросов остаётся прежней.

Когда стоит выбрать частичный индекс вместо обычного?

Когда вы постоянно запрашиваете небольшой срез большой таблицы — необработанные заказы, активных пользователей, незавершённые задачи. Индекс по этому срезу остаётся крошечным, а вставки и обновления остальных строк его вообще не затрагивают. Важный нюанс: если в ваших запросах нет того же условия WHERE, что и в индексе, планировщик его просто не подхватит.

Можно ли через частичный индекс обеспечить уникальность?

Да. CREATE UNIQUE INDEX ... WHERE ... следит за уникальностью только среди строк, попадающих под фильтр. Классика жанра — «одна активная запись на пользователя»: soft-deleted строки в индекс не попадают, поэтому удалённых записей с одинаковым ключом может быть сколько угодно, а живая — только одна.

Связанные концепции

Частичные индексы SQLite: CREATE INDEX ... WHERE