正确使用MySQL JDBC setFetchSize()方法解决JDBC处理大结果集 java.lang.OutOfMemoryError: Java heap space

ResultSet 过亿 数据

正确使用MySQL JDBC setFetchSize()方法解决JDBC处理大结果集 java.lang.OutOfMemoryError: Java heap space

https://blog.csdn.net/seven_3306/article/details/9303879

昨天在项目中需要对日志的查询结果进行导出功能。

日志导出功能的实现是这样的，输入查询条件，然后对查询结果进行导出。由于日志数据量比较大。多的时候，有上亿条记录。

之前的解决方案都是多次查询，然后使用limit 限制每次查询的条数。然后导出。这样的结果是效率比较低效。

那么能不能一次查询就把所有结果倒出来了？于是我就使用一次查询，不使用limit分页。结果出现 java.lang.OutOfMemoryError: Java heap space问题。

看来是DB服务器端将一次将查询到的结果集全部发送到Java端保存在内存中。由于结果集比较大，所以出现OOM问题。

首先我想到的是游标功能。那么是不是可以使用游标，一次从服务器端慢慢的取呢？上网查询了一下，大家都说MySQL不支持游标功能等等。

后来就去看JDBC代码。找到了setFetchSize()方法，结果设置以后，却不能生效，还是出现OOM问题。

我的设置如下

[java]  view plain  copy

1. ps=conn.con.prepareStatement("select * from bigTable");  
2.  ps.setFetchSize(1000);  

后来在MySQL看到了这样的方法：

[java]  view plain  copy

1. ps = (PreparedStatement) con.prepareStatement("select * from bigTable",  
2.                 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);  
3.         ps.setFetchSize(Integer.MIN_VALUE);  
4.         ps.setFetchDirection(ResultSet.FETCH_REVERSE);  

对此解释是：MySQL JDBC默认客户端数据接收方式为如下：

默认为从服务器一次取出所有数据放在客户端内存中，fetch size参数不起作用，当一条SQL返回数据量较大时可能会出现JVM OOM。

要一条SQL从服务器读取大量数据，不发生JVM OOM，可以采用以下方法之一：

1、当statement设置以下属性时，采用的是流数据接收方式，每次只从服务器接收部份数据，直到所有数据处理完毕，不会发生JVM OOM。

          setResultSetType(ResultSet.TYPE_FORWARD_ONLY);

          setFetchSize(Integer.MIN_VALUE); 

2、调用statement的enableStreamingResults方法，实际上enableStreamingResults方法内部封装的就是第1种方式。

3、设置连接属性useCursorFetch=true (5.0版驱动开始支持)，statement以TYPE_FORWARD_ONLY打开，再设置fetch size参数，表示采用服务器端游标，每次从服务器取fetch_size条数据。

设置以后，果然可以解决我的问题。

附上代码：

[java]  view plain  copy

1. package com.seven.dbTools.DBTools;  
2.   
3. import java.sql.Connection;  
4. import java.sql.DriverManager;  
5. import java.sql.PreparedStatement;  
6. import java.sql.ResultSet;  
7. import java.sql.SQLException;  
8. import java.sql.Statement;  
9. import java.util.ArrayList;  
10.   
11. public class JdbcHandleMySQLBigResultSet {  
12.   
13.     public static long importData(String sql){  
14.         String url = "jdbc:mysql://ipaddress:3306/test?user=username&password=password";  
15.         try {  
16.             Class.forName("com.mysql.jdbc.Driver");  
17.         } catch (ClassNotFoundException e1) {  
18.             e1.printStackTrace();  
19.         }  
20.         long allStart = System.currentTimeMillis();  
21.         long count =0;  
22.   
23.         Connection con = null;  
24.         PreparedStatement ps = null;  
25.         Statement st = null;  
26.         ResultSet rs = null;  
27.         try {  
28.             con = DriverManager.getConnection(url);  
29.               
30.             ps = (PreparedStatement) con.prepareStatement(sql,ResultSet.TYPE_FORWARD_ONLY,  
31.                       ResultSet.CONCUR_READ_ONLY);  
32.                         
33.             ps.setFetchSize(Integer.MIN_VALUE);  
34.               
35.             ps.setFetchDirection(ResultSet.FETCH_REVERSE);  
36.   
37.             rs = ps.executeQuery();  
38.   
39.   
40.             while (rs.next()) {  
41.                   
42.                 //此处处理业务逻辑  
43.                 count++;  
44.                 if(count%600000==0){  
45.                     System.out.println(" 写入到第  "+(count/600000)+" 个文件中！");  
46.                     long end = System.currentTimeMillis();  
47.                 }  
48.                   
49.             }  
50.             System.out.println("取回数据量为  "+count+" 行！");  
51.         } catch (SQLException e) {  
52.             e.printStackTrace();  
53.         } finally {  
54.             try {  
55.                 if(rs!=null){  
56.                     rs.close();  
57.                 }  
58.             } catch (SQLException e) {  
59.                 e.printStackTrace();  
60.             }  
61.             try {  
62.                 if(ps!=null){  
63.                     ps.close();  
64.                 }  
65.             } catch (SQLException e) {  
66.                 e.printStackTrace();  
67.             }  
68.             try {  
69.                 if(con!=null){  
70.                     con.close();  
71.                 }  
72.             } catch (SQLException e) {  
73.                 e.printStackTrace();  
74.             }  
75.         }  
76.         return count;  
77.   
78.     }  
79.   
80.     public static void main(String[] args) throws InterruptedException {  
81.   
82.         String sql = "select * from test.bigTable ";  
83.         importData(sql);  
84.   
85.     }  
86.   
87. }  

最近对JDBC有了进一步的了解。关于JDBC，推荐我的另一篇文章，用于解决不写文件，从Java IO流中直接导入数据到MySQL：

Java不写文件，LOAD DATA LOCAL INFILE大批量导入数据到MySQL的实现 http://blog.csdn.net/chenyechao/article/details/9237495

推荐另外两篇来自阿里巴巴叶正盛的文章我转载的：

关于oracle与mysql官方jdbc的一些区别

http://blog.csdn.net/chenyechao/article/details/9303979   

这篇文章是我解决问题以后才看到的，上面已经说明了MySQL JDBC的setFetchSize的使用。

另外一篇：面向程序员的数据库访问性能优化法则 http://blog.csdn.net/yzsind/article/details/6059209

oracle jdbc 分批抓取查询FetchSize(100)

https://blog.csdn.net/gongxinju/article/details/62217839

JDBC用ResultSet访问大量数据时会遇到的问题

https://www.cnblogs.com/qingxinblog/p/4240111.html