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

Предыстория проблемы:

Создание индекса для большой таблицы MySQL может оказаться сложной задачей, особенно в производственной среде, где бесперебойный доступ имеет решающее значение. Традиционные операторы CREATE INDEX могут привести к полной блокировке таблицы, блокируя все одновременные операции.

Соображения по версии MySQL:

• В MySQL 5.6 и выше обновляются индексы выполняются онлайн, что позволяет продолжать операции чтения и записи во время создания индекса.
• Однако в MySQL 5.5 и более ранних версиях, включая InnoDB и Таблицы MyISAM, обновления индекса будут блокировать запись в таблицу.

Подход циклических мастеров:

Для версий MySQL до 5.6 одним из эффективных подходов является циклический подход. техника мастера:

1. Настройте вторичный мастер (Master B), реплицирующийся с первичного мастера (Master A).
2. Выполните обновление схемы на ведущем устройстве B (позволяя ему отставать во время обновления).
3. Убедитесь, что изменение схемы совместимо с командами репликации из схемы пониженной версии на ведущем устройстве A. .
4. Атоматически переключите всех клиентов с Master A на Master B.
5. Обновите схему на Master A и сделайте ее вторичной master.

Инструмент pt-online-schema-change от Percona:

Этот инструмент автоматизирует подход циклических мастеров посредством:

• Создание новой таблицы с обновленной схемой.
• Синхронизация новой таблицы с исходной таблицей с помощью триггер.
• Пакетное копирование строк из исходной таблицы.
• Замена исходной таблицы новой таблицей.

Рекомендации по AWS RDS:

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

Альтернативные методы:

• Используйте временный table: Создайте временную таблицу с новым индексом, вставьте данные из исходной таблицы и замените исходную таблицу временной. table.
• Разбейте таблицу на разделы: Разделите таблицу на более мелкие разделы, создайте индекс для каждого раздела отдельно, а затем объедините разделы обратно.
• Используйте фоновый процесс: Создайте отдельный фоновый процесс, который постепенно создает индекс, в то время как таблица остается доступной для обычных операций. Этот подход поддерживается не во всех версиях MySQL.