查看: 4695|回复: 7
BULK COLLECT INTO一次性插入大量数据会造成内存溢出么？
认证徽章论坛徽章:1
如果我一个表中有几百万条数据，然后我在存储过程中执行一条语句 select * bulk collect into t_
这样的操作会让你的程序出现内存溢出的问题么，求解！
本地自测，拿一个几十万行的数据插入，好像无任何问题。
还有，BULK COLLECT INO批量插入的时候报通信通道的文件结束，ORACLE连接断开，网上解释说是因为有日期字段为空，所以才导致这个问题，敢问各位大侠，是否真的是这样。
论坛徽章:395
那就制造一个日期字段为空测试下
论坛徽章:263
加子句 limit 1000
论坛徽章:479
&通信通道的文件结束&通常是ORACLE的BUG所致。
内存溢出没有碰到过，可能会被换页到磁盘上去，应该如楼上所言加上数组大小的限制。
招聘 : 论坛徽章:38
如果不加limit，效率可能反而是最差的
认证徽章论坛徽章:25
limit 是 mysql的语法
论坛徽章:1088
原帖由 qingyun 于
19:28 发表
limit 是 mysql的语法
哈哈，这个不是分页，是plsql里的limit
论坛徽章:2
内存溢出倒不会，不过你的数据库MEMORY,UNDO,TEMP参数不合适的话可能会有其它问题，根据实际情况自己调调LIMIT
itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：10　广播电视节目制作经营许可证：编号（京）字第1149号开发（79）
在需要向Sqlserver中批量导入数据的时候，Bulk Insert是个利器。
使用的方法：
BULK INSERT CSVTable
　　FROM 'D:\avgdata.csv'
CODEPAGE='ANSI',
DATAFILETYPE='char',
　　FIELDTERMINATOR = ','
现场碰到有台服务器window2003系统升级后，数据统计服务无法启动。
提示“服务依赖项不存在，启动服务失败”。
当时的处理方法：
把数据统计服务挪到了另一台服务器上，安装及启动都正常，但统计数据csv文件
无法正常写入数据库中。
查找原因，在SqlServer Management Studio中手动输入该命令，
提示“无法找到文件”。
仔细分析下，原来bulk insert必须在Sqlserver安装的服务器上执行，
Bulk insert找的目录不是当前机器的目录，而是服务所在机器的目录，就和数据库备份、恢复一样。
&&相关文章推荐
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：98480次
积分：1371
积分：1371
排名：千里之外
原创：28篇
转载：80篇
(1)(3)(1)(1)(4)(2)(1)(2)(2)(1)(2)(1)(1)(4)(2)(2)(3)(5)(3)(4)(1)(3)(1)(1)(1)(4)(2)(2)(6)(2)(4)(4)(1)(3)(5)(5)(3)(3)(1)(7)(2)(1)(1)
(window.slotbydup = window.slotbydup || []).push({
id: '4740881',
container: s,
size: '200,200',
display: 'inlay-fix'2013年 总版技术专家分年内排行榜第二
2014年8月 总版技术专家分月排行榜第一2014年7月 总版技术专家分月排行榜第一2014年6月 总版技术专家分月排行榜第一2014年5月 总版技术专家分月排行榜第一2014年4月 总版技术专家分月排行榜第一2014年3月 总版技术专家分月排行榜第一2014年1月 总版技术专家分月排行榜第一2013年12月 总版技术专家分月排行榜第一
2013年 总版技术专家分年内排行榜第二
2014年8月 总版技术专家分月排行榜第一2014年7月 总版技术专家分月排行榜第一2014年6月 总版技术专家分月排行榜第一2014年5月 总版技术专家分月排行榜第一2014年4月 总版技术专家分月排行榜第一2014年3月 总版技术专家分月排行榜第一2014年1月 总版技术专家分月排行榜第一2013年12月 总版技术专家分月排行榜第一
2013年 总版技术专家分年内排行榜第二
2014年8月 总版技术专家分月排行榜第一2014年7月 总版技术专家分月排行榜第一2014年6月 总版技术专家分月排行榜第一2014年5月 总版技术专家分月排行榜第一2014年4月 总版技术专家分月排行榜第一2014年3月 总版技术专家分月排行榜第一2014年1月 总版技术专家分月排行榜第一2013年12月 总版技术专家分月排行榜第一
2013年 总版技术专家分年内排行榜第二
2014年8月 总版技术专家分月排行榜第一2014年7月 总版技术专家分月排行榜第一2014年6月 总版技术专家分月排行榜第一2014年5月 总版技术专家分月排行榜第一2014年4月 总版技术专家分月排行榜第一2014年3月 总版技术专家分月排行榜第一2014年1月 总版技术专家分月排行榜第一2013年12月 总版技术专家分月排行榜第一
本帖子已过去太久远了，不再提供回复功能。博客访问： 1196485
博文数量： 456
博客积分： 8399
博客等级： 中将
技术积分： 4350
注册时间：
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： Mysql/postgreSQL
最近一个项目测试，有几个mysql数据库的表数据记录达到了几十万条，在搭建测试环境 导入 测试数据时，十分慢。
在网上搜索了一下，有下面一些方法可以加快
mysql数据库导入数据的速度：
0. 最快的当然是直接 copy 数据库表的数据文件（版本和平台最好要相同或相似）；
1. 设置 innodb_flush_log_at_trx_commit&=&0 ，相对于 innodb_flush_log_at_trx_commit&= 1 可以十分明显的提升导入速度；
2. 使用 load data local infile 提速明显；
3. 修改参数 bulk_insert_buffer_size， 调大批量插入的缓存；
4. 合并多条 insert 为一条: insert into t values(a,b,c),& (d,e,f) ,,,
5. 手动使用事物；
下面是UC的一篇相关博客文章：
对于一些数据量较大的系统，数据库面临的问题除了查询效率低下，还有就是数据入库时间长。特别像报表系统，每天花费在数据导入上的时间可能会长达几个小时或十几个小时之久。因此，优化数据库插入性能是很有意义的。
经过对MySQL innodb的一些性能测试，发现一些可以提高insert效率的方法，供大家参考参考。
1. 一条SQL语句插入多条数据。
常用的插入语句如：
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('1', 'userid_1', 'content_1', 1);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)& VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)& VALUES ('1', 'userid_1', 'content_1', 1);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1);
修改后的插入操作能够提高程序的插入效率。这里第二种SQL执行效率高的主要原因是合并后日志量（MySQL的binlog和innodb的事务让日志）
减少了，降低日志刷盘的数据量和频率，从而提高效率。通过合并SQL语句，同时也能减少SQL语句解析的次数，减少网络传输的IO。
这里提供一些测试对比数据，分别是进行单条数据的导入与转化成一条SQL语句进行导入，分别测试1百、1千、1万条数据记录。
2. 在事务中进行插入处理。
把插入修改成：
START TRANSACTION;
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('1', 'userid_1', 'content_1', 1);
START TRANSACTION;
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)&&&
VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)&&
VALUES ('1', 'userid_1', 'content_1', 1);
...COMMIT;
使用事务可以提高数据的插入效率，这是因为进行一个INSERT操作时，MySQL内部会建立一个事务，在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗，所有插入都在执行后才进行提交操作。
这里也提供了测试对比，分别是不使用事务与使用事务在记录数为1百、1千、1万的情况。
3. 数据有序插入。
数据有序的插入是指插入记录在主键上是有序排列，例如datetime是记录的主键：
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('1', 'userid_1', 'content_1', 1);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('2', 'userid_2', 'content_2',2);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)& VALUES ('1', 'userid_1', 'content_1', 1);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)& VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)& VALUES ('2', 'userid_2', 'content_2',2);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('1', 'userid_1', 'content_1', 1);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('2', 'userid_2', 'content_2',2);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)& VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)& VALUES ('1', 'userid_1', 'content_1', 1);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)& VALUES ('2', 'userid_2', 'content_2',2);
由于数据库插入时，需要维护索引数据，无序的记录会增大维护索引的成本。我们可以参照innodb使用的B+tree索引，如果每次插入记录都在索引的最
后面，索引的定位效率很高，并且对索引调整较小；如果插入的记录在索引中间，需要B+tree进行分裂合并等处理，会消耗比较多计算资源，并且插入记录的
索引定位效率会下降，数据量较大时会有频繁的磁盘操作。
下面提供随机数据与顺序数据的性能对比，分别是记录为1百、1千、1万、10万、100万。
从测试结果来看，该优化方法的性能有所提高，但是提高并不是很明显。
性能综合测试：
这里提供了同时使用上面三种方法进行INSERT效率优化的测试。
从测试结果可以看到，合并数据+事务的方法在较小数据量时，性能提高是很明显的，数据量较大时（1千万以上），性能会急剧下降，这是由于此时数据量超过了
innodb_buffer的容量，每次定位索引涉及较多的磁盘读写操作，性能下降较快。而使用合并数据+事务+有序数据的方式在数据量达到千万级以上表
现依旧是良好，在数据量较大时，有序数据索引定位较为方便，不需要频繁对磁盘进行读写操作，所以可以维持较高的性能。
注意事项：
1. SQL语句是有长度限制，在进行数据合并在同一SQL中务必不能超过SQL长度限制，通过max_allowed_packet配置可以修改，默认是1M，测试时修改为8M。
2. 事务需要控制大小，事务太大可能会影响执行的效率。MySQL有innodb_log_buffer_size配置项，超过这个值会把innodb的数据刷到磁盘中，这时，效率会有所下降。所以比较好的做法是，在数据达到这个这个值前进行事务提交。
阅读(19654) | 评论(2) | 转发(2) |
相关热门文章
给主人留下些什么吧！~~
千万级不算大，优化一下，可以在几十到几百毫秒几查询。&/read-16422.html
请登录后评论。