Индексы: зачем они и как не навредить

Индексы: зачем они и как не навредить

Индекс - это как оглавление в книге. Можно искать главу по оглавлению за секунды, а можно листать 500 страниц и чувствовать жизнь.

Открываешь книгу на 800 страниц. Тебе нужно одно слово. Вариант первый: листать всё подряд, страницу за страницей, пока не наткнёшься. Вариант второй: открыть алфавитный указатель в конце - слово, номер страницы, готово. Секунда.

База данных работает точно так же. Пишешь WHERE email = '...', а индекса на email нет - и база честно листает всю таблицу. От начала до конца. Все два миллиона строк. Каждый раз.

В жизниВ коде
книга на 800 страництаблица на два миллиона строк
листать подряд, пока не наткнёшьсяSeq Scan - полный перебор
алфавитный указатель в концеиндекс: значения отсортированы, рядом адрес строки
заглянуть, как ты вообще ищешьEXPLAIN перед запросом
указатель занимает страницы и его правят при каждой правке книгииндекс ест место на диске и замедляет запись

Проверить просто: напиши перед запросом EXPLAIN. Увидел Seq Scan на большой таблице - вот он, твой вечер с листанием 800 страниц. Как читать такие планы целиком - в уроке EXPLAIN.

Что ускоряет индекс

Обычно индексы ускоряют:

  • поиск по WHERE
  • соединения по ключам (JOIN ON)
  • сортировку ORDER BY (иногда) - значения в индексе уже лежат по порядку, поэтому базе не нужно сортировать заново
  • уникальность (UNIQUE)

Как работает индекс: B-tree

По умолчанию PostgreSQL использует B-tree индекс (родственник бинарного дерева). Мысли о нём так:

  • Всё отсортировано и организовано в дерево
  • БД может быстро сказать: «вот эти записи точно не нужны»
  • Дальше ищет только в нужных листьях дерева

Структура B-tree индекса: корень, ветви, листья со значениями

Не вникай в детали, но знай: это быстро для большого диапазона значений.

Обход B-tree - `O(log n)` переходов по узлам, и как алгоритмическая модель это верно. Но вывод «индекс есть - значит быстро» из неё не следует: стоимость запроса в PostgreSQL складывается не только из числа сравнений.

На неё влияют доля выбираемых строк (нашли по индексу тысячу записей - за каждой придётся идти в таблицу), кеш и то, лежат ли нужные страницы в памяти, физический порядок строк относительно индекса, возможность index-only scan, видимость версий строк. Планировщик всё это учитывает и вполне может решить, что полный скан дешевле - и часто быть правым.

Практический смысл модели: она объясняет, почему индекс помогает, и не обещает, во сколько раз. На вопрос «во сколько» отвечает EXPLAIN (ANALYZE, BUFFERS) на твоих данных. Хеш-таблица (та самая, что внутри map) ищет по точному ключу за constant time, но не умеет в диапазоны, сортировку и поиск по префиксу столбца - поэтому по умолчанию в базах именно B-tree, он покрывает больше видов запросов одной структурой. Что до «hash быстрее на равенстве» - в PostgreSQL разница на практике невелика: поиск в B-tree это несколько переходов по узлам, которые почти всегда уже в кеше. Hash-индекс там существует, но выбирают его редко и по замерам, а не по общему правилу. Указатель в книге полезен ровно по той же причине - он отсортирован по алфавиту. Свали слова в кучу, и листать придётся уже сам указатель.

Seq Scan против Index Scan: O(n) перебор vs O(log n) точка входа

Создание индекса

-- Простой индекс
CREATE INDEX idx_users_email ON users(email);

-- С индивидуальным именем (good practice: idx_tablename_column)
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- На нескольких столбцах (composite index)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Для быстрого поиска в частичном диапазоне
CREATE INDEX idx_users_created_at ON users(created_at DESC);
Каждый индекс замедляет `INSERT/UPDATE/DELETE` и занимает место на диске. Не делай индекс на всё подряд - база не ёлка.

