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.

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

原貼是《使用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實現的批量操作的性能十分優秀。

---------------------------------測試方法：?---------------------------------

下面是測試代碼：

Java代碼??

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?

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?

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?

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?

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

}

}

}

}

總結

以上是生活随笔為你收集整理的mysql批量插入 增加参数_MySql 的批量操作，要加rewriteBatchedStatements参数的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。