Какой индекс используется при постоянном запросе к таблице по нескольким полям?

Оптимизация запросов по нескольким полям

При постоянных запросах к таблице по нескольким полям оптимальным решением является создание составного индекса (composite index). Этот тип индекса включает два или более столбца, что позволяет эффективно выполнять фильтрацию, сортировку и соединения по указанным полям.

Ключевые принципы составных индексов

1. Порядок столбцов критически важен – индекс работает слева направо. Запросы должны использовать префиксные комбинации столбцов.
2. Кардинальность – столбцы с высокой уникальностью (например, id) следует размещать раньше в индексе.
3. Охватывающие индексы – если индекс содержит все поля, упомянутые в запросе, происходит "покрытие" (covering), что исключает чтение данных таблицы.

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

Рассмотрим таблицу пользователей с частыми запросами по country_id и status:

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255),
    country_id INT,
    status ENUM('active', 'inactive', 'pending'),
    created_at TIMESTAMP,
    INDEX idx_country_status (country_id, status)
);

Варианты использования индекса

Эффективные запросы (используют префикс индекса):

SELECT * FROM users WHERE country_id = 5 AND status = 'active';
SELECT * FROM users WHERE country_id = 5; -- Использует только первую часть индекса

Неэффективные запросы (не используют префикс):

SELECT * FROM users WHERE status = 'active'; -- Не использует индекс idx_country_status
-- Для этого случая нужен отдельный индекс на status

Дополнительные стратегии индексирования

Индекс с включенными столбцами

Для запросов с дополнительными полями в SELECT можно создать покрывающий индекс:

CREATE INDEX idx_country_status_covering ON users (country_id, status) 
INCLUDE (email, created_at);
-- Или для MySQL (включение через добавление в индекс):
CREATE INDEX idx_country_status_covering ON users (country_id, status, email);

Многоколоночные индексы для сортировки

Если запросы включают ORDER BY, порядок столбцов в индексе должен соответствовать порядку сортировки:

-- Для запроса: WHERE country_id = 5 ORDER BY status
CREATE INDEX idx_country_status_sort ON users (country_id, status);

Частичные индексы (для PostgreSQL)

Для фильтрации по конкретным значениям:

CREATE INDEX idx_active_users ON users (country_id) 
WHERE status = 'active';

Рекомендации по проектированию

1. Анализируйте реальные запросы с помощью EXPLAIN:

EXPLAIN SELECT * FROM users WHERE country_id = 1 AND status = 'active';

1. Мониторьте производительность – используйте медленный лог запросов (slow query log).

2. Балансируйте – каждый дополнительный индекс замедляет операции INSERT/UPDATE/DELETE.

3. Рассмотрите альтернативы:

   • Индексы на выражениях для сложных условий
   • Хэш-индексы для точных совпадений (PostgreSQL)
   • Полнотекстовые индексы для текстового поиска

Типичные ошибки

1. Создание избыточных индексов (дублирующих друг друга)
2. Неучет порядка столбцов при проектировании
3. Игнорирование кардинальности данных
4. Отсутствие пересмотра индексов при изменении паттернов запросов

Для максимальной эффективности регулярно анализируйте выполнение запросов и адаптируйте индексы под актуальные рабочие нагрузки. В современных СУБД также полезно использовать инструменты автоматической настройки индексов.