mybatis流式查询

前言

MyBatis中使用流式查询避免数据量过大导致OOM，基本概念如下：

流式查询指的是查询成功后不是返回一个集合而是返回一个迭代器，应用每次从迭代器取一条查询结果。流式查询的好处是能够降低内存使用。

如果没有流式查询，我们想要从数据库取 1000 万条记录而又没有足够的内存时，就不得不分页查询，而分页查询效率取决于表设计，如果设计的不好，就无法执行高效的分页查询。因此流式查询是一个数据库访问框架必须具备的功能。

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

MyBatis 流式查询接口

MyBatis 提供了一个叫 org.apache.ibatis.cursor.Cursor 的接口类用于流式查询，这个接口继承了 java.io.Closeable 和 java.lang.Iterable 接口，由此可知：

Cursor 是可关闭的；
Cursor 是可遍历的。
除此之外，Cursor 还提供了三个方法：

isOpen()：用于在取数据之前判断 Cursor 对象是否是打开状态。只有当打开时 Cursor 才能取数据；
isConsumed()：用于判断查询结果是否全部取完。
getCurrentIndex()：返回已经获取了多少条数据

因为 Cursor 实现了迭代器接口，因此在实际使用当中，从 Cursor 取数据非常简单：

cursor.forEach(rowObject -> {...});

但构建 Cursor 的过程不简单

我们举个实际例子。下面是一个 Mapper 类：

@Mapper
public interface FooMapper {
    @Select("select * from foo limit #{limit}")
    Cursor<Foo> scan(@Param("limit") int limit);
}

方法 scan() 是一个非常简单的查询。通过指定 Mapper 方法的返回值为 Cursor 类型，MyBatis 就知道这个查询方法一个流式查询。

然后我们再写一个 SpringMVC Controller 方法来调用 Mapper（无关的代码已经省略）：

@GetMapping("foo/scan/0/{limit}")
public void scanFoo0(@PathVariable("limit") int limit) throws Exception {
    try (Cursor<Foo> cursor = fooMapper.scan(limit)) {  // 1
        cursor.forEach(foo -> {});                      // 2
    }
}

上面的代码中，fooMapper 是 @Autowired 进来的。注释 1 处调用 scan 方法，得到 Cursor 对象并保证它能最后关闭；2 处则是从 cursor 中取数据。

上面的代码看上去没什么问题，但是执行 scanFoo0() 时会报错：

java.lang.IllegalStateException: A Cursor is already closed.

这是因为我们前面说了在取数据的过程中需要保持数据库连接，而 Mapper 方法通常在执行完后连接就关闭了，因此 Cusor 也一并关闭了。

所以，解决这个问题的思路不复杂，保持数据库连接打开即可。我们至少有三种方案可选。

方案一：SqlSessionFactory

我们可以用 SqlSessionFactory 来手工打开数据库连接，将 Controller 方法修改如下：

@GetMapping("foo/scan/1/{limit}")
public void scanFoo3(@PathVariable("limit") int limit) throws Exception {
    try (
        SqlSession sqlSession = sqlSessionFactory.openSession();  // 1
        Cursor<Foo> cursor =
              sqlSession.getMapper(FooMapper.class).scan(limit)   // 2
    ) {
        cursor.forEach(foo -> { });
    }
}

上面的代码中，1 处我们开启了一个 SqlSession （实际上也代表了一个数据库连接），并保证它最后能关闭；2 处我们使用 SqlSession 来获得 Mapper 对象。这样才能保证得到的 Cursor 对象是打开状态的。

方案二：TransactionTemplate

在 Spring 中，我们可以用 TransactionTemplate 来执行一个数据库事务，这个过程中数据库连接同样是打开的。代码如下：

