Какой порядок фильтрации эффективнее по уникальности и селективности

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

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

Как отвечать

Сначала термины. Селективность условия это доля строк, которые оно пропускает: чем меньше доля, тем выше селективность. Уникальность, или кардинальность, это число различных значений в колонке. Дальше три контекста:

• код на Java: цепочки && и filter() в Stream API выполняются слева направо, && с коротким замыканием. Первым ставьте условие, которое дёшево и отсекает больше всего. Для независимых условий строгое правило такое: сортировать по отношению стоимости проверки к доле отсекаемых элементов, по возрастанию. Дорогое условие с походом в базу или регуляркой идёт последним, даже если оно селективное;
• distinct() и sorted(): операции с состоянием, distinct() держит набор всех встреченных элементов. Ставьте их после фильтров, чтобы через них прошло меньше данных;
• SQL: порядок условий в WHERE оптимизатор PostgreSQL, MySQL или Oracle не учитывает, план строится по статистике. Важен порядок колонок в составном индексе: сначала колонки с равенством, потом колонка с диапазоном. Индекс (status, created_at) обслуживает status = ? AND created_at > ?, а (created_at, status) для того же запроса заметно хуже. Среди колонок с равенством порядок решают другие запросы: первой ставят ту, по которой бывают и одиночные условия.

В JPA и Hibernate порядок предикатов в Criteria API или JPQL на план тоже не влияет, смотреть нужно на сгенерированный SQL и EXPLAIN.

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

Путают направление: называют высокоселективным условие, которое пропускает почти всё. Такое условие как раз низкоселективное.

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

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