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

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

Центральные пункты

• ОпределениеФрагментированные деревья B* генерируют больше операций ввода-вывода и медленнее сканируют.
• Причины: Разделение страниц, удаление, смещение ключевых значений.
• ПорогиПерестройка с ~5-30 %, перестройка с ~30 %.
• Ориентация на MySQLОПТИМИЗИРУЙТЕ ТАБЛИЦУ и коэффициенты заполнения.
• АвтоматизацияЗапланированные задания, онлайн-операции, метрики.

Что означает фрагментация индекса с технической точки зрения?

Я называю Фрагментация несоответствие между логической последовательностью ключей и физической цепочкой страниц древовидного индекса B*. Многочисленные INSERT, UPDATE и DELETE приводят к появлению пробелов, разрывов и неупорядоченных листовых страниц, что вызывает большее количество операций чтения. В результате сканирование происходит чаще, количество обращений к буферному кэшу уменьшается, а затраты процессора растут. Даже идеальные планы страдают, поскольку память медленнее доставляет разрозненные страницы. Поэтому я всегда обращаю внимание на контекст объем работы, Размер данных и расположение памяти.

Виды фрагментации и их симптомы

Я делаю прагматическое различие:

• Логическая фрагментацияЛистовые страницы больше не конкатенируются в последовательности ключей. Сканирование диапазона требует дополнительных переходов, чтение с опережением менее эффективно.
• Внутренняя фрагментацияНа страницах много неиспользуемого пространства (низкий уровень заполнения). Приходится считывать больше страниц на строку результата; размер индекса увеличивается без пользы.
• Структурная фрагментацияНеблагоприятная высота дерева, несбалансированные узлы или кучи с переадресованными записями (например, в SQL Server). Доступ становится более непрямым.

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

Причины: Вставки, обновления, разделение страниц

Частые вставки заполняют страницы до самого края, а затем новый ключ заставляет Разделение страницы, что оставляет две наполовину заполненные страницы. Удаление удаляет записи, но свободное место остается распределенным и не всегда используется локально при следующей вставке. Обновления, изменяющие ключевые столбцы, перемещают записи и создают больше пробелов. Случайные шаблоны ключей, такие как GUID, еще больше увеличивают разброс и, следовательно, беспорядок. Я минимизирую разрывы, используя Коэффициент заполнения чтобы соответствовать нагрузке при записи.

Сделать потери производительности измеримыми

Я измеряю фрагментацию не изолированно, а в сочетании с временем выполнения запросов, чтениями журналов, чтениями страниц и классами ожидания. Если средняя задержка сканирования диапазона увеличивается, а CPU на запрос растет, я в первую очередь проверяю физические ключевые показатели индексов. Высокая фрагментация увеличивает количество прочитанных страниц на равное количество строк и сокращает время ожидания ввода-вывода. Обоснованное сравнение до и после реорганизации или перестройки показывает реальную выгоду. Для получения справочной информации о блокировках, планах и узких местах стоит взглянуть на Производительность базы данных, правильно классифицировать симптомы.

Метрики, ожидания и эффективность страниц в деталях

На практике я также наблюдаю:

• Страниц за сканированиеСколько страниц листа считывается при обычном сканировании области? Если значение увеличивается при том же количестве результатов, это указывает на фрагментацию или слишком низкий уровень заполнения.
• Удар с опережением чтенияФрагментированные цепочки саботируют последовательные предварительные загрузку; на SSD эффект меньше, но не нулевой, поскольку процессор, защелки и кэш продолжают страдать.
• Классы ожиданияPAGEIOLATCH/IO-Waits (SQL Server), последовательное/рассеянное чтение db-файла (Oracle) или увеличенные задержки чтения InnoDB (MySQL) увеличиваются при более сильных скачках в индексе.
• Качество кэшаЕсли частота попадания в буферный пул падает параллельно с фрагментацией, перестроение почти всегда имеет смысл - особенно при сканировании больших диапазонов.

Анализ фрагментации: SQL Server, MySQL, Oracle

Я всегда начинаю анализ с надежного Снимок здоровья индекса и отсеивать небольшие индексы, чье использование страниц колеблется статистически. В SQL Server sys.dm_db_index_physical_stats предоставляет степень фрагментации вместе с количеством страниц, чтобы я мог взвесить отклонения. Значения выше 5-30 % указывают на реорганизацию, сильные выбросы выше 30 % указывают на перестройку, особенно при большом количестве страниц. В MySQL я проверяю представления SHOW TABLE STATUS или INFORMATION_SCHEMA и наблюдаю за длиной данных и индексов с течением времени. В Oracle я также проверяю, доступна ли онлайн-перестройка, чтобы Время простоя которых следует избегать.