Composite индексы - порядок имеет значение

Когда ты создаёшь индекс на (user_id, status), БД сортирует данные сначала по user_id, потом по status внутри каждого user_id.

CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Этот запрос ПОЛНОСТЬЮ использует индекс (быстро):
SELECT * FROM orders WHERE user_id = 42 AND status = 'completed';

-- Этот тоже (по первому столбцу):
SELECT * FROM orders WHERE user_id = 42;

-- А этот использует индекс хуже - status стоит вторым:
SELECT * FROM orders WHERE status = 'completed';

Указатель в книге отсортирован сначала по первой букве - искать в нём по второй букве бессмысленно. Отсюда и рабочее правило: первыми ставь столбцы, по которым идёт сравнение на равенство, а столбцы для диапазонов (>, <, BETWEEN) и сортировки - после них.

Правило «работает только слева направо» - хорошая начальная модель, но как запрет оно неверно. Планировщик может использовать такой индекс и без ограничения по ведущему столбцу: прочитать его целиком дешевле, чем таблицу, если индекс узкий, а таблица широкая. В PostgreSQL для части случаев есть и оптимизация, которая перебирает значения ведущего столбца, вместо того чтобы отказываться от индекса.

Поэтому формулировка такая: запрос без ограничения по ведущему столбцу обычно использует индекс хуже, чем запрос с ним - но «хуже» и «никак» разные вещи. Окончательный ответ даёт EXPLAIN на твоих данных: если столбец попал в Index Cond - он участвовал в поиске, если ушёл в Filter - проверялся после чтения.

Практический вывод не меняется: под частый запрос по status нужен индекс, где status ведущий. Меняется только то, чего ждать от существующего.

Совет «ставь первым то, что чаще в WHERE» работает в простых случаях и подводит в двух заметных:
  • Диапазон обрывает индекс. В WHERE created_at > $1 AND status = $2 индекс (created_at, status) использует только первый столбец: после неравенства порядок внутри уже не помогает. (status, created_at) отработает оба - хотя created_at встречается в запросах чаще.
  • Сортировка тоже часть индекса. WHERE user_id = $1 ORDER BY created_at DESC на индексе (user_id, created_at) отдаёт строки уже упорядоченными, без шага сортировки.

Про селективность («самый избирательный столбец первым») стоит знать, что для B-tree это не главный критерий: набор поддерживаемых запросов важнее. Проверяется всё тем же EXPLAIN - смотри, попал ли столбец в Index Cond или уехал в Filter.

UNIQUE индекс vs UNIQUE constraint

UNIQUE гарантирует, что значения уникальны. Это уже территория ограничений целостности, просто под капотом всё равно живёт индекс:

-- Через индекс
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Через constraint (часто удобнее)
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE(email);

-- Результат одинаков, но constraint более явный в схеме

Когда НЕ делать индекс

  • Булевы столбцы (is_active, is_deleted) при близком к равномерному распределении - планировщик всё равно предпочтёт полный скан
  • Небольшие таблицы - пока таблица укладывается в несколько страниц, полный скан дешевле обхода индекса
  • Низкая селективность - если условию удовлетворяет большая часть строк, индекс добавит работы, а не уберёт
«Меньше 1000 строк - индекс не нужен», «больше 100 000 - обязателен» ходят по статьям как закон, но зависят от размера строки, доли нужных строк в результате, наличия index-only scan, скорости диска и настроек планировщика (`random_page_cost`, `effective_cache_size`). На узкой таблице полный скан бывает быстрее индекса и на десятках тысяч строк; на широкой индекс окупается раньше.

Единственный честный ответ даёт EXPLAIN (ANALYZE, BUFFERS) до и после на твоих данных - и он же покажет, что планировщик думает про селективность. То же касается булевых столбцов: при распределении 99/1 частичный индекс WHERE is_deleted = false вполне может окупиться. 

