数据库流式查询的探索

POI导出OOM

上万数据的导出，使用POI的XSSFWorkBook出现OOM，修改为SXSSWorkBook，定量刷盘

为什么不用easyExcel，因为easyExcel对于Excel样式的支持不够，客户要求颜色、格式等与前端界面相同

查询OOM

当数据来到百万的时候，查询的方法出现了OOM

场景模拟优化

以单表查询500w数据量进行模拟，其中表包含10列

基于本地数据库，可以完全忽略网速问题

默认情况

使用List接收500w数据

Xmx6g OOM
Xmx8g，查询成功，监控使用内存大约需要7g，耗时62s

使用游标Cursor

不修改任何参数只使用Cursor类出现

出现下面报错

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

查询相关信息后得知，使用游标Cursor的方式需要保持与数据库的连接

最简单的方式是直接使用 @Transactional(readOnly = true)

不修改其他参数，Cursor配合@Transactional(readOnly = true)

Xmx6g，查询成功，最大使用内存4g，耗时64s。内存上升到3.9g左右平滑回收到3g，通过监控观察到老年代未发生回收，主要发生回收在年轻代
Xmx3g，查询成功，最大使用内存接近3g，耗时62s。老年代上升到2.8g以后频繁回收新生代。
Xmx2g，OOM

使用Cursor后内存占用下降明显，但仍然需要不少内存

使用Cursor + useCursorFetch=true + fetchSize

经过查阅网上博客发现，在使用Cursor需要搭配后面两个参数来才能达到游标查询的效果。

游标查询，即类似分页查询，jdbc驱动对mysql交互的一种分页方式

Xmx2g，fetch=10000，查询时间太久了遂放弃，内存占用不大且回收主要在年轻代，年轻代达到250M左右进行回收持续往复。
Xmx2g，fetch=100000，耗时70s，G1收集器的情况下发生mixed gc，内存最大占用约650M
那么自然试一下Xmx700m，fetch=100000，耗时78s，发生mixed gc，内存最大占用450M
Xmx200m同上，young gc增多耗时5s吞吐量下降
Xmx100m，出现full gc吞吐量下降严重，耗时达到90s

通过这种方式，可以有效的控制JDBC在每次从数据库获取的数据多少，但会增加与数据库的网络交互次数，通过控制fetchSize参数可以达到一个内存占用与耗时之间的平衡控制。

在使用这种方式的时候有一个很有趣的现象，在一开始执行请求的时候，内存的占用并不会发生变化，但mysql的io占用会急剧升高，直到mysql io结束占用以后，内存才会发生明显波动。

但是，这只是游标查询，如果fetchSize设置为1的话将会一直在与数据库交互，500w次的交互耗时会更长，有没有真正的流式的方式能够保证吞吐量的同时又保证内存消耗呢？

Cursor + fetchSize = Integer.MIN_VALUE

在mysql-jdbc中有下面这么一段源码

    /**
     * We only stream result sets when they are forward-only, read-only, and the
     * fetch size has been set to Integer.MIN_VALUE
     * 
     * @return true if this result set should be streamed row at-a-time, rather
     *         than read all at once.
     */
    protected boolean createStreamingResultSet() {
        return ((this.query.getResultType() == Type.FORWARD_ONLY) && (this.resultSetConcurrency == java.sql.ResultSet.CONCUR_READ_ONLY)
                && (this.query.getResultFetchSize() == Integer.MIN_VALUE));
    }

前面两个值在JDBC的源码中有直接设置默认值没有去过多了解，最后一个值则是fetchSize

Xmx100m，fetchSize = Integer.MIN_VALUE，耗时达到了惊人的40s，且内存消耗最高80m，只平稳的发生了young gc
Xmx50m，fetchSize = Integer.MIN_VALUE，耗时50s，程序发生了频繁的young gc，占用了极大cpu严重影响吞吐量

也就是说，在大数据量下的查询，正确的流式查询应该是Cursor + fetchSize = Integer.MIN_VALUE

在Mapper中的写法是

    @Options(fetchSize = Integer.MIN_VALUE)
    @Select("select * from table")
    Cursor<Table> listByCursor();

在xml中的写法是，-2147483648则是Integer.MIN_VALUE的值

  <select id="listByCursor" resultMap="BaseResultMap" fetchSize="-2147483648">
        select * from table
  </select>

可以具体查看mybatis plus中呢，可以具体查看mybatis plus的官方文档

MyBatis-Plus 从 3.5.4 版本开始支持流式查询，
这是 MyBatis 的原生功能，通过 ResultHandler 接口实现结果集的流式查询。
这种查询方式适用于数据跑批或处理大数据的业务场景。

但需要注意一个地方，官方文档中案例实际效果相当于上面单独使用了Cursor
我们既可以通过配置mybatis-plus.configuration.default-fetch-size为具体值来控制达到游标查询的效果，
也可以设置为-2147483648达到真正的流式查询

VVV