Что быстрее: несколько одиночных INSERT или одна многострочная INSERT?
Я пытаюсь оптимизировать одну часть моего кода, которая вставляет данные в MySQL. Должен ли я связать INSERT, чтобы сделать одну огромную многорядную INSERT или несколько отдельных INSERT быстрее?
13 ответов
https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html
Время, необходимое для вставки строки, определяется следующими факторами, где числа указывают приблизительные пропорции:
- Подключение: (3)
- Отправка запроса на сервер: (2)
- Запрос синтаксического анализа: (2)
- Вставка строки: (1 × размер строки)
- Вставка индексов: (1 × количество индексов)
- Закрытие: (1)
Из этого должно быть очевидно, что отправка одного большого оператора сэкономит вам 7 накладных расходов на оператор вставки, что в дальнейшем чтении текста также говорит:
Если вы вставляете много строк из одного и того же клиента одновременно, используйте операторы INSERT с несколькими списками VALUES для вставки нескольких строк одновременно. Это значительно быстрее (во многих случаях быстрее), чем использование отдельных однострочных операторов INSERT.
Я знаю, что отвечаю на этот вопрос почти через два с половиной года после того, как он был задан, но я просто хотел предоставить некоторые точные данные из проекта, над которым я сейчас работаю, который показывает, что действительно делать несколько блоков VALUE на одну вставку - НАМНОГО быстрее, чем последовательные одиночные операторы INSERT блока VALUE.
Код, который я написал для этого теста в C#, использует ODBC для чтения данных в память из источника данных MSSQL (~ 19 000 строк, все считываются до начала любой записи), а также соединителя MySql.NET (Mysql.Data. *) Для Вставьте данные из памяти в таблицу на сервере MySQL с помощью подготовленных операторов. Он был написан таким образом, чтобы позволить мне динамически регулировать количество блоков VALUE на подготовленную INSERT (т. Е. Вставлять n строк за раз, где я мог отрегулировать значение n перед выполнением). Я также выполнил тест несколько раз для каждого n.
Выполнение отдельных блоков VALUE (например, по 1 строке за раз) заняло 5,7 - 5,9 секунд. Другие значения следующие:
2 строки за раз: 3,5 - 3,5 секунды
5 строк одновременно: 2,2 - 2,2 секунды
10 строк за раз: 1,7 - 1,7 секунды
50 строк одновременно: 1,17 - 1,18 секунды
100 строк одновременно: 1,1 - 1,4 секунды
500 строк одновременно: 1,1 - 1,2 секунды
1000 строк одновременно: 1,17 - 1,17 секунды
Так что да, даже просто объединение 2 или 3 записей обеспечивает резкое улучшение скорости (время выполнения сокращается с коэффициентом n) до тех пор, пока вы не достигнете где-то между n = 5 и n = 10, и в этот момент улучшение заметно падает, и где-то в диапазоне от n = 10 до n = 50 улучшение становится незначительным.
Надеюсь, что это поможет людям решить (а), следует ли использовать идею множественной подготовки, и (б) сколько блоков VALUE нужно создать для каждого оператора (при условии, что вы хотите работать с данными, которые могут быть достаточно большими, чтобы протолкнуть запрос за максимальный размер запроса для MySQL, который, по моему мнению, по умолчанию составляет 16 МБ во многих местах, возможно, больше или меньше в зависимости от значения max_allowed_packet, установленного на сервере.)
Основным фактором будет то, используете ли вы транзакционный движок и включена ли автоматическая фиксация.
Автокоммит включен по умолчанию, и вы, вероятно, хотите оставить его включенным; поэтому каждая вставка, которую вы делаете, выполняет свою собственную транзакцию. Это означает, что если вы делаете одну вставку на строку, вы собираетесь совершать транзакции для каждой строки.
Предполагая единый поток, это означает, что сервер должен синхронизировать некоторые данные на диск для КАЖДОГО РЯДА. Необходимо подождать, пока данные достигнут постоянного места хранения (возможно, оперативной памяти с резервным питанием в вашем RAID-контроллере). Это по своей природе довольно медленно и, вероятно, станет ограничивающим фактором в этих случаях.
Я, конечно, предполагаю, что вы используете транзакционный движок (обычно innodb) И что вы не изменили настройки, чтобы уменьшить срок службы.
Я также предполагаю, что вы используете один поток для этих вставок. Использование нескольких потоков несколько затрудняет работу, потому что в некоторых версиях MySQL есть рабочая групповая фиксация в innodb - это означает, что несколько потоков, выполняющих свои собственные коммиты, могут совместно использовать одну запись в журнал транзакций, что хорошо, потому что это означает меньшее количество синхронизаций в постоянном хранилище.,
С другой стороны, в результате вы действительно хотите использовать многорядные вставки.
Существует предел, по которому он становится контрпродуктивным, но в большинстве случаев это не менее 10000 строк. Так что если вы упаковываете их до 1000 строк, вы, вероятно, в безопасности.
Если вы используете MyISAM, есть множество других вещей, но я не буду утомлять вас этим. Мир.
Вот результаты небольшого PHP-теста, который я сделал:
Я пытаюсь вставить 3000 записей тремя разными способами, используя PHP 8.0, MySQL 8.1 (mysqli)
Множественные запросы вставки с множественной транзакцией:
$start = microtime(true);
for($i = 0; $i < 3000; $i++)
{
mysqli_query($res, "insert into app__debuglog VALUE (null,now(), 'msg : $i','callstack','user','debug_speed','vars')");
}
$end = microtime(true);
echo "Took " . ($end - $start) . " s\n";
Сделал это 5 раз, в среднем: 11,132 с (+/- 0,6 с)
Множественные запросы вставки с одной транзакцией:
$start = microtime(true);
mysqli_begin_transaction($res, MYSQLI_TRANS_START_READ_WRITE);
for($i = 0; $i < 3000; $i++)
{
mysqli_query($res, "insert into app__debuglog VALUE (null,now(), 'msg : $i','callstack','user','debug_speed','vars')");
}
mysqli_commit($res);
$end = microtime(true);
echo "Took " . ($end - $start) . " ms\n";
Результат с 5 тестами: 0,48 с (+/- 0,04 с)
Единый агрегированный запрос на вставку
$start = microtime(true);
$values = "";
for($i = 0; $i < 3000; $i++)
{
$values .= "(null,now(), 'msg : $i','callstack','user','debug_speed','vars')";
if($i !== 2999)
$values .= ",";
}
mysqli_query($res, "insert into app__debuglog VALUES $values");
$end = microtime(true);
echo "Took " . ($end - $start) . " ms\n";
Результат с 5 тестами: 0,085 с (+/- 0,05 с)
Итак, для вставки 3000 строк это выглядит так:
- Использование нескольких запросов в одной транзакции записи примерно в 22 раза быстрее, чем выполнение нескольких запросов с несколькими транзакциями для каждой вставки.
- Использование одного агрегированного оператора вставки по-прежнему в ~ 6 раз быстрее, чем использование нескольких запросов с одной транзакцией записи.
Отправьте как можно больше вставок через провод за один раз. Фактическая скорость вставки должна быть одинаковой, но вы увидите увеличение производительности за счет снижения нагрузки на сеть.
В целом, чем меньше обращений к базе данных, тем лучше (то есть быстрее, эффективнее), поэтому старайтесь кодировать вставки таким образом, чтобы минимизировать доступ к базе данных. Помните, что если вы не используете пул соединений, каждый доступ к базе данных должен создать соединение, выполнить sql, а затем разорвать соединение. Немного накладных расходов!
Я только что провел небольшой тест, и оказалось, что для многих строк это не быстрее. Вот мой результат для вставки 280000 строк:
- на 10000: 164,96 секунды
- на 5000: 37секунд
- на 1000: 12,56 секунды
- на 600: 12,59 секунды
- на 500: 13,81 секунды
- на 250: 17,96 секунды
- на 400: 14,75 секунды
- на 100: 27секунд
Похоже, что 1000 на 1000 - лучший выбор.
Вы можете захотеть:
- Убедитесь, что авто-фиксация выключена
- Открытое соединение
- Отправить несколько пакетов вставок в одной транзакции (размер около 4000-10000 строк? Вы видите)
- Закрыть соединение
В зависимости от того, насколько хорошо масштабируется ваш сервер (PostgreSQl, Oracle а также MSSQL), выполните вышеуказанные действия с несколькими потоками и несколькими подключениями.
Как правило, несколько вставок будет медленнее из-за издержек соединения. Выполнение нескольких вставок одновременно уменьшит стоимость накладных расходов на одну вставку.
В зависимости от того, какой язык вы используете, вы можете создать пакет на своем языке программирования / сценариев, прежде чем переходить к базе данных, и добавлять каждую вставку в пакет. Тогда вы сможете выполнить большой пакет, используя одну операцию подключения. Вот пример на Java.
MYSQL 5.5 Один SQL-оператор вставки занял от ~300 до ~450 мс. в то время как приведенная ниже статистика предназначена для встроенных множественных вставок.
(25492 row(s) affected)
Execution Time : 00:00:03:343
Transfer Time : 00:00:00:000
Total Time : 00:00:03:343
Я бы сказал, встроенный способ пойти:)
Смешно, как плохо оптимизируются Mysql и MariaDB, когда дело доходит до вставок. Я тестировал mysql 5.7 и mariadb 10.3, никакой разницы в них нет.
Я проверил это на сервере с дисками NVME, 70 000 операций ввода-вывода в секунду, пропускной способностью 1, 1 ГБ / сек, и это возможно в дуплексном режиме (чтение и запись).
Сервер также является высокопроизводительным сервером.
Дали 20 ГБ оперативной памяти.
База данных полностью пуста.
Скорость, которую я получаю, составляла 5000 вставок в секунду при выполнении многорядных вставок (пробовал с 1 МБ до 10 МБ кусков данных)
Теперь подсказка:
Если я добавлю другой поток и вставлю в те же таблицы, у меня вдруг будет 2x5000 / сек. Еще одна нить и у меня всего 15000 в секунду
Учтите это: когда вы выполняете вставку ОДНОГО потока, это означает, что вы можете последовательно записывать на диск (за исключением индексов). При использовании потоков вы фактически снижаете возможную производительность, потому что теперь нужно делать гораздо больше случайных обращений. Но проверка реальности показывает, что mysql настолько плохо оптимизирован, что потоки очень помогают.
Реальная производительность, возможная с таким сервером, вероятно, составляет миллионы в секунду, процессор простаивает, диск простаивает.
Причина этого совершенно ясна: у mariadb, как и у mysql, есть внутренние задержки.
Отключение проверок ограничений делает вставки намного быстрее. Неважно, есть на вашем столе это или нет. Например, проверьте отключение внешних ключей и наслаждайтесь скоростью:
SET FOREIGN_KEY_CHECKS=0;
Я бы добавил информацию о том, что слишком много строк одновременно в зависимости от их содержимого может привести к получению пакета больше, чем max_allowed_packet .
Возможно, подумайте об использовании таких функций, как PHP array_chunk, для выполнения нескольких вставок для ваших больших наборов данных.