Как быстро вставить данные в MariaDB

В этой статье описаны различные методы быстрого внедрения данных в MariaDB.

Общие сведения

При вставке новых данных в MariaDB время затрачивается на следующие операции (в порядке важности):

• Синхронизация данных на диске (в рамках завершения транзакций)
• Добавление новых ключей. Чем больше индекс, тем больше времени требуется для обновления ключей.
• Проверка на наличие внешних ключей (если они существуют).
• Добавление строк в движок хранения.
• Отправка данных на сервер.

Ниже описаны различные методы (снова, в порядке важности), которые можно использовать для быстрого вставки данных в таблицу.

Отключение ключей

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

ALTER TABLE table_name DISABLE KEYS;
BEGIN;
... inserting data with INSERT or LOAD DATA ....
COMMIT;
ALTER TABLE table_name ENABLE KEYS;

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

При вставке большого объёма данных проверки целостности занимают много времени. Можно отключить индексы UNIQUE и проверки внешних ключей с помощью системных переменных unique_checks и foreign_key_checks:

SET @@session.unique_checks = 0;
SET @@session.foreign_key_checks = 0;

Для таблиц InnoDB можно временно установить режим блокировки AUTO_INCREMENT lock mode на 2, что является самым быстрым значением:

SET @@global.innodb_autoinc_lock_mode = 2;

Также, если таблица имеет INSERT-триггеры или PERSISTENT столбцы, вы можете их удалить, вставить все данные и затем восстановить.

Загрузка текстовых файлов

Самый быстрый способ вставки данных в MariaDB — это команда LOAD DATA INFILE.

Простейшая форма команды:

LOAD DATA INFILE 'file_name' INTO TABLE table_name;

Вы также можете прочитать файл локально на машине, где выполняется клиент, используя:

LOAD DATA LOCAL INFILE 'file_name' INTO TABLE table_name;

Это не так быстро, как чтение файла на стороне сервера, но разница не такая большая.

LOAD DATA INFILE очень быстро, потому что:

1. нет синтаксического анализа SQL.
2. данные считываются большими блоками.
3. если таблица пуста в начале операции, все не уникальные индексы отключаются во время операции.
4. движку сказано сначала кешировать строки, а затем вставлять их большими блоками (это поддерживают MyISAM и Aria).
5. для пустых таблиц некоторые транзакционные движки (например, Aria) не записывают вставленные данные в журнал транзакций, так как операцию можно откатить, выполнив TRUNCATE для таблицы.

Благодаря вышеуказанным преимуществам скорости во многих случаях, когда вам нужно вставить много строк за раз, может быть быстрее создать файл локально, добавить строки туда и затем использовать LOAD DATA INFILE для их загрузки, по сравнению с использованием INSERT для вставки строк.

Вы также получите отчет о выполнении для LOAD DATA INFILE.

mariadb-import

Вы можете параллельно импортировать несколько файлов с помощью mariadb-import (mysqlimport до MariaDB 10.5). Например:

mariadb-import --use-threads=10 database text-file-name [text-file-name...]

Внутренне mariadb-import использует LOAD DATA INFILE для чтения данных.

Вставка данных с помощью операторов INSERT

Использование больших транзакций

При выполнении многих вставок подряд вы должны заключить их в BEGIN / END, чтобы избежать полной транзакции (которая включает синхронизацию диска) для каждой строки. Например, выполнение begin/end каждые 1000 вставок ускорит ваши вставки почти в 1000 раз.

BEGIN;
INSERT ...
INSERT ...
END;
BEGIN;
INSERT ...
INSERT ...
END;
...

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

Вставки нескольких значений

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

INSERT INTO table_name values(1,"row 1"),(2, "row 2"),...;

Предел того, сколько данных можно иметь в одном операторе, контролируется системной переменной сервера max_allowed_packet.

Вставка данных в несколько таблиц одновременно

Если вам нужно вставить данные в несколько таблиц одновременно, лучший способ сделать это — включить многострочные операторы и отправить на сервер сразу несколько вставок:

INSERT INTO table_name_1 (auto_increment_key, data) VALUES (NULL,"row 1");
INSERT INTO table_name_2 (auto_increment, reference, data) values (NULL, LAST_INSERT_ID(), "row 2");

LAST_INSERT_ID() — это функция, которая возвращает последнее auto_increment вставленное значение.

По умолчанию клиент командной строки mariadb будет отправлять вышеуказанное как несколько операторов.

Для тестирования этого в клиенте mariadb нужно сделать:

delimiter ;;
select 1; select 2;;
delimiter ;

Системные переменные сервера, которые можно использовать для настройки скорости вставки

Полный список системных переменных сервера см. в Системные переменные сервера.