Какие риски и проблемы связаны с добавлением индекса в большую таблицу в production?

Добавление индекса в большую таблицу (сотни миллионов или миллиарды строк) — это рискованная операция, которая может вызвать серьезные проблемы в работающей системе:

1. Блокировка таблицы (Locking)

   • Проблема: По умолчанию CREATE INDEX во многих СУБД (например, в старых версиях PostgreSQL или MySQL) устанавливает эксклюзивную блокировку на таблицу. Это блокирует все операции записи (INSERT, UPDATE, DELETE) и иногда даже чтения на всё время создания индекса, что может привести к простою приложения на часы.
   • Решение: Использовать онлайн-создание индексов.
     • В PostgreSQL: CREATE INDEX CONCURRENTLY. Этот метод не блокирует запись, но выполняется дольше и требует больше ресурсов. Он может завершиться ошибкой, если в системе есть долгоживущие транзакции.
     • В MySQL (InnoDB): Начиная с версии 5.6, CREATE INDEX по умолчанию выполняется онлайн (ALGORITHM=INPLACE, LOCK=NONE), но всё равно создаёт высокую нагрузку.

2. Высокое потребление ресурсов

   • Проблема: Создание индекса — это ресурсоёмкая операция. Она активно использует CPU для сортировки данных и вызывает интенсивные операции ввода-вывода (I/O) для чтения таблицы и записи индекса. Это может замедлить работу всей базы данных и повлиять на другие запросы.
   • Решение: Выполнять операцию в периоды наименьшей нагрузки (например, ночью). Тщательно мониторить нагрузку на CPU, I/O и память во время процесса.

3. Увеличение размера базы данных

   • Проблема: Индекс — это отдельная структура данных, которая занимает место на диске. Для больших таблиц размер индекса может быть очень значительным (иногда сопоставимым с размером самой таблицы).
   • Решение: Заранее оценить размер будущего индекса и убедиться, что на диске достаточно свободного места.

4. Замедление операций записи в будущем

   • Проблема: После создания индекса каждая операция INSERT, UPDATE или DELETE становится медленнее, так как СУБД теперь нужно обновлять не только данные в таблице, но и связанные с ней индексы.
   • Решение: Добавлять только действительно необходимые индексы, которые значительно ускоряют важные SELECT-запросы. Регулярно анализировать и удалять неиспользуемые индексы.