批處理寫入是最有效的數據庫優化之一。 批處理寫入受大多數現代數據庫和JDBC標準的一部分支持，并且受大多數JPA提供程序支持。

普通數據庫訪問包括在單獨的數據庫/網絡訪問中將每個DML（插入，更新，刪除）語句發送到數據庫。 每個數據庫訪問都有一定的開銷，并且數據庫必須獨立處理每個語句。 批處理寫入有兩種形式，動態的和參數化的。 參數化是最常見的方法，通常可以帶來最大的好處，因為動態可能存在解析問題。

要了解批處理編寫，您必須首先了解參數化的SQL。 SQL執行由兩部分組成，即解析和執行。 解析包括將字符串SQL表示形式轉換為數據庫表示形式。 執行包括在數據庫上執行已解析的SQL。 數據庫和JDBC支持綁定參數，因此SQL（數據）的參數不必嵌入到SQL中。 這避免了將數據轉換為文本的成本，并允許重復執行同一SQL語句并執行多次。 這允許單個解析和多個執行，也稱為“參數化SQL”。 大多數JDBC DataSource實現和JPA提供程序都支持參數化的SQL和語句緩存，這可以有效避免在運行的應用程序中進行解析。

動態SQL示例

INSERT INTO EMPLOYEE (ID, NAME) VALUES (34567, "Bob Smith")

參數化的SQL示例

INSERT INTO EMPLOYEE (ID, NAME) VALUES (?, ?)

參數化批處理編寫涉及執行單個DML語句，但是具有用于多個同質語句的一組綁定參數，而不是用于單個語句的綁定參數。 這有效地允許數據庫和網絡將大批同質的插入，更新或刪除作為單個操作而不是n個操作來處??理。 數據庫僅需要執行最少的工作，因為只有一條語句，因此最多只有一個解析。 它也與語句緩存兼容，因此根本不需要進行語句解析。 限制是所有語句的SQL必須相同。 因此，說插入1,000個Orders確實非常有用，因為每個Order的插入SQL都是相同的，只是bind參數不同。 但這對于插入1個訂單或插入1個訂單，1個OrderLine和1個客戶沒有幫助。 同樣，所有語句必須是同一數據庫事務的一部分。

動態批處理編寫包括將一堆異構動態SQL語句鏈接到一個塊中，然后通過單個數據庫/網絡訪問將整個塊發送到數據庫。 這是有利的，因為只有一個網絡訪問權限，因此，如果數據庫是遠程的或通過慢速的網絡訪問，則可能會有很大的不同。 缺點是不允許參數綁定，并且數據庫必須在接收到此龐大的SQL塊時對其進行解析。 在某些情況下，解析成本可能超過網絡收益。 另外，動態SQL與語句緩存不兼容，因為每個SQL都不相同。

JDBC通過其Statement和PrepareStatement批處理API（從JDBC 2.0開始，很早以前就是JDK 1.2）標準化了批處理寫入。 JDBC批處理API需要不同的JDBC代碼，因此，如果您使用的是原始JDBC，則需要重寫代碼以在批處理和非批處理API之間切換。 現在，大多數JDBC驅動程序都支持這些API，但是有些驅動程序實際上并不模擬DML批量發送DML到數據庫。 那么，如何知道您是否真的正在批量編寫？ 唯一真正的方法是對其進行測試，并衡量性能差異。

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"使用早于JDBC標準API的Oracle數據庫JDBC API，現在已過時。 在EclipseLink 2.5之前，此選項允許在使用開放式鎖定時進行批處理寫入，但是現在常規的"JDBC"選項支持開放式鎖定。

EclipseLink 2.5支持在所有（兼容）數據庫平臺上進行樂觀鎖定的批處理寫入，而以前僅在選定的數據庫平臺上才支持。 EclipseLink 2.5還提供了一個"eclipselink.jdbc.batch-writing"查詢提示，以禁止無法寫入的本機查詢（例如DDL或某些數據庫平臺上的存儲過程）的批量寫入。

EclipseLink通過"eclipselink.jdbc.bind-parameters"和"eclipselink.jdbc.cache-statements"持久單元屬性來支持參數化SQL。 但是，通常不需要設置這些參數，因為參數綁定是默認設置，因此您只需將屬性設置為禁用綁定即可。 默認情況下，語句緩存未啟用，如果使用EclipseLink的連接池，則僅與EclipseLink相關；如果使用的是JDBC或Java EE DataSource，則必須在DataSource配置中配置語句緩存。

在EclipseLink中啟用批量寫入時，默認情況下它是參數化的批量寫入。 要啟用動態批處理寫入，必須禁用參數綁定。 這與啟用緩沖批寫入相同。

