Что быстрее: несколько одиночных INSERT или один многострочный INSERT?

Я пытаюсь оптимизировать одну часть моего кода, который вставляет данные в MySQL. Должен ли я цепочки INSERT сделать один огромный многострочный INSERT или несколько раз в несколько раз?

http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html

Время, необходимое для вставки строки, определяется следующими факторами, в которых цифры указывают приблизительные пропорции:

  • Подключение: (3)
  • Отправка запроса на сервер: (2)
  • Разбор запроса: (2)
  • Вставка строки: (1 × размер строки)
  • Вставка индексов: (1 × количество индексов)
  • Закрытие: (1)

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

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

Я знаю, что я отвечаю на этот вопрос почти через два с половиной года после того, как его спросили, но я просто хотел предоставить некоторые жесткие данные из проекта, над которым я сейчас работаю, что показывает, что действительно выполнение нескольких блоков VALUE для каждой вставки MUCH быстрее, чем последовательные одиночные операторы 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 улучшение становится незначительным.

Надеемся, что люди решат: а) использовать идею multiprepare и (б) сколько блоков VALUE для создания каждого оператора (если вы хотите работать с данными, которые могут быть достаточно большими, чтобы выталкивать запрос за максимальный размер запроса для MySQL, по моему мнению, по умолчанию 16 МБ во многих местах, возможно, больше или меньше в зависимости от значения max_allowed_packet, установленного на сервере.)

Основной фактор будет заключаться в том, используете ли вы механизм транзакций и используете ли вы автокоммутировать.

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

Предполагая единственный stream, это означает, что серверу необходимо синхронизировать некоторые данные с диском для КАЖДОЙ РЯДЫ. Он должен дождаться, пока данные достигнут постоянного хранилища (надеюсь, что байт-резервный баран в вашем RAID-controllerе). Это по своей сути довольно медленно и, вероятно, станет ограничивающим фактором в этих случаях.

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

Я также предполагаю, что вы используете один stream для этих вставок. Использование нескольких streamов немного искажает вещи, потому что некоторые версии MySQL имеют групповую фиксацию в innodb – это означает, что несколько streamов, выполняющих свои собственные коммиты, могут совместно использовать одну запись в журнале транзакций, что хорошо, потому что это означает, что меньше синхронизаций с постоянным хранилищем ,

С другой стороны, результатом является то, что вы ДЕЙСТВИТЕЛЬНО ХОТИТЕ ИСПОЛЬЗОВАТЬ многострочные вставки.

Существует предел, по которому он становится контрпродуктивным, но в большинстве случаев он составляет не менее 10 000 строк. Поэтому, если вы заказываете до 1000 строк, вы, вероятно, будете в безопасности.

Если вы используете MyISAM, есть еще одна нагрузка, но я не буду вас утомлять. Мир.

Отправляйте как можно больше вставок по проводу за один раз. Фактическая скорость вставки должна быть одинаковой, но вы увидите прирост производительности от сокращения сетевых издержек.

В общем, чем меньше вызовов в базе данных, тем лучше (что означает более быстрый, более эффективный), поэтому попробуйте закодировать вставки таким образом, чтобы они минимизировали доступ к базе данных. Помните, что если вы не используете пул соединений, каждый доступ к базам данных должен создать соединение, выполнить sql, а затем разорвать соединение. Довольно немного накладных расходов!

Вы можете:

  • Убедитесь, что автоматическая фиксация выключена
  • Открыть соединение
  • Отправить несколько пакетов вставок в одной транзакции (размер около 4000-10000 строк)
  • Закрыть соединение

В зависимости от того, насколько хорошо масштабируется ваш сервер (его окончательно в порядке с PostgreSQl , Oracle и MSSQL ), выполните вышеизложенное с несколькими streamами и несколькими соединениями.

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 

Я бы сказал, что встроенный способ пойти 🙂

В общем, несколько вложений будут медленнее из-за дополнительных затрат на соединение. Выполнение нескольких вставок сразу уменьшит стоимость накладных расходов на каждую вставку.

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

Отключить проверки ограничений делают вставки намного быстрее. Это не важно, имеет ли это ваш стол или нет. Например, отключите foreign keys и наслаждайтесь скоростью:

 SET FOREIGN_KEY_CHECKS=0; 

Смешно, насколько плохи Mysql и MariaDB оптимизированы, когда дело доходит до вставок. Я тестировал mysql 5.7 и mariadb 10.3, и никаких реальных отличий от них не было.

Я тестировал это на сервере с дисками NVME, 70 000 IOPS, пропускной способностью 1,1 ГБ / сек и возможностью полного дуплекса (чтение и запись).
Сервер также является высокопроизводительным сервером.
Дал ему 20 ГБ барана.
База данных полностью пуста.

Скорость, которую я получаю, составляла 5000 вставок в секунду при выполнении многострочных вставок (пробовала с 1 МБ до 10 МБ fragmentов данных)

Теперь подсказка:
Если я добавлю еще один stream и вставлю в ТАБЛИЦУШИХ таблиц, у меня вдруг будет 2×5000 / сек. Еще один stream, и у меня 15000 всего / сек

Рассмотрим это: при выполнении ONE вставки вставки это означает, что вы можете последовательно записывать на диск (с исключениями для индексов). При использовании streamов вы фактически деgradleируете возможную производительность, потому что теперь ей нужно делать гораздо более произвольный доступ. Но проверка реальности показывает, что mysql настолько сильно оптимизирован, что streamи очень помогают.

Реальная производительность, возможная с таким сервером, вероятно, составляет миллионы в секунду, процессор находится в режиме ожидания, когда диск простаивает.
Причина в том, что mariadb точно так же, как mysql имеет внутренние задержки.

  • Я продолжаю получать этот код ошибки mysql # 1089
  • Как решить MySQL max_user_connections
  • Ошибка PID при запуске mysql.server?
  • Как выводить результаты запросов MySQL в формате CSV?
  • Вставка нескольких строк в mysql
  • MySQL Query - записи между сегодняшним и последним 30 днями
  • Таблица обновления MySQL на основе другого значения таблиц
  • Как я могу использовать ON DUPLICATE KEY UPDATE в моей модели CodeIgniter?
  • Вставить и вставить MySQL
  • Служба WAMP MySQL не запускается
  • Как создать связанный сервер MySQL
  • Давайте будем гением компьютера.