Индексы

Как база данных находит заказы клиента 3? Без подсказок она читает таблицу orders строку за строкой, все подряд, и оставляет те, у которых customer_id равен 3. Для 30 строк это мгновенно; для 30 миллионов — заметное время.

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

CREATE INDEX orders_customer_id ON orders (customer_id);

У индекса есть имя (обычно из названий таблицы и столбца), а после ON идут таблица и, в скобках, столбец. В запросах индекс упоминать не нужно: база данных сама решает, когда он помогает. Запросы остаются прежними и находят те же строки, только быстрее.

Чтобы увидеть, что собирается делать база данных, поставьте EXPLAIN QUERY PLAN перед запросом. В его результате несколько столбцов; важен тот, что называется detail. Без индекса:

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 3;

SCAN означает, что база данных читает всю таблицу. С индексом она ищет:

SEARCH означает, что она ищет значение в индексе, здесь это orders_customer_id. В скобках указано, по какому столбцу идёт поиск, а вопросительный знак заменяет искомое значение, здесь 3.

Точные слова зависят от версии SQLite; важно, что там SCAN или SEARCH. Индекс помогает на больших таблицах, для столбцов, по которым запросы ищут в WHERE, соединяют в JOIN … ON и сортируют в ORDER BY. У первичного ключа есть собственный индекс, как и у каждого столбца UNIQUE (так база данных быстро проверяет, что значения там ещё нет). У внешних ключей в SQLite индекса нет, а поскольку таблицы постоянно соединяют по ним, индекс часто создают для них в первую очередь. Индекс может быть и по нескольким столбцам: ON games (genre, price) помогает запросам, которые ищут по жанру или по жанру и цене, как телефонная книга, отсортированная по фамилии и имени.

Но индекс не бесплатен. Он занимает место, а каждый INSERT, UPDATE и DELETE должен обновлять все индексы таблицы, из-за чего становится медленнее. Поэтому индекс заводят не для каждого столбца, а только для тех, которые запросам действительно нужны. А на маленьких таблицах, как в Pixel Shop, индекс не даёт заметной разницы.

CREATE UNIQUE INDEX создаёт индекс, который ещё и допускает каждое значение только один раз, как UNIQUE. Так можно добавить это правило в уже существующую таблицу, чего ALTER TABLE в SQLite не умеет. Ненужный индекс удаляют командой DROP INDEX с его именем.

Типичная ошибка: обернуть столбец в функцию. Индекс по email не помогает запросу WHERE LOWER(email) = '…': в индексе адреса хранятся как есть, а не в нижнем регистре, поэтому базе данных всё равно придётся вычислять LOWER() для каждой строки.