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

Question

各位大神，我测试了一个插入100w的程序，要用时4.04个小时，这是我不敢相信的，因为我还要插入9亿条记录这不知道要用多少时间！所以请大家帮我看看我的插入语句有什么可以优化的地方，还是表结构可以优化。有对该问题有什么要求尽管提出来。谢谢了
为什么要用这么长的时间呀。让小弟好不久等呀，大神help，help。。小弟在线狂等呀！ 

Answer 1

把索引先取消了。
然后在EXCEL或者其它软件中把数据准备好生成 txt或者csv文件。
再用MYSQL的 load data 把数据传到表中。

Answer 2

在配置文件里mysqld加上innodb_flush_log_at_trx_commit=0  重启mysql服务

Answer 3

谢谢 版主，我马上把索引取消了，再做实验，在线等我的消息
大神 这句话是什么意思呀？
在配置文件里mysqld加上innodb_flush_log_at_trx_commit=0  重启mysql服务

Answer 4

谢谢 版主，我马上把索引取消了，再做实验，在线等我的消息
大神 这句话是什么意思呀？
在配置文件里mysqld加上innodb_flush_log_at_trx_commit=0  重启mysql服务大神这个配置是什么意思呀innodb_flush_log_at_trx_commit；

Answer 5

innodb_flush_log_at_trx_commit的意思是每执行X条操作会像日志里写入，把设为0可能会影响到事务回滚，安全性不高，可能会插入错误的数据。但是速度有很大改善。
我的建议跟ACMAIN_CHM 版主大神是一样的，从txt，excel导入数据库重新生成新表速度更快。
还有一个就是因为索引的原因。

Answer 6

我看楼主用的是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()
    }
}使用批处理应该也可以减轻不少压力。

Answer 7

其实可以试试其他引擎，比如说archive 引擎。

Answer 8

讲的不错，但是，你不觉的数据库执行的次数变多了吗？这样会不会很慢
这个要看你的数据库服务配置，比如一次执行一千条的时间都在接受范围内，你就可以把200改为1000，以此类推。方案在这里只能做为参考，因为必竟服务器的配置未必相同，达到的效果也会不一样。这个配置看哪个变量呀？
if (i%200==0){ //每200条执行一次，可以缓解mysql的压力这里的200 是说每累积了多少条就去执行一次

Answer 9

我不懂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

Answer 10

呵呵，谢谢你的建议，您讲的我有些地方不太懂，暂时还没接触过,
1.你讲的这个我试过，但是我要插入的还有二进制字节，这个不能用java组合该字符串吧？
2.alter table tbl disable keys我不知道是什么意思，
3.我不知道tokudb是什么引擎，我平时没见过，呵呵！
谢谢大神！！！

Answer 11

我不是大神，只是初学者
1.如果你已经试过，那就算了，是什么二进制你可以举例，按理是不会因为二进制字节产生问题，也许要转义。
2.这是暂时关闭表tbl索引，插入后用alter table tbl enable keys打开索引，tbl是个表名
3.这个引擎比innodb最大的特点就是写入速度快,这里有介绍

Answer 12

我不是大神，只是初学者
1.如果你已经试过，那就算了，是什么二进制你可以举例，按理是不会因为二进制字节产生问题，也许要转义。
2.这是暂时关闭表tbl索引，插入后用alter table tbl enable keys打开索引，tbl是个表名
3.这个引擎比innodb最大的特点就是写入速度快,这里有介绍