java通过ResultSet一行一行查询mysql大数据量记录,避免jvm内存溢出

  • A+
所属分类:java

近期割接,从1700万老表将数据割接到多个新表中。我使用了结果集方式来查询,因为我想只查一次老库,通过rs.next() 来遍历结果集。这应该是最高效的方法。

ResultSet rs = PreparedStatement.executeQuery();

使用的时候,发现jvm总是内存溢出。查阅了mysql官方文档,发现里面另有蹊跷。

mysql原文地址:

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-implementation-notes.html

By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate and, due to the design of the MySQL network protocol, is easier to implement. If you are working with ResultSets that have a large number of rows or large values and cannot allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.

默认情况下,resultSets 获取的是所有结果,并保存在内存中。大多数情况,由于mysql的网络协议设计的原因,这是最高效的方法,也是最简单的实现方式。如果数据量比较大,jvm不够用,需要手动来设置,每次只返回一条记录。

To enable this functionality, create a Statement instance in the following manner:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
              java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

The combination of a forward-only, read-only result set, with a fetch size of Integer.MIN_VALUE serves as a signal to the driver to stream result sets row-by-row. After this, any result sets created with the statement will be retrieved row-by-row.

开启这个功能,xiang像下面这样来创建Statement。汗,为什么fetchSize 要设置成Integer.MIN_VALUE ? 完全没解释为啥,那就照抄吧,

只要记住,这些组合参数给mysql驱动一个信号,那就是一条一条记录的传输。这样,这个Statement 所有的结果集都是一条一条获取的。

另外:spring集成的jdbc可以这样获取connection

Connection conn = jdbcTemplate1.getDataSource().getConnection();

测试一下,很好使。以后查资料,还是尽量看官方的。少走弯路


  • 我的微信
  • 这是我的微信扫一扫
  • weinxin
  • 我的微信公众号
  • 我的微信公众号扫一扫
  • weinxin

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: