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

2013-07-13 08:37:44 1k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

【数据库基础概念（入门篇）04】数据库索引内幕：为什么正确的索引让查询速度提升100倍数据库索引优化指南：从原理到实践核心摘要数据库索引是提升查询性能的关键技术，相当于数据的地图导航系统。本文通过生动类比和实验演示揭示：性能差异：在百万级数据表中，添加索引可使查询速度提升100倍，从0.4秒缩短到0.004秒工作原理：索引类似图书馆目录系统，通过B+树数据结构实现高效查找，仅需几次比较即可定位数据索引类型： B+树索引：适合范围查询，时间复杂度O(log n) 哈希索引：等值查询速度O(1)，但不支持范围查询全文索引：支持自然语言文本搜索空间索引：优化地理数据查询实践价值：正阅读详情

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

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

...全文

Ubuntu 14.04下MongoDB数据导入导出实战指南 MongoDB数据迁移是数据库运维的基础能力，其核心在于理解JSON/CSV格式转换、跨版本兼容性、权限认证机制与数据一致性保障。在Linux系统（尤其是长期服役的Ubuntu 14.04）环境下，mongoimport和mongoexport作为轻量级、无依赖、可审计的命令行工具，承担着冷备份、测试数据供给、合规导出等关键任务。本文聚焦于真实生产约束——老旧操作系统、旧版MongoDB 2.4/2.6、终端编码陷阱、NDJSON格式歧义、时区与日期解析异常等典型问题，结合权限配置、字段筛选、分页导出、流式阅读详情

孤狼仔

MySQL 57,062 成员 56,759 内容

+ 关注 + 加入社区

切换为时间正序

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

孤狼仔 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()
    }
}
使用批处理应该也可以减轻不少压力。

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导入数据库重新生成新表速度更快。还有一个就是因为索引的原因。

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

麦芽糖的小男孩 2013-07-16

打赏
举报

引用 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）

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

相关推荐