海量数据查询优化

 

摘　要：对海量数据进行访问查询时，常常遇到系统响应时间过长，占用系统资源过多的问题。本文结合实例着重对Oracle数据库中的查询优化进行了研究，测试结果表明采用的方法是很有效的，大大缩短了测试用例表的响应时间，最后对海量数据的优化方法提出了实用性的建议。
　　
　　关键词：海量数据；Oracle数据库；查询优化；数据查询

1　引　言
　　在实际业务中，当表中有海量数据时，如何对其进行快速访问，提高系统响应时间就显得十分重要。在实际应用中，往往采用各种优化措施，使得SQL查询经过数据库优化器的处理，得到最佳的执行计划，即数据访问路径，来达到提高响应速度的目的。由于项目采用的是Oracle数据库，以下考虑对Oracle数据库进行的优化情况。
　　
    2　Oracle查询顺序及其调整
　　Oracle优化的一般顺序如下：一、环境调整（服务器、网络、磁盘）； 二、Oracle实例调整； 三、Oracle对象调整；  四、Oracle SQL调整。
      2．1　实例调整
　　首先进行Oracle实例调整。Oracle实例涉及到SGA内存区和一组Oracle后台处理进程。对Oracle实例的调整就是对SGA内存区和Oracle后台处理进程的调整。在对该问题的解决中，主要是针对SGA内存区的调整。
　　
　　2．1．1　SGA内存区结构
　　SGA就是系统全局区，是指内存中允许多个进程相互通信的区域。在Oracle中，SGA对所有进程来说都是全局的可用的。图1为SGA结构图。
　　缓冲区高速缓存是SGA中为所有用户和系统进程保存数据的区域，任何数据在传递给一个调用的应域是共享的，所以多个进程可以从这片高速缓存读取同样的数据块，而不必每次都从物理磁盘中读取。
　　共享池是SGA中的另一个区域，其中保存着关于待执行的SQL语句的信息。他由两部分组成：数据字典高速缓存，存放从数据字典中读取的信息以用于处理SQL请求；库高速缓存，存放需要执行的SQL语句信息，包括每个SQL语句的语法分析树和执行计划。如果多个用户要执行同样的SQL语句，那么语法分析树和执行计划就可以重复利用，省去了语法分析步骤的昂贵花费。
　 　
　　2．1．2　调整SGA结构
　　一般来讲，在系统硬件支持的情况下，系统全局区越大越有利于数据库高效的运行。大的缓冲区高速缓存可以缓存更多的数据块，这样可以提高缓存命中率，节省物理磁盘读取的高昂代价；大的共享池意味着大的库高速缓存。库缓存的内存结构如图2所示。
         
　　库缓存越大，可以保存的SQL语法分析信息越多；此外，数据库中的一些对象，如表、索引、过程、触发器、软件包等也在首次执行后进驻库高速缓存。大的库缓存可以保证对这些对象的高命中率，从而节省解析和载入代价。
　　作为一个通用的优化原则，我们在解决该问题时，适当增大了SGA的容量，从而保证Oracle实例可以比较高效的运行。设置缓冲区高速缓存的容量为32 M，设置共享池的大小为56 M。运行rpt＿lib．sql程序检查库高速缓存不足率（还没有运行该脚本），表明库缓存足够。

2．2　对象调整
　　这一步骤我们要对每一个Oracle对象进行调整从 而优化性能，包括对所有的存储参数进行正确的设置，尤其是对影响输入输出的参数进行设置。Pctfree，pctused，freelist参数的设置都会对SQL性能产生重要影响。
　　在解决该问题时，我们没有对对象的存储参数进行调整。对象的存储参数的调整要建立在具体对象的基础之上，我们使用的测试大表除了在容量上模拟可能的实际对象之外，和实际对象并没有多少可比之处。举例来说，pctfree参数是指在实际的存储块中，留出来为块中的记录扩展所用的空闲空间占该存储块容量的百分比，这个参数和实际表记录的长度及可扩展性有关，要在设计出实际表之后进行调整。因此我们对对象的存储参数使用了数据库的缺省设置。数据库的对象还包括索引、过程、包等，对对象的调整要包括对他们的调整，这里一并略去。

