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

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

Обычный доступ к базе данных состоит из отправки каждого оператора DML (вставка, обновление, удаление) в базу данных в отдельной базе данных / доступ к сети. Каждый доступ к базе данных имеет определенные накладные расходы, и база данных должна обрабатывать каждый оператор независимо. Пакетная запись имеет две формы, динамическую и параметризованную. Параметризация является наиболее распространенной и обычно обеспечивает наилучшее преимущество, так как динамические могут иметь проблемы с анализом.

Чтобы понять пакетную запись, вы должны сначала понять параметризованный SQL. Выполнение SQL состоит из двух частей: синтаксического анализа и выполнения. Анализ состоит из преобразования строкового представления SQL в представление базы данных. Выполнение состоит из выполнения проанализированного SQL в базе данных. Базы данных и JDBC поддерживают параметры связывания, поэтому аргументы SQL (данные) не нужно встраивать в SQL. Это позволяет избежать затрат на преобразование данных в текст и позволяет повторно использовать один и тот же оператор SQL при многократном выполнении. Это допускает один разбор и многократное выполнение, также называемое «параметризованный SQL». Большинство реализаций JDBC DataSource и JPA-провайдеров поддерживают параметризованный SQL и кэширование операторов, что фактически позволяет избежать анализа в запущенном приложении.

Пример динамического SQL

Пример параметризованного SQL

Параметризованная пакетная запись включает в себя выполнение одного оператора DML, но с набором параметров связывания для нескольких однородных операторов вместо параметров связывания для одного оператора. Это эффективно позволяет обрабатывать базу данных и сеть большой партией однородных вставок, обновлений или удалений как одну операцию, а не как n операций. База данных должна выполнять только минимальный объем работы, поскольку существует только один оператор, то есть не более одного анализа. Он также совместим с кэшированием операторов, поэтому анализ синтаксиса не требуется вообще. Ограничение состоит в том, что все SQL оператора должны быть идентичны. Таким образом, это действительно хорошо работает, скажем, для вставки 1000 ордеров, поскольку SQL вставки одинаков для каждого ордера, отличаются только параметры связывания. Но это не помогает для вставки 1 Order или 1 Order, 1 OrderLine и 1 Customer. Кроме того, все операторы должны быть частью одной транзакции базы данных.

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

JDBC стандартизирует пакетную запись через свои API-интерфейсы Statement и PrepareStatement (начиная с JDBC 2.0, который был JDK 1.2, то есть давно). Для пакетного API JDBC требуется другой код JDBC, поэтому, если вы используете сырой JDBC, вам нужно переписать свой код для переключения между пакетным и не пакетным API. Большинство драйверов JDBC теперь поддерживают эти API, но некоторые на самом деле не отправляют DML в базу данных в виде пакета, они просто эмулируют API. Так как же узнать, действительно ли вы получаете пакетную запись? Единственный реальный способ — это проверить его и измерить разницу в производительности.

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

EclipseLink поддерживает как параметризованную, так и динамическую пакетную запись (начиная с EclipseLink 1.0). В EclipseLink пакетная запись включается через свойство модуля персистентности "eclipselink.jdbc.batch-writing" . EclipseLink предоставляет три опции: "JDBC" , "Buffered" и "Oracle-JDBC" . Всегда следует использовать опцию "JDBC" .

"Buffered" предназначен для драйверов JDBC, которые не поддерживают пакетную запись, и объединяет динамические операторы SQL в один блок. "Buffered" не поддерживает параметризованный SQL и не рекомендуется.

"Oracle-JDBC" использует API JDBC базы данных Oracle, который предшествовал стандартному API JDBC и теперь устарел. До EclipseLink 2.5 этот параметр разрешал пакетную запись при использовании оптимистической блокировки, но теперь обычная опция "JDBC" поддерживает оптимистическую блокировку.

EclipseLink 2.5 поддерживает пакетную запись с оптимистической блокировкой на всех (совместимых) платформах баз данных, где, как и ранее, она поддерживалась только на выбранных платформах баз данных. EclipseLink 2.5 также предоставляет подсказку для запроса "eclipselink.jdbc.batch-writing" чтобы отключить пакетную запись для собственных запросов, которые не могут быть пакетированы (например, DDL или хранимые процедуры на некоторых платформах баз данных).

