先说个真实场景。我早些年做数据迁移写了个导出程序逻辑很简单SELECT * FROM 大表然后逐行写入文件。本地测试没问题一上生产跑了不到两分钟JVM直接OOM接着数据库连接被占死整个应用卡了十几秒。后来查了半天问题就出在JDBC默认的查询行为上——所有结果一次性被拉到客户端内存里。也就是从那天起我才认真去研究JDBC的三种查询模式普通查询、流式查询、游标查询。这三种方式表面看只是executeQuery()之后的处理差异实际背后是“数据到底存在哪、什么时候传、占多少内存”的完全不同的机制。这篇文章我就把自己踩过的坑、对比过的测试数据、以及最终的选型思路完整分享出来给正在跟大结果集较劲的兄弟们一个参考。1. 三种查询方式究竟差在哪先给个整体认知别一上来就纠结配置参数。JDBC执行一条查询数据从MySQL服务端到客户端应用中间经过网络传输、驱动解析、ResultSet封装。普通、流式、游标这三种模式区别就在于“服务端什么时候发数据”和“客户端什么时候收数据”。1.1 普通查询一把梭哈拉全量普通查询是MySQL Connector/J的默认行为。你调用executeQuery()后驱动会阻塞等待MySQL服务端返回完整的结果集报文所有行数据一次性写入客户端内存然后再把ResultSet对象交给你遍历。这种方式写起来最顺手代码最简单性能在小数据量下也最快因为只有一次网络往返。但代价是内存。如果查询结果有500万行每行平均2KB光结果集就可能占10GB内存OOM只是时间问题。1.2 流式查询边读边扔的客户端流流式查询的触发条件是Statement.setFetchSize(Integer.MIN_VALUE)。在这个模式下驱动不会等全部数据到达才返回而是从网络流中读一行返回一行。你的业务代码调用rs.next()一次驱动就去底层socket读一条记录。注意一个重要事实MySQL服务端仍然会完整生成结果集并发送只是客户端驱动“按需取用”不会把所有数据囤在内存里。所以流式查询的本质是“客户端流式”不是“服务端流式”。它的特点是客户端内存占用恒定但读取过程中连接被独占直到结果集读取完毕。1.3 游标查询服务端真正的分页取数游标查询需要设置useCursorFetchtrue并且fetchSize必须为正整数。这个模式下MySQL服务端会在临时表上创建真正的游标第一次只发送fetchSize行数据给客户端客户端处理完这批之后再通过COM_STMT_FETCH协议向服务端要下一批。这才是名副其实的“服务端游标”。优点是你可以在处理一批数据后做点别的事情再取下一批连接不会被一个巨大的结果集一直占着缺点是服务端要维护游标状态有额外的临时表、排序、磁盘IO开销。2. 普通查询的原理与实操细节普通查询虽然简单但很多人对它的理解其实有偏差。我经常在代码评审里看到有人以为ResultSet是懒加载的以为遍历到哪一行才从数据库读哪一行——这完全是误解。2.1 普通查询背后发生了什么为了说清楚我直接讲MySQL协议层面的事情。当你执行一条查询服务端会按照行数分包发送给客户端。MySQL Connector/J在普通模式下会一口气把socket缓冲区里所有结果集数据读出来组装成内存里的RowData结构。这期间你的应用线程是阻塞的直到整个结果集读取完成executeQuery()才返回。也就是说executeQuery()耗时包括了“SQL执行时间 全量数据传输时间 全量数据组装时间”。这是很多人忽略的点普通查询下executeQuery()的耗时和结果集大小强相关结果集越大这个方法阻塞越久。2.2 什么时候用普通查询就够了普通查询不是洪水猛兽。我的经验是结果集在几千行以内单行数据不超过几百字节直接用普通查询完全没问题。比如后台管理系统的列表页分页查个几百条或者业务中需要一次性取出配置表所有数据做缓存预热。这些场景用普通查询代码简洁性能最优没必要为了“炫技”去引入流式或游标。还有一类场景必须用普通查询你需要ResultSet支持可滚动TYPE_SCROLL_INSENSITIVE或可更新的结果集CONCUR_UPDATABLE。流式查询强制要求结果集是TYPE_FORWARD_ONLY和CONCUR_READ_ONLY游标查询同样不支持滚动结果集。如果你要在大结果集里随机跳转只能靠普通查询在内存里硬扛或者把数据先拉出来自己分页。2.3 普通查询的潜在隐患普通查询最怕的是“不可见的大结果集”。比如一条联表查询你以为只会返回几百行结果因为数据质量问题变成了几十万行瞬间内存暴涨。这种问题在测试环境通常发现不了因为测试数据量小生产数据一多就炸。另一个坑是连接池。普通查询执行时间越长连接占用时间越长。如果连接池最大连接数是10同时有10个大查询在跑后续所有请求都会排队等连接。我之前排查过一个线上故障最后定位到就是某个报表查询把连接池打满了普通查询在executeQuery()阶段阻塞时间太长导致。普通查询的核心代码不用多写大家都会。我想强调的是用普通查询一定要养成设置maxRows或queryTimeout的习惯至少给查询兜个底防止SQL写得不好或者数据量异常时把应用拖垮。Statement stmt conn.createStatement(); // 限制最多返回10000行防止异常大结果集打爆内存 stmt.setMaxRows(10000); // 限制执行时间超过10秒直接抛异常 stmt.setQueryTimeout(10); ResultSet rs stmt.executeQuery(select * from biz_order where create_time 2024-01-01);3. 流式查询的正确打开方式流式查询是我做大数据量导出时最常用的方案。它把“一次性拉全量”变成了“逐行读取”内存占用稳定在极低水平。但它的坑也最多配置不对、使用不当反而会引发更严重的连接问题。3.1 流式查询的核心配置流式查询的触发方式在不同版本的Connector/J里略有区别。比较通用的做法是Connection conn dataSource.getConnection(); Statement stmt conn.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY ); stmt.setFetchSize(Integer.MIN_VALUE); ResultSet rs stmt.executeQuery(select * from big_table);重点在于两点。第一createStatement()必须指定TYPE_FORWARD_ONLY和CONCUR_READ_ONLY这是流式查询的硬性前提。如果你用默认的TYPE_FORWARD_ONLY、CONCUR_READ_ONLY其实也行但显式声明更稳妥。第二fetchSize必须设置为Integer.MIN_VALUE这是一个魔法值驱动看到这个值才会进入流式模式。顺带提一句网上很多文章说“MySQL JDBC流式查询只要设置fetchSizeInteger.MIN_VALUE”这是对的但其实有个前提连接URL里不能设置useCursorFetchtrue。如果你同时设置了useCursorFetchtrue和fetchSizeInteger.MIN_VALUE驱动会走游标逻辑Integer.MIN_VALUE会被当成一个异常的正整数处理反而报错。3.2 流式查询的代码实现与执行过程来看一个完整的流式查询导出代码try (Connection conn dataSource.getConnection(); Statement stmt conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) { stmt.setFetchSize(Integer.MIN_VALUE); stmt.setQueryTimeout(600); try (ResultSet rs stmt.executeQuery(select * from big_table)) { while (rs.next()) { // 逐行处理可以是写文件、发消息、落库 processRow(rs); } } }执行过程是这样的executeQuery()返回后客户端实际上只收到了第一行数据或者极少量起始数据ResultSet内部有一个状态标记。每次调用rs.next()驱动检查下一行数据是否已经在本地如果不在就继续从网络流中读取下一行。这意味着整个while循环期间网络连接始终处于“读取中”的状态。这个模式我实际用下来500万行数据导出到CSVJVM堆内存稳定在几百MB以内执行时间取决于网络和业务处理速度。对比普通查询的OOM效果立竿见影。3.3 流式查询的四大限制流式查询不是万能的我用下来总结了四个必须记住的限制。第一连接独占。流式查询过程中同一个Connection不能再执行任何其他SQL。因为驱动正在从socket里持续读数据如果你在while循环里用同一个连接去查另一张表会触发“Connection is busy”之类的异常甚至会导致协议错乱。这就是为什么流式查询一定要用独立的连接不能和业务操作共用。第二必须读完或显式关闭。如果while循环里提前break或者抛异常退出了ResultSet没有被读取完驱动内部可能还残留着未读完的数据包。此时直接调rs.close()驱动会尝试把剩余的包读完再释放连接这个“清理”过程可能会阻塞很长时间。我之前遇到过一个问题导出任务处理到一半失败连接池里的连接被占用了几分钟才释放就是这个原因。第三不支持自动提交切换。流式查询要求连接处于非自动提交模式吗并不是硬性要求但如果你在读取过程中调了commit()或rollback()会破坏流式读取状态。我的建议是流式查询期间老老实实读取不要做任何事务操作。第四无法随机访问。ResultSet只能调用next()向后遍历不能previous()、不能absolute()跳到指定行。这决定了流式查询只适合“顺序处理”的场景。3.4 流式查询与事务的相互作用这里有个容易忽略的点流式查询本身不强制要求关闭自动提交但如果你在事务里使用流式查询事务持续时间会很长。因为你要把整个结果集读完才可能提交或回滚长时间事务会带来锁和undo log膨胀的问题。所以我的习惯是流式查询尽量用自动提交模式读完数据直接关闭连接。如果业务上确实需要事务保护每条记录的处理那要评估到底是“边读边写”需要的长事务还是可以先查询后统一处理的短事务。这个没有标准答案得根据业务权衡。4. 游标查询的完整使用方案游标查询是我在处理“超大结果集 分批次取数”时的首选。它跟流式查询的最大区别在于服务端真正承担了保存结果状态的责任客户端可以“取一批、歇一会儿、再取一批”。4.1 游标查询的配置参数游标查询需要两个条件同时满足连接URL参数jdbc:mysql://host:3306/db?useCursorFetchtrueStatement上设置fetchSize为正整数比如1000注意useCursorFetch是连接级别的参数一旦开启你在这条连接上执行的所有预编译语句都会按游标模式处理。这会影响普通小查询的性能所以我不建议在业务主连接上全局开启而是专门准备一个用于大查询的连接。如果你用的是连接池可以配置一个独立的DataSource专门给游标查询用或者在获取连接后动态修改URL参数不同连接池实现方式不一样HikariCP可以通过DataSource属性配置。4.2 游标查询的代码实现先看代码// 连接URL需要包含 useCursorFetchtrue Connection conn dataSource.getConnection(); // 游标查询必须关闭自动提交 conn.setAutoCommit(false); PreparedStatement ps conn.prepareStatement( select * from big_table order by id, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY ); // 每次从服务端拉取1000行 ps.setFetchSize(1000); ResultSet rs ps.executeQuery(); int batch 0; while (rs.next()) { processRow(rs); batch; if (batch % 1000 0) { // 也可以在这里做一些批处理操作比如积攒1000条后批量insert flushBatch(); // 注意这里不能执行新的SQL但可以执行非SQL逻辑 } } rs.close(); ps.close(); conn.commit(); conn.close();这里有几个关键点。第一个就是setAutoCommit(false)官方文档要求游标查询必须显式关闭自动提交否则会报错或者游标不生效。第二个是fetchSize决定每次从服务端拉取的行数我下面专门讲怎么设。第三个是游标模式下while循环里读取完下一批数据时驱动会自动发起新的取数请求这个过程中连接仍然被占用所以同样不能在循环里执行其他SQL。4.3 fetchSize到底设置多大合适这是游标查询里最值得说的参数。fetchSize决定了客户端和服务端之间每次传输的数据量。设置太小比如10行那每处理10行就要发起一次网络请求10万行就是1万次往返性能直接拉胯。设置太大比如100万行那和普通查询没什么区别一次性把数据拉进内存失去了游标的优势。我举一个实际测算的例子。假设单行数据约1KB网络延迟在机房内约0.1ms。fetchSize设为1000每批次的网络传输开销大约0.1ms 1MB传输时间万兆网卡约1ms处理100万行总共1000批次额外开销约1-2秒完全可接受。如果fetchSize设为10批次就变成10万次光网络往返就要10秒以上这还不包括服务端处理游标取数的开销。个人的经验值单行数据小几KB以内网络好fetchSize2000到5000比较平衡单行数据大比如包含大字段、JSON建议fetchSize500到1000避免单批次数据量过大如果查询结果只需要做简单聚合统计可以调大到10000减少网络往返另外一个建议游标查询不要再额外LIMIT因为游标本身就是为了避免一次性加载。如果你只需要前100万行可以在业务层处理到100万行后主动close()但要注意这会增加游标清理开销。4.4 游标查询的服务端代价游标查询那么好用是不是所有大查询都应该用它我的建议是要看服务端资源是否扛得住。MySQL实现游标时通常会把结果集物化到临时表中如果结果集很大临时表会写到磁盘带来额外的磁盘IO。而且排序字段如果没有索引服务端要做filesort游标创建时间会明显变长。这里有个有意思的对比流式查询的服务端负载是“全量发送”游标查询的服务端负载是“物化 分批检索”。如果只是单纯地顺序读取全量数据流式查询的服务端代价其实更小如果需要“暂停、续取、随意控制进度”游标查询才值得付出额外的服务端代价。我通常这样判断结果集在100万行以内用流式结果集更大且需要断点续跑或分批提交事务用游标结果集不大但SQL复杂、需要看执行计划的老老实实用普通查询。5. 三种方式对比与选型建议前面分别讲了三者的原理和代码这节做个横向对比顺便分享我在实际项目中是怎么选的。5.1 核心对比一图流对比维度普通查询流式查询游标查询客户端内存占用高一次性全量低逐行读取中按批次读取服务端行为一次性发送全部一次性发送全部物化游标分批发送触发条件默认fetchSizeInteger.MIN_VALUEuseCursorFetchtruefetchSize0连接占用方式执行期间占用全程独占直到读完按批次占用间隙可做非SQL操作是否支持滚动结果集支持不支持不支持是否支持随机跳转支持不支持只支持顺序取数网络往返次数1次较少驱动内部处理结果集大小/fetchSize次适合场景小结果集、需滚动超大结果集顺序导出超大结果集分批处理5.2 我的选型经验先说结论我平时处理大数据量查询时的默认选择是普通查询处理常规业务流式查询处理导出和全量扫描游标查询处理需要分批、可中断的大任务。举个例子做数据同步任务时源表有2000万行数据每条记录大概500字节同步到目标库。我用的是流式查询因为处理逻辑是“读一行、转换一行、写一行”完全顺序执行不需要中断。连接是专线带宽充足流式查询在这个场景下内存占用最低速度也不慢。再举个例子做数据对账任务需要从主库拉出全部数据按ID分段比对并且每一段比对完要记录断点方便下次从断点继续。这个场景我用游标查询。因为任务允许中断我需要“拉到第N行后停下来、记录进度、关闭连接”等下次任务再从游标位置继续。流式查询做不到这种进度控制游标查询配合fetchSize则很灵活。还有一个大家容易忽略的选型维度数据库负载。流式查询对数据库连接占用时间长但对数据库服务端内存友好游标查询占用连接时间短但服务端要临时表物化对IO和内存有额外压力。如果数据库本来负载就高建议优先流式查询如果数据库资源充足但应用连接池吃紧游标查询能更快释放连接。6. 常见问题与排查技巧实录最后这部分是我在实际项目中遇到的高频问题基本都是从生产环境摸爬滚打出来的经验希望能帮大家少走弯路。6.1 设置了fetchSize1000为什么还是OOM这个问题出现频率非常高。很多人从其他数据库比如PostgreSQL的文档里看到“设置fetchSize可以避免大结果集内存占用”然后在MySQL里照抄结果发现没用。原因在于MySQL Connector/J在普通模式下fetchSize只是一个“建议值”驱动并不保证按批次从服务端取数。只有满足前面说的流式Integer.MIN_VALUE或游标useCursorFetchtrue 正整数条件时fetchSize才真正生效。如果你只是设置setFetchSize(1000)但没开useCursorFetch数据照样一次性全量加载到内存。6.2 流式查询报“Connection is busy”怎么办这个错误通常出现在你在while (rs.next())循环里又拿同一个连接去执行了其他SQL。比如stmt.setFetchSize(Integer.MIN_VALUE); ResultSet rs stmt.executeQuery(select * from big_table); while (rs.next()) { // 错误用法又用同一个conn执行其他SQL stmt2 conn.createStatement(); stmt2.execute(update ...); }流式查询期间连接处于“读取中”状态不能处理新请求。解决办法是大数据量处理永远用独立连接处理完再归还连接池。如果业务逻辑里必须边读边查其他表可以考虑把数据先临时存储比如写入本地文件、临时表再重新开连接处理。还有一个隐蔽情况流式查询的ResultSet没读完就关闭连接不会立刻恢复可用状态。Connector/J在close()时会尝试清理未读完的数据这个清理过程可能很慢。所以流式查询一定要保证while循环完整执行完或者在finally里先循环把剩余数据读完再关闭。6.3 游标查询开启后其他SQL变慢这个现象让我纠结过很久。后来看了监控才发现连接URL里设置了useCursorFetchtrue后该连接上的所有查询都按游标模式执行。比如一条本来只要查100行的列表页SQL因为游标模式只取fetchSize行如果fetchSize设置得足够大可能还好但如果fetchSize设置太小比如100每条列表页SQL都要多次往返服务端取数性能自然变差。我的建议是不要在主连接池上开启useCursorFetch单独配置一个“大查询专用”数据源只有需要游标的地方用这个数据源。6.4 MyBatis/Flink场景下的坑现在很多项目用MyBatisMyBatis 3.4支持CursorT接口你可以直接定义返回Cursor的Mapper方法。但它底层就是包装了JDBC的流式或游标查询。用的时候要注意Cursor必须在一个Transactional方法里使用否则连接可能提前关闭。Flink的JDBC连接器在读MySQL大表时常见异常是连接超时或SocketTimeoutException。我排查过几次基本都是两个原因一是连接URL没设置socketTimeout二是流式查询占用连接时间太长被服务端wait_timeout杀掉。建议在JDBC URL里合理设置connectTimeout和socketTimeout并且对于流式查询的大任务定期发送心跳或者每次多取几行降低单次查询的总时长。6.5 连接池参数调整的心得不论用流式还是游标查询大结果集查询都会增加单条连接的占用时间。连接池的maxLifetimeHikariCP中的配置如果设置得太短连接在查询中途被回收整个查询就废了。我的做法是单独为大数据量查询建一个DataSource设置大一点的connectionTimeout比如30秒maxLifetime不要小于任务预计最长执行时间maximumPoolSize设小一点比如5避免大量连接同时执行大查询把数据库压垮。这样能保证导出任务稳定运行同时不影响主业务连接池。另外补一个细节如果你用HikariCP并且设置的connectionTimeout是默认值30秒而流式查询前面的executeQuery()阶段因为数据量大阻塞超过了30秒就会从连接池获取连接超时。这种情况下不是连接不够而是executeQuery()占用的时间超过了获取连接的等待预算。结尾这三种查询方式我实战用了很多年从一开始被OOM打懵到后来研究Connector/J源码才彻底弄明白。现在回头看普通、流式、游标对应的是“客户端全量缓存”、“客户端流式读取”、“服务端分批缓存”三种数据交付模型没有绝对的好坏只有合不合适。最后分享一个我的小习惯任何SQL查询先想想“结果集最大可能有多少行”再决定用哪种方式这个习惯帮我避免了好几次线上事故。如果你在项目中还遇到过其他跟这三种查询相关的诡异问题欢迎在评论区留言交流。