8.5.5 Bulk Data Loading for InnoDB Tables — MySQL 5.7

8.5.5 Загрузка больших данных в таблицы InnoDB

Эти советы по производительности дополняют общие рекомендации по быстрым вставкам в разделе 8.2.4.1 «Оптимизация операторов INSERT».

При импорте данных в InnoDB отключите режим автокоммита, так как он выполняет сброс журнала на диск для каждой вставки. Чтобы отключить автокоммит во время операции импорта, заключите ее в операторы SET autocommit и COMMIT:
```
SET autocommit=0;
... SQL import statements ...
COMMIT;
```
Параметр mysqldump --opt создает файлы дампов, которые быстро импортируются в таблицу InnoDB, даже без обёртывания их операторами SET autocommit и COMMIT.
Если у вас есть UNIQUE ограничения на вторичных ключах, вы можете ускорить импорт таблиц, временно отключив проверки уникальности во время сессии импорта:
```
SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;
```
Для больших таблиц это экономит много дисковых операций ввода-вывода, так как InnoDB может использовать свой буфер изменений для записи записей вторичного индекса в пакетном режиме. Убедитесь, что данные не содержат дубликатов ключей.
Если у вас есть FOREIGN KEY ограничения в ваших таблицах, вы можете ускорить импорт таблиц, отключив проверки внешних ключей на время сессии импорта:
```
SET foreign_key_checks=0;
... SQL import statements ...
SET foreign_key_checks=1;
```
Для больших таблиц это может значительно сэкономить дисковые операции ввода-вывода.
Используйте синтаксис многострочного INSERT, чтобы уменьшить накладные расходы на передачу данных между клиентом и сервером, если вам нужно вставить много строк:
```
INSERT INTO yourtable VALUES (1,2), (5,5), ...;
```
Этот совет справедлив для вставок в любые таблицы, а не только в таблицы InnoDB.
При выполнении массовых вставок в таблицы с автоинкрементными столбцами установите innodb_autoinc_lock_mode в значение 2 вместо значения по умолчанию 1. Подробности см. в разделе 14.6.1.6 «Обработка AUTO_INCREMENT в InnoDB».
При выполнении массовых вставок быстрее вставлять строки в PRIMARY KEY порядке. InnoDB таблицы используют , что делает относительно быстрым использование данных в порядке PRIMARY KEY. Выполнение массовых вставок в PRIMARY KEY порядке особенно важно для таблиц, которые полностью не помещаются в буферный пул.
Для оптимальной производительности при загрузке данных в таблицу InnoDB FULLTEXT индекса выполните следующие действия:
1. Определите столбец FTS_DOC_ID при создании таблицы, типа BIGINT UNSIGNED NOT NULL, с уникальным индексом FTS_DOC_ID_INDEX. Например:
```
CREATE TABLE t1 (
FTS_DOC_ID BIGINT unsigned NOT NULL AUTO_INCREMENT,
title varchar(255) NOT NULL DEFAULT '',
text mediumtext NOT NULL,
PRIMARY KEY (`FTS_DOC_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);
```
2. Загрузите данные в таблицу.
3. Создайте индекс FULLTEXT после загрузки данных.
Примечание

При добавлении столбца FTS_DOC_ID при создании таблицы, убедитесь, что столбец FTS_DOC_ID обновляется при обновлении столбца FULLTEXT с индексом, поскольку FTS_DOC_ID должен монотонно возрастать при каждом вызове INSERT или UPDATE. Если вы решите не добавлять столбец FTS_DOC_ID при создании таблицы и хотите, чтобы InnoDB управлял идентификаторами DOC, InnoDB добавляет столбец FTS_DOC_ID в качестве скрытого столбца с следующим вызовом CREATE FULLTEXT INDEX. Однако этот подход требует перестроения таблицы, что может повлиять на производительность.