EclipseLink поддерживает параметризованный SQL через свойства модуля персистентности "eclipselink.jdbc.bind-parameters" и "eclipselink.jdbc.cache-statements" . Однако их обычно не нужно устанавливать, так как привязка параметров используется по умолчанию, поэтому вы можете установить свойство только для отключения привязки. Кэширование операторов не включено по умолчанию, но имеет отношение только к EclipseLink, если используется пул соединений EclipseLink, если вы используете JDBC или Java EE DataSource, тогда вы должны настроить кэширование операторов в конфигурации DataSource.

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

Поддержка пакетной записи не является невероятно сложной, большинство провайдеров JPA поддерживают это, упорядочивая SQL таким образом, чтобы его можно было пакетировать, — сложная часть. Во время операции фиксации или сброса EclipseLink автоматически группирует SQL по таблицам, чтобы гарантировать возможность формирования однородных операторов SQL (и в то же время все еще поддерживает ограничения ссылочной целостности и избегает мертвых блокировок). Большинство провайдеров JPA этого не делают, поэтому, даже если они поддерживают пакетную запись, большую часть времени SQL не извлекает выгоду из пакетной обработки.

Чтобы включить пакетную запись в EclipseLink, добавьте следующее в свойство модуля постоянства;

Вы также можете настроить размер пакета, используя свойство единицы сохранения состояния "eclipselink.jdbc.batch-writing.size" . Размер по умолчанию — 100.

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

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

Каждая база данных работает на различном оборудовании, некоторые являются локальными, а некоторые находятся в сети, поэтому не сравнивайте одну базу данных с другой. Интересующие данные — это процентное преимущество, которое дает возможность пакетной записи не использовать пакетную запись. Для теста вставки я также измерил разницу между использованием параметризованного и динамического SQL и параметризованного SQL без кэширования операторов. Результатом является количество транзакций, обработанных за 10 секунд (выполненных 5 раз и усредненных), поэтому большее число — лучший результат.

База данных: MySQL версия: 5.5.16
Драйвер: MySQL-AB JDBC Версия драйвера: mysql-connector-java-5.1.22

Вставить тест

Обновить тест

Таким образом, результаты указывают на то, что пакетная запись никак не влияет (5% находится в пределах отклонения). На самом деле это означает, что драйвер MySQL JDBC на самом деле не использует пакетную обработку, он просто эмулирует пакетные API-интерфейсы JDBC и выполняет инструкции один за другим.

MySQL, тем не менее, поддерживает пакетную обработку, просто требует другого SQL. Драйвер MySQL JDBC поддерживает это, но требует установки rewriteBatchedStatements=true JDBC-соединения rewriteBatchedStatements=true . Это можно легко установить, изменив URL подключения, например;

MySQL: rewriteBatchedStatements = true

Вставить тест

Обновить тест

Таким образом, кажется, что пакетная запись имеет большое значение в MySQL, если настроена правильно (почему драйвер JDBC не делает это по умолчанию, я понятия не имею). Параметризованная пакетная запись работает лучше всего: она на 332% быстрее для вставок и на 179% быстрее для обновлений. Динамическая пакетная запись также работает довольно хорошо. Интересно, что в MySQL, похоже, есть небольшая разница между динамическим и параметризованным SQL (я предполагаю, что MySQL действительно быстрее разбирает или мало оптимизирует подготовленные операторы).

Версия PostgreSQL: 9.1.1
PostgreSQL 8.4 JDBC4

Вставить тест

Обновить тест

Результаты показывают, что пакетная запись имеет большое значение для PostgreSQL. Параметризованная пакетная запись работает лучше всего: она на 325% быстрее для вставок и на 203% быстрее для обновлений. Динамическая пакетная запись также работает довольно хорошо. Для PostgreSQL я также измеряю производительность буферизованной пакетной записи EclipseLink, которая выполняет те же функции, что и динамическая пакетная запись JDBC, поэтому я предполагаю, что драйвер делает то же самое. Параметризованный SQL превосходит динамический SQL примерно на 10%, но параметризованный SQL без кэширования операторов работает аналогично динамическому SQL.

