Краткий ответ

Изменение значения ключевого столбца обычно нельзя выполнить «на месте»: СУБД удаляет старую индексную запись и добавляет новую в позицию, соответствующую новому значению. Поэтому такой UPDATE оплачивается как операция над индексом и может вызывать дополнительные изменения страниц, блокировки и журналирование.

Исторический контекст

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

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

Постановка проблемы

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

Неверная оценка последствий приводит к неожиданным задержкам записи, росту журнала транзакций, блокировкам и увеличению объёма операций ввода-вывода. Особенно дорого это становится, если столбец входит в несколько индексов или является ключом кластеризации.

Подробное решение

Для изменения индексного ключа СУБД обычно выполняет логическую последовательность «удалить старую запись — вставить новую». Новая запись может попасть на другую страницу B-дерева; если свободного места недостаточно, возможны расщепление страницы и дополнительные изменения соседних структур.

Если столбец является только включённым, неключевым атрибутом покрывающего индекса, порядок записей не меняется. Однако значение всё равно нужно обновить внутри индекса, поэтому полностью бесплатным такой UPDATE не становится.

Для кластеризованного индекса последствия могут быть серьёзнее: сама строка физически или логически организована вокруг кластерного ключа. Перемещение ключа способно изменить расположение строки, а вторичные индексы могут потребовать обновления своих ссылок — конкретный механизм зависит от СУБД.

При обновлении нескольких индексируемых столбцов затраты суммируются не буквально один к одному: СУБД может оптимизировать внутренние операции, но каждый затронутый индекс должен сохранить согласованность. Операция также фиксируется в журнале, чтобы её можно было восстановить или откатить.

CREATE TABLE customers (
    id       bigint PRIMARY KEY,
    segment  varchar(20) NOT NULL,
    name     varchar(100) NOT NULL
);

CREATE INDEX ix_customers_segment ON customers (segment);

UPDATE customers
SET segment = 'premium'
WHERE id = 42;

В примере изменяется ключ вторичного индекса ix_customers_segment: старая запись для прежнего сегмента удаляется, а новая добавляется для premium. Индекс ускоряет последующий поиск по сегменту, но увеличивает стоимость этого UPDATE.

Практическая оптимизация состоит не в безусловном удалении индекса, а в сопоставлении частоты чтения и записи. Иногда помогает стабильный суррогатный ключ вместо часто меняющегося бизнес-атрибута, удаление действительно ненужного индекса или разделение рабочих нагрузок. Нельзя рассчитывать, что изменение значения всегда будет дешёвым только потому, что обновляется одна строка.

Ситуация из практики

В системе сегментации клиентов массовое изменение сегментов стало выполнятьcя заметно дольше после добавления нескольких индексов для отчётов. Рассматривались три варианта: удалить индексы, выполнять обновление пакетами или оставить структуру без изменений.

Удаление индексов ускорило бы запись, но ухудшило бы регулярные отчёты. Обновление меньшими пакетами снизило бы длительность отдельных блокировок и пиковую нагрузку на журнал, однако не устранило бы суммарную работу индексов. Полное сохранение схемы без изменения процесса не решало проблему.

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

Что кандидаты часто упускают

Всегда ли изменение индексируемого значения приводит к физическому перемещению строки в таблице?

Нет. Для обычного вторичного индекса может перемещаться только индексная запись, тогда как строка таблицы остаётся на месте. Для кластеризованного индекса изменение ключа может затронуть расположение самой строки или структуру ссылок вторичных индексов; точное поведение зависит от реализации СУБД.

Почему индекс с включённым столбцом тоже увеличивает стоимость UPDATE этого столбца?

Включённое поле не участвует в порядке поиска, но хранится в листовых страницах индекса. При изменении его значения СУБД должна обновить сохранённую копию, иначе индекс перестанет возвращать актуальные данные для покрывающего запроса. Поэтому отсутствие столбца среди ключевых не означает отсутствия write overhead.

Почему замена часто меняющегося бизнес-ключа на стабильный идентификатор может улучшить производительность?

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