Какие накладные расходы возникают при использовании индексов при вставке данных?

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

Основные виды накладных расходов при вставке

• Дополнительные операции ввода-вывода (I/O): Для каждого индекса движок базы данных должен записать новую запись (ключ и указатель на строку) в соответствующую структуру данных на диске. Это увеличивает общее количество записываемых данных.
• Вычислительные затраты на поддержание структуры: Большинство индексов, например B-дерево, требуют поддержания порядка и сбалансированности. При вставке нового ключа система должна найти правильный листовой узел, вставить запись, а затем, если узел переполнен, выполнить его разделение (split) и потенциально ребалансировку дерева вверх до корня.
• Блокировки и конкуренция: Обновление индексов часто требует кратковременных блокировок (latch) на страницах индекса. В высоконагруженных системах с множеством одновременных вставок это может стать узким местом, приводя к конкуренции и ожиданиям.
• Увеличение размера хранилища: Каждый индекс занимает место на диске, пропорциональное объёму данных в индексируемых столбцах и количеству строк. Это увеличивает затраты на хранение и может замедлить операции резервного копирования.
• Влияние на журнал транзакций (Write-Ahead Log): Все изменения индексов, как и данных, должны быть записаны в журнал транзакций для обеспечения долговечности (durability) и возможности восстановления. Это увеличивает объём записываемой в лог информации.

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

Рассмотрим таблицу users с индексами на id (первичный ключ), email (уникальный) и country_id (неуникальный).

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100),
    country_id INT
);

CREATE INDEX idx_users_country ON users(country_id);

При вставке одной строки INSERT INTO users (email, name, country_id) VALUES ('test@example.com', 'Alice', 1) СУБД выполнит примерно следующие шаги:

1. Вставить строку данных в таблицу (heap).
2. Обновить индекс B-дерево для первичного ключа id (добавить новый ключ).
3. Обновить индекс B-дерево для уникального ключа email (проверить уникальность и добавить ключ).
4. Обновить индекс B-дерево для country_id (добавить ключ).
5. Записать все эти изменения в журнал транзакций.

Таким образом, одна логическая вставка превращается в несколько физических операций записи.

Вывод

Индексы следует создавать обдуманно, особенно на таблицах с интенсивной записью (OLTP). Каждый дополнительный индекс увеличивает стоимость операций вставки и обновления. Рекомендуется индексировать только те столбцы, которые часто используются в условиях WHERE, JOIN или ORDER BY, и периодически пересматривать набор индексов, удаляя неиспользуемые.