Как выбрать какой индекс создать

# Как выбрать какой индекс создать

Выбор правильного индекса — это один из ключевых факторов оптимизации производительности БД. Вот систематический подход к этому решению.

Шаг 1: Определи узкие места (Profiling)

Проанализируй slow query log

-- MySQL
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;

-- PostgreSQL
SET log_min_duration_statement = 500;

Используй EXPLAIN и EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = john@example.com AND status = active;

-- Результат:
-- Seq Scan on users  (cost=0.00..1234.56 rows=1) ← полное сканирование таблицы!
-- Filter: (email = john@example.com AND status = active)

Если видишь Seq Scan (Sequential Scan) — это признак того, что нужен индекс.

Шаг 2: Выбери колонки для индекса

Правило 1: Индексируй колонки в WHERE

-- Запрос
SELECT * FROM orders WHERE user_id = 123 AND status = pending;

-- Индекс
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

Правило 2: Порядок колонок в индексе важен

Для комбинированного индекса порядок влияет на эффективность:

-- Индекс (user_id, status)
CREATE INDEX idx ON orders(user_id, status);

-- ✅ Используется полностью (оба условия)
SELECT * FROM orders WHERE user_id = 123 AND status = pending;

-- ✅ Используется по user_id
SELECT * FROM orders WHERE user_id = 123;

-- ❌ НЕ используется для status (нужно сначала user_id)
SELECT * FROM orders WHERE status = pending;

Лучший порядок:

1. Колонки из WHERE с оператором =
2. Колонки из ORDER BY
3. Колонки из SELECT (covering index)

Правило 3: Индексируй колонки для JOIN

-- Запрос
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = active;

-- Индексы
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_users_status ON users(status);

Правило 4: Используй covering indexes

-- Запрос
SELECT id, email, created_at FROM users WHERE status = active;

-- Обычный индекс
CREATE INDEX idx_users_status ON users(status);
-- БД найдёт rows по индексу, но потом обратится к основной таблице (Index Lookup)

-- Covering index (включает все нужные колонки)
CREATE INDEX idx_users_status_cover ON users(status, id, email, created_at);
-- БД найдёт всё в самом индексе (Index Only Scan) — быстрее!

Шаг 3: Выбери тип индекса

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

CREATE INDEX idx ON table(col);
-- Подходит для: =, <, >, <=, >=, BETWEEN, LIKE
-- Лучший выбор в 90% случаев

Hash Index

CREATE INDEX idx ON table USING HASH(col);
-- Подходит для: только =
-- Быстрее B-Tree для точного поиска, но не поддерживает диапазоны

Bitmap Index (Oracle, PostgreSQL с расширением)

-- Для колонок с малым числом уникальных значений
CREATE INDEX idx ON users(status);
-- Если статус = active, pending, inactive (всего 3)
-- Bitmap Index эффективен

Full-Text Index

CREATE FULLTEXT INDEX idx ON articles(title, body);
SELECT * FROM articles WHERE MATCH(title) AGAINST(java IN BOOLEAN MODE);

GiST / GIN (PostgreSQL)

-- Для массивов и полнотекстового поиска
CREATE INDEX idx_tags ON posts USING GIN(tags);

Шаг 4: Оцени стоимость

Индекс стоит памяти и времени на INSERT/UPDATE/DELETE

// Мониторинг индексов в приложении
public class IndexOptimizationExample {
    public static void main(String[] args) {
        // Слишком много индексов замедляет INSERT
        // 1 таблица = не более 4-6 индексов обычно
        
        // Баланс:
        // - SELECT быстрее (читаем часто)
        // - INSERT медленнее (пишем редко)
    }
}

Чеклист выбора индекса

✅ Запрос в slow query log? ✅ EXPLAIN показывает Seq Scan? ✅ Колонка используется в WHERE? ✅ Высокая selectivity (много уникальных значений)? ✅ Запрос выполняется часто? ✅ Таблица большая (> 10k rows)? ✅ Индекс не замедлит INSERT/UPDATE/DELETE?

Если все галочки — создавай индекс.

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

-- Исходная проблема
EXPLAIN ANALYZE SELECT * FROM users 
WHERE created_at >= 2024-01-01 AND status = active;
-- Seq Scan on users (cost=0.00..10000.00)

-- Решение
CREATE INDEX idx_users_created_status ON users(created_at, status);

-- Результат
EXPLAIN ANALYZE SELECT * FROM users 
WHERE created_at >= 2024-01-01 AND status = active;
-- Index Range Scan (cost=0.42..150.00) ← в 60 раз быстрее!

Ошибки при выборе индекса

❌ Индексируешь каждую колонку подряд ❌ Не проверяешь selectivity ❌ Создаёшь дублирующиеся индексы ❌ Не следишь за неиспользуемыми индексами ❌ Забываешь про стоимость обновления

Выбор индекса — это баланс между скоростью чтения и стоимостью записи. Используй EXPLAIN, профилируй реальные запросы и итеративно улучшай индексацию.