Составной индекс MySQL: порядок колонок начинается с запроса

Список последних заказов клиента фильтрует по одному столбцу, а сортирует по другому. Два отдельных индекса не всегда заменяют один составной. В MySQL 8.0 порядок столбцов такого индекса стоит выбирать по запросу. Посмотрим на левый префикс, план выполнения и цену дополнительного индекса при записи.

Заказы одного клиента

SELECT id, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20;

CREATE INDEX orders_customer_created_id
ON orders(customer_id, created_at, id);

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

Помним о левом префиксе

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

EXPLAIN SELECT id, created_at
FROM orders WHERE customer_id = 42
ORDER BY created_at DESC, id DESC LIMIT 20;

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

Цена записи

Новый индекс занимает место и замедляет изменения строк. Перед созданием проверьте, нет ли уже подходящего левого префикса. На рабочей базе оцените время и ресурсы построения. После этого сравните выигрыш чтения с затратами записи — так будет понятно, стоит ли индекс оставлять.

Источники и дальнейшее чтение

• MySQL 8.0: составные индексы
• MySQL 8.0: EXPLAIN