-----------------------------------
20170528 第二次编辑：主要是补充mysql相关内容。
-----------------------------------
mysql支持batch提交改进方案：声明：mysql仍然没有内部游标，让数据库支持executeBatch的方式处理。
MySql 的批量操作，要加rewriteBatchedStatements参数
引用“
MySql的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入。
例如： String connectionUrl="jdbc:mysql://192.168.1.100:3306/test?rewriteBatchedStatements=true" ;
还要保证Mysql JDBC驱的版本。MySql的JDBC驱动的批量插入操作性能是很优秀的。
”
原文链接：http://elf8848.iteye.com/blog/770032 (建议去看看这个链接的评论区)
参考资料：https://www.oschina.net/question/2553117_2162171?sort=time
20170528亲测，插入26663条数据，
加上rewriteBatchedStatements后，耗时：3734毫秒
不加rewriteBatchedStatements前，耗时：672551毫秒
mysql装在本机上，行字段数多，仅从本次测试看，性能提高了180倍。
---------------------------------------------------
第一版：20170526 原创
---------------------------------------------------
前言：做一个数据同步项目，要求：同步数据不丢失的情况下，提高插入性能。
项目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 配置信息
1 <?xml version="1.0" encoding="UTF-8"?> 2 <beans xmlns="http://www.springframework.org/schema/beans" 3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 4 xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx" 5 xsi:schemaLocation="http://www.springframework.org/schema/beans 6 http://www.springframework.org/schema/beans/spring-beans.xsd 7 http://www.springframework.org/schema/context 8 http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd"> 9 10 <!-- 自动扫描(自动注入) --> 11 <context:annotation-config/> 12 <context:component-scan base-package="com.company.dao"/> 13 14 <!-- 动态数据源 --> 15 <bean id="dataSource" class="com.company.dao.datasource.DataSource"> 16 <property name="myConfigFile" value="mySource.xml"/> 17 </bean> 18 19 <!-- mybatis配置 --> 20 <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> 21 <property name="dataSource" ref="dataSource"/> 22 <property name="mapperLocations" value="classpath*:mapper/*/*/*.xml"/> 23 <property name="configLocation" value="classpath:/mybatisConfig.xml"/> 24 </bean> 25 26 <!-- 自动创建映射器，不用单独为每个 mapper映射--> 27 <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> 28 <property name="basePackage" value="com.company.dao.mapper"/> 29 <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/> 30 </bean> 31 32 <!-- 事务管理器配置,单数据源事务 --> 33 <bean id="transactionManager" 34 class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> 35 <property name="dataSource" ref="dataSource"/> 36 </bean> 37 38 <tx:annotation-driven transaction-manager="transactionManager"/> 39 40 </beans>
再上mybatisConfig.xml（在本项目中，我没有设置setting。最终采用的局部批处理，因此未设置全局批处理，具体原因后面再说。）
1 <?mapper.xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> 3 <configuration> 4 5 <settings> 6 <!-- 配置默认的执行器。SIMPLE 就是普通的执行器；REUSE 执行器会重用预处理语句（prepared statements）； BATCH 执行器将重用语句并执行批量更新。--> 7 <setting name="defaultExecutorType" value="BATCH"/> 8 <!--详见：http://www.mybatis.org/mybatis-3/zh/configuration.html--> 9 </settings> 10 11 <!-- 别名列表 --> 12 <typeAliases> 13 <!-- typeAliases 中的配置都是配置别名，在此就不贴出来了 --> 14 </typeAliases> 15 16 </configuration>
这样子设置好后，在BaseService开放saveBatch(List<T> list)方法
1 @Override 2 public void save(List<T> list) { 3 for (int i = 0;i < list.size();i++){ 4 mapper.insert(list.get(i)); 5 } 6 } 7 8 @Override 9 public void saveBatch(List<T> list) { 10 int size = list.size(); 11 int unitNum = 500; 12 int startIndex = 0; 13 int endIndex = 0; 14 while (size > 0){ 15 if(size > unitNum){ 16 endIndex = startIndex+unitNum; 17 }else { 18 endIndex = startIndex+size; 19 } 20 List<T> insertData = list.subList(startIndex,endIndex); 21 save(insertData); 22 size = size - unitNum; 23 startIndex = endIndex; 24 } 25 }
虽然看上去是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
1 SqlSession session = sqlSessionFactory.openSession(); 2 try { 3 BlogMapper mapper = session.getMapper(BlogMapper.class); 4 // do work 5 } finally { 6 session.close(); 7 }
后查阅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()方法签名设置允许你选择这些可选中的任何一个组合。
1 SqlSession openSession() 2 SqlSession openSession(boolean autoCommit) 3 SqlSession openSession(Connection connection) 4 SqlSession openSession(TransactionIsolationLevel level) 5 SqlSession openSession(ExecutorType execType,TransactionIsolationLevel level) 6 SqlSession openSession(ExecutorType execType) 7 SqlSession openSession(ExecutorType execType, boolean autoCommit) 8 SqlSession openSession(ExecutorType execType, Connection connection) 9 Configuration getConfiguration();
因此出来了局部批处理第一套代码实现方式：
1 public static void sqlSession(List<Student> data) throws IOException { 2 String resource = "mybatis-dataSource.xml"; 3 InputStream inputStream = null; 4 SqlSession batchSqlSession = null; 5 try{ 6 inputStream = Resources.getResourceAsStream(resource); 7 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); 8 batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false); 9 int batchCount = 500;//每批commit的个数 10 for(int index = 0; index < data.size();index++){ 11 Student stu = data.get(index); 12 batchSqlSession.getMapper(Student.class).insert(stu); 13 if(index !=0 && index%batchCount == 0){ 14 batchSqlSession.commit(); 15 } 16 } 17 batchSqlSession.commit(); 18 }catch (Exception e){ 19 e.printStackTrace(); 20 }finally { 21 if(batchSqlSession != null){ 22 batchSqlSession.close(); 23 } 24 if(inputStream != null){ 25 inputStream.close(); 26 } 27 } 28 }
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE configuration 3 PUBLIC "-//mybatis.org//DTD Config 3.0//EN" 4 "http://mybatis.org/dtd/mybatis-3-config.dtd"> 5 <configuration> 6 <environments default="development"> 7 <environment id="development"> 8 <transactionManager type="JDBC"/> 9 <dataSource type="POOLED"> 10 <property name="driver" value="${driver}"/> 11 <property name="url" value="${url}"/> 12 <property name="username" value="${username}"/> 13 <property name="password" value="${password}"/> 14 </dataSource> 15 </environment> 16 </environments> 17 <mappers> 18 <mapper resource="org/mybatis/example/Student.xml"/> 19 </mappers> 20 </configuration>
已经在Spring-Mybatis.xml 中配置了SQLSessionFactory，那我为何还要自己去创建SQLSessionFactory呢？因此继续改良代码
1 public static void mybatisSqlSession(List<Student> data){ 2 DefaultSqlSessionFactory sqlSessionFactory = (DefaultSqlSessionFactory) ServiceBeanConstant.CTX.getBean("sqlSessionFactory"); 3 SqlSession batchSqlSession = null; 4 try{ 5 batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false); 6 int batchCount = 500;//每批commit的个数 7 for(int index = 0; index < data.size();index++){ 8 Student stu = data.get(index); 9 batchSqlSession.getMapper(StudentMapper.class).insert(stu); 10 if(index !=0 && index%batchCount == 0){ 11 batchSqlSession.commit(); 12 } 13 } 14 batchSqlSession.commit(); 15 }catch (Exception e){ 16 e.printStackTrace(); 17 }finally { 18 if(batchSqlSession != null){ 19 batchSqlSession.close(); 20 } 21 } 22 }
这个版本的局部批处理插入是比较满意的，最终采用的方式也是这个版本。
下面放出在IService接口定义和Service的具体实现代码：
IService接口定义
1 /** 2 * 批处理插入数据(方法内部定义500条为一个批次进行提交) 3 * 使用注意事项：必须在XxxMappper.xml中实现<insert id="insert" ...>....<insert/>的sql 4 * @param data 批量插入的数据 5 * @param mClass 调用的XxxMaperr.class 6 * @auth robin 7 * Created on 2016/3/14 8 */ 9 void saveBatch(List<T> data,Class mClass);
Service实现
1 @Override 2 public void saveBatch(List<T> data,Class mClass) { 3 DefaultSqlSessionFactory sqlSessionFactory = (DefaultSqlSessionFactory) ServiceBeanConstant.CTX.getBean("sqlSessionFactory"); 4 SqlSession batchSqlSession = null; 5 try{ 6 batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false); 7 int batchCount = 500;//每批commit的个数 8 for(int index = 0; index < data.size();index++){ 9 T t = data.get(index); 10 ((BaseMapper)batchSqlSession.getMapper(mClass)).insert(t); 11 if(index !=0 && index%batchCount == 0){ 12 batchSqlSession.commit(); 13 } 14 } 15 batchSqlSession.commit(); 16 }catch (Exception e){ 17 e.printStackTrace(); 18 }finally { 19 if(batchSqlSession != null){ 20 batchSqlSession.close(); 21 } 22 } 23 }
局部和全局批处理插入对比：局部批处理，可以对特定一类的方法，进行数据批处理，不会影响其他DML语句，其他DML语句，可以正常返回影响DB_TABLE的行数。
!这样既能针对特殊需求(批处理)支持，也能支持未来需要返回影响数据行的要求。
注意：使用批处理方式进行DML操作，是无法反馈影响DB_TABLE行数的数据。无论是局部批处理还是java自带的批处理方式，皆无法反馈DB_TABLE count。
补充完善：
在我的Service实现中，通过注入的方式，获取mapper的实例
1 public class BaseService<MAPPER extends BaseMapper, T, PK extends Serializable> implements IBaseService<T, PK> { 2 3 protected T tt; 4 /** 5 * 实体操作的自动注入Mapper(随初始化一同注入，必须用set方法) 6 */ 7 protected MAPPER mapper; 8 9 public MAPPER getMapper() { 10 return mapper; 11 } 12 13 @Autowired 14 public void setMapper(MAPPER mapper) { 15 this.mapper = mapper; 16 } 17 //后续代码略 18 }
前面的Service saveBatch方法中，还需要传入指定的Mapper.class.对本项目其他开发者来说，与之前的环境相比，多传一个参数感觉别扭。
那么为何我不继续封装，外部无需传入Mapper.class，而是通过内部注入的mapper实例获取Mapper.class.
改良后的代码：
1 @Override 2 public T saveBatch(List<T> data) { 3 T back = null; 4 DefaultSqlSessionFactory sqlSessionFactory = (DefaultSqlSessionFactory) ServiceBeanConstant.CTX.getBean("sqlSessionFactory"); 5 SqlSession batchSqlSession = null; 6 try{ 7 batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false); 8 int batchCount = 500;//每批commit的个数 9 for(int index = 0; index < data.size();index++){ 10 T t = data.get(index); 11 back = t; 12 Class<?>[] interfaces=mapper.getClass().getInterfaces(); 13 Class clazz = null; 14 for (int i=0;i<interfaces.length;i++){ 15 if(BaseMapper.class.isAssignableFrom(interfaces[i])){ 16 clazz = interfaces[i]; 17 } 18 } 19 if(clazz == null){ 20 throw new Exception("user-defined exception:mapper not implements interfaces com.company.dao.mapper.BaseMapper"); 21 } 22 BaseMapper baseMapper = (BaseMapper) batchSqlSession.getMapper(clazz); 23 baseMapper.insert(t); 24 if(index !=0 && index%batchCount == 0){ 25 batchSqlSession.commit(); 26 } 27 } 28 batchSqlSession.commit(); 29 }catch (Exception e){ 30 e.printStackTrace(); 31 }finally { 32 if(batchSqlSession != null){ 33 batchSqlSession.close(); 34 } 35 return back; 36 } 37 }
这里对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 语句块
我的实现：
1 <insert id="insertBatch" parameterType="java.util.List"> 2 BEGIN 3 <foreach collection="list" item="item" index="index" separator=";" > 4 INSERT INTO TABLE.STUDENT (ID,AGE,NAME,STU_ID) VALUES 5 ( DEMO.SEQ_EID.NEXTVAL,#{item.age},#{item.name},#{item.stuId} ) 6 </foreach> 7 ;END ; 8 </insert>
调用方式：
1 @Override 2 public void saveBatch(List<T> list) { 3 int size = list.size(); 4 int unitNum = 500; 5 int startIndex = 0; 6 int endIndex = 0; 7 while (size > 0){ 8 if(size > unitNum){ 9 endIndex = startIndex+unitNum; 10 }else { 11 endIndex = startIndex+size; 12 } 13 List<T> insertData = list.subList(startIndex,endIndex); 14 mapper.insertBatch(insertData); 15 size = size - unitNum; 16 startIndex = endIndex; 17 }
---------------------------------------------------------------------
四、java自带的批处理方式
废话不多说，直接上代码
1 package DB; 2 3 import base.Student; 4 5 import java.sql.Connection; 6 import java.sql.DriverManager; 7 import java.sql.PreparedStatement; 8 import java.sql.SQLException; 9 import java.util.ArrayList; 10 import java.util.List; 11 12 /** 13 * Created by robin on 2016/5/23. 14 * 15 * @author robin 16 */ 17 public class InsertTableDemo { 18 19 public static void main(String args[]) throws SQLException { 20 Connection connection = null; 21 List<Student> dataList = getDataList(100000); 22 long startTime = 0; 23 try{ 24 connection = getConn(); 25 startTime=System.currentTimeMillis(); 26 connection.setAutoCommit(false); 27 PreparedStatement statement = connection.prepareStatement("INSERT INTO STUDENT (ID,AGE,NAME,STU_ID) VALUES ( DEMO.SEQ_EID.NEXTVAL, ?,?,? ) "); 28 int num = 0; 29 for (int i = 0;i< dataList.size();i++){ 30 Student s = dataList.get(i); 31 statement.setInt(1, s.getAge()); 32 statement.setString(2, s.getName()); 33 statement.setString(3, s.getStuId()); 34 statement.addBatch(); 35 num++; 36 if(num !=0 && num%500 == 0){ 37 statement.executeBatch(); 38 connection.commit(); 39 num = 0; 40 } 41 } 42 statement.executeBatch(); 43 connection.commit(); 44 }catch (Exception e){ 45 e.printStackTrace(); 46 connection.rollback(); 47 }finally { 48 if(connection != null){ 49 connection.close(); 50 } 51 long endTime=System.currentTimeMillis(); 52 System.out.println("方法执行时间："+(endTime-startTime)+"ms"); 53 } 54 55 } 56 57 public static Connection getConn(){ 58 String driver = "oracle.jdbc.driver.OracleDriver"; 59 String url = "jdbc:oracle:thin:@//ip:port/DMEO"; //DMEO为数据库名 60 String user = "user"; 61 String password = "pwd"; 62 try{ 63 Class.forName(driver); 64 Connection conn = DriverManager.getConnection(url, user, password); 65 return conn; 66 } catch (ClassNotFoundException e) { 67 e.printStackTrace(); 68 } catch (SQLException e) { 69 e.printStackTrace(); 70 } 71 return null; 72 } 73 public static List<Student> getDataList(int f){ 74 List<Student> data = new ArrayList<>(); 75 for (int i =0;i<f;i++){ 76 Student s = new Student(""+i,"小明" + i,i); 77 data.add(s); 78 } 79 return data; 80 } 81 82 83 }
这种批量插入大量数据的方式，性能上最好。但是因为我们小组代码管理所限制，因此这种方式不使用。
------------------------------------------------------------------------
五、其他方式
现在已经忘了，其他方式到底使用过哪些，但总归是比以上四种效果都更差，所以没什么印象了。
如果各位，还有什么其他更好的批量插入数据的方式，欢迎加入讨论，集思广益。
以上就是这两天，对在原项目基础上不进行大变动的基础上，提供批处理插入数据的所思所行。
-------------------------------------------------------------------------------
后记：
这里吐槽一句：希望大家不要把未经过自己验证的东西，言之凿凿地写到博客中去。
在我做批处理这件事的时候，领导也在参阅网上的博客。其中有一篇博客，说自己在oracle中批量插入数据，采用foreach insert into (field1,field2,...) values (v11,v12,...),(v21,v22,...) ,(v31,v32,...),...也可以。
虽然我明知不行，但是无可奈何还是要去演示给领导看，在oracle中，这种写法确实不适用。
领导问我为何他说可以，我想我也只能回答：他抄别人的博客呗，抄来抄去都不自己实践验证，就想当然地写到博客里。
所以，如果你看完了我这篇分享，希望您也能写个demo验证下，起码可以加深自己的理解。
感谢领导和DB同事，在此过程中的帮助。
以上内容，都经过本人实践验证过。若转发，请在标题上标记[转]，并注明原文链接：http://www.cnblogs.com/robinjava77/p/5530681.html，作者名称：robin。并在文章最后一行附上本句话。否则，作者保留追究的权利。