Внутреннее соединение фильтрует одну таблицу по ключу, но индекс есть только на второй: какой вывод предиката позволяет использовать этот индекс?
Оптимизатор может вывести транзитивный предикат: если значения ключей двух таблиц равны при соединении, а ключ первой таблицы ограничен конкретным значением, то такой же фильтр можно применить ко второй таблице. Благодаря этому индекс второй таблицы становится пригодным для поиска, хотя соответствующее условие явно не написано в запросе.
Реляционные оптимизаторы изначально стремились преобразовывать логически эквивалентные выражения в более дешёвые физические планы. Одно из таких преобразований основано на правилах реляционной алгебры и равенстве: из условий «A равно B» и «A равно константе» следует «B равно той же константе».
Это позволяет протолкнуть фильтры ближе к источникам данных и уменьшить объём строк до соединения. Без такого преобразования оптимизатор мог бы сначала читать большую часть второй таблицы, а затем отбрасывать строки во время соединения.
Рассмотрим соединение заказов с клиентами. Фильтр по идентификатору клиента задан только для таблицы клиентов, а на таблице заказов имеется индекс по идентификатору клиента. Если оптимизатор не выведет дополнительное условие, индекс заказов может остаться неиспользованным.
Неверный или чрезмерно агрессивный вывод предикатов опасен: он должен сохранять семантику SQL, включая поведение с NULL, дубликатами и внешними соединениями. Поэтому такое преобразование безопасно не во всех видах соединений и не для любых сравнений.
Для внутреннего соединения оптимизатор может рассуждать так: clients.id = orders.client_id и clients.id = 42 означают, что orders.client_id = 42. Второй предикат является транзитивным и может быть использован как условие индексного поиска по таблице заказов.
Если на orders.client_id есть индекс, физический план может начать с поиска заказов по значению 42, а затем соединить найденные строки с клиентом. Конкретный выбор зависит от стоимости альтернатив: размера таблиц, селективности фильтра, статистики, стоимости случайного чтения и доступных индексов.
Транзитивный вывод не означает, что индекс будет выбран всегда. Если значение встречается в большинстве строк, полное сканирование может быть дешевле. Кроме того, устаревшая статистика способна привести к неверной оценке селективности и неудачному плану.
Для LEFT JOIN перенос фильтра с сохранением исходной семантики сложнее. Условие, применённое к правой таблице в разных частях запроса, может изменить результат, особенно когда подходящей строки нет и правая сторона заполняется NULL. Аналогичные ограничения возникают при неравенствах, преобразованиях типов и выражениях, для которых равенство не обладает нужными свойствами.
В системе заказов таблица клиентов содержала несколько миллионов строк, а заказы — сотни миллионов. Запрос ограничивал одного клиента, но индекс был только на orders.client_id; индекс на clients.id отсутствовал, поскольку clients.id являлся первичным ключом.
Рассматривались варианты: принудительно указать индекс заказов, переписать запрос с явным фильтром по orders.client_id или оставить оптимизатору возможность вывести предикат самостоятельно. Принудительный индекс был рискованным: при изменении распределения данных он мог стать хуже полного сканирования. Переписывание запроса могло помочь, но создавало дублирование логики и не решало проблему для других запросов.
Был выбран обычный запрос с проверкой фактического плана и актуализацией статистики. Оптимизатор вывел транзитивное условие, выполнил индексный поиск заказов по идентификатору клиента и избежал чтения всей таблицы. Решение оказалось устойчивее подсказки, потому что стоимость плана продолжала пересчитываться с учётом распределения данных.
Нет. Для внутреннего соединения равенство обычно позволяет безопасно вывести такое условие, но внешнее соединение сохраняет строки без пары. Перенос фильтра через его границу может превратить внешнее соединение в фактически внутреннее или удалить строки, которые должны присутствовать в результате. Оптимизатор применяет такое преобразование только при доказанной эквивалентности или при наличии условий, делающих его безопасным.
Наличие выведенного условия ещё не гарантирует выгодность индексного доступа. Если значение имеет низкую селективность и соответствует большой доле таблицы, множество индексных обращений и последующих чтений строк может стоить дороже последовательного сканирования. Решение принимается по оценочной стоимости, а не по самому факту существования индекса.
Обычное SQL-равенство использует трёхзначную логику: сравнение с NULL не даёт TRUE, оно даёт UNKNOWN. Поэтому вывод, корректный для обычных ненулевых значений, нельзя безоговорочно трактовать как универсальное равенство строк. Оптимизатор учитывает свойства столбцов, ограничения NOT NULL и тип соединения, чтобы не изменить результат запроса при проталкивании предиката.