2．3　SQL语句调整
　　SQL语言是一种灵活的语言，相同的功能可以使用不同的语句来实现，但是语句的执行效率是很不相同，一般考虑如下规则：
　　（1）添加索引
　　在对大表进行扫描时，首先要避免不必要的全表扫描。最通常的做法就是给大表添加索引。所谓全表扫描，就是在访问表时，从磁盘上存储该表的起始位置开始逐记录读数据，直到该表的结束位置。给大表添加索引后，我们可以通过访问索引的方式获得记录的物理位置，从而达到访问表的目的。设想一下，对于一个拥有大量字段的表，如果只需要返回其中少量字段，那么在这些字段上建立索引，通过索引访问获得记录，将大大降低物理磁盘读写次数，从而降低了整个查询响应时间（事实上，这种情况下根本没有必要访问数据表，只是访问索引就足够了）。在我们的测试用表中，只有4个字段，显然无法发挥索引的这一优势。我们的测试用表的特点是记录条数多，达到50万条，但每次返回的记录数只有几十条，在这种情况下，使用索引同样可以起到好的效果。首先在检索条件上建立索引，在表访问时，我们通过索引来获取目的记录集的物理地址，因为返回的记录数不多，所以这种方式造成的物理读写很少，应该可以获得较满意的访问时间。在测试中，我们对测试用表的条件字段添加索引，但事实上并没有提高查询的响应时间，这是因为在SQL语句的执行计划中，在添加索引之前和之后，Oracle生成的执行计划中，表访问方式都是全表扫描，根本没有使用我们添加的索引。那么此时我们可以采用添加提示的方法。
 1．合理使用索引
　　     索引是数据库中重要的数据结构，它的根本目的就是为了提高查询效率。现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。索引的使用要恰到好处，其使用原则如下：
  ●在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引。
　　  ●在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引。
　　  ●在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。
  ●如果待排序的列有多个，可以在这些列上建立复合索引（compound index）。
  ●检查可疑的索引。在一些数据库服务器上，索引可能失效或者因为频繁操作而使得读取效率降低，如果一个使用索引的查询不明不白地慢下来，可以试着用tbcheck工具检查索引的完整性，必要时进行修复。另外，当数据库表更新大量数据后，删除并重建索引可以提高查询速度。
 2．避免或简化排序
　　           应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时，优化器就避免了排序的步骤。以下是一些影响因素：
　　  ●索引中不包括一个或几个待排序的列；
　　  ●group by或order by子句中列的次序与索引的次序不一样；
　　  ●排序的列来自不同的表。
　　  为了避免不必要的排序，就要正确地增建索引，合理地合并数据库表（尽管有时可能影响表的规范化，但相对于效率的提高是值得的）。如果排序不可避免，那么应当试图简化它，如缩小排序的列的范围等。

　　（2）添加提示
　　在Oracle中，我们可以在SQL语句中加入提示，来影响Oracle对优化模式的选择，从而生成最优的执行计划。于是我们在SQL语句中加入提示，强制优化器在生成执行计划时将表的访问方式从全表扫描改为索引范围扫描。结果该查询花费了比全表扫描多得多的响应时间。仔细查看这个SQL语句的执行计划，我们发现Oracle对这个索引进行了全索引扫描，这样造成的物理读写数量比起全表扫描不仅没有减少，反而大幅上升。寻找原因，问题出在我们索引的字段上。我们索引的AGE字段为了测试方便，每条记录的值都不相同，这样的字段其实并不具备建立索引的条件。在该测试中，我们首先要将索引载入内存，由于索引各不相同，Oracle采取了全索引扫描的方式访问索引，我们要读取的是30万条记录（AGE＞200 k），通过索引，我们获得的将是30万个记录的ROWID，如果这些记录在磁盘上的物理位置没有按索引顺序排序，那么我们就不得不通过30万个ROWID来访问这些记录，这样造成的物理读写是相当惊人的，这就是为什么我们使用了索引，却反而查询速度更慢的原因所在。事实上Oracle的优化器选择全表访问是已经对不同的执行计划作过比较的了。同时，这里同样违背了一个Oracle优化的准则，对于没有按索引顺序排序的表，如果检索记录数小于总记录数的7％，用索引比全表扫描快。
　　（3）更改优化器模式
　　优化器是Oracle数据库中接受来自产生器处理过的SQL语句的程序。他将对SQL进行优化，生成内部执行计划，这个计划是Oracle存取物理磁盘数据的路径。根据优化器模式的不同，生成的内部执行计划也不同，而对于相同的SQL，永远只有一个最优的执行计划，因此选择合适的优化器模式，是很重要的。Oracle有2种优化器模式：基于规则的优化模式和基于成本的优化模式。在这次测试中，我们使用的优化器模式参数为CHOOSE，这样Oracle将根据SQL语句相关表索引的有无，统计资料的有无以及SQL语句中的提示，自动判断使用哪种优化模式。
　　（4）将调整持久化
　　Oracle的执行计划是根据各种情况，比如表的统计资料变化的，但有时这种变化是我们不希望的。为了将我们已经调整好的SQL执行计划固定，我们可以 用Oracle的工具将执行计划持久化存储。
　　（5）修改sql，通过修改sql，提高SQL的执行效率
 1、消除对大型表行数据的顺序存取
