当前页面: 开发资料首页 → JSP 专题 → 100分求教：十万条数据，如何一次性插入数据库，才能保证效率？

100分求教：十万条数据，如何一次性插入数据库，才能保证效率？

摘要: 100分求教：十万条数据，如何一次性插入数据库，才能保证效率？

一个上报数据的项目，一次上报的数据记录量可能多达十几万条，甚至更多。

曾经想过用数据库提供的import/load工具来导入，但却苦于不知道如何在java中调用。

看了论坛里以前的一些帖子，对于大批量的插入操作，很多朋友都建议使用批插入方式（batchUpdate）。但如此巨量的数据如果一次性提交，是不是会极大的影响服务器性能？

麻烦大家给点建议，谢谢。

---

关注，学习中

---

学习中
批是好的方法

---

关注一下

---

getRunTime().exec来执行 sql loader

---

写JDBC效率是最高的，你用hibernate或者其它的外部工具它最终还是会转化成JDBC代码，而且JDBC本身是有很多性能调优的方法的，采用addBatch(String)是一个方法，还有用PreparedStatement会比直接用Statement要好。

---

关注

---

zgysc(翠湖寒), 你觉得利用Runtime直接调用数据库中的import工具来进行导入这种方法可行吗？如何捕获错误？

谢谢。

---

bcp 批量导入

---

statement.addBatch();
statement.executeBatch();

---

mark

---

qingyuan18(zealot_tang)
不用ORM你自己慢慢写效率高的JDBC吧

---

呵呵，直接写后台包，前台调用

---

mark

---

When loading large-volumes of data, you have several choices:

1 - Tune INSERTS - Parallelized programs doing concurrent INSERT statements

2 - Tune SQL*Loader - Using sqlldr Direct Load, and adjusting parameters improves INSERT performance.

3 - Tune imports - Use Oracle Data Pump (Formally Oracle import utility) - Here are tips for hypercharging Oracle import.

Optimizing Oracle INSERT performance

When using standard SQL statements to load Oracle data tables, there are several tuning approaches:

a - Manage segment header contention for parallel inserts - Make sure to define multiple freelist (or freelist groups) to remove contention for the table header. Multiple freelists add additional segment header blocks, removing the bottleneck. You can also use Automatic Segment Space Management (bitmap freelists) to support parallel DML, but ASSM has some limitations.

b - Parallelize the load - You can invoke parallel DML (i.e. using the PARALLEL and APPEND hint) to have multiple inserts into the same table. For this INSERT optimization, make sure to define multiple freelists and use the SQL "APPEND" option. Mark Bobak notes that if you submit parallel jobs to insert against the table at the same time, using the APPEND hint may cause serialization, removing the benefit of parallel jobstreams.


c - APPEND into tables - By using the APPEND hint, you ensure that Oracle always grabs "fresh" data blocks by raising the high-water-mark for the table. If you are doing parallel insert DML, the Append mode is the default and you don't need to specify an APPEND hint. Mark Bobak notes "Also, if you're going w/ APPEND, consider putting the table into NOLOGGING mode, which will allow Oracle to avoid almost all redo logging."

insert /*+ append */ into customer values ('hello',';there');


d - Use a large blocksize - By defining large (i.e. 32k) blocksizes for the target table, you reduce I/O because more rows fit onto a block before a "block full" condition (as set by PCTFREE) unlinks the block from the freelist.


e - Disable/drop indexes - It's far faster to rebuild indexes after the data load, all at-once. Also indexes will rebuild cleaner, and with less I/O if they reside in a tablespace with a large block size.

---

写JDBC效率是最高的，你用hibernate或者其它的外部工具它最终还是会转化成JDBC代码，而且JDBC本身是有很多性能调优的方法的，采用addBatch(String)是一个方法，还有用PreparedStatement会比直接用Statement要好。

---

UP

---

本来就是很慢 用SQLLD也是很慢的

---

多线程

---

学习

---

感谢诸位的答复。

为了实践批处理一次插入十万条数据，我昨晚做了个简单的测试。
数据库：DB2，表中没建任何键值和索引，通过JDBC本地驱动连接。并采用Spring的JdbcTemplate+事务的方式。性能还算不错。

但这种测试无疑是脱离实际的。现实中的表将建立很多索引，可能还有聚簇索引，表间关联等。表中的数据保守的讲在数千万条记录。

对JDBCTemplate的效率，我并不怀疑，基本上就是JDBC的简单封装，对性能的影响基本上可以忽略。问题就在于，插入过程中索引的重建、一致性检查、以及产生的大量日志，无疑都会大大的影响效率。