Oracle Database 11g Enterprise Edition, выпуск 11.1.0.7.0
Драйвер Oracle JDBC Версия: 11.2.0.2.0

Вставить тест

Обновить тест

Результаты показывают, что параметризованная пакетная запись имеет большое значение для Oracle: на 503% быстрее для вставок и на 492% быстрее для обновлений. Динамическая пакетная запись не дает никаких преимуществ, потому что драйвер Oracle JDBC просто эмулирует динамическую пакетную запись и выполняет операторы один за другим, поэтому он имеет ту же производительность, что и динамический SQL. Пакетная запись с буферизацией на самом деле имеет худшую производительность, чем не пакетная. Это связано с ценой синтаксического анализа огромного блока динамического SQL, который может варьироваться в разных конфигурациях. Если база данных удаленная или в медленной сети, я вижу, что это дает преимущество.

Параметризованный SQL с кэшированием операторов обеспечивает около 10% преимущества по сравнению с динамическим SQL и указывает, что для получения преимуществ от параметризации необходимо использовать кэширование операторов, в противном случае производительность может быть хуже динамического SQL. Грубо говоря, у параметризованного SQL есть и другие преимущества, так как он удаляет процессорную обработку с сервера, что может мало помочь в этом однопоточном случае, но может иметь огромное значение в многопоточном случае, когда база данных является узким местом.

Apache Derby Версия: 10.9.1.0 — (1344872)
Apache Derby Embedded JDBC Версия драйвера: 10.9.1.0 — (1344872)
(местный)

Вставить тест

Обновить тест

Результаты показывают, что параметризованная пакетная запись имеет значение для Дерби: на 7% быстрее для вставок и на 51% быстрее для обновлений. Эта разница в результатах не так велика, как в других базах данных, потому что моя база данных была локальной. Для сетевой базы данных это будет большая разница, но это показывает, что пакетная запись может принести пользу даже для локальных баз данных, так что это не просто оптимизация сети. Действительно интересные результаты от Derby — ужасная производительность динамических и некэшируемых операторов. Это показывает, что Derby имеет огромные затраты на синтаксический анализ, поэтому, если вы используете Derby, использование параметризованного SQL с кэшированием операторов действительно важно.

Версия DB2 / NT64: SQL09070
Драйвер IBM Data Server для JDBC и SQLJ Версия: 4.0.100

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

Версия Microsoft SQL Server: 10.50.1617
Драйвер JDBC для Microsoft SQL Server 2.0 Версия: 2.0.1803.100

Результаты были аналогичны PostgreSQL, демонстрируя значительную выгоду как параметризованной, так и динамической пакетной записи. Параметризованная пакетная запись показала лучший результат, а параметризованный SQL превзошел динамический SQL и не кэшировал операторы.

** ОБНОВИТЬ **

Мне было предложено также протестировать H2 и HSQL, так что вот результаты.

База данных: H2 Версия: 1.3.167 (2012-05-23)
Драйвер: H2 JDBC Версия драйвера: 1.3.167 (2012-05-23)
(местный)

Вставить тест

Результаты показывают, что H2 работает на 20% быстрее с параметризованной пакетной записью. H2 — это база данных в памяти (поддерживаемая постоянным файлом журнала), поэтому не ожидается, что она принесет пользу так же, как и сеть. Динамическая пакетная запись и динамический SQL хуже, чем параметризованный SQL. Интересно, что использование кэширования операторов с параметризованным SQL не имеет значения. Я предполагаю, что H2 всегда кэширует подготовленные операторы в своем соединении, поэтому пользователю не нужно делать свое собственное кэширование операторов.

База данных: HSQL Database Engine Версия: 1.8.1
Драйвер: HSQL Database Engine Версия драйвера: 1.8.1
(местный)

Вставить тест

Результаты показывают, что HSQL работает на 25% быстрее при параметризованной пакетной записи. HSQL — это база данных в памяти (поддерживаемая постоянным лог-файлом), поэтому не ожидается, что она принесет большую пользу, так как сеть не задействована. Динамическая пакетная запись и динамический SQL хуже, чем параметризованный SQL.