Oracle千万条记录插入与查询小结

　　最近做了个项目，实现对存在千万条记录的库表进行插入、查询操作。原以为对数据库的插入、查询是件很容易的事，可不知当数据达到百万甚至千万条级别的时候，这一切似乎变得相当困难。几经折腾，总算完成了任务。在此做些简单的小结，不足之处，还望javaeye的高手们帮忙补充补充！

1、 避免使用Hibernate框架
　　Hibernate用起来虽然方便，但对于海量数据的操作显得力不从心。
　　关于插入：
　　试过用Hibernate一次性进行5万条左右数据的插入，若ID使用sequence方式生成，Hibernate将分5万次从数据库取得5万个sequence，构造成相应对象后，再分五万次将数据保存到数据库。花了我十分钟时间。主要的时间不是花在插入上，而是花在5万次从数据库取sequence上，弄得我相当郁闷。虽然后来把ID生成方式改成increase解决了问题，但还是对那十分钟的等待心有余悸。
　　关于查询：
　　Hibernate对数据库查询的主要思想还是面向对象的，这将使许多我们不需要查询的数据占用了大量的系统资源（包括数据库资源和本地资源）。由于对Hibernate的偏爱，本着不抛弃、不放弃的作风，做了包括配SQL，改进SQL等等的相当多的尝试，可都以失败告终，不得不忍痛割爱了。

2、 写查询语句时，要把查询的字段一一列出
　　查询时不要使用类似select * from x_table的语句，要尽量使用select id,name from x_table，以避免查询出不需要的数据浪费资源。对于海量数据而言，一个字段所占用的资源和查询时间是相当可观的。

3、 减少不必要的查询条件
　　当我们在做查询时，常常是前台提交一个查询表单到后台，后台解析这个表单，而后进行查询操作。在我们解析表单时，为了方便起见，常常喜欢将一些不需要查询的条件用永真的条件来代替（如：select count(id) from x_table where name like ‘%’），其实这样的SQL对资源的浪费是相当可怕的。我试过对于同样的近一千万条记录的查询来说，使用select count(id) from x_table 进行表查询需要11秒，而使用select count(id) from x_table where name like ‘%’却花了33秒。

4、 避免在查询时使用表连接
　　在做海量数据查询时，应尽量避免表连接（特别是左、右连接），万不得已要进行表连接时，被连接的另一张表数据量一定不能太大，若连接的另一张表也是数万条的话，那估计可以考虑重新设计库表了，因为那需要等待的时间决不是正常用户所能忍受的。

5、 嵌套查询时，尽可能地在第一次select就把查询范围缩到最小
　　在有多个select嵌套查询的时候，应尽量在最内层就把所要查询的范围缩到最小，能分页的先分页。很多时候，就是这样简单地把分页放到内层查询里，对查询效率来说能形成质的变化。

　　就是这些了，希望对遇到类似问题的朋友们能有所帮助！

评论

20 楼 仰望星空的狼 2009-09-17  

吐血了  发个言这么多限制  不过这样倒保证了论坛质量

东西讲的很细  学习了

19 楼 zhuzhigang_skcc 2009-04-07  

楼主，你说的很好，我做网站开发的时候，考虑了以上各位的一些意见，但今天在这里听大家这么一说，我感觉我以前做的网站还是没有将系统做到最优化！

18 楼 liujunsong 2009-04-05  

给点自己的意见:
step1:写一个简单的代码,来读取原始信息,然后每分钟将数据写到一个指定目录下,格式自己定义好,简单为上.文件名按照时间递增.一分钟一个,或者5分钟一个.
step2:写一个转换类,把上面谈的文件一个一个转成数据库可以直接导入的格式,这一步可省略.
step3:写第三个类,按照时间判断文件,把这些文件用oracle命令直接导进去.

17 楼 sunshing 2009-04-04  

虽然还没有到达这种程度，但还是感谢下，长见识了！

16 楼 shiyiwan 2009-04-03  

第一条我不懂
第二，三条是PL/SQL程序员的常识
第四条避免表连接是不现实的
比较好的方式是，（用个例子说明）
TABLE A，B；其中A数据量 > B
则在sql中应该写成select column from A a, B b
关于条件的顺序：
与两表连接的条件和大表index column有关的条件紧跟from；
然后是大表中的条件，
然后是小表中的条件，
避免使用not exist，
若不行则用exist代替，
避免使用exist，
若不行则用in代替，
写法顺序依次是not exist,exist,not in,in,like
能够一次减少大量数据的条件则写在最后，比如b.id = 001之类。

若有三个表A,B,C，其中表C是关系表，起连接表A，表B作用时，应将表C放在最后，写成selct column from A a, B b, C c；
关于C与其他表关系的连接条件写在最前。也即让它最后执行。

第五条，
嵌套最多不要超过三层，一般两层，否则请重新设计。

15 楼 jwnest 2009-04-03  

