Что такое Database Index Fragmentation и как она влияет на скорость выполнения SELECT-запросов в больших таблицах?
Database Index Fragmentation (фрагментация индекса базы данных) — это состояние, при котором логический порядок страниц индекса не соответствует их физическому расположению на диске, либо страницы индекса заполнены не полностью. Это явление возникает в результате операций вставки, обновления и удаления данных в таблицах.
## Типы фрагментации индексов
**1. Внутренняя фрагментация (Internal Fragmentation)**
Возникает, когда страницы индекса заполнены не полностью. Например, если страница рассчитана на 100 записей, но содержит лишь 40, это означает, что 60% пространства используется неэффективно. Причина — частые операции DELETE, после которых освободившееся место не заполняется сразу.
**2. Внешняя фрагментация (External Fragmentation)**
Возникает, когда логический порядок страниц индекса нарушен. Страницы, которые логически должны следовать одна за другой, физически расположены в разных частях диска. Это особенно критично для операций диапазонного сканирования (range scan).
## Как фрагментация влияет на SELECT-запросы
**Снижение производительности при сканировании диапазонов.** Когда выполняется запрос вида `SELECT * FROM orders WHERE date BETWEEN ‘2024-01-01’ AND ‘2024-12-31’`, SQL-движок должен читать последовательные страницы индекса. При высокой внешней фрагментации чтение становится случайным (random I/O) вместо последовательного (sequential I/O), что в 10–100 раз медленнее на HDD и заметно медленнее даже на SSD.
**Увеличение количества операций ввода-вывода.** Из-за внутренней фрагментации для получения того же объёма данных требуется прочитать больше страниц — данные «размазаны» по большему числу страниц, чем необходимо.
**Повышенная нагрузка на буферный пул.** Большее количество страниц вытесняет другие данные из кэша, что увеличивает число физических чтений.
**Неэффективное использование статистики.** Фрагментированные индексы могут приводить к устаревшей статистике, из-за чего оптимизатор запросов выбирает неоптимальные планы выполнения.
## Измерение фрагментации
В Microsoft SQL Server:
sql
SELECT index_id, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(‘tablename’), NULL, NULL, ‘DETAILED’);
В PostgreSQL используется расширение `pgstattuple`:
sql
SELECT * FROM pgstattuple(‘table_name’);
## Методы устранения фрагментации
— **REORGANIZE** (реорганизация) — дефрагментация без блокировки таблицы, подходит при фрагментации 10–30%.
— **REBUILD** (перестройка) — полное пересоздание индекса, эффективно при фрагментации >30%, но требует больше ресурсов.
— **FILLFACTOR** — настройка заполнения страниц при создании индекса (например, 80%), оставляет место для будущих вставок.
— **Регулярное обслуживание** — планирование ночных заданий по обслуживанию индексов.
В больших таблицах (от нескольких миллионов строк) даже 20–30% фрагментации может увеличить время выполнения SELECT-запросов в 2–5 раз, поэтому регулярный мониторинг и обслуживание индексов являются обязательной практикой для production-систем.