MySQL JDBC/MyBatis Stream方式读取SELECT超大结果集
情景: 遍历并处理一个大表中的所有数据, 这个表中的数据可能会是千万条或者上亿条, 很多人可能会说用分页limit……但需求本身一次性遍历更加方便, 且Oracle/DB2都有方便的游标机制.
对DB来说Stream其实也就是我们说的游标(Cursor), MySQL的Stream方式有2种, Client Side Cursor和Server Side Cursor. JDBC默认的方式Client Side Cursor, 没有任何设置的默认情况下JDBC驱动会将select的全部结果都读取到Client Side后再处理, 这样的话当select返回的结果集非常大时将会撑爆Client端的内存, JDBC下就是普通的OOM; 当然用MyBatis之类的ORM也有同样的问题, 因为这些东西都是架构在JDBC之上的.
解决办法:
1. 使用Client Side Cursor
PreparedStatement/Statement的setFetchSize方法设置为Integer.MIN_VALUE或者使用方法Statement.enableStreamingResults(), 其实这个方法和设置Integer.MIN_VALUE一样, 源码如下:
public void enableStreamingResults() throws SQLException {
synchronized (checkClosed().getConnectionMutex()) {
this.originalResultSetType = this.resultSetType;
this.originalFetchSize = this.fetchSize;
setFetchSize(Integer.MIN_VALUE);
setResultSetType(ResultSet.TYPE_FORWARD_ONLY);
}
}
在网上查了下这种Client Side Cursor的大概实现, 其实mysql本身并没有FetchSize方法, 它是通过使用CS阻塞方式的网络流控制实现服务端不会一下发送大量数据到客户端撑爆客户端内存, 我认为这种方式非常LOW! 是很明显的"补丁"策略; 这样就会造成一个必然的问题就是如果没有全部读取完ResultSet的结果再执行其他sql, 那么将会影响该连接的缓存, 所以这种方式要求要么读取完ResultSet中的全部数据要么需要自己调用ResultSet.close()方法, 也就是得用try {} finally{ rs.close(); }或者jdk7下的try-with-resources语法, 例如:
try (ResultSet rs = pstmt.executeQuery()) {
Role role = new Role();
int i = 0;
while (rs.next()) {
try {
role.setRoleId(rs.getString("roleId"));
role.setState(rs.getInt("state"));
role.setMiscData(rs.getString("miscData"));
selectHandler.action(role);
} catch (Exception ex) {
logger.error("selectAllRoles error!", ex);
}
}
}
常用的ORM MyBatis下, 默认select的结果是一个List<XXXObject>, 这样问题就更明显了, 要将select全部结果放到一个集合中再处理, 那么结果集一大OOM是必然; 经过查询MyBatis资料发现有ResultHandler机制, 就是这样handler:
sqlSession.select("chenlong.mybatislearn.db.mapper.RoleMapper.findAllRoles", handler);
但是和JDBC方式一样, MyBatis即便用了ResultHandler也是将所有结果都读到Client Side, 内存一样爆掉, 最后总算发现xml mapper里可以配置select的fetchSize, 按照前面JDBC方式将其配置为Integer.MIN_VALUE即-2147483648就正常了, 如下:
<select id="findAllRoles" fetchSize="-2147483648" resultType="chenlong.mybatislearn.db.struct.Role">
SELECT * FROM role
</select>
但还有一个问题就是这种方式必须自己ResultSet.close(), 通过扒MyBatis代码发现它已经帮我们做了, 如下
这样就可以放心的在MyBatis下使用Client Side Cursor了.
2. 使用Server Side Cursor
MySQL JDBC Driver文档中有这样参数的说明:
useCursorFetch
If connected to MySQL > 5.0.2, and setFetchSize() > 0 on a statement, should that statement use cursor-based fetching to retrieve rows?
Default: false
Since version: 5.0.0
在MyBatis中位置为:
<property name="url" value="jdbc:mysql://localhost:3008/mybatislearn?autoReconnect=true&useCursorFetch=true"/>
实测这种Server Side Cursor执行sql后要等很久才开始返回结果, 而Client Side Cursor几乎是瞬间就开始返回结果; 网上查询后的结果是Server Side Cursor使用MySQL Server端的资源(内存/CPU……)处理Cursor, 这个可能是其原因, 但一旦开始返回结果目测两者差别不大.
两者各有优缺点, 尤其是Client Side Cursor必须自己记得ResultSet.close()否则整个连接将不再可用, 此为大坑, 尤其是有连接池的情况.
再次也发现MySQL相比其他大型RDBMS的弱点, 这种查询游标遍历本该是标配! 而MySQL用这么LOW的实现, 还需要用户掌握这么多黑魔法……F***
参考代码如下:
https://files.cnblogs.com/files/logicbaby/MyBatisLearn.zip
