写点什么

聊聊 JDBC 的 executeBatch || 对比下不同数据库对 JDBC batch 的实现细节

  • 2022 年 1 月 30 日
  • 本文字数:6285 字

    阅读完需:约 21 分钟

大家好,我是明哥!

上篇博文,“对比下 datax 的 OceanBase/MYSQL 不同数据同步方案的效率差异 || 聊聊参数 rewriteBatchedStatements” 发表后,有小伙伴问到不同数据库对 JDBC 批量更新的实现细节,所以通过本片博文,我们系统看下 jdbc batch 相关知识。

1. JDBC batch 为什么能提高性能?

通过查看源码可知,JDBC1.2 引入了 Batch 功能,涉及的 API 主要有以下四个:

  • java.sql.Statement#addBatch

  • java.sql.PreparedStatement#addBatch

  • java.sql.Statement#executeBatch

  • java.sql.Statement#clearBatch






JDBC 引入上述 batch 功能的主要目的,是加快对客户端 SQL 的执行和响应速度,并进而提高数据库整体并发度,而 jdbc batch 能够提高对客户端 SQL 的执行和响应速度,其主要原理有:

  • 减少了 JDBC 客户端和数据库服务器之间网络传输的开销:使用 batch 功能前,每提交一个 SQL,都需要一次网络 IO 开销,且提交后需要等待服务端返回结果后,才能提交下一个 SQL;而使用 batch 功能后,客户端的多个 SQL 是一起提交给服务器的,只涉及到一次网络 IO 开销(single database round trip),其示意图如下:



  • 当 batch 底层使用的是静态 SQL 并参数化执行时(JAVA 中一般是使用类 java.sql.PreparedStatement 来参数化执行静态 SQL),数据库服务器可以只做一次解析:利用对参数化机制的支持,数据库服务器仅需要对 PreparedStatement 做一次解析(sql parse),即可传入不同参数执行该 batch 中所有的 SQL;

  • 网上有个帖子,详细对比了不同场景下,不同数据库的插入和更新性能的差异,可以看出,ORACLE/PG/MYSQL 使用 batch 功能后,性能都有了 3-5 被的提高:




2. JDBC batch 的使用场景和限制有哪些?

  • batch 功能对 statement 和 PreparedStatement 都有效,但为了避免 SQL 注入的风险,不推荐使用动态 SQL,而是推荐使用静态 SQL 和绑定变量(也就是使用 PreparedStatement 和 stored procedures);

  • 从上述 JDBC 的 API 源码可以看出,batch 功能对所有 SQL 都有效, 包括 SELECT/INSERT/UPDATE/DELETE,但由于使用 batch 功能后,返回值是 int[] 数组,不太方便获取 batch 底层每个 sql 的执行结果,所以大家一般不会对 SELECT 语句使用 batch 功能 (毕竟 select 查询的目的是获得每个 select 语句的结果 resultSet),而只会在大量 INSERT/UPDATE/DELETE 的场景下,尤其是大批量插入的场景下,使用 batch 功能,所以大家提到 batch 时,常说“批量更新”;(数据仓库数据湖等涉及到数据集成和 ETL 的场景,经常会使用到批量插入);

  • 另外需要注意的是,使用 batch 功能并不代表所有的 SQL 都在一个事务里:在 autocommit 模式下,after each statement you have created, the database will ensure that the result persists correctly before moving on to the next statement,if the nth sentence of a batch raises a constraint exception, all previously inserted rows will not be rollbacked;

3. 不同数据库对 JDBC batch 的实现有何异同?

  • 由于 batch 功能是通过 JDBC API 定义的,是 JDBC 规范的一部分,所以所有实现了 JDBC 接口的数据库驱动,都需要实现这些接口,所以 mysql/oracle/pg/db2/sqlserver 等所有提供了 JDBC 接口的数据库,原则上都支持 jdbc batch 功能;

  • 但不同数据库对这些接口的具体实现是不同的,所以其最终效果和使用细节会有些差异,甚至相同数据库驱动的不同版本,也可能会有所差异,所以用户在使用前,需要查看下对应数据库的说明,不能想当然地认为,某个数据驱动的参数,也同样使用于其它数据库驱动;

  • 这里重点指出下,jdbc batch 相关参数,mysql 有参数 rewriteBatchedStatements,postgresql 有参数 reWriteBatchedInserts;

