多线程查询MySQL大数据量单表导致OOM及线程卡死

1 需求

对数据进行迁移，需要将MySQL中的一张大表数据（亿级）读出并插入另一个数据库。

2 问题

对数据分组后，然后开启对应个数的线程，每个线程处理一个分组数据的任务（每个分组后的数据量从几十万至千万级），最初思路是从原库取出1000条数据后，再进行入库，设置了PreparedStatement 的 fetchSize为1000， 但是很快就报“ java.lang.OutOfMemoryError: GC overhead limit exceeded” 内存溢出错误。

Exception in thread "pool-4-thread-7" java.lang.OutOfMemoryError: GC overhead limit exceeded
	at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2267)
	at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:2044)
	at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:3549)
	at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:489)
	at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3240)
	at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2411)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2834)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2840)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2212)
	at com.xxx.test.FetchData2.getCellInfoData(FetchCellInfoData2.java:104)
	at com.xxx.test.FetchData2.access$000(FetchCellInfoData2.java:40)
	at com.xxx.test.FetchData2$1.run(FetchCellInfoData2.java:245)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:745)

后面参照网上的方法在数据库连接url上设置游标读取属性（useCursorFetch=true），解决了 OOM 的问题，但是线程准备阶段还是比较耗时，对于数据量为400万以下的任务可以正常跑，但是执行到400万数据量以上的任务时，线程直接卡死。

通过jstack查看进程，发现所有的线程都处于 RUNNABLE 状态：

"pool-4-thread-26" #48 prio=5 os_prio=0 tid=0x0000000013b76800 nid=0x54d8 runnable [0x000000001a62d000]
   java.lang.Thread.State: RUNNABLE
   ...
"pool-4-thread-25" #47 prio=5 os_prio=0 tid=0x0000000013b75800 nid=0x339c runnable [0x000000001a42e000]
   java.lang.Thread.State: RUNNABLE
   ...
"pool-4-thread-24" #46 prio=5 os_prio=0 tid=0x000000001484a800 nid=0x43c4 runnable [0x000000001a2cd000]
   java.lang.Thread.State: RUNNABLE
   ...
"pool-4-thread-23" #45 prio=5 os_prio=0 tid=0x0000000014849800 nid=0x4318 runnable [0x000000001a17e000]
   java.lang.Thread.State: RUNNABLE
   ...

3 方法

之后从《Mysql遍历大表》这篇文章了解到以数据流方式来处理大表数据：

“mysql jdbc默认把select的所有结果全部取回，放到内存中，如果遍历很大的表，则可能把内存撑爆。
一种办法是：用limit，offset，但这样你会发现取数据的越来越慢，原因是设置了offset，mysql需要将读取位置移动到offset的位置，随着offset增大，取数据也越来越慢；
另外一种办法是：用数据流的方式取数据”[1]。

关键的步骤就是需要设置 statement 的 fetchSize 参数为 Integer.MIN_VALUE。

PreparedStatement ps = null;
ps = conn.prepareStatement(String.valueOf(sql), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
// 关键是 ps.setFetchSize(Integer.MIN_VALUE);
ps.setFetchSize(Integer.MIN_VALUE);
ps.setFetchDirection(ResultSet.FETCH_FORWARD);

问题解决了，而且速度可以达到1万多条/s。
但很疑惑为什么设置了 Integer.MIN_VALUE 就开启了数据流方式。

后面查看了jdbc 的MySQL 实现源码，明白了其中的原委。
StatementImpl.class (mysql-connector-java 5.1.38)

下面这个版本的更好理解

注释说明只有ResultType同时设置了 TYPE_FORWARD_ONLY, 数据集并发 CONCUR_READ_ONLY 及fetch size 设置为 Integer.MIN_VALUE 时才启动流结果集处理。

如果前面两个参数不设置，MySQL是默认设置这两个参数的，MySQL的 statementImpl 类实现了prepareStatement（）方法的3个重构方法，源码如下：

public PreparedStatement prepareStatement(String sql) throws SQLException {
        return this.prepareStatement(sql, 1003, 1007);
    }

public PreparedStatement prepareStatement(String sql, int autoGenKeyIndex) throws SQLException {
    PreparedStatement pStmt = this.prepareStatement(sql);
    ((com.mysql.jdbc.PreparedStatement)pStmt).setRetrieveGeneratedKeys(autoGenKeyIndex == 1);
    return pStmt;
}

public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
    synchronized(this.getConnectionMutex()) {
    ...
    }
}     

---

参考：
1 Mysql遍历大表
2 正确使用MySQL JDBC setFetchSize()方法解决JDBC处理大结果集 java.lang.OutOfMemoryError: Java heap space