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

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


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

25 个解决方案

#1

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

#2

谢谢 版主，我马上把索引取消了，再做实验，在线等我的消息

#3

引用 1 楼 ACMAIN_CHM 的回复:

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

谢谢 版主，我马上把索引取消了，再做实验，在线等我的消息

#4

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

#5

引用 3 楼 liyi4337222 的回复:

Quote: 引用 1 楼 ACMAIN_CHM 的回复:

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

谢谢 版主，我马上把索引取消了，再做实验，在线等我的消息

大神 这句话是什么意思呀？

#6

引用 5 楼 liyi4337222 的回复:

Quote: 引用 3 楼 liyi4337222 的回复:

Quote: 引用 1 楼 ACMAIN_CHM 的回复:

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

谢谢 版主，我马上把索引取消了，再做实验，在线等我的消息

大神 这句话是什么意思呀？

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

#7

引用 6 楼 rucypli 的回复:

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

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

大神这个配置是什么意思呀innodb_flush_log_at_trx_commit；

#8

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

#9

把和数据库的交互放到一次来执行，你可以循环把语句生成到文本，然后再一次导入进去

#10

引用 9 楼 u010714871 的回复:

把和数据库的交互放到一次来执行，你可以循环把语句生成到文本，然后再一次导入进去

嗯   200万条数据我写文件花了不到5分钟，循环取语句执行的话每分钟只能插入3K条
有什么方法能够与数据库只有一次交互

#11

引用 8 楼 fremcode 的回复:

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

虽然我不懂什么事务管理，但是你讲的很有到底，谢谢了

#12

我看楼主用的是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()
    }
}

使用批处理应该也可以减轻不少压力。

#13

该回复于2013-07-19 15:28:32被管理员删除

#14

引用 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()
    }
}

使用批处理应该也可以减轻不少压力。

讲的不错，但是，你不觉的数据库执行的次数变多了吗？这样会不会很慢

#15

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

#16

引用 15 楼 aca_jingru 的回复:

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

好的，但是我只想问问出了什么问题

#17

引用 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()
    }
}

使用批处理应该也可以减轻不少压力。

讲的不错，但是，你不觉的数据库执行的次数变多了吗？这样会不会很慢

这个要看你的数据库服务配置，比如一次执行一千条的时间都在接受范围内，你就可以把200改为1000，以此类推。方案在这里只能做为参考，因为必竟服务器的配置未必相同，达到的效果也会不一样。

#18

引用 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()
    }
}

使用批处理应该也可以减轻不少压力。

讲的不错，但是，你不觉的数据库执行的次数变多了吗？这样会不会很慢

这个要看你的数据库服务配置，比如一次执行一千条的时间都在接受范围内，你就可以把200改为1000，以此类推。方案在这里只能做为参考，因为必竟服务器的配置未必相同，达到的效果也会不一样。

这个配置看哪个变量呀？

#19

你这循环就不能这么写 你这相当于访问了100万次数据库

#20

引用 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()
    }
}

使用批处理应该也可以减轻不少压力。

讲的不错，但是，你不觉的数据库执行的次数变多了吗？这样会不会很慢

这个要看你的数据库服务配置，比如一次执行一千条的时间都在接受范围内，你就可以把200改为1000，以此类推。方案在这里只能做为参考，因为必竟服务器的配置未必相同，达到的效果也会不一样。

这个配置看哪个变量呀？

if (i%200==0){ //每200条执行一次，可以缓解mysql的压力

这里的200 是说每累积了多少条就去执行一次

#21

引用 19 楼 biandongfeng 的回复:

你这循环就不能这么写 你这相当于访问了100万次数据库

那我该这么写呀，大神，给个例子吧！

#22

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

#23

引用 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是什么引擎，我平时没见过，呵呵！
谢谢大神！！！

#24

引用 23 楼 liyi4337222 的回复:

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

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

#25

引用 24 楼 prolee750607 的回复:

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

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