4. 详解 mysql 的参数 rewriteBatchedStatements

  • MySQL JDBC 驱动虽然实现了各个 JDBC batch api,但默认情况下,其对 batch 功能的支持仅仅是语法层面的支持,并没有真正通过 batch 功能提升性能:即使用户代码中编写了 executeBatch(), mysql 仍会把用户期望批量执⾏的⼀组 sql 语句拆散,逐条发给 MySQL 数据库,所以 mysql 的批量插入实际上是单条插入,性能较低;(By default, the MySQL Jdbc driver ignores the executeBatch () statement, disassembles a set of SQL statements that we expect to execute in batches, and sends them to the MySQL database one by one, which directly causes lower performance.)

  • 比如以下批量插入测试代码,虽然代码中使用了 batch api ps.executeBatch(),但通过 wireshark 在代码执行时抓包查看,可以发现底是将批量执⾏的⼀组 sql 语句拆散并逐条发给数据库服务器的,也就是说 mysql 的批量插入实际上是单条插入:



  • 测试代码如下:

    /**     * mysql batch insert, without rewriteBatchedStatements=true in the url     */    public static void testMysqlBatch1(){        try{            Class<?> mysqlDriverClass = Class.forName("com.mysql.cj.jdbc.Driver");            Connection con=DriverManager.getConnection("jdbc:mysql://10.23.2.215:33061/hs_cic","root","hundsun");            String sql = "insert into test_liming (AIR_CODE, AIR_NAME) values (?,?)";            PreparedStatement ps = con.prepareStatement(sql);            for (int i = 0; i < 10; i++) {                ps.setString(1,Integer.toString(i));                ps.setString(2,Integer.toString(i));                ps.addBatch();            }            int[] results = ps.executeBatch();            for (int i = 0; i < results.length; i++) {                System.out.println("results:" + results[i]);            }            con.close();        }catch(Exception e){ System.out.println(e);}    }
复制代码
  • Mysql 提供了其特有的 JDBC 连接参数 rewriteBatchedStatements,当把该参数置为 true 时, mysql jdbc 驱动会在客户端重写用户提交的原始 SQL,并将重写后的 SQL “send the batched statements in a single request”;

  • 比如以下批量插入测试代码,代码中使用了 batch api ps.executeBatch(),且 url 中指定了 rewriteBatchedStatements=true, 通过 wireshark 在代码执行时抓包查看,可以发现底是将批量执⾏的⼀组 sql Insert 语句,改写为一条 batched 语句 insert into tableA (colA,colB) values (colA-value1,colB-value1),(colA-value2,colB-value2),(colA-value3,colB-value3), 并通过一次请求发送给数据库服务器的,也就是说此时 mysql 使用了批量插入功能;


  • 测试代码如下:

    /**     * mysql batch insert, with rewriteBatchedStatements=true in the url     */    public static void testMysqlBatch2(){        try{            Class<?> mysqlDriverClass = Class.forName("com.mysql.cj.jdbc.Driver");            Connection con=DriverManager.getConnection("jdbc:mysql://10.23.2.215:33061/hs_cic?rewriteBatchedStatements=true","root","hundsun");            String sql = "insert into test_liming (AIR_CODE, AIR_NAME) values (?,?)";            PreparedStatement ps = con.prepareStatement(sql);            for (int i = 0; i < 10; i++) {                ps.setString(1,Integer.toString(i));                ps.setString(2,Integer.toString(i));                ps.addBatch();            }            int[] results = ps.executeBatch();            for (int i = 0; i < results.length; i++) {                System.out.println("results:" + results[i]);            }            con.close();        }catch(Exception e){ System.out.println(e);}    }
复制代码
  • 经笔者测试总结,对批量插入的 ps.executeBatch(),mysql jdbc 驱动,会改写批量中的一组 sql 为一条 “multi-values” 语句,并一次性提交给数据库服务器:

    batchInsert(10 records) 会被改写为 "insert into t (…) values (…), (…), (…)” 并一次性提交;

    如果不能被改写为 "multi-values", 则会改写为多个;分割的 sql 语句并一次性提交:语句 “INSERT INTO TABLE(col1) VALUES (?) ON DUPLICATE KEY UPDATE col2=?” 与变量 [1,2] 和 [2,3],会被改写为 “INSERT INTO TABLE(col1) VALUES (1) ON DUPLICATE KEY UPDATE col2=2;INSERT INTO TABLE(col1) VALUES (3) ON DUPLICATE KEY UPDATE col2=4” 并一次性提交;

  • 经笔者测试总结,对批量删除和批量更新的 ps.executeBatch(),mysql jdbc 驱动,会改写 SQL 语句,改写为多条;分割的 SQL 语句,并一次性提交给数据库服务器:

    batchDelete(10 records) 会被改写为 "delete from t where id = 1; delete from t where id = 2; delete from t where id = 3;…."并一次性提交;

    batchUpdate(10 records) 会被改写为 “update t set… where id = 1; update t set… where id = 2; update t set… where id = 3…” 并一次性提交;

  • 有的小伙伴,可能会有疑问,为什么 MYSQL 不像别的数据库,比如 oracle 和 postgresql 那样,默认支持 batch 功能呢?为什么必须手动指定参数 rewriteBatchedStatements=true,才会支持通过改写 SQL 支持 batch 功能呢?笔者也不是很清楚,不过大概猜测如下:

    改写后的 SQL 语句,很多时候并不只是简单地通过符号”;”来分割和追加原始的多个 SQL,这有时候并不是我们期望的;

    并不是所有的 SQL 语句都能被很好地改写;

    当部分语句的执行可能会出错时,错误处理不太方便,查看 update counts 也不太方便;

    可能还有其它笔者不知道的原因。

