Как создать индекс в SQL

Зачем нужны индексы

Запрос с WHERE user_id = 42 на таблице в миллиард строк без индекса может идти минуты. Тот же запрос с индексом — миллисекунды. Индекс — это структура данных, которая ускоряет поиск, но замедляет запись (каждый INSERT обновляет индекс).

Аналитик сам не всегда создаёт индексы (это чаще задача data engineer), но должен понимать, когда индекс поможет, какой тип выбрать (B-tree, GIN, BRIN), когда он только замедляет (малая таблица, часто обновляемая колонка). На собеседованиях middle+ индексы — обязательная тема.

В статье — практическое руководство:

• Обычный B-tree индекс (для 95% задач)
• Composite index и правило leftmost
• UNIQUE, partial, functional (expression) индексы
• GIN для JSON / массивов, BRIN для time series
• Covering index (INCLUDE) — query-only без обращения к таблице
• CREATE INDEX CONCURRENTLY — чтобы не блокировать prod
• Когда индекс НЕ нужен

Базовый синтаксис

CREATE INDEX idx_name ON table_name (column_name);

1. Индекс на одну колонку

CREATE INDEX idx_users_email ON users (email);

Ускоряет:

• WHERE email = 'x'
• ORDER BY email
• JOIN ON users.email = ...

2. Composite (составной) индекс

CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);

Ускоряет:

• WHERE user_id = 42 (использует leftmost)
• WHERE user_id = 42 AND created_at > '...' (использует оба)
• WHERE created_at > '...' — НЕ использует (нет leftmost)

Правило leftmost: индекс используется, если в WHERE фигурируют колонки слева направо.

3. UNIQUE индекс

CREATE UNIQUE INDEX idx_users_email ON users (email);

То же, что UNIQUE constraint: нет дубликатов + ускоряет поиск.

4. Partial (частичный) индекс

Индекс только на подмножество строк:

-- Postgres
CREATE INDEX idx_orders_paid
ON orders (user_id)
WHERE status = 'paid';

Меньше размер → быстрее.

5. Functional (выражение-индекс)

Для case-insensitive поиска:

CREATE INDEX idx_users_email_lower
ON users (LOWER(email));

-- теперь это использует индекс
SELECT * FROM users WHERE LOWER(email) = 'alice@...';

6. Индекс по несколько типов

B-tree (по умолчанию)

Для сравнений =, <, >, BETWEEN, ORDER BY.

CREATE INDEX idx_a ON orders (created_at);

Hash

Только для равенства =. Обычно не быстрее B-tree.

CREATE INDEX idx_b ON users USING HASH (email);

GIN — для массивов, JSON, text search

CREATE INDEX idx_tags ON users USING GIN (tags);

Ускоряет:

WHERE tags @> ARRAY['vip']

GiST — для геоданных, ranges

CREATE INDEX idx_range ON events USING GIST (event_range);

BRIN — для огромных таблиц с упорядоченными данными

CREATE INDEX idx_time ON events USING BRIN (created_at);

Маленький по размеру, быстрый для временных рядов.

7. Covering index (INCLUDE)

Postgres 11+:

CREATE INDEX idx_orders_covering
ON orders (user_id)
INCLUDE (total, status);

Запрос может вернуть total, status только из индекса (Index Only Scan) — не обращаясь к таблице.

8. Удалить индекс

DROP INDEX idx_name;

9. Посмотреть существующие индексы

Postgres

SELECT * FROM pg_indexes WHERE tablename = 'users';

MySQL

SHOW INDEX FROM users;

10. Когда НЕ создавать индекс

• Маленькие таблицы (< 1000 строк) — seq scan и так быстрый
• Часто обновляемые колонки — индекс тоже обновляется
• Низкая селективность (boolean, gender) — индекс не даст выигрыша

Compact guide: какой индекс для чего

Производительность

• Создание индекса на большой таблице — блокировка. В Postgres — CREATE INDEX CONCURRENTLY (не блокирует запись).
• Индекс занимает место: обычно 10-30% от размера таблицы.
• Лишний индекс замедляет INSERT / UPDATE.

Частые ошибки

Индекс на bool

Низкая селективность → толку мало.

Индекс на WHERE функцию

WHERE LOWER(col) = x — обычный индекс не работает. Нужен functional.

Много индексов на OLTP-таблице

Каждый INSERT/UPDATE перестраивает всё. Держите только нужные.

Не использовать leftmost

Composite (a, b) не ускоряет WHERE b = x. Нужен отдельный индекс на b.

Связанные темы

• Индексы SQL — шпаргалка
• EXPLAIN SQL
• Почему медленный запрос в SQL
• Оптимизация SQL

FAQ

Сколько индексов на таблицу — нормально?

3-10 для OLTP. Больше — замедляет запись.

B-tree или hash?

B-tree почти всегда. Hash только когда нужен строгий = без >, <.

Когда partial index?

Когда запрос часто использует конкретное условие (WHERE status = 'active').

CONCURRENTLY или обычное создание?

На prod — CONCURRENTLY. Не блокирует запись.