Мне нужно создать индекс в таблице MySQL из 5М строк. Это производственная таблица, и я боюсь полного блока всего, если я запускаю оператор CREATE INDEX...
Есть ли способ создать этот индекс без блокировки вставок и выбора?
Просто интересно, что мне не нужно останавливаться, создавать индекс и перезагружать мою систему!
https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html
В MySQL 5.6 и выше таблица остается доступной для операций чтения и записи во время создания или удаления индекса. Операция CREATE INDEX или DROP INDEX завершается только после завершения всех транзакций, которые обращаются к таблице, так что начальное состояние индекса отражает самое последнее содержимое таблицы. Ранее изменение таблицы при создании или удалении индекса обычно приводило к тупиковой ситуации, которая отменила оператор INSERT, UPDATE или DELETE в таблице.
Из вышеприведенного ответа:
"Если вы используете версию с индексом более 5,1, когда база данных находится в сети, то не беспокойтесь, вы не будете прерывать использование системы производства."
Это **** FALSE **** (по крайней мере, для таблиц MyISAM/InnoDB, что составляет 99,999% людей, которых там используют. Clustered Edition отличается.)
Выполнение операций UPDATE в таблице будет BLOCK во время создания индекса. MySQL действительно, действительно глупо об этом (и еще несколько вещей).
Тест Script:
(
for n in {1..50}; do
#(time mysql -uroot -e 'select * from website_development.users where id = 41225\G'>/dev/null) 2>&1 | grep real;
(time mysql -uroot -e 'update website_development.users set bio="" where id = 41225\G'>/dev/null) 2>&1 | grep real;
done
) | cat -n &
PID=$!
sleep 0.05
echo "Index Update - START"
mysql -uroot website_development -e 'alter table users add index ddopsonfu (last_name, email, first_name, confirmation_token, current_sign_in_ip);'
echo "Index Update - FINISH"
sleep 0.05
kill $PID
time mysql -uroot website_development -e 'drop index ddopsonfu on users;'
Мой сервер (InnoDB):
Server version: 5.5.25a Source distribution
Выход (обратите внимание, как 6-я операция блокирует для ~ 400 мс, чтобы завершить обновление индекса):
1 real 0m0.009s
2 real 0m0.009s
3 real 0m0.009s
4 real 0m0.012s
5 real 0m0.009s
Index Update - START
Index Update - FINISH
6 real 0m0.388s
7 real 0m0.009s
8 real 0m0.009s
9 real 0m0.009s
10 real 0m0.009s
11 real 0m0.009s
Операции чтения Vs, которые не блокируют (заменяйте комментарий строки в script):
1 real 0m0.010s
2 real 0m0.009s
3 real 0m0.009s
4 real 0m0.010s
5 real 0m0.009s
Index Update - START
6 real 0m0.010s
7 real 0m0.010s
8 real 0m0.011s
9 real 0m0.010s
...
41 real 0m0.009s
42 real 0m0.010s
43 real 0m0.009s
Index Update - FINISH
44 real 0m0.012s
45 real 0m0.009s
46 real 0m0.009s
47 real 0m0.010s
48 real 0m0.009s
Таким образом, существует только один метод, который я знаю, чтобы обновить схему MySql и не испытывать сбоя доступности. Круговые мастера:
Простым способом обновления схемы это не так. Работает в серьезной производственной среде; да. Пожалуйста, пожалуйста, пожалуйста, если есть более простой способ добавить индекс в таблицу MySQL, не блокируя записи, сообщите мне.
Googling приведет меня к этой статье, в которой описывается подобная методика. Еще лучше, они советуют пить в одном и том же месте в процессе (обратите внимание, что я написал свой ответ, прежде чем читать статью)!
статья Я связал выше разговоры о инструменте, pt-online-schema-change, который работает следующим образом:
Я никогда не пробовал инструмент самостоятельно. YMMV
В настоящее время я использую MySQL через Amazon RDS. Это действительно отличный сервис, который объединяет и управляет MySQL, позволяя вам добавлять новые реплики для чтения с помощью одной кнопки и прозрачно обновлять базу данных через аппаратные SKU. Это очень удобно. Вы не получаете SUPER доступ к базе данных, поэтому вы не можете напрямую вставлять репликацию (это благословение или проклятие?). Тем не менее, вы можете использовать Read Replica Promotion, чтобы внести изменения в схему на ведомом только для чтения, а затем продвинуть этот ведомый, чтобы стать вашим новым мастером. Точно такой же трюк, как я описал выше, просто намного проще выполнить. Они все еще не делают много, чтобы помочь вам с сокращением. Вам необходимо перенастроить и перезапустить приложение.
Как показано в блоге, механизм InnoDB ALTER TABLE полностью переработан для MySQL 5.6.
(Для эксклюзивного обзора этой темы документация по MySQL может обеспечить дневную стоимость чтения.)
Чтобы добавить индекс в таблицу без блокировки в результате UPDATE/INSERT, можно использовать следующий формат инструкции:
ALTER TABLE my_table ADD INDEX my_table__idx (my_column), ALGORITHM=INPLACE, LOCK=NONE;
Обновление MySQL 5.6 (февраль 2013): теперь вы можете выполнять операции чтения и записи, пока индекс создается даже с таблицами InnoDB - http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html
В MySQL 5.6 и выше таблица остается доступной для операций чтения и записи во время создания или удаления индекса. Операция CREATE INDEX или DROP INDEX завершается только после завершения всех транзакций, которые обращаются к таблице, так что начальное состояние индекса отражает самое последнее содержимое таблицы. Ранее изменение таблицы при создании или удалении индекса обычно приводило к тупиковой ситуации, которая отменила оператор INSERT, UPDATE или DELETE в таблице.
и:
В MySQL 5.6 эта функция становится более общей: вы можете читать и писать в таблицы при создании индекса, а также выполнять многие другие операции ALTER TABLE без копирования таблицы без блокировки операций DML или обоих, Таким образом, в MySQL 5.6 и выше мы обычно ссылаемся на этот набор функций как на онлайн-DDL, а не на Fast Index Creation.
из http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_fast_index_creation
pt-online-schema-change - это путь, если вы действительно хотите убедиться, что миграция не приведет к удалению сайта.
Как я писал в приведенном выше комментарии, у меня есть несколько опытов с pt-online-schema-change в производстве. У нас есть наша основная таблица записей 20M + и master → 2 ведомых репликации только для чтения. Я выполнил по крайней мере десятки миграций с pt-online-schema-change от добавления нового столбца, изменяя набор символов, добавляя несколько индексов. Мы также обслуживаем тонны трафика во время миграции, и у нас не было икоты. Конечно, вам нужно будет тщательно протестировать все сценарии, прежде чем запускать его.
Я попытался внести изменения в 1 script, чтобы изменить pt-online-schema только один раз. И будьте очень осторожны с изменением имени столбца, так как вы потеряете свои данные. Однако добавление индекса должно быть прекрасным.