Индексы: зачем они и как не навредить
Индексы: зачем они и как не навредить
Индекс - это как оглавление в книге. Можно искать главу по оглавлению за секунды, а можно листать 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 индекс (родственник бинарного дерева). Мысли о нём так:
- Всё отсортировано и организовано в дерево
- БД может быстро сказать: «вот эти записи точно не нужны»
- Дальше ищет только в нужных листьях дерева
Не вникай в детали, но знай: это быстро для большого диапазона значений.
ℹ️ O(log n) - это про дерево, а не про время запроса
Обход B-tree -
O(log n)переходов по узлам, и как алгоритмическая модель это верно. Но вывод «индекс есть - значит быстро» из неё не следует: стоимость запроса в PostgreSQL складывается не только из числа сравнений.На неё влияют доля выбираемых строк (нашли по индексу тысячу записей - за каждой придётся идти в таблицу), кеш и то, лежат ли нужные страницы в памяти, физический порядок строк относительно индекса, возможность index-only scan, видимость версий строк. Планировщик всё это учитывает и вполне может решить, что полный скан дешевле - и часто быть правым.
Практический смысл модели: она объясняет, почему индекс помогает, и не обещает, во сколько раз. На вопрос «во сколько» отвечает
EXPLAIN (ANALYZE, BUFFERS)на твоих данных.
Хеш-таблица (та самая, что внутри map) ищет по точному ключу за constant time, но не умеет в диапазоны, сортировку и поиск по префиксу столбца - поэтому по умолчанию в базах именно B-tree, он покрывает больше видов запросов одной структурой. Что до «hash быстрее на равенстве» - в PostgreSQL разница на практике невелика: поиск в B-tree это несколько переходов по узлам, которые почти всегда уже в кеше. Hash-индекс там существует, но выбирают его редко и по замерам, а не по общему правилу. Указатель в книге полезен ровно по той же причине - он отсортирован по алфавиту. Свали слова в кучу, и листать придётся уже сам указатель.
Создание индекса
-- Простой индекс
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);
-- Для диапазонов и сортировки по дате
-- (B-tree читается в обе стороны, так что DESC тут не обязателен;
-- направление важно в составных индексах со смешанным порядком)
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) и сортировки - после них.
⚠️ «Индекс (a, b) бесполезен для WHERE b = ...» - слишком сильно
Правило «работает только слева направо» - хорошая начальная модель, но как запрет оно неверно. Планировщик может использовать такой индекс и без ограничения по ведущему столбцу: прочитать его целиком дешевле, чем таблицу, если индекс узкий, а таблица широкая. Начиная с PostgreSQL 18 для части случаев есть и оптимизация (skip scan), которая перебирает значения ведущего столбца, вместо того чтобы отказываться от индекса.
Поэтому формулировка такая: запрос без ограничения по ведущему столбцу обычно использует индекс хуже, чем запрос с ним - но «хуже» и «никак» разные вещи. Окончательный ответ даёт
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на твоих данных
⚠️ PostgreSQL не индексирует внешние ключи
Распространённое заблуждение, и оно устойчиво, потому что рядом стоит правда:
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)
);
-- Индексы:
-- На комбинацию для популярного WHERE. user_id стоит первым, поэтому
-- этот же индекс закрывает и внешний ключ: PostgreSQL сам индекс под FK
-- не создаёт, а без него страдает не столько JOIN, сколько DELETE/UPDATE
-- в таблице users. Отдельный индекс (user_id) был бы лишним.
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_status (левый префикс)
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)