影院网站建设,wordpress看后台浏览量,网站开发语言php,网站设计的基本步骤和方法sql 动态写入数据库字段最有效的数据库优化之一是批处理写入。 批处理写入受大多数现代数据库和JDBC标准的一部分支持#xff0c;并且受大多数JPA提供程序支持。 普通数据库访问包括在单独的数据库/网络访问中将每个DML#xff08;插入#xff0c;更新#xff0c;删除并且受大多数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-writingJDBC 您还可以使用eclipselink.jdbc.batch-writing.size持久性单元属性来配置批处理大小。 默认大小为100。 eclipselink.jdbc.batch-writing.size1000 批处理非常依赖数据库并且依赖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驱动程序确实支持此功能但是需要设置rewriteBatchedStatementstrue JDBC连接属性。 可以通过修改您的连接URL轻松地进行设置例如 jdbc:mysql://localhost:3306/test?rewriteBatchedStatementstrueMySQLrewriteBatchedStatements 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.htmlsql 动态写入数据库字段
