Как добавить индекс на колонку большой таблицы, не залочив таблицу

1️⃣ Как кратко ответить

Для добавления индекса на колонку большой таблицы без блокировки используйте метод создания индекса в фоновом режиме, если это поддерживается вашей СУБД. Например, в PostgreSQL используйте CREATE INDEX CONCURRENTLY, а в MySQL — ALTER TABLE ... ADD INDEX ... ALGORITHM=INPLACE, LOCK=NONE.

2️⃣ Подробное объяснение темы

Добавление индекса на колонку большой таблицы может привести к блокировке таблицы, что недопустимо в системах с высокой нагрузкой. Чтобы избежать блокировки, можно использовать методы, которые позволяют создавать индексы в фоновом режиме, не мешая текущим операциям с таблицей.

Зачем нужны индексы?

Индексы ускоряют операции поиска в таблицах базы данных. Они работают как указатели, которые позволяют быстро находить нужные строки без необходимости сканировать всю таблицу. Однако создание индекса на большой таблице может занять значительное время и заблокировать таблицу для других операций.

Как это работает?

Разные системы управления базами данных (СУБД) предлагают свои методы для создания индексов без блокировки:

PostgreSQL

В PostgreSQL можно использовать команду CREATE INDEX CONCURRENTLY. Этот метод позволяет создавать индекс без блокировки таблицы для операций чтения и записи. Однако стоит учитывать, что:

• Процесс создания индекса будет медленнее, чем при обычном создании.
• Если операция прервется, индекс останется в невалидном состоянии и его нужно будет удалить.

Пример:

CREATE INDEX CONCURRENTLY idx_column_name ON table_name (column_name);

• CREATE INDEX CONCURRENTLY: Создает индекс в фоновом режиме.
• idx_column_name: Имя нового индекса.
• ON table_name (column_name): Указывает таблицу и колонку, на которую создается индекс.

MySQL

В MySQL начиная с версии 5.6 можно использовать ALTER TABLE с параметрами ALGORITHM=INPLACE и LOCK=NONE. Это позволяет добавлять индексы без блокировки таблицы для операций чтения и записи.

Пример:

ALTER TABLE table_name ADD INDEX idx_column_name (column_name) ALGORITHM=INPLACE, LOCK=NONE;

• ALTER TABLE table_name: Изменяет структуру таблицы.
• ADD INDEX idx_column_name (column_name): Добавляет индекс на указанную колонку.
• ALGORITHM=INPLACE: Указывает, что изменения должны быть выполнены на месте.
• LOCK=NONE: Указывает, что таблица не должна блокироваться.

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

PostgreSQL

Предположим, у вас есть таблица orders с колонкой order_date, и вы хотите добавить индекс на эту колонку:

CREATE INDEX CONCURRENTLY idx_order_date ON orders (order_date);

Этот запрос создаст индекс idx_order_date на колонке order_date таблицы orders без блокировки таблицы.

MySQL

Для таблицы customers с колонкой email:

ALTER TABLE customers ADD INDEX idx_email (email) ALGORITHM=INPLACE, LOCK=NONE;

Этот запрос добавит индекс idx_email на колонку email таблицы customers без блокировки.

Заключение

Использование методов создания индексов в фоновом режиме позволяет поддерживать высокую доступность и производительность базы данных, минимизируя время простоя и блокировки. Это особенно важно для систем с высокой нагрузкой, где даже кратковременная блокировка может привести к значительным задержкам и потерям в производительности.