Что быстрее: перестроить индекс после удаления данных или создать новый на пустой таблице?

Что быстрее: перестроить индекс после удаления данных или создать новый индекс на пустой таблице?

• Контекст: индексация в СУБД
• Создание индекса на пустой таблице выполняется за линейное время относительно объёма данных; если таблица действительно пуста, этот объём равен нулю.
• Скорость перестроения индекса после удаления определяется тем, каким образом выполнялось удаление:
• При удалении большого объёма данных из таблицы с обычным индексом перестроение снижает фрагментацию и восстанавливает структуру индекса.
• Если удалена значительная часть данных, реорганизация может оказаться медленнее полной пересборки индекса.
• Когда после удаления таблица действительно пуста, создание нового индекса всегда быстрее перестроения существующего.
• Если строки в таблице сохранились, новый индекс придётся строить по всем оставшимся данным, поэтому затраты могут быть сопоставимы с перестроением.
• На практике после существенного удаления данных, но при наличии оставшихся строк, обычно выбирают перестроение: это позволяет избежать затрат на полную пересборку.
• Если таблица полностью очищена, создание нового индекса будет минимально затратным и более быстрым.

Итог: на пустой таблице новый индекс создаётся быстрее, чем перестраивается после удаления данных. Если строки остались, результат зависит от их количества и архитектуры СУБД.

Подробный ответ

Основной ответ

В большинстве ситуаций новый индекс на пустой таблице создаётся быстрее, чем существующий индекс перестраивается после удаления данных. При построении «с нуля» СУБД не приходится учитывать прежнюю структуру: она может эффективно сформировать индекс с помощью сортировки и алгоритмов bulk load. Перестроение после удаления требует обработки уже существующей индексации, включая очистку удалённых записей и дефрагментацию, поэтому обычно оказывается менее эффективным.

Ключевые моменты

• Создание индекса на пустой таблице проходит оптимально: СУБД сортирует ключи и формирует B-дерево либо другую структуру данных без дополнительных затрат на удаление и изменение записей.
• Перестроение индекса после удаления данных сохраняет актуальность существующей структуры, однако требует дополнительных операций очистки и реорганизации, что снижает производительность.
• В отдельных СУБД, включая PostgreSQL и SQL Server, перестроение индекса при большом количестве удалённых записей может быть затратным. При этом свежий индекс на пустой таблице создаётся быстрее и потребляет меньше ресурсов.

Практический контекст

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