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