为什么我插入100万条数据要4.04个小时,帮帮我看看原因

孤狼仔 2013-07-13 08:37:44
各位大神,我测试了一个插入100w的程序,要用时4.04个小时,这是我不敢相信的,因为我还要插入9亿条记录这不知道要用多少时间!所以请大家帮我看看我的插入语句有什么可以优化的地方,还是表结构可以优化。有对该问题有什么要求尽管提出来。谢谢了


为什么要用这么长的时间呀。让小弟好不久等呀,大神help,help。。小弟在线狂等呀!

...全文
957 25 打赏 收藏 转发到动态 举报
写回复
用AI写文章
25 条回复
切换为时间正序
请发表友善的回复…
发表回复
孤狼仔 2013-07-24
  • 打赏
  • 举报
回复
引用 24 楼 prolee750607 的回复:
呵呵,谢谢你的建议,您讲的我有些地方不太懂,暂时还没接触过, 1.你讲的这个我试过,但是我要插入的还有二进制字节,这个不能用java组合该字符串吧? 2.alter table tbl disable keys我不知道是什么意思, 3.我不知道tokudb是什么引擎,我平时没见过,呵呵! 谢谢大神!!!
我不是大神,只是初学者 1.如果你已经试过,那就算了,是什么二进制你可以举例,按理是不会因为二进制字节产生问题,也许要转义。 2.这是暂时关闭表tbl索引,插入后用alter table tbl enable keys打开索引,tbl是个表名 3.这个引擎比innodb最大的特点就是写入速度快,这里有介绍[/quote]
代码誊写工 2013-07-24
  • 打赏
  • 举报
回复
引用 23 楼 liyi4337222 的回复:
呵呵,谢谢你的建议,您讲的我有些地方不太懂,暂时还没接触过, 1.你讲的这个我试过,但是我要插入的还有二进制字节,这个不能用java组合该字符串吧? 2.alter table tbl disable keys我不知道是什么意思, 3.我不知道tokudb是什么引擎,我平时没见过,呵呵! 谢谢大神!!!
我不是大神,只是初学者 1.如果你已经试过,那就算了,是什么二进制你可以举例,按理是不会因为二进制字节产生问题,也许要转义。 2.这是暂时关闭表tbl索引,插入后用alter table tbl enable keys打开索引,tbl是个表名 3.这个引擎比innodb最大的特点就是写入速度快,这里有介绍
孤狼仔 2013-07-24
  • 打赏
  • 举报
回复
引用 22 楼 prolee750607 的回复:
我不懂Java,仅提几个小建议,未必正确,仅供参考: 1.采用bulk insert insert into cetext(i_zi) value(?1),(?2),(?3)...(?100w)一次提交所有插入,请用java组合该庞大字符串,或者用Java写成CSV/TEXT文件用load data导入 2.innodb引擎可以对nnodb_file_per_table,innodb_flush_log_at_trx_commit等进行优化,在my.cnf/my.ini修改值,参考MySQL官方手册 3.关闭自动提交auto commit和索引(alter table tbl disable keys),但别忘了干完后开启 4.检查你的MySQL服务器,这么慢肯定有问题,不是内存就是硬盘 4.把innodb改成tokudb
呵呵,谢谢你的建议,您讲的我有些地方不太懂,暂时还没接触过, 1.你讲的这个我试过,但是我要插入的还有二进制字节,这个不能用java组合该字符串吧? 2.alter table tbl disable keys我不知道是什么意思, 3.我不知道tokudb是什么引擎,我平时没见过,呵呵! 谢谢大神!!!
代码誊写工 2013-07-23
  • 打赏
  • 举报
回复
我不懂Java,仅提几个小建议,未必正确,仅供参考: 1.采用bulk insert insert into cetext(i_zi) value(?1),(?2),(?3)...(?100w)一次提交所有插入,请用java组合该庞大字符串,或者用Java写成CSV/TEXT文件用load data导入 2.innodb引擎可以对nnodb_file_per_table,innodb_flush_log_at_trx_commit等进行优化,在my.cnf/my.ini修改值,参考MySQL官方手册 3.关闭自动提交auto commit和索引(alter table tbl disable keys),但别忘了干完后开启 4.检查你的MySQL服务器,这么慢肯定有问题,不是内存就是硬盘 4.把innodb改成tokudb
孤狼仔 2013-07-22
  • 打赏
  • 举报
