JDBC查询大数据时怎么防止内存溢出-流式查询

1.前言

在使用 JDBC 查询大数据时，由于 JDBC 默认将整个结果集加载到内存中，当查询结果集过大时，很容易导致 JVM 内存溢出的问题。

解决办法通常是使用分页查询，但是分页查询越往后要遍历的行数越多，效率越低。除非能够添加索引条件，但这又提高了业务逻辑的复杂度。

2.流式查询介绍

JDBC的流式查询就是在使用ResultSet对象获取查询结果集的时候，不是把结果集一次性全部加载到内存中，而是分批次读取数据。

在jdbc客户端和mysql服务端建立tcp连接后，mysql以包的形式返回数据。在查询大数据的情况下，需要分多个包发送给客户端，而流式查询就是一次读取一个包的数据（通常情况下如此），所以查询的数据大小与MySQL一次发送的包大小息息相关。可以通过MySQL的配置max_allowed_packet设置包大小上限。

3.使用流式查询

java需要引入jdbc的依赖。

3.1不开启流式查询的内存占用情况

测试代码如下：

private static void testFetch() throws SQLException {
    Connection c = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1", "zhuzi", "123456");
    Statement s = c.createStatement();
    //查询1000w条数据
    ResultSet rs = s.executeQuery("select * from gg limit 10000000");
    while (rs.next()) {
		//执行处理数据的逻辑
    }
    //休眠100s，方便查看内存情况
    try {
        Thread.sleep(100000);
    } catch (InterruptedException e) {
        e.printStackTrace();
    }
    rs.close();
    s.close();
    c.close();
}

使用jconsole工具查看内存使用情况，如下图所示。

可以看到，大约占用了1.5GB的内存，并且内存曲线很平稳，这说明数据是一次性全部加载到内存中的。

3.2开启流式查询的内存占用情况

测试代码如下：

private static void testFetch() throws SQLException {
    Connection c = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1", "zhuzi", "123456");
    //必须设置为TYPE_FORWARD_ONLY和CONCUR_READ_ONLY 当然默认也是这两个值，可以不写
    Statement s = c.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    //必须设置为Integer.MIN_VALUE，其他值都不会生效
    s.setFetchSize(Integer.MIN_VALUE);//-2147483648
    ResultSet rs = s.executeQuery("select * from gg limit 10000000");
    while (rs.next()){
		//执行处理数据的逻辑
    }
    try {
        Thread.sleep(100000);
    } catch (InterruptedException e) {
        e.printStackTrace();
    }
    rs.close();
    s.close();
    c.close();
}

内存占用情况如下：

可以看到，仅占用了几十MB内存，内存占用极大的降低了，并且内存使用在慢慢增加，这是因为数据是一批一批不断加载进来的，但前面的数据还没来得及清理。但实际上我们用完一批数据那么这批数据占用的内存就能够释放掉了。

4.开启流式查询的注意点

前面的测试代码中提到了，在调用Statement对象的setFetchSize方法时，传递的参数必须为-2147483648，否则不会开启流式查询。

StatementImpl类源码定义如下：

protected boolean createStreamingResultSet() {
    return this.query.getResultType() == Type.FORWARD_ONLY && this.resultSetConcurrency == 1007 && this.query.getResultFetchSize() == -2147483648;
}

该方法用于判断是否开启流式查询，可以看到，它要求ResultType为FORWARD_ONLY，ResultSetConcurrency为CONCUR_READ_ONLY，以及ResultFetchSize为-2147483648

在ResultSet类中这些变量的定义如下：

//查询结果通过next方法只能向后遍历，不能使用previous方法往前遍历
//开启该选项后调用previous方法回报错：
//Operation not allowed for a result set of type ResultSet.TYPE_FORWARD_ONLY.
int TYPE_FORWARD_ONLY = 1003;

//查询结果可前后遍历，数据库数据改变不会影响结果集
int TYPE_SCROLL_INSENSITIVE = 1004;

//查询结果可前后遍历，数据库数据改变会影响结果集（测试了，好像没用，不知道怎么做）
int TYPE_SCROLL_SENSITIVE = 1005;

//结果集只能读
int CONCUR_READ_ONLY = 1007;

//结果集可以修改，并且对结果集的修改能够同步到数据库
int CONCUR_UPDATABLE = 1008;

参考博客：
Mysql中JDBC的三种查询(普通、流式、游标)详解
正确使用MySQL JDBC setFetchSize()方法解决JDBC处理大结果集