hxtao001
hxtao001
2013-04-19 20:33
浏览 1.9k
已采纳

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]

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

7条回答 默认 最新

  • yunzhu666
    yunzhu666 2013-04-19 21:52
    已采纳

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

    点赞 评论
  • Dead_Knight
    Dead_Knight 2013-04-19 20:42

    你这种方式本身就有问题啊。
    600万全表数据,肯定不适合使用sql方式导出啊,就算你花费时间生成这样的sql,到时候还原时,批量更新或插入也会非常慢的。什么数据库?为何不做增量备份呢?拼接sql的方式不如用脚本来备份。

    点赞 评论
  • jinnianshilongnian
    jinnianshilongnian 2013-04-19 20:42

    像这种东西 还是交给数据库来的快 可以考虑下多线程并发查 然后单线程写
    时间很大一部分花在了网络io上

    点赞 评论
  • zyn010101
    zyn010101 2013-04-19 21:01

    每次都导出这么多数据?

    点赞 评论
  • wanglei2012
    wanglei2012 2013-04-19 21:05

    这就不是java干的活,强扭的瓜不甜 数据库备份当然是用数据库干啊

    点赞 评论
  • weixin_42617871
    成正炳 2013-04-20 00:33

    直接调用mysqldump
    Runtime.getRuntime().exec("%MYSQL_HOME%\bin>mysqldump -uroot -proot DB_NAME > D:/DB_NAME_BAK.sql");

    点赞 评论
  • ycscsjj
    ycscsjj 2013-04-20 02:00

    使用数据库自己的备份 举个例子oracle 采用sqlload

    点赞 评论

相关推荐