Почему индекс на updated_at ухудшает производительность update?

Влияние индексов на операции UPDATE

Индексы в базах данных создаются для ускорения операций чтения (SELECT), но они имеют обратную сторону — замедление операций записи (INSERT, UPDATE, DELETE). Когда вы создаете индекс на столбце updated_at, база данных должна поддерживать его актуальность при каждом изменении значения этого столбца.

Как это работает

При выполнении UPDATE, если изменяется значение столбца, входящего в индекс, база данных выполняет следующие шаги:

• Находит старую запись в индексе и удаляет ее.
• Вставляет новую запись в индекс с обновленным значением.
• Перестраивает или балансирует структуру индекса (например, B-дерево), что может потребовать дополнительных операций ввода-вывода.

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

Пример

-- Создаем таблицу с индексом на updated_at
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    updated_at TIMESTAMP
);

CREATE INDEX idx_updated_at ON users(updated_at);

-- Этот UPDATE обновит не только таблицу, но и индекс
UPDATE users SET name = 'John', updated_at = NOW() WHERE id = 1;

В данном примере, даже если изменяется только name, но updated_at также обновляется, индекс idx_updated_at будет перестроен. Если бы updated_at не обновлялся, индекс остался бы нетронутым.

Когда это критично

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

Вывод

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