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

提示：为什么要用流式查询？

---

前言

提示：这里可以添加本文要记录的大概内容：

例如：随着人工智能的不断发展，机器学习这门技术也越来越重要，很多人都开启了学习机器学习，本文就介绍了机器学习的基础内容。

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、为什么要用流式查询？

• 如果有一个很大的查询结果需要遍历处理，又不想一次性将结果集装入客户端内存，就可以考虑使用流式查询；

• 分库分表场景下，单个表的查询结果集虽然不大，但如果某个查询跨了多个库多个表，又要做结果集的合并、排序等动作，依然有可能撑爆内存；详细研究了sharding-sphere的代码不难发现，除了group by与order by字段不一样之外，其他的场景都非常适合使用流式查询，可以最大限度的降低对客户端内存的消耗。

二、使用步骤

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的执行引擎对数据库的连接方式提供了两种：内存限制模式和连接限制模式。（参考：https://shardingsphere.apache.org/document/current/cn/features/sharding/principle/execute/），在内存限制模式中（也就是要使用流式查询的场景），对于每一张表的查询，都需要创建一个数据库连接，如果跨库跨表查询操作很多，这对数据库连接数的消耗将会非常大。起初十分不理解这种方式，为何不能多个查询共用同一个连接。一定有什么我没有了解清楚的问题。

带着这个疑问，不妨做一次小小的测试：

使用同一个MySQL数据库连接，分别执行多次查询，在得到多个ResultSet之后，再进行结果集的遍历。

代码如下（示例）：

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);

这最关键的一行注释掉，关闭流式查询，对多个结果集的遍历可以得到正确的结果。

第二次试验，开启流式查询，果然问题来了。

index:0============================
大 家 电
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)

3. 总结

查了下异常发生的原因发现，其实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关闭它之前，你不能使用同一个数据库连接去发起另外一个查询，否者抛出异常（第一次调用的正常，第二次的抛出异常）。

对比测试了Oracle和DB2，设置fetchSize之后，数据库连接依然可以被其他查询共用，并没有MySQL的这个坑。再一次应证了MySQL相比于大型商业数据库来说，还是显得太弱了，这种游标遍历的功能理应提供，但是它偏偏没有。

mybatis 查询

• 流式查询的过程当中，数据库连接是保持打开状态的，因此要注意的是：执行一个流式查询后，数据库访问框架就不负责关闭数据库连接了，需要应用在取完数据后自己关闭。

解决方案

• SqlSessionFactory

@GetMapping(“foo/scan/1/{limit}”)publicvoid scanFoo1(@PathVariable(“limit”) int limit) throwsException{ try( SqlSession sqlSession = sqlSessionFactory.openSession(); // 1 Cursor cursor = sqlSession.getMapper(FooMapper.class).scan(limit) // 2 ) { cursor.forEach(foo -> { }); }}、

• TransactionTemplate

@GetMapping(“foo/scan/2/{limit}”)publicvoid scanFoo2(@PathVariable(“limit”) int limit) throwsException{ TransactionTemplate transactionTemplate = newTransactionTemplate(transactionManager); // 1 transactionTemplate.execute(status -> { // 2 try(Cursor cursor = fooMapper.scan(limit)) { cursor.forEach(foo -> { }); } catch(IOException e) { e.printStackTrace(); } returnnull; });}

• @Transactional 注解

引用

mybatis 查询_查询千万数据
深入了解MySQL的流式查询机制