我使用的数据库是Mysql 5.5,建了一个55个字段的表,8个索引。在插入100W条时,使用addBatch却不如直接用execute快,这是为啥?主要代码如下:
conn = DriverManager.getConnection(test.url, test.userName, test.password);
stmt = conn.createStatement();
pstmt = conn.prepareStatement(test.pinsert(tableName));
int count = 0;
int end = index + MyDBTest.THREAD_REC_COUNT;
System.out.println("write id from " + index + " to " + end + "!"); long startTime = System.currentTimeMillis();
for (int i = index; i < end; i++) {
test.setParam(pstmt, i);
pstmt.addBatch();
count++;
if (count % 1000 == 0) {
pstmt.executeBatch();
}
//stmt.execute(test.insert(tableName, i));
}
System.out.println("Thead"+ (index/MyDBTest.THREAD_REC_COUNT) + " store from " + index + " to " +
end + " objects and time used " + (System.currentTimeMillis() - startTime) + "ms");
System.out.println("total used time " + (System.currentTimeMillis() - test.getUsedTime()) + "ms");
这是是我线程里run里的代码。这个与线程无关,单线程的也试过了,还是一样。
conn = DriverManager.getConnection(test.url, test.userName, test.password);
stmt = conn.createStatement();
pstmt = conn.prepareStatement(test.pinsert(tableName));
int count = 0;
int end = index + MyDBTest.THREAD_REC_COUNT;
System.out.println("write id from " + index + " to " + end + "!"); long startTime = System.currentTimeMillis();
for (int i = index; i < end; i++) {
test.setParam(pstmt, i);
pstmt.addBatch();
count++;
if (count % 1000 == 0) {
pstmt.executeBatch();
}
//stmt.execute(test.insert(tableName, i));
}
System.out.println("Thead"+ (index/MyDBTest.THREAD_REC_COUNT) + " store from " + index + " to " +
end + " objects and time used " + (System.currentTimeMillis() - startTime) + "ms");
System.out.println("total used time " + (System.currentTimeMillis() - test.getUsedTime()) + "ms");
这是是我线程里run里的代码。这个与线程无关,单线程的也试过了,还是一样。
public String insert(String name, long id) {
StringBuilder sql = new StringBuilder("insert into ").append(name).append(" values (" + id + ", 'abc',");
for (int i = 1; i < 54; i++) {
sql.append("'abcdefg1345'").append(",");
}
sql.replace(sql.length() - 1, sql.length(), ");");
return sql.toString();
}
public String pinsert(String name) {
StringBuilder sql = new StringBuilder("insert into ").append(name).append(" values (?, ?,");
for (int i = 1; i < 54; i++) {
//sql.append("'abcdefg1345'").append(",");
sql.append("?,");
}
sql.replace(sql.length() - 1, sql.length(), ");");
return sql.toString();
}
public void setParam(PreparedStatement pstmt, long id) throws Exception {
pstmt.setLong(1, id);
for (int i = 2; i < 56; i++) {
pstmt.setString(i, "abcdefg1345");
}
}code]
我的表是这样的:
[code=Java] public void createTable(String name) {
String sql = "CREATE TABLE `" + name + "` (`Id` int(11) DEFAULT 0,`name` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name1` varchar(20) CHARACTER SET utf8 DEFAULT NULL,"
+ "`name2` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name3` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name4` varchar(20) CHARACTER SET utf8 DEFAULT NULL,"
+ "`name5` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name6` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name7` varchar(20) CHARACTER SET utf8 DEFAULT NULL,"
+ "`name8` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name9` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name10` varchar(20) CHARACTER SET utf8 DEFAULT NULL,"
+ "`name11` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name12` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name13` varchar(20) CHARACTER SET utf8 DEFAULT NULL,"
+ "`name14` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name15` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name16` varchar(20) CHARACTER SET utf8 DEFAULT NULL,"
+ "`name17` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name18` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name19` varchar(20) CHARACTER SET utf8 DEFAULT NULL,"
+ "`name20` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name21` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name22` varchar(20) CHARACTER SET utf8 DEFAULT NULL,"
+ "`name23` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name24` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name25` varchar(20) CHARACTER SET utf8 DEFAULT NULL,"
+ "`name26` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name27` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name28` varchar(20) CHARACTER SET utf8 DEFAULT NULL,"
+ "`name29` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name30` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name31` varchar(20) CHARACTER SET utf8 DEFAULT NULL,"
+ "`name32` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name33` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name34` varchar(20) CHARACTER SET utf8 DEFAULT NULL,"
+ "`name35` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name36` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name37` varchar(20) CHARACTER SET utf8 DEFAULT NULL,"
+ "`name38` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name39` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name40` varchar(20) CHARACTER SET utf8 DEFAULT NULL,"
+ "`name41` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name42` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name43` varchar(20) CHARACTER SET utf8 DEFAULT NULL,"
+ "`name44` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name45` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name46` varchar(20) CHARACTER SET utf8 DEFAULT NULL,"
+ "`name47` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name48` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name49` varchar(20) CHARACTER SET utf8 DEFAULT NULL,"
+ "`name50` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name51` varchar(20) CHARACTER SET utf8 DEFAULT NULL,`name52` varchar(20) CHARACTER SET utf8 DEFAULT NULL,"
+ "`name53` varchar(20) CHARACTER SET utf8 DEFAULT NULL, KEY `id` (`Id`),KEY `a` (`name`),KEY `b` (`name1`),KEY `c` (`name3`),KEY `d` (`name4`),KEY `e` (`name5`),"
+ "KEY `f` (`name6`),KEY `g` (`name2`)) ENGINE=MyISAM DEFAULT CHARSET=utf8";
Connection conn = null;
Statement stmt = null;
try {
conn = DriverManager.getConnection(url, userName, password);
stmt = conn.createStatement();
stmt.execute("DROP TABLE IF EXISTS `" + name + "`");
stmt.execute(sql);
}
catch(Exception e) {
e.printStackTrace();
}
finally {
try {
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
catch(Exception e) {
e.printStackTrace();
}
}
}
刚测了一下,好象没什么太大的变化,时间还多了几秒。
另补充说明我用的引擎是MyISAM
有可能的,conn = DriverManager.getConnection(url, userName, password);
是单用户单线程的连接,好像不太适合并发,批量这种情况的。
结帖