Сравните, при каких условиях оптимизатор выбирает полное сканирование таблицы вместо индексного поиска, даже когда подходящий индекс существует?
Оптимизатор выбирает полное сканирование, когда по его оценке оно дешевле индексного доступа. Это обычно происходит при низкой селективности условия: запросу нужно прочитать значительную часть строк, а переходы от индекса к данным создают больше затрат, чем последовательное чтение таблицы.
Наличие индекса не обязывает использовать его. План выбирается по оценочной стоимости, основанной на статистике, размере таблицы, стоимости операций ввода-вывода, доступной памяти и особенностях конкретной СУБД.
Индексы появились как способ не просматривать все записи при поиске небольшого набора строк. Однако индексный доступ сам по себе требует чтения структуры индекса, поиска нужных ключей и, во многих случаях, обращения к отдельным страницам таблицы.
Поэтому оптимизаторы используют стоимостную модель. Она позволяет сравнить последовательное чтение таблицы с индексным доступом и выбрать вариант, который предположительно потребует меньше ресурсов.
Предположим, условие запроса совпадает с большой долей строк таблицы. Индекс быстро находит ключи, но затем для каждой найденной записи может потребоваться чтение данных из множества страниц в разрозненном порядке.
Такой план может оказаться дороже последовательного сканирования: таблица читается целиком, но страницы обрабатываются последовательно, а накладные расходы на многочисленные обращения через индекс отсутствуют. Если статистика ошибочно показывает маленькое число строк, оптимизатор, наоборот, может выбрать индекс и получить медленный план.
Главный фактор — оценочная селективность, то есть доля строк, которую условие предположительно вернёт. Для небольшого результата индекс обычно выгоден: прочитывается небольшая часть индекса и данных. Для большого результата последовательное сканирование часто эффективнее.
На выбор влияют несколько факторов:
Если индекс содержит все столбцы, нужные запросу, чтение может остаться внутри индекса. Такой план иногда выгоден даже для относительно большого результата, поскольку не требует отдельных обращений к таблице. Но покрывающий индекс увеличивает размер структуры и стоимость операций вставки, обновления и удаления.
План не следует оценивать по одному признаку вроде наличия операции Index Seek. Нужно сравнивать фактическое количество прочитанных страниц, оценённое и реальное число строк, узкие места по времени и объёму ввода-вывода. Конкретные названия операторов и правила стоимости зависят от СУБД.
Принудительный выбор индекса обычно не является универсальным исправлением. Он может ускорить запрос для одного значения, но ухудшить его для другого, особенно если распределение данных неравномерно или статистика устарела.
В таблице заказов есть индекс по статусу, но статус имеет всего несколько значений. Запрос выбирает заказы одного массового статуса и читает много столбцов, которых нет в индексе. Оптимизатор выбирает полное сканирование, потому что индекс потребовал бы найти множество ключей и затем обратиться к большому числу страниц таблицы.
Рассматривались три варианта. Принудительно использовать существующий индекс можно быстро внедрить, но это связывает план с текущим распределением данных и может ухудшить редкие случаи. Создать покрывающий индекс уменьшает обращения к таблице, но увеличивает размер индекса и нагрузку на запись. Оставить полное сканирование разумно, если запрос действительно часто читает большую долю таблицы.
Выбранным решением стало не принуждение индекса, а проверка фактического плана и статистики. Для запросов с малым результатом индекс сохранили, а массовую выборку оставили на последовательном чтении; при изменении распределения данных запланировали пересмотр индекса и статистики.
Да. Если нужные столбцы отсутствуют в индексе, СУБД должна получить их из таблицы. При большом количестве строк это приводит к множеству обращений к страницам данных. Поэтому стоимость состоит не только из поиска ключей, но и из последующего извлечения строк.
Потому что значения могут иметь разную распространённость. Для редкого значения индексный доступ обычно выгоден, а для массового — полное сканирование. Если СУБД переиспользует ранее построенный план без учёта такой разницы, один из вариантов может выполняться неоптимально; способы решения зависят от конкретной СУБД.
Нет. Полное сканирование может быть оптимальным, если запрос читает значительную часть таблицы, таблица мала, данные хорошо помещаются в памяти или сканирование эффективно распараллеливается. Ошибку следует подозревать не по самому оператору, а по расхождению между оценками и фактическими данными, избыточному вводу-выводу и измеренному времени выполнения.