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

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

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: какой индекс для чего

Запрос	Индекс
WHERE col = x	B-tree на col
WHERE col BETWEEN a AND b	B-tree на col
ORDER BY col	B-tree на col
JOIN ON a.col = b.col	B-tree на обе стороны
WHERE col @> array['x']	GIN
WHERE JSONB_col ->> 'key' = x	GIN or functional
WHERE LOWER(col) = x	functional LOWER(col)
Highly selective + static filter	partial index

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

• Создание индекса на большой таблице — блокировка. В 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. Не блокирует запись.

---

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.