深入了解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的执行引擎对数据库的连接方式提供了两种：内存限制模式和连接限制模式。（参考：