10.10.6 大数据量插入优化
在很多涉及支付和金融相关的系统中，夜间会进行批处理，在批处理的一开始或最后一般需要将数据回库，因为应用和数据库通常部署在不同的服务器，而且应用所在的服务器一般也不会去安装oracle客户端，同时为了应用管理和开发模式统一，很多会利用mybatis的foreach collection特性，如下：
<insert id="batchInsertStudent" parameterType="List">
insert into /*+ append_values */ t_student(id,name)
<foreach collection="list" item="item" index="index" separator="union all">
select #{item.id}, #{item.name} from dual
</foreach>
</insert>
还有一些开发人员会仿照mysql的写法，拼接成一个巨大的SQL，一次性提交给oracle执行，如下：
这些写法会生成很长的SQL语句，严重浪费客户端内存和oracle服务器共享池，如果这段期间需要生成AWR报告的话，没有这些语句几十秒就完成了，有这些语句的时候可能要十几分钟，生成的AWR文件就有十几兆，并且oracle服务器CPU利用率一直高负载。如果仅仅是如此也就罢了，最主要是这些看似优化的方法实际上性能仅仅比一条条提交提升快了几倍而已，对于一次性加载几十万、几百万行来说，并没有采用真正高效的做法。对于此类需要加载大量数据的方法，如本书第7章所述，应尽可能采用特殊优化的接口而不是为通用CRUD目的实现的接口，比如mybatis提供了批量执行器ExecutorType.BATCH，JDBC也提供了标准的批处理接口。
mybatis批量执行器的实现如下：
<insert id="insertBatch" parameterType="chapter10.batch.pojo.User">
insert into EMP (EMPNO,ENAME,JOB,MGR,SAL,COMM,DEPTNO)
values (#{empno,jdbcType=BIGINT},……,#{deptno,jdbcType=BIGINT})
</insert>
SqlSession session2 = sqlMapper.openSession(ExecutorType.BATCH, false);// 批处理方式 手动提交事务
UserMapper userDao2 = session2.getMapper(UserMapper.class);
try {
long t1 = System.currentTimeMillis();
for (int i = 0; i < 1000000; i++) {
User user_new = new User();
user_new.setComm(i % 10000);
……
user_new.setSal(i % 1000);
userDao2.insertBatch(user_new);
if (i % 10000 == 0) {
session2.commit();
}
}
System.out.println(System.currentTimeMillis() - t1 + "ms");
} finally {
session2.commit();
session2.close();
}
oracle jdbc批处理的实现如下：
Connection connection = dbpool.getConnection();
connection.setAutoCommit(false);
PreparedStatement preparedStatement = connection.prepareStatement("insert into EMP (EMPNO,ENAME,JOB,MGR,SAL,COMM,DEPTNO) values (?,?,?,?,?,?,?)");
long t1 = System.currentTimeMillis();
for (int i = 0; i < 1000000; i++) {
User user_new = new User();
user_new.setComm(i % 10000);
……
preparedStatement.setInt(7, user_new.getDeptno());
preparedStatement.addBatch();
if (i % 10000 == 0) {
preparedStatement.executeBatch();
connection.commit();
}
}
preparedStatement.close();
加载100w数据，使用jdbc Batch需要3秒左右，mybatis batch（标准JDBC批处理）9.2秒，mybatis foreach每5000条（1w时报java.sql.SQLException: ORA-01745: 无效的主机/绑定变量名）提交一次，需要执行203秒左右，甚至不如每行一次、每10000行提交一次的效率，并且子游标的共享内存占用了27M，固定内存加起来占了14M左右，如下：
SQL> select o.sql_id, sharable_mem, persistent_mem, runtime_mem
2 from v$sql o
3 where o.sql_text like '%insert into EMP (%'
4 and sql_text not like '%v$sql%'
5 ;
SQL_ID SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM
------------- ------------ -------------- -----------
bqwhad7f0gxxd 27473066 9127256 4925984
上面演示了jdbc针对oracle加载大数据量的优化，除此之外，从oracle 12.2开始，instant client中包含了sql loader，可以直接使用。从oracle 19c开始，jdbc还支持直接路径加载，请参考oracle 19c jdbc之Reactive Streams Ingestion (RSI) Library。
针对mysql，一般来说，大数据量加载推荐使用load local infile实现，性能最佳。但是load local infile要使用mysql的扩展语法。实际上mysql也支持batch特性，只是该特性几乎被开发都忽略了，它需要通过增加rewriteBatchedStatements=true连接参数，如下：
Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; //MySql的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入。 //优化插入性能，用JDBC的addBatch方法，但是注意在连接字符串加上面写的参数。 //例如： String connectionUrl="jdbc:mysql://192.168.1.100:3306/test?rewriteBatchedStatements=true" ; String sql = "insert into arp_standard(guid, devicebrand, devicename, deviceip, ipaddress, " + "macaddress, createtime) values(?,?,?,?,?,?,?)"; try{ conn = DBConnection.getConnection(); ps = conn.prepareStatement(sql); //优化插入第一步 设置手动提交 conn.setAutoCommit(false); int len = list.size(); for(int i=0; i<len; i++) { ps.setString(1, list.get(i).getGuid()); ps.setString(2, list.get(i).getDeviceBrand()); ps.setString(3, list.get(i).getDeviceName()); ps.setString(4, list.get(i).getDeviceIp()); ps.setString(5, list.get(i).getIpAddress()); ps.setString(6, list.get(i).getMacAddress()); ps.setString(7, list.get(i).getCreateTime()); //if(ps.executeUpdate() != 1) r = false; 优化后，不用传统的插入方法了。 //优化插入第二步 插入代码打包，等一定量后再一起插入。 ps.addBatch(); //if(ps.executeUpdate() != 1)result = false; //每200次提交一次 if((i!=0 && i%200==0) || i==len-1){//可以设置不同的大小；如50，100，200，500，1000等等 ps.executeBatch(); //优化插入第三步 提交，批量插入数据库中。 conn.commit(); ps.clearBatch(); //提交后，Batch清空。 } } } catch (Exception e) { System.out.println("MibTaskPack->getArpInfoList() error:" + e.getMessage()); return false; //出错才报false } finally { DBConnection.closeConection(conn, ps, rs); } return true; }
但是需要注意mysql的batch模式有一个bug参考https://bugs.mysql.com/bug.php?id=88105，https://bugs.mysql.com/bug.php?id=81468，如果表名中包含select，则会导致batch模式失效。
ibatis、mybatis的测试参见：https://github.com/mybatis/mybatis-3/issues/98
LZ最近用JdbcTemplate BeanPropertyRowMapper.newInstance(Pojo.class)测了一下select 30w/15个字段到pojo的性能，要比mybatis慢了接近50%。