MySql的JDBC驱动不支持批量操作(已结)

再继续深入一下，需要借助服务器工具，监视服务器的sql 执行。像sql server的事件查看器一样，如果有这样的工具，进行监视，就能更清楚地了解执行过程。

还可以在 mysql 服务器端直接写存储过程批量执行，同时查看执行的计划，与java客户端访问比较看有什么差异。从而能更好的掌握java mysql jdbc.

另外，那个mysql jdbc connection parameters 确实很重要

如果数据都是well-formed，即不存在大字段或二进制数据类数据。

感觉可以在jdbc驱动实现原理类似：
传输批量文本数据+Load Data的preparedstatement

应该能大幅度的提高性能。

mysql 虽然用得不多，公司一直用的mssql.

楼主的测试结果让人非常失望...

但mysql名气这么大，不太相信在这样的问题上会出现与oracle这么大的差距。

直觉告诉我，应该是一些简单的设置问题，事实上最后得到的结果也是如此的。

带着解决这个疑惑的想法，依据大家之前得到的一些结果，信息，开始测试

工具：
eclipse-3.6, mysql-5.1.48, mysql-jdbc-driver 5.1.11, mysql workbench

前面说过了，我直觉认为代码不会有问题，所以先着手改善mysql 的服务器配置，innodb的设置。
改了几个参数，都没有什么效果。加大了日志缓存，只是提高到7000多毫秒。最后甚至很多歪门邪道的设置都大胆用了，一度让mysql 无法启动。。。最终都收效甚微，这个步骤大概试了将近一个小时。

这条路看来是走不通了。。得寻找别的方法


冷静下来想想，其实从代码中应该是可以发现些端倪
楼主的非batch代码中，每次调用 execute() 其实是会通过网络发送一条语句到服务器端的，是不会在客户端排队攒着的。
因为这个方法必须返回一个结果。它必然跟服务器发生了一次交互。

而在batch处理的代码中，其addBatch 就是无返回值，它提供了一个可能就是在客户端将语句缓存排队攒着，最后executeBatch时才发送到服务器端。

用代码可以证明，在batch处理方法的代码中，在 executeBatch, 及 commit 方法执行前，分别安插两条打印时间语句：

            System.out.println("before executeBatch. "+ (System.currentTimeMillis()-a)+" ms");
            prest.executeBatch();
            System.out.println("before commit. "+ (System.currentTimeMillis()-a)+" ms");
            conn.commit();        

在我机器上的结果是,

before executeBatch. 279 ms
before commit. 7922 ms
MySql批量插入10万条记录用时7923 ms

说明客户端在攒语句时，相当的快，279毫秒就完成了，但在 executeBatch 这个方法的调用过程中，花费了 7920  减 去 279 的毫秒数。大部分都耗在这里了。 最后提交事务非常快，1毫秒而已

想想看，前边说过，非batch和batch的处理几乎是一样的时间。

可不可以先假设 batch 的方式与非batch一样，每一条insrt语句事实上均是单独发往服务器的呢？

浏览下源代码吧。

好几位兄弟都描述了源代码，直接从那几个类入手吧，事实上关键的类是这个 com.mysql.jdbc.PreparedStatement

先看了其中的 addBatch 方法，没有任何问题，只是将语句添加进入一个 List 中保存。

那么 executeBatch 呢？

再贴一下吧， 关键看其中的这部分，顺带说一下， 这个mysql-jdbcdriver的源代码是 5.1.13的

			try {
				clearWarnings();

				if (!this.batchHasPlainStatements
						&& this.connection.getRewriteBatchedStatements()) {
					
					
					if (canRewriteAsMultiValueInsertAtSqlLevel()) {
						return executeBatchedInserts(batchTimeout); //执行路径之一
					}
					
					if (this.connection.versionMeetsMinimum(4, 1, 0) 
							&& !this.batchHasPlainStatements
							&& this.batchedArgs != null 
							&& this.batchedArgs.size() > 3 /* cost of option setting rt-wise */) {
						return executePreparedBatchAsMultiStatement(batchTimeout); //执行路径之二
					}
				}

				return executeBatchSerially(batchTimeout); //执行路径之三
			} finally {
				clearBatch();
			}

其实最终，executeBatch 的执行路径有三种可能。代码中我已标出来

不小心按了提交了，继续编辑此回复吧。


