JDBC批量Insert深度优化（有事务）（转）

环境：

MySQL 5.1

RedHat Linux AS 5

JavaSE 1.5

DbConnectionBroker 微型数据库连接池

 

测试的方案：

执行10万次Insert语句，使用不同方式。

 

A组：静态SQL，自动提交，没事务控制（MyISAM引擎）

1、逐条执行10万次

2、分批执行将10万分成m批，每批n条，分多种分批方案来执行。

 

B组：预编译模式SQL，自动提交，没事务控制（MyISAM引擎）

1、逐条执行10万次

2、分批执行将10万分成m批，每批n条，分多种分批方案来执行。

-------------------------------------------------------------------------------------------

C组：静态SQL，不自动提交，有事务控制（InnoDB引擎）

1、逐条执行10万次

2、分批执行将10万分成m批，每批n条，分多种分批方案来执行。

 

D组：预编译模式SQL，不自动提交，有事务控制（InnoDB引擎）

1、逐条执行10万次

2、分批执行将10万分成m批，每批n条，分多种分批方案来执行。

 

本次主要测试C、D组，并得出测试结果。

 

SQL代码

DROP TABLE IF EXISTS tuser; 

CREATE TABLE tuser ( 
    id bigint(20) NOT NULL AUTO_INCREMENT, 
    name varchar(12) DEFAULT NULL, 
    remark varchar(24) DEFAULT NULL, 
    createtime datetime DEFAULT NULL, 
    updatetime datetime DEFAULT NULL, 
    PRIMARY KEY (id) 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

 

C、D组测试代码：

package testbatch; 

import java.io.IOException; 
import java.sql.*; 

/** 
* JDBC批量Insert优化（下） 
* 
* @author leizhimin 2009-7-29 10:03:10 
*/ 
public class TestBatch { 
        public static DbConnectionBroker myBroker = null; 

        static { 
                try { 
                        myBroker = new DbConnectionBroker("com.mysql.jdbc.Driver", 
                                        "jdbc:mysql://192.168.104.163:3306/testdb", 
                                        "vcom", "vcom", 2, 4, 
                                        "c:\\testdb.log", 0.01); 
                } catch (IOException e) { 
                        e.printStackTrace(); 
                } 
        } 

        /** 
         * 初始化测试环境 
         * 
         * @throws SQLException 异常时抛出 
         */ 
        public static void init() throws SQLException { 
                Connection conn = myBroker.getConnection(); 
                conn.setAutoCommit(false); 
                Statement stmt = conn.createStatement(); 
                stmt.addBatch("DROP TABLE IF EXISTS tuser"); 
                stmt.addBatch("CREATE TABLE tuser (\n" + 
                                "    id bigint(20) NOT NULL AUTO_INCREMENT,\n" + 
                                "    name varchar(12) DEFAULT NULL,\n" + 
                                "    remark varchar(24) DEFAULT NULL,\n" + 
                                "    createtime datetime DEFAULT NULL,\n" + 
                                "    updatetime datetime DEFAULT NULL,\n" + 
                                "    PRIMARY KEY (id)\n" + 
                                ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8"); 
                stmt.executeBatch(); 
                conn.commit(); 
                myBroker.freeConnection(conn); 
        } 

        /** 
         * 100000条静态SQL插入 
         * 
         * @throws Exception 异常时抛出 
         */ 
        public static void testInsert() throws Exception { 
                init();         //初始化环境 
                Long start = System.currentTimeMillis(); 
                for (int i = 0; i < 100000; i++) { 
                        String sql = "\n" + 
                                        "insert into testdb.tuser \n" + 
                                        "\t(name, \n" + 
                                        "\tremark, \n" + 
                                        "\tcreatetime, \n" + 
                                        "\tupdatetime\n" + 
                                        "\t)\n" + 
                                        "\tvalues\n" + 
                                        "\t('" + RandomToolkit.generateString(12) + "', \n" + 
                                        "\t'" + RandomToolkit.generateString(24) + "', \n" + 
                                        "\tnow(), \n" + 
                                        "\tnow()\n" + 
                                        ")"; 
                        Connection conn = myBroker.getConnection(); 
                        conn.setAutoCommit(false); 
                        Statement stmt = conn.createStatement(); 
                        stmt.execute(sql); 
                        conn.commit(); 
                        myBroker.freeConnection(conn); 
                } 
                Long end = System.currentTimeMillis(); 
                System.out.println("单条执行100000条Insert操作，共耗时：" + (end - start) / 1000f + "秒！"); 
        } 

        /** 
         * 批处理执行静态SQL测试 
         * 
         * @param m 批次 
         * @param n 每批数量 
         * @throws Exception 异常时抛出 
         */ 
        public static void testInsertBatch(int m, int n) throws Exception { 
                init();             //初始化环境 
                Long start = System.currentTimeMillis(); 
                for (int i = 0; i < m; i++) { 
                        //从池中获取连接 
                        Connection conn = myBroker.getConnection(); 
                        conn.setAutoCommit(false); 
                        Statement stmt = conn.createStatement(); 
                        for (int k = 0; k < n; k++) { 
                                String sql = "\n" + 
                                                "insert into testdb.tuser \n" + 
                                                "\t(name, \n" + 
                                                "\tremark, \n" + 
                                                "\tcreatetime, \n" + 
                                                "\tupdatetime\n" + 
                                                "\t)\n" + 
                                                "\tvalues\n" + 
                                                "\t('" + RandomToolkit.generateString(12) + "', \n" + 
                                                "\t'" + RandomToolkit.generateString(24) + "', \n" + 
                                                "\tnow(), \n" + 
                                                "\tnow()\n" + 
                                                ")"; 
                                //加入批处理 
                                stmt.addBatch(sql); 
                        } 
                        stmt.executeBatch();    //执行批处理 
                        conn.commit(); 
//                        stmt.clearBatch();        //清理批处理 
                        stmt.close(); 
                        myBroker.freeConnection(conn); //连接归池 
                } 
                Long end = System.currentTimeMillis(); 
                System.out.println("批量执行" + m + "*" + n + "=" + m * n + "条Insert操作，共耗时：" + (end - start) / 1000f + "秒！"); 
        } 

        /** 
         * 100000条预定义SQL插入 
         * 
         * @throws Exception 异常时抛出 
         */ 
        public static void testInsert2() throws Exception {     //单条执行100000条Insert操作，共耗时：40.422秒！ 
                init();         //初始化环境 
                Long start = System.currentTimeMillis(); 
                String sql = "" + 
                                "insert into testdb.tuser\n" + 
                                "    (name, remark, createtime, updatetime)\n" + 
                                "values\n" + 
                                "    (?, ?, ?, ?)"; 
                for (int i = 0; i < 100000; i++) { 
                        Connection conn = myBroker.getConnection(); 
                        conn.setAutoCommit(false); 
                        PreparedStatement pstmt = conn.prepareStatement(sql); 
                        pstmt.setString(1, RandomToolkit.generateString(12)); 
                        pstmt.setString(2, RandomToolkit.generateString(24)); 
                        pstmt.setDate(3, new Date(System.currentTimeMillis())); 
                        pstmt.setDate(4, new Date(System.currentTimeMillis())); 
                        pstmt.executeUpdate(); 
                        conn.commit(); 
                        pstmt.close(); 
                        myBroker.freeConnection(conn); 
                } 
                Long end = System.currentTimeMillis(); 
                System.out.println("单条执行100000条Insert操作，共耗时：" + (end - start) / 1000f + "秒！"); 
        } 

        /** 
         * 批处理执行预处理SQL测试 
         * 
         * @param m 批次 
         * @param n 每批数量 
         * @throws Exception 异常时抛出 
         */ 
        public static void testInsertBatch2(int m, int n) throws Exception { 
                init();             //初始化环境 
                Long start = System.currentTimeMillis(); 
                String sql = "" + 
                                "insert into testdb.tuser\n" + 
                                "    (name, remark, createtime, updatetime)\n" + 
                                "values\n" + 
                                "    (?, ?, ?, ?)"; 
                for (int i = 0; i < m; i++) { 
                        //从池中获取连接 
                        Connection conn = myBroker.getConnection(); 
                        conn.setAutoCommit(false); 
                        PreparedStatement pstmt = conn.prepareStatement(sql); 
                        for (int k = 0; k < n; k++) { 
                                pstmt.setString(1, RandomToolkit.generateString(12)); 
                                pstmt.setString(2, RandomToolkit.generateString(24)); 
                                pstmt.setDate(3, new Date(System.currentTimeMillis())); 
                                pstmt.setDate(4, new Date(System.currentTimeMillis())); 
                                //加入批处理 
                                pstmt.addBatch(); 
                        } 
                        pstmt.executeBatch();    //执行批处理 
                        conn.commit(); 
//                        pstmt.clearBatch();        //清理批处理 
                        pstmt.close(); 
                        myBroker.freeConnection(conn); //连接归池 
                } 
                Long end = System.currentTimeMillis(); 
                System.out.println("批量执行" + m + "*" + n + "=" + m * n + "条Insert操作，共耗时：" + (end - start) / 1000f + "秒！"); 
        } 

        public static void main(String[] args) throws Exception { 
                init(); 
                Long start = System.currentTimeMillis(); 
                System.out.println("--------C组测试----------"); 
                testInsert(); 
                testInsertBatch(100, 1000); 
                testInsertBatch(250, 400); 
                testInsertBatch(400, 250); 
                testInsertBatch(500, 200); 
                testInsertBatch(1000, 100); 
                testInsertBatch(2000, 50); 
                testInsertBatch(2500, 40); 
                testInsertBatch(5000, 20); 
                Long end1 = System.currentTimeMillis(); 
                System.out.println("C组测试过程结束，全部测试耗时：" + (end1 - start) / 1000f + "秒！"); 

                System.out.println("--------D组测试----------"); 
                testInsert2(); 
                testInsertBatch2(100, 1000); 
                testInsertBatch2(250, 400); 
                testInsertBatch2(400, 250); 
                testInsertBatch2(500, 200); 
                testInsertBatch2(1000, 100); 
                testInsertBatch2(2000, 50); 
                testInsertBatch2(2500, 40); 
                testInsertBatch2(5000, 20); 

                Long end2 = System.currentTimeMillis(); 
                System.out.println("D组测试过程结束，全部测试耗时：" + (end2 - end1) / 1000f + "秒！"); 
        } 
}

 

执行结果：

--------C组测试---------- 
单条执行100000条Insert操作，共耗时：103.656秒！ 
批量执行100*1000=100000条Insert操作，共耗时：31.328秒！ 
批量执行250*400=100000条Insert操作，共耗时：31.406秒！ 
批量执行400*250=100000条Insert操作，共耗时：31.75秒！ 
批量执行500*200=100000条Insert操作，共耗时：31.438秒！ 
批量执行1000*100=100000条Insert操作，共耗时：31.968秒！ 
批量执行2000*50=100000条Insert操作，共耗时：32.938秒！ 
批量执行2500*40=100000条Insert操作，共耗时：33.141秒！ 
批量执行5000*20=100000条Insert操作，共耗时：35.265秒！ 
C组测试过程结束，全部测试耗时：363.656秒！ 
--------D组测试---------- 
单条执行100000条Insert操作，共耗时：107.61秒！ 
批量执行100*1000=100000条Insert操作，共耗时：32.64秒！ 
批量执行250*400=100000条Insert操作，共耗时：32.641秒！ 
批量执行400*250=100000条Insert操作，共耗时：33.109秒！ 
批量执行500*200=100000条Insert操作，共耗时：32.859秒！ 
批量执行1000*100=100000条Insert操作，共耗时：33.547秒！ 
批量执行2000*50=100000条Insert操作，共耗时：34.312秒！ 
批量执行2500*40=100000条Insert操作，共耗时：34.672秒！ 
批量执行5000*20=100000条Insert操作，共耗时：36.672秒！ 
D组测试过程结束，全部测试耗时：378.922秒！ 

 

 

测试结果意想不到吧，最短时间竟然超过上篇。观察整个测试结果，发现总时间很长，原因是逐条执行的效率太低了。

 

结论：

 

在本测试条件下，得出结论：

 

数据库连接池控制下，不自动提交，事务控制（InnoDB引擎）

 

1、逐条执行的效率很低很低，尽可能避免逐条执行。

2、事务控制下，静态SQL的效率超过预处理SQL。

3、分批的大小对效率影响挺大的，一般来说，事务控制下，分批大小在100-1000之间比较合适。

4、谈到优化方式，上面的批处理就是很好的优化策略。

 

 

大总结：

 

对比上篇没事务的测试结果，得出一个全面的结论：

 

1、连接池最基本的也是最重要的优化策略，总能大幅提高性能。

 

2、批处理在效率上总是比逐条处理有优势，要处理的数据的记录条数越大，批处理的优势越明显，批处理还有一个好处就是减少了对数据库的链接次数，从而减轻数据库的压力。

 

3、批处理执行SQL的时候，批处理的分批的大小与数据库的吞吐量以及硬件配置有很大关系，需要通过测试找到最佳的分批大小，一般在50-1000之间。

 

4、预处理SQL在没事务的表上效率较高，在有实物的情况下比静态SQL稍有不及。但预定义SQL还有个好处就是消耗的内存较少，静态SQL串会占用大量的内存资源，容易导致内存溢出的问题。因此批量执行时候可以优先选择预定义SQL。

 

5、在批处理执行的时候，每批执行完成后，最好显式的调用pstmt.close()或stmt.close()方法，以便尽快释放执行过的SQL语句，提高内存利用率。

 

6、对于有大量SELECT操作，MyISAM是更好的选择；对于有大量INSERT和UPDATE操作的表，InnoDB效率更好。

 

7、虽然测试结果只能反映特定情况下的一些事实，以上的优化策略是普遍策略，可以明显缩短寻找最优策略的时间，对于效率要求很高的程序，还应该做并发性等测试。

 

8、测试是件很辛苦的事情，你需要有大量的事实来证明你的优化是有效的，而不能单单凭经验，因为每个机器的环境都不一样，使用的方式也不同。

 

本文出自 “熔 岩” 博客，转载请与作者联系！