Чому індекси сповільнюють операції запису?

Тому що під час вставки, оновлення чи видалення рядка база має змінити не лише дані в таблиці, а й усі пов'язані з ними індекси - а індекс це окрема структура, яку теж потрібно підтримувати в актуальному вигляді.

Розкладемо по механіці на рівні B-tree індексу:

#1. INSERT

Коли з'являється новий рядок, СУБД має:

1. записати рядок у таблицю (основна дія)
2. вставити ключ у B-tree індекс, знайшовши потрібний листок і вставивши туди значення
3. за потреби - перебалансувати дерево (спліт вузла, перебудова посилань)

Тобто одна операція запису перетворюється на кілька додаткових операцій з індексною структурою.

#2. UPDATE

Якщо оновлюється колонка, що бере участь в індексі, база фактично робить:

• delete key з індексу
• insert key в індекс наново

Тому UPDATE за індексними колонками - одна з найдорожчих операцій.

#3. DELETE

При видаленні рядка потрібно не лише прибрати дані, а й видалити посилання з індексу - це теж прохід по дереву й модифікація листка.

#4. Чому це сповільнення помітне

B-tree зберігається у вигляді сторінок на диску/в пам'яті. Підтримка індексу вимагає:

• зайвих IO-операцій
• блокувань сторінок індексу
• операцій балансування вузлів

Що більше індексів у таблиці, то довший будь-який запис, бо кожна операція над даними = модифікація кожного індексу.

#Підсумок

Індекси пришвидшують читання, але сповільнюють запис, тому що кожен INSERT/UPDATE/DELETE вимагає оновлення B-tree, пошуку потрібного місця в структурі індексу й можливої балансування. Тому надлишкова кількість індексів - прямий удар по продуктивності запису.