基于Oracle数据库大数据的检索优化分析与设计

杨莹 辽宁锦州渤海大学信息科学与技术学院

基于Oracle数据库大数据的检索优化分析与设计

杨莹 辽宁锦州渤海大学信息科学与技术学院

文中基于Oracle数据库采用各种检索优化方法来提高数据库的响应速度：合理设计表字段存储结构、合理建立索引技术、表分区技术、优化SQL检索语句等技术手段实现数据库的检索优化。本文研究关于如何优化数据库检索性能优化方案，通过分析影响检索优化因素提升系统性能，确定本文检索优化方案的目标，期望通过本文检索优化方案达到最优化，另外，基于分页显示检索到的全部查询结果在某种程度上是为了提高检索速度。

Oracle数据库 大数据 检索优化 表分区技术 SQL优化 索引技术

1 引言

目前，随着Oracle数据库不断应用深入大型应用系统的基础架构中，作为软件系统的运行的骨架，用户的访问量指数上升，数据库需要同时高并发处理用户请求瞬间达到极限，数据库数据处理性能迅速下降，在大型数据库中，用户请求检索的等待时间过长、占用系统CPU资源过多是当前系统面临最大的问题问题。因此，对大型数据查询需求有必要进行检索优化操作。根据本文研究需要优化系统的检索性能，在满足用户使用的前提下，对其检索性能的优化操作，使数据库的运行处于最优状态。

对硬件的改进也是提高检索速度的有效方法，例如：更换或升级性能更优秀的服务器，但是本文中仅对软件编程方面实现的检索优化研究。以Oracle研究对象，主要采取使用建立索引、表分区、优化SQL语句等技术手段以系统的提高检索速度，从而达到实现优化数据库的逻辑结构。

2 表建立索引技术

合理建立索引是为了达到快速检索数据，索引建立在数据表的非空列上，当某列建立索引之后，每次对该索引列发生修改操作之后需要根据索引值进行重新排序，目的是为了以后方便对该列进行检索数据操作。因此，在建立索引结构时需要把某个索引列设置在经常进行查询操作的列，并不是经常实现动态修改列之上。关于大型数据表建立索引结构需要注意:

(1)建立索引目的方便数据检索，但是要时刻维护索引列的代价；

(2)为了检索数据表中唯一的一行数据，不可避免的扫描整个数据表；

(3)由于多个表中有可能存在错误的索引列，最终导致多表联查检索发生等待时间过长的现象。

3 表的分区技术

Oracle数据库中为了达到高效的检索速度，建立表的分区技术是针对具有大数据的应用系统而言，大型数据表进行检索优化是一种多种优化方案中选择最佳的行为，理论上是将一个完整大型数据表实现物理上分区，但是，从逻辑结构上依然是完整的大型数据表，系统管理员或者用户看不出其中有什么变化，数据库系统的设计人员需要知道数据存储位置即可，只要程序设计人员从逻辑上将其当成完整的大型数据表即可。为数据库系统增加了可用性与灵活性。

3.1 实现表的分区方法

Oracle数据库实现数据表的分区方法主要有多种:一是按照表存储字段值范围实现表分区方法；二是按照表存储字段的HASH函数值实现表的HASH分区方法；三是按照字段的数据范围进行表分区划分，必要说明的是按照数据范围实现的分区划分需要有限的值范围，再根据HASH函数值实现二次分区方法；四是在Oracle数据库中增加了按找表存储字段列值实现分区方法。

在本文的数据库表分区实验中，针对马路上车辆行走记录表中的过车时间存储字段实现表的分区方法，每月的行车记录数据实现一个分区表，如果按照某个行车时间进行检索数据时，会大大增加数据库系统的检索速度。本文的数据库表分区实验中，行车记录分区表的SQL语句如下:

3.2 表实现分区的优点

通过对大型数据表实行分区之后，可以获得下面的几点有点如下:

(1)表的操作性增强:如果大型数据表的某个分区出现问题，数据表的其他分区依然可用。

(2)维护表的分区方便:如果数据表的某个分区出现问题，需要维护表的分区数据，只修改出现问题的分区即可。

(3)均衡数据库系统的I/O操作:可以根据表的不同分区映射到低速磁盘上以平衡数据库I/O操作，以改善整个数据库系统的检索性能。