支持批處理寫入并不是很難，大多數JPA提供程序都支持這一點，對SQL進行排序以使其可以進行批處理是困難的部分。 在提交或刷新操作期間，EclipseLink會自動按表對SQL進行分組，以確?？梢耘幚硗惖腟QL語句（同時仍保持引用完整性約束并避免死鎖）。 大多數JPA提供程序都不這樣做，因此即使它們支持批處理寫入，SQL的很多時間也無法從批處理中受益。

要在EclipseLink中啟用批處理寫入，請將以下內容添加到持久性單元屬性；

"eclipselink.jdbc.batch-writing"="JDBC"

您還可以使用"eclipselink.jdbc.batch-writing.size"持久性單元屬性來配置批處理大小。 默認大小為100。

"eclipselink.jdbc.batch-writing.size"="1000"

批處理非常依賴數據庫，并且依賴JDBC驅動程序。 因此，我對與哪些數據庫，它使用的驅動程序以及好處有興趣。 我進行了兩次測試，一個進行了50次插入操作，一個進行了100次更新操作（使用樂觀鎖定）。 我嘗試了所有批處理寫入選項，以及不使用任何批處理。

請注意，這不是數據庫基準，我不是在相互比較數據庫，而只是對自己進行比較 。

每個數據庫都在不同的硬件上運行，有些是本地的，有些是跨網絡的，因此不要將一個數據庫與另一個數據庫進行比較。 感興趣的數據是使批寫入相對于不使用批寫入所帶來的百分比收益。 對于插入測試，我還測量了使用參數化SQL與動態SQL以及不使用語句緩存的參數化SQL之間的差異。 結果是在10秒內處理的事務數（運行5次，并平均），因此，較大的數目是更好的結果。

驅動程序：MySQL-AB JDBC驅動程序版本：mysql-connector-java-5.1.22

插入測試

選項 平均結果 與非批次的百分比差異

參數化SQL，無批處理	483	0％
動態SQL，無批次	499	3％
參數化的SQL，無語句緩存	478	-1％
動態SQL，批處理	499	3％
參數化SQL，批處理	509	5％

更新測試

選項 平均結果 與非批次的百分比差異

參數化SQL	245	0％
動態SQL，批處理	244	0％
參數化SQL，批處理	248	1％

因此，結果似乎表明批處理寫入沒有任何影響（5％在方差之內）。 這的真正含義是，MySQL JDBC驅動程序實際上并不使用批處理，它只是模擬JDBC批處理API，并在其下逐個執行語句。

盡管MySQL確實具有批處理支持，但它只需要不同的SQL。 MySQL JDBC驅動程序確實支持此功能，但是需要設置rewriteBatchedStatements=true JDBC連接屬性。 可以通過修改您的連接URL輕松地進行設置，例如；

jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true

MySQL：rewriteBatchedStatements = true

插入測試

選項 平均結果 與非批次的百分比差異

參數化SQL，無批處理	504	0％
動態SQL，無批次	508	0％
參數化的SQL，無語句緩存	483	-4％
動態SQL，批處理	1292	156％
參數化SQL，批處理	2181	332％

更新測試

選項 平均結果 與非批次的百分比差異

參數化SQL	250	0％
動態SQL，批處理	669	167％
參數化SQL，批處理	699	179％

因此，如果配置正確（似乎JDBC驅動程序默認不執行此操作，我不知道），那么批處理寫入確實會在MySQL中產生很大的不同。 參數化批處理寫入效果最佳，插入速度快332％，更新速度快179％。 動態批處理寫入效果也很好。 有趣的是，MySQL上的動態SQL和參數化SQL之間似乎沒有什么區別（我猜想MySQL解析的速度確實更快，或者對預準備語句的優化很少）。

PostgreSQL 8.4 JDBC4

插入測試

選項 平均結果 與非批次的百分比差異

參數化SQL，無批處理	479	0％
動態SQL，無批次	418	-12％
參數化的SQL，無語句緩存	428	-10％
動態SQL，緩沖	1127	135％
動態SQL，批處理	1127	135％
參數化SQL，批處理	2037	325％

更新測試

選項 平均結果 與非批次的百分比差異

參數化SQL	233	0％
動態SQL，批處理	395	69％
參數化SQL，批處理	707	203％

結果表明，批寫在PostgreSQL上有很大的不同。 參數化批處理寫入性能最佳，插入速度快325％，更新速度快203％。 動態批處理寫入效果也很好。 對于PostgreSQL，我還評估了EclipseLink的緩沖批處理寫入的性能，該性能與動態JDBC批處理寫入的性能相同，因此我假設驅動程序在做相同的事情。 參數化的SQL優于動態SQL約10％，但不帶語句緩存的參數化SQL與動態SQL相似。

Oracle JDBC驅動程序版本：11.2.0.2.0

插入測試

選項 平均結果 與非批次的百分比差異

