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

原理:

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 方式

<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

<select id="selectByExampleForPetaData" fetchSize="-2147483648" resultMap="xxx">

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/