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

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";
<p style="margin: 0.0px 0.0px 0.0px 0.0px; color: #3b3df5;"><span style="font-family: Verdana,Arial,Helvetica,sans-serif; color: #000000;">     String url="jdbc:mysql://127.0.0.1:3306/dev?rewriteBatchedStatements=true";</span>


</p>


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