Фрагментация индексов

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

Причины возникновения

Фрагментация индексов является естественным следствием динамического характера работы систем управления базами данных (СУБД). Основные причины включают:

Операции модификации данных

• Вставка записей: При добавлении новых строк в таблицу СУБД вынуждена размещать соответствующие записи в индексной структуре. Если страница индекса, куда должна попасть запись, уже заполнена, происходит операция разделения страницы (page split). Часть данных перемещается на новую страницу, что нарушает физическую последовательность.
• Обновление записей: Изменение значений ключевых полей индекса может привести к перемещению записи в другую часть индексной структуры, что также вызывает фрагментацию.
• Удаление записей: Удаление строк не всегда приводит к немедленному сжатию страниц. Освободившееся пространство остаётся внутри страницы, что приводит к внутренней фрагментации, когда страница содержит значительное количество пустого места.

Параметры настройки СУБД

• Коэффициент заполнения (Fill Factor): Параметр, определяющий, насколько процентов изначально заполняется каждая страница индекса при его создании или перестроении. Низкий коэффициент (например, 50%) оставляет больше места для будущих вставок, снижая частоту разделений страниц, но увеличивая общий размер индекса и потенциально повышая фрагментацию из-за большего количества частично пустых страниц.
• Автоматическое управление пространством: Некоторые СУБД (например, Oracle) используют автоматическое управление сегментами, которое может приводить к фрагментации при неправильной настройке или в условиях высокой конкурентной нагрузки.

Виды фрагментации

В контексте индексов принято выделять два основных типа фрагментации:

Внешняя фрагментация

Характеризуется тем, что логически последовательные страницы индекса физически разбросаны по диску. Это происходит, когда в результате разделения страниц новые страницы выделяются из свободного пространства, которое может находиться далеко от исходного расположения. Внешняя фрагментация наиболее критична для последовательных сканирований индекса (index scan), когда СУБД вынуждена выполнять большое количество перемещений головок диска.

Внутренняя фрагментация

Возникает, когда страницы индекса содержат значительное количество неиспользуемого пространства. Это может быть следствием:

• Удаления большого количества записей без последующего сжатия.
• Низкого коэффициента заполнения.
• Неравномерного распределения данных (например, при использовании GUID в качестве кластеризованного ключа).

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

Влияние на производительность

Фрагментация индексов негативно сказывается на производительности базы данных по нескольким направлениям:

• Увеличение времени чтения: Для выполнения запроса СУБД требуется прочитать большее количество страниц, чем необходимо, из-за внутренней фрагментации. Внешняя фрагментация увеличивает время позиционирования дисковых головок.
• Рост нагрузки на ввод-вывод (I/O): Большее количество операций чтения/записи приводит к повышенной нагрузке на диск и подсистему ввода-вывода.
• Увеличение потребления памяти: Фрагментированные индексы занимают больше места в буферном кэше, что снижает эффективность кэширования.
• Замедление операций обслуживания: Резервное копирование, восстановление и проверка целостности данных выполняются медленнее на фрагментированных структурах.

Методы устранения

Для борьбы с фрагментацией индексов в СУБД предусмотрены две основные операции:

Перестроение индекса (Rebuild)

Операция, которая полностью удаляет старый индекс и создаёт новый на его основе. В процессе перестроения данные перераспределяются в соответствии с заданным коэффициентом заполнения, что устраняет как внешнюю, так и внутреннюю фрагментацию. Перестроение может выполняться как в режиме онлайн (с доступом к таблице), так и в режиме офлайн (с блокировкой таблицы). В Microsoft SQL Server для этого используется команда ALTER INDEX ... REBUILD, в PostgreSQL — REINDEX, в Oracle — ALTER INDEX ... REBUILD.

Реорганизация индекса (Reorganize)

Более лёгкая операция, которая сжимает страницы индекса на физическом уровне, не удаляя и не создавая структуру заново. Реорганизация устраняет внутреннюю фрагментацию и частично — внешнюю. Она выполняется с меньшей нагрузкой на систему и может проводиться в фоновом режиме. В Microsoft SQL Server для этого используется команда ALTER INDEX ... REORGANIZE, в PostgreSQL — VACUUM (для индексов) или CLUSTER (для переупорядочивания таблицы по индексу).

Критерии оценки фрагментации

Для определения необходимости обслуживания индекса используются метрики, вычисляемые СУБД:

• Средняя фрагментация (Avg Fragmentation): Процент логически непоследовательных страниц. Значение до 10% считается низким, от 10% до 30% — умеренным (рекомендуется реорганизация), выше 30% — высоким (рекомендуется перестроение).
• Средняя плотность страницы (Avg Page Density): Показывает, насколько плотно заполнены страницы данными. Низкая плотность указывает на внутреннюю фрагментацию.

Практические рекомендации

• Регулярное обслуживание: Для систем с высокой интенсивностью операций модификации данных рекомендуется проводить обслуживание индексов в периоды низкой нагрузки (например, ночью или в выходные дни).
• Мониторинг: Необходимо настроить автоматический сбор статистики по фрагментации индексов с помощью скриптов или встроенных средств СУБД (например, SQL Server Agent, Oracle Scheduler).
• Выбор стратегии: Для небольших индексов (менее 1000 страниц) перестроение обычно предпочтительнее реорганизации, так как оно полностью устраняет фрагментацию. Для больших индексов реорганизация может быть менее ресурсоёмкой альтернативой.
• Учёт нагрузки: Операции перестроения индекса могут потреблять значительные ресурсы процессора и диска, поэтому их следует выполнять с осторожностью, особенно в системах с ограниченными ресурсами.

Источники

1. Microsoft Learn. "Reorganize and Rebuild Indexes".
2. PostgreSQL Documentation. "Routine Database Maintenance Tasks".
3. Oracle Database Administrator's Guide. "Managing Indexes".
4. Книга: "SQL Server 2019 Administration Inside Out" by William Assaf, Randolph West, et al.
5. Книга: "Database Internals: A Deep Dive into How Distributed Data Systems Work" by Alex Petrov.