Почему создание индексов в базе данных замедляет операции INSERT, UPDATE и DELETE?

Индексы ускоряют операции чтения (SELECT), но создают дополнительные затраты на запись, так как СУБД должна поддерживать их структуру в актуальном состоянии.

Причины снижения производительности при записи:

1. Дополнительные операции ввода-вывода (I/O): При каждой модификации данных в таблице СУБД должна также обновить все связанные индексы. Каждый индекс — это отдельная структура данных (чаще всего B-дерево), запись в которую требует дисковых операций.
2. Увеличение нагрузки на CPU: Необходимо вычислить новые значения ключей для индексов и найти правильное место для их вставки/удаления в структуре индекса.
3. Фрагментация и балансировка: Частые обновления могут приводить к фрагментации страниц индекса и необходимости ребалансировки B-дерева, что является ресурсоемкой операцией.
4. Блокировки: Обновление индекса может требовать захвата дополнительных блокировок, увеличивая конкуренцию между параллельными транзакциями.

Пример накладных расходов:

-- Предположим, в таблице `users` есть индекс по полю `email`.
INSERT INTO users (name, email, created_at) VALUES ('Alice', 'alice@example.com', NOW());

Что происходит внутри СУБД:

1. Запись новой строки в таблицу users (heap).
2. Дополнительно: Вставка новой записи (ключ: 'alice@example.com', значение: указатель на строку) в B-дерево индекса idx_users_email.

Вывод: Количество индексов на таблицу, подверженную частым изменениям (OLTP), должно быть тщательно сбалансировано. Каждый новый индекс улучшает чтение, но ухудшает запись.