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

Один индекс — сразу несколько колонок

Составной индекс в sqlite (его ещё называют индексом по нескольким колонкам) — это один индекс, построенный сразу по двум или более колонкам. Создаётся он простым перечислением колонок в нужном порядке:

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

CREATE INDEX idx_orders_customer_status
    ON orders(customer_id, status);

INSERT INTO orders(customer_id, status, created_at, total) VALUES
    (1, 'paid',    '2026-01-10', 49.00),
    (1, 'paid',    '2026-02-03', 12.50),
    (1, 'pending', '2026-02-14',  9.99),
    (2, 'paid',    '2026-01-22', 80.00),
    (2, 'refunded','2026-02-01', 25.00);

SELECT * FROM orders WHERE customer_id = 1 AND status = 'paid';

Индекс idx_orders_customer_status хранит записи, отсортированные сначала по customer_id, а затем — по status внутри каждого клиента. Этот порядок и есть вся суть: всё остальное поведение составного индекса вытекает именно из него.

Ментальная модель: телефонная книга с сортировкой

Представьте старую телефонную книгу. Записи отсортированы по фамилии, а внутри одной фамилии — по имени. Именно так и выглядит индекс по (last_name, first_name).

Какие-то запросы выполняются мгновенно, а какие-то — нет:

«Найти всех с фамилией Петров» — легко, все Петровы лежат рядом.
«Найти Ивана Петрова» — легко: прыгаем к Петровым, дальше сканируем до Ивана.
«Найти всех Иванов» — медленно: придётся пролистать каждую страницу. Иваны разбросаны по всем фамилиям.

Составной индекс sqlite работает ровно так же. Первая колонка — главный ключ сортировки, а вторая упорядочивает только те записи, у которых значение первой колонки совпадает.

Правило leftmost prefix

SQLite может задействовать составной индекс только тогда, когда WHERE ограничивает левый префикс его колонок. Для индекса по (a, b, c):

Фильтр по a — индекс используется.
Фильтр по a и b — индекс используется.
Фильтр по a, b и c — индекс используется.
Фильтр только по b, только по c, или по b и c — индекс не используется.

Проверить это можно напрямую через EXPLAIN QUERY PLAN:

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

CREATE INDEX idx_events_user_kind_time
    ON events(user_id, kind, occurred_at);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id = 42 AND kind = 'login';

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE kind = 'login';

Первый план показывает SEARCH events USING INDEX idx_events_user_kind_time. Второй же скатывается до SCAN events — фильтрация только по kind пропускает ведущую колонку user_id, так что индекс для такого запроса бесполезен.

Порядок колонок в индексе sqlite — это архитектурное решение

Поскольку leftmost prefix играет ключевую роль, порядок перечисления колонок в CREATE INDEX — это осознанный выбор, а не вопрос стиля. Два практических правила:

Первой ставьте ту колонку, по которой фильтруете чаще всего. Именно она открывает доступ к индексу для максимально широкого круга запросов.
Сначала колонки с равенством, потом — с диапазоном. SQLite умеет точечно заходить в индекс через =, а затем сканировать непрерывный диапазон с помощью <, > или BETWEEN — но только по последней задействованной колонке.

CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    region TEXT NOT NULL,
    sold_at TEXT NOT NULL,
    amount REAL NOT NULL
);

-- Хорошо: сначала равенство по region, затем диапазон по sold_at.
CREATE INDEX idx_sales_region_time ON sales(region, sold_at);

INSERT INTO sales(region, sold_at, amount) VALUES
    ('EU', '2026-01-05', 120),
    ('EU', '2026-02-11', 80),
    ('US', '2026-01-09', 200),
    ('US', '2026-02-20', 150);

EXPLAIN QUERY PLAN
SELECT * FROM sales
WHERE region = 'EU' AND sold_at >= '2026-02-01';

План показывает SEARCH sales USING INDEX idx_sales_region_time (region=? AND sold_at>?). SQLite сразу прыгает к region = 'EU', а дальше идёт по диапазону дат. Поменяйте порядок колонок на (sold_at, region) — и тот же запрос будет вынужден просканировать все строки в диапазоне дат и для каждой перепроверить region.

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

Частый вопрос: что лучше — один составной индекс sqlite по (a, b) или два отдельных индекса по a и по b?

CREATE TABLE tickets (
    id INTEGER PRIMARY KEY,
    project_id INTEGER NOT NULL,
    assignee_id INTEGER NOT NULL,
    state TEXT NOT NULL
);

-- Вариант A: один составной индекс.
CREATE INDEX idx_tickets_proj_state ON tickets(project_id, state);

-- Вариант B (альтернатива): два индекса по одному столбцу.
-- CREATE INDEX idx_tickets_project ON tickets(project_id);
-- CREATE INDEX idx_tickets_state   ON tickets(state);

EXPLAIN QUERY PLAN
SELECT * FROM tickets
WHERE project_id = 7 AND state = 'open';

Для составного фильтра выигрывает индекс по нескольким колонкам — SQLite сразу прыгает к нужным записям (project_id, state). Когда же у вас два отдельных индекса по одной колонке, SQLite обычно берёт один из них, сужает выборку, а потом перепроверяет вторую колонку для каждой оставшейся строки. Иногда он умеет пересекать индексы, но составной индекс sqlite — куда более чистое решение, если колонки запрашиваются вместе.

Если же project_id и state фигурируют ещё и в отдельных запросах, имеет смысл держать оба варианта: составной — для совместного фильтра, и одноколоночный по state — для запросов, где фильтруется только это поле.

