Размещение столбца с высокой кардинальностью в составных индексах с диапазонными запросами

При запросе к таблице с составным индексом, включающим условие диапазона, размещение столбцы в индексе могут существенно повлиять на производительность.

Рассмотрим файлы таблиц с первичным ключом (did, имя файла) и двумя составными индексы: INDEX(filetime, ext) и INDEX(ext, filetime). Оба индекса содержат столбец filetime, мощность которого выше, чем ext.

Запрос:

WHERE ext = '...'
  AND filetime BETWEEN ... AND ...

требует доступа к данным на основе как ext, так и filetime. Возникает вопрос: какой индекс оптимален для такого запроса?

Анализ

Чтобы определить оптимальный индекс, мы можем использовать FORCE INDEX и изучить планы выполнения:

-- Force range on filetime first
FORCE INDEX(fe) SELECT COUNT(*), AVG(fsize)
FROM files
WHERE ext = 'gif'
  AND filetime >= '2015-01-01'
  AND filetime = '2015-01-01'
  AND filetime 
Вывод показывает, что INDEX(ext, filetime) (ef) имеет значительно меньшее количество строк, что указывает на более эффективное сканирование.
Трассировка оптимизатора
Для дальнейшего анализа поведения оптимизатора мы можем использовать оптимизатор трассировка:
SELECT explain_format = 'JSON';

SELECT COUNT(*), AVG(fsize)
FROM files
WHERE ext = 'gif'
  AND filetime >= '2015-01-01'
  AND filetime 
Трассировка показывает, что оптимизатор выбирает INDEX(ext, filetime), поскольку он может использовать оба столбца индекса для фильтрации и выборки данных. Напротив, INDEX(filetime, ext) может использовать для фильтрации только первый столбец (filetime).
Выводы
На основе анализа можно сделать следующие выводы. draw:

• Для составных индексов, используемых в запросах диапазона, должен быть помещен столбец, участвующий в предикате равенства (в данном случае ext) сначала в определении индекса.
• Производительность запросов повышается, когда столбцы в индексе упорядочиваются в соответствии с порядком, в котором они используются в предложении WHERE.
• Количество элементов само по себе не имеет решающего значения. фактор при определении оптимального индекса. В сценариях, где столбец диапазона имеет более высокую мощность, но столбец равенства участвует в предикате равенства, размещение столбца равенства первым повышает производительность.