如何通过设置PreparedStatement的setFetchSize()提升大数据集数据库查询效率？

本文共计891个文字，预计阅读时间需要4分钟。

很多人以为 `setFetchSize` 是让数据库只返回指定数量的行，实际上它控制的是 JDBC 驱动从数据库服务器获取结果的批次大小。分批拉取结果集时，每批的行数由 setFetchSize 决定。底层影响的包括网络缓冲区和内存分配节流。

MySQL 的 `mysql-connector-java` 和 PostgreSQL 的 `pgjdbc` 都支持，但行为有差异：

• 不设或设为 0 → 驱动可能一次性把全部结果加载进内存（OOM 风险）
• 设为正整数 N → 驱动按每批 N 行向数据库发 fetch 请求（实际是否生效取决于驱动+数据库配置）
• 对 Oracle，还需确保 ResultSet.TYPE_FORWARD_ONLY 和 ResultSet.CONCUR_READ_ONLY

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

MySQL 驱动默认用“一次性缓存全量结果”的模式，setFetchSize() 单独调用完全无效。必须在连接 URL 中显式开启游标获取：

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

否则即使你写了 ps.setFetchSize(1000)，驱动仍会把几百万行全塞进堆内存，然后才开始遍历 ResultSet。验证是否生效的方法是观察 GC 日志或堆内存增长曲线——设了但没配参数，内存占用依然线性飙升。

• 同时建议加上 fetchSize 参数（如 &defaultFetchSize=1000）作为兜底
• 注意：开启游标后，ResultSet 不再支持 rs.last() 或 rs.getRow() 等随机访问方法
• 事务隔离级别不影响 fetch 行为，但长事务可能延长游标持有时间

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

PostgreSQL 的 pgjdbc 默认支持服务器端游标，setFetchSize() 调用后会自动触发 DECLARE CURSOR + FETCH 流程。不过要注意：

• 设成 10000 以上反而可能拖慢整体吞吐——网络往返次数减少，但单次响应变大，容易卡住 TCP 缓冲区
• 实测中 500～2000 是较稳的区间，具体看单行数据大小（比如每行 10KB，fetchSize=1000 就是 10MB/次）
• 如果查询带 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() 或使用 try-with-resources（JDK 7+）
• PreparedStatement.close() 也会级联关闭关联的 ResultSet，但显式 close 更可控
• Spring JDBC 的 JdbcTemplate 默认会关闭，但自定义 ConnectionCallback 里仍需手动处理

最常被忽略的是：流式读取场景下，异常提前退出时 finally 块没覆盖所有分支，导致游标泄漏。这点比性能调优本身更致命。