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

Jul 21, 2016 15:04 · 176 words · 1 minute read mysql

Порой возникает необходимость добавить индексы в таблицу MySQL в процессе ее эксплуатации. Добавление индексов с помощью ALTER TABLE будет блокировать таблицу, и при серьезных объемах данных время блокировки может оказаться довольно большим. Давайте разберемся, как создать индекс в таблице MySQL без блокировки!

Для добавления индексов «на лету» в таблицу MySQL используется утилита pt-online-schema-change из пакета Percona Toolkit (подробнее об установке этого набора инструментов читайте здесь). Утилита работает по следующей схеме:

  • создает новую таблицу с такой же структурой;
  • добавляет нужные индексы в новую (пустую) таблицу;
  • создает необходимые триггеры (INSERT‘ы в новую таблицу, SELECT‘ы из старой, и т.д.);
  • копирует данные из старой таблицы в новую (это может быть долго, но зато без блокировок);
  • переименовывает новую и удаляет старую таблицу.

Чтобы добавить индекс в таблицу MySQL без блокировки достаточно выполнить следующую команду:

pt-online-schema-change --alter-foreign-keys-method=auto --critical-load Threads_running=150 --alter "ADD INDEX id (id)" D=test_auto_agent,t=test_activity_tmp,u=<ПОЛЬЗОВАТЕЛЬ>,p=<ПАРОЛЬ> --execute

Параметры утилиты pt-online-schema-change аналогичны тем, которые мы использовали при дефрагментации таблиц (за исключением имени базы данных/таблицы).

В результате выполнения вы должны увидеть примерно следующую строку:

...
2016-06-30T12:35:31 Dropping triggers...
2016-06-30T12:35:31 Dropped triggers OK.
Successfully altered `test_auto_agent`.`test_activity_tmp`.
tweet Share