При разборе плана один и тот же набор строк читается повторно. Объясните, зачем оптимизатор может добавить оператор spool.
Spool материализует промежуточный результат во временной структуре, чтобы повторно использовать его без повторного выполнения дорогой части плана. Это может уменьшить число чтений и вычислений, но добавляет стоимость записи, хранения и последующего чтения временных данных.
Реляционные планы часто строятся из потоковых операторов: один оператор передаёт строки следующему по мере их получения. Такой подход экономит память, но при повторном обращении к одному и тому же результату может заново выполнять предшествующие операции.
Оператор spool появился как общий способ сохранить уже полученные строки между обращениями. Он позволяет оптимизатору выбирать планы с повторным использованием данных, не требуя от пользователя явно создавать временную таблицу.
Типичный риск возникает при вложенном цикле: внешняя часть плана многократно обращается к внутренней. Если внутренний результат вычисляется заново для каждой внешней строки, стоимость может стать значительно выше ожидаемой.
Оптимизатор сравнивает стоимость повторного выполнения с затратами на материализацию. Ошибка оценки количества строк, недостаток памяти или слишком большой промежуточный результат могут сделать выбранный spool невыгодным: появятся дополнительные операции ввода-вывода, ожидания временного хранилища и конкуренция за ресурсы.
При создании spool оператор читает строки из источника и сохраняет их во внутренней рабочей структуре. При последующих обращениях план читает сохранённые строки, а не запускает источник заново. Конкретная реализация зависит от СУБД: данные могут находиться в памяти, временных страницах или специальном рабочем файле.
Lazy spool обычно сохраняет строки по мере первого чтения. Это выгодно, если потребителю понадобится только часть результата. Eager spool сначала полностью читает и материализует источник, после чего отдаёт строки дальше; такой вариант полезен, когда результат гарантированно будет использован повторно или требуется зафиксировать снимок входных данных.
Spool также может применяться для защиты от эффекта Хэллоуина: при изменении строк нельзя допустить, чтобы уже изменённая строка повторно попала в тот же процесс обработки. В этом случае материализация разделяет чтение исходного набора и его изменение.
Наличие spool не означает автоматически плохой план. Его нужно оценивать по фактическим объёмам, числу обращений, времени записи и чтения временной структуры. Если промежуточный набор велик, а повторное использование мало, полезнее может оказаться другой порядок соединения, хеш-соединение, более подходящий индекс или устранение лишнего повторного обращения.
Отчёт соединяет список клиентов с большим набором заказов через вложенный цикл. План оценивает внутренний результат как небольшой, но фактически один и тот же набор заказов запрашивается много раз. Оптимизатор добавляет lazy spool: первые обращения наполняют рабочую структуру, последующие используют уже сохранённые строки.
Рассматривались три варианта. Дополнительный индекс мог уменьшить стоимость каждого поиска, но увеличить цену изменений данных. Принудительная смена соединения на хеш-соединение устраняла повторные поиски, однако требовала памяти для хеш-таблицы. Оставить spool было разумно, если фактический объём промежуточных данных оставался умеренным и повторное использование действительно сокращало чтения.
Если же мониторинг показывал большое временное хранилище и малое число повторных обращений, принудительный hint был бы плохим решением: он закрепил бы план на конкретном распределении данных. Предпочтительно было исправить оценку кардинальности или структуру индекса, затем проверить новый план на репрезентативных параметрах.
Вопрос: Всегда ли spool означает, что оптимизатор обнаружил повторное чтение?
Ответ: Нет. Помимо повторного использования результата, spool может обеспечивать корректность обновления через защиту от эффекта Хэллоуина или стабилизировать вход для оператора, которому нужен повторный проход. Поэтому по одному названию оператора нельзя делать вывод о причине его появления: нужно смотреть его место в плане и тип материализации.
Вопрос: Почему spool может исчезнуть после обновления статистики, хотя запрос не менялся?
Ответ: Статистика влияет на оценку количества строк и стоимости альтернативных планов. Если оптимизатор ранее ожидал много повторных обращений, материализация могла казаться выгодной. После уточнения оценки он может выбрать другой порядок соединения, другой тип соединения или повторное выполнение более дешёвого поиска без spool.
Вопрос: Чем spool принципиально отличается от индекса на временной таблице?
Ответ: Spool обычно является внутренним и краткоживущим объектом, которым управляет сам планировщик; пользователь не задаёт его схему и не может напрямую использовать его в другом запросе. Временная таблица с индексом создаётся явно, может участвовать в нескольких операциях и предоставляет больше контроля, но требует дополнительных операторов, места, синхронизации и обслуживания статистики. Она оправдана, когда промежуточный результат используется в нескольких независимых шагах или его структуру нужно оптимизировать вручную.