代码不算太复杂，但是有一个参数能帮助我们更快的确定mysql的batch工作机制，那就是
mysql jdbc driver 的connection url， 其中有一个参数是: rewriteBatchedStatements

完整的参数参考看这里：http://ftp.ntu.edu.tw/ftp/pub/MySQL/doc/refman/5.1/en/connector-j-reference-configuration-properties.html

rewriteBatchedStatements 参数默认为false, 需要手工设置为true，设置方式大概像这样：

    String connectionUrl="jdbc:mysql://192.168.1.100:3306/test?rewriteBatchedStatements=true";  

默认时候，rewriteBatchedStatements=false时，执行路径会跳到 executeBatchSerially，此方法内部将语句一条条发送，与非batch处理简直一样，所以慢，就在这里了。

当设为 true时，会执行executeBatchedInserts方法，事实上mysql支持这样的插入语句

insert into t_user(id,uname) values(1, '1'), (2,'2'), (3, '3') ....

所以，当rewriteBatchedStatements=true时， 楼主的例子会被编译为以上形式，当然values里全是?, mysql 客户端会对这些值添加参数. 这样的方式当然就快很多了。

其实到现在还不太了解 batch 处理时，执行计划这个概念，不过我猜 mysql 可能并没有缓存执行计划。而只是将这些语句组合起来了。

所以如果是这样，他的机制与oracle可能是有所不同的，还不是达到最高效的机制，也许这就是开源与商业的区别吧。

我们如果想更深入了解，只能借助于一些服务器端监视工具,sql分析工具了。




写贴子过程断断续续给打扰了，本来还有一些可以写更详细的，就留给大家自己去探索了，包括，如果调用addBatch(String sql)后，则仍会按照 executeBatchSerially 方式执行，包括何时执行 executePreparedBatchAsMultiStatement，都可以继续深入了解。

后记，当使用 update 时，会执行 executePreparedBatchAsMultiStatement，但是如果攒的语句太多，会导致 mysql 崩溃. 我的测试中10000条update不会有事，20000时，mysql 就崩掉了。

看了mysql jdbc源代码，情况很复杂~呵呵。但是可以确定的是，mysql 是有特殊的方式优化整个batch insert 结果的。中午一直没吃饭，先去吃饭，回头发贴出来。

先说结果:

按楼主的代码原封不动，在我的机器上执行情况是 8000 毫秒左右，
优化后，非batch 方式保持不变，batch 的方式 1000毫秒

mysql 虽然用得不多，公司一直用的mssql.

楼主的测试结果让人非常失望...

但mysql名气这么大，不太相信在这样的问题上会出现与oracle这么大的差距。

直觉告诉我，应该是一些简单的设置问题，事实上最后得到的结果也是如此的。

带着解决这个疑惑的想法，依据大家之前得到的一些结果，信息，开始测试

工具：
eclipse-3.6, mysql-5.1.48, mysql-jdbc-driver 5.1.11, mysql workbench

前面说过了，我直觉认为代码不会有问题，所以先着手改善mysql 的服务器配置，innodb的设置。
改了几个参数，都没有什么效果。加大了日志缓存，只是提高到7000多毫秒。最后甚至很多歪门邪道的设置都大胆用了，一度让mysql 无法启动。。。最终都收效甚微，这个步骤大概试了将近一个小时。

这条路看来是走不通了。。得寻找别的方法


冷静下来想想，其实从代码中应该是可以发现些端倪
楼主的非batch代码中，每次调用 execute() 其实是会通过网络发送一条语句到服务器端的，是不会在客户端排队攒着的。
因为这个方法必须返回一个结果。它必然跟服务器发生了一次交互。

而在batch处理的代码中，其addBatch 就是无返回值，它提供了一个可能就是在客户端将语句缓存排队攒着，最后executeBatch时才发送到服务器端。

用代码可以证明，在batch处理方法的代码中，在 executeBatch, 及 commit 方法执行前，分别安插两条打印时间语句：

            System.out.println("before executeBatch. "+ (System.currentTimeMillis()-a)+" ms");
            prest.executeBatch();
            System.out.println("before commit. "+ (System.currentTimeMillis()-a)+" ms");
            conn.commit();        

在我机器上的结果是,

before executeBatch. 279 ms
before commit. 7922 ms
MySql批量插入10万条记录用时7923 ms

