MySQL大数据量查询方案

在业务开发过程中,有时会遇到大数据量查询的情况,如果将一次性加载全部查询结果的话会导致内存量暴增,甚至出现OOM异常,为了解决这种场景,MySQL中提供了针对此类场景解决方案,本文针对此类场景进行了研究.

测试表

DROP TABLE IF EXISTS tb_user_stream;
CREATE TABLE tb_user_stream(
  id bigint(11) not null auto_increment comment '主键',
  name varchar(64) not null comment '姓名',
  address varchar(255),
  primary key(id)
)engine=InnoDB,charset=utf8,comment='用户表';
INSERT INTO tb_user_stream(name,gender,address)
values ("zhangsan","male","吴巷1号, 营口, 宁 418354"),("李四","female","丁栋3号, 大庆, 鄂 151355");
1
2
3
4
5
6
7
8
9

使用 JDBC 驱动：

<groupId>mysqlgroupId>
<artifactId>mysql-connector-javaartifactId>
<version>5.1.49version>
1
2
3

Mybatis 版本：

<groupId>org.mybatisgroupId>
<artifactId>mybatisartifactId>
<version>3.5.10version>
1
2
3

使用 ResultHandler 接收结果

我们平常使用 Mybatis进行对象映射逻辑是这样子的：

<select id="select">
    SELECT * FROM tb_user_stream
select>
1
2
3
4

// UserMapper.java
List<User> select();
1
2

在这样逻辑下，我们得到的返回结果集会被封装成为一个 List 对象，当我们进行大数据量查询时，Mybatis 不会进行额外处理，而是封装为一个大的 List 返回，在这种情况下这样的大 List 就有可能将内存撑爆。

要想解决这个问题，我们可以调整接口方法，不使用 List 接收结果，而是使用一个 ResultHandler 回调返回结果：

// UserMapper.java
void select(ResultHandler<User> handler);
1
2

调用时传入一个 lambda 表达式获取结果：

mapper.handlerFetch50000(resultContext -> {
    UserDO user = resultContext.getResultObject();
    System.out.println(user);
});
1
2
3
4

相同的 SQL 调整下接口方法。这样由于是逐条处理,所以在 JDBC 取到数据后会立即回调 ResultHandler处理数据,在数据处理完成后重复上述逻辑，已经处理过的数据就可以被GC掉，避免占用大量内存。

从上面对比图中可以看到，在使用 ResultHandler 接收结果的情况下，内存增长速度更加平缓从而降低了 OOM 的风险。

Stream 流式查询

通过配置实现 Stream 流式查询

我下面要讲一些流式查询的实现思路,但是从实现上讲,使用ResultHandler 的方式已经完全能够满足要求了.流式查询的思路不过是将每次取回的批量数据变为取单条数据并且为流式读取的逻辑。

使用 JDBC 实现流式查询的的条件有两个，首先需要设置 fetchSize 为 Integer.MIN_VALUE,然后设置取值方式为java.sql.ResultSet.TYPE_FORWARD_ONLY

stmt.setFetchSize(Integer.MIN_VALUE);
stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
1
2

在 JDBC 中，如果判断上述条件满足，则会将查询方式设置为流式查询：

// com.mysql.jdbc.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
  * 
  * @return true if this result set should be streamed row at-a-time, rather
  *         than read all at once.
  */
protected boolean createStreamingResultSet() {
    return ((this.resultSetType == java.sql.ResultSet.TYPE_FORWARD_ONLY)
            && (this.resultSetConcurrency == java.sql.ResultSet.CONCUR_READ_ONLY)
            && (this.fetchSize == Integer.MIN_VALUE));
}
1
2
3
4
5
6
7
8
9
10
11
12
13

在 Mybatis 中，我们只需要在编写 SQL 时设置 fetchSize 为 -2147483648 即可满足流式查询的条件：

<select id="select" fetchSize="-2147483648">
    SELECT * FROM tb_user_stream
select>
1
2
3
4

其中 -2147483648 是 Integer.MIN_VALUE。当然，如果我们在设置流式查询后，还是直接使用 List 方式接收结果，则仍然看不到流式查询的效果，一种解决方案就是使用上面提到的 ResultHandler 回调结果：

// UserMapper.java
void select(ResultHandler<User> handler);
1
2

或者直接使用注解的方式：

@Select("select * from tb_user_stream")
@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = Integer.MIN_VALUE)
@ResultType(UserDO.class)
void select(ResultHandler<User> handler);
1
2
3
4

上图是使用流式查询后堆内存的变化情况，相对于只使用ResultHandler的情况内存占用进一步降低。

使用游标 Cursor 实现流式查询

当然除了上述实现方式外我们还可以直接使用Mybatis 提供的 Cursor 以游标的方式接收结果实现流式查询,这种方式下就不需要我们设置 fetchSize了。

在Cursor<UserDO> select();
1

使用 Cursor 进行获取结果的方式在 Spring 中可能会遇到问题，这是由于Spring 在没有事务的情况下在查询结果返回后就将不再使用的连接关闭了，导致虽然获取到了 Cursor ，但是在拿到 Cursor 的同时连接池就将对应的连接回收了，最终的 Cursor 可能是一个已关闭的状态。

要想解决这个问题也很简单，创建一个事务 Transaction ，使得整个查询逻辑都在事务中执行即可。

transactionTemplate.execute(status->{
  try(Cursor<UserDO> cursor = userMapper.select()){
    cursor.forEach(System.out::println);
  }
})
1
2
3
4
5

或者手动创建一个connection，由开发者自己维护 connection 的关闭：

try(Connection connection = sqlSessionTemplate.getConnection()){
  Cursor<UserDO> cursor = userMapper.select();
  cursor.forEach(System.out::println);
}
1
2
3
4

参考资料

MySQL如何流式读取千万级大数据

MySQL 流式查询的用法和坑

JDBC操作MySQL（3）—查询（普通、流式、游标）

聊聊jdbc statement的fetchSize

jdbc.fetch-size官网文档

What does Statement.setFetchSize(nSize) method really do in SQL Server JDBC driver?

JDBC读取数据优化-fetch size

Fetch Size 与 JDBC 内存管理

mybatis之ResultHandler如何使用

mybatis实现mysql流式查询的原理

Mybatis流式查询避免OOM