Практические запросы и взвешивание

Я работаю с простыми, многократно используемыми запросами и расставляю приоритеты в зависимости от размера страницы и релевантности:

• SQL ServerЯ определяю фрагментацию и отфильтровываю мелкие индексы.

  SELECT DB_NAME() AS db, OBJECT_NAME(i.object_id) AS obj, i.name AS idx,
         ips.index_type_desc, ips.page_count, ips.avg_fragmentation_in_percent
  FROM sys.indexes i
  CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), i.object_id, i.index_id, NULL, 'SAMPLED') ips
  WHERE ips.page_count >= 100
  ORDER BY ips.avg_fragmentation_in_percent DESC, ips.page_count DESC;

• MySQL (InnoDB)Я смотрю на размер индекса, свободное пространство и скорость изменения.

  SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, INDEX_LENGTH, DATA_FREE
  FROM information_schema.TABLES
  WHERE ENGINE = 'InnoDB'
    И INDEX_LENGTH > 0
  ORDER BY (DATA_FREE) DESC;

  В то же время я сравниваю значения с течением времени (например, ежедневно), чтобы отделить реальные тенденции от провалов. Для статистики я использую ANALYZE TABLE в редких случаях, если оптимизатор предполагает неправильную кардинальность.

• OracleЯ проверяю статистику сегментов (свободные места, экстенты) и доступность REBUILD ONLINE, чтобы поддерживать окна обслуживания в предсказуемом состоянии.

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

Реорганизация против перестройки: Матрица решений

Я выбираю метод в зависимости от степени Фрагментация и операционных окон, поскольку не каждая среда может справиться с интенсивными пиками ввода-вывода. Реорганизация переставляет страницы листа, уменьшает логические переходы, сжимает до коэффициента заполнения и обычно остается в режиме онлайн. Rebuild перестраивает индекс, полностью очищает его, возвращает память и обновляет статистику, но требует затрат процессора, ввода-вывода и часто более длительных блокировок. Небольшие индексы объемом менее 100 страниц редко получают значительные преимущества, в то время как большие структуры с фрагментацией 30 % и более значительно выигрывают. Я документирую решение с ключевыми цифрами, чтобы эффект оставался понятным и График технического обслуживания подходит.

Метод	Требования к ресурсам	Типичное использование	Основной эффект
Реорганизация	От низкого до среднего	~5-30 % Фрагментация	Реорганизация, сжатие до коэффициента заполнения
Восстановить	Высокий	> 30 % Фрагментация	Полная перестройка, освобождение памяти

Онлайн-варианты, замки и побочные эффекты

Для работы с низким уровнем перебоев я использую - там, где это возможно - Онлайн-реконструкция в. Я обращаю на это внимание:

• Издание/версияВозможности онлайн зависят от базы данных и редакции. Я проверяю каждую среду отдельно.
• Временные блокировки метаданныхДаже “онлайн” обычно требует блоков в начале/конце. Я намеренно планирую их в спокойных фазах.
• Температурные/рабочие диапазоныТакие опции, как SORT_IN_TEMPDB (SQL Server), снижают нагрузку на основной файл данных, но требуют дополнительного места для хранения.
• РепликацияПри перестройке увеличивается объем журнала. Я отслеживаю отставание реплики и при необходимости дросселирую, чтобы избежать задержек.

Для кучи SQL-сервера я принимаю во внимание Переданные записи; Здесь перестройка таблицы помогает удалить перенаправления. В Oracle я использую REBUILD ONLINE или MOVE PARTITION (с UPDATE INDEXES), чтобы сократить время простоя.

Коэффициент заполнения, разделение страниц и память

Подходящий Коэффициент заполнения Для таблиц с большим количеством записей я устанавливаю значение 70-90 %, чтобы будущие вставки могли использовать свободное место локально. Если я снижаю коэффициент заполнения слишком сильно, индекс растет быстрее и занимает больше памяти; если я устанавливаю его слишком высоким, увеличиваются разбиения и фрагментация. Поэтому я наблюдаю за взаимосвязью между использованием страниц, нагрузкой при записи и шаблоном вставки в течение нескольких циклов. При перестройке я намеренно определяю коэффициент заполнения для каждого индекса, а не для всей базы данных. Регулярный мониторинг позволяет избежать изначально хорошей компромисс несколько месяцев спустя.

Понимание коэффициентов заполнения для каждой платформы

