基于ORACLE的数据库性能优化设计

文/黄杰生

影响数据库性能的包括逻辑因素和物理因素，无论哪方面出现问题，都会导致数据库性能降低。为确保数据库能提供快速稳定的数据存储和应用服务，必须重视数据库的性能优化。在数据库设计阶段同步考虑数据库性能问题，对数据库的逻辑架构与物理架构进行合理优化设计，将对数据库性能的增强具有重要作用。

1　ORACLE数据库

ORACLE是一种优秀先进的关系型数据库，具有强大的数据存储及管理功能，具有很高的市场占有率 。ORACLE主要使用SQL语言作为数据管理与应用开发的接口，系统可在Unix、Windows多平台上运行，具有功能强大、稳定性好、兼容性强、维护效率高等优点。但由于ORACLE十分庞大和复杂，如果在数据库设计和使用的时候不合理，都会导致严重的性能问题。

2　基于ORACLE的数据库性能优化设计方法与效果

ORACLE数据库架构主要包括逻辑架构与物理架构，分别面向两种架构的若干主要性能优化设计方法如下：

2.1　逻辑架构优化

2.1.1数据库表设计

设计库表前应尝试预估存放的数据量，并分配足够大的初始扩展区(INITIAL EXTENTS)来存放全表。但如果需要使用并行查询机制，则应该使用多个数据文件分布存放全表数据，并注意使每次分配的扩展区数目与并行度相等。对于超过1G大小或超过1千万条数据的库表可设计为分区表(partitioned table)。

考虑创建多个表空间，每个表空间用于存放不同类型或大小的数据表，实现表空间负载的合理均衡利用。尽量保证INITIAL和NEXT扩展区的大小是相同大小的单元的整数倍，比如128K的整数倍，这样可以保持扩展区的统一大小，而且不会造成表空间碎片。表空间碎片会导致很多无法利用的小扩展区分散在表空间中，引起额外的性能开销。

2.1.2有效利用索引

正确使用索引可以使数据库性能得到大幅提高，因此根据不同场景需要合理建立并利用不同类型的索引，使数据访问性能最优。可参照如下指引使用索引：

（1）索引信息存放在一个单独的表空间，并确保该表空间的数据文件与索引对应的库表的表空间的数据文件不在同一磁盘上。

（2）对where条件语句中高频出现的字段建立索引，同时注意索引应建立在小字段上，对于大文本字段甚至超长字段，不要建立索引。

（3）当where条件中经常出现多个选择字段时，可选择区分度高的字段进行组合创建复合索引，但不宜超过3个字段。

（4）索引包含了库表所有字段时，可直接将表数据存放在索引块中，构成唯一索引表(index-only table)。

（5）被索引的列数据具有低基数(cardinality)时(即索引列具有相对较少的确定值)，通过创建位图索引(bitmap index)可大幅改善性能。

（6）若对某个表字段的操作全是固定函数操作，不是原始操作，可考虑建立函数索引。

（7）对于需要经常与其他库表进行连接的库表，在连接字段上应建立索引。

（8）通过定时任务定期重建索引，减少索引碎片。

2.1.3内存优化

对系统全局区(SGA)进行合理配置。系统全局区包括四个部分：数据缓冲区；共享池；重做日志缓冲区；大型共享池。其中数据缓冲区和共享池是SGA的两个最重要的优化区域。在系统内存足够时，增大参数DB_BLOCK_BUFFERS的值可以提高访问数据缓冲区的性能；增大参数SHARED_POOL_SIZE的值可以提高访问数据字典及共享SQL和PL/SQL语句的性能。

Oracle在执行类似建立索引和执行带子句的查询（例如Order By）的操作时，需要内存空间来对数据进行排序，合理配置SORT_AREA_RESERVED_SIZE和SORT_AREA_SIZE参数不仅能明显提高需要排序的查询性能，而且还能释放I/O带宽等系统资源，从而提高性能。

另外，为日志(redo log)缓冲区分配更多的内存，也可有效减少磁盘I/O，特别是对于事务特别长或事物数量较多的OLTP数据库，性能提升效果显著。

2.2　物理架构优化

2.2.1磁盘读写能力的优化

ORACLE存储与访问数据非常依赖磁盘访问，而磁盘访问是任何计算机系统上最慢的操作，是性能的最大瓶颈，可参照以下指引进行优化：

（1）尽量将输入/输出操作分离到不同的磁盘上，避免串行等待。例如回滚段和日志文件在同一磁盘上的话，会导致写完回滚记录再将磁盘磁头移动至日志文件存放的部分，非常耗时。

（2）把高输入/输出的磁盘放在不同的控制器上。单个控制器可以处理有限的并发操作，但应该尽可能利用更多的控制器提高并行度，消除等候时间。

（3）把操作最频繁的数据库对象(如日志文件、回滚段、索引表空间)放在速度最快的磁盘上。

（4）通过RAID-0+1技术实现ORACLE条带化，既可消除等待磁盘头定位的延迟，也可通过磁盘镜像实现数据保护，特别在多CPU并行查询的情况下，能充分发挥硬件潜力。

2.2.2基础运行环境优化

（1）应充分利用物理内存，但同时应避免过多交换(swapping)发生，因为将内存交换到磁盘的过程非常缓慢。对于系统全局区(SGA)的大小应合理控制，切勿占据过多物理内存，否则会引起过多SGA交换，会严重降低ORACLE性能。

（2）尽量用主干专用网络将服务器连接在一起，并尽量将服务器-服务器的通信与服务器-客户端的通信分离。

（3）尽量将ORACLE数据库部署在单独的机器上，与其他服务系统分离。

2.3　优化效果评价

为验证数据库性能的优化设计效果，本文以一张记录数200万、占用空间500M的单表为对象，对其进行不同方法优化后再对其执行相同查询语句的时间进行了对比观察，验证结果如下：

（1）未作任何优化时，执行时间为21.7s。

（2）索引建立后，执行时间为10.4s。（3）表结构优化后，执行时间为6.7s。（4）读写优化后，执行时间为4.0s。（5）综合优化后，执行时间下降到了2.4s。

3　结论

综上所述，本文以ORACLE数据库为研究对象，从数据库表设计、索引利用、内存配置、磁盘读写能力、基础环境等方面进行了性能优化设计，使查询执行时间得到了明显的降低，数据库性能得到明显提升，证实了优化方法的有效性，为数据库管理与应用工作提供了指引。