MySQL отключить и включить ключи

В моей базе данных MySQL есть таблица, которая содержит 2 000 000 записей. Теперь я хотел бы добавить еще 6 000 000 новых записей в эту таблицу.

Чтобы ускорить вставку, я бы использовал клавиши disable/enable, как показано ниже:

ALTER TABLE cars DISABLE KEYS;

INSERT INTO cars ...
...
...
INSERT INTO cars ...

ALTER TABLE search_all_values ENABLE KEYS;

OPTIMIZE TABLE cars;

Но я как-то чувствую, что клавиши disable/enable имеют смысл использовать пустую вставку в таблицу.

В моем случае у меня уже есть 2 000 000 записей в таблице, когда ENABLE KEYS, mysql будет воссоздавать все индексы (включая существующие записи и новые добавленные записи), которые, вероятно, не приведут к эффективной вставке данных в целом в моем случае. Как повторное создание всех индексов займет много времени, и, возможно, так же OPTIMIZE TABLE

Я хотел бы спросить ваше мнение о том, насколько я прав, и как я могу иметь эффективную вставку данных в моем случае?

Ответ 1

Вам определенно нужно выбрать свой подход на основе типа двигателя... оптимизация для MyISAM или для InnoDB.

Недавно мы провели тест, сравнивающий различные способы вставки данных и измерения времени от вставки до тех пор, пока все индексы не будут полностью восстановлены. Это было на пустом столе, но мы использовали до 10 миллионов строк.

MyISAM с LOAD DATA INFILE и ALTER TABLE ... ENABLE/DISABLE KEYS выиграл руки в нашем тесте (в системе Windows 7, MySQL 5.5.27 - теперь мы пытаемся использовать его в системе Linux).

ENABLE и DISABLE KEYS не работают для InnoDB, это только MyISAM. Для InnoDB используйте SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS = 0; SET UNIQUE_CHECKS = 0;, если вы уверены, что ваши данные не содержат дубликатов (не забудьте установить их в 1 после завершения загрузки).

Я не думаю, что вам нужно OPTIMIZE TABLE после массовой вставки - строки MySQL упорядочиваются вставкой, и индекс все равно перестраивается. Нет лишней фрагментации, делая объемную вставку.

Не стесняйтесь комментировать, если я сделал фактические ошибки.

ОБНОВЛЕНИЕ:. Согласно нашим более поздним и полным результатам теста, советы для клавиш DISABLE/ENABLE неверны.

У коллеги была программа с несколькими различными тестами - таблица с предустановленными и пустыми версиями InnoDB/MyISAM с параметрами LOAD DATA LOCAL, INSERT INTO, REPLACE INTO и UPDATE, на "плотных" и "фрагментированных" таблиц (я не совсем уверен, как, я думаю, это было по строкам DELETE FROM ... ORDER BY RAND() LIMIT ... с фиксированным семенем, поэтому оно все же сопоставимо), а также с включенными и диалогическими индексами.

Мы протестировали его со многими различными версиями MySQL (5.0.27, 5.0.96, 5.1.something, 5.5.27, 5.6.2) в Windows и Linux (хотя и не с теми же версиями на обеих ОС). MyISAM выигрывал только тогда, когда таблица была пустой. InnoDB был быстрее, когда данные уже присутствовали и в целом лучше выполнялись (за исключением hdd-space - MyISAM на диске меньше).

Тем не менее, чтобы извлечь из этого выгоду, вам нужно протестировать его самостоятельно - с разными версиями, различными настройками конфигурации и большим количеством терпения - особенно в отношении странных несоответствий (5.0.97 было намного быстрее, чем 5.5.27 с тем же config - мы все еще ищем причину). Мы обнаружили, что DISABLE KEYS и ENABLE KEYS находятся рядом с бесполезными и иногда вредоносными, если вы не начинаете с пустой таблицы.

Ответ 2

Индексирование новых ключей займет некоторое время. Вам решать, хотите ли вы, чтобы все это выполнялось сразу (сначала отключив его) или по одному (сохраняя его как есть и позволяя индексировать при добавлении каждой записи).

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