• SQL ServerFILLFACTOR - это свойство индекса, которое вступает в силу при перестройке/создании. Я устанавливаю более низкое значение для очень волатильных вторичных индексов и более высокое значение для структур с большим объемом чтения. Я документирую выбранное значение для каждого индекса и перекалибрую его после изменения профиля нагрузки.
• MySQL (InnoDB)С innodb_fill_factor Я влияю на свободное пространство, которое InnoDB оставляет для (пере)сборки. Это не относится к повседневному DML, но с помощью OPTIMIZE/ALTER это помогает уменьшить расщепления в будущем. Я также планирую горячие точки (однотонные ключи) таким образом, чтобы уменьшить конкуренцию защелок и сплиты.
• Oracle и PostgreSQLПараметр STORAGE или. FILLFACTOR (Postgres) дают место для свободного воздуха в страницах. Для таблиц с большим объемом записи я использую консервативные уровни заполнения и уравновешиваю дополнительную память ощутимо лучшим временем сканирования.

Специально для MySQL и WordPress

В MySQL мне помогает ОПТИМИЗИРОВАТЬ TABLE в InnoDB для реорганизации таблиц и связанных с ними индексов и возврата свободного места. Сильно фрагментированные рабочие нагрузки с большим количеством удалений также выигрывают от периодического создания критических вторичных индексов. В установках WordPress перед оптимизацией я удаляю беспорядок, например, ревизии и спам-комментарии, чтобы меньше страниц нуждалось в переупорядочивании. Я сочетаю эти шаги со стратегией создания чистых индексов для wp_postmeta и подобных таблиц, которые часто подвергаются сканированию. Практическое введение можно найти в руководстве Оптимизация индексов WordPress, в котором рассматриваются типичные камни преткновения.

Практика MySQL: OPTIMIZE, разделы и побочные эффекты

Я также обращаю внимание на InnoDB:

• ОПТИМИЗИРОВАТЬ СТОЛ реконструирует таблицу (и индексы) и может выполняться в значительной степени “на месте” в зависимости от версии, но всегда требует мета-блокировок и свободного места в журнале. Я планирую выделить для этого специальные временные окна.
• Разделы позволяет осуществлять целенаправленное обслуживание: OPTIMIZE PARTITION только для горячих или сильно стертых областей снижает пики ввода-вывода и время работы.
• РепликацияБольшие перестройки генерируют объем бинлога и могут привести к задержке реплик. Я распределяю обслуживание на несколько ночей или работаю в разделах.
• АНАЛИЗИРОВАТЬ ТАБЛИЦУ обновляет статистику, которая нужна оптимизатору для составления более точных планов - особенно после масштабных структурных изменений.

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

Краткие сведения о специфике PostgreSQL

Несмотря на то, что основное внимание здесь уделяется MySQL, я принимаю во внимание гетерогенные среды:

• Автовакуум/Автовакуум предотвращает раздувание, но не заменяет REINDEX, если структуры B-деревьев сильно фрагментированы.
• ОДНОВРЕМЕННОЕ ПЕРЕИНДЕКСИРОВАНИЕ позволяет создавать новые индексы в основном в режиме онлайн с ограниченным блокированием.
• коэффициент заполнения на таблицу/индекс контролирует свободный воздух для будущих INSERT'ов/UPDATE'ов. Для таблиц с большим объемом записи лучше использовать более низкие значения.
• Перегородки за период разгружает окна обслуживания; REINDEX можно использовать специально для каждого раздела.

Автоматизированное обслуживание и пороговые значения

Я автоматизирую реорганизацию и перестройку с помощью robust Пороги и активировать только индексы с достаточным количеством страниц (page_count), чтобы избежать шума. Задания выполняются в окна обслуживания, а длительные операции я выполняю через онлайн-опции с минимальным временем простоя. При поэтапном подходе большие перестройки откладываются на спокойные периоды, а небольшие перестройки выполняются чаще. Я обновляю статистику после крупных изменений, чтобы оптимизатор оперативно выбирал лучшие планы. Оповещения запускаются, как только фрагментация или задержки превышают заданные пределы, чтобы я мог принять меры до того, как пользователи начнут жаловаться.

Runbook: Последовательность шагов для достижения устойчивых результатов

1. ОпределитеСнимок N лучших индексов по размеру и фрагментации, фильтр малых индексов.
2. ПриоритетСортировка по критичности нагрузки, количеству страниц и нагрузке сканирования.
3. ПланированиеПланируйте перестройку в соответствии с пороговыми значениями, вычисляйте онлайн-опции и требования к хранилищам/журналам.
4. ВыполнитеПоэтапное распределение больших объектов, дросселирование ввода-вывода, контроль за задержкой репликации.
5. СтатистикаОбновляйте статистику после перестройки/OPTIMIZE (или убедитесь, что это делается автоматически).
6. ПроверитьИзмерения до и после: Задержка, количество прочитанных страниц, время ожидания, частота попадания в кэш.
7. КалибровкаПроверьте коэффициенты заполнения и пороговые значения, задокументируйте полученные уроки.

