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

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

Умеете ли вы объяснить, почему запрос быстрый или медленный, глядя на схему и план. Для Java-разработчика вопрос обычно упирается ещё и в другое: понимает ли он, какой именно SQL порождает его ORM и попадает ли этот SQL в существующие индексы.

Как отвечать

Составной индекс это индекс по нескольким колонкам. Ключ в B-дереве это кортеж значений в объявленном порядке, поэтому записи отсортированы по первой колонке, внутри равных значений по второй и так далее.

Отсюда правило левого префикса: индекс по (a, b, c) годится для условий по a, по a и b, по a, b и c. Для поиска по одному b он бесполезен, порядка по b в дереве нет. Порядок предикатов в самом WHERE при этом роли не играет, значение имеет порядок колонок в индексе.

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

CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at);
-- покрывает: WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20

Если в индекс входят все колонки, нужные запросу, он становится покрывающим и обращения к таблице не будет. В JPA индекс объявляют в аннотации таблицы, но решает не аннотация, а план запроса.

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

Заводят по отдельному индексу на каждую колонку и ждут эффекта составного. Объединить их в одном поиске база умеет, но обычно это заметно дороже одного правильно упорядоченного индекса.

Ставят первой самую селективную колонку по привычке. Первой должна идти та, по которой условие есть в каждом запросе, иначе индекс не подойдёт ни одному из них.

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