sql查询结果太大导致oom解决方案

谁借莪１个温暖的怀抱￠ 2022-01-27 16:15 1027阅读 0赞

执行的查询sql结果集太大，导致oom的现象很常见，下面是一些解决方案

## 条数限制的逻辑 ##

限制最多查询10万条，设置maxRow为10万+1条，如果实际查询到的rowNum为10万+1，那么说明超过最大限制条数，报错

public  static int resultLimit = 100000;
                    statement.setMaxRows(1+resultLimit);来限制获取的条数
                    rs = statement.executeQuery(sql);
                    rs.last();
                    int  totalRowNum = rs.getRow();
                    if (totalRowNum > resultLimit  ) {
                        rs = null;
                        throw new CBoardException("Cube result count " + totalRowNum + ", is greater than limit " + resultLimit);
                    }
                    rs.beforeFirst();

##    限制结果集占用内存大小 ##

因为RamUsageEstimator.sizeOf方法执行很慢，所以采用仅仅在1000条预估内存的方法，公式如下  
实际占用内存 ~= 实际行数\*1000条时的size/1000

while (rs.next()) {
                        resultCount++;
                        row = new String[columnCount];
                        for (int j = 0; j < columnCount; j++) {
                            row[j] = rs.getString(j + 1);
                        }
                        list.add(row);
                        // 因为SizeTool.getObjectMSize方法很慢，所以采取1000行的时候估计总的占用内存
                        if(resultCount == 1000){
                            double memMSize = totalRowNum*SizeTool.getObjectMSize(list)/1000;
                            if(memMSize > SizeTool.MAX_SQL_SIZE){
                                rs = null;
                                throw new CBoardException("Cube result MSize " + memMSize + ", is greater than limitMSize " + SizeTool.MAX_SQL_SIZE);
                            }
                        }
                    }

上面一个对象占用内存大小需要引入：

<dependency>
                <groupId>org.apache.lucene</groupId>
                <artifactId>lucene-core</artifactId>
                <version>4.0.0</version>
            </dependency>

SizeTool代码：

public class SizeTool {
        public static double MSIZE = 1024 * 1024;
        public  static int MAX_COMPONENT_SIZE =  5 ;
        public  static int MAX_SQL_SIZE =  50 ;
    
        public static double getObjectMSize(Object o) {
            return Double.valueOf(RamUsageEstimator.sizeOf(o)) / MSIZE;
        }
    
        public static void main(String[] args) {
            ArrayList arrayList = new ArrayList();
            arrayList.add("a");
            System.out.println(getObjectMSize(arrayList));
        }
    
    }

##    流式查询的方式 ##

还有一种是流式查询的方式，这种查询方式不会一次将查询结果全部返回，所以如果你是边查询边消耗的方式推荐使用该种方式，但是如果你最终还是需要把所有查询结果放到一个List之类的集合中，那么还是会导致oom。，流式查询实现方式：

//修改为流式查询
                    ps = con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
                    ps.setFetchSize(Integer.MIN_VALUE);
                    ps.execute("SET SQL_BIG_SELECTS=1;");

注意：此种查询不允许执行类似resultSet.beforeFirst()方法，只能一直向后获取数据。

### 不要使用mybatis中分页插件-RowBounds ###

在DefaultResultSetHandler中，逻辑分页会将所有的结果都查询到，然后根据RowBounds中提供的offset和limit值来获取最后的结果  
借鉴自：https://blog.csdn.net/qq924862077/article/details/52611848  
RowBounds源码如下：

public class RowBounds {
     
      /* 默认offset是0**/
      public static final int NO_ROW_OFFSET = 0;
      
      /* 默认Limit是int的最大值，因此它使用的是逻辑分页**/
      public static final int NO_ROW_LIMIT = Integer.MAX_VALUE;
      public static final RowBounds DEFAULT = new RowBounds();
     
      private int offset;
      private int limit;
     
      public RowBounds() {
        this.offset = NO_ROW_OFFSET;
        this.limit = NO_ROW_LIMIT;
      }
     
      public RowBounds(int offset, int limit) {
        this.offset = offset;
        this.limit = limit;
      }
     
      public int getOffset() {
        return offset;
      }
     
      public int getLimit() {
        return limit;
      }
     
    }

逻辑分页的实现原理：  
在DefaultResultSetHandler中，逻辑分页会将所有的结果都查询到，然后根据RowBounds中提供的offset和limit值来获取最后的结果，DefaultResultSetHandler实现如下：

private void handleRowValuesForSimpleResultMap(ResultSetWrapper rsw, ResultMap resultMap, ResultHandler<?> resultHandler, RowBounds rowBounds, ResultMapping parentMapping)
          throws SQLException {
        DefaultResultContext<Object> resultContext = new DefaultResultContext<Object>();
        //跳过RowBounds设置的offset值
        skipRows(rsw.getResultSet(), rowBounds);
        //判断数据是否小于limit，如果小于limit的话就不断的循环取值
        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 boolean shouldProcessMoreRows(ResultContext<?> context, RowBounds rowBounds) throws SQLException {
        //判断数据是否小于limit，小于返回true
        return !context.isStopped() && context.getResultCount() < rowBounds.getLimit();
      }
      //跳过不需要的行,应该就是rowbounds设置的limit和offset
      private void skipRows(ResultSet rs, RowBounds rowBounds) throws SQLException {
        if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY) {
          if (rowBounds.getOffset() != RowBounds.NO_ROW_OFFSET) {
            rs.absolute(rowBounds.getOffset());
          }
        } else {
          //跳过RowBounds中设置的offset条数据
          for (int i = 0; i < rowBounds.getOffset(); i++) {
            rs.next();
          }
        }
      }