本文转载自微信公众号“源码兴趣圈”，作者马云。转载本文请联系源码兴趣圈公众号。由于ORM框架的成熟应用，很多小伙伴对于JDBC的概念有些薄弱。ORM框架的底层其实是JDBC操作的DBJDBC（JavaDataBaseConnectivity），是Java数据库连接。公司提供了一套标准化的访问数据库的API，并提供了相应的连接数据库的协议标准，然后各个厂商按照规范提供了一套访问自己数据库的API接口。JDBC大数据操作通用查询流式查询游标查询JDBCRowDataJDBC通信原理流式游标内存分析单次调用内存使用并发调用内存使用MyBatis如何使用流式查询结语MySqlJDBC大数据操作数据量操作是正题，相关的知识点是通过开发过程中的需求引入的。数据迁移数据导出数据批量处理数据笔者认为一般来说，在JavaWeb程序中，可以称得上是大数据量，少则几十万，多则上千万。等等，再高，Java（WEB应用）处理也不是很合适。比如现在业务系统需要从MySQL数据库中读取500w条数据行进行处理。定时查询一次性读取500w条数据到JVM应该怎么做？在内存中，或者分页读取流式查询，建立长连接，使用服务器端游标，每次读取一个游标查询加载到JVM内存中，使用fetchSize参数控制一次读取多少条数据。默认情况下，完整的检索结果集会将其存储在内存中。在大多数情况下，这是最高效的操作方式，而且由于MySQL网络协议的设计，更容易实现。假设单表有500w的数据量，没有人会一次性加载到内存中，一般使用分页@SneakyThrows@OverridepublicvoidpageQuery(){@CleanupConnectionconn=dataSource.getConnection();@CleanupStatementsstmt=conn.createStatement();longstart=System.currentTimeMillis();longoffset=0;intsize=100;while(true){Stringsql=String.format("SELECTCOLUMN_A,COLUMN_B,COLUMN_CFROMYOU_TABLELIMIT%s,%s",offset,size);@CleanupResultSetrs=stmt。executeQuery(sql);longcount=loopResultSet(rs);if(count==0)break;offset+=size;}log.info("🚀🚀🚀分页查询耗时::{}",System.currentTimeMillis()-start);}上面的方法比较简单，但是没有考虑LIMIT深度分页优化这种情况下线上的数据库服务器冷了，还是等几天再取数据吧流式查询如果你使用的ResultSet有大量的数据行，并且无法在JVM中为其分配所需的内存堆空间，你可以告诉driver从结果流中返回一行。流式查询有一个警告：在连接上发出任何其他查询之前，必须读取（或关闭）结果集中的所有行，否则查询将抛出异常，有必要保持对所引用表的并发访问生成结果集的语句，因为它的查询会独占连接，所以必须尽快处理nt(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);stmt.setFetchSize(Integer.MIN_VALUE);longstart=System.currentTimeMillis();@CleanupResultSetrs=stmt.executeQuery("SELECTCOLUMN_A,COLUMN_B,COLUMN_CFROMYOUSet;Res_TAB)(循环).info("🚀🚀🚀流式查询耗时::{}",(System.currentTimeMillis()-start)/1000);}流式查询数据库表数据量500w单次耗时每次调用：≈6s游标查询SpringBoot2.x版本默认连接池为HikariPool，连接对象为HikariProxyConnection，所以下面设置游标的方法不可行((JDBC4Connection)conn).setUseCursorFetch(true);需要在数据库中拼接连接信息&useCursorFetch=true其次，设置Statement每次读取的数据量，比如一次读取1000条@SneakyThrowspublicvoidcursorQuery(){@CleanupConnectionconn=dataSource.getConnection();@CleanupStatementsstmt=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);stmt.setFetchSize(1000);longstart=System.currentTimeMillis();@CleanupResultSetrs=stmt.executeQuery("SELECTCOLUMN_A,COLUMN_B,COLUMN_CFROMYOU_TABLE");loopResultSet(rs);log.info("🚀🚀🚀游标查询耗时::{}",(System.currentTimeMillis()-start)/1000);}游标查询库表数据量500w单次调用耗时：≈18sJDBCRowData使用上面的方法loopResultSet,并且该方法只进行了While循环，正则、流式、游标查询的核心点在于next方法@SneakyThrowsprivateLongloopResultSet(ResultSetrs){while(rs.next()){//业务操作}returnxx;}ResultSet.next()的逻辑是实现类ResultSetImpl获取数据of每次从RowData的下一行。RowData是一个接口，实现关系图如下。默认情况下，ResultSet将使用RowDataStatic实例。生成RowDataStatic对象时，会将ResultSet中的所有记录读入内存，然后通过next()从内存中读取RowDataCursor调用，进行批处理，然后进行内部缓存，过程如下:首先会检查其内部缓冲区中是否还有未返回的数据，如果有则返回下一行。如果全部读完，则触发新的请求到MySQLServer读取fetchSize的结果，并将返回的结果缓冲到内部缓冲区，然后返回第一行数据。使用流处理时，ResultSet使用RowDataDynamic对象，而这个objectnext()每次调用读取单行数据时都会发起IO。总结起来就是，默认的RowDataStatic将所有的数据读入到客户端内存中，也就是我们的JVM；RowDataCursor一次读取fetchSize行，消费完成后发起请求调用；RowDataDynamic每次IO调用都会读取一条数据与MySQL服务器的交互是通过Socket完成的。对应网络编程，MySQL可以看做一个SocketServer，所以一个完整的请求环节应该是：JDBC客户端->客户端Socket->MySQL->获取数据并返回->MySQLKernelSocketBuffer->网络->ClientSocketBuffer->JDBCClient普通查询当查询大量数据时，其所在的JVM可能是冷的，原因如下：MySQLServer会将检索到的SQL结果集通过输出写入内核对应的SocketBuffer溪流。内核缓冲区通过JDBC发起的TCP链接返回数据。这时数据会先进入JDBC客户端所在的内核缓冲区。JDBC发起SQL操作后，程序会Block对输入流的读操作。当buffer中有数据时，程序会被唤醒，将buffer中的数据读取到JVM内存中。MySQLServer会继续发送数据，JDBC会继续读取缓存的数据。缓冲区中的数据存储在Java内存中。此时虽然数据已经到达JDBC程序本地，但是JDBC还没有响应execute方法的调用，因为需要等到读取到相应的数据后才返回。缺点很明显。如果查询的数据量过大，会不断的经历GC，然后游标查询就会出现内存溢出。从上面我们知道游标可以解决普通大数据量查询的内存溢出问题，但是大家有没有想过这样的问题，MySQL并不知道客户端程序消费完成后，应该如何处理呢？另一个连接导致对表的DML写操作？其实我们在使用游标查询的时候，MySQL需要创建一个临时空间来存放需要读取的数据，所以不会和DML写操作冲突，但是游标查询会造成如下现象：IOPS飙升，因为需要返回的数据需要写入暂存空间，有大量的IO读写，这个过程可能会引起其他业务写输入抖动的磁盘空间飙升，因为数据写入临时空间的是原始表之外。如果表数据太大，极端情况下可能会导致数据库磁盘满。此时网络输出没有变化，写入了临时空间客户端发起ResultSet#close操作时数据会被MySQL回收，客户端JDBC发起的SQL查询可能需要很长时间才能完成等待SQL响应。这个时期是服务器的数据准备阶段。但是普通查询的等待时间和游标查询的等待时间原则上是不一致的。前者一致读取网络缓冲区中的数据，不响应业务层面；后者是MySQL正在准备临时数据空间，还没有响应JDBC数据准备。完成后，在数据传输阶段，网络响应开始飙升，IOPS由“读写”变为“读取”。游标查询的通信效率比较低，因为需要客户端发起请求到服务器端的请求，在数据库准备初期IOPS会非常高，占用大量的磁盘空间和性能。流式查询当客户端与MySQLServer建立连接并进行查询交互时，MySQLServer会将SQL结果集通过输出流Output，即向本地内核对应的SocketBuffer写入数据，然后返回内核中的数据通过TCP链接到JDBC对应的服务器内核缓冲区。JDBC通过输入流读取方法Data读取内核缓冲区，由于启用了流式读取，每次业务程序只接收到一条数据，MySQL服务器会不断向JDBC代表的客户端内核发送数据，直到客户端请求套接字缓冲区已满。这时候，MySQL服务器就会阻塞。对于JDBC客户端，每次都是从机器的内核缓冲区中读取数据，所以性能会更快。一般情况下不用担心机器内核消费数据不足（除非MySQL服务器传给数据，客户端不做任何业务逻辑，直接放弃数据，clientsideconsumptionwillbeaheadoftheserverside)streaming方式似乎比cursor方式要好，但是事情往往没有表面上那么简单。与游标查询相比，流式查询对数据库的影响更久。另外，流式查询依赖于网络，导致网络拥塞的可能性更大。流式游标内存分析表数据量：500w内存查看工具：JDK自带Jvisualvm设置JVM参数：-Xmx512m-Xms512m单次调用内存使用流式查询内存性能报告如图1数据仅供参考Cursor查询内存性能报告如图图2中的数据仅供参考根据内存使用情况，游标查询和流并发查询可以很好的防止OOM并发调用。内存使用并发调用：Jmete10线程并发调用流式查询内存性能报告如图3所示，数据仅供参考。并发调用对于内存使用也没有问题。流查询的并发调用时间平均消耗有叠加增加：≈55s游标查询的内存性能报告如图4所示，数据仅供参考并发调用时间的平均消耗游标查询：≈83s由于设备限制，有些情况只会出现在极端情况下，所以没有做生产和测试的多环境验证。如果你有兴趣，可以测试一下MyBatis是如何使用流式查询的。以上介绍了如何使用JDBC原生API进行查询。ORM框架Mybatis也封装了ResultHandler用于流式查询。接口只包含handleResult方法，可以获取到已转换后的Java实体类@Slf4j@ServicepublicclassMyBatisStreamService{@ResourceprivateMyBatisStreamMappermyBatisStreamMapper;publicvoidmybatStreamisStreamQuery(){longstartillimybatStreamisStreamQuery(){longstartillimybatmybatStreamMisper(newResultHandler
