Индексы SQLite: создание, уникальность и ускорение запросов

Разберу, как устроены индексы в SQLite, зачем они нужны и как их правильно создавать, проверять и удалять

В реляционных базах данных таблица — это набор строк с одинаковой структурой. Каждая строка идентифицируется уникальным числом rowid, что позволяет рассматривать таблицу как набор пар: (rowid, строка)

В отличие от таблицы, индекс хранит пары значение → rowid (обратное отображение по сравнению с таблицей). Индекс — это дополнительная структура данных, которая помогает ускорить выполнение запроса

SQLite использует B-дерево (B-tree) для организации индексов. Обратите внимание: «B» означает balanced (сбалансированный) — это сбалансированное дерево, а не бинарное

B-дерево поддерживает баланс объёма данных с обеих сторон дерева, так что количество уровней, которые необходимо пройти для нахождения строки, всегда примерно одинаково. Кроме того, запросы с использованием равенства (=) и диапазонов (>, >=, <, <=) по индексам на основе B-дерева выполняются очень эффективно

Индекс в базе данных напоминает указатель в книге: он позволяет быстро находить нужные данные по ключевым значениям, избегая необходимости просматривать всю таблицу

Как работает индекс в SQLite

Индекс связан с конкретной таблицей и состоит из одного или нескольких столбцов, все из которых должны принадлежать этой самой таблице

Каждый раз, когда вы создаёте индекс, SQLite создаёт структуру B-дерева для хранения данных индекса

Индекс содержит данные из столбцов, указанных в индексе, и соответствующее значение rowid. Это помогает SQLite быстро находить строку на основе значений индексированных столбцов

Оператор CREATE INDEX: синтаксис и примеры

Для создания индекса используется оператор CREATE INDEX со следующим синтаксисом:

CREATE [UNIQUE] INDEX index_name ON table_name(column_list);

Для создания индекса необходимо указать следующую информацию:

• имя индекса после ключевых слов CREATE INDEX;
• имя таблицы, которой принадлежит индекс, после ключевого слова ON;
• список столбцов индекса в круглых скобках () после имени таблицы.

Если нужно обеспечить уникальность значений в одном или нескольких столбцах — например, email и phone — используйте параметр UNIQUE в операторе CREATE INDEX для создания уникального индекса

Уникальный индекс: пример с UNIQUE

Создадим новую таблицу с именем contacts для демонстрации:

CREATE TABLE contacts ( first_name text NOT NULL, last_name text NOT NULL, email text NOT NULL
);

Предположим, нужно обеспечить уникальность email — создаём уникальный индекс следующим образом:

CREATE UNIQUE INDEX idx_contacts_email ON contacts (email);

Для проверки выполним следующие шаги

Во-первых, вставим строку в таблицу contacts:

INSERT INTO contacts (first_name, last_name, email)
VALUES ('John', 'Doe', 'john.doe@emaildomain.com');

Во-вторых, попробуем вставить ещё одну строку с дублирующимся email:

INSERT INTO contacts (first_name, last_name, email)
VALUES ('Johny', 'Doe', 'john.doe@emaildomain.com');

SQLite выдаст сообщение об ошибке, указывающее на нарушение уникального индекса. При вставке второй строки SQLite проверил и убедился, что email уникален среди всех строк в столбце email таблицы contacts

Вставим ещё две строки в таблицу contacts:

INSERT INTO contacts (first_name, last_name, email)
VALUES ('David', 'Brown', 'david.brown@emaildomain.com'), ('Lisa', 'Smith', 'lisa.smith@emaildomain.com');

Если запрашивать данные из таблицы contacts по конкретному email, SQLite будет использовать индекс для поиска данных:

SELECT first_name, last_name, email
FROM contacts
WHERE email = 'lisa.smith@emaildomain.com';

Чтобы убедиться, что SQLite действительно использует индекс, воспользуйтесь оператором EXPLAIN QUERY PLAN:

EXPLAIN QUERY PLAN
SELECT first_name, last_name, email
FROM contacts
WHERE email = 'lisa.smith@emaildomain.com';

Рекомендую всегда использовать команду EXPLAIN QUERY PLAN, чтобы подтвердить, что новый индекс задействован оптимизатором SQLite

Составной индекс: порядок столбцов имеет значение

Если создать индекс, состоящий из одного столбца, SQLite использует этот столбец в качестве ключа сортировки. Однако если индекс включает несколько столбцов, SQLite использует дополнительные столбцы в качестве последующих ключей сортировки

