Индексы в SQLite: CREATE INDEX и когда их использовать

Что такое индекс на самом деле

Индекс — это отдельная структура данных, отсортированное B-дерево, которое позволяет SQLite находить строки по значению столбца, не сканируя всю таблицу целиком. Без индекса запрос вроде WHERE email = 'rosa@example.com' читает каждую строку и сверяет её. С индексом по email SQLite проходит по дереву примерно за log(n) шагов и сразу попадает в нужную запись.

Но за такое ускорение приходится платить. Индекс — это копия значений индексируемого столбца плюс указатель обратно на строку. Каждый INSERT, UPDATE индексируемого столбца и DELETE вынужден обновлять и сам индекс. Места на диске уходит больше, скорость записи слегка проседает. Сделка такая: немного теряем на записи — сильно выигрываем на чтении.

Как создать индекс в SQLite

Базовый синтаксис команды CREATE INDEX:

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

Соглашение об именовании: в большинстве команд принято писать idx_<таблица>_<столбец> — так сразу понятно, для чего нужен индекс. Имя должно быть уникальным в пределах всей базы, а не только таблицы — поэтому в него и включают название таблицы.

Чтобы удалить индекс:

DROP INDEX idx_users_email;

Индексы в SQLite — это чисто производительные «леса». Удалили индекс — данные не пострадают, изменится только скорость запросов.

Уникальные индексы в SQLite

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

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

Третья вставка падает с ошибкой UNIQUE constraint failed: accounts.username. SQLite автоматически создаёт уникальные индексы для столбцов PRIMARY KEY и UNIQUE — их можно увидеть под именами вида sqlite_autoindex_<table>_<n>. Писать CREATE UNIQUE INDEX руками нужно только тогда, когда ограничение не было объявлено прямо в самой таблице.

Что на самом деле делает планировщик

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

Ищите в выводе SEARCH ... USING INDEX idx_orders_customer — это означает, что индекс задействован. Если же видите SCAN orders, значит планировщик решил, что полное сканирование таблицы дешевле (часто это верно на маленьких таблицах), либо форма запроса просто не позволила использовать индекс. Подробнее о том, как читать такие планы, — в отдельной статье ниже.

Когда SQLite не использует индекс

У индексов есть несколько хорошо известных слепых зон. Каждый из примеров ниже сводит индекс по email на нет:

-- Функция оборачивает столбец
SELECT * FROM users WHERE lower(email) = 'rosa@example.com';

-- Ведущий шаблонный символ в LIKE
SELECT * FROM users WHERE email LIKE '%@example.com';

-- Несоответствие типов вызывает преобразование
SELECT * FROM users WHERE email = 12345;

B-дерево отсортировано по «сырому» значению email, поэтому любое преобразование столбца на лету заставляет SQLite перейти к полному сканированию. Вариантов решения несколько: хранить данные уже в нормализованном виде (отдельный столбец email_lower), использовать индекс по выражению (CREATE INDEX idx ON users(lower(email))) или подключить полнотекстовый поиск SQLite для поиска по подстроке.

Покрывающий индекс sqlite

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

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;

Так как обе колонки, которые запрашивает запрос, лежат прямо в индексе, SQLite сообщает USING COVERING INDEX. Обращаться к самой таблице уже не нужно. Покрывающий индекс sqlite — одна из самых выгодных оптимизаций для горячих путей чтения, расплата лишь одна: индекс занимает больше места. Составные индексы — отдельная большая тема, и ей посвящён следующий материал.

Как посмотреть список индексов

Есть два способа узнать, что вообще создано в базе:

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

Так вы получите список всех индексов в базе вместе с их CREATE-выражениями. Для конкретной таблицы используйте PRAGMA index_list('products'); — он покажет только индексы этой таблицы, а PRAGMA index_info('idx_products_name'); подскажет, какие именно колонки в индексе. Всё, что начинается с sqlite_autoindex_, SQLite создал автоматически под ограничение PRIMARY KEY или UNIQUE — удалить такие индексы нельзя.

Когда индекс лучше не добавлять

Бывают ситуации, когда индекс sqlite не помогает, а только вредит:

• Маленькие таблицы. Пара сотен строк сканируется за микросекунды. Планировщик всё равно, скорее всего, проигнорирует индекс — а вы получите лишние накладные расходы на запись.
• Колонки с частой записью, по которым почти не фильтруют. Каждая вставка и обновление трогает все индексы таблицы. Индекс на колонке, по которой вы почти никогда не ищете, — это чистый минус.
• Колонки с низкой кардинальностью сами по себе. Индекс по колонке status, у которой всего три возможных значения, мало что отсекает. Он ещё может пригодиться как вторая колонка составного индекса или в виде частичного индекса, но в одиночку чаще всего не окупается.
• Уже покрыто другим индексом. Если есть индекс на (a, b), отдельный индекс на (a) не нужен. SQLite использует ведущие колонки составного индекса и для запросов, где фильтр идёт только по a.

Честный ответ на вопрос «а стоит ли добавить этот индекс?» почти всегда один и тот же: попробуйте, прогоните EXPLAIN QUERY PLAN, замерьте на реальных данных — и решайте.

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

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