Индексы и EXPLAIN QUERY PLAN
Ускоряем поиск по таблице с помощью индексов.
Индекс — это вспомогательная структура, которая помогает базе быстро находить строки по значению столбца, не просматривая всю таблицу.
Зачем нужны индексы
Когда вы пишете WHERE city = 'Москва', без индекса база вынуждена просмотреть каждую строку и проверить условие — это называется полным сканированием (full scan). На сотне строк это мгновенно, а на миллионе уже заметно. Индекс по столбцу city — как алфавитный указатель в книге: база сразу прыгает к нужным строкам, минуя остальные.
Создание индекса
Индекс создают командой CREATE INDEX имя ON таблица(столбец). Имя принято делать говорящим, например idx_users_city.
CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, city TEXT);
INSERT INTO users (email, city) VALUES
('[email protected]', 'Москва'), ('[email protected]', 'Казань'), ('[email protected]', 'Москва');
CREATE INDEX idx_users_city ON users(city);
SELECT email, city FROM users WHERE city = 'Москва';Вывод:
[email protected]|Москва [email protected]|Москва
Результат тот же, что и без индекса, — индексы не меняют данные, а только ускоряют их поиск. Первичный ключ INTEGER PRIMARY KEY, к слову, уже проиндексирован автоматически, а UNIQUE создаёт индекс неявно.
EXPLAIN QUERY PLAN — заглянуть под капот
Как убедиться, что запрос использует индекс? Поставьте перед ним EXPLAIN QUERY PLAN — SQLite покажет план выполнения. Сравним два случая. Без индекса план говорит SCAN (полный просмотр):
CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, city TEXT);
INSERT INTO users (email, city) VALUES ('[email protected]', 'Москва'), ('[email protected]', 'Казань');
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE city = 'Москва';Вывод:
QUERY PLAN `--SCAN users
А с индексом по тому же столбцу план меняется на SEARCH ... USING INDEX:
CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, city TEXT);
INSERT INTO users (email, city) VALUES ('[email protected]', 'Москва'), ('[email protected]', 'Казань');
CREATE INDEX idx_users_city ON users(city);
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE city = 'Москва';Вывод:
QUERY PLAN `--SEARCH users USING INDEX idx_users_city (city=?)
Слово SEARCH вместо SCAN — признак, что индекс работает. Это главный инструмент диагностики медленных запросов.
Когда индексы не нужны
Индексы не бесплатны: они занимают место и немного замедляют вставку/обновление, ведь их тоже надо поддерживать. Поэтому индексируют столбцы, по которым часто ищут и соединяют (в WHERE, JOIN, ORDER BY), а не всё подряд. На маленькой таблице индекс может и не понадобиться — полный скан и так быстр.
Итог
- Индекс ускоряет поиск по столбцу, избавляя от полного перебора строк.
CREATE INDEX имя ON таблица(столбец)создаёт индекс; PK и UNIQUE индексируются автоматически.EXPLAIN QUERY PLANпоказывает план:SCAN— полный просмотр,SEARCH ... USING INDEX— работа по индексу.
