Раздел 35 · SQLite с нуля

Индексы и EXPLAIN QUERY PLAN

junior~15 мин

без входаВойди, чтобы отмечать прогресс, вести заметки и решать задачи в редакторе. войти

В «Пенке» двенадцать заказов, и любой запрос к ним отвечает мгновенно. Через год заказов будет сто тысяч, а через три года миллион. Запрос «все заказы Маши» начнёт задумываться, хотя Маша сделала всего три заказа. Почему так происходит и как это исправить, разбираем в этом уроке. А заодно учимся спрашивать у SQLite, как он собирается выполнять запрос.

Алфавитный указатель в конце книги

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

В конце хорошего учебника есть алфавитный указатель: «аргон, с. 412». Слова в нём стоят по алфавиту, поэтому нужное находится за пару взглядов, а потом ты сразу открываешь страницу 412.

Таблица без индекса это книга без указателя. Чтобы найти заказы Маши, SQLite читает все строки orders подряд и проверяет у каждой customer_id. Индекс это и есть указатель: значения столбца, разложенные по порядку, и рядом номер строки, где каждое встречается. SQLite хранит индекс в виде B-дерева, подробно про него в уроке про индексы.

Как спросить план

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

sqlite> EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 1;
QUERY PLAN
`--SCAN orders

SCAN orders значит «прочитаю таблицу orders целиком, строку за строкой». На двенадцати строках это незаметно, на миллионе это секунды.

Строим указатель по столбцу customer_id:

sqlite> CREATE INDEX idx_orders_customer ON orders(customer_id);
sqlite> EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 1;
QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_customer (customer_id=?)

Теперь план говорит SEARCH: SQLite находит в индексе нужное значение и читает только подходящие строки. Запись (customer_id=?) показывает, по какому условию идёт поиск: на месте ? будет то число, что стоит в запросе.

Запомни пару слов:

  • SCAN это просмотр всей таблицы, как листать книгу с начала;
  • SEARCH это поиск по индексу, как открыть указатель.

Имя индекса выбираешь ты. Принято начинать с idx_, потом таблица и столбец, чтобы через полгода было понятно, что это за индекс.

Индексы, которые SQLite строит сам

Некоторые индексы появляются без CREATE INDEX. Посмотри на поиск по первичному ключу и по названию напитка:

sqlite> EXPLAIN QUERY PLAN SELECT * FROM drinks WHERE id = 3;
QUERY PLAN
`--SEARCH drinks USING INTEGER PRIMARY KEY (rowid=?)
sqlite> EXPLAIN QUERY PLAN SELECT * FROM drinks WHERE name = 'Латте';
QUERY PLAN
`--SEARCH drinks USING INDEX sqlite_autoindex_drinks_1 (name=?)

Первичный ключ INTEGER PRIMARY KEY это и есть rowid, номер строки, по которому SQLite хранит таблицу. Мы разбирали его в уроке про ограничения. Искать по нему быстро всегда.

У name стоит UNIQUE. Чтобы проверять уникальность при каждой вставке, SQLite нужен быстрый поиск по этому столбцу, поэтому он сам строит автоматический индекс с именем sqlite_autoindex_drinks_1. Команда .indexes такие индексы прячет, а с ключом -a показывает все:

sqlite> .indexes
idx_orders_customer
sqlite> .indexes -a
idx_orders_customer   sqlite_autoindex_drinks_1   sqlite_autoindex_order_items_1

Второй автоматический индекс принадлежит составному первичному ключу order_items.

Индекс и сортировка

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

sqlite> EXPLAIN QUERY PLAN SELECT * FROM orders ORDER BY ordered_at;
QUERY PLAN
|--SCAN orders
`--USE TEMP B-TREE FOR ORDER BY

USE TEMP B-TREE FOR ORDER BY значит, что SQLite прочитает строки и разложит их по порядку во временной структуре. Это лишняя работа. Добавим индекс по времени заказа и заодно проверим пару опасных условий:

sqlite> CREATE INDEX idx_orders_time ON orders(ordered_at);
sqlite> EXPLAIN QUERY PLAN SELECT * FROM orders ORDER BY ordered_at;
QUERY PLAN
`--SCAN orders USING INDEX idx_orders_time
sqlite> EXPLAIN QUERY PLAN SELECT * FROM orders WHERE date(ordered_at) = '2026-10-01';
QUERY PLAN
`--SCAN orders
sqlite> EXPLAIN QUERY PLAN SELECT * FROM orders WHERE ordered_at >= '2026-10-01' AND ordered_at < '2026-10-02';
QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_time (ordered_at>? AND ordered_at<?)
sqlite> EXPLAIN QUERY PLAN SELECT * FROM drinks WHERE name LIKE '%латте';
QUERY PLAN
`--SCAN drinks

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

Когда индекс не помогает

Два запроса выше остались на SCAN, хотя индекс есть.

Функция над столбцом. В индексе лежат значения ordered_at, а не date(ordered_at). Чтобы сравнить результат функции, SQLite должен вычислить её для каждой строки. Тот же вопрос, заданный диапазоном >= ... AND < ..., превращается в SEARCH: даты в «Пенке» хранятся текстом ISO 8601, и такие строки упорядочены так же, как время. Про это был урок о датах.

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

Есть и третья причина, которую план не покажет: таблица слишком маленькая. На десятке строк SQLite иногда честно читает всё, потому что это быстрее, чем ходить в индекс.

Цена индекса

Если индексы ускоряют поиск, почему не построить их на все столбцы? Потому что у каждого индекса есть цена.

  • Запись медленнее. При каждом INSERT, UPDATE и DELETE SQLite обновляет не только таблицу, но и все её индексы. Пять индексов значит шесть записей вместо одной.
  • Файл больше. Индекс хранит копию значений столбца, а база SQLite это один файл.

Правило простое: индекс строят под конкретный частый запрос, а не «на всякий случай». Сначала EXPLAIN QUERY PLAN, увидел SCAN на большой таблице в частом запросе, построил индекс, проверил, что стало SEARCH.

Уникальный индекс

CREATE UNIQUE INDEX строит указатель и заодно запрещает повторы, как ограничение UNIQUE. Так можно запретить двум гостям один телефон:

sqlite> CREATE UNIQUE INDEX idx_customers_phone ON customers(phone);
sqlite> UPDATE customers SET phone = '+7 900 111-22-33' WHERE id = 3;
Error near line 2: UNIQUE constraint failed: customers.phone
sqlite> SELECT name, phone FROM customers WHERE phone IS NULL;
╭───────┬───────╮
│ name  │ phone │
╞═══════╪═══════╡
│ Пётр  │ NULL  │
│ Света │ NULL  │
╰───────┴───────╯

Телефон Маши уже занят, и база отказалась его повторить. А вот два NULL уживаются: для уникальности NULL не равен другому NULL, как ты помнишь из урока про NULL.

Ненужный индекс удаляют командой DROP INDEX имя. Данные таблицы при этом не трогаются.

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

Попробуй сам

Песочница начинает со свежей базой «Пенки», без индексов из урока. Посмотри план поиска заказов одного бариста, построй индекс и посмотри снова:

Почему последний запрос снова дал SCAN, хотя смысл у него тот же? Попробуй построить индекс по двум столбцам, ON orders(staff_id, ordered_at), и спроси план для WHERE staff_id = 3 ORDER BY ordered_at.

Проверь себя

Задание

Дальше

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

домашка

Домашка