На проверке плана SQL Server запрос с двумя подходящими индексами внезапно начал сканировать таблицу после добавления второго условия. Какой механизм объясняет такой выбор оптимизатора?
CREATE TABLE dbo.Orders (
OrderID int NOT NULL PRIMARY KEY,
ClientID int NOT NULL,
Status varchar(20) NOT NULL,
Amount decimal(12,2) NOT NULL
);
CREATE INDEX IX_Orders_ClientID ON dbo.Orders (ClientID);
CREATE INDEX IX_Orders_Status ON dbo.Orders (Status);
SELECT OrderID, Amount
FROM dbo.Orders
WHERE ClientID = 42 OR Status = 'overdue';
Добавление OR не гарантирует использование обоих индексов. Оптимизатор может оценить альтернативу в виде двух индексных поисков с объединением результатов как более дорогую, чем одно сканирование таблицы, и выбрать сканирование.
Причина обычно в совокупной стоимости: доле подходящих строк, количестве обращений к таблице за неключевыми столбцами, необходимости удалить дубликаты и стоимости случайного чтения. Индексы существуют, но план выбирается по оценённой стоимости всего запроса, а не по самому факту их наличия.
Индексы появились как способ не читать всю таблицу для поиска небольшого набора строк. Однако составление результата по нескольким независимым условиям требует отдельной стратегии: выполнить несколько поисков, объединить найденные идентификаторы и затем получить нужные столбцы.
Оптимизаторы поддерживают такие преобразования, когда они выгодны. Но индексный план имеет накладные расходы, поэтому выбор между несколькими поисками и сканированием является задачей стоимостной оптимизации, а не фиксированным правилом.
В запросе используется дизъюнкция: строка подходит, если выполнено хотя бы одно из условий. Каждый индекс может найти свою часть строк, но сами индексы не содержат полностью результат запроса: для OrderID и Amount может потребоваться обращение к базовой таблице.
Если каждое условие недостаточно селективно, таких обращений становится много. Дополнительный риск — одна строка может удовлетворять обоим условиям, поэтому простое объединение результатов должно корректно обрабатывать дубликаты.
Оптимизатор рассматривает несколько вариантов. Первый — сканировать таблицу и проверить оба предиката для каждой строки. Второй — выполнить поиск по ClientID, поиск по Status, объединить найденные ключи, устранить возможные повторы и выполнить операции получения остальных столбцов.
Второй вариант полезен, когда оба условия возвращают небольшие множества. Если же одно из условий выбирает значительную долю таблицы, два поиска и многочисленные обращения к данным могут оказаться дороже последовательного сканирования.
На решение влияют оценка кардинальности, статистика по индексируемым столбцам, физическая организация данных, стоимость случайного чтения и наличие нужных столбцов в индексе. Поэтому одинаковый текст запроса может получить разные планы для разных объёмов данных или после обновления статистики.
В SQL Server преобразование OR в несколько индексных ветвей иногда реализуется через объединение потоков, но это не обязательное поведение. Также оптимизатор может преобразовать условие к форме, эквивалентной поиску по нескольким значениям, однако и это зависит от конкретного предиката и оценённой стоимости.
UNION удаляет дубликаты, поэтому сохраняет семантику исходного OR. UNION ALL дешевле, но может вернуть одну строку дважды, если она удовлетворяет обоим условиям; применять его можно только при доказанном отсутствии пересечения или при явном удалении дубликатов.
Переписывание запроса не гарантирует улучшения: оно может зафиксировать неудачную стратегию и усложнить поддержку. Практический подход — сравнить фактические планы и количество прочитанных строк, проверить актуальность статистики и оценить покрывающие индексы, уменьшающие стоимость обращений к таблице.
В таблице заказов индекс по ClientID хорошо работал для поиска одного клиента. После добавления OR Status = 'overdue' план стал сканировать таблицу, потому что просроченные заказы составляли значительную долю данных, а результат требовал Amount, отсутствовавшего в обоих индексах.
Рассматривались три варианта. Сохранение исходного запроса не меняло план; принудительное указание индекса могло снизить стоимость на текущих данных, но стало хрупким при изменении распределения; переписывание через UNION позволяло отдельно оценить ветви, но требовало контроля дубликатов.
Выбрали проверку статистики и индекс с включёнными столбцами для наиболее селективной ветви, после чего сравнили фактические планы на типичных объёмах данных. Сканирование оставили допустимым вариантом для случаев, когда условие действительно возвращает большую часть таблицы: ускорение индексом в такой ситуации не является гарантированным.
UNION ALL эквивалентен OR в такой переписи?Нет. Если строка одновременно имеет ClientID = 42 и Status = 'overdue', исходный OR возвращает её один раз, а UNION ALL — дважды. Эквивалентность возможна только при доказанном отсутствии пересечения либо при использовании дополнительной логики удаления дублей.
Если индекс содержит все столбцы, нужные для фильтрации и выдачи, оптимизатору не нужно обращаться к базовой таблице для каждой найденной строки. Уменьшаются случайные чтения и стоимость lookup-операций, поэтому план с несколькими ветвями может стать дешевле сканирования. Это увеличивает размер индекса и стоимость изменений данных, поэтому покрытие следует оценивать по реальной нагрузке.
Оптимизатор использует статистику и оценённую долю подходящих строк. После изменения распределения данных или обновления статистики стоимость ветвей с индексами и стоимость сканирования могут пересечься, поэтому выбранный план изменится. Наличие прежнего индекса не фиксирует план: решение зависит от кардинальности, стоимости чтения и формы запроса.