В таблице часто ищут записи по двум полям и сортируют результат по третьему. Как выбрать порядок колонок составного индекса?
Порядок колонок составного индекса выбирают по форме конкретного запроса: сначала обычно ставят поля с условиями точного равенства, затем поле с диапазоном, а поле сортировки — до диапазонного поля или после него в зависимости от того, какие части запроса индекс должен обслуживать. Универсального правила «сначала самая селективная колонка» нет: важнее совместимость индекса с фильтрацией и сортировкой.
Составные индексы появились как способ одним упорядоченным индексом поддерживать часто используемые многоколонные запросы. Без такого индекса хранилищу пришлось бы просматривать значительную часть таблицы, строить дополнительные структуры для сортировки или объединять несколько отдельных индексов.
Индекс хранит записи в порядке, определяемом последовательностью колонок. Поэтому перестановка колонок меняет не только стоимость построения индекса, но и набор запросов, которые могут эффективно использовать его начальный префикс.
Предположим, запрос фильтрует записи по tenant_id и status, а затем сортирует их по created_at. Индекс с порядком колонок tenant_id, status, created_at хорошо соответствует запросу, если первые два условия — равенство: после выбора конкретного арендатора и статуса записи уже идут в нужном порядке по времени.
Но если одно из условий является диапазоном, например «дата больше заданной», индекс может эффективно ограничить диапазон, однако последующие колонки не всегда смогут одновременно поддержать сортировку. Неправильный порядок приводит к сканированию большого участка индекса, дополнительной сортировке, росту задержки и нагрузке на память.
Сначала нужно выписать реальные шаблоны запросов: какие поля сравниваются на равенство, какие — диапазоном, какие используются для сортировки, насколько часто выполняется каждый шаблон и сколько строк он возвращает.
Для запроса с несколькими равенствами типичный порядок таков: поля равенства, затем поле сортировки, если сортировка должна выполняться индексом. Если присутствует диапазонное условие, его часто размещают после равенств. Колонки после диапазона обычно хуже помогают фильтрации и могут перестать обеспечивать глобальный порядок выдачи.
Например, для условий tenant_id = ..., status = ... и сортировки по created_at естественным кандидатом будет индекс (tenant_id, status, created_at). Он позволяет найти нужный участок по первым двум колонкам и читать его уже в порядке created_at.
Если же запросы обычно используют tenant_id = ..., created_at > ... и затем сортируют по priority, индекс (tenant_id, created_at, priority) может хорошо ограничивать временной диапазон, но не обязан избавлять от сортировки по priority. В такой ситуации нужно решить, что важнее: быстро сузить множество по диапазону или избежать сортировки.
Отдельные индексы по каждой колонке не являются полноценной заменой составному индексу. Оптимизатор иногда может объединять их, но результат зависит от конкретного хранилища и часто уступает индексу, который напрямую соответствует форме запроса.
Нужно проверять план выполнения на реалистичных данных, а не только наличие индекса в схеме. Также учитываются стоимость вставок и обновлений, дополнительное место, изменение распределения значений и необходимость покрывать индексом возвращаемые поля.
В системе задач запросы команды фильтровали по project_id и state, а затем показывали последние задачи по updated_at. Сначала создали отдельные индексы по каждому полю; база отбирала кандидатов по нескольким структурам, объединяла результаты и иногда выполняла дорогую сортировку.
Рассматривались два варианта. Индекс (updated_at, project_id, state) хорошо поддерживал общий порядок времени, но плохо сужал выборку до конкретного проекта. Индекс (project_id, state, updated_at) быстро находил нужную группу и отдавал её в требуемом порядке, однако был полезен прежде всего для запросов, начинающихся с project_id.
Выбрали второй вариант, потому что это был основной и наиболее частый шаблон, а запросы без фильтра по проекту имели отдельный сценарий с другой стратегией выдачи. После проверки плана выполнения исчезла необходимость сортировать большую промежуточную выборку, но индекс увеличил стоимость записи, поэтому его не стали добавлять для всех близких, но редко используемых запросов.
Нет. Селективность важна, но не является единственным критерием. Если запрос имеет равенство по полю A и диапазон по полю B, индекс (A, B) обычно лучше соответствует его структуре, даже если B в среднем селективнее. Кроме того, порядок должен учитывать частоту запросов и возможность использовать индекс для сортировки.
Потому что порядок индекса должен оставаться однозначным для всего диапазона поиска. После условия диапазона по колонке порядок последующих колонок может быть независимым внутри разных значений диапазонной колонки, поэтому он не всегда соответствует требуемой сортировке. Точное поведение зависит от оптимизатора и условий запроса, поэтому его проверяют по фактическому плану выполнения.
Не обязательно. Добавление возвращаемых колонок может позволить выполнить покрывающий индексный доступ и не обращаться к таблице, но увеличивает размер индекса и стоимость записи. Это оправдано для очень частого запроса с небольшим набором возвращаемых полей, если выигрыш подтверждён измерениями; для широких или изменяемых данных такой индекс может оказаться слишком дорогим.