怎么利用 PreparedStatement.setFetchSize() 优化从数据库读取大数据集的性能

怎么利用 PreparedStatement.setFetchSize() 优化从数据库读取大数据集的性能

setFetchSize() 不是“一次查多少条”，而是“一次从网络拿多少条”

先澄清一个常见的误解：很多人以为 setFetchSize() 是给数据库下达指令，让它只返回指定数量的行。其实不然，这个参数控制的是 JDBC 驱动从数据库服务器**分批拉取结果集时，每一批要拿多少行**。它的底层逻辑，是调整网络缓冲区和内存分配的节奏，而不是去限制数据库的查询结果。

主流数据库如 MySQL 的 mysql-connector-ja va 和 PostgreSQL 的 pgjdbc 都支持这个机制，但它们的“脾气”可大不相同：MySQL 默认是关闭流式读取的，需要额外配置；而 PostgreSQL 则默认就启用了游标式获取。

• 不设置或设为 0：驱动很可能会图省事，一次性把所有结果都加载到应用内存里，这就埋下了内存溢出（OOM）的风险。
• 设为正整数 N：驱动会尝试按每批 N 行向数据库发送 fetch 请求。不过，它到底生不生效，还得看驱动和数据库的具体配置。
• 对于 Oracle 数据库：除了设置 fetchSize，通常还需要确保创建 Statement 或 PreparedStatement 时，指定 ResultSet.TYPE_FORWARD_ONLY 和 ResultSet.CONCUR_READ_ONLY 这两个参数，流式读取才能正确工作。

MySQL 下必须配 useCursorFetch=true 才能生效

这里有个大坑：MySQL 驱动默认采用的是“一次性缓存全量结果”的模式。所以，如果你只是单独调用 ps.setFetchSize(1000)，完全不会起作用。必须在数据库连接 URL 中显式开启游标获取功能：

jdbc:mysql://localhost:3306/db?useCursorFetch=true

否则，哪怕你的代码写得再规范，驱动还是会固执地把几百万行数据一股脑儿全塞进堆内存，然后才允许你开始遍历 ResultSet。怎么验证配置生效了呢？一个实用的方法是观察 GC 日志或者堆内存的增长曲线——如果参数设了但没配对，内存占用依然会线性飙升。

• 建议搭配使用：除了 useCursorFetch=true，还可以在 URL 中加上 &defaultFetchSize=1000 作为全局兜底值。
• 注意功能限制：一旦开启游标，产生的 ResultSet 将不再支持 rs.last() 或 rs.getRow() 这类需要随机访问的方法，因为它变成了只能向前遍历的流。
• 事务的影响：事务隔离级别本身不影响 fetch 行为，但长时间不提交的事务可能会延长游标在服务器端的持有时间。

PostgreSQL 下 setFetchSize() 基本即开即用，但别设太大

相比之下，PostgreSQL 的 pgjdbc 驱动就“友好”多了，它默认就支持服务器端游标。调用 setFetchSize() 后，驱动会自动在后台触发 DECLARE CURSOR 和 FETCH 的流程。不过，也别高兴得太早，这里也有讲究：

• 值不是越大越好：如果把 fetchSize 设得过大（比如超过10000），反而可能拖慢整体吞吐。虽然网络往返次数减少了，但单次传输的数据包变得非常庞大，很容易卡住 TCP 缓冲区，造成等待。
• 经验值区间：根据多数实践，将值设置在 500 到 2000 之间是比较稳妥的。具体多少合适，还得看单行数据的大小——假设每行数据约10KB，fetchSize=1000 就意味着一次网络传输要搬运将近10MB的数据。
• 注意查询优化：如果你的 SQL 语句中已经包含了 LIMIT 子句，驱动可能会“自作聪明”地忽略 setFetchSize()，转而采用更激进的优化策略，因为结果集本身已经被限制了。

来看一个典型的代码片段：

PreparedStatement ps = conn.prepareStatement("SELECT * FROM huge_table WHERE status = ?");
ps.setFetchSize(1000);
ps.setString(1, "active");
ResultSet rs = ps.executeQuery(); // 注意：游标声明是在执行查询这一刻才真正发起的

别忘了关闭 ResultSet 和 PreparedStatement

使用 setFetchSize() 开启流式读取后，游标资源是由数据库服务器在维持的。如果应用层没有及时关闭 ResultSet，服务器端的游标就不会被释放，久而久之可能导致数据库连接池耗尽，或者数据库直接报出 cursor not found 之类的错误。这一点在手动管理资源（而非使用 try-with-resources 语法）时尤其容易被遗漏。

• 务必确保关闭：一定要调用 rs.close()，或者直接使用 JDK 7+ 提供的 try-with-resources 语法自动管理。
• 级联关闭：调用 PreparedStatement.close() 通常也会级联关闭其关联的 ResultSet，但显式地进行关闭操作仍然是更可控、更推荐的做法。
• 框架行为：像 Spring JDBC 的 JdbcTemplate 这类框架，默认会帮我们关闭资源，但如果你在自定义的 ConnectionCallback 中操作，仍需手动处理。

最后提一个最致命也最常被忽略的点：在流式读取的场景下，如果程序因为异常而提前退出，但 finally 块又没有覆盖到所有异常分支，就会导致游标资源泄漏。这个问题，往往比性能调优本身更加致命。