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

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

код, который я написал для этого теста в C#, использует ODBC для чтения данных в память из источника данных MSSQL (~19 000 строк, все читаются перед любой записью начинается), и MySQL .NET connector (Mysql.Данные.* ) материал для вставки данных из памяти в таблицу на сервере MySQL с помощью подготовленных операторов. Он был написан таким образом, чтобы позволить мне динамически регулировать количество блоков значений на подготовленную вставку (т. е. вставлять n строк за раз, где я мог бы настроить значение n перед запуском.) Я также провел тест несколько раз для каждого n.

выполнение одиночных блоков значений (например, 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 и (Б) сколько блоков значений для создания каждого оператора (предполагая, что вы хотите работать с данными, которые могут быть достаточно большими, чтобы протолкнуть запрос мимо максимального размера запроса для MySQL, который, я считаю, составляет 16 МБ по умолчанию во многих случаях). места, возможно больше или меньше, в зависимости от значение max_allowed_packet на сервере.)

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

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

предполагая один поток, это означает, что сервер должен синхронизировать некоторые данные на диск для каждой строки. Его нужно дождаться данные для достижения постоянного места хранения (надеюсь, ОЗУ с батарейным питанием в вашем RAID-контроллере). Это по своей сути довольно медленно и, вероятно, станет сдерживающим фактором в этих случаях.

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

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

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

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

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

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

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

вы хотите :

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

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

MYSQL 5.5 Один оператор SQL insert занял от ~300 до ~450 МС. а ниже статистика для встроенных нескольких Insert заявления.

(25492 row(s) affected)
Execution Time : 00:00:03:343
Transfer Time  : 00:00:00:000
Total Time     : 00:00:03:343

Я бы сказал, что inline-это путь:)

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

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

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

SET FOREIGN_KEY_CHECKS=0;

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

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

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

теперь разгадка:
Если я добавлю еще один поток и вставлю в те же таблицы, у меня вдруг будет 2x5000 /сек. Еще один поток и у меня есть 15000 всего /сек

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

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