最后总结 rewriteBatchedStatements 如下:

  • MySQL JDBC driver defines the rewriteBatchedStatements connection property, so that statements get rewritten into a single String buffer;

  • Without setting this property, the MySQL driver simply executes each DML statement separately, therefore defeating the purpose of batching;

  • property rewriteBatchedStatements may be specified in either the connection URL or an additional Properties object parameter to DriverManager.getConnection;

  • In order to fetch the auto-generated row keys, the batch must contain insert statements only;

  • For PreparedStatement, this property rewrites the batched insert statements into a multi-value insert;

  • the driver is not able to use server-side prepared statements when enabling rewriting;

5. 抓个包看看,批量插入时 oracle 底层的 SQL 语句

作为跟 Mysql 的对比,我们测试下 oracle 中批量插入时,抓包获取的 SQL 语句。

  • oracle 批量插入时,抓包内容如下,可以看到,底层使用了静态 SQL 和绑定变量:


  • oracle 批量插入,底层使用了 ps.executeBatch,测试代码如下:

 public static void testOracleBatch(){        try{            Class<?> driverManagerClass = Class.forName("java.sql.DriverManager");            Class<?> driverClass = Class.forName("java.sql.Driver");            Class<?> oracleDriverClass = Class.forName("oracle.jdbc.driver.OracleDriver");          Connection con=DriverManager.getConnection("jdbc:oracle:thin:@//10.20.155.75:1521/pdb19","hs_cic_cda","hundsun");            String sql = "insert into test_liming (AIR_CODE, AIR_NAME) values (?,?)";            PreparedStatement ps = con.prepareStatement(sql);            for (int i = 0; i < 10; i++) {                ps.setString(1,Integer.toString(i));                ps.setString(2,Integer.toString(i));                ps.addBatch();            }            int[] results = ps.executeBatch();            for (int i = 0; i < results.length; i++) {                System.out.println("results:" + results[i]);            }            con.close();        }catch(Exception e){ System.out.println(e);}    }
复制代码

6. 介绍下 postgreSql 的参数 reWriteBatchedInserts

  • postgreSql 跟 oracle 一样,默认都是支持 jdbc batch 功能的(这点跟 MYSQL 不同!);

  • 但为了进一步优化性能,pg 在 9.4.1208 版本后,又提供了参数 reWriteBatchedInserts,该参数默认值为 FALSE;


  • 当参数 reWriteBatchedInserts 为 true 时,pgjdbc 会将批量的 “insert into ... values(?, ?)” 改写为 “insert into ... values(?, ?), (?, ?)” ;

  • 这样的改写的好处是:减少了每个 statement 的开销;

  • 这样的改写的坏处是:如果某个语句执行失败的话, 整个 batch 都会失败; The default value is false.

  • 比如某次线上案列,查看 pg 日志,开启 reWriteBatchedInserts 前后日志如下:

未开启参数reWriteBatchedInserts的日志:LOG:  execute S_2: insert into post (title, id) values ($1, $2)DETAIL:  parameters: $1 = 'Post no. 1', $2 = '1'LOG:  execute S_2: insert into post (title, id) values ($1, $2)DETAIL:  parameters: $1 = 'Post no. 2', $2 = '2'开启参数reWriteBatchedInserts的日志:LOG:  execute <unnamed>: insert into post (title, id) values ($1, $2),($3, $4),($5, $6),($7, $8),($9, $10),($11, $12),($13, $14),($15, $16)DETAIL:  parameters: $1 = 'Post no. 1', $2 = '1', $3 = 'Post no. 2', $4 = '2', $5 = 'Post no. 3', $6 = '3', $7 = 'Post no. 4', $8 = '4', $9 = 'Post no. 5', $10 = '5', $11 = 'Post no. 6', $12 = '6', $13 = 'Post no. 7', $14 = '7', $15 = 'Post no. 8', $16 = '8'
复制代码

参考链接:

  • https://leanpub.com/high-performance-java-persistence/read

  • http://java-persistence-performance.blogspot.com/2013/05/batch-writing-and-dynamic-vs.html

  • https://www.alibabacloud.com/blog/how-to-write-into-a-database-using-rewritebatchedinserts-parameter_597796

微信公众号主要用来信息的传播和分享,同名知识星球主要用来知识的沉淀和积累!欢迎大家扫码加入免费知识星球 “明哥的 IT 随笔”,这是一个围绕泛大数据生态的技术交流社区,可以探讨任何 IT 技术话题和工作上的问题,一起学习共同进步!

image

发布于: 刚刚阅读数: 2
用户头像

Keep Striving! 2018.04.25 加入

明哥,十四年 IT经验,六年大数据经验; 做过大数据集群的搭建运维,大数据应用系统的开发优化,也做过大数据平台的技术选型以及架构咨询; 目前聚焦于泛大数据生态,包括数据仓库/数据湖,云计算和人工智能。

评论

发布
暂无评论
聊聊 JDBC 的 executeBatch || 对比下不同数据库对 JDBC batch 的实现细节