mybatis查询大量数据内存溢出解决办法

平常使用mybatis时并没有考虑内存问题,但今天遇到一件事情是一次需要查询的数据量很大,直接导致系统oom,所以对需要大量查询的sql做了如下修改…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
@Autowired
private SqlSessionTemplate sqlSessionTemplate;


@Test
public void test() {
MerchantOrderPushExample merchantOrderPushExample = new MerchantOrderPushExample();
MerchantOrderPushExample.Criteria criteria = merchantOrderPushExample.createCriteria();
criteria.andIdIsNotNull();
merchantOrderPushExample.setOrderByClause(" id desc");
HashMap<String, Object> param = new HashMap<>();
param.put("oredCriteria", merchantOrderPushExample.getOredCriteria());
param.put("orderByClause", merchantOrderPushExample.getOrderByClause());
// param.put("limit",20); 测试分页的使用
sqlSessionTemplate.select("com.xws.business.mapper.MerchantOrderPushMapper.selectByExample", param, resultContext -> {
MerchantOrderPush resultObject = (MerchantOrderPush) resultContext.getResultObject();
System.out.println(JSON.toJSONString(resultObject));
});
}

这里使用了mybatis的流式查询,具体原理是内部直接操作 ResultContext 逐条获取数据并回调handler的handleResult方法处理

原理:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
private void handleRowValuesForSimpleResultMap(ResultSetWrapper rsw, ResultMap resultMap, ResultHandler<?> resultHandler, RowBounds rowBounds, ResultMapping parentMapping)
throws SQLException {
DefaultResultContext<Object> resultContext = new DefaultResultContext<Object>();
skipRows(rsw.getResultSet(), rowBounds);
while (shouldProcessMoreRows(resultContext, rowBounds) && rsw.getResultSet().next()) {
ResultMap discriminatedResultMap = resolveDiscriminatedResultMap(rsw.getResultSet(), resultMap, null);
Object rowValue = getRowValue(rsw, discriminatedResultMap);
storeObject(resultHandler, resultContext, rowValue, parentMapping, rsw.getResultSet());
}
}

private void storeObject(ResultHandler<?> resultHandler, DefaultResultContext<Object> resultContext, Object rowValue, ResultMapping parentMapping, ResultSet rs) throws SQLException {
if (parentMapping != null) {
linkToParents(rs, parentMapping, rowValue);
} else {
callResultHandler(resultHandler, resultContext, rowValue);
}
}

//调用回调
@SuppressWarnings("unchecked" /* because ResultHandler<?> is always ResultHandler<Object>*/)
private void callResultHandler(ResultHandler<?> resultHandler, DefaultResultContext<Object> resultContext, Object rowValue) {
resultContext.nextResultObject(rowValue);
((ResultHandler<Object>) resultHandler).handleResult(resultContext);
}

结果对比:
图就不上了,使用非流式查询内存使用时线性增长的,使用流式查询后内存使用是平稳的.成功避免oom.

另外,mybatis流式查询还有一种方法:没有验证,
注入bean,springboot 可以使用 @Configration 方式

1
2
3
4
5
<bean id="myMyBatisCursorItemReader" class="org.mybatis.spring.batch.MyBatisCursorItemReader">
<property name="sqlSessionFactory" ref="sqlSessionFactory" />
<property name="queryId"
value="com.taobao.accs.mass.petadata.dal.sqlmap.AccsDeviceInfoDAOMapper.selectByExampleForPetaData" />
</bean>

在mapper.xml的sql上加上fetchSize=”-2147483648” ,Integer.MIN_VALUE=-2147483648

1
<select id="selectByExampleForPetaData" fetchSize="-2147483648" resultMap="xxx">
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
static void testCursor1() throws UnexpectedInputException, ParseException, Exception {

try {
Map<String, Object> param = new HashMap<String, Object>();


AccsDeviceInfoDAOExample accsDeviceInfoDAOExample = new AccsDeviceInfoDAOExample();
accsDeviceInfoDAOExample.createCriteria().andAppKeyEqualTo("12345").andAppVersionEqualTo("5.7.2.4.5")
.andPackageNameEqualTo("com.test.zlx");

param.put("oredCriteria", accsDeviceInfoDAOExample.getOredCriteria());

// 设置参数
myMyBatisCursorItemReader.setParameterValues(param);

// 创建游标
myMyBatisCursorItemReader.open(new ExecutionContext());

//使用游标迭代获取每个记录
Long count = 0L;
AccsDeviceInfoDAO accsDeviceInfoDAO;
while ((accsDeviceInfoDAO = myMyBatisCursorItemReader.read()) != null) {

System.out.println(JSON.toJSONString(accsDeviceInfoDAO));
++count;
System.out.println(count);

}
} catch (Exception e) {
System.out.println("error:" + e.getLocalizedMessage());
} finally {

// do some
myMyBatisCursorItemReader.close();
}

}

参考:
jdbc的流式查询 http://ifeve.com/mysq-stream-search/


mybatis查询大量数据内存溢出解决办法
https://www.weypage.com/2018/10/21/Java框架/Mybatis/mybatis查询大量数据内存溢出解决办法/
作者
weylan
发布于
2018年10月21日
许可协议