jiangsheng(蒋晟.Net[MVP])提供的理论，主要是针对Oracle，对DB2也许会有所不同。但如果只允许通过Servlet，估计只有JDBC这一条路可走。import/load方法涉及到本机码调用的问题，安全性和及时的用户反馈无法保证。

要提高插入效率，比较多的建议无非就是：
1、插入前删除索引，插入后重建；
2、把表设为不记录日志；
3、调整某些参数，让数据库的页空间尽量的大，以避免过多的I/O操作；

对于一个通过用户界面上传数据的项目来说，只有3还有可行之处。不记录日志似乎可行，但commit之后的性能问题，实在让人担心。

希望大家群策群力，帮忙给出最优的解决方案。谢谢。

---

学习

---

大家可以参考以下的一篇文章：

在DB2中提高INSERT性能
http://database.51cto.com/art/200512/12779.htm

---

路过关注~

---

路过关注，等待真正的解决办法

---

推荐用JDBC操作

---

学习

---

楼主的亲身验证和分析,是非常好的行为与精神。

当这个问题显得很困难,可能使获得解决办法的成本影响到整个项目的进行时,我想也就必须从宏观上来看看,这种插入大量数据的需求是如何产生的？这种需求是否合理？

程序性能方面：
１。提高插入速度,必须用多线程,对数据分批处理。

数据库承载方面：
２。利用数据库的分布特性,分布到几台数据服务器上,进行数据分流,应该可以提高插入效率和避免影响查询性能。最佳的情况是,假设有十台服务器,则数据插入时可以以轮插方式分插进去。

２。利用数据库的发布特性,可以分成专门插入的数据库和专门查询的数据库。专门插入的数据库在一批数据插入之后,利用一个访问数量较少的时间向专门查询的数据库作发布。

---

我以前做过的项目每天60w的数据量,为了提高效率基本上能试的都试过了,就个人经验来说最快的还是后台的bcp,其它的提速方法(删索引,不写日志,提高缓存,优化数据库配置,还有提高机器配置)上面基本上也都提到了,这几个里面比较明显的是提高机器配置^_^,别的不能从根本上有什么提高的,当然能都做了是最好了.对于你说的怕影响数据库性能我觉得你可以先把这些数据插入到一个临时表里面然后再从临时表导到主表里面,这样子把对用户的影响降低到最低.

---

同意楼上
多线程处理，分批插入不同的临时表
然后把所有临时表的数据导入到主表内部
最关键的是服务器的性能＝＝优秀（其他的都是多余，测试过100W以上的插入，普通的想法都是很少的提高，服务器性能上去后一切逗解决）

---

补充测试的环境为ｗｉｎ2003，ｏｒａｃｌｅ9.2　，100W时间4小时以上。
单纯数据库的插入，表没有关联。
好像这样的测试不现实吧
呵呵

---

关注

---

非常感谢theforever(碧海情天)和plphoenix() 两位的答复，你们提出了一些比较切实可行的解决方案。

首先这是个中央银行针对金融系统的大额现金数据进行统计收集和分析的B/S项目，从原来的C/S版改版过来。原计划每天进行一次上报，后改为每周上报，这样的数据量一下子剧增到十几万。

在数据库选择的方案上我们也是破费脑筋，原打算使用Sybase，后来由于IBM的Websphere和DB2都被人总行买断，所以从节约成本上来说选择了后者。但不管用谁，大批量的插入操作确实不可避免。而且目前没有采用分布式处理。

本项目采用了Ajax方案，数据上报经过压缩和编目通过XMLHttp上传后，用户持有一个等待界面等待操作结果。所以用户的及时反馈很重要。楼上提出的多线程方案我会考虑，毕竟用户同时会上报多个文件。但采用多线程后，为了用户反馈，Servlet线程就需要处于挂起状态了。服务器的配置是双CPU的，我想对于多线程应该很有帮助。

分成专门插入的数据库和专门查询的数据库，也许也可以算得上一个后备的解决方案。但数据的维护和更新会增加麻烦。

采用临时表的方式也是一个好的想法，但依然无法避免和解决记录插入时建立索引和关联表以及日志的开销。

总的来说，看来只能结合JDBC的批处理和从数据库配置的角度出发，才能拿出最优方案。

---

用多线程

---

楼上的测试：插入100W数据，用了4个小时？如果要是这样，让用户等待岂不是很糟的设计？

