最近在優(yōu)化大批量數(shù)據(jù)插入的性能問(wèn)題僻肖。
項(xiàng)目原來(lái)使用的大批量數(shù)據(jù)插入方法是Mybatis的foreach拼接SQL的方法袁辈。
我發(fā)現(xiàn)不管改成Mybatis Batch提交或者原生JDBC Batch的方法都不起作用，實(shí)際上在插入的時(shí)候仍然是一條條記錄的插，速度遠(yuǎn)不如原來(lái)Mybatis的foreach拼接SQL的方法。這對(duì)于常理來(lái)說(shuō)是非常不科學(xué)的舰绘。
下面先羅列一下三種插入方式：
public class NotifyRecordDaoTest extends BaseTest {
@Resource(name = "masterDataSource")
private DataSource dataSource;
@Test
public void insert() throws Exception {
Connection connection = dataSource.getConnection();
connection.setAutoCommit(false);
String sql = "insert into notify_record(" +
" partner_no," +
" trade_no, loan_no, notify_times," +
" limit_notify_times, notify_url, notify_type,notify_content," +
" notify_status)" +
" values(?,?,?,?,?,?,?,?,?) ";
PreparedStatement statement = connection.prepareStatement(sql);
for (int i = 0; i < 10000; i++) {
statement.setString(1, "1");
statement.setString(2, i + "");
statement.setInt(3, 1);
statement.setInt(4, 1);
statement.setString(5, "1");
statement.setString(6, "1");
statement.setString(7, "1");
statement.setString(8, "1");
statement.setString(9, "1");
statement.addBatch();
}
long start = System.currentTimeMillis();
statement.executeBatch();
connection.commit();
connection.close();
statement.close();
System.out.println(System.currentTimeMillis() - start);
}
@Test
public void insertB() {
List<NotifyRecordEntity> notifyRecordEntityList = Lists.newArrayList();
for (int i = 0; i < 10000; i++) {
NotifyRecordEntity record = new NotifyRecordEntity();
record.setLastNotifyTime(new Date());
record.setPartnerNo("1");
record.setLimitNotifyTimes(1);
record.setNotifyUrl("1");
record.setLoanNo("1");
record.setNotifyContent("1");
record.setTradeNo("" + i);
record.setNotifyTimes(1);
record.setNotifyType(EnumNotifyType.DAIFU);
record.setNotifyStatus(EnumNotifyStatus.FAIL);
notifyRecordEntityList.add(record);
}
long start = System.currentTimeMillis();
Map<String, Object> params = Maps.newHashMap();
params.put("notifyRecordEntityList", notifyRecordEntityList);
DaoFactory.notifyRecordDao.insertSelectiveList(params);
System.out.println(System.currentTimeMillis() - start);
}
@Resource
SqlSessionFactory sqlSessionFactory;
@Test
public void insertC() {
SqlSession sqlsession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
NotifyRecordDao notifyRecordDao = sqlsession.getMapper(NotifyRecordDao.class);
int num = 0;
for (int i = 0; i < 10000; i++) {
NotifyRecordEntity record = new NotifyRecordEntity();
record.setLastNotifyTime(new Date());
record.setPartnerNo("1");
record.setLimitNotifyTimes(1);
record.setNotifyUrl("1");
record.setLoanNo("1");
record.setNotifyContent("1");
record.setTradeNo("s" + i);
record.setNotifyTimes(1);
record.setNotifyType(EnumNotifyType.DAIFU);
record.setNotifyStatus(EnumNotifyStatus.FAIL);
notifyRecordDao.insert(record);
num++;
// if(num>=1000){
// sqlsession.commit();
// sqlsession.clearCache();
// num=0;
// }
}
long start = System.currentTimeMillis();
sqlsession.commit();
sqlsession.clearCache();
sqlsession.close();
System.out.println(System.currentTimeMillis() - start);
}
}
測(cè)試插入一萬(wàn)條數(shù)據(jù)的發(fā)現(xiàn)除了拼接SQL的方式需要用5秒多的時(shí)間外，Mybatis Batch和原生JDBC Batch都需要50多秒葱椭，怎么想都覺(jué)得不可能捂寿，寫法沒(méi)有問(wèn)題一定是數(shù)據(jù)庫(kù)或者數(shù)據(jù)庫(kù)連接配置上有問(wèn)題。
后來(lái)才發(fā)現(xiàn)要批量執(zhí)行的話孵运，JDBC連接URL字符串中需要新增一個(gè)參數(shù)：rewriteBatchedStatements=true
master.jdbc.url=jdbc:mysql://112.126.84.3:3306/outreach_platform?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&rewriteBatchedStatements=true
關(guān)于rewriteBatchedStatements這個(gè)參數(shù)介紹：
MySQL的JDBC連接的url中要加rewriteBatchedStatements參數(shù)秦陋，并保證5.1.13以上版本的驅(qū)動(dòng)，才能實(shí)現(xiàn)高性能的批量插入治笨。
MySQL JDBC驅(qū)動(dòng)在默認(rèn)情況下會(huì)無(wú)視executeBatch()語(yǔ)句驳概，把我們期望批量執(zhí)行的一組sql語(yǔ)句拆散，一條一條地發(fā)給MySQL數(shù)據(jù)庫(kù)旷赖，批量插入實(shí)際上是單條插入顺又，直接造成較低的性能。
只有把rewriteBatchedStatements參數(shù)置為true, 驅(qū)動(dòng)才會(huì)幫你批量執(zhí)行SQL
另外這個(gè)選項(xiàng)對(duì)INSERT/UPDATE/DELETE都有效
添加rewriteBatchedStatements=true這個(gè)參數(shù)后的執(zhí)行速度比較：
同個(gè)表插入一萬(wàn)條數(shù)據(jù)時(shí)間近似值：
JDBC BATCH 1.1秒左右 > Mybatis BATCH 2.2秒左右 > 拼接SQL 4.5秒左右
