Что такое составной индекс и как он работает

Что проверяют

Связываете ли вы три вещи: миграцию, запрос, который порождает код, и план его выполнения. PHP-разработчик обычно работает с MySQL и ORM, где запрос собирается в коде, а индексы объявляются в миграциях, и проверяют, умеете ли вы свести одно с другим.

Как отвечать

Составной индекс это индекс по нескольким колонкам, ключ собран из значений в порядке объявления.

Правило левого префикса: индекс по (a, b, c) применим для условий по a, по a и b, по всем трём сразу. Запрос по одному c его не использует. Порядок предикатов в WHERE не важен, важен порядок колонок в индексе.

В InnoDB в листьях вторичного индекса лежит первичный ключ, и за остальными колонками база идёт в кластерный индекс. Если всё нужное уже есть в самом индексе, EXPLAIN покажет Using index и лишнего чтения не будет.

Диапазон обрывает цепочку: если по a равенство, по b диапазон, по c равенство, то индекс по (a, b, c) применит для поиска только a и b, а c останется фильтром.

Schema::table('orders', function (Blueprint $t) {
    $t->index(['user_id', 'status', 'created_at']);
});

EXPLAIN SELECT id, total FROM orders
WHERE user_id = ? AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

Где спотыкаются

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

Прячут колонку под функцией или сравнивают разные типы: условие по дате, обёрнутой в функцию, или строковая колонка против числа отключают индекс. Тот же эффект даёт соединение таблиц по колонкам с разными кодировками.

Плодят дубли. При наличии индекса по (a, b, c) отдельный индекс по a избыточен: он покрыт префиксом, но продолжает занимать место и замедлять каждую вставку.