执行的查询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();
}
}
}