如果数据库有 100 万条数据,你会怎么查?
不少人的第一反应是分页查询:
SELECT id, user_name, phone
FROM user_info
ORDER BY id
LIMIT 1000 OFFSET 0;
每次查 1000 条,处理完再查下一页,循环往复。
这种方式本身没有问题,但如果需要读取几十万、几百万条数据,分页查询不一定是最合适的选择。例如数据导出、历史数据迁移、批量数据校验和生成统计报表,都可能需要处理大量结果。
分页查询需要反复执行 SQL,数据库也要不断处理查询请求。尤其是 OFFSET 越来越大时,性能问题可能逐渐暴露出来。
例如:
SELECT * FROM user_info LIMIT 1000 OFFSET 500000;
虽然只返回 1000 条数据,但数据库可能需要先扫描并跳过前面的 50 万条记录。
有没有其他选择?MyBatis 提供了 Cursor,可以用于流式读取查询结果。
一、MyBatis 流式查询是什么?
普通查询通常会返回一个 List:
List<UserInfo> users = userMapper.selectList();
MyBatis 会将查询结果封装成 List 返回。如果结果有 50 万条,这些数据就需要加载到 Java 内存中。
数据量小时可能感觉不明显,一旦数据量上来,堆内存就容易吃紧,严重时还可能出现:
java.lang.OutOfMemoryError: Java heap space
流式查询的处理方式不同:它不需要先把所有结果装进 List,而是通过游标逐条读取。业务代码处理一条,再继续读取下一条。
这样,应用就不必在 Java 堆内存中保存完整的查询结果集。不过,驱动是否会缓存结果,还需要结合数据库和 JDBC 驱动的配置判断。
二、MyBatis 如何实现流式查询?
先定义 Mapper:
@Mapper
public interface UserInfoMapper {
@Select("""
SELECT id, user_name, phone
FROM user_info
WHERE id > #{lastId}
ORDER BY id
""")
@Options(fetchSize = 500)
Cursor<UserInfo> scanUsers(@Param("lastId") Long lastId);
}
这里有两个细节:
- 返回类型使用
Cursor<UserInfo>,而不是 List。
- 通过
fetchSize 提示 JDBC 驱动获取结果时使用合适的批量大小。
注意,fetchSize 不代表 MyBatis 一定会严格按照每批 500 条读取,具体行为还取决于 JDBC 驱动。示例中的文本块语法需要 Java 15 或更高版本。
接下来编写业务代码:
@Service
@RequiredArgsConstructor
public class UserExportService {
private final UserInfoMapper userInfoMapper;
@Transactional(readOnly = true)
public void exportUsers(Long lastId) {
try (Cursor<UserInfo> cursor =
userInfoMapper.scanUsers(lastId)) {
for (UserInfo user : cursor) {
// 逐条处理,避免一次性加载全部数据
writeToFile(user);
}
}
}
private void writeToFile(UserInfo user) {
// 写入文件的业务逻辑
}
}
这段代码有几个需要注意的地方。
第一,Cursor 使用完毕后要及时关闭,因此这里使用了 try-with-resources。
第二,流式查询依赖数据库连接,游标遍历完成之前不能释放连接。
第三,事务要覆盖整个游标遍历过程。否则,游标还没读取完,数据库连接或 ResultSet 就可能已经关闭。
实际使用时,不同数据库和 JDBC 驱动对流式读取的支持方式并不完全相同,必要时需要额外配置。
三、流式查询有哪些坑?
Cursor 能减少应用层一次性持有的结果,但并不是用了它就万事大吉。以下几个问题需要提前考虑。
1. 不要在循环中执行耗时操作
例如:
for (UserInfo user : cursor) {
remoteService.send(user);
}
如果远程接口响应很慢,数据库连接就会一直被占用。几十万条数据处理下来,连接池中的连接可能长期被这个任务占着。
可以尽量将读取和业务处理解耦,必要时使用有界队列进行批量交接。同时要控制好背压,避免把流式读取变成另一种内存堆积。
2. 不要忘记关闭 Cursor
Cursor 底层依赖 JDBC 的 ResultSet 和数据库连接。如果没有正确关闭,连接就可能一直被占用。
特别是定时导出任务,一旦出现连接泄漏,其他业务也可能受到影响。
3. fetchSize 不是越大越好
fetchSize 设置太小,数据库交互次数可能增加;设置太大,又可能增加驱动端的缓冲开销。
具体数值需要结合数据库类型、驱动实现和实际数据量进行调整。
4. MySQL 需要特别注意
使用 MySQL Connector/J 时,如果希望逐步拉取结果,还需要关注驱动配置。
一种常见方式是使用服务端游标:
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/app_db?useCursorFetch=true
同时,Mapper 中需要配合设置正数 fetchSize。
否则,即便代码使用了 Cursor,驱动仍可能将大量结果缓存在客户端内存中。具体行为应结合驱动版本和配置确认。
四、分页和流式查询怎么选?
选择哪种方式,还是要看业务场景,不必为了使用 Cursor 而强行替换分页。
- 普通列表查询:使用分页,方便前端翻页,也能控制单次查询的数据量。
- 大批量数据导出:可以使用
Cursor,逐条读取和处理。
- 数据迁移:可以考虑流式读取,也可以采用主键游标分页,结合批量写入。
- 批量更新:建议分批处理,避免长事务和长时间占用数据库资源。
流式查询并不意味着一定比分页快。如果数据需要长时间处理,Cursor 会持续占用数据库连接;主键游标分页则可以在每批处理完成后释放连接,更适合某些长时间运行的批处理任务。
因此,流式查询不能简单替代分页。它的价值在于:对于适合顺序处理的大结果集,可以避免应用一次性把几十万条数据装进 List,从而减轻堆内存压力。
下次遇到大数据量导出时,除了分页循环,也可以结合数据库驱动配置和连接占用情况,考虑使用 MyBatis Cursor。