批量插入数据(基于Mybatis的实现-Oracle)
前言:做一个数据同步项目,要求:同步数据不丢失的情况下,提高插入性能。
项目DB框架:Mybatis。DataBase:Oracle。
----------------------------------------------------------------------------
批量插入数据方式:
一、Mybatis 全局设置批处理;
二、Mybatis 局部设置批处理;
三、Mybatis foreach批量插入:
①SELECT UNION ALL;
②BEGIN INSERT INTO ...;INSERT INTO...;...;END;
四、java自带的批处理插入;
五、其他方式
-----------------------------------------------------------------------------
先说结论:Mybatis(全局/局部)批处理和java自带的批处理 性能上差不多,属于最优处理办法,我这边各种测试后,最后采用Mybatis局部批处理方式。
一、Mybatis 全局设置批处理
先上Spring-Mybatis.xml 配置信息
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
再上mybatisConfig.xml(在本项目中,我没有设置setting。最终采用的局部批处理,因此未设置全局批处理,具体原因后面再说。)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
这样子设置好后,在BaseService开放saveBatch(List<T> list)方法
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
虽然看上去是500条记录,一次次INSERT INTO,但由于在全局已经设置Mybatis是批处理执行器,所以这500条INSERT INTO只会与Oracle数据库通信一次。
全局设置批处理的局限性在哪里呢?
先附上mybatis官方的讨论列表中最很关键的一句:“If the BATCH executor is in use, the update counts are being lost. ”
设置全局批处理后,DB里的insert、Update和delete方法,都无法返回进行DML影响DB_TABLE的行数。
1.insert 无法返回影响的行数,这个好解决,一个批处理放在一个事务里,记录批处理失败次数,总数-批处理失败次数*单位批处理数据量,就能得到insert 影响DB_TABLE的行数;
2.但是update和delete就无法很简单的去统计影响行数了,如果做反复查询,反而降低了效率,得不偿失。
虽现在的项目尚未有需要反馈影响DB_TABLE行数的需求,但是为了更灵活,我们放弃了全局批处理的方式。
!这里提个疑问:为什么Mybatis官方,不将批处理的选择方式下沉到方法级别?方便开发者根据实际情况,灵活选择。我觉得这是个可以改进的地方,如有机会,可看源码去进行改进。
---------------------------------------------------------------------------------------------------------
二、Mybatis局部批处理方式
由于领导说全局批处理方式,不够灵活,不适宜项目所需,要另想办法支持。但是java自带的批处理,因为项目代码管理的要求,也不能采用。因此,在仔细阅读官方文档后,设想自己能否获取SQLSession后openSession,将这个会话设置为批处理呢?
先看MyBatis官方网站(须FanQiang):http://www.mybatis.org/mybatis-3/zh/getting-started.html
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
后查阅Mybatis java API(须FanQiang): http://www.mybatis.org/mybatis-3/zh/java-api.html
现在你有一个 SqlSessionFactory,可以用来创建 SqlSession 实例。
SqlSessionFactory
SqlSessionFactory 有六个方法可以用来创建 SqlSession 实例。通常来说,如何决定是你 选择下面这些方法时:
- Transaction (事务): 你想为 session 使用事务或者使用自动提交(通常意味着很多 数据库和/或 JDBC 驱动没有事务)?
- Connection (连接): 你想 MyBatis 获得来自配置的数据源的连接还是提供你自己
- Execution (执行): 你想 MyBatis 复用预处理语句和/或批量更新语句(包括插入和 删除)
重载的 openSession()方法签名设置允许你选择这些可选中的任何一个组合。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
因此出来了局部批处理第一套代码实现方式:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
已经在Spring-Mybatis.xml 中配置了SQLSessionFactory,那我为何还要自己去创建SQLSessionFactory呢?因此继续改良代码
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
这个版本的局部批处理插入是比较满意的,最终采用的方式也是这个版本。
下面放出在IService接口定义和Service的具体实现代码:
IService接口定义
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Service实现
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
局部和全局批处理插入对比:局部批处理,可以对特定一类的方法,进行数据批处理,不会影响其他DML语句,其他DML语句,可以正常返回影响DB_TABLE的行数。
!这样既能针对特殊需求(批处理)支持,也能支持未来需要返回影响数据行的要求。
注意:使用批处理方式进行DML操作,是无法反馈影响DB_TABLE行数的数据。无论是局部批处理还是java自带的批处理方式,皆无法反馈DB_TABLE count。
补充完善:
在我的Service实现中,通过注入的方式,获取mapper的实例
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
前面的Service saveBatch方法中,还需要传入指定的Mapper.class.对本项目其他开发者来说,与之前的环境相比,多传一个参数感觉别扭。
那么为何我不继续封装,外部无需传入Mapper.class,而是通过内部注入的mapper实例获取Mapper.class.
改良后的代码:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
这里对mapper实例进行一个简短的说明:
1.mapper实例是通过java动态代理来实例化的;
2.mapper的SQLSession是使用mybatis统一的配置实例的;
3.mapper的默认执行器是SIMPLE(普通的执行器);
-------------------------------------------------------------------------------------
三、Mybatis foreach批量插入
Mybatis foreach 批量插入,如果批量插入的数据量大,不得不说这真是一个非常糟糕的做法。
无论是SELECT ** UNION ALL 还是BEGIN ...;END; ,相对而言后者比前者稍微好点。
放出DB和我测试的结果:
耗时 | 占当时整个数据库CPU百分比 | 说明 |
15.5 | 98.33 | union all方式拼接插入 |
16.4 | 97.75 | begin end方式插入块 |
1.54 | 64.81 | java 自带的batch方式插入 |
①foreach union all的批量插入,现已有大量的博客资源可供参考,我就不贴出自己的实现方式了。
如果有兴趣可以参阅:http://blog.csdn.net/sanyuesan0000/article/details/19998727 (打开浏览器,复制url)
这篇博客。BEGIN END的方式,也是从这篇博客中得到启发。只不过他是把BEGIN END用在update中。
②foreach begin end 语句块
我的实现:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
调用方式:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
---------------------------------------------------------------------
四、java自带的批处理方式
废话不多说,直接上代码
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
这种批量插入大量数据的方式,性能上最好。但是因为我们小组代码管理所限制,因此这种方式不使用。
------------------------------------------------------------------------
五、其他方式
现在已经忘了,其他方式到底使用过哪些,但总归是比以上四种效果都更差,所以没什么印象了。
如果各位,还有什么其他更好的批量插入数据的方式,欢迎加入讨论,集思广益。
以上就是这两天,对在原项目基础上不进行大变动的基础上,提供批处理插入数据的所思所行。
-------------------------------------------------------------------------------
后记:
这里吐槽一句:希望大家不要把未经过自己验证的东西,言之凿凿地写到博客中去。
在我做批处理这件事的时候,领导也在参阅网上的博客。其中有一篇博客,说自己在oracle中批量插入数据,采用foreach insert into (field1,field2,...) values (v11,v12,...),(v21,v22,...) ,(v31,v32,...),...也可以。
虽然我明知不行,但是无可奈何还是要去演示给领导看,在oracle中,这种写法确实不适用。
领导问我为何他说可以,我想我也只能回答:他抄别人的博客呗,抄来抄去都不自己实践验证,就想当然地写到博客里。
所以,如果你看完了我这篇分享,希望您也能写个demo验证下,起码可以加深自己的理解。