Покрывающие индексы (covering index)

Когда индекс содержит все колонки, нужные запросу, — и те, по которым идёт фильтрация, и те, что попадают в SELECT, — SQLite отвечает на запрос, вообще не заглядывая в таблицу. Это и есть covering index, и быстрее запрос уже не выполнится.

CREATE TABLE invoices (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    issued_at TEXT NOT NULL,
    total REAL NOT NULL,
    notes TEXT
);

CREATE INDEX idx_invoices_cover
    ON invoices(customer_id, issued_at, total);

INSERT INTO invoices(customer_id, issued_at, total, notes) VALUES
    (1, '2026-01-12', 100, 'первый'),
    (1, '2026-02-02', 250, NULL),
    (2, '2026-01-30',  75, 'срочно');

EXPLAIN QUERY PLAN
SELECT issued_at, total FROM invoices WHERE customer_id = 1;

План показывает USING COVERING INDEX idx_invoices_cover. Запрос читает issued_at и total прямо из индекса — notes и id ему не нужны, поэтому до самой таблицы дело даже не доходит. Добавить колонку в составной индекс только ради покрытия горячего запроса — вполне оправданный размен, если этот запрос крутится постоянно.

Составной уникальный индекс в SQLite

Составной индекс умеет ещё и обеспечивать уникальность по комбинации колонок. Это удобно, когда ни одна колонка по отдельности не уникальна, а вот их сочетание обязано быть таким:

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id  INTEGER NOT NULL,
    enrolled_at TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_enrollments_pair
    ON enrollments(student_id, course_id);

INSERT INTO enrollments VALUES (1, 100, '2026-01-05');
INSERT INTO enrollments VALUES (1, 101, '2026-01-05');  -- ок, другой курс
INSERT INTO enrollments VALUES (1, 100, '2026-02-01');  -- ошибка: пара повторяется

Третий INSERT падает с ошибкой UNIQUE constraint failed: enrollments.student_id, enrollments.course_id. Такая пара уже есть в индексе, и SQLite отказывается добавлять дубликат.

Подводные камни, о которых стоит знать

OR между неведущими колонками ломает индекс. Условие WHERE a = 1 OR b = 2 при индексе (a, b), как правило, вообще не сможет его использовать — SQLite вынужден рассматривать обе ветви по отдельности.
Функции над индексированными колонками отключают индекс. Запрос WHERE lower(email) = 'x' не воспользуется индексом по email. Индексируйте само выражение либо нормализуйте данные при вставке.
Индексы не бесплатны. Каждый индекс обновляется при любом INSERT, UPDATE (если меняются индексированные колонки) и DELETE. Три составных индекса на таблице с активной записью могут съесть основную часть стоимости записи.
После создания индексов запускайте ANALYZE. Планировщик SQLite опирается на статистику, которую собирает ANALYZE, чтобы выбрать между кандидатами-индексами. Без этой статистики он использует эвристики, и они не всегда оптимальны.

Рабочий процесс на практике

Когда тюните медленный запрос, цикл обычно выглядит так:

Запустите EXPLAIN QUERY PLAN на запросе и посмотрите, что SQLite делает прямо сейчас.
Если идёт скан — изучите WHERE. Где колонка для равенства? Где колонка для диапазона? Какие колонки выбираются?
Постройте составной индекс: сначала равенство, потом диапазон, плюс выбираемые колонки в конце, если covering index реально помогает.
Запустите ANALYZE.
Снова запустите EXPLAIN QUERY PLAN. Убедитесь, что план изменился и индекс действительно используется.
Замерьте время выполнения запроса до и после на репрезентативных данных.

Не пропускайте шаг 6 — себе дороже. Индекс, который выглядит правильно в плане, на практике может оказаться медленнее: например, если таблица маленькая или планировщик выбрал другой путь.

Дальше: частичные индексы

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

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

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

Составной индекс — это один индекс, который покрывает сразу две или больше колонок. Создаётся он так: CREATE INDEX idx_name ON table(col_a, col_b). SQLite хранит записи отсортированными сначала по col_a, а внутри каждого значения col_a — уже по col_b. Принцип тот же, что и в телефонной книге: сначала по фамилии, потом по имени.

Важен ли порядок колонок в составном индексе SQLite?

Очень важен. SQLite сможет задействовать составной индекс, только если в WHERE фильтрация идёт по leftmost prefix — то есть по колонкам слева направо без пропусков. Индекс на (a, b, c) отработает для запросов с фильтром по a, по a и b, либо по всем трём. А вот если фильтр только по b или только по c — индекс окажется бесполезным.

Когда лучше делать составной индекс, а когда — отдельные индексы на каждую колонку?

Составной индекс имеет смысл, когда запросы постоянно фильтруют или сортируют данные по одной и той же связке колонок. Если же колонки фигурируют в запросах независимо друг от друга — лучше сделать отдельные одноколоночные индексы. Какой вариант реально подхватит планировщик — покажет EXPLAIN QUERY PLAN. Это единственный надёжный способ проверить.

Что такое покрывающий индекс (covering index) в SQLite?

Покрывающий индекс — это индекс, который содержит все колонки, нужные запросу. SQLite в этом случае отвечает прямо из индекса, не обращаясь к самой таблице. Когда так происходит, в EXPLAIN QUERY PLAN появляется пометка USING COVERING INDEX. Добавление лишних колонок в составной индекс ради покрытия горячего запроса — частый и вполне рабочий приём оптимизации.

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

Составные индексы SQLite: порядок колонок и leftmost prefix