MySQL Streaming 的使用和约束

在 Oracle 下,开发人员开发 Java 连接数据库进行超大结果集的查询后处理的程序时,可以通过设置游标进行小批量、迭代多次获取数据记录,避免程序出现 OOM 异常。

而 MySQL 本身并不提供游标功能,而是通过 Connector/J 客户端阻塞式逐行读取数据记录交给业务进行处理。这种“流式(Streaming)”读取看似是很快很便利方式,实际是对应用场景提出了很高的约束,使用不当容易对照成数据库链接长期占用,链接超时,消耗数据库资源等问题。

在 MySQL 中查询超大结果集时,由于 MySQL JDBC 的 ResultSet 默认是接收完全部数据后再交由应用程序进行处理的。对于数据量很大的查询(如报表统计)、有限的 JVM 内存等程序,很容易就照成应用程序 OOM 异常(java.lang.OutOfMemoryError: Java heap space)。对于这个错误解决起来也很简单。

MySQL JDBC 支持客户端的流方式(Streaming)读取数据,也叫 Client Side Cursor。即应用程序一边从数据库拉取数据,每获取到一条数据后就可以立即丢给业务逻辑程序处理。代码实现也很简单:

只要在初始化 Statement 的时候,设置这几个参数即可:ResultSet.TYPE_FORWARD_ONLYResultSet.CONCUR_READ_ONLYInteger.MIN_VALUE

1
2
preparedStatement = connection.prepareStatement(formatSql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
preparedStatement.setFetchSize(Integer.MIN_VALUE);

setFetchSize=Integer.MIN_VALUE 这是一个 magic number,而不是指定每次读取多少数据。不能改成其他值。具体参考官方文档

而实际上,在高版本的 JDBC 驱动,ResultSet.TYPE_FORWARD_ONLYResultSet.CONCUR_READ_ONLY 这两个参数是 Statement 里面默认设置的(这个看 JDBC Driver 的源码可以了解到,文档上是没写的,这里是个坑),所以实际我们只需要设置 preparedStatement.setFetchSize(Integer.MIN_VALUE); 这一行代码也是可以的。

下面是一个简单的使用示例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Streaming {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/test";
String username = "root";
String password = "Aa123456";
Connection conn = DriverManager.getConnection(url, username, password);
Statement statement =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
statement.setFetchSize(Integer.MIN_VALUE);
// MySQL connector 的 StatementImpl 的 createStreamingResultSet 方法中明确指出:
// We only stream result sets when they are forward-only, read-only, and the fetch size has been set to Integer.MIN_VALUE
// 也就是说必须设置这三个属性才能做到流式读取
int batchSize = 2000;
ResultSet rs = statement.executeQuery("select * from steaming_test");//一般的数据读取
while (true) {
for (int rowIndex = 0; rowIndex < batchSize; rowIndex++) {
if(!rs.next()){
return;
}
System.out.println("one line");
}
System.out.println("one batch");
}
}
}

指定完 Statement 的这几个参数后,执行 SQL 获取到的 ResultSet 就会在调用 rs.next() 方法时从 MySQL 服务端获取一条数据记录,然后执行后续的业务逻辑程序,依次迭代完成所有的数据记录获取和处理。而不是等 ResultSet 接收 MySQL 端查询到的所有数据后才业务逻辑程序。如果是大结果集查询,采用 Streaming 方式,程序很快就有查询记录可以进行处理。

通过简单的测试,如果是单纯的获取数据记录,采用 Streaming 方式是更快的。

为什么 MySQL 没有默认就采用 Streaming 方式?

Streaming 方式看似很好,而且测试显示获取数据更快,那为什么 MySQL 没有默认就采用 Streaming 方式?

其实很简单,因为采用 Streaming 方式,获取数据库记录于业务逻辑处理就变得耦合了。整个数据结果集的获取时间等于数据库查询结果获取时间 + 业务逻辑程序执行时间。这导致了当前查询需要长时间占用一个链接;MySQL 当前查询结果集需要长时间缓存;如果业务逻辑程序中还有 SQL 语句执行,那当前链接无法复用。

总结 Streaming 的主要问题

  • 长时间占用数据库链接
    • 单个链接占用时间长
    • 同个事务内链接无法复用
  • 数据库查询结果集需要缓存的时间大大延长
  • 数据库当前锁
    • 不同链接无法获取到事务锁

这里有两篇很好地讨论了 Streaming 流式读取记录的代价,分别:The Cost of Streaming Data from MySQLHow does MySQL result set streaming perform vs fetching the whole JDBC ResultSet at once

采用 Streaming 方式在 MySQL 协议和数据报文上有没什么影响?

并没有,Streaming 方式有客户端模式(Client Side Cursor)和服务端模式(Server Side Cursor),上面程序例子采用的是客户端模式,MySQL 数据库的数据报文没任何差异。

应用场景

Streaming 的应用场景

目前只有这一种场景建议采用 Streaming 方式读取数据:需要大批量获取数据库记录用于产生其他存储的数据的业务场景

具体的使用例子有:

  • 报表数据统计;
  • 用户查询大批量数据后用于导出;
  • 获取整表数据用于全量更新缓存/消息中间件数据等;

而且在这种场景下,同时需要将查询结果集获取和处理程序异步化,避免由于处理程序执行慢导致整个数据库查询和获取结果集时间被拉得很长。

什么场景下不适合使用

有一些场景看似可以用 Streaming 方式处理但不应该用的,比如:

  • 场景 1:数据批量处理
    典型业务场景如:查询一批数据进行业务处理,然后将数据改成下一个流转状态的。
    这种场景可以采用小批量+数据业务状态查询,处理完后再获取下一批数据进行处理,依次遍历完所有待处理的记录。

参考材料