java从数据库读取百万级数据保存到text文件中,速度慢,怎么解决
代码如下,做一个数据脚本的备份与还原,从数据库查询所有记录,拼接成插入语句,数据库中有600多万条数据,在自的电脑上试,写到txt文件中足足花了半个小时的时间啊。。。。。不知道哪里有问题,麻烦各位指点一下
[code="java"]
/**
* 如果记录大于10000行,则 分页抓取,每次抓取10000条,这里只适应于在mysql
* @param rowCount
* 总记录数
* @param table
* 表名
* @param writer
* 输入流
*/
private static BufferedWriter fetchByPage(int rowCount, String table,
BufferedWriter writer) {
final int fetchSize = 10000;
final int pageSize = rowCount % fetchSize == 0 ? rowCount / fetchSize
: rowCount / fetchSize + 1;
int currentPage = 1;
Connection conn = null;
Statement stsm = null;
ResultSet rs = null;
try {
conn = newConnection();
conn.setAutoCommit(false);
stsm = conn.createStatement();
if (isSqlServer(conn)) {
writer
.write("SET IDENTITY_INSERT [dbo].[" + table
+ "] ON; \n");
} else {
writer.write("SET FOREIGN_KEY_CHECKS=0;\n"); // 默认是mysql
}
while (currentPage <= pageSize) {
String sql = "select * from " + table + " limit "
+ (currentPage - 1) * fetchSize + "," + fetchSize;
rs = stsm.executeQuery(sql);
writeByRow(conn, table, rs, writer);
currentPage++;
}
if (isSqlServer(conn)) {
writer.write("SET IDENTITY_INSERT [dbo].[" + table
+ "] OFF; \n");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (conn != null) {
try {
if (!conn.isClosed())
conn.close();
if (stsm != null)
stsm.close();
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return writer;
}
private static BufferedWriter writeByRow(Connection conn, String tableName,
ResultSet rs, BufferedWriter writer) throws Exception {
while (rs.next()) {
StringBuilder insertSql = new StringBuilder();
insertSql.append("insert into ");
String[] columnNames = getColumnNames(rs);
if (isSqlServer(conn)) {
insertSql.append("" + tableName + ";
for (String columnName : columnNames) {
insertSql.append("[" + columnName + "],");
}
} else {
insertSql.append("" + tableName + "
(");
for (String columnName : columnNames) {
insertSql.append("" + columnName + "
,");
}
}
insertSql.deleteCharAt(insertSql.length() - 1);
insertSql.append(")");
insertSql.append(" values(");
for (int i = 0; i < columnNames.length; i++) {
String columnClassName = rs.getMetaData().getColumnClassName(
i + 1);
if (rs.getObject(i + 1) != null) {
if (columnClassName.equalsIgnoreCase("java.lang.String")) {
String strValue = rs.getString(columnNames[i]);
strValue = strValue.replaceAll("\r", "\\\\r");
strValue = strValue.replaceAll("\n", "\\\\n");
insertSql.append("'" + strValue + "',");
} else {
insertSql.append("'" + rs.getObject(i + 1) + "',");
}
} else {
insertSql.append(rs.getObject(i + 1) + ",");
}
}
int index = insertSql.toString().lastIndexOf(",");
String sqlText = insertSql.toString().substring(0, index) + ")";
writer.write(sqlText + ";\n");
}
return writer;
}
[/code]
这种方式确实本身就有问题,性能不会很高,像MySQL本身就有备份还原数据库的命令:
%MYSQL_HOME%\bin>mysqldump -uroot -proot DB_NAME > "D:/DB_NAME_BAK.sql"
当然这种方式一次性导出,针对大数据量可能确实不行。
[color=red]LZ现在的方式,首当其冲的问题就是:每从数据库查询一条数据就生成SQL并写到文件中。[/color]
[b]程序效率低的原因分析:[/b]
这必然导致效率极低,因为数据库查询时的阻塞会导致整个任务暂停,这时既不生成SQL也不写文件;写文件时的阻塞也会导致整个任务暂停,这时也不再查询数据库了。这样当然效率很低了,因为这两种阻塞不断发生,导致整个任务很大一部分时间啥也没干,CPU闲置。
[b]一、初步优化的方案:[/b]
“数据库查询的操作”和“生成SQL并写文件”分开来,用两个线程去做:
- 一个线程从数据库查询数据并简单处理(比如把每条数据放在一个map中,甚至放在一个数组中,这样速度更快,但是要在程序中控制,要清楚数组中每个元素是什么值),然后将简单处理后的数据放到一个队列中。
- 另一个线程负责从队列中读取简单处理过的数据,生成SQL,然后写入到文件中。
[color=red]注意点:进行这一步优化后,那个队列需要重点实现,应该是线程安全的,因为两个线程都要访问。典型的消费者模式。[/color]
[b]二、进一步的优化方案:[/b]
按照方案一优化后,效率有所提升,但还是达不到所需的性能要求,那么进一步进行优化。方案一中,最大的性能瓶颈可能是访问数据库的那个线程,可以考虑用多个线程并发访问数据库,但是这个线程也不能太多,太多也会慢,要通过具体实践取各平衡点。
[color=red]注意点:这里要注意几个并发访问数据库的线程的分工,比如每个线程各负责某个时间段的数据,得有一个总线程负责调度。[/color]
[b]三、再一步的优化方案:[/b]
按照方案二优化后,还可以在一个地方优化,哪里呢?那就是除了访问数据库之外的另一个可能阻塞的地方——写文件。这里也可以考虑多线程并发,但是不要多个线程写一个文件,可以每个线程写一个文件,最后把多各文件进行汇总,就得到了最终的数据库备份脚本。
另外有一个方案是——每个访问数据库的线程对应一个生成SQL并写文件的线程,然后一个总线程,总线程负责给每个问数据库的线程分配任务,最后把所有SQL文件汇总成最终结果。
LZ先按照第一个方案优化下,这个是必须的。不满足再进行后续优化。
你这种方式本身就有问题啊。
600万全表数据,肯定不适合使用sql方式导出啊,就算你花费时间生成这样的sql,到时候还原时,批量更新或插入也会非常慢的。什么数据库?为何不做增量备份呢?拼接sql的方式不如用脚本来备份。
像这种东西 还是交给数据库来的快 可以考虑下多线程并发查 然后单线程写
时间很大一部分花在了网络io上
每次都导出这么多数据?
这就不是java干的活,强扭的瓜不甜 数据库备份当然是用数据库干啊
直接调用mysqldump
Runtime.getRuntime().exec("%MYSQL_HOME%\bin>mysqldump -uroot -proot DB_NAME > D:/DB_NAME_BAK.sql");
使用数据库自己的备份 举个例子oracle 采用sqlload