PreparedStatement 大数据查询

PreparedStatement 大数据查询慢解决办法

前言
项目有个需求需要查询从A库取1000w条数据搬到B库，这边需要支持不同的数据库类型，并且sql是根据客户动态配置出来的，所以就选择了PreparedStatement，后来不懈努力传输1000w条数据只需要7分钟左右。
记录一下经验

1.开始选择的方案是使用分页查询，每页查1w条，使用while循环查询，查到没有数据为止

String sql = "查询1000w条数据 limit ?,?";
PreparedStatement sourcePs = sourceConn.prepareStatement(sql);
boolean wl = Boolean.TRUE;
while (wl) {
	// 分页
	sourcePs.setInt(Constants.INT_2, pageIndex);
    sourcePs.setInt(Constants.INT_1, pageSize);
    // 每次取数加1页
    pageIndex += pageSize; 
    // 获取源端数据结果集
    ResultSet sourceRs = sourcePs.executeQuery();
    if (sourceRs.next()) {
		// 数据处理
	} else {
		wl = Boolean.FALSE;
	}
}

结果：1000w数据处理完需要一个小时左右，不能满足需求规定时间。然后打印执行日志，发现前面处理1000条数据只要几百毫秒，后来到了七八秒之多，开始怀疑是不是循环查询导致数据库变慢了，然后优化sql，建索引，但效果都微乎其微

2.后来就考虑到使用多线程的方式去处理数据，由于这次修改代码没保存找不到了，就不重新写了，大概列一下思路

//============1.创建线程池
String sql = "查询1000w条数据 limit ?,?";
String countSql = "查询1000w条数据总数";
PreparedStatement sourcePs = sourceConn.prepareStatement(sql);
boolean wl = Boolean.TRUE;
int count = 总数 / 10000
for (int i = 0; i < count; i++) {
	// 分页
	sourcePs.setInt(Constants.INT_2, pageIndex);
    sourcePs.setInt(Constants.INT_1, pageSize);
    // 每次取数加1页
    pageIndex += pageSize; 
    //============2.开启线程
    // 获取源端数据结果集
    ResultSet sourceRs = sourcePs.executeQuery();
    if (sourceRs.next()) {
		// 数据处理
	} else {
		wl = Boolean.FALSE;
	}
	//============3.单个子线程结束
}

结果：导致一次性创建了1000个线程，每个线程都在抢资源对数据库操作，最后执行完成花费了三个小时之多，并且某些子线程挂掉了导致数据丢失

3.最终方案后来想过多次方法之后还是没能解决问题，开始往数据库连接方面找解决方案，然后就找到了这篇文章→葵花宝典←

String sql = "查询1000w条数据";
PreparedStatement sourcePs = sourceConn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
sourcePs.setFetchSize(Constants.INT_10000);
sourcePs.setFetchDirection(ResultSet.FETCH_FORWARD);

解释一下

1. ResultSet.TYPE_SCROLL_INSENSITIVE 结果集的游标可以上下移动，当数据库变化时，当前结果集不变。
2. ResultSet.CONCUR_READ_ONLY 不能用结果集更新数据库中的表。
3. sourcePs.setFetchSize(Constants.INT_10000); 是使用流的方式接受数据，每次从服务器取出设置的大小的数据，
4. sourcePs.setFetchDirection(ResultSet.FETCH_FORWARD); 是按游标从头到尾取数

这些设置在PreparedStatement源码里面注释写得很清楚了，可以自行了解下。

最终使用流这种方式1000w条数据只需要七分钟左右，完美解决！