说明客户端在攒语句时，相当的快，279毫秒就完成了，但在 executeBatch 这个方法的调用过程中，花费了 7920  减 去 279 的毫秒数。大部分都耗在这里了。 最后提交事务非常快，1毫秒而已

想想看，前边说过，非batch和batch的处理几乎是一样的时间。

可不可以先假设 batch 的方式与非batch一样，每一条insrt语句事实上均是单独发往服务器的呢？

浏览下源代码吧。

好几位兄弟都描述了源代码，直接从那几个类入手吧，事实上关键的类是这个 com.mysql.jdbc.PreparedStatement

先看了其中的 addBatch 方法，没有任何问题，只是将语句添加进入一个 List 中保存。

那么 executeBatch 呢？

再贴一下吧， 关键看其中的这部分，顺带说一下， 这个mysql-jdbcdriver的源代码是 5.1.13的

			try {
				clearWarnings();

				if (!this.batchHasPlainStatements
						&& this.connection.getRewriteBatchedStatements()) {
					
					
					if (canRewriteAsMultiValueInsertAtSqlLevel()) {
						return executeBatchedInserts(batchTimeout); //执行路径之一
					}
					
					if (this.connection.versionMeetsMinimum(4, 1, 0) 
							&& !this.batchHasPlainStatements
							&& this.batchedArgs != null 
							&& this.batchedArgs.size() > 3 /* cost of option setting rt-wise */) {
						return executePreparedBatchAsMultiStatement(batchTimeout); //执行路径之二
					}
				}

				return executeBatchSerially(batchTimeout); //执行路径之三
			} finally {
				clearBatch();
			}

其实最终，executeBatch 的执行路径有三种可能。代码中我已标出来

不小心按了提交了，继续编辑此回复吧。


代码不算太复杂，但是有一个参数能帮助我们更快的确定mysql的batch工作机制，那就是
mysql jdbc driver 的connection url， 其中有一个参数是: rewriteBatchedStatements

完整的参数参考看这里：http://ftp.ntu.edu.tw/ftp/pub/MySQL/doc/refman/5.1/en/connector-j-reference-configuration-properties.html

rewriteBatchedStatements 参数默认为false, 需要手工设置为true，设置方式大概像这样：

    String connectionUrl="jdbc:mysql://192.168.1.100:3306/test?rewriteBatchedStatements=true";  

默认时候，rewriteBatchedStatements=false时，执行路径会跳到 executeBatchSerially，此方法内部将语句一条条发送，与非batch处理简直一样，所以慢，就在这里了。

当设为 true时，会执行executeBatchedInserts方法，事实上mysql支持这样的插入语句

insert into t_user(id,uname) values(1, '1'), (2,'2'), (3, '3') ....

所以，当rewriteBatchedStatements=true时， 楼主的例子会被编译为以上形式，当然values里全是?, mysql 客户端会对这些值添加参数. 这样的方式当然就快很多了。

其实到现在还不太了解 batch 处理时，执行计划这个概念，不过我猜 mysql 可能并没有缓存执行计划。而只是将这些语句组合起来了。

所以如果是这样，他的机制与oracle可能是有所不同的，还不是达到最高效的机制，也许这就是开源与商业的区别吧。

我们如果想更深入了解，只能借助于一些服务器端监视工具,sql分析工具了。




写贴子过程断断续续给打扰了，本来还有一些可以写更详细的，就留给大家自己去探索了，包括，如果调用addBatch(String sql)后，则仍会按照 executeBatchSerially 方式执行，包括何时执行 executePreparedBatchAsMultiStatement，都可以继续深入了解。

后记，当使用 update 时，会执行 executePreparedBatchAsMultiStatement，但是如果攒的语句太多，会导致 mysql 崩溃. 我的测试中10000条update不会有事，20000时，mysql 就崩掉了。

针对rewriteBatchedStatements=true 参数我做了测试，我加了这个参数，做同们的插入10万条记录测试：
我的mysql 安装的虚拟机上，所以慢一些。

MySql JDBC 驱动版本	结果
5.0.8	没有提高 18秒
5.1.7	没有提高 18秒
5.1.13	有提高 1.6秒

看来加了rewriteBatchedStatements=true 参数，还要保证mysql JDBC驱的版本。
http://dev.mysql.com/downloads/connector/j  这是官方的下载地址，目前最新版本是5.1.13

最后感谢srdrm ，这位朋友太能钻研了，呵呵。