sql 动态写入数据库字段_批处理写入，动态SQL和参数化SQL，数据库的性能如何？...

sql 动态写入数据库字段

最有效的数据库优化之一是批处理写入。 批处理写入受大多数现代数据库和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批量发送给数据库，它们只是模拟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"使用早于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进行分组，以确保可以批处理同类SQL语句（同时仍保持引用完整性约束并避免死锁）。 大多数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和参数化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 动态写入数据库字段