Oracle数据库中SQL语句优化的研究.docVIP

Oracle数据库中SQL语句优化的研究 　　摘 要： 简要介绍数据库中SQL语句优化技术，并结合具体的事例，探讨基于Oracle数据库的SQL语句优化。 　　关键词： Oracle数据库；SQL语句；优化 　　中图分类号：TP393 文献标识码：A 文章编号：1671－7597（2011）1020111－01 　　 　　0 引言 　　Oracle数据库是当前应用最广泛的大型数据库管理系统，它在各个领域的使用不断增长，其查询性能直接关系到系统的运行效率，对其查询优化方法的研究更具有现实意义。数据库SQL查询优化的一个基本原则就是：通过尽可能少的磁盘I/O访问获取所需要的数据。要评价查询优化性能，需要在数据库优化前后比较其评价指标：响应时间和吞吐量之间的权衡、数据库的命中率以及内存的使用效率，并以此来衡量优化的效果和指导优化的方向。优质的查询语句可以使应用系统的性能得到大大提高。拙劣的应用SQL语句、大小不合适的SGA内存结构、效率差的SQL语句、执行计划过度的文件I/O及访问数据库资源的紊乱等都会影响数据库系统的性能和执行效率。本文将从数据库设计的角度及SQL语句优化技术的两方面进行详细地阐述如何提高数据库的性能和执行效率。 　　1 数据库设计的优化 　　数据库设计是应用系统设计的基础，其性能直接影响应用系统的性能。数据库性能包括查询响应时间和吞吐量两个方面。数据库设计优化的主要目标就是减少磁盘I/O、减少CPU利用率和资源竞争，降低查询响应时间或提高系统吞吐量。 　　1.1 磁盘I/O优化 　　磁盘I/O是影响Oracle数据库性能的瓶颈，而影响磁盘I/O性能的主要原因有磁盘竞争、I/O。资源竞争增强时，响应时间增长读取次数和数据块空间的分配管理不当等。Oracle进程经常需要访问数据文件和重做日志文件，当这两种类型的文件位于同一磁盘中，就会造成磁盘竞争。将表空间所属的数据文件存储在多个不同的物理磁盘上，使系统I/O趋于平衡；把数据、日志、索引放到不同的I/O设备上，可以减少磁盘竞争和I/O读取次数。提高I/O设备的并发访问率，可以有效提高SQL语句的执行效率。当竞争增强的时候，系统响应时间将增长。 　　1.2 内存优化调整 　　要充分发挥Oracle据库的优势，必须对数据库的各项初始化参数进行合理配置。从Oracle数据库内存优化管理的角度出发，针对影响其性能的因素及其对应的参数，分别从数据缓冲区优化、共享池优化、重做日志缓冲区优化几个方面完成内存优化配置。 　　1.3 优化全表扫描操作 　　一次I/O能读取多块数据块（由db_block_multiblock_read_count参数设定），这极大地减少I/O总次数，提高了系统的吞吐量，所以利用多块读取的方法可以高效地实现全表扫描。 　　2 SQL语句优化技术 　　2.1 基于索引的优化 　　索引是影响SQL语句性能的一个重要因素，在表上建立合适的索引，可以避免全表扫描并减少I/O开销，提高数据查询速度。但是创建索引会增加系统时间和空间的开销，创建索引时必须要与实现应用系统的查询需求密切结合，才能达到优化查询的目的。索引使用的一些原则： 　　1）当选择数据少于全表的20%，并且表的大小超过ORACLE的5个数据块时，使用索引才会有效，否则用于索引的I/O加上用于数据的I/O就会大于做一次全表扫描的I/O，反而会降低查询效率； 　　2）由于表和索引的数据块通常是被同时读取的，所以应该尽量将表和其相关联的索引放置在不同的磁盘上，减少I/O冲突； 　　3）索引的创建也是需要代价的，对于DML操作，每个索引都要进行相应的删除、更新、插入操作。从而导致DML操作的效率变低。因此定期的重构索引是有必要的，alter indexrebuild； 　　4）避免隐式转换和不必要的类型转换。例如：to_char（），to_num 　　ber（），to_date（）等会导致该字段的索引失效，从而对该表进行全表扫描， 对一个百万数量级的表进行全表扫描的会对系统带来严重的性能隐患； 　　2.2 基于Oracle 数据库对表访问的优化 　　2.2.1 全表扫描 　　全表扫描就是对表中每条记录进行顺序的访问。Oracle采用一次读入多个数据块（database block）的方式对全表扫描进行优化。 　　2.2.2 选择最有效率的表名顺序 　　Oracle的解析器按照从右到左的顺序对FROM子句中的表名进行处理，FROM子句中写在最后的表将最先被处理，当FROM子句中包含多个表时，必须选择记录条数最少的表作为基础表。如果有3个以上的表进行连接查询，那么，就需要选择交叉表作为基础表，交叉表就是被其他表所引用的那个表。 　　2.2.3 通过ROWI