Настройка хостинга: практические правила

В среде хостинга я планирую провести анализ Еженедельник, регулируют окно ввода-вывода для обслуживания и в сочетании с кэшированием сохраняют хотсеты в памяти. Параметры TempDB/redo/binlog и носители информации существенно влияют на ощутимый эффект от дефрагментации. Я также оцениваю, приносят ли лишние индексы только расходы, поскольку каждый дополнительный индекс увеличивает работу по записи и вероятность фрагментации. Перед созданием каждого нового индекса я проверяю модели рабочей нагрузки, кардинальность и существующий охват. В этом обзоре я описываю типичные камни преткновения Индексные ловушки в MySQL, что позволяет избежать неверных оценок.

Затраты/выгоды и когда я сознательно ничего не делаю

Не каждый фрагмент стоит поддерживать. Я сознательно обхожусь без них, когда:

• Объект маленький (например, менее 100 страниц) и сильно колеблется - вот где преимущества не работают.
• Запросы являются выборочными (в основном поиск по ключу), и сканирование диапазона не выполняется.
• Рабочая нагрузка является преходящей (окно миграции, скоро архивирование) - тогда я планирую только окончательную перестройку.

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

Когда реорганизовывать, когда ждать?

Я выпускаю Реорганизация если степень фрагментации увеличивается умеренно и затрагивается достаточное количество страниц для получения реального эффекта. После массового удаления или архивирования упорядоченное перераспределение часто приносит заметный прирост сканирования. В случае сильных выбросов или необходимости хранения я планирую перестройку, предпочтительно в режиме онлайн, чтобы свести к минимуму перебои в работе. Я часто не трогаю небольшие индексы объемом менее 100 страниц, поскольку их расположение сильно колеблется, а выгода минимальна. Я документирую решение вместе с цифрами "до/после", чтобы было легче планировать будущие циклы.

Долгосрочная профилактика с помощью дизайна

Хороший сайт Разработка схемы уменьшает фрагментацию еще до первой вставки, обеспечивая согласованность выбора ключей, типов данных и нормализации. Я избегаю сверхшироких строк, которые позволяют сократить количество записей данных на странице и способствуют дроблению. Разбиение на разделы отделяет холодные данные от горячих и уменьшает побочные эффекты при обслуживании и резервном копировании. Тщательная оптимизация запросов снижает зависимость от дорогостоящего сканирования и выравнивает индексы в соответствии с реальными шаблонами. По мере изменения рабочей нагрузки я постепенно корректирую определения индексов, а не отбрасываю целые структуры наобум.

Выбор ключа и схема вставки

Выбор первичного ключа оказывает решающее влияние на поведение разбиения:

• Монотонные клавиши (например, AUTO_INCREMENT, идентификаторы, основанные на времени) вставляет пучки по правому краю, уменьшает рассеивание и расщепление, но может создавать горячие точки. Я выравниваю горячие точки с помощью буферизации/бэтчинга.
• Случайные ключи (например, GUID/UUID v4) распределяют нагрузку, но увеличивают вероятность разделения. Последовательные варианты (например, UUID, основанные на времени) лучше распределяют нагрузку и порядок.
• Широкий ключ увеличивают индекс и количество необходимых страниц. Бережливые, избирательные ключи более устойчивы.

Кроме того, сжатие строк и страниц снижает скорость разбиения, поскольку на каждой странице остается место для большего количества записей. Однако я всегда проверяю стоимость процессора и наличие лицензий/функций, прежде чем активировать сжатие.

Краткое содержание: Шаги с эффектом

Я начинаю с целенаправленного Анализ самых больших и фрагментированных индексов, расставляю приоритеты в соответствии с количеством страниц и критичностью рабочей нагрузки. Затем я принимаю поэтапные меры: реорганизую умеренные случаи, перестраиваю тяжелые случаи, корректирую коэффициенты заполнения для каждого индекса. Автоматизированные задания поддерживают порядок без постоянного ручного вмешательства, а оповещения надежно срабатывают в случае отклонения от нормы. Среды MySQL и WordPress заметно выигрывают, если я заранее сокращаю потери данных и сохраняю только полезные индексы. Благодаря последовательному мониторингу, четким пороговым значениям и повторяющимся игровым процессам Производительность стабильность - даже при быстром росте объема данных.