Какие правила необходимо учитывать при создании составных индексов?

При проектировании составных (композитных) индексов в SQL-базах данных я руководствуюсь следующими правилами:

1. Порядок столбцов (самое важное): Индекс работает по принципу левого префикса. Запрос может использовать индекс, только если условие WHERE, ORDER BY или JOIN включает столбцы с начала индекса.

   • Первым должен идти наиболее селективный столбец (с наибольшим количеством уникальных значений).
   • Пример: Для запроса SELECT * FROM orders WHERE user_id = 123 AND status = 'shipped' оптимален индекс (user_id, status), а не (status, user_id), так как user_id обычно более селективен.

2. Охват запроса (Covering Index): Если добавить в индекс все столбцы, запрашиваемые в SELECT, база данных может выполнить запрос, обращаясь только к индексу (Index-Only Scan), что значительно ускоряет работу.

   -- Индекс (user_id, created_at) покроет этот запрос
   SELECT user_id, created_at FROM logs WHERE user_id = 5 ORDER BY created_at DESC;

3. Условия WHERE и ORDER BY: Если запрос содержит и фильтрацию, и сортировку, порядок столбцов в индексе должен соответствовать: сначала столбцы для WHERE (по равенству), затем для ORDER BY.

   -- Оптимальный индекс: (department_id, salary)
   SELECT * FROM employees WHERE department_id = 10 ORDER BY salary DESC;

4. Кардинальность и размер: Слишком длинные индексы (много столбцов или столбцы с большим типом данных, как TEXT) занимают много памяти и могут замедлять операции вставки/обновления.

5. Ограничения СУБД: Нужно знать лимиты своей СУБД (например, максимальная длина индекса в MySQL — 3072 байта для InnoDB).