最近在做一个数据同步分发工具,高并发,高效率,异步非实时是主要特点。
为此,选择的方案是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;
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 + "秒!");
}
}
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秒!
单条执行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秒!
![](https://i-blog.csdnimg.cn/blog_migrate/a41196812310a5d81c2acaa7b9b3d727.png)
上面的测试结果似乎在意料之外,看来是用连接池技术,各种执行方式性能差异不是很大。细心观察测试结果,可以得出一些结论。
结论:
以下结论以测试的环境和组别为前提:
数据库连接池控制下,单线程,自动提交,没事务控制(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、数据库连接池可以大大提高效率,如果没有连接池管理,效率会大大降低,但不管怎么连接,上面的优化策略都是有效的。
本文出自 “熔 岩” 博客,转载请与作者联系!