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