数据库批量插入数据优化 prest.execute()与prest.executeBatch()

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

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

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


 

参与评论 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

caiyl1314

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值