MySql 的批量操作，要加rewriteBatchedStatements参数

elf8848

浏览: 7185175 次
性别:
来自: 北京

最近访客更多访客>>

winicecold

zhaoshijie

zhw_0010

xierui54

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

MySQL数据库

MySQL批量操作

MySql 的批量操作，要加rewriteBatchedStatements参数

作者：赵磊

博客：http://elf8848.iteye.com

--------------------------------结论 ---------------------------------

MySql 非批量 10万条记录， 5700条/秒

MySql 批量(batch) 10万条记录， 62500条/秒

oracle 非批量插入10万条记录， 4464 条/秒

oracle 批量 (batch)插入10万条记录， 27778 条/秒

注：以上测试都是在插入10万条数据完成之后，一次性提交事务(对性能影响很大，占了很大便宜)。

另有一篇文章，说明提交事务的次数对insert性能的影响：《MySql 插入(insert)性能测试》

MySql的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入。

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

还要保证Mysql JDBC驱的版本。MySql的JDBC驱动的批量插入操作性能是很优秀的。

Mysql JDBC驱动，各个版本测试结果：

MySql JDBC 驱动版本号	插入10万条数据用时
5.0.8	加了rewriteBatchedStatements参数，没有提高还是17.4秒
5.1.7	加了rewriteBatchedStatements参数，没有提高还是17.4秒
5.1.13	加了rewriteBatchedStatements参数，插入速度提高到1.6秒

关于rewriteBatchedStatements参数，Mysql官方的说明：

Should the driver use multiqueries (irregardless of the setting of "allowMultiQueries") as well as rewriting of prepared statements for INSERT into multi-value inserts when executeBatch() is called? Notice that this has the potential for SQL injection if using plain java.sql.Statements and your code doesn't sanitize input correctly. Notice that for prepared statements, server-side prepared statements can not currently take advantage of this rewrite option, and that if you don't specify stream lengths when using PreparedStatement.set*Stream(), the driver won't be able to determine the optimum number of parameters per batch and you might receive an error from the driver that the resultant packet is too large. Statement.getGeneratedKeys() for these rewritten statements only works when the entire batch includes INSERT statements.

请参见：http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html

-------------------------事情的起因 ---------------------------------

原贴是《使用DBCP 数据库连接池遇到的两个比较怀疑的问题》 http://www.iteye.com/topic/768416

帖子是问连接池问题的，主要是使用连接池向MySql插入大量数据的事儿，有很多javaEye的朋友让作者使用JDBC的批量操作来提高大量插入数据的性能。

mercyblitz 写道

elf8848 写道

前面有多位朋友提出，让楼主使用 jdbc的批量操作，就是PreparedStatement 类上的addBatch(),executeBatch()方法。

在这里要提醒一下大家，MySql的JDBC驱动，是不支持批量操作的，就算你在代码中调用了批量操作的方法，MySql的JDBC驱动，也是按一般insert操作来处理的。

同样Fetch Size特性MySql的JDBC驱动也不支持。而Oracle的JDBC驱动是都支持的。

楼主使用的是Mysql数据库，不要指望通过批处理来提高性能了。

请不要想当然，建议你去看一下MySQL JDBC的源代码！

MySQL JDBC驱动在发送命令是，都是传递一个数组的String类型，然后转化为一个二维byte数组。

如果是一条的Statement的话，这个String数组只有一个元素，如果是Batch的话，则有相应个元素。

最后发送IO命令。不清楚你的结论是哪里来的？

我在这里重复的提醒大家：
MySql的JDBC驱动，不是真正支持批量操作的，就算你在代码中调用了批量操作的方法，MySql的JDBC驱动也是按照一般操作来处理的。
这不是什么重大发现，也不是什么新消息，是老调重弹，如果你使用Mysql数据库不要寄希望于通过JDBC批量操作来提高大量插入数据的性能，起码目前的MySql的JDBC驱动是这样的。

--------------------------------- 测试环境 ---------------------------------

测试机：笔记本电脑ThinkPad T400

操作系统：Windows XP

CPU:P8600 2.4G

内存:2G

IP:192.168.10.124

数据库服务器：笔记本ThinkPad T400

操作系统：虚拟机Windows 2003

内存：操作系统分配了500M （真可怜啊)

IP:192.168.10.139

数据库服务器上安装有MySql5.0.18 ,Oracle10G,都是默认安装。

JDBC驱动版本：

MySql : mysql-connector-java-5.0.8

Oracle : ojdbc6.jar (之前使用ojdbc14.jar批量插入10万条，实际只插入了3万多条，其它的丢失了，换ojdbc6.jar后，一次commit插入100万条也没有问题)

表只有两个字段：

id int

