Что такое индексы в SQL?

Индексы в SQL

Индексы — это структуры данных, которые ускоряют поиск и фильтрацию записей в таблицах. Без индексов БД вынуждена сканировать всю таблицу (Full Table Scan), что медленно для больших объёмов данных.

1. Что такое индекс

Индекс — это отдельная структура, которая хранит отсортированные значения столбца и указатели на соответствующие строки:

# Без индекса — Full Table Scan O(n)
SELECT * FROM users WHERE email = "john@example.com";  -- Сканирует все 1M строк

# С индексом — Binary Search O(log n)
CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE email = "john@example.com";  -- Находит за ~20 проверок

2. Структуры индексов

B-Tree индекс (по умолчанию в PostgreSQL, MySQL)

# B-Tree — сбалансированное дерево поиска
# Хорошо для: <, >, <=, >=, =, BETWEEN, LIKE (если начинается с буквы)
#
#          40
#       /      \
#     20        60
#    /  \      /  \
#   10  30    50  70

CREATE INDEX idx_age ON users(age);

SELECT * FROM users WHERE age > 25 AND age < 50;
SELECT * FROM users WHERE age = 30;
SELECT * FROM users WHERE first_name LIKE "John%";

Hash индекс

# Hash — быстро для точного равенства
# Хорошо для: =
# Плохо для: <, >, BETWEEN

CREATE INDEX idx_hash ON users USING HASH(status);

SELECT * FROM users WHERE status = "active";  -- Очень быстро
SELECT * FROM users WHERE status > "a";       -- Не использует индекс!

BRIN индекс (Block Range Index)

# BRIN — для больших таблиц, сортированных по одному столбцу
# Занимает очень мало памяти
# Хорошо для: time series, логи

CREATE INDEX idx_created_at ON logs USING BRIN(created_at);

SELECT * FROM logs WHERE created_at > NOW() - INTERVAL "1 day";

GiST и GIN индексы (для полнотекстового поиска)

# GIN (Generalized Inverted Index) — для массивов и full-text search
# Быстрый поиск но медленное добавление

CREATE INDEX idx_tags ON posts USING GIN(tags);
SELECT * FROM posts WHERE tags @> ARRAY["python", "django"];

# Full-text search
CREATE INDEX idx_search ON articles USING GIN(to_tsvector("english", content));
SELECT * FROM articles WHERE to_tsvector("english", content) @@ plainto_tsquery("database");

3. Составные (Composite) индексы

# Один индекс на несколько столбцов
CREATE INDEX idx_user_email_status ON users(email, status);

# Может быть использован в следующих случаях:
SELECT * FROM users WHERE email = "john@example.com" AND status = "active";
SELECT * FROM users WHERE email = "john@example.com";  -- email есть в индексе

# НЕ будет использован:
SELECT * FROM users WHERE status = "active";  -- status не первый в индексе!

# Порядок столбцов ВАЖЕН для составных индексов!
# CREATE INDEX idx_wrong ON users(status, email);  -- Неправильный порядок

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

# Индекс только для подмножества строк
# Экономит место и ускоряет вставку

CREATE INDEX idx_active_users ON users(email) WHERE status = "active";

# Очень полезно для:
SELECT * FROM users WHERE status = "active" AND email = "john@example.com";

# Пример: индекс только для не удалённых записей
CREATE INDEX idx_users_active ON users(id) WHERE deleted_at IS NULL;

5. Уникальные индексы

# Гарантирует уникальность значений + ускоряет поиск
CREATE UNIQUE INDEX idx_email_unique ON users(email);

# Эквивалентно:
ALTER TABLE users ADD CONSTRAINT email_unique UNIQUE (email);

# Пример с составным индексом:
CREATE UNIQUE INDEX idx_user_post UNIQUE ON likes(user_id, post_id);

6. Индексы с NULL значениями

# B-Tree индексы включают NULL (в начало или конец)
CREATE INDEX idx_phone ON users(phone);

SELECT * FROM users WHERE phone IS NULL;      -- Использует индекс
SELECT * FROM users WHERE phone IS NOT NULL;  -- Может использовать индекс

# Если много NULL, рассмотри PARTIAL индекс:
CREATE INDEX idx_phone_not_null ON users(phone) WHERE phone IS NOT NULL;

7. EXPLAIN — анализ плана выполнения

# Посмотри как БД выполняет запрос
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = "john@example.com";

