DBC批量Insert深度优化（有事务）的方法。

　　本文主要介绍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、测试是件很辛苦的事情，你需要有大量的事实来证明你的优化是有效的，而不能单单凭经验，因为每个机器的环境都不一样，使用的方式也不同。

　　本文出自 “熔 岩” 博客。http://webservices.ctocio.com.cn/java/9/9041009.shtml