參數化SQL，無批處理	548	0％
動態SQL，無批次	494	-9％
參數化的SQL，無語句緩存	452	-17％
動態SQL，緩沖	383	-30％
動態SQL，批處理	489	-10％
參數化SQL，批處理	3308	503％

更新測試

選項 平均結果 與非批次的百分比差異

參數化SQL	282	0％
動態SQL，批處理	258	-8％
參數化SQL，批處理	1672	492％

結果表明，參數化批處理寫入對Oracle產生了很大的影響，插入速度快503％，更新速度快492％。 動態批處理寫入沒有任何好處，這是因為Oracle的JDBC驅動程序僅模擬動態批處理并逐個執行語句，因此它具有與動態SQL相同的性能。 緩沖批寫入實際上比根本不批處理具有更差的性能。 這是因為巨大的動態SQL塊的解析成本，這在不同的配置中可能會有所不同，如果數據庫是遠程的或跨慢速的網絡，則我會看到這樣做的好處。

帶有語句緩存的參數化SQL比動態SQL提供約10％的收益，并指出，要從參數化中受益，您需要使用語句緩存，否則性能可能會比動態SQL差。 粗略地講，參數化SQL還有其他好處，因為它從服務器中刪除了CPU處理，這在單線程情況下可能無濟于事，但在數據庫是瓶頸的多線程情況下，可能會產生很大的不同。

（本地）

插入測試

選項 平均結果 與非批次的百分比差異

參數化SQL，無批處理	3027	0％
動態SQL，無批次	24	-99％
參數化的SQL，無語句緩存	50	-98％
動態SQL，批處理	24	-99％
參數化SQL，批處理	3252	7％

更新測試

選項 平均結果 與非批次的百分比差異

參數化SQL	1437	0％
動態SQL，批處理	6	-99％
參數化SQL，批處理	2172	51％

結果表明，參數化批處理寫入對Derby有所不同，插入速度快7％，更新速度快51％。 由于我的數據庫是本地數據庫，因此結果差異不如其他數據庫那么大。 對于網絡數據庫，這將是一個更大的差異，但這確實表明，即使對于本地數據庫，批處理寫入也可以帶來好處，因此，這不僅僅是網絡優化。 Derby真正有趣的結果是動態和非緩存語句的可怕性能。 這表明Derby具有巨大的解析成本，因此，如果您使用的是Derby，那么將帶參數的SQL與語句緩存一起使用非常重要。

用于JDBC和SQLJ的IBM數據服務器驅動程序版本：4.0.100

結果基本上與Oracle類似，因為參數化的批處理編寫具有很大的性能優勢。 動態批處理寫入的性能較差，因此無法使用參數化SQL進行批處理，而動態SQL和未使用語句緩存的參數化SQL會導致性能降低。

Microsoft SQL Server JDBC驅動程序2.0版本：2.0.1803.100

結果類似于PostgreSQL，顯示參數化和動態批處理編寫均提供了顯著的好處。 參數化批處理編寫性能最好，參數化SQL優于動態SQL，并且沒有語句緩存。

**更新**

有人要求我也測試H2和HSQL，所以這里是結果。

（本地）

插入測試

選項 平均結果 與非批次的百分比差異

參數化SQL，無批處理	4757	0％
動態SQL，無批次	3210	-32％
參數化的SQL，無語句緩存	4757	0％
動態SQL，緩沖	1935年	-59％
動態SQL，批處理	3293	-30％
參數化SQL，批處理	5753	20％

結果表明，通過參數化批處理寫入，H2的執行速度提高了20％。 H2是一個內存數據庫（由持久日志文件支持），因此預期不會受益于不涉及任何網絡的情況。 動態批處理編寫和動態SQL執行的參數化SQL更差。 有趣的是，將參數緩存與參數化SQL一起使用不會產生任何區別。 我的假設是H2始終在其連接中緩存準備好的語句，因此用戶不需要自己進行語句緩存。

（本地）

插入測試

選項 平均結果 與非批次的百分比差異

參數化SQL，無批處理	7319	0％
動態SQL，無批次	5054	-30％
參數化的SQL，無語句緩存	6776	-7％
動態SQL，批處理	5500	-24％
參數化SQL，批處理	9176	25％

結果表明，通過參數化批處理編寫，HSQL的執行速度提高了25％。 HSQL是一個內存數據庫（由持久日志文件支持），因此期望它不會像沒有網絡一樣受益。 動態批處理編寫和動態SQL執行的參數化SQL更差。

參考： 批處理以及動態和參數化SQL，數據庫的性能如何？ Java持久性性能博客中來自我們JCG合作伙伴 James Sutherland的內容。

翻譯自: https://www.javacodegeeks.com/2013/09/batch-writing-and-dynamic-vs-parametrized-sql-how-well-does-your-database-perform.html

總結

以上是生活随笔為你收集整理的批处理写入以及动态与参数化SQL，数据库的性能如何？的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。