思享工具箱

[mysql] mysql批量操作时性能优化

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

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

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以上版本的驱动，才能实现高性能的批量插入。

例如：StringconnectionUrl="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 ms17140 ms22703 ms359 ms

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

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

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

下面是测试代码：

Java代码 复制代码收藏代码[mysql] mysql批量操作时性能优化第3张
  1. packagejdbc2;
  2. importjava.sql.Connection;
  3. importjava.sql.DriverManager;
  4. importjava.sql.PreparedStatement;
  5. importjava.sql.SQLException;
  7. publicclassMysql{
  8. publicstaticvoidmain(String[]args){
  9. test_mysql();
  10. //test_mysql_batch();
  11. //test_oracle();
  12. //test_oracle_batch();
  13. }
  14. /**
  15. *mysql非批量插入10万条记录
  16. *第1次：17437ms
  17. *第2次：17422ms
  18. *第3次：17046ms
  19. */
  20. publicstaticvoidtest_mysql(){
  21. Stringurl="jdbc:mysql://192.168.10.139:3306/test";
  22. StringuserName="root";
  23. Stringpassword="1234";
  24. Connectionconn=null;
  25. try{
  26. Class.forName("com.mysql.jdbc.Driver");
  27. conn=DriverManager.getConnection(url,userName,password);
  28. conn.setAutoCommit(false);
  29. Stringsql="insertintot_user(id,uname)values(?,?)";
  30. PreparedStatementprest=conn.prepareStatement(sql);
  31. longa=System.currentTimeMillis();
  32. for(intx=0;x<100000;x++){
  33. prest.setInt(1,x);
  34. prest.setString(2,"张三");
  35. prest.execute();
  36. }
  37. conn.commit();
  38. longb=System.currentTimeMillis();
  39. System.out.println("MySql非批量插入10万条记录用时"+(b-a)+"ms");
  40. }catch(Exceptionex){
  41. ex.printStackTrace();
  42. }finally{
  43. try{
  44. if(conn!=null)conn.close();
  45. }catch(SQLExceptione){
  46. e.printStackTrace();
  47. }
  48. }
  49. }
  50. /**
  51. *mysql批量插入10万条记录(未加rewriteBatchedStatements参数)，加了参数后是1600ms左右
  52. *第1次：17437ms
  53. *第2次：17562ms
  54. *第3次：17140ms
  55. */
  56. publicstaticvoidtest_mysql_batch(){
  57. //Stringurl="jdbc:mysql://192.168.10.139:3306/test";
  58. <pstyle="margin:0px;color:rgb(59,61,245);"><spanstyle="color:rgb(0,0,0);font-family:Verdana,Arial,Helvetica,sans-serif;">Stringurl="jdbc:mysql://127.0.0.1:3306/dev?rewriteBatchedStatements=true";</span>
  61. </p>
  64. StringuserName="root";
  65. Stringpassword="1234";
  66. Connectionconn=null;
  67. try{
  68. Class.forName("com.mysql.jdbc.Driver");
  69. conn=DriverManager.getConnection(url,userName,password);
  70. conn.setAutoCommit(false);
  71. Stringsql="insertintot_user(id,uname)values(?,?)";
  72. PreparedStatementprest=conn.prepareStatement(sql);
  73. longa=System.currentTimeMillis();
  74. for(intx=0;x<100000;x++){
  75. prest.setInt(1,x);
  76. prest.setString(2,"张三");
  77. prest.addBatch();
  78. }
  79. prest.executeBatch();
  80. conn.commit();
  81. longb=System.currentTimeMillis();
  82. System.out.println("MySql批量插入10万条记录用时"+(b-a)+"ms");
  83. }catch(Exceptionex){
  84. ex.printStackTrace();
  85. }finally{
  86. try{
  87. if(conn!=null)conn.close();
  88. }catch(SQLExceptione){
  89. e.printStackTrace();
  90. }
  91. }
  92. }
  93. /**
  94. *oracle非批量插入10万条记录
  95. *第1次：22391ms
  96. *第2次：22297ms
  97. *第3次：22703ms
  98. */
  99. publicstaticvoidtest_oracle(){
  100. Stringurl="jdbc:oracle:thin:@192.168.10.139:1521:orcl";
  101. StringuserName="scott";
  102. Stringpassword="tiger";
  103. Connectionconn=null;
  104. try{
  105. Class.forName("oracle.jdbc.OracleDriver");
  106. conn=DriverManager.getConnection(url,userName,password);
  107. conn.setAutoCommit(false);
  108. Stringsql="insertintot_user(id,uname)values(?,?)";
  109. PreparedStatementprest=conn.prepareStatement(sql);
  110. longa=System.currentTimeMillis();
  111. for(intx=0;x<100000;x++){
  112. prest.setInt(1,x);
  113. prest.setString(2,"张三");
  114. prest.execute();
  115. }
  116. conn.commit();
  117. longb=System.currentTimeMillis();
  118. System.out.println("Oracle非批量插入10万记录用时"+(b-a)+"ms");
  119. conn.close();
  120. }catch(Exceptionex){
  121. ex.printStackTrace();
  122. }finally{
  123. try{
  124. if(conn!=null)conn.close();
  125. }catch(SQLExceptione){
  126. e.printStackTrace();
  127. }
  128. }
  129. }
  130. /**
  131. *oracle批量插入10万条记录
  132. *第1次：360ms
  133. *第2次：328ms
  134. *第3次：359ms
  135. */
  136. publicstaticvoidtest_oracle_batch(){
  137. Stringurl="jdbc:oracle:thin:@192.168.10.139:1521:orcl";
  138. StringuserName="scott";
  139. Stringpassword="tiger";
  140. Connectionconn=null;
  141. try{
  142. Class.forName("oracle.jdbc.OracleDriver");
  143. conn=DriverManager.getConnection(url,userName,password);
  144. conn.setAutoCommit(false);
  145. Stringsql="insertintot_user(id,uname)values(?,?)";
  146. PreparedStatementprest=conn.prepareStatement(sql);
  147. longa=System.currentTimeMillis();
  148. for(intx=0;x<100000;x++){
  149. prest.setInt(1,x);
  150. prest.setString(2,"张三");
  151. prest.addBatch();
  152. }
  153. prest.executeBatch();
  154. conn.commit();
  155. longb=System.currentTimeMillis();
  156. System.out.println("Oracle批量插入10万记录用时"+(b-a)+"ms");
  157. conn.close();
  158. }catch(Exceptionex){
  159. ex.printStackTrace();
  160. }finally{
  161. try{
  162. if(conn!=null)conn.close();
  163. }catch(SQLExceptione){
  164. e.printStackTrace();
  165. }
  166. }
  167. }
  168. }