大家估算一下，现在的服务器初步方案是采用8G内存+双CPU(3.0G 双核)+6*146G SCSI硬盘。
在DB2数据库已有5000万数据的情况下，一次插入10W条数据，用户的响应时间有多久？

插入采用JDBC的批处理方式，考虑日志、索引、表关联等因素。

---

补充一下：操作系统 Susi Linux 6.0服务器版

---

真怀疑lz和我曾是一个公司的

---

呵呵，plphoenix() 何出此言啊？以前做过类似的项目？

---

暂时关闭日志，直接在后台做吧

---

mark

---

以前是招行c/s下的sybase,项目里面也有部分功能是和你现在的一样的...
我以前是删除索引然后再建的,但是这个有个特点的,本身数据表内数据相对插入的数据不是多太多倍(300w/60w),所以会有点用处,lz现在这样可能不是太合适了,5000w的表建索引的话估计得半个小时了(我以前的服务器配置和你差不多,就是CPU是4个双核的).
我想要是我做的话我会通过先建临时表,然后直接插入主表.不删索引.写日志(其实要是直接把一张表全部插入到另一张表的话这个影响不了太多时间的,同时由于插入时间会比较长,所以为了防止意外还是应该写的).
其实现在的根本是这张表的大小相对硬件配置来说太大了,最好能分表

---

其实你可以先试一下插入1000条或多一点看看要多少时间,然后再算出来是不是能够接受这样的等待.

---

sqlserver中用xp_cmdshell
例如
EXEC master..xp_cmdshell 'bcp pubs..title in e:/test.txt -c -t, -S127.0.0.1 -Usa -P '
这种是通过文本导入，可以通过参数修改，xp_cmdshell也可以导入excel
或者用BULK INSERT
例如
BULK INSERT pubs.dbo.[title] FROM 'e:/test.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR= ',',
ROWTERMINATOR='
'
)
xp_cmdshell会有安全隐患，所以一般网站数据库管理员都可能会封，BULK INSERT在2000下没问题，在2005下好像验证更严格。
两个的速度，10w条都不超过10秒

---

如果你能用工具连上数据库，那就直接用dts工具就可以，如果不可以，是web与数据库服务器分开的，那用sql语句

---

谢谢。plphoenix()你的经验对我很有帮助，我的确应该考虑一下分表或者临时表的方式。

现在有一个方案：每次插入一个临时表中，在该临时表中不使用日志，不建立索引。然后在后台进行数据的转储。用户得到反馈的时间将会很快。

---

这个帖子有点技术含量，关注。

---

测试结果：采用临时表的方式，插入10万条数据，只需要3~5秒。（包含日志，没建立索引）

---

你用SQl事件探查器不就知道Sql Server是怎么import/output的?

---

奉劝楼主不要用临时表
除非服务器的内存特别大 ，不然会报内存错误中断服务的。

---

我做的是一个把xml数据提取到数据库,数据也非常多,频繁的操作数据库,造成占用cpu100%,最后用双核的cpu就解决了.

---

关注学习中,门外汉,不懂!

---

mark

---

使用临时表不是指的内存中的表，而是一个起到中间过渡作用的表。表结构和实际表相同，但不采用事务。

用户送来的数据先插入的中间临时表中，因为临时表中的数据量不大，插入几乎可以在数S内完成，然后返回给用户信息。

设计一个后台线程负责处理临时表中的数据，假定只在晚间空闲时进行。把临时表中的数据通过insert....select的方式导入到实际表中。最后删除临时表中的数据。

---

我刚才查了一下DB2的资料，发现DB2中提供了一个DB2LOAD的存储过程，可以用来直接导入文本文件到数据库中。

这真的是一个值得研究的新的解决方案！

---

关注一下

---

如果是sqlserver，sybase建议使用bcp，分批次进去 效率很高

jdbc效率比较低。

---

这人疯了
我也学习一下
自己也疯一下吧

---

楼上的朋友，这是一个现实中遇到的问题，何疯之由？

---

关注

---

如果用户数据实时性不强的话,可以考虑以下方案:

用户操作后执行:
. 源数据导出到指定格式的文件(xml/txt/...)
. 上传数据文件
. 显示操作成功(用户有感知的部分完成)
----
. 后台定时执行(在访问量小的时间,比如后半夜):
. 从指定格式的文件导入数据,建立索引,....
====
以上仅为想法,未经实际测试.

---

谢谢楼上。你的想法基本上和我不谋而合。

惟一的不同在于，在上传完毕后，还需要反馈数据中有错的部分。

---