Как строить составные индексы?

Построение составных индексов в базах данных

Составной индекс (композитный, составной) — это индекс, созданный на основе двух или более столбцов таблицы. Он позволяет оптимизировать запросы, которые выполняют фильтрацию, сортировку или соединение по нескольким полям одновременно. Правильное построение таких индексов критически важно для производительности СУБД.

Принципы построения составных индексов

1. Порядок столбцов имеет ключевое значение

Составной индекс эффективен для запросов, которые используют префикс (левую часть) индекса. Например, для индекса (col1, col2, col3):

• Работает для условий по col1
• Работает для условий по col1, col2
• Работает для условий по col1, col2, col3
• Не работает для условий только по col2 или col3 отдельно (пропуск столбцов слева)

-- Пример создания составного индекса
CREATE INDEX idx_users_name_department 
ON users (last_name, department_id);

2. Кардинальность столбцов

Располагайте столбцы с более высокой кардинальностью (большим количеством уникальных значений) первыми:

• Высокая кардинальность: user_id, email
• Низкая кардинальность: gender, status

-- Правильный порядок: сначала высококардинальные столбцы
CREATE INDEX idx_orders_user_date 
ON orders (user_id, order_date); -- user_id имеет больше уникальных значений

3. Учет условий WHERE и ORDER BY

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

-- Запрос для оптимизации
SELECT * FROM products 
WHERE category_id = 5 
AND price > 1000 
ORDER BY created_at DESC;

-- Оптимальный индекс
CREATE INDEX idx_products_category_price_created 
ON products (category_id, price, created_at);

Практические рекомендации

Правило ESR (Equality, Sort, Range):

Располагайте столбцы в порядке:

1. Equality — столбцы с проверкой на равенство (=, IN)
2. Sort — столбцы для сортировки (ORDER BY)
3. Range — столбцы с диапазонными условиями (>, <, BETWEEN)

-- Пример запроса
SELECT * FROM logs 
WHERE app = 'api' 
AND level = 'ERROR' 
AND timestamp >= '2024-01-01'
ORDER BY timestamp DESC;

-- Индекс по правилу ESR
CREATE INDEX idx_logs_app_level_timestamp 
ON logs (app, level, timestamp DESC);

Покрывающие индексы

Составной индекс может стать покрывающим (covering index), если содержит все столбцы, необходимые для запроса:

// Пример запроса в Go-приложении
query := `SELECT id, name, email FROM users 
          WHERE city = $1 AND age > $2 
          ORDER BY created_at`

// Оптимальный покрывающий индекс
// CREATE INDEX idx_users_city_age_created 
// ON users (city, age, created_at) INCLUDE (id, name, email)

Распространенные ошибки

1. Избыточные индексы:

-- НЕПРАВИЛЬНО: дублирование функциональности
CREATE INDEX idx1 ON table (a, b);
CREATE INDEX idx2 ON table (a); -- Избыточно, idx1 уже покрывает фильтрацию по 'a'

1. Игнорирование типа данных и коллаций:

-- Проблемы с производительностью при разных collation
CREATE INDEX idx_name ON users (last_name COLLATE "en_US", first_name COLLATE "ru_RU");

1. Индексы для низкокардинальных столбцов:

-- Антипаттерн: индекс на столбец с 3-5 значениями
CREATE INDEX idx_gender ON users (gender); -- Обычно неэффективно

Специфика для Go-разработчика

В Go-приложениях важно анализировать паттерны запросов ORM или query builder:

// GORM пример с неправильным подходом
db.Where("department_id = ?", deptID).
   Order("created_at desc").
   Find(&users)
// Требуется индекс: (department_id, created_at DESC)

// SQLx пример с правильной оптимизацией
query := `
    SELECT u.*, d.name 
    FROM users u 
    JOIN departments d ON u.department_id = d.id 
    WHERE u.active = true 
    AND u.role IN (?, ?) 
    ORDER BY u.last_login DESC`

// Рекомендуемые индексы:
// CREATE INDEX idx_users_active_role_login ON users (active, role, last_login DESC)
// CREATE INDEX idx_departments_id_name ON departments (id) INCLUDE (name)

Мониторинг и анализ

1. Используйте EXPLAIN ANALYZE для анализа планов запросов
2. Отслеживайте индексные сканирования vs последовательные сканирования
3. Регулярно пересматривайте индексы на основе реальной нагрузки
4. Учитывайте баланс между производительностью чтения и стоимостью записи (индексы замедляют INSERT/UPDATE/DELETE)

Правильное построение составных индексов требует глубокого понимания структуры данных, паттернов доступа и конкретной СУБД. Начните с анализа самых частых и тяжелых запросов, используйте правило ESR как отправную точку, и постоянно проводите тестирование производительности с реальными данными.