　　  在嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存取策略，一个嵌套3层的查询，如果每层都查询1000行，那么这个查询就要查询10亿行数据。避免这种情况的主要方法就是对连接的列进行索引。例如，两个表：学生表（学号、姓名、年龄……）和选课表（学号、课程号、成绩）。如果两个表要做连接，就要在“学号”这个连接字段上建立索引。
　　  还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的where子句强迫优化器使用顺序存取。下面的查询将强迫对orders表执行顺序操作：SELECT ＊ FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008
　　  虽然在customer_num和order_num上建有索引，但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合，所以应该改为如下语句：SELECT ＊ FROM orders WHERE customer_num=104 AND order_num>1001
　　   UNION
　　 SELECT ＊ FROM orders WHERE order_num=1008
　　这样就能利用索引路径处理查询。
 2、避免相关子查询
　　 一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。
 3、使用临时表和中间表
   数据量增加时，处理中要考虑提前汇总。这样做的目的是化整为零，大表变小表，分块处理完成后，再利用一定的规则进行合并，处理过程中的临时表的使用和中间结果的保存都非常重要，如果对于超海量的数据，大表处理不了，只能拆分为多个小表。如果处理过程中需要多步汇总操作，可按汇总步骤一步步来，不要一条语句完成，一口气吃掉一个胖子。

 4、用排序来取代非顺序存取
　　  非顺序磁盘存取是最慢的操作，表现在磁盘存取臂的来回移动。SQL语句隐藏了这一情况，使得在写应用程序时很容易写出要求存取大量非顺序页的查询。
　　  有些时候，用数据库的排序能力来替代非顺序的存取能改进查询。

2．4　库表调整、分区操作
      为了提高查询修改速度，设计较为灵活的库表划分策略，如手机收费系统，可按照月、区号等等进行建表。减少表的数据量。
      对海量数据进行分区操作对海量数据进行分区操作十分必要，例如针对按年份存取的数据，我们可以按年进行分区，不同的数据库有不同的分区方式，不过处理机制大体相同。例如SQL Server的数据库分区是将不同的数据存于不同的文件组下，而不同的文件组存于不同的磁盘分区下，这样将数据分散开，减小磁盘I/O，减小了系统负荷，而且还可以将日志，索引等放于不同的分区下。

　　2．5　调整结果
　　通过对这个测试用例的调整，我们最终将此大表的响应时间从5 s缩短到不足2 s。经过调整后的缓冲区高速缓存达到113 M，大约占物理内存的50％，调整后的SQL语句为：
　　SELECT name，age，memo FROM（SELECTname，age，memo，rownum ASmynum FROM test2）WHEREmynum＞400000 and mynum＜＝400010；
　　我们测试所用的机器配置为CELETRON500 M，内存256 M，硬盘为IDE8．4 G。可以说是运行Oracle8I的最低配置。启动Oracle 8I数据库后，内存使用达到300 M以上，也就是说已经在用虚拟内存；测试开始后，CPU保持或接近满载，这些都会影响Oracle的性能表现，也会影响测试结果。
　　
　　3　结　语
　　以下几点可以作为通用的Oracle SQL调整原则：
　　（1）消除不必要的全表扫描，可以通过添加索引达到。
　　（2）缓存小型表的全表扫描，可以通过将小型表置入缓冲区高速缓存的KEEP池中实现。
　　（3）如果表有多个索引，要保证Oracle正在使用对此SQL最优化的索引，可以通过添加提示实现。
　　（4）用优化的连接技术，可以通

 

参考文献：
http://oracle.chinaitlab.com/optimize/806525.html
http://www.bitscn.com/pdb/oracle/200604/19456.html
http://www.5kkm.com/post/author/admin/page/21   这个很全很强大
http://www.examw.com/Oracle/zhonghe/100689/