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

Ответ

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

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