Как определить порядок полей в составном индексе, если хочешь добавить индекс на три поля?

Определение порядка полей в составном индексе

При создании составного индекса на три поля в базах данных (например, MySQL, PostgreSQL) порядок полей имеет критическое значение, поскольку влияет на производительность запросов. Основной принцип: индекс работает слева направо, то есть он эффективен для запросов, которые используют префикс полей в том же порядке, в котором они объявлены в индексе.

Ключевые факторы для определения порядка

1. Селективность полей:

   • Располагайте первыми наиболее селективные поля (с высоким кардинальностью), которые фильтруют больше данных. Например, поле user_id с тысячами уникальных значений селективнее, чем status с 3-4 значениями.
   • Пример для индекса (user_id, status, created_at):

     -- Хорошо: использует префикс индекса
     SELECT * FROM orders WHERE user_id = 100 AND status = 'active';
     
     -- Плохо: индекс неэффективен, т.к. status неселективен и идет первым
     SELECT * FROM orders WHERE status = 'active';
     

2. Частота использования в запросах:

   • Анализируйте WHERE, JOIN и ORDER BY в частых запросах.
   • Если запросы часто фильтруют по A и B, но редко по C, индекс должен быть (A, B, C).
   • Пример:

     -- Частый запрос: использует все три поля
     SELECT * FROM logs WHERE app_id = 5 AND level = 'ERROR' AND date >= '2023-01-01';
     -- Оптимальный индекс: (app_id, level, date)
     

3. Операции сортировки (ORDER BY):

   • Индекс может покрывать сортировку, если порядок полей в ORDER BY совпадает с порядком в индексе (или его префиксом).
   • Для запроса с ORDER BY date, user_id индекс (date, user_id, status) будет эффективен.
   • Пример:

     -- Индекс (category, price, stock) ускорит и фильтрацию, и сортировку
     SELECT * FROM products WHERE category = 'electronics' ORDER BY price, stock;
     

4. Покрывающие индексы (Covering Index):

   • Если индекс включает все поля запроса, база данных может выполнить запрос, обращаясь только к индексу (без чтения таблицы). Добавьте в конец индекса поля из SELECT.
   • Пример:

     -- Покрывающий индекс: (department, salary, name)
     SELECT name, salary FROM employees WHERE department = 'Sales' AND salary > 5000;
     

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

Предположим, есть таблица orders с полями:

• user_id (селективный, используется в 80% запросов)
• status (низкая селективность, 5 значений)
• created_at (высокая селективность, диапазон дат)

Анализ запросов:

-- Запрос 1: частый
SELECT * FROM orders WHERE user_id = 10 AND status = 'shipped';
-- Запрос 2: частый с сортировкой
SELECT * FROM orders WHERE user_id = 10 ORDER BY created_at DESC;
-- Запрос 3: редкий
SELECT * FROM orders WHERE status = 'shipped' AND created_at > '2023-01-01';

Решение:

• Создать индекс (user_id, created_at, status).
• Обоснование:
  • user_id идет первым из-за высокой частоты использования и селективности.
  • created_at добавлен вторым для покрытия сортировки по дате.
  • status включен последним для покрытия запроса 1, хотя он менее селективен.

Создание индекса в MySQL:

ALTER TABLE orders ADD INDEX idx_user_created_status (user_id, created_at, status);

Тестирование и мониторинг

Всегда проверяйте планы выполнения запросов с помощью EXPLAIN:

EXPLAIN SELECT * FROM orders WHERE user_id = 10 AND status = 'shipped' ORDER BY created_at;

Ключевые показатели: использование индекса (Using index), временные таблицы (Using temporary), файловая сортировка (Using filesort).

Итог: порядок полей должен максимально соответствовать реальным запросам приложения, учитывая селективность, фильтрацию и сортировку. Составные индексы требуют баланса между разными сценариями использования, и оптимальный выбор зависит от конкретной нагрузки.