Преобразование большой таблицы из MyISAM в Innodb

Из книги вышел следующий отрывок "Высокопроизводительный MySQL, второе издание".

Это отличная книга, и я всем рекомендую ее.

Короткий ответ:

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

Преобразования таблиц

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

ИЗМЕНИТЬ ТАБЛИЦУ

mysql> ALTER TABLE mytable ENGINE = Falcon;

Этот синтаксис работает для всех механизмов хранения, но есть загвоздка: это может занять много времени. MySQL выполнит построчное копирование вашей старой таблицы в новую таблицу. В течение этого времени вы, вероятно, будете использовать всю емкость дискового ввода-вывода сервера, а исходная таблица будет заблокирована для чтения во время выполнения преобразования.

Дамп и импорт

Чтобы получить больший контроль над процессом преобразования, вы можете сначала выгрузить таблицу в текстовый файл с помощью утилиты mysqldump. После создания дампа таблицы вы можете просто отредактировать файл дампа, чтобы настроить содержащийся в нем оператор CREATE TABLE. Обязательно измените имя таблицы, а также ее тип, потому что у вас не может быть двух таблиц с одинаковым именем в одной базе данных, даже если они имеют разные типы, а mysqldump по умолчанию записывает команду DROP TABLE перед CREATE TABLE , поэтому вы можете потерять свои данные, если не будете осторожны!

СОЗДАТЬ и ВЫБРАТЬ

Третий метод преобразования - это компромисс между скоростью первого механизма и безопасностью второго. Вместо того, чтобы выгружать всю таблицу или преобразовывать ее сразу, создайте новую таблицу и используйте синтаксис MySQL INSERT ... SELECT для ее заполнения, как показано ниже:

mysql> CREATE TABLE innodb_table LIKE myisam_table;
mysql> ALTER TABLE innodb_table ENGINE=InnoDB;
mysql> INSERT INTO innodb_table SELECT * FROM myisam_table;

Это хорошо работает, если у вас не так много данных, но если у вас есть, часто бывает более эффективно заполнять таблицу постепенно, фиксируя транзакцию между каждым фрагментом, чтобы журналы отмены не разрастались. Предполагая, что id является первичным ключом, запускайте этот запрос несколько раз (каждый раз используя большие значения x и y), пока вы не скопируете все данные в новую таблицу:

mysql> START TRANSACTION;
mysql> INSERT INTO innodb_table SELECT * FROM myisam_table
-> WHERE id BETWEEN x AND y;
mysql> COMMIT;

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

Оператор ALTER TABLE по сути делает то же самое: сервер создает временную таблицу, в которую он копирует все строки, а затем выполняет RENAME. Дисковые форматы InnoDB и MyISAM сильно различаются, поэтому я не ожидаю, что вы найдете какой-либо ярлык для этого.

Еще одно замечание (о котором вы, возможно, знаете, но оно поможет другим, читающим это): дисковый формат InnoDB сильно зависит от первичного ключа, поскольку он кластеризует записи на его основе. Поэтому при работе с большими таблицами InnoDB дважды подумайте, прежде чем выбирать первичный ключ, потому что при его изменении перестраивается вся таблица, как и в случае с этой проблемой.

В любом случае, я рекомендую сначала провести несколько тестов на столе среднего размера и рассчитать время.