上个月我同事在生产环境跑了一条统计SQL结果集大概两百多万行应用直接OOM了。后来我把连接串改了一下加上useCursorFetchtrue问题就消失了。但有意思的是当我接着问他“流式查询和游标查询到底有什么区别”时他愣了半天只憋出一句“流式不占内存”。JDBC三种查询方式确实是后端开发里“听过、用过、但说不清”的典型代表。我最早接触这个坑是做一个数据清洗任务需要把一张千万级的大表全量扫一遍。一开始用普通的PreparedStatement.executeQuery()结果刚跑几分钟JVM堆就满了。后来翻MySQL官方文档和Connector/J源码才把普通查询、流式查询、游标查询这三者的行为差异彻底搞明白。今天就把我实测过的数据、踩过的坑、以及底层的工作原理一次性讲清楚希望能帮你少走弯路。1. 三种查询方式的概念与选型思路很多人以为JDBC查询只有一种写法就是拿到ResultSet之后while(rs.next())遍历。但实际上同样的代码在不同的连接参数和fetchSize配置下底层行为完全不同。理解这三种查询本质上是理解同一个问题当查询结果集很大时数据是从服务端一次性全量拉到客户端还是按需分批拉1.1 普通查询最常用也是内存杀手普通查询就是最常规的写法创建一个Statement或PreparedStatement执行executeQuery()然后遍历结果集。这个过程中MySQL Connector/J驱动会在executeQuery()返回之前把服务端返回的所有行全部读取到客户端内存里。这意味着什么如果你的SQL查出来100万行、每行平均1KB驱动就会在堆内存里构建一个大约1GB的ResultSet。这个阶段不需要你调用next()内存已经被吃掉了。我之前做过一个测试用默认方式查一张200万行的表还没开始遍历JVM堆就从500MB涨到了1.6GB。所以普通查询只适合结果集可控的场景比如前端分页、按主键查单条、或者通过LIMIT限制行数的小查询。生产环境里那种“先全查出来再Java代码里过滤”的写法一旦数据量上来就是OOM的定时炸弹。1.2 流式查询客户端内存很小但网络开销很大流式查询是我解决同事那次问题的候选方案之一。核心思想是设置setFetchSize(Integer.MIN_VALUE)驱动不会在executeQuery()时一次性拉回所有数据而是返回一个流式结果集客户端每次调用next()时驱动才从服务端Socket读取后续数据。这种模式的客户端内存占用极低因为任何时候内存里只保留当前这一行或者极小一批。但它有一个非常容易被忽略的代价如果驱动内部是每行一个网络往返那么读取100万行就需要100万次网络交互整体耗时可能比普通查询慢好几倍。我之前实测在局域网内读50万行数据普通查询大约4秒流式查询花了大半分钟完全不在一个量级。所以流式查询更适合“网络延迟极低、单条记录很小、并且无论如何也没法把结果集全放内存”的场景。1.3 游标查询服务端维护游标客户端分批拉取游标查询是我最终在同事那个场景中采用的方案。它的核心配置是在JDBC URL上加上useCursorFetchtrue和useServerPrepStmtstrue然后连接必须是非自动提交模式同时给Statement设置一个正整数的fetchSize。游标查询的原理是MySQL服务端为这条SQL维护一个游标状态客户端每读取fetchSize行驱动才会向服务端发送一次COM_STMT_FETCH命令拉取下一批数据。这种方式兼顾了内存和性能——客户端内存只和fetchSize成正比网络往返次数等于总行数除以fetchSize。比如fetchSize1000时100万行只需要1000次网络交互远远好于流式查询。我把三种方式整理成一个对比表方便你直接做选型对比项普通查询流式查询游标查询客户端内存占用高等于整个结果集极低基本只缓存当前行低取决于fetchSize网络请求次数较少一次性传输多可能每行一次中等每fetchSize行一次是否需特殊配置不需要setFetchSize(Integer.MIN_VALUE)useCursorFetchtrue、useServerPrepStmtstrue、autoCommitfalse、fetchSize0适合场景小结果集、业务查询超大表、网络极好的内网环境大数据量、需要控制内存和服务端压力主要问题大结果集直接OOM网络开销大结果集必须一次读完配置复杂连接占用时间长2. 底层机制为什么同样是Java代码行为差别这么大三种查询方式的表象差异根源在于MySQL客户端/服务端协议的处理方式不同。这一节我把底层的机制拆开讲理解了这一层你以后遇到问题就不会再靠猜了。2.1 普通查询的时序executeQuery时数据已经全部到内存普通查询走的是MySQL的文本协议或二进制协议。当驱动向服务端发送查询请求后服务端会把结果集编码后通过网络返回。Connector/J驱动默认的ResultSet实现是RowDataStatic这个类在构造时就会把网络流里的所有行全部读进内存形成一个静态的行数组。所以普通查询的耗时大部分花在executeQuery()这一步而不是next()遍历阶段。如果你在代码里给executeQuery()前后打点计时会看到这一步特别慢一旦返回ResultSet后面的遍历其实是纯内存操作非常快。这条特征可以用来判断一条查询到底有没有走普通模式——如果executeQuery()很快就返回了而next()阶段却卡了好久说明大概率不是普通查询。2.2 流式查询的时序executeQuery很快next才是重头戏流式查询的触发条件是setFetchSize(Integer.MIN_VALUE)同时要求ResultSet类型必须是TYPE_FORWARD_ONLY。驱动在executeQuery()时不会一次性把服务端返回的所有行读入内存而是立刻返回一个ResultsetRowsStreaming实现。这时候驱动底层持有的是同一个网络连接上的输入流每次next()操作才会去流里读数据。如果服务端的数据还没完全到达next()就会阻塞等待网络数据。你看到的现象就是executeQuery()瞬间返回然后next()循环里每一行都可能有一个短暂的网络等待。流式查询有一个致命限制结果集没有遍历完之前不能在这个连接上执行任何其他SQL语句。因为驱动持有的网络流还挂着当前结果集的上下文你再执行一个stmt.execute()协议就错乱了。典型报错是Streaming result set com.mysql.cj.protocol.a.result.ResultsetRowsStreamingxxx is still active. No statements were executed when the streaming had finished.这个限制在实际项目里非常麻烦尤其是你用同一个连接做“边查边写”的操作时必须先把流式结果集读完或者主动关闭再去执行写操作。2.3 游标查询的时序服务端维护状态客户端按批拉取游标查询依赖MySQL服务端真正的游标能力。它要求连接使用useServerPrepStmtstrue也就是让驱动使用服务端预处理语句协议。SQL执行后服务端不会把结果全部发送给客户端而是把结果集合保存在服务端的游标数据结构里客户端通过COM_STMT_FETCH命令按批次获取。驱动在next()遍历时内部会维护一个计数器当读取的行数达到fetchSize边界时自动向服务端发起下一次批量拉取。所以游标查询的executeQuery()也是很快返回的但next()阶段是“走走停停”的——每拉取一批数据会有一次网络交互。游标模式对连接的事务状态有硬性要求。MySQL服务端游标是在事务上下文中工作的所以连接必须设置为autoCommitfalse否则驱动无法保证游标跨越多批拉取的一致性。我自己的习惯是拿到连接的第一件事就setAutoCommit(false)最后在finally里统一commit或rollback避免遗漏。2.4 排序与临时表所有查询方式都躲不开的服务端压力这里有一个很多文章没提到的坑无论你用哪种查询方式只要SQL里带了ORDER BY、GROUP BY、DISTINCT、UNION这类需要“先汇集再输出”的操作MySQL服务端都必须先把完整结果集构建出来再开始发送数据。这个构建过程可能会用到内部临时表而临时表一旦超过内存阈值tmp_table_size就会落到磁盘上。我实测过一条带ORDER BY的200万行查询不管是普通、流式还是游标模式第一个next()都等了好几秒——因为服务端在做排序。区别只在于普通查询把数据全部拉到客户端后排序结果分发完就结束了而游标和流式模式下服务端要额外维护游标状态或持续发送服务端内存和临时表压力并没有因为客户端内存降低而减少。所以用游标或流式查询之前一定要先看执行计划。如果出现Using filesort或Using temporary你真正要优化的是索引和SQL本身而不是纠结选哪种查询方式。3. 实操对比三种查询方式的完整代码与配置这一节直接上代码。我以一个实际的数据导出场景为例从MySQL中查询一张100万行的大表需要把数据逐行处理后写文件。分别用三种方式实现你对比看配置差异和执行表现。3.1 环境准备与基线查询先准备好MySQL连接和基础表结构。假设我们有一张t_order表包含id、user_id、amount、created_at四个字段一共100万行。CREATE TABLE t_order ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, created_at DATETIME NOT NULL, KEY idx_user_id (user_id) ) ENGINEInnoDB;JDBC依赖我用的是mysql-connector-j8.0.33版本坐标如下dependency groupIdcom.mysql/groupId artifactIdmysql-connector-j/artifactId version8.0.33/version /dependency连接URL我建议统一使用String url jdbc:mysql://127.0.0.1:3306/test_db ?useSSLfalsecharacterEncodingutf8serverTimezoneAsia/Shanghai;注意这里是基础配置没有加任何流式或游标参数。后面两种方式会在此基础上改。3.2 普通查询代码最简单风险也最直接普通查询的代码是最常见的写法直接看代码public void queryNormal(Connection conn) throws SQLException { long start System.currentTimeMillis(); String sql SELECT id, user_id, amount, created_at FROM t_order; try (PreparedStatement pstmt conn.prepareStatement(sql); ResultSet rs pstmt.executeQuery()) { System.out.println(executeQuery 耗时(ms): (System.currentTimeMillis() - start)); int count 0; while (rs.next()) { // 模拟逐行处理 count; } System.out.println(遍历完成: count 行, 总耗时(ms): (System.currentTimeMillis() - start)); } }这段代码本质上是“查询”和“处理”解耦的但对于100万行数据executeQuery()这一步就会把数据全部加载到内存然后才会进入while循环。我用-Xmx512m堆内存跑这个程序结果直接OOM。就算把堆内存调到2GBJVM也会频繁Full GC执行效率非常差。所以普通查询不是不能用而是你必须清楚它只适合小结果集。我自己的建议是任何可能超过几万行的查询都不要用默认模式除非你明确加了LIMIT。3.3 流式查询一个参数触发但使用限制必须牢记流式查询的代码改动很小关键就两行public void queryStreaming(Connection conn) throws SQLException { conn.setAutoCommit(false); // 强烈建议避免流式结果集未读完时事务状态混乱 String sql SELECT id, user_id, amount, created_at FROM t_order; try (PreparedStatement pstmt conn.prepareStatement(sql)) { pstmt.setFetchSize(Integer.MIN_VALUE); try (ResultSet rs pstmt.executeQuery()) { System.out.println(executeQuery 耗时(ms): (System.currentTimeMillis() - start)); int count 0; while (rs.next()) { count; // 逐行处理 } System.out.println(遍历完成: count 行, 总耗时(ms): (System.currentTimeMillis() - start)); } } }这里有几个细节你必须注意第一setFetchSize(Integer.MIN_VALUE)必须是精确的这个值这是MySQL驱动判断是否启用流式模式的标志不是随便一个负数。你写成-1都不会触发流式。第二PreparedStatement的ResultSet类型必须是TYPE_FORWARD_ONLY。如果你显式设置了TYPE_SCROLL_INSENSITIVE或TYPE_SCROLL_SENSITIVE驱动会忽略流式模式退化为全量加载。这一点我踩过坑之前为了能随机访问结果集在prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)上研究了好久结果流式一直没生效内存照样爆。第三流式结果集必须在当前连接上“一次性消费完”。如果你在读取过程中发现自己只需要前100行然后直接调用rs.close()这没问题但你要是想在读到一半时去执行另一条SQL哪怕是在同一个Statement对象上再执行一次查询都会触发“Streaming result set still active”的异常。我用堆内存监控工具观察流式查询结果稳定在50MB左右明显比普通查询好太多。但耗时也确实感人读100万行比普通查询慢了将近两倍。原因是驱动在流式模式下每行的网络读取开销比较高这个代价在“内存换时间”的场景里可以接受但在性能敏感的场景里就要慎重。3.4 游标查询参数组合复杂却是大数据量首选游标查询的代码和流式查询长得很像但关键在于连接URL、事务状态、fetchSize三者的配合。先看连接URLString url jdbc:mysql://127.0.0.1:3306/test_db ?useSSLfalsecharacterEncodingutf8serverTimezoneAsia/Shanghai useCursorFetchtrueuseServerPrepStmtstrue;然后代码public void queryWithCursor(Connection conn) throws SQLException { conn.setAutoCommit(false); // 游标查询必须非自动提交 String sql SELECT id, user_id, amount, created_at FROM t_order; try (PreparedStatement pstmt conn.prepareStatement(sql)) { pstmt.setFetchSize(1000); // 每批拉取1000行 long start System.currentTimeMillis(); try (ResultSet rs pstmt.executeQuery()) { System.out.println(executeQuery 耗时(ms): (System.currentTimeMillis() - start)); int count 0; while (rs.next()) { count; // 逐行处理 } System.out.println(遍历完成: count 行, 总耗时(ms): (System.currentTimeMillis() - start)); } } finally { conn.commit(); // 或者 conn.rollback() } }游标查询的关键点useCursorFetchtrue告诉驱动启用服务端游标模式useServerPrepStmtstrue是它的前置条件因为服务端游标依赖服务端预处理语句协议。这两个参数缺一不可。我见过很多人只加了useCursorFetchtruefetchSize设了也白设最后还是全量加载。setFetchSize(1000)的值不能太大也不能太小。太大会增加客户端单次缓冲内存太小会导致网络请求次数过多。一般来说1000到5000是实践中最常用的范围。连接必须setAutoCommit(false)。如果保持自动提交游标查询的行为不可控甚至可能报错。处理完结果集后记得手动commit或rollback释放游标相关的事务资源。实测下来游标查询100万行的耗时介于普通查询和流式查询之间堆内存占用大约等于fetchSize * 单行大小再乘以一些缓冲容量通常稳定在几十MB。这个平衡性让它成为我处理大数据量查询的默认方案。4. 参数与环境的细节决策fetchSize、连接池和其他框架代码跑通只是第一步真正生产环境还有各种框架和连接池的干扰。这一节聊聊我在实际项目中总结出的几个决策点和环境适配经验。4.1 fetchSize到底设多少合适很多人把fetchSize当成一个“随便填个1000”的参数其实它直接影响游标查询的网络交互次数和内存占用。假设结果集有100万行fetchSize设为1000时需要1000次网络请求设为10000时只需要100次网络请求但单次缓冲的内存会高一个量级。我在实践中通常遵守以下经验单行数据很小比如只有ID、状态列fetchSize可以设大一些比如5000到10000减少网络往返。单行数据较大比如包含大文本字段fetchSize尽量不要超过2000否则单批缓冲内容太多客户端内存压力上升。如果网络延迟偏高适当增大fetchSize可以明显提升总吞吐量但要先以客户端堆内存为上限做评估。有一个小技巧你可以用单行字节数 * fetchSize * 1.5粗略估算每批数据在客户端的缓冲占用。假如单行1KBfetchSize5000那么单批约5MB再算上其他缓冲完全可控。如果单行10KB同样的fetchSize就是50MB那就要慎重了。4.2 连接池默认配置会让游标模式静默失效现在几乎没有项目裸用JDBC基本都是HikariCP、Druid或者C3P0。连接池对游标查询最大的影响是autoCommit状态。HikariCP的默认配置是autoCommittrue也就是说你从连接池拿到的连接默认是自动提交的。如果直接用这个连接去执行游标查询useCursorFetchtrue可能不会生效或者行为不符合预期。正确做法是每次拿到连接后手动设置Connection conn dataSource.getConnection(); conn.setAutoCommit(false); try { // 执行游标查询... } finally { conn.commit(); conn.setAutoCommit(true); // 恢复默认归还连接 conn.close(); }归还连接前把autoCommit恢复成true是为了避免连接池里的连接状态被污染。不然下一个拿到这条连接的代码块可能莫名其妙就在非自动提交模式里运行了。另外一个连接池有关的问题是资源占用。流式或游标查询本质上都是“长连接上的长事务”一条连接在结果集遍历完成前不会释放。如果你同时启动10个这样的查询连接池就可能被打满其他业务查询只能排队等待。我的建议是大数据量的流式/游标任务单独配置一个连接池并且用信号量把并发度限制在预估范围之内。4.3 MyBatis和JPA里的实践方式大多数项目会用ORM框架这里简单提一下我在MyBatis中处理大结果集的方法。MyBatis 3.4.0之后提供了CursorT接口底层就是利用JDBC的流式或游标查询能力。使用方式很简单Mapper public interface OrderMapper { Select(SELECT id, user_id, amount, created_at FROM t_order) CursorOrder scanAll(); }然后在Service里这样用try (SqlSession sqlSession sqlSessionFactory.openSession(); CursorOrder cursor orderMapper.scanAll()) { IteratorOrder iterator cursor.iterator(); while (iterator.hasNext()) { Order order iterator.next(); // 逐行处理 } }关键是这个SqlSession必须显式打开并关闭不能让Spring的SqlSessionTemplate帮你管理否则连接生命周期不受控游标结果集可能没读几行连接就被归还了容易引发“Connection is not associated with a managed connection”之类的报错。Spring环境下可以用TransactionTemplateSqlSessionSessionFactory配合处理但最简单的方式就是单独开一个SqlSession专门跑扫描任务。JPA用户会更关心Hibernate的StreamT。Hibernate 5.2之后的query.stream()底层是一个只向前的游标但Hibernate对MySQL的游标适配有个老问题它可能默认使用大fetchSize或直接忽略游标效果不够稳定。我个人的建议是如果你用的是Hibernate/JPA不要依赖stream()完成超大结果集导出任务直接用原生JDBC或者MyBatis的Cursor更靠谱。5. 常见问题与排查技巧实录这一节整理我在生产环境真正遇到过的几个疑难杂症每一条都是踩过坑之后的经验总结。5.1 “Streaming result set still active”的真相与应对这是流式查询最具代表性的报错报错信息长这样java.sql.SQLException: Streaming result set com.mysql.cj.protocol.a.result.ResultsetRowsStreamingxxxx is still active. No statements were executed when the streaming had finished.发生原因几乎只有一个流式结果集还没被读完你又在这个连接上执行了其他SQL。MySQL协议不支持“并发”的两个活动结果集驱动通过这个异常主动保护协议不乱掉。解决办法有三种把流式结果集完整读完再执行后续SQL这最符合直觉但会导致处理逻辑必须全部堆在同一个方法里代码不优雅。不需要全部数据时主动调用rs.close()关闭流式结果集然后再执行其他SQL。注意只能是关闭结果集而不是关闭连接。换用游标查询。游标查询在读取若干批后如果提前关闭结果集驱动会发送一条清理命令给服务端释放游标后续SQL可以正常执行容错性远好于流式模式。5.2 设置了fetchSize却不生效可能是驱动版本和ResultSet类型的锅我接到过不少类似咨询“为什么我明明设置了setFetchSize(1000)内存还是爆了”排查这一类问题按下面几步走基本能定位确认连接URL是否包含useCursorFetchtrueuseServerPrepStmtstrue。少一个都不行。确认PreparedStatement的ResultSet类型是不是TYPE_FORWARD_ONLY。很多人在prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)这种写法下fetchSize会被驱动忽略因为可滚动的ResultSet无法与服务端游标配合。确认MySQL驱动版本。老版本mysql-connector-java5.x和8.x的行为有一定差异8.x对协议的支持更完整。如果项目里还在用5.1.x建议至少升级到8.0.x。打开MySQL的general_log确认是否真的走了游标。如果看到SQL执行后驱动陆续发送了多次COM_STMT_FETCH命令说明游标模式生效如果只有一次查询命令然后大量数据一次性到达那fetchSize肯定没生效。SET GLOBAL general_log ON; SET GLOBAL log_output TABLE;然后查询mysql.general_log表观察记录。这个排查手段非常直观比盲猜参数快得多。5.3 游标查询卡在第一个next()大概率是服务端在排序前几天一个同事问我游标查询跑一条带ORDER BY的SQLexecuteQuery()秒回但第一次next()卡了20多秒才出来。他怀疑是驱动或网络问题我看了一眼执行计划果不其然有个Using filesort。游标查询并不会改变SQL的执行计划。服务端必须先完成排序、聚合等操作才能维护一个有序的游标。所以第一个next()等待的时间就是服务端构建临时结果集的时间。如果你的查询带有ORDER BY且排序字段没有合适的索引服务端会先在内存或磁盘上排好序这期间客户端只能干等。这种情况的优化方向很明确让排序走索引或者避免大数据量排序。比如ORDER BY id配合主键索引就是天然有序如果在其他字段上排就要评估是否真的需要全量排序。如果必须要排序那也不能指望靠游标或流式解决它们只能解决“客户端内存”解决不了“服务端排序”的开销。5.4 连接池连接被长时间占用导致业务阻塞游标和流式查询都是长事务操作意味着它们会霸占一个连接很长时间。如果应用的数据源只有20个连接你同时启动5个全表扫描任务这5个连接会被长期占用剩下15个连接还要服务正常的业务请求。一旦业务请求并发上来连接池就会进入等待状态整个应用看起来像“卡死了”。我的处理经验是给批量任务单独建一个数据源和业务数据源隔离。控制同时运行的扫描任务数量比如用Semaphore限制最大并发为2或3。为这些连接设置合理的socketTimeout和connectTimeout避免由于网络异常导致连接无限期挂着。在代码的finally块里确保ResultSet、Statement、Connection按逆序关闭。游标模式下如果结果集没有关闭连接归还后底层游标状态可能是残留的下次复用会莫名其妙出错。5.5 问题排查速查表我把上面这些问题整理成一张速查表方便你排查时对照现象可能原因排查和解决方向大结果集导致OOM使用了普通查询全量加载到内存改为游标查询或加LIMIT限制流式查询报still active结果集未读完就执行其他SQL读完再执行或提前关闭结果集useCursorFetch不生效缺少useServerPrepStmts参数URL同时带上两个参数fetchSize无效ResultSet类型不是FORWARD_ONLY显式使用无参的prepareStatement游标查询第一个next()很慢SQL含排序、聚合等操作优化索引检查执行计划Using filesort连接池被占满并发启动过多长事务查询单独数据源并发限流流式查询整体耗时很长网络往返次数过多改用游标查询或增大fetchSize结尾一句实话和一个实用小技巧最后说点我的真实体会。很多人一听到大数据量查询第一反应就是“用流式查询”这其实是个误区。流式查询虽然把客户端内存压下来了但网络开销很大而且使用限制苛刻。相比之下游标查询在绝大多数生产场景里才是更稳的选择前提是你把连接URL、事务状态、fetchSize都配置正确。如果你实在不确定当前连接到底走的哪种模式我教你一个最简单直接的验证方法在MySQL服务端开启general_log然后执行查询观察日志里SQL之后是否出现了多次Fetch命令。有Fetch命令基本就是游标模式一条SQL执行完直接开启数据传输、没有任何Fetch那就是普通查询或者流式查询。这招比看代码、猜参数都靠谱排查线上问题特别实用。