问题:当一次性查询 百万行的数据,返回给前端的时候,很容易就会导致OOM。全网搜索,没有满意的解决方案,因此自己研究了 几番 JdbcTemplate 源码,手动编码解决。
原因:JDBC 一次性封装成了 百万个 对象,而且都不能被回收。如此大规模的快速生成对象,会立刻把JVM 盛满,而且都是不可回收对象,导致GC频繁,触发FullGC, 但是没什么实际效果。机器变得卡顿,OOM随之发生。
思路:自己手动控制 ResultSet 对象的生成过程,及时 将大对象 改变成小对象,临时保存为 磁盘的File ,立刻释放内存的占用,让GC及时回收垃圾。最终将文件流封装进 response 的输出流中,返回给前端。
核心思想:此时的 ResultSet 仅仅是 数据库的一个游标指针(cursor),并不是真实的全部数据,所以并不会占据内存空间。理解这一点非常重要!!!我当年研究了 很多资料才得到这一看似简单的真知灼见。
避坑指南:千万不能使用 SqlRowSet,因为 该对象是 真实的全部数据,并不是游标指针,所以会占据很多的内存空间。请看:
// The results will be mapped to an SqlRowSet which holds the data in a disconnected fashion 意思就是 这是离线版的ResultSet ,而不是指针 ResultSet
SqlRowSet queryForRowSet(String sql, Object... args) throws DataAccessException;
protected SqlRowSet createSqlRowSet(ResultSet rs) throws SQLException {
CachedRowSet rowSet = newCachedRowSet();
rowSet.populate(rs); // 这段代码的意思,就是 将 ResultSet 指针指向的数据,全部倒腾出来,缓存到JVM 本地
return new ResultSetWrappingSqlRowSet(rowSet);
}
解决以上问题的思路,最核心的示例代码如下:
每隔5w行,写一次磁盘,并且清空一次当前的大对象,让 GC 来 work ,收走 garbage
File file = new File("临时文件全路径")
Object query = getJdbcTemplate().query(sql.toString(), new ResultSetExtractor<Object>() {
@Override
public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
while (rs.next()) {
processRow(columnCount, rs, sb);
if(行数 >= 50000){
IOUtils.write(sb, new FileOutputStream(file,true), "UTF-8"); //及时写1次数磁盘
sb.delete(0, sb.length());// GC 清理垃圾,重置 sb 大对象
}
}
return file;
}
});
完整代码如下: 可以直接CP到你的项目中使用
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.IOUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.JdbcUtils;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
/**
* 自定义的 ResultSetExtractor, 直接操作 数据库的指针 ResultSet ,并且返回最终想要的 CSV 文件
*
* 在其中做 逻辑分步处理,可以避免 OOM . 比如,
* 直接返回 CSV 文件,避免封装成 List 或者Map 对象
* 及时处理大对象,让GC 释放临时空间,避免撑爆 JVM 内存, 也可以解决 OOM 问题
*/
public File queryBigResult(CharSequence sql,Object... o){
// 一般只有超过 5万行 的 结果集,才需要 返回csv 格式,所以容量最好是 5w行的两倍(以每行估计 100个字符来算)
final StringBuilder sb = new StringBuilder(5000000 * 2);
final File file = new File(SystemUtils.getJavaIoTmpDir() + File.separator + "临时大文件存储,可以删除".concat(System.currentTimeMillis() + "").concat(".csv"));
Object query = getJdbcTemplate().query(sql.toString(), new ResultSetExtractor<Object>() {
@Override
public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
ResultSetMetaInfo info = new ResultSetMetaInfo(rs); //缓存基础信息,避免在while循环中反复调用,提高效率
String[] columnNames = info.getColumnNames();
int columnCount = info.getColumnCount(), line = 0;
// first line : coLNames
line++;
for (int i = 0; i < columnCount; i++) {
sb.append(columnNames[i]).append(",");
}
sb.deleteCharAt(sb.length() - 1).append("\n");
while (rs.next()) {
processRow(columnCount, rs, sb);
line++;
// 数据及时落盘
if (line >= 50000) {
try {
IOUtils.write(sb, new FileOutputStream(file,true), "UTF-8");
} catch (IOException e) {
log.error("正在写盘 [{}] 行时出错:{}", line, Utils.logMessage(e));
}
// reset line & result
line = 0;
sb.delete(0, sb.length());
}
}
try {
IOUtils.write(sb, new FileOutputStream(file,true), "UTF-8");
return file;
} catch (IOException e) {
log.error("最后写盘 [{}] 行时出错:{}", line, Utils.logMessage(e));
}
sb.delete(0, sb.length());
return file;
}
},o);
return file;
}
/**
* 大文件:处理单行
*/
private static void processRow(int columnCount, ResultSet rs, StringBuilder sb) throws SQLException {
for (int i = 1; i <= columnCount; i++) {
Object value = JdbcUtils.getResultSetValue(rs, i); // 参考牛掰spring的工具类方法,真汗颜,当初我自研究了个把月,手写了Jdbc框架工具,后来才发现spring的完美写法
sb.append(value).append(",");
}
sb.deleteCharAt(result.length() - 1).append("\n");
}
上文中用到的 对象:
ResultSetMetaInfo: 为了缓存 ResultSet 指针的元数据信息,避免 在while 循环中 反复get 调用导致 的效率降低。
import lombok.Builder;
import lombok.Data;
import lombok.SneakyThrows;
import lombok.AllArgsConstructor;
import lombok.NoArgsConstructor;
import org.springframework.jdbc.support.JdbcUtils;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
/**
* @author stormfeng
* @date 2020-11-25 14:39
*/
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class ResultSetMetaInfo {
private int columnCount;
private int[] columnTypes;
private String[] columnNames;
/**
* @see org.springframework.jdbc.core.RowCountCallbackHandler#processRow(java.sql.ResultSet)
*/
@SneakyThrows
public ResultSetMetaInfo(ResultSet rs) {
ResultSetMetaData rsmd = rs.getMetaData();
this.columnCount = rsmd.getColumnCount();
this.columnTypes = new int[columnCount];
this.columnNames = new String[columnCount];
for (int i = 0; i < columnCount; i++) {
columnTypes[i] = rsmd.getColumnType(i + 1);
columnNames[i] = JdbcUtils.lookupColumnName(rsmd, i + 1);
}
}
}
如果是 mysql ,请注意 ,
官网MySQL :: MySQL Connector/J 8.0 Developer Guide :: 6.4 JDBC API Implementation Notes
进行如下设置 ,共有1种可用设置,推荐第2种设置 fetchSize =100 。
如果按照设置1 ,那么 resultset 每次取回的就是 1行 数据;单条取回,速度肯定较慢
stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);
如果按照设置2 ,那么 resultset 每次取回的就是 fetchsize的那几行 数据;比第1种肯定快很多,推荐
conn = DriverManager.getConnection("jdbc:mysql://localhost/?useCursorFetch=true", "user", "s3cr3t");
stmt = conn.createStatement();
stmt.setFetchSize(100);
rs = stmt.executeQuery("SELECT * FROM your_table_here");
此时,虽然避免了 由于mysql数据库本身的 resultset 过大而导致的 oom ,但是,依然需要按照本文一开始的代码那样,不要一次性生成百万或者千万java对象,而是要及时将内存落地为写磁盘,避免超大java对象成的 oom 。