Что такое селективность индекса и почему не всегда индекс используется?

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

Понимаете ли, что решение об использовании индекса принимает оптимизатор по стоимости, а не по наличию индекса. От Java-разработчика ждут умения объяснить, почему запрос, сгенерированный слоем доступа к данным, идёт мимо индекса, который в схеме есть.

Как отвечать

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

Причины, по которым индекс не используется, стоит перечислить по группам. Условие недостаточно избирательно. Предикат нельзя применить к индексу: над колонкой вызвана функция, происходит неявное приведение типа, шаблон поиска начинается с подстановочного знака. Статистика устарела или данные перекошены. В составном индексе условие не попадает на ведущие колонки. Условия соединены через OR по разным колонкам. Сортировка или пагинация уводят план к другому индексу.

Для Java отдельно полезны типичные источники таких запросов. Слой сохранения передаёт параметр не того типа, и на стороне базы появляется приведение, убивающее индекс. Метод репозитория с поиском по вхождению превращается в шаблон с подстановочным знаком в начале. Регистронезависимый поиск через функцию требует индекса по выражению. Поэтому смотрят на реально отправленный SQL в журнале и на план в базе, а не на имя метода в интерфейсе.

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

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

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

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