- Оригинальный адрес:High-speed inserts with MySQL
- Оригинальный автор:Benjamin Morel
- Перевод с:Программа перевода самородков
- Постоянная ссылка на эту статью:GitHub.com/rare earth/gold-no…
- Переводчик:Ситу Гонгзи
- Корректор:GJXAIOU,QinRoc
Когда вам нужно пакетно вставить миллионы фрагментов данных в базу данных MySQL, вы понимаете, что отправка одного за другимINSERTЗаявления не являются жизнеспособным подходом.
Кое-что, что стоит прочитать в документации MySQLВСТАВИТЬ Советы по оптимизации.
В этой статье я расскажу о двух методах эффективной загрузки данных в базу данных MySQL.
LOAD DATA INFILE
Если вы ищете решение для повышения исходной производительности, это определенно ваше решение.LOAD DATA INFILEпредставляет собой оптимизированный оператор специально для MySQL, который вставляет данные непосредственно в таблицу из файла CSV/TSV.
Есть два способа использованияLOAD DATA INFILE. Вы можете скопировать файлы данных в каталог данных сервера (обычно/var/lib/mysql-files/) и запустите:
LOAD DATA INFILE '/path/to/products.csv' INTO TABLE products;
Этот метод довольно громоздкий, потому что вам нужно получить доступ к файловой системе сервера, установить правильные разрешения для файлов данных и т. д.
Хорошей новостью является то, что вы также можете хранить файлы данныхна стороне клиентаи использоватьLOCALКлючевые слова:
LOAD DATA LOCAL INFILE '/path/to/products.csv' INTO TABLE products;
В этом случае файл считывается из файловой системы клиента, прозрачно копируется во временный каталог сервера и импортируется из этого каталога. в конце концов,Это почти так же быстро, как загрузка файлов непосредственно из файловой системы сервера., однако вам нужно убедиться, что на сервере это включеноопции.
LOAD DATA INFILEЕсть много вариантов, в основном связанных со структурой файла данных (разделители полей, вложения и т. д.). пожалуйста, просмотритеДокументациячтобы увидеть полное содержание.
Хотя с точки зрения производительностиLOAD DATA INFILE— лучший вариант, но этот метод требует, чтобы вы сначала экспортировали данные в текстовый файл в форме, разделенной запятыми. Если у вас нет таких файлов, вам придется потратить дополнительные ресурсы на их создание, что может усложнить ваше приложение. К счастью, есть альтернатива.
Расширенные вставки
типичныйINSERTОператор SQL выглядит следующим образом:
INSERT INTO user (id, name) VALUES (1, 'Ben');
extended INSERTОбъедините несколько записей вставки в один оператор запроса:
INSERT INTO user (id, name) VALUES (1, 'Ben'), (2, 'Bob');
Суть в том, чтобы найти оптимальное количество записей для вставки в каждый оператор. Не существует универсального числа, подходящего для всех, поэтому вам потребуется сравнить образец ваших данных, чтобы найти максимальный прирост производительности или лучший компромисс между использованием памяти и производительностью.
Чтобы в полной мере воспользоваться расширенными вставками, мы также рекомендуем:
- Используйте подготовленные операторы
- запустить оператор в транзакции
Ориентиры
Я вставляю 1,2 миллиона записей, каждая запись состоит из 6 смешанных типов данных, и каждый размер данных составляет в среднем около 26 байт. Я использовал две общие конфигурации для тестирования:
- Клиент и сервер находятся на одном компьютере и общаются через сокеты UNIX.
- Клиент и сервер находятся на разных машинах и обмениваются данными по гигабитной сети с очень низкой задержкой (менее 0,1 миллисекунды).
В качестве основы для сравнения я используюINSERT ... SELECTТаблица реплицируется, и производительность этой операции313 000 вставок данных в секунду.
LOAD DATA INFILE
К моему удивлению, результаты испытаний показали, чтоLOAD DATA INFILEчем копировать таблицуБыстрее:
-
LOAD DATA INFILE: в секунду377,000вставлять -
LOAD DATA LOCAL INFILEЧерез сеть: в секунду322,000вставлять
Разница между этими двумя числами, по-видимому, напрямую связана со временем, которое требуется для передачи данных от клиента к серверу: размер файла данных составляет 53 МБ, а разница во времени между двумя тестами составляет 543 мс, что представляет собой скорость передачи 780 Мбит/с, около гигабитной скорости.
Это означает, весьма вероятно,Сервер MySQL не начал обработку файла, пока файл не был полностью передан: Так что скорость вставки напрямую связана с пропускной способностью между клиентом и сервером, что очень важно учитывать, если они не на одной машине.
Extended inserts
я используюBulkInserterЧтобы проверить скорость вставки,BulkInserterя это написалбиблиотека с открытым исходным кодомЧасть класса PHP, который вставляет до 10 000 записей на запрос:
Как мы видим, по мере роста количества вставок на запрос скорость вставки также быстро увеличивается. по пунктам插入По сравнению со скоростью мы получаем 6-кратную производительность на локальном хосте и 17-кратную производительность на веб-хостинге:
- Увеличено число вставок в секунду с 40 000 до 247 000 на локальном хосте.
- Увеличение количества вставок в секунду с 1 2000 до 201 000 на веб-хостах.
В обоих случаях требуется примерно 1000 вставок на запрос для максимальной пропускной способности. но40 вставок на запрос достаточно для пропускной способности 90% на локальном хосте, что может быть хорошим компромиссом. Также обратите внимание, что после пика производительность фактически снижается по мере увеличения количества вставок на запрос.
Преимущество расширенной вставки еще более заметно в случае сетевого подключения, поскольку скорость непрерывной вставки зависит от вашей сетевой задержки.
max sequential inserts per second ~= 1000 / ping in milliseconds
Чем выше задержка между клиентом и сервером, тем больше преимуществ вы получаете от расширенных вставок.
в заключении
Как и ожидалось,LOAD DATA INFILEявляется предпочтительным вариантом для повышения производительности при одном соединении.. Это требует, чтобы вы подготовили правильно отформатированный файл, если вам нужно сначала сгенерировать этот файл и / или передать его на сервер базы данных, обязательно примите во внимание время, затрачиваемое на этот процесс, при тестировании скорости вставки.
С другой стороны, расширенная вставка не требует временного текстового файла и может быть эквивалентнаLOAD DATA INFILEПропускная способность 65%, что является очень разумной скоростью вставки. Интересно, будь то сетевой или локальный,Объединение нескольких вставок в один запрос всегда дает лучшую производительность..
Если вы решили начать использовать расширенную вставку, обязательно сначалаПример данных из производственной средыи некоторые другие номера вставки дляПроверьте свою среду, чтобы найти лучшее значение. .
Будьте осторожны при увеличении количества вставок для одного запроса, поэтому может потребоваться:
- Выделите больше памяти на стороне клиента
- Увеличьте сервер MySQLmax_allowed_packetконфигурация параметров.
Наконец, стоит упомянуть, что, согласно Percona, вы можете использовать одновременные соединения, разбиение на разделы и несколько пулов буферов для повышения производительности. Для получения дополнительной информации см.Эта статья из их блога.
Тесты проводились на голом сервере с Centos 7 и MySQL 5.7, с первичной аппаратной конфигурацией процессора Xeon E3 с частотой 3,8 ГГц, 32 ГБ ОЗУ и твердотельным накопителем NVMe. Тестовые таблицы MySQL используют механизм хранения InnoBD.
Исходный код бенчмарка хранится вgist, полученный график сохраняется вplot.lyначальство.
Если вы обнаружите ошибки в переводе или в других областях, требующих доработки, добро пожаловать наПрограмма перевода самородковВы также можете получить соответствующие бонусные баллы за доработку перевода и PR. начало статьиПостоянная ссылка на эту статьюЭто ссылка MarkDown этой статьи на GitHub.
Программа перевода самородковэто сообщество, которое переводит высококачественные технические статьи из Интернета сНаггетсДелитесь статьями на английском языке на . Охват контентаAndroid,iOS,внешний интерфейс,задняя часть,блокчейн,продукт,дизайн,искусственный интеллектЕсли вы хотите видеть более качественные переводы, пожалуйста, продолжайте обращать вниманиеПрограмма перевода самородков,официальный Вейбо,Знай колонку.