回复
引用 17 楼 yousteely 的回复:
[quote=引用 14 楼 liyi4337222 的回复:] [quote=引用 12 楼 yousteely 的回复:] 我看楼主用的是java的jdbc,除了前面各位讲到的数据库优化之外java方面也可以优化一下.以下是关键代码。

sql=insert into cetext(i_zi) value(?)
PreparedStatement pstmt = conn.prepareStatement(sql);
for (int i=0;i<1000000;i++){
    pstmt.setInt(1,i);
    pstmt.addBatch();
    if (i%200==0){ //每200条执行一次,可以缓解mysql的压力
        pstmt.executeBatch()
    }
}
使用批处理应该也可以减轻不少压力。
讲的不错,但是,你不觉的数据库执行的次数变多了吗?这样会不会很慢[/quote] 这个要看你的数据库服务配置,比如一次执行一千条的时间都在接受范围内,你就可以把200改为1000,以此类推。方案在这里只能做为参考,因为必竟服务器的配置未必相同,达到的效果也会不一样。[/quote] 这个配置看哪个变量呀?
steely_chen 2013-07-22
  • 打赏
  • 举报
回复
引用 14 楼 liyi4337222 的回复:
[quote=引用 12 楼 yousteely 的回复:] 我看楼主用的是java的jdbc,除了前面各位讲到的数据库优化之外java方面也可以优化一下.以下是关键代码。

sql=insert into cetext(i_zi) value(?)
PreparedStatement pstmt = conn.prepareStatement(sql);
for (int i=0;i<1000000;i++){
    pstmt.setInt(1,i);
    pstmt.addBatch();
    if (i%200==0){ //每200条执行一次,可以缓解mysql的压力
        pstmt.executeBatch()
    }
}
使用批处理应该也可以减轻不少压力。
讲的不错,但是,你不觉的数据库执行的次数变多了吗?这样会不会很慢[/quote] 这个要看你的数据库服务配置,比如一次执行一千条的时间都在接受范围内,你就可以把200改为1000,以此类推。方案在这里只能做为参考,因为必竟服务器的配置未必相同,达到的效果也会不一样。
孤狼仔 2013-07-22
  • 打赏
  • 举报
回复
引用 19 楼 biandongfeng 的回复:
你这循环就不能这么写 你这相当于访问了100万次数据库
那我该这么写呀,大神,给个例子吧!
steely_chen 2013-07-22
  • 打赏
  • 举报
回复
引用 18 楼 liyi4337222 的回复:
[quote=引用 17 楼 yousteely 的回复:] [quote=引用 14 楼 liyi4337222 的回复:] [quote=引用 12 楼 yousteely 的回复:] 我看楼主用的是java的jdbc,除了前面各位讲到的数据库优化之外java方面也可以优化一下.以下是关键代码。

