java从数据库读取百万级数据保存到text文件中，速度慢，怎么解决

Question

代码如下，做一个数据脚本的备份与还原，从数据库查询所有记录，拼接成插入语句，数据库中有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]

Answer 1

这种方式确实本身就有问题，性能不会很高，像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先按照第一个方案优化下，这个是必须的。不满足再进行后续优化。