赞
踩
软件开发过程中需要批量插入数据的场景有几种:
上面这些场景都是长时间的处理过程,在软件设计时需要将其设计成带进度展示的异步任务(同步任务微服务有http请求超时的风险)。异步任务可以使用消息框架。
使用批量插入技术能提升数据持久化的性能。用mybatis有两种批量插入数据的方式可选:1. 拼接批量插入多条数据的SQL. 2. 使用Batch Insert技术。
/**
* 插入数据列表
*
* @param dataList 数据列表
*/
void insertDataList(@Param("list") List<BatchData> dataList);
<insert id="batchInsertData" useGeneratedKeys="true" keyColumn="id" keyProperty="id"> INSERT INTO t_batch_data ( column1, column2, column3, column4, column5, column6, column7, column8, column9, column10 ) VALUES <foreach item="data" collection="list" separator=","> ( #{data.column1}, #{data.column2}, #{data.column3}, #{data.column4}, #{data.column5}, #{data.column6}, #{data.column7}, #{data.column8}, #{data.column9}, #{data.column10} ) </foreach> </insert>
可以看到,XML配置文件使用 foreach 对多条数据做了拼接,Value部分用逗号分隔。拼接后的SQL样式:
INSERT INTO t_batch_data ( column1, column2, column3, column4, column5, column6, column7, column8, column9, column10 ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
可以看到,拼接的SQL长度跟批量插入数据的条数和单条数据的字段数相关。对于像postgres这样限定了参数个数的数据库,需要提前对大批量数据做拆分处理。
下面的示例代码对批量数据按200条一组做拆分,然后再入库。
public long foreachBatchInsert(@PathVariable("amount") int amount) {
long beginTime = System.currentTimeMillis();
List<BatchData> dataList = buildDataList(amount);
// 大数据分批处理入库
List<List<BatchData>> dataGroup = ListUtil.splitList(dataList, 200);
for (List<BatchData> group : dataGroup) {
batchInsertMapper.insertDataList(group);
}
return System.currentTimeMillis() - beginTime;
}
/**
* 插入单条数据
*
* @param data PO数据
*/
void insertData(@Param("data") BatchData data);
<insert id="insertData" useGeneratedKeys="true" keyProperty="data.id" keyColumn="id"> INSERT INTO t_batch_data ( column1, column2, column3, column4, column5, column6, column7, column8, column9, column10 ) VALUES ( #{data.column1}, #{data.column2}, #{data.column3}, #{data.column4}, #{data.column5}, #{data.column6}, #{data.column7}, #{data.column8}, #{data.column9}, #{data.column10} ) </insert>
映射实例接口和SQL代码与插入单个对象无异。关键代码在应用层。
public long mybatisBatchInsert(@PathVariable("amount") int amount) { SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH, false); long beginTime = System.currentTimeMillis(); try { BatchInsertMapper insertMapper = session.getMapper(BatchInsertMapper.class); List<BatchData> dataList = buildDataList(amount); for (BatchData data : dataList) { insertMapper.insertData(data); } session.commit(); session.clearCache(); } catch (Exception e) { session.rollback(); } finally { session.close(); } return System.currentTimeMillis() - beginTime; }
查看打印出执行的SQL语句:
INSERT INTO t_batch_data ( column1, column2, column3, column4, column5, column6, column7, column8, column9, column10 ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
拦截StatementHandler的prepare执行方法,可以看到只执行了一次预编译。批量插入不会出现参数个数超限或者SQL语句超长的问题。
在postgres数据库中新建了一个包含10个text类型字段的表(t_batch_data)验证了一下,插入20万条数据时间都在15秒左右,相差不大。方案1必须做分组(参数个数超过限制);方案二本身是调用的mapper的插入单个对象的接口, 不需要做分批。
如表字段是固定的,字段数量也不大可以使用方案一;如表字段数量不固定(元数据驱动)推荐使用第二种方案。第二种方案在代码执行到session.commit()时数据才真正入库,如果在这之前使用数据库的数据或者回填的自增ID是有问题的。
实际产品开发过程中,即使采用第二种方案也建议对大数量做分组处理,将单次操作数据库的时间控制在2秒以内。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。