uname varchar(10)

innoDB引擎

---------------------------------测试结果： ---------------------------------

	mysql非批量插入10万条记录	mysql批量插入10万条记录（JDBC的URL中未加参数）	oracle非批量插入10万条记录	oracle批量插入10万条记录
第1次	17437 ms	17437 ms	22391 ms	360 ms
第2次	17422 ms	17562 ms	22297 ms	328 ms
第3次	17046 ms	17140 ms	22703 ms	359 ms

这里通过一个点也可以看出来Mysql批量与非批量性能是一样。

oracle的JDBC实现的批量操作的性能十分优秀。

---------------------------------测试方法： ---------------------------------

下面是测试代码：

package jdbc2;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Mysql {
	public static void main(String[] args) {
		test_mysql();
		//test_mysql_batch();
		//test_oracle();
		//test_oracle_batch();
	}
	/**
	 * mysql非批量插入10万条记录
	 * 第1次：17437 ms
	 * 第2次：17422 ms
	 * 第3次：17046 ms
	 */
	public static void test_mysql(){
		String url="jdbc:mysql://192.168.10.139:3306/test";
		String userName="root";
		String password="1234";
		Connection conn=null;
		try {      
		      Class.forName("com.mysql.jdbc.Driver");      
		      conn =  DriverManager.getConnection(url, userName, password);      
		      conn.setAutoCommit(false);      
		      String sql = "insert into t_user(id,uname) values(?,?)";      
		      PreparedStatement prest = conn.prepareStatement(sql);      
		      long a=System.currentTimeMillis();
		      for(int x = 0; x < 100000; x++){      
		         prest.setInt(1, x);      
		         prest.setString(2, "张三");      
		         prest.execute();
		      }      
		      conn.commit();      
		      long b=System.currentTimeMillis();
		      System.out.println("MySql非批量插入10万条记录用时"+ (b-a)+" ms");
		} catch (Exception ex) {
			ex.printStackTrace();
		}finally{
			try {
				if(conn!=null)conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}   
		}
	}
	/**
	 * mysql批量插入10万条记录(未加rewriteBatchedStatements参数)   ，加了参数后是1600ms左右
	 * 第1次：17437 ms
	 * 第2次：17562 ms
	 * 第3次：17140 ms
	 */
	public static void test_mysql_batch(){
		//String url="jdbc:mysql://192.168.10.139:3306/test";
		String url="jdbc:mysql://127.0.0.1:3306/dev?rewriteBatchedStatements=true";





		String userName="root";
		String password="1234";
		Connection conn=null;
		try {      
			Class.forName("com.mysql.jdbc.Driver");      
			conn =  DriverManager.getConnection(url, userName, password);      
			conn.setAutoCommit(false);      
			String sql = "insert into t_user(id,uname) values(?,?)";      
			PreparedStatement prest = conn.prepareStatement(sql);      
			long a=System.currentTimeMillis();
			for(int x = 0; x < 100000; x++){      
				prest.setInt(1, x);      
				prest.setString(2, "张三");      
				prest.addBatch();    
			}      
			prest.executeBatch();      
			conn.commit();      
			long b=System.currentTimeMillis();
			System.out.println("MySql批量插入10万条记录用时"+ (b-a)+" ms");
		} catch (Exception ex) {
			ex.printStackTrace();
		}finally{
			try {
				if(conn!=null)conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}   
		}
	}
	/**
	 * oracle非批量插入10万条记录
	 * 第1次：22391 ms
	 * 第2次：22297 ms
	 * 第3次：22703 ms
	 */
	public static void test_oracle(){
		String url="jdbc:oracle:thin:@192.168.10.139:1521:orcl";
		String userName="scott";
		String password="tiger";	
		Connection conn=null;
		try {      
			Class.forName("oracle.jdbc.OracleDriver");      
			conn =  DriverManager.getConnection(url, userName, password);      
			conn.setAutoCommit(false);      
			String sql = "insert into t_user(id,uname) values(?,?)";      
			PreparedStatement prest = conn.prepareStatement(sql);      
			long a=System.currentTimeMillis();
			for(int x = 0; x < 100000; x++){      
				prest.setInt(1, x);      
				prest.setString(2, "张三");      
				prest.execute();
			}
			conn.commit();      
			long b=System.currentTimeMillis();
			System.out.println("Oracle非批量插入10万记录用时"+ (b-a)+" ms");
			conn.close();    
		} catch (Exception ex) {
			ex.printStackTrace();
		}finally{
			try {
				if(conn!=null)conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}   
		}
	}
	/**
	 * oracle批量插入10万条记录
	 * 第1次：360 ms
	 * 第2次：328 ms
	 * 第3次：359 ms
	 */
	public static void test_oracle_batch(){
		String url="jdbc:oracle:thin:@192.168.10.139:1521:orcl";
		String userName="scott";
		String password="tiger";	
		Connection conn=null;
		try {      
			Class.forName("oracle.jdbc.OracleDriver");      
			conn =  DriverManager.getConnection(url, userName, password);      
			conn.setAutoCommit(false);      
			String sql = "insert into t_user(id,uname) values(?,?)";      
			PreparedStatement prest = conn.prepareStatement(sql);      
			long a=System.currentTimeMillis();
			for(int x = 0; x < 100000; x++){      
				prest.setInt(1, x);      
				prest.setString(2, "张三");      
				prest.addBatch();
			}
			prest.executeBatch();      
			conn.commit();      
			long b=System.currentTimeMillis();
			System.out.println("Oracle批量插入10万记录用时"+ (b-a)+" ms");
			conn.close();    
		} catch (Exception ex) {
			ex.printStackTrace();
		}finally{
			try {
				if(conn!=null)conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}   
		}
	}
}