# Результат (без индекса):
# Seq Scan on users  (cost=0.00..35.50 rows=1 width=50)
#   Filter: (email = "john@example.com")
#   Planning Time: 0.082 ms
#   Execution Time: 2.341 ms

# С индексом:
# Index Scan using idx_email on users  (cost=0.29..8.30 rows=1 width=50)
#   Index Cond: (email = "john@example.com")
#   Planning Time: 0.123 ms
#   Execution Time: 0.245 ms  -- Намного быстрее!

8. Когда индекс НЕ используется

# 1. Преобразование столбца
SELECT * FROM users WHERE LOWER(email) = "john@example.com";  -- Не использует idx_email!
SELECT * FROM users WHERE email = "JOHN@EXAMPLE.COM";  -- Лучше

# 2. Арифметика
SELECT * FROM users WHERE age + 5 > 30;  -- Не использует idx_age!
SELECT * FROM users WHERE age > 25;     -- Правильно

# 3. Неправильный порядок для составного индекса
CREATE INDEX idx_first_last ON users(first_name, last_name);
SELECT * FROM users WHERE last_name = "Smith";  -- Не использует индекс!
SELECT * FROM users WHERE first_name = "John" AND last_name = "Smith";  -- Использует

# 4. LIKE без префикса
SELECT * FROM users WHERE email LIKE "%example.com";  -- Не использует индекс!
SELECT * FROM users WHERE email LIKE "john%";         -- Использует индекс

# 5. Неправильная статистика
ANALYZE users;  -- Обновить статистику

9. Типичные ошибки и оптимизация

# ❌ НЕПРАВИЛЬНО — индекс на столбец который редко ищут
CREATE INDEX idx_country ON users(country);

# ✅ ПРАВИЛЬНО — индекс на часто ищущиеся столбцы
CREATE INDEX idx_email ON users(email);      -- Часто ищут по email
CREATE INDEX idx_created_at ON posts(created_at);  -- Часто фильтруют по дате

# ❌ НЕПРАВИЛЬНО — слишком много индексов
CREATE INDEX i1 ON users(id);
CREATE INDEX i2 ON users(email);
CREATE INDEX i3 ON users(phone);
CREATE INDEX i4 ON users(status);
-- Замедляет INSERT и UPDATE!

# ✅ ПРАВИЛЬНО — минимум индексов, только нужные
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_status_created ON users(status, created_at);

10. Выбор стратегии индексирования

# Шаг 1: Найти медленные запросы
SELECT query, mean_time FROM pg_stat_statements
ORDER BY mean_time DESC LIMIT 10;

# Шаг 2: Анализировать план выполнения
EXPLAIN ANALYZE SELECT ...;

# Шаг 3: Добавить индекс на фильтруемые столбцы
CREATE INDEX idx_search ON users(email, status) WHERE deleted_at IS NULL;

# Шаг 4: Проверить результат
EXPLAIN ANALYZE SELECT ...;

# Шаг 5: Мониторить production
SELECT * FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;  -- Какие индексы используются

SELECT * FROM pg_stat_user_indexes
WHERE idx_scan = 0;  -- Неиспользуемые индексы (можно удалить)

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

# CREATE TABLE users с индексами
CREATE TABLE users (
    id SERIAL PRIMARY KEY,              -- PRIMARY KEY автоматически индексируется
    email VARCHAR(255) UNIQUE NOT NULL, -- UNIQUE также индексируется
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    status VARCHAR(20) DEFAULT "active",
    created_at TIMESTAMP DEFAULT NOW(),
    deleted_at TIMESTAMP
);

-- Дополнительные индексы
CREATE INDEX idx_email ON users(email);  -- Для поиска по email
CREATE INDEX idx_status_created ON users(status, created_at);
CREATE INDEX idx_deleted ON users(id) WHERE deleted_at IS NULL;  -- Partial
CREATE INDEX idx_search ON users(first_name, last_name);  -- Для сортировки

-- Мониторинг
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE tablename = "users"
ORDER BY idx_scan DESC;

Итоговые рекомендации

1. Индексируй правильно — только часто ищущиеся столбцы
2. Используй EXPLAIN — всегда проверяй план выполнения
3. Составные индексы — лучше чем много простых
4. Partial индексы — для подмножеств данных
5. Мониторь использование — удаляй неиспользуемые индексы
6. Не переусложняй — каждый индекс замедляет INSERT/UPDATE
7. Анализируй регулярно — ANALYZE обновляет статистику

Правильное индексирование может ускорить запросы в 1000 раз!