(4)改善检索性能:对表的分区对象的检索行为可以实现仅对检索本身属于该表分区的一些数据，在部分数据中实现检索操作，不但提高检索速度，而且检索数据精准。

当表分区所执行的事务操作时，系统会指定了大型数据库的某个分区，大型数据库系统在执行特定事务时则只检索该分区的全部数据，大大地提高了数据检索的速度。不需要指定数据表的某个分区检索操作也可实现。

4 优化SQL语句技术

4.1 优化应用系统的SQL语句技术

在数据库系统中实现数据检索行为，程序员可以编写多种可执行的SQL语句实现对需求数据进行检索，因此，优化SQL执行语句就是为了提高数据的检索效率，把低效的SQL语句替换为可以实现高效执行、检索的SQL语句。优化SQL语句的目的主要是进行数据检索等价替换操作，主要是从数据库数据检索语句将用户提交所执行的SQL检索语句替换为高效的方式。优化SQL语句的一些方法:

（1）尽量为表数据结构建立索引；

（2）选择在多种数据表联合检索数据的次序；

（3）数据检索过程中，要尽量子查询时慎重使用IN或者NOT IN关键字，当大数据检索过程中，当使用where NOT exists检索效果要比其他语句好；

（4）非特别的情况下禁止使用视图的实现关联检索数据，尤其非常复杂的视图关系的管理检索；

（5）尽量避免在SQL执行语句中使用PL/SQL函数调用；

（6）避免对大数据库实现远程数据表的访问；

（7）在where子句中包含索引列时，尽量使用UNION代替OR关键字；

（8）优化GROUP BY关键字，将不需要排序的列在GROUP BY之前去除；

优化Sql语句的方法有多种，在这里只是重点强调优化Sql语句的使用方法，对于具体的检索优化方法还需要根据实际情况而定，在此不再具体赘述。

4.2 优化数据库的SQL语句

在数据库系统中实现数据检索功能时，往往通过本文检索优化方法达到一定的效果，但是，在大数据的检索过程中，程序员往往使用分页检索技术来提高系统处理的检索速度。分页检索的技术原理就是通过先执行SQL语句后过滤掉大部分检索数据，只将满足需求的部分数据检索出n(一般情况下n值不会超出20条)条数据，如果用户对已经检索的部分数据不够满意，则需要检索其他的后n条数据达到检索数据的目的，通常在应用系统中有“首页”、“上一页”、“下一页”、“尾页”等按钮，可点击“下一页”按钮方可检索后n条数据而不必将全部的数据调入到内存中。

5 本文结论与未来研究方向

针对本文所研究的数据检索优化内容，下面专门做了一个关于数据检索与优化的测试，根据测试优化结果进行分析，本文的测试数据接近上百万条数据量。当应用系统的检索功能未实现优化之前，在同样的数据库表的数据内容，检索时间大致为6到8秒，通过本文研究的数据检索与优化方法实现的应用系统优化之后，检索速度比之前缩减到几十毫秒即可实现相同的检索内容，数据库系统的检索速度瞬间提高了数十倍的效果。表1是实现对数据表建立索引测试分析情况，表1数据表中数十万数据量的有无索引测试分析表。

表1 数据表中数十万数据量的有无索引测试分析表

当将表1的数据检索过程使用分页技术处理时，会在表中建立索引的情形下的检索时间都减少1毫秒左右，这样可以大大的提高数据库系统的检索速度，增加用户体验。

本文中主要是从数据库与应用系统对检索优化技术的重点论述，例如：如何建立表的索引技术、实现表的分区技术、优化SQL执行语句技术以及在大型数据表中的数据检索分页技术等等。另外，当应用系统的软件部分全部实现检索优化操作之后，并没有其他的优化之处，还可以通升级硬件配置来提高数据库系统的检索速度。

[1]周彦,陈梅,王翰虎,等．基于层次位图连接索引的数据仓库查询优化［J］．计算机技术与发展,2011,21(3):41－43

[2]王君,祝永志,魏榕晖,等．基于Oracle分布式数据库的查询优化［J］．计算机技术与发展,2008,18(1):157－160

[3]杨小艳,尹明,戴学丰．Oracle数据库查询优化方法研究［J］．计算机与现代化,2008(4):4－7