分享到：

Oracle原理笔记（待续） | SQL查询练习题（oracle)

2010-09-23 20:58
浏览 33185
评论(38)
论坛回复 / 浏览 (35 / 21427)
分类:编程语言
查看更多

38 楼 teamilk 2014-10-11

帮助很大，非常感谢

37 楼 qing_gee 2014-07-28

一个参数能够让大家如此热烈的讨论自己的见解，的确让你欣慰和开心，加油大家，受教了。

36 楼 di1984HIT 2014-07-16

写的很好~

35 楼步青龙 2013-04-12

学习了

34 楼 angie_hawk7 2011-04-01

学习了，挺好的

33 楼 matt.u 2011-01-08

各位同学都很给力，非常感谢。
我这里提点注意：插入语句要采用INSET INTO ... VALUES(...)形式，不能写成INSERT INTO ... VALUE(...)，否则是rewriteBatchedStatements不生效。。我试了半天就是因为同事用的INSERT INTO VALUE，一直未生效。

另外插入语句最后不要加分号，否则会报错。

32 楼 finallygo 2010-09-26

经过测试,确实是这样的

31 楼 ccx007 2010-09-25

好文章..

30 楼 nj_first 2010-09-25

受教了，在平时也没机会应用到这方面。看到这篇文章，学习了

29 楼 lvp 2010-09-25

看着看着,我就跑来看这个问题了！

我学习了！

28 楼 shuiguozheng 2010-09-24

学习中！，，，，，

27 楼 Roshan2 2010-09-24

http://www.iteye.com/topic/770328

26 楼 kimmking 2010-09-24

elf8848 写道

srdrm 写道

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

先说结果:

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

srdrm 写道

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 ，这位朋友太能钻研了，呵呵。

1、个人的结论和看法都是片面的过时的
2、问题是越辩越清的
3、技术的争议是有益的
4、mysql是在进步的
5、大家都是好样的

25 楼 elf8848 2010-09-24

srdrm 写道

            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

			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();
			}

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

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

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

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

24 楼 diunei 2010-09-24

1、如果是用InnoDB，确实跟配置是有关系的，如果我没记错，InnoDB会使用一部分buffer作为insert buffer，如果buffer设置够大，对提高插入性能应该是有帮助的。

2、使用mysql的multi insert，前面有人说了，就不重复。

3、前面还有人说“插入前，去掉索引和其他约束，插入完成后，再加上”，个人觉得不尽然，主键索引是必须的，否则InnoDB默认会自动生成作为聚簇索引的主键，再建主键索引应该会导致对记录重新进行物理组织。另外，在建表时就建好索引是有好处的，1、中提的insert buffer不会马上更新索引的叶子页,而是把若干对同一页面的更新缓存起来,一次性更新。当然insert buffer只对辅助索引有效。

4、如果对表加锁，插入完解除应该也可以提高一点速度。

不成熟的经验，以上欢迎大家讨论。

23 楼 mercyblitz 2010-09-24

srdrm 写道

            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

			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();
			}

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

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

值得继续讨论下，不过这里仅讨论了PreparedStatement！

22 楼 mercyblitz 2010-09-24

kimmking 写道

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

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

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

MySQL传输数据貌似是字符型的(字节数组），PreparedStatement 和Statement的语句传输时。在开头几个字节上面有区别。如果网络分析的话，可以反向解析。

21 楼 kimmking 2010-09-24

20 楼 kimmking 2010-09-24

srdrm 写道

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

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

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

看了一半猜是values接多条记录，果然是。。。
这个感觉降低了网络开销和事务数，但是还是每次解释sql。

19 楼 srdrm 2010-09-24

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论