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