SQLite сортирует данные в составном индексе по первому столбцу, указанному в операторе CREATE INDEX. Затем дублирующиеся значения сортируются по второму столбцу и так далее

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

Следующий оператор создаёт составной индекс по столбцам first_name и last_name таблицы contacts:

CREATE INDEX idx_contacts_name ON contacts (first_name, last_name);

Если запрашивать таблицу contacts с одним из следующих условий в предложении WHERE, SQLite будет использовать составной индекс для поиска данных

1) Фильтрация данных по столбцу first_name:

WHERE first_name = 'John';

2) Фильтрация данных по обоим столбцам first_name и last_name:

WHERE first_name = 'John' AND last_name = 'Doe';

Однако SQLite не будет использовать составной индекс при следующих условиях

1) Фильтрация только по столбцу last_name:

WHERE last_name = 'Doe';

2) Фильтрация по столбцам first_name ИЛИ last_name.

На практике я не раз сталкивался с ситуацией, когда разработчики создавали составной индекс, а потом удивлялись, почему запрос по второму столбцу не ускоряется — именно из-за несоблюдения порядка столбцов. Мой совет: всегда проверяйте план запроса через EXPLAIN QUERY PLAN сразу после создания индекса, не откладывая на потом

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы

Просмотр существующих индексов

Чтобы найти все индексы, связанные с таблицей, используйте следующую команду:

PRAGMA index_list('table_name');

Например, этот оператор показывает все индексы таблицы contacts:

PRAGMA index_list('contacts');

Чтобы получить информацию о столбцах в конкретном индексе, используйте следующую команду:

PRAGMA index_info('idx_contacts_name');

Этот пример возвращает список столбцов индекса idx_contacts_name

Другой способ получить все индексы из базы данных — выполнить запрос к таблице sqlite_master:

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

Типичные ошибки при использовании индексов SQLite

Несколько ситуаций, с которыми часто сталкиваются при работе с индексами в SQLite

Индекс создан, но не используется. Чаще всего это происходит при составном индексе, когда запрос фильтрует только по второму или третьему столбцу, минуя первый. SQLite не сможет задействовать такой индекс

Слишком много индексов. Каждый индекс ускоряет чтение, но замедляет запись: при каждой вставке или обновлении строки SQLite обновляет все связанные индексы. Не стоит индексировать каждый столбец подряд

Уникальный индекс вместо ограничения. CREATE UNIQUE INDEX и UNIQUE в определении таблицы дают схожий результат, но уникальный индекс даёт больше гибкости — его можно удалить отдельно, не меняя схему таблицы

Забытая проверка плана запроса. Добавить индекс недостаточно — нужно убедиться через EXPLAIN QUERY PLAN, что оптимизатор его действительно использует

Оператор DROP INDEX: удаление индекса

Чтобы удалить индекс из базы данных, используйте оператор DROP INDEX:

DROP INDEX [IF EXISTS] index_name;

В этом синтаксисе укажите имя индекса, который нужно удалить, после ключевых слов DROP INDEX. Параметр IF EXISTS удаляет индекс только в том случае, если он существует — это позволяет избежать ошибки при попытке удалить несуществующий индекс

Например, используйте следующий оператор для удаления индекса idx_contacts_name:

DROP INDEX idx_contacts_name;

Индекс idx_contacts_name полностью удалён из базы данных

Частые вопросы об индексах в SQLite

Когда стоит создавать индекс в SQLite? Индекс имеет смысл создавать на столбцах, которые часто используются в условиях WHERE, JOIN или ORDER BY, особенно если таблица содержит тысячи строк и более. На маленьких таблицах индекс практически не даёт прироста скорости

Замедляет ли индекс операции записи? Да. При каждой вставке, обновлении или удалении строки SQLite обновляет все индексы, связанные с таблицей. Чем больше индексов — тем выше накладные расходы на запись

Можно ли создать индекс по нескольким столбцам? Да, это называется составным индексом. Важно помнить, что SQLite использует его только тогда, когда запрос фильтрует по первому столбцу индекса или по первому и последующим столбцам в том же порядке

Чем уникальный индекс отличается от обычного? Уникальный индекс (UNIQUE INDEX) дополнительно запрещает дублирование значений в индексированных столбцах. При попытке вставить дублирующееся значение SQLite вернёт ошибку

Как проверить, использует ли SQLite индекс в конкретном запросе? Используйте оператор EXPLAIN QUERY PLAN перед запросом. В выводе будет указано, задействован ли индекс и какой именно