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个回答

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

yunzhu666
yunzhu666 能有这么有意思的项目写已经很不多啦,想当年我都没有代码可写,只能自己鼓捣
7 年多之前 回复
hxtao001
hxtao001 现在在网吧。。木有工具。苦逼的实习生,伤不起,~~~~(>_<)~~~~ ,
7 年多之前 回复
yunzhu666
yunzhu666 呵呵,赶紧动手吧,第一步优化是必须的。
7 年多之前 回复
hxtao001
hxtao001 O(∩_∩)O~。。。 还是你分析的详细
7 年多之前 回复
yunzhu666
yunzhu666 好吧,回复慢了,看到开涛已经在回答的回复里都说到了
7 年多之前 回复

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

jinnianshilongnian
jinnianshilongnian 是查影响速度还是写到影响速度? 这个都影响 写要看磁盘 读可以这样优化 select 列名 from table where id > ? limit 0,1000 1、把之前的*---->改成列名 可以减少sql解析 2、id > ? ---> 指定上次的最大的id 这样总从第一页开始 这个sql就只需解析一次 而且速度肯定比以前快
7 年多之前 回复
jinnianshilongnian
jinnianshilongnian 你访问本地的数据库 也是走网络的 通过tcp/ip 所以网络io占一部分 再就是写磁盘 估计你的磁盘转速慢 这也是一部分 你试试我说的 估计能提高一些 但可能不会很大 如果你对速度没要求的话 其实可以把这个功能做成定时任务 如每天临晨3点做 这样在那个点 访问量少 压力小
7 年多之前 回复
hxtao001
hxtao001 是查影响速度还是写到影响速度?
7 年多之前 回复
hxtao001
hxtao001 哦,我试试。能不能跟我讲讲我用贴出来的那种方式速度之所以慢的原因,因为数据库是在本地,不存在网络问题,跟IO有关?
7 年多之前 回复
jinnianshilongnian
jinnianshilongnian 还是一张表 不是有分页嘛 假设600w 第一个线程处理前100w 第二个从200w 以此类推
7 年多之前 回复
hxtao001
hxtao001 线程怎么分工?每个线程处理特定的几张表?
7 年多之前 回复
jinnianshilongnian
jinnianshilongnian 1、首先查询总记录数 假设600w行 2、600w/6个线程 并发 写 6个文件 (每个线程100w) 3、合并文件(当然也可以考虑不合并) 可以试试这种策略
7 年多之前 回复
hxtao001
hxtao001 如果就要用这种方式,怎么做才能速度最快,要求跨数据库。。所以不能用工具。。
7 年多之前 回复
jinnianshilongnian
jinnianshilongnian 比如 每天定时 临晨3点备份 如mysql使用mysqldump
7 年多之前 回复

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

Dead_Knight
Dead_Knight http://www.oschina.net/question/122462_44121 这里也有人讨论过,也是用多线程分页来处理。但是我觉得提高不了多少效率。你可以试试看
7 年多之前 回复
hxtao001
hxtao001 我这里用的是mysql,但老大要求与数据库无关,我用bufferedWriter写,没有堆溢出的的总是。因为系统有备份还原功能,你说的增量备份我有时间去了解,如果非要用java读取的方法,怎样做到速度最快?
7 年多之前 回复
Dead_Knight
Dead_Knight 你这是mysql数据库么?你这个用多线程也不好办,因为不能全部读取记录600万条记录,这样jvm堆内存不够用的。建议你第一次这么做,然后就在mysql中配置增量备份的log,然后导出增量sql
7 年多之前 回复
hxtao001
hxtao001 老大的要求就是这样,写成脚本的形式保存到txt中
7 年多之前 回复
hxtao001
hxtao001 我试过过,批量插入速度100万数据几秒,不懂增量备份,脚本备份,要求与数据库无关,平台无关
7 年多之前 回复

每次都导出这么多数据?

hxtao001
hxtao001 理论上是的,因为是备份还原,之前几位大哥说这种方式不对,但假如这里不是备分还原,而是从数据库中查询所有数据保存到txt中(总有这种场景吧),速度还不是一样慢么。。
7 年多之前 回复

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

hxtao001
hxtao001 我也知道强扭的瓜不甜~~~~(>_<)~~~~,可是作为一个刚进公司的实习生,我只能执行。如果非要用这种方式,有没有提高速度的方法?
7 年多之前 回复

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

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

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