关于第一条，其实不是hibernate的问题，既然你已经知道主要问题是从数据库取sequence， 那你可以自己写个取sequence的方法，一次取5万个出来，然后再hibernate配置中指定使用你自定义的这个方法，在我们之前项目里面就是这么做的，存几万条数据很快的。
第二和第三条和db没什么关系，纯粹是你们系统的设计问题；
最后两个的确应该在写sql的时候注意，但有时候如果的确无法避免大表关联查询的时候，可以好好设计一下数据表的索引，会对查询速度有很大帮助的，当然终极办法是写成SP，SP比较适合逻辑比较简单，但数据量很大的场合，尽量不要把复杂的业务逻辑放到sp里面。

14 楼 tiandeyu188 2009-04-03  

的确是这样的，在千万级的数据时，应该不使用持久化工具，数据应该直接从文件里面导入，批量入库的话我感觉30000条提交一次比较合适（ps:oracle 10g）

13 楼 cnriver 2009-04-02  

we are doing EMPI(Enterprise Master Patient Index),and also need to handle tens of millions of patient data from hospital. and i am finding informations about this part.thank you for this author.

12 楼 avanry 2009-04-02  

不用HIBERNATE那底层用什么好呢？

11 楼 elfmtian 2009-04-01  

受教了，现在就在大数据量的问题上发愁呢。尤其是并发性太高了而且数量大

10 楼 chenfengcn 2009-04-01  

liujunsong 写道

大数据量的导入,应该走文件直接导入了. 这个是数据库管理的常识.

数据是通过采集程序即时生成的，平均每秒100条左右，不可能直接导啊！

9 楼 liujunsong 2009-04-01  

大数据量的导入,应该走文件直接导入了.
这个是数据库管理的常识.

8 楼 littcai 2009-04-01  

没有更深入一点的吗？比如数据的批量入库，事务处理，日志处理，数据和索引分开...？

7 楼 zhuyx808 2009-04-01  

什么东西都是怕极端，5w条数据走一条一条插入就是极端，存储过程好吗？适当用很好，多了也是极端容易数据库死锁

6 楼 娴静女生 2009-03-31  

的确是有这方面的忧患啊，数据的执行效率太慢了，心急了，要是用在银行上都快把用户

给急死了，没准人家拿脑袋去撞ATM机

深感Hibernate的操作繁琐，效率低。。。

5 楼 il2 2009-03-31  

受教了，深有同感

4 楼 chenfengcn 2009-03-31  

lin_style 写道

分区表。。

分区表确实是一个不错的选择，但是有些操作不一定都能用分区表啊，如果就是要把所有记录都查出来，并做一些操作的时候怎么办呢？

3 楼 chenfengcn 2009-03-31  

metadmin 写道

我补充几点。以前做个银行分析系统，数据量是亿级别的。1，怎样造Java对象。有句话说得好：尽可能的少造对象。别说千万级，就是上万级都不要考虑造对象了。因为几个请求一并发，喀嚓，系统肯定完蛋。2，合理摆正系统设计的位置。大量数据操作，和少量数据操作一定是分开的。大量的数据操作，肯定不是ORM框架搞定的。绝对不能ORM，因为1，要少造对象；2，数据库资源合理利用。就像博主的例子：id分配就是一个好例子。3，合理利用数据库的分区、索引技术。4，有的时候可以考虑临时表之类的，尤其是大数据量。5，有人说非常大的数据量，一定要用存储过程。我存储过程不好，所以还是使用jdbc，哈哈效果非常好，所以除了上面几点注意外，还要注意：   1，控制好内存，让数据流起来，而不是全部读到内存再处理，而是边读取边处理；   2，合理利用内存，有的数据要缓存；   3，偶承认，这里技术含量还是有的，反正不简单。不好言传。   -----------------------权限管理圈子欢迎您加入：http://accessmanager.group.iteye.com/

恩，记下了，目前还没用，希望将来用得上，呵呵

2 楼 lin_style 2009-03-31  

分区表。。

1 楼 metadmin 2009-03-31  

我补充几点。以前做个银行分析系统，数据量是亿级别的。

1，怎样造Java对象。有句话说得好：尽可能的少造对象。别说千万级，就是上万级都不要考虑造对象了。因为几个请求一并发，喀嚓，系统肯定完蛋。
2，合理摆正系统设计的位置。大量数据操作，和少量数据操作一定是分开的。大量的数据操作，肯定不是ORM框架搞定的。绝对不能ORM，因为1，要少造对象；2，数据库资源合理利用。就像博主的例子：id分配就是一个好例子。
3，合理利用数据库的分区、索引技术。
4，有的时候可以考虑临时表之类的，尤其是大数据量。
5，有人说非常大的数据量，一定要用存储过程。我存储过程不好，所以还是使用jdbc，哈哈效果非常好，所以除了上面几点注意外，还要注意：
   1，控制好内存，让数据流起来，而不是全部读到内存再处理，而是边读取边处理；
   2，合理利用内存，有的数据要缓存；
   3，偶承认，这里技术含量还是有的，反正不简单。不好言传。
  

-----------------------
权限管理圈子欢迎您加入：
http://accessmanager.group.iteye.com/