深入了解MySQL的流式查询机制

1. 流式查询与MySQL fetchsize的关系

        既然fetchsize这么好用，那MySQL直接设一个值，不就也可以用到缓冲区，不必每次都将全量结果集装入内存。但是，非常遗憾，MySQL的JDBC驱动本质上并不支持设置fetchsize，不管设置多大的fetchsize，JDBC驱动依然会将select的全部结果都读取到客户端后再处理, 这样的话当select返回的结果集非常大时将会撑爆Client端的内存。

        但也不是完全没办法，PreparedStatement/Statement的setFetchSize方法设置为Integer.MIN_VALUE或者使用方法Statement.enableStreamingResults(), 也可以实现流式查询，在执行ResultSet.next()方法时，会通过数据库连接一条一条的返回，这样也不会大量占用客户端的内存。

2. MySQL流式查询的坑 

        sharding-sphere的执行引擎对数据库的连接方式提供了两种：内存限制模式和连接限制模式。在内存限制模式中（也就是要使用流式查询的场景），对于每一张表的查询，都需要创建一个数据库连接，如果跨库跨表查询操作很多，这对数据库连接数的消耗将会非常大。起初十分不理解这种方式，为何不能多个查询共用同一个连接。一定有什么我没有了解清楚的问题。

实验：

public class LoopConnectionTest {
 
    private static Connection conn = getConn();
 
    public static void main(String[] args) {
 
        List<ResultSet> actualResultSets = new ArrayList<>();
 
        for (int i = 0; i < 3; i++) {
            actualResultSets.add(getAllCategory(conn));
        }
 
 
        boolean flag = true;
        int i = 0;
        while (true) {
 
            try {
                int index = i++;
                flag = displayResultSet(actualResultSets.get(index%3), index%3);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            if (!flag) {
                break;
            }
        }
 
    }
 
    private static ResultSet getAllCategory(Connection conn) {
        String sql = "select * from tb_category";
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            pstmt = (PreparedStatement)conn.prepareStatement(sql);
//            pstmt.setFetchSize(Integer.MIN_VALUE);
            resultSet = pstmt.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
//        finally {  
//            if (null!=pstmt) {
//                try {
//                    pstmt.close();//注释掉close方法是因为，一旦pstmt关闭，resultSet也会随之关闭
//                } catch (SQLException e) {
//                    e.printStackTrace();
//                }
//            }
//        }
        return resultSet;
    }
 
    private static boolean displayResultSet(ResultSet rs, int index) throws SQLException {
        int col = rs.getMetaData().getColumnCount();
        System.out.println("index:" + index + "============================");
        boolean flag = rs.next();
        if (flag) {
            System.out.println(rs.getString("name"));
        }
        return flag;
    }
 
    public static Connection getConn() {
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://192.168.178.140:3306/jasper";
        String username = "root";
        String password = "123456";
        Connection conn = null;
        try {
            Class.forName(driver); //classLoader,加载对应驱动
            conn = (Connection) DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
 
}

第一次试验，我们将pstmt.setFetchSize(Integer.MIN_VALUE);这一行注释掉，关闭流式查询，对多个结果集的遍历可以得到正确的结果。
第二次试验，开启流式查询，果然报错：

java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@617f84e0 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:932)
	at com.mysql.jdbc.MysqlIO.checkForOutstandingStreamingData(MysqlIO.java:3338)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2504)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2758)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2820)
	at com.mysql.jdbc.StatementImpl.executeSimpleNonQuery(StatementImpl.java:1657)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2177)
	at com.cmbc.jdbc.test.LoopConnectionTest.getAllCategory(LoopConnectionTest.java:44)
	at com.cmbc.jdbc.test.LoopConnectionTest.main(LoopConnectionTest.java:16)
java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@617f84e0 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:932)
	at com.mysql.jdbc.MysqlIO.checkForOutstandingStreamingData(MysqlIO.java:3338)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2504)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2758)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2820)
	at com.mysql.jdbc.StatementImpl.executeSimpleNonQuery(StatementImpl.java:1657)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2177)
	at com.cmbc.jdbc.test.LoopConnectionTest.getAllCategory(LoopConnectionTest.java:44)
	at com.cmbc.jdbc.test.LoopConnectionTest.main(LoopConnectionTest.java:16)
Exception in thread "main" java.lang.NullPointerException
	at com.cmbc.jdbc.test.LoopConnectionTest.displayResultSet(LoopConnectionTest.java:61)
	at com.cmbc.jdbc.test.LoopConnectionTest.main(LoopConnectionTest.java:26)

        其实mysql本身并没有FetchSize方法, 它是通过使用CS阻塞方式的网络流控制实现服务端不会一下发送大量数据到客户端撑爆客户端内存,这种实现方式比起商业数据库Oracle使用客户端、服务器端缓冲块暂存查询结果数据来说，简直是弱爆了！这样带来的问题：如果使用了流式查询，一个MySQL数据库连接同一时间只能为一个ResultSet对象服务，并且如果该ResultSet对象没有关闭，势必会影响其他查询对数据库连接的使用！此为大坑，难怪sharding-sphere费劲心思要提供两种数据库连接模式，如果应用对数据库连接的消耗要求严苛，那么流式查询就不再适合。

        贴下MySQL Connector/J 5.1 Developer Guide中原文：

        There are some caveats with this approach. You must read all of the rows in the result set (or close it) before you can issue any other queries on the connection, or an exception will be thrown. 也就是说当通过流式查询获取一个ResultSet后，在你通过next迭代出所有元素之前或者调用close关闭它之前，你不能使用同一个数据库连接去发起另外一个查询，否者抛出异常（第一次调用的正常，第二次的抛出异常）。

参考：                       
原文链接：https://blog.csdn.net/qq_41134142/article/details/112709213