java 查询大批量数据 内存溢出_mybatis查询大量数据内存溢出解决办法

更多内容请浏览本人博客

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

直接上代码

@Autowired

private SqlSessionTemplate sqlSessionTemplate;

@Test

public void test() {

MerchantOrderPushExample merchantOrderPushExample = new MerchantOrderPushExample();

MerchantOrderPushExample.Criteria criteria = merchantOrderPushExample.createCriteria();

criteria.andIdIsNotNull();

merchantOrderPushExample.setOrderByClause(" id desc");

HashMap 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方法处理

原理:

private void handleRowValuesForSimpleResultMap(ResultSetWrapper rsw, ResultMap resultMap, ResultHandler> resultHandler, RowBounds rowBounds, ResultMapping parentMapping)

throws SQLException {

DefaultResultContext resultContext = new DefaultResultContext();

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 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*/)

private void callResultHandler(ResultHandler> resultHandler, DefaultResultContext resultContext, Object rowValue) {

resultContext.nextResultObject(rowValue);

((ResultHandler) resultHandler).handleResult(resultContext);

}

结果对比:

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

另外,mybatis流式查询还有一种方法:没有验证,

注入bean,springboot 可以使用 @Configration 方式

value="com.taobao.accs.mass.petadata.dal.sqlmap.AccsDeviceInfoDAOMapper.selectByExampleForPetaData" />

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

static void testCursor1() throws UnexpectedInputException, ParseException, Exception {

try {

Map param = new HashMap();

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();

}

}

参考