JDBC批量Insert深度优化（没事务）（转）
最近在做一个数据同步分发工具，高并发，高效率，异步非实时是主要特点。为此，选择的方案是JDBC、只有两种操作，插入和更新。?对于更新，只能逐条分批就可以了，优化空间不大。对于插入，则可以做批量的优化，优化的策略只能是具体问题具体分析，以测试结论为主要依据了。?环境：MySQL 5.1RedHat Linux AS 5JavaSE 1.5DbConnectionBroker 微型数据库连接池?测试的方案：执行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条，分多种分批方案来执行。?本次主要测试A、B组，并得出测试结果。?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=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;??下面是A、B组的测试代码：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();
????????????????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=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8");
????????????????stmt.executeBatch();
????????????????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();
????????????????????????Statement stmt = conn.createStatement();
????????????????????????stmt.execute(sql);
????????????????????????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();
????????????????????????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();????//执行批处理
//????????????????????????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();
????????????????????????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();
????????????????????????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();
????????????????????????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();????//执行批处理
//????????????????????????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("--------A组测试----------");
????????????????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("B组测试过程结束，全部测试耗时：" + (end1 - start) / 1000f + "秒！");
????????????????System.out.println("--------B组测试----------");
????????????????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("B组测试过程结束，全部测试耗时：" + (end2 - end1) / 1000f + "秒！");
????????}
} ?运行结果：--------A组测试----------
单条执行100000条Insert操作，共耗时：36.766秒！
批量执行100*1000=100000条Insert操作，共耗时：33.625秒！
批量执行250*400=100000条Insert操作，共耗时：35.063秒！
批量执行400*250=100000条Insert操作，共耗时：35.296秒！
批量执行500*200=100000条Insert操作，共耗时：37.016秒！
批量执行1000*100=100000条Insert操作，共耗时：35.953秒！
批量执行2000*50=100000条Insert操作，共耗时：36.265秒！
批量执行2500*40=100000条Insert操作，共耗时：36.625秒！
批量执行5000*20=100000条Insert操作，共耗时：37.234秒！
B组测试过程结束，全部测试耗时：323.906秒！
--------B组测试----------
单条执行100000条Insert操作，共耗时：44.188秒！
批量执行100*1000=100000条Insert操作，共耗时：34.235秒！
批量执行250*400=100000条Insert操作，共耗时：34.328秒！
批量执行400*250=100000条Insert操作，共耗时：34.032秒！
批量执行500*200=100000条Insert操作，共耗时：33.625秒！
批量执行1000*100=100000条Insert操作，共耗时：34.125秒！
批量执行2000*50=100000条Insert操作，共耗时：33.797秒！
批量执行2500*40=100000条Insert操作，共耗时：35.359秒！
批量执行5000*20=100000条Insert操作，共耗时：36.218秒！
B组测试过程结束，全部测试耗时：320.0秒！?
?上面的测试结果似乎在意料之外，看来是用连接池技术，各种执行方式性能差异不是很大。细心观察测试结果，可以得出一些结论。结论：?以下结论以测试的环境和组别为前提：?数据库连接池控制下，单线程，自动提交，没事务控制（MyISAM引擎）?1、预定义SQL并没有想象中的那么高效，因为两组测试结果相差3秒，最短执行时间均为33.625秒！单项测试结果各有胜出。?2、批量执行时，分批的大小对效率影响也很大，静态SQL以200-1000条分批执行为宜。预处理SQL以50-400条为宜。?3、通过结果看，预处理SQL效率稍稍胜出，因此批量执行时候优先选择预定义SQL，预定义SQL还有个好处就是消耗的内存较少。静态SQL串会占用大量的内存资源，容易导致内存溢出的问题。?4、在批处理执行的时候，每批执行完成后，最好显式的调用pstmt.close()或stmt.close()方法，以便尽快释放执行过的SQL语句，提高内存利用率。?5、谈到优化方式，上面的批处理就是很好的优化策略。?6、数据库连接池可以大大提高效率，如果没有连接池管理，效率会大大降低，但不管怎么连接，上面的优化策略都是有效的。?
本文出自 “熔 岩” 博客，转载请与作者联系！