[转载自:http://elf8848.iteye.com/blog/770032]

标签：#jdbc#mysql#oracle#性能优化#mysql数据库 | 浏览：176 | 发布日期：

免责声明：文章转载自《[mysql] mysql批量操作时性能优化》仅用于学习参考。如对内容有疑问，请及时联系本站处理。

上篇软件架构 "4+1" 视图模型maven创建ssm项目依赖(pom.xml文件)下篇

宿迁高防，2C2G15M，22元/月；香港BGP，2C5G5M，25元/月 雨云优惠码：MjYwNzM=

相关文章

Oracle中游标的使用

一、 游标的概念：       游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。   二、游标分类：       游标有两种类...

mysql，oracle查询当天的数据

mysql： DATEDIFF() 函数返回两个日期之间的时间。 比如查询今天打卡的人员信息，只需要条件是打卡时间和当前时间差值为零就可以了 SELECT t.* FROM t_dkxq t WHERE DATEDIFF(t.dksj, NOW()) = 0  oracle: 原文链接：oracle 查询当天数据的sql条件写法...

MySQL 存储过程 定时任务

drop procedure if existsmove_track_data; delimiter // create proceduremove_track_data() begin declare i int default 0; start transaction; set i = 1; case w...

mysql 重置当前的自动增量值

mysql的语法与sql server的语法不同 1. sql server sql server的语法dbcc checkident('tablename',reseed,5) 2. mysql mysql的语法是ALTER TABLE tablename AUTO_INCREMENT = 5; 其中tablename是表名，5是重置之后的自动增量值大小...

利用mybatis连接mysql数据库进行数据的操作

整体结构如下： 首先写大配置，该配置的作用是连接数据库。    可以将连接数据库的方法单独提出来，写在jdbc.propterties中，代码如下： jdbc.driver=com.mysql.jdbc.Driver //加载驱动jdbc.url=jdbc:mysql://localhost:3306/school //连接mysql数据库jdbc...

mysql 数据库存储路径更改

使用了VPS一段时间之后发现磁盘空间快满了。本人的VPS在购买的时候买了500gb的磁盘，提供商赠送了20GB的高性能系统磁盘。这样系统就有两个磁盘空间了。在初次安装mysql 的时候将数据库目录安装在了系统盘。（第一个磁盘）使用了一段时间之后数据库存储量变大，快将20GB的存放空间占满了。因此必须将存放数据空间换地方了。嘿嘿下面是简单的操作了，不合理之处...

最新文章

随机推荐

思享工具箱导航