-- Нет смысла:
CREATE INDEX idx_users_is_active ON users(is_active); -- half/half распределение

-- Зато полезно:
CREATE INDEX idx_orders_user_id ON orders(user_id); -- few users, many orders

Как посмотреть существующие индексы

PostgreSQL:

-- Все индексы в текущей БД
\di

-- Индексы конкретной таблицы
\d orders

-- Или через запрос
SELECT indexname FROM pg_indexes WHERE tablename = 'users';

SQLite:

.indices users

Когда индекс почти обязателен

  • Внешние ключи (orders.user_id) - нужен для JOIN и, что важнее, для проверок при удалении родителя. PostgreSQL его не создаёт - см. врезку
  • Уникальные поля (email, username) - здесь индекс правда появляется сам: UNIQUE реализован через уникальный индекс
  • Частые фильтры в WHERE - индекс по полям, по которым часто ищешь
  • Таблицы, выросшие настолько, что полный скан перестал быть мгновенным - где эта граница, показывает EXPLAIN ANALYZE на твоих данных
Распространённое заблуждение, и оно устойчиво, потому что рядом стоит правда: `UNIQUE` и `PRIMARY KEY` индекс действительно создают - без него ограничение не проверить. А `REFERENCES` **не создаёт ничего** на ссылающейся стороне: индекс нужен на таблице, куда ссылаются (там он и есть - первичный ключ), а на `orders.user_id` его нет, пока ты не напишешь `CREATE INDEX` сам.

Проверяется за минуту:

CREATE TABLE users (id INT PRIMARY KEY);
CREATE TABLE orders (id INT PRIMARY KEY, user_id INT REFERENCES users(id));
SELECT indexname FROM pg_indexes WHERE tablename = 'orders';
-- orders_pkey  ← и всё, индекса на user_id нет

Заметно это не на JOIN (планировщик найдёт способ), а на удалении и обновлении родителя: каждый DELETE FROM users WHERE id = 42 заставляет базу убедиться, что осиротевших заказов не осталось, - и без индекса это полный скан orders на каждую удалённую строку. На таблице в десятки миллионов строк «быстрое» удаление одного пользователя встаёт на минуты и держит блокировки.

Формулировка из документации PostgreSQL: поскольку DELETE в целевой таблице требует сканирования ссылающейся, индексировать ссылающиеся столбцы «часто хорошая идея» - но нужно это не всегда, а способов проиндексировать много, поэтому объявление внешнего ключа индекс не создаёт.

MySQL/InnoDB, наоборот, создаёт индекс под внешний ключ автоматически - отсюда половина путаницы. Правило переносить нельзя: оно про конкретную СУБД, и проверяется одним запросом к pg_indexes.

Если даже с индексом запрос остаётся тяжёлым (сложная агрегация, десятки миллионов строк), следующий шаг - не двадцатый индекс, а кеш перед базой.

Практический пример

-- Таблица заказов (миллионы строк)
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id INT NOT NULL,
    status VARCHAR(20),
    total DECIMAL,
    created_at TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Индексы:
-- На внешний ключ: PostgreSQL его не создаёт, а без него страдает
-- не столько JOIN, сколько DELETE/UPDATE в таблице users
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- На комбинацию для популярного WHERE
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- На дату для сортировки и фильтрации
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- Запросы, которые эти индексы ускорят:
SELECT * FROM orders WHERE user_id = 42; -- idx_orders_user_id
SELECT * FROM orders WHERE user_id = 42 AND status = 'completed'; -- idx_orders_user_status
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10; -- idx_orders_created_at

Мини-задание

  • Посмотри существующие индексы в своей БД (\di в PostgreSQL)
  • Подумай, какие поля в твоей схеме ты часто ищешь в WHERE
  • Создай composite индекс на два часто используемых вместе столбца
  • Проверь, что индекс создан (через \d tablename)

Зарегистрируйтесь бесплатно, чтобы пройти квиз, решить задание с автопроверкой, вести прогресс.