@GetMapping("foo/scan/2/{limit}")
public void scanFoo1(@PathVariable("limit") int limit) throws Exception {
    TransactionTemplate transactionTemplate =
            new TransactionTemplate(transactionManager);  // 1
 
    transactionTemplate.execute(status -> {               // 2
        try (Cursor<Foo> cursor = fooMapper.scan(limit)) {
            cursor.forEach(foo -> { });
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    });
}

上面的代码中，1 处我们创建了一个 TransactionTemplate 对象（此处 transactionManager 是怎么来的不用多解释，本文假设读者对 Spring 数据库事务的使用比较熟悉了），2 处执行数据库事务，而数据库事务的内容则是调用 Mapper 对象的流式查询。注意这里的 Mapper 对象无需通过 SqlSession 创建。

方案三：@Transactional 注解

这个本质上和方案二一样，代码如下：

@GetMapping("foo/scan/3/{limit}")
@Transactional
public void scanFoo2(@PathVariable("limit") int limit) throws Exception {
    try (Cursor<Foo> cursor = fooMapper.scan(limit)) {
        cursor.forEach(foo -> { });
    }
}

它仅仅是在原来方法上面加了个 @Transactional 注解。这个方案看上去最简洁，但请注意 Spring 框架当中注解使用的坑：只在外部调用时生效。在当前类中调用这个方法，依旧会报错。

以上是三种实现 MyBatis 流式查询的方法。

真要用这个，你看到这里就能去使用，下面的那个可以看下在这个这个版本前是怎么实现的，spring框架的坑，看这个

实战使用

springboot中整合mybatis

pom文件中的配置

<dependency>
  <groupId>org.mybatis.spring.boot</groupId>
  <artifactId>mybatis-spring-boot-starter</artifactId>
  <version>1.1.1</version>
</dependency>

mapper.xml文件配置

select语句需要增加fetchSize属性，底层是调用jdbc的setFetchSize方法，查询时从结果集里面每次取设置的行数，循环去取，直到取完。默认size是0，也就是默认会一次性把结果集的数据全部取出来，当结果集数据量很大时就容易造成内存溢出。

<select id="selectGxids" resultType="java.lang.String" fetchSize="1000">
   SELECT gxid from t_gxid
 </select>

自定义ResultHandler来分批处理结果集

package flowselect;

import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;
import java.util.Set;

public class GxidResultHandler implements ResultHandler<String> {
  // 这是每批处理的大小
  private final static int BATCH_SIZE = 1000;
  private int size;
  // 存储每批数据的临时容器
  private Set<String> gxids;

  public void handleResult(ResultContext<? extends String> resultContext) {
    // 这里获取流式查询每次返回的单条结果
    String gxid = resultContext.getResultObject();
    // 你可以看自己的项目需要分批进行处理或者单个处理，这里以分批处理为例
    gxids.add(gxid);
    size++;
    if (size == BATCH_SIZE) {
      handle();
    }
  }

  private void handle() {
    try {
      // 在这里可以对你获取到的批量结果数据进行需要的业务处理
    } finally {
      // 处理完每批数据后后将临时清空
      size = 0;
      gxids.clear();
    }
  }

  // 这个方法给外面调用，用来完成最后一批数据处理
  public void end(){
    handle();// 处理最后一批不到BATCH_SIZE的数据
  }
}

ServiceImpl类中的使用

package flowselect;

import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;

@Service
public class ServiceImpl implements Service {
  @Autowired
  SqlSessionTemplate sqlSessionTemplate;

  public void method(){
    GxidResultHandler gxidResultHandler = new GxidResultHandler();
    sqlSessionTemplate.select("flowselect.Mapper.selectGxids", gxidResultHandler);
    gxidResultHandler.end();
  }
}

总结

非流式查询：内存会随着查询记录的增长而近乎直线增长。
流式查询：内存会保持稳定，不会随着记录的增长而增长。其内存大小取决于批处理大小BATCH_SIZE的设置，该尺寸越大，内存会越大。所以BATCH_SIZE应该根据业务情况设置合适的大小。
另外要切记每次处理完一批结果要记得释放存储每批数据的临时容器，即上文中的gxids.clear();

注：这个比较旧了，可以借鉴下，网上比较多好的

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

听了那么多好处，为啥实现用的并不很多，比较纳闷，网上看了一堆，发现介绍也不是很多

为什么要用流式查询？

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

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

1、oracle等商业数据库的fetchsize

使用过oracle数据库的程序猿都知道，oracle驱动默认设置了fetchsize为10，那什么是fetchsize？

先来简单解释一下，当我们执行一个SQL查询语句的时候，需要在客户端和服务器端都打开一个游标，并且分别申请一块内存空间，作为存放查询的数据的一个缓冲区。这块内存区，存放多少条数据就由fetchsize来决定，同时每次网络包会传送fetchsize条记录到客户端。应该很容易理解，如果fetchsize设置为20，当我们从服务器端查询数据往客户端传送时，每次可以传送20条数据，但是两端分别需要20条数据的内存空闲来保存这些数据。fetchsize决定了每批次可以传输的记录条数，但同时，也决定了内存的大小。这块内存，在oracle服务器端是动态分配的。而在客户端，PS对象会存在一个缓冲中（LRU链表），也就是说，这块内存是事先配好的，应用端内存的分配在conn.prepareStatement(sql)或都conn.CreateStatement(sql)的时候完成。

2、流式查询与MySQL fetchsize的关系

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

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

3、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)

查了下异常发生的原因发现，其实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相比于大型商业数据库来说，还是显得太弱了，这种游标遍历的功能理应提供，但是它偏偏没有。

备注：看了你还想知道为啥正常的分页查询还慢，那就是你的表设计有问题，你加索引、sql优化这些的虽然能提升性能，但是条件允许的话，表设计还是前期规划好

参考：http://www.zyiz.net/tech/detail-130890.html
https://blog.csdn.net/weixin_43221845/article/details/84871362
https://blog.csdn.net/nym232/article/details/89240054