sql=insert into cetext(i_zi) value(?)
PreparedStatement pstmt = conn.prepareStatement(sql);
for (int i=0;i<1000000;i++){
    pstmt.setInt(1,i);
    pstmt.addBatch();
    if (i%200==0){ //每200条执行一次,可以缓解mysql的压力
        pstmt.executeBatch()
    }
}
使用批处理应该也可以减轻不少压力。
讲的不错,但是,你不觉的数据库执行的次数变多了吗?这样会不会很慢[/quote] 这个要看你的数据库服务配置,比如一次执行一千条的时间都在接受范围内,你就可以把200改为1000,以此类推。方案在这里只能做为参考,因为必竟服务器的配置未必相同,达到的效果也会不一样。[/quote] 这个配置看哪个变量呀?[/quote] if (i%200==0){ //每200条执行一次,可以缓解mysql的压力 这里的200 是说每累积了多少条就去执行一次
biandongfeng 2013-07-22
  • 打赏
  • 举报
回复
你这循环就不能这么写 你这相当于访问了100万次数据库
孤狼仔 2013-07-21
  • 打赏
  • 举报
回复
引用 15 楼 aca_jingru 的回复:
其实可以试试其他引擎,比如说archive 引擎。
好的,但是我只想问问出了什么问题
过去的我 2013-07-20
  • 打赏
  • 举报
回复
其实可以试试其他引擎,比如说archive 引擎。
孤狼仔 2013-07-20
  • 打赏
  • 举报
回复
引用 12 楼 yousteely 的回复:
我看楼主用的是java的jdbc,除了前面各位讲到的数据库优化之外java方面也可以优化一下.以下是关键代码。

sql=insert into cetext(i_zi) value(?)
PreparedStatement pstmt = conn.prepareStatement(sql);
for (int i=0;i<1000000;i++){
    pstmt.setInt(1,i);
    pstmt.addBatch();
    if (i%200==0){ //每200条执行一次,可以缓解mysql的压力
        pstmt.executeBatch()
    }
}
使用批处理应该也可以减轻不少压力。
讲的不错,但是,你不觉的数据库执行的次数变多了吗?这样会不会很慢
steely_chen 2013-07-17
  • 打赏
  • 举报
回复
我看楼主用的是java的jdbc,除了前面各位讲到的数据库优化之外java方面也可以优化一下.以下是关键代码。

sql=insert into cetext(i_zi) value(?)
PreparedStatement pstmt = conn.prepareStatement(sql);
for (int i=0;i<1000000;i++){
    pstmt.setInt(1,i);
    pstmt.addBatch();
    if (i%200==0){ //每200条执行一次,可以缓解mysql的压力
        pstmt.executeBatch()
    }
}
使用批处理应该也可以减轻不少压力。
孤狼仔 2013-07-17
  • 打赏
  • 举报
回复
引用 8 楼 fremcode 的回复:
innodb_flush_log_at_trx_commit的意思是每执行X条操作会像日志里写入,把设为0可能会影响到事务回滚,安全性不高,可能会插入错误的数据。但是速度有很大改善。 我的建议跟ACMAIN_CHM 版主大神是一样的,从txt,excel导入数据库重新生成新表速度更快。 还有一个就是因为索引的原因。
虽然我不懂什么事务管理,但是你讲的很有到底,谢谢了
  • 打赏
  • 举报
回复
引用 9 楼 u010714871 的回复:
把和数据库的交互放到一次来执行,你可以循环把语句生成到文本,然后再一次导入进去
嗯 200万条数据我写文件花了不到5分钟,循环取语句执行的话每分钟只能插入3K条 有什么方法能够与数据库只有一次交互
无名氏W 2013-07-16
  • 打赏
  • 举报
回复
把和数据库的交互放到一次来执行,你可以循环把语句生成到文本,然后再一次导入进去
土豆王 2013-07-15
  • 打赏
  • 举报
回复
innodb_flush_log_at_trx_commit的意思是每执行X条操作会像日志里写入,把设为0可能会影响到事务回滚,安全性不高,可能会插入错误的数据。但是速度有很大改善。 我的建议跟ACMAIN_CHM 版主大神是一样的,从txt,excel导入数据库重新生成新表速度更快。 还有一个就是因为索引的原因。
孤狼仔 2013-07-15
  • 打赏
  • 举报
回复
引用 6 楼 rucypli 的回复:
把索引先取消了。 然后在EXCEL或者其它软件中把数据准备好生成 txt或者csv文件。 再用MYSQL的 load data 把数据传到表中。
谢谢 版主,我马上把索引取消了,再做实验,在线等我的消息[/quote] 大神 这句话是什么意思呀?[/quote] 在配置文件里mysqld加上innodb_flush_log_at_trx_commit=0 重启mysql服务[/quote] 大神这个配置是什么意思呀innodb_flush_log_at_trx_commit;
孤狼仔 2013-07-14
  • 打赏
  • 举报
回复
引用 3 楼 liyi4337222 的回复:
[quote=引用 1 楼 ACMAIN_CHM 的回复:] 把索引先取消了。 然后在EXCEL或者其它软件中把数据准备好生成 txt或者csv文件。 再用MYSQL的 load data 把数据传到表中。
谢谢 版主,我马上把索引取消了,再做实验,在线等我的消息[/quote] 大神 这句话是什么意思呀?
rucypli 2013-07-14
  • 打赏
  • 举报
回复
引用 5 楼 liyi4337222 的回复:
[quote=引用 3 楼 liyi4337222 的回复:] [quote=引用 1 楼 ACMAIN_CHM 的回复:] 把索引先取消了。 然后在EXCEL或者其它软件中把数据准备好生成 txt或者csv文件。 再用MYSQL的 load data 把数据传到表中。
谢谢 版主,我马上把索引取消了,再做实验,在线等我的消息[/quote] 大神 这句话是什么意思呀?[/quote] 在配置文件里mysqld加上innodb_flush_log_at_trx_commit=0 重启mysql服务
加载更多回复(4)

56,673

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