JDBC批量Insert深度优化（没事务）
最近在做一个数据同步分发工具，高并发，高效率，异步非实时是主要特点。
为此，选择的方案是JDBC、只有两种操作，插入和更新。
对于更新，只能逐条分批就可以了，优化空间不大。
对于插入，则可以做批量的优化，优化的策略只能是具体问题具体分析，以测试结论为主要依据了。
环境：
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条，分多种分批方案来执行。
本次主要测试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、数据库连接池可以大大提高效率，如果没有连接池管理，效率会大大降低，但不管怎么连接，上面的优化策略都是有效的。
本文出自 “熔 岩” 博客，转载请与作者联系！
本文出自 51CTO.COM技术博客
分享到：
相关推荐
5. **连接池**：可以通过`jdbc_pool_timeout`、`jdbc_validate_connection`等设置，优化数据库连接的管理和性能。 **应用场景** - 日志分析：将日志数据存储到数据库，便于进行复杂SQL查询和报表生成。 - 实时监控...
20.4 Java应用通过JDBC API声明JDBC事务 20.5 Java应用通过Hibernate API声明JDBC事务 20.5.1 处理异常 20.5.2 Session与事务的关系 20.5.3 设定事务超时 20.6 Java应用通过Hibernate API声明JTA事务 ...
20.4 Java应用通过JDBC API声明JDBC事务 20.5 Java应用通过Hibernate API声明JDBC事务 20.5.1 处理异常 20.5.2 Session与事务的关系 20.5.3 设定事务超时 20.6 Java应用通过Hibernate API声明JTA事务 ...
20.4 Java应用通过JDBC API声明JDBC事务 20.5 Java应用通过Hibernate API声明JDBC事务 20.5.1 处理异常 20.5.2 Session与事务的关系 20.5.3 设定事务超时 20.6 Java应用通过Hibernate API声明JTA事务 ...
20.4 Java应用通过JDBC API声明JDBC事务 20.5 Java应用通过Hibernate API声明JDBC事务 20.5.1 处理异常 20.5.2 Session与事务的关系 20.5.3 设定事务超时 20.6 Java应用通过Hibernate API声明JTA事务 ...
《MySQL与Java开发者指南》是一本深度探讨MySQL数据库与Java编程语言如何协同工作的专业书籍，由Mark Matthews、Jim Cole和Joseph D. Gradecki三位作者共同撰写。本书旨在为开发人员提供一个全面的理解框架，帮助...
**Ibatis API 深度解析** Ibatis，作为一个轻量级的持久层框架，它在Java开发领域中被广泛使用。Ibatis 提供了一种将SQL语句与Java代码解耦的方式，使得数据库操作更加灵活，易于维护。本文将深入探讨Ibatis API的...
本篇文章将针对从Apache网站通过SVN下载的iBatis-2源代码进行深度剖析，帮助开发者更深入地理解其内部机制。 1. **iBatis框架概述** iBatis提供了一个SQL映射框架，允许开发者编写动态SQL，将数据库操作与业务逻辑...
1. 插入（Insert）：使用Session的save()或saveOrUpdate()方法，将Java对象持久化到数据库。 2. 更新（Update）：调用Session的update()或saveOrUpdate()方法，更新数据库中的记录。 3. 删除（Delete）：通过Session...
《SQLite在Java中的应用深度解析》 SQLite是一个轻量级的、开源的、自包含的数据库引擎，广泛应用于移动设备、嵌入式系统以及桌面应用程序。在Java开发中，SQLite作为一个方便的数据存储解决方案，尤其适合那些对...
3. **插入数据**：使用`insert()`方法插入数据，可以是单条记录，也可以是批量插入。 4. **查询数据**：使用`select()`方法构造查询条件，结合`forEach()`遍历查询结果。 5. **更新数据**：`update()`方法用于更新...