Краткий ответ

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

Исторический контекст

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

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

Постановка проблемы

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

На малом объёме результата это обычно быстро. Однако при росте числа строк увеличивается количество обращений к таблице, ухудшается локальность доступа и возрастает суммарная стоимость. В итоге план формально использует индекс, но практически проигрывает сканированию таблицы.

Подробное решение

Механизм состоит из двух этапов: индекс находит значения ключа и идентификаторы строк, затем по каждому идентификатору извлекаются недостающие столбцы из основной таблицы. Если таблица кластеризована по другому ключу либо строки физически распределены по множеству страниц, вторые обращения могут быть преимущественно случайными.

Оптимизатор оценивает ожидаемое число таких обращений. При небольшой выборке он часто выбирает индексный план, а при большой может предпочесть сканирование или другой индекс. Граница зависит от селективности условия, статистики, стоимости чтения страниц, кэширования, физической организации таблицы и конкретной СУБД.

Один из способов устранить дополнительные чтения — сделать индекс покрывающим, добавив в него нужные для запроса столбцы. Это ускоряет чтение, но увеличивает размер индекса, стоимость вставок и обновлений, а также расход памяти и дискового пространства. Поэтому покрытие следует проектировать для действительно важных запросов, а не включать все столбцы подряд.

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

SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 42;

Если индекс эффективно ищет по customer_id, но не содержит order_date и total_amount, плану может понадобиться дополнительное чтение таблицы для каждой найденной строки. При небольшом числе заказов это приемлемо, а при большом — покрывающий индекс или сканирование могут оказаться выгоднее.

Ситуация из практики

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

Рассматривались три варианта. Полное сканирование не требовало изменений, но замедляло запросы для обычных клиентов. Принудительный выбор сканирования был нестабилен при разных параметрах. Покрывающий индекс ускорял отчёт, но увеличивал размер индекса и цену записи.

Выбрали покрытие только часто используемых столбцов отчёта и проверили влияние на операции записи. Это снизило число обращений к таблице и ускорило отчёт, а стоимость поддержки индекса осталась приемлемой. Для редких запросов отдельный широкий индекс создавать не стали.

Что кандидаты часто упускают

1. Всегда ли индексный поиск с дополнительным чтением хуже сканирования?

Нет. Если условие возвращает очень мало строк, несколько обращений к таблице обычно дешевле чтения всех страниц. Проблема возникает не из-за самого факта дополнительного чтения, а из-за его масштабирования с числом найденных строк и плохой локальности доступа.

2. Почему одинаковое число найденных строк может давать разное время выполнения?

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

3. Может ли покрывающий индекс ухудшить систему?

Да. Широкий индекс занимает больше места, дольше строится и чаще требует обновления при вставках, изменениях и удалениях. Если добавленные столбцы редко нужны или запрос недостаточно важен, выигрыш чтения не компенсирует постоянные расходы на обслуживание индекса.