На какие столбцы создавать индексы и как выбирать их по селективности и составу?

Как выбрать столбцы для создания индексов?

• создавать индексы на столбцах с высокой селективностью, содержащих много уникальных значений
• учитывать, как часто столбцы используются в WHERE и JOIN
• для столбцов, регулярно применяемых вместе, использовать составные индексы
• в составном индексе располагать сначала столбец, который сильнее всего ограничивает выборку
• не создавать индексы без необходимости на столбцах с низкой селективностью, например флагах и boolean
• сопоставлять пользу индекса с затратами на его поддержание, поскольку индексы замедляют INSERT/UPDATE
• проверять планы запросов и оценивать эффективность по страницам данных

Итог: индексировать стоит часто используемые в условиях столбцы, которые существенно уменьшают объём выборки и ускоряют поиск и соединения.

Подробный ответ

Основной ответ

Индексы обычно создают для столбцов, участвующих в фильтрации, соединениях (JOIN) и сортировке. Это позволяет быстрее находить нужные строки и сокращает время выполнения запросов. Один из главных критериев выбора — селективность, то есть доля уникальных значений среди всех значений столбца: чем она выше, тем больше пользы приносит индекс. Для запросов с несколькими условиями также применяют составные индексы, при этом учитывают порядок столбцов и их использование в WHERE и ORDER BY.

Ключевые моменты

• Показатель селективности особенно важен при выборе столбца. Если в нём много уникальных значений, как в email или id, индекс быстро исключает ненужные строки. Для низкоселективных столбцов, например полей пола или boolean, индекс часто не даёт выигрыша и в некоторых случаях способен замедлить запрос.
• При создании составных индексов порядок полей должен соответствовать характерным шаблонам поиска: индекс (A, B) эффективно применяется в запросах с фильтрацией по A или A и B, но не при фильтрации только по B.
• Использование индексов наиболее оправдано для столбцов, которые регулярно участвуют в условиях выборки (WHERE), соединениях (JOIN ON), сортировках (ORDER BY) и группировках (GROUP BY).
• Также необходимо учитывать стоимость поддержки индексов: каждый лишний индекс увеличивает накладные расходы при вставке и обновлении данных и замедляет операции записи.

Практический контекст

В прикладных проектах на PostgreSQL 14+ или MySQL 8 часто индексируют ключевые внешние ключи, чтобы ускорить JOIN, а также столбцы, регулярно используемые в условиях фильтрации. Для сложных отчётов применяют составные индексы. Их фактическую пользу и использование проверяют по планам выполнения запросов (EXPLAIN).