java对包含数十亿条记录的表执行查询
我想在不使用limit子句的情况下从数据库中获取一些记录(可以是50100或用户配置的其他记录),因为我们的应用程序可能在多个数据库上工作,比如mysql、oracle、mssql、db2
我做了如下的解决方案
package com.test;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.util.Date;
public class BatchRetrieveTest extends Object {
private static final int FETCH_SIZE = 10;
public BatchRetrieveTest() {
}
public static void main(String[] args) {
BatchRetrieveTest batchRetrieveTest = new BatchRetrieveTest();
batchRetrieveTest.test();
}
void test() {
Connection conn = null;
Statement stmt2 = null;
Date start = null;
Date end = null;
int i = 0;
try {
conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test",
"root", "root");
stmt2 = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
conn.setAutoCommit(false);
stmt2.setFetchSize(FETCH_SIZE);
stmt2.setPoolable(true);
start = new Date();
System.out.println(new Date() + "second execute start"
+ new Date().getTime());
ResultSet rs2 = stmt2
.executeQuery("SELECT * FROM sample_final_attendance limit 1000");
end = new Date();
System.out.println(new Date() + "*************second execute end"
+ (end.getTime() - start.getTime()));
rs2.absolute(200000);
i = 0;
while (rs2.next()) {
if (i++ > 100) {
break;
}
}
rs2.close();
stmt2.close();
end = new Date();
System.out.println(new Date() + "second read end"
+ (end.getTime() - start.getTime()));
conn.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
stmt2.close();
conn.close();
} catch (Exception e) {
}
}
}
}
这里的样本\最终\出席人数表包含15列和320万条记录 执行该程序时需要2GB内存和47秒的执行时间
这里我想知道,如果某个表有数十亿条记录,那么它将无法执行
我还按照建议使用了setFetchSize
,但问题是一样的
请提出一些解决方案
提前谢谢
共 (0) 个答案