高效TDDL批量插入
使用TDDL客户端，向MySQL数据库插入1000条数据，插入时间10秒内。
常规方法
单条插入，代码如下：
|
1
2
3
4
5
6
7
8
|
@Test
@Transactional (TransactionMode.ROLLBACK) public void testInsters2() { for ( int i = 0 ; i < 10000 ; i++) { CheckItemDO checkItemDO = createCheckItem2(i); checkItemDao.insertCheckItem(checkItemDO); } } |
消费时间：
居然花了144秒，悲剧啊！
批量插入
第一种批量插入，ibatis批量插入数据-iterate
TDDL不支持SQL多条插入，INSERT INTO tableName (col_name,…) VALUES (expression,…),(…)；所以要添加备注，绕开语法校验，
/*+TDDL({type:executeByCondition,parameters:["seller_id=#sellerId#;l"],virtualTableName:jxc_check_item})*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<!-- 批量插入盘点明细 -->
<
insert
id
=
"insertCheckItems"
parameterClass
=
"java.util.Map"
>
/*+TDDL({type:executeByCondition,parameters:["seller_id=#sellerId#;l"],virtualTableName:jxc_check_item})*/
insert into
jxc_check_item (id, check_item_id, check_id, from_site, seller_id, warehouse_id, product_id, before_check_num, after_check_num, creator, modifier, gmt_create, gmt_check, gmt_modified)
values
<
iterate
conjunction
=
","
property
=
"checkItems"
>
<![CDATA[
(
#checkItems[].id#, #checkItems[].checkItemId#, #checkItems[].checkId#, #checkItems[].fromSite#, #checkItems[].sellerId#, #checkItems[].warehouseId#, #checkItems[].productId#, #checkItems[].beforeCheckNum#, #checkItems[].afterCheckNum#, #checkItems[].creator#, #checkItems[].modifier#, now(), now(), now()
)
]]>
</
iterate
>
</
insert
>
说白了这种方式就是拼接SQL，但是MySQL长度是有限制的，默认限制是1M，所以，如果一次性插入10000调数据，程序会出现假死状态。所有，要分批插入，一批插入1000调，插10次。Java代码如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
@Test
@Transactional
(TransactionMode.ROLLBACK)
public
void
testInsters() {
List<CheckItemDO> checkItemDOsTemp =
new
ArrayList<CheckItemDO>();
for
(
int
i =
0
; i <
10000
; i++) {
CheckItemDO checkItemDO = createCheckItem2(i);
checkItemDOsTemp.add(checkItemDO);
if
(i %
1000
==
0
) {
checkItemDao.insertCheckItems(sellerId, checkItemDOsTemp);
checkItemDOsTemp.clear();
}
}
}
消费时间： 只消耗了40秒不到，性能提升了3.5倍。
虽然时候性能已经提高了不少，但是，还有没有更给力的方法呢？答案是：Yes。
第二种批量插入，jdbc Rewrite Batched Statements批量插入
java代码如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
String url =
"jdbc:mysql://127.0.0.1:3306/dev?rewriteBatchedStatements=true"
;
String userName =
"root"
;
String password =
"1234"
;
Connection conn =
null
;
try
{
Class.forName(
"com.mysql.jdbc.Driver"
);
conn = DriverManager.getConnection(url, userName, password);
conn.setAutoCommit(
false
);
String sql =
"insert into t_user(id,uname) values(?,?)"
;
PreparedStatement prest = conn.prepareStatement(sql);
long
a = System.currentTimeMillis();
for
(
int
x =
0
; x <
100000
; x++) {
prest.setInt(
1
, x);
prest.setString(
2
,
"张三"
);
prest.addBatch();
}
prest.executeBatch();
conn.commit();
long
b = System.currentTimeMillis();
System.out.println(
"MySql批量插入10万条记录用时"
+ (b - a) +
" ms"
);
}
catch
(Exception ex) {
ex.printStackTrace();
}
finally
{
try
{
if
(conn !=
null
) conn.close();
}
catch
(SQLException e) {
e.printStackTrace();
}
}
这里，要求mysql url必须带rewriteBatchedStatements=true。此处是jdbc的代码，而本人的开发环境是基于ibatis的，下面为ibatic批量插入的实现代码：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
@Test
@Transactional
(TransactionMode.DISABLED)
public
void
testBatch2() {
List<CheckItemDO> checkItemDOsTemp =
new
ArrayList<CheckItemDO>();
for
(
int
i =
0
; i <
10000
; i++) {
CheckItemDO checkItemDO = createCheckItem2(i);
checkItemDOsTemp.add(checkItemDO);
}
final
List<CheckItemDO> checkItemDOs =
new
ArrayList<CheckItemDO>(checkItemDOsTemp);
CheckItemDaoImpl checkItemDaoImpl = (CheckItemDaoImpl) checkItemDao;
SqlBaseExecutorImp sqlBaseExecutorImp = (SqlBaseExecutorImp)checkItemDaoImpl.getSqlBaseExecutor();
sqlBaseExecutorImp.getSqlMapClientTemplate().execute(
new
SqlMapClientCallback() {
public
Object doInSqlMapClient(SqlMapExecutor executor) {
try
{
executor.startBatch();
for
(CheckItemDO checkItemDO : checkItemDOs) {
executor.insert(CheckItemDao.SQL_MAPPING_NAME_SPACE +
".insertCheckItem"
, checkItemDO);
}
executor.executeBatch();
}
catch
(SQLException e) {
e.printStackTrace();
return
false
;
}
return
true
;
};
});
}
消费时间：质的飞跃，3.384秒，性能提升了40倍，Perfect。
注意事项
第一，MySql的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入。
Mysql JDBC驱动，各个版本测试结果：
| MySql JDBC 驱动版本号 | 插入10万条数据用时 |
| 5.0.8 | 加了rewriteBatchedStatements参数，没有提高还是17.4秒 |
| 5.1.7 | 加了rewriteBatchedStatements参数，没有提高还是17.4秒 |
| 5.1.13 | 加了rewriteBatchedStatements参数，插入速度提高到1.6秒 |
第二，保证表插入的顺序，如果要批量插入A和B两个表的数据，要保证先批量插入表A，再批量插入表B。原因在于磁盘IO，应为MySQL会保证SQL的顺序性，不断在A,B表之间切换，相当与磁盘寻址要不断改变，相当损耗性能，这无异于单条插入。
第三，小心事务嵌套，jdbc Rewrite Batched Statements批量插入要求在一个事务中进行批量插入，所以，外面不应该再嵌套事务，在使用TestNG事务的时候，要声明为无事务，即：@Transactional(TransactionMode.DISABLED)。
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/13583.html