В предыдущих статьях я говорил о том, как максимально быстро сохранить большой объем информации когда вы ограничены JPA и как сделать это только с JDBC.



Быстрое сохранение в базе данных: JPA
Когда у вас есть много данных, которые нужно сохранить за короткое время, вы должны искать каждую неэффективность в доступе к базе данных. medium.com





Быстрое сохранение в базе данных: JDBC
В моем предыдущем посте я объяснил, как сохранить информацию, когда вы вынуждены использовать JPA, и как заставить ее работать как… medium.com



Хотя JDBC является основным элементом связи с базой данных, есть способы пропустить его и, не покидая JVM, сохранить информацию еще быстрее с помощью некоторых нестандартных методов драйверов JDBC каждой базы данных.

LOAD DATA и COPY команды

Языком связи с базами данных является SQL, и 99,99% времени - это механизм, который мы используем для связи с ними.

Когда мы делаем предложение с несколькими вставками таким образом

INSERT INTO persona (ssn, name, age) VALUES
 ('987-65-4321', 'Alberto Zaplana', 26),
 ('123-45-6789', 'Zoe Alarcón', 62);

если мы удалим синтаксис SQL, у нас останется CSV

ssn,name,age
'987-65-4321','Alberto Zaplana',26
'123-45-6789','Zoe Alarcón',62

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

Вот почему базы данных предпочитают данные в формате CSV при большом импорте, а не в качестве запроса. Для импорта файлов в формате CSV в MySQL у нас есть команда LOAD DATA, в Postgres команда COPY и в SQL Server команда BULK INSERT.

Самая большая проблема - это установка формата CSV, потому что необходимо настроить такие вещи, как:

  • Какой будет символ разделителя столбцов
  • Нужно ли заключать строки в кавычки и с каким символом
  • Какой будет символ изменения строки / записи
  • Какой будет символ, представляющий нулевое значение (в CSV нет значения, это пустая строка, отличная от нулевого значения)
  • Какой будет символ escape, когда мы найдем один из предыдущих символов

Пример каждой команды с набором данных, который использовался в прошлых статьях, и той же таблицей:

  • Над файлом myfile.csv в формате CSV, готовым к использованию:
  • В консоли MySQL:
LOAD DATA LOCAL INFILE 'myfile.csv' INTO TABLE bike_trip CHARACTER SET UTF8 FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY ''
(tripduration, starttime, stoptime, start_station_id, start_station_name, start_station_latitude, start_station_longitude, end_station_id, end_station_name, end_station_latitude, end_station_longitude, bike_id, user_type, birth_year, gender)
  • В консоли Postgres:
COPY bike_trip (tripduration, starttime, stoptime, start_station_id,
start_station_name, start_station_latitude, start_station_longitude, end_station_id, end_station_name, end_station_latitude, end_station_longitude, bike_id, user_type, birth_year, gender)
FROM 'myfile.csv' WITH (FORMAT TEXT, ENCODING 'UTF-8', DELIMITER '\t', HEADER false)

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

Его функциональность одинакова во всех случаях: мы выполняем с обычным соединением JDBC предложение, подобное тому, которое мы написали бы в консоли запросов, и мы прикрепляем InputStream к CSV:

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

Код будет делать то же, что и в предыдущих сообщениях: сохранять информацию в блоках по 1000 записей с коммитами в каждом пакете (в этом случае он будет в режиме автоматической фиксации). Чтобы иметь возможность сравнивать, мы измерим количество сохраняемых записей в секунду.

MySQL: LOAD DATA

Из соединения, возвращенного источником данных, мы должны сначала получить объект с собственным com.mysql.jdbc.Connection интерфейсом MySQL. Как-то делаем кастинг на этот интерфейс. Если бы JDBC не смог, это вызвало бы исключение.

Интерфейс MySQL Connection имеет метод setAllowLoadLocalInfile, который позволяет драйверу использовать команду LOAD DATA.

Затем нам нужно только создать строку CSV из объектов, которые у нас есть в памяти, в соответствии с форматом, который мы определили в предложении LOAD DATA:

Когда мы достигаем соответствующего размера пакета, мы отправляем CSV, используя специальный метод реализации MySQL Statement, который необходимо преобразовать:

Метод setLocalInfileInputStream сохраняет во внутренней переменной объекта Statement the InputStream, откуда следует читать CSV для отправки в базу данных. Поскольку это информация, генерируемая на лету, я помещаю ее в InputStream памяти. Вы также можете сохранить его в файле и использовать FileInputStream.

Наконец, мы выполняем оператор LOAD DATA как обычный JDBC Statement. База данных внутренне ответит, что ей нужна информация, и драйвер, наконец, отправит все содержимое InputStream в базу данных.

Postgres: COPY

В случае с Postgres его реализация лучше, и нам не нужно изменять специальный флаг подключения, чтобы сделать что-то исключительное в некоторых предложениях. Нам просто нужно запросить специальный объект, который обо всем позаботится: CopyManager.

Мы также должны создать CSV со значениями, которые будут сохраняться в соответствии с выбранным форматом (в этом примере я буду использовать формат ТЕКСТ):

и, наконец, когда мы достигаем количества записей в пакете, мы отправляем CSV в базу данных с CopyManager, снова используя InputStream в памяти:

Контрольные точки

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

Впервые мы преодолеваем барьер в 50 000 записей в секунду!

Но чтобы узнать, насколько нам удалось улучшить, сравним с результатами последних публикаций:

Неплохо! повышение производительности примерно на 50% по сравнению с более сложной версией JDBC и на 80–130% быстрее, чем у JPA.

Следующие шаги?

Есть и другие методы повышения скорости вставки, и некоторые из них, которые я видел (и не пробовал все из них):

  • Тестируйте с разными размерами партий. Каждая таблица запрашивает разный размер в зависимости от типа и размера данных.
  • Если вы загружаете пустую таблицу, отключите индексы в начале и активируйте их в конце. Создание индексов по всей таблице обходится дешевле, чем их создание при вставке данных.
  • То же самое и с внешними ключами: лучше не использовать их при вставке. Если ваша бизнес-логика позволяет это, вы сэкономите много времени, если вам не придется проверять каждую ссылку.
  • Если ваши данные уже содержат первичный ключ, MySQL предпочитает, чтобы вы вставляли его отсортированным по первичному ключу.

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

Что касается последних двух пунктов, будьте хорошо информированы и проверьте, прежде чем что-либо делать, потому что последствия могут быть катастрофическими.

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