Как создать индекс в MySQL таблице без блокировок?

Часто необходимо ставить индексы на таблицы уже во время их активной эксплуатации. В MySQL на время создания индекса вся таблица блокируется. Ясно, что на крупных таблицах время блокировки может быть существенным (минуты или даже часы).

pt-online-schema-change

Инструмент pt-online-schema-change решает проблему блокировок таким образом:

1. Создает таблицу с такой же структурой, как и у рабочей.
2. Создает индексы на новой таблице (а она пустая, значит все быстро).
3. Копирует данные из рабочей таблицы в новую (это долго, однако никаких блокировок).
4. Создает триггеры для синхронизации данных между рабочей и новой таблицами.
5. Заменяет рабочую таблицу на новую и удаляет старую.

Установка из пакетов:

apt-get install percona-toolkit

Для создания индекса на большой рабочей таблице нужна такая команда:
pt-online-schema-change --alter "add index some_idx (some_val)" D=test,t=test,h=127.0.0.1 --user root --password root **--execute**
## Добавляем индекс на колонку some_val таблицы test из базы test

Опция –execute нужна для того, чтобы выполнить сам замену таблиц. Без этой опции будет проведено только тестовое копирование, но оригинальная таблица не будет изменена. Первый раз команду лучше запустить без этой опции, чтобы ничего не сломать.

No slaves found.  See --recursion-method if host ruhighload.com has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`test`...
Creating new table...
Created new table test._test_new OK.
Altering new table...
Altered `test`.`_test_new` OK.
2015-02-21T20:31:08 Creating triggers...
2015-02-21T20:31:08 Created triggers OK.
2015-02-21T20:31:08 Copying approximately 1 rows...
2015-02-21T20:31:08 Copied rows OK.
2015-02-21T20:31:08 Swapping tables...
2015-02-21T20:31:08 Swapped original and new tables OK.
2015-02-21T20:31:08 Dropping old table...
2015-02-21T20:31:08 Dropped old table `test`.`_test_old` OK.
2015-02-21T20:31:08 Dropping triggers...
2015-02-21T20:31:08 Dropped triggers OK.
**Successfully altered `test`.`test`.**

Если Вы увидели такую же строку, значит команда сработала.

Осторожно

Этот инструмент изменяет данные. Ошибка в команде может привести к их потере. Обязательно тестируйте команду (без опции –execute) перед окончательным запуском.