Как индексы влияют на операции вставки и обновления данных?

Индексы в базах данных — это дополнительные структуры данных, которые ускоряют поиск и фильтрацию записей по определённым столбцам. Однако за это ускорение приходится платить при выполнении операций, изменяющих данные: INSERT, UPDATE и DELETE. Каждый индекс требует поддержания своей собственной структуры, что добавляет накладные расходы на запись.

Как происходит вставка с индексами

При добавлении новой строки в таблицу (INSERT) система выполняет следующие шаги:

• Записывает данные строки в основную таблицу (например, в heap-файл).
• Для каждого существующего индекса на таблицу вычисляет значение ключа индекса для новой строки.
• Вставляет это значение ключа в структуру индекса (например, в B-дерево). Это может потребовать разделения страниц и ребалансировки дерева для поддержания эффективности поиска.

Таким образом, один INSERT превращается в одну запись в таблицу плюс N записей в индексы, где N — количество индексов. Это увеличивает время операции и нагрузку на ввод-вывод.

Как происходит обновление с индексами

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

• Если обновляется непроиндексированный столбец, изменения затрагивают только основную таблицу (и индексы, включающие этот столбец как включённый, но не как ключевой).
• Если обновляется столбец, который является частью ключа индекса, система должна:
  1. Найти и удалить старую запись из структуры индекса.
  2. Вычислить и вставить новое значение ключа в индекс.
  Это по сути равносильно выполнению DELETE и INSERT внутри индекса.

Практический пример

Рассмотрим таблицу пользователей и индекс по email.

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    age INT
);

CREATE INDEX idx_email ON users(email);

При выполнении вставки:

INSERT INTO users (id, name, email, age) VALUES (1, 'Alice', 'alice@example.com', 30);

Система запишет строку в таблицу и добавит запись со значением 'alice@example.com' и ссылкой на строку в индекс idx_email. Если мы позже обновим email:

UPDATE users SET email = 'alice.new@example.com' WHERE id = 1;

Системе придётся удалить 'alice@example.com' из индекса и вставить 'alice.new@example.com', что медленнее, чем просто изменить значение в таблице.

Вывод: Индексы следует создавать обдуманно, особенно на таблицах с высокой частотой операций записи (OLTP-системы). Необходимо находить баланс между скоростью чтения (запросов WHERE, JOIN) и скоростью записи, избегая создания индексов по столбцам, которые редко используются в условиях поиска.