基于ORACLE的數(shù)據(jù)庫(kù)性能優(yōu)化設(shè)計(jì)

文/黃杰生

影響數(shù)據(jù)庫(kù)性能的包括邏輯因素和物理因素，無(wú)論哪方面出現(xiàn)問(wèn)題，都會(huì)導(dǎo)致數(shù)據(jù)庫(kù)性能降低。為確保數(shù)據(jù)庫(kù)能提供快速穩(wěn)定的數(shù)據(jù)存儲(chǔ)和應(yīng)用服務(wù)，必須重視數(shù)據(jù)庫(kù)的性能優(yōu)化。在數(shù)據(jù)庫(kù)設(shè)計(jì)階段同步考慮數(shù)據(jù)庫(kù)性能問(wèn)題，對(duì)數(shù)據(jù)庫(kù)的邏輯架構(gòu)與物理架構(gòu)進(jìn)行合理優(yōu)化設(shè)計(jì)，將對(duì)數(shù)據(jù)庫(kù)性能的增強(qiáng)具有重要作用。

1　ORACLE數(shù)據(jù)庫(kù)

ORACLE是一種優(yōu)秀先進(jìn)的關(guān)系型數(shù)據(jù)庫(kù)，具有強(qiáng)大的數(shù)據(jù)存儲(chǔ)及管理功能，具有很高的市場(chǎng)占有率 。ORACLE主要使用SQL語(yǔ)言作為數(shù)據(jù)管理與應(yīng)用開(kāi)發(fā)的接口，系統(tǒng)可在Unix、Windows多平臺(tái)上運(yùn)行，具有功能強(qiáng)大、穩(wěn)定性好、兼容性強(qiáng)、維護(hù)效率高等優(yōu)點(diǎn)。但由于ORACLE十分龐大和復(fù)雜，如果在數(shù)據(jù)庫(kù)設(shè)計(jì)和使用的時(shí)候不合理，都會(huì)導(dǎo)致嚴(yán)重的性能問(wèn)題。

2　基于ORACLE的數(shù)據(jù)庫(kù)性能優(yōu)化設(shè)計(jì)方法與效果

ORACLE數(shù)據(jù)庫(kù)架構(gòu)主要包括邏輯架構(gòu)與物理架構(gòu)，分別面向兩種架構(gòu)的若干主要性能優(yōu)化設(shè)計(jì)方法如下：

2.1　邏輯架構(gòu)優(yōu)化

2.1.1數(shù)據(jù)庫(kù)表設(shè)計(jì)

設(shè)計(jì)庫(kù)表前應(yīng)嘗試預(yù)估存放的數(shù)據(jù)量，并分配足夠大的初始擴(kuò)展區(qū)(INITIAL EXTENTS)來(lái)存放全表。但如果需要使用并行查詢(xún)機(jī)制，則應(yīng)該使用多個(gè)數(shù)據(jù)文件分布存放全表數(shù)據(jù)，并注意使每次分配的擴(kuò)展區(qū)數(shù)目與并行度相等。對(duì)于超過(guò)1G大小或超過(guò)1千萬(wàn)條數(shù)據(jù)的庫(kù)表可設(shè)計(jì)為分區(qū)表(partitioned table)。

考慮創(chuàng)建多個(gè)表空間，每個(gè)表空間用于存放不同類(lèi)型或大小的數(shù)據(jù)表，實(shí)現(xiàn)表空間負(fù)載的合理均衡利用。盡量保證INITIAL和NEXT擴(kuò)展區(qū)的大小是相同大小的單元的整數(shù)倍，比如128K的整數(shù)倍，這樣可以保持?jǐn)U展區(qū)的統(tǒng)一大小，而且不會(huì)造成表空間碎片。表空間碎片會(huì)導(dǎo)致很多無(wú)法利用的小擴(kuò)展區(qū)分散在表空間中，引起額外的性能開(kāi)銷(xiāo)。

2.1.2有效利用索引

正確使用索引可以使數(shù)據(jù)庫(kù)性能得到大幅提高，因此根據(jù)不同場(chǎng)景需要合理建立并利用不同類(lèi)型的索引，使數(shù)據(jù)訪問(wèn)性能最優(yōu)。可參照如下指引使用索引：

（1）索引信息存放在一個(gè)單獨(dú)的表空間，并確保該表空間的數(shù)據(jù)文件與索引對(duì)應(yīng)的庫(kù)表的表空間的數(shù)據(jù)文件不在同一磁盤(pán)上。

（2）對(duì)where條件語(yǔ)句中高頻出現(xiàn)的字段建立索引，同時(shí)注意索引應(yīng)建立在小字段上，對(duì)于大文本字段甚至超長(zhǎng)字段，不要建立索引。

（3）當(dāng)where條件中經(jīng)常出現(xiàn)多個(gè)選擇字段時(shí)，可選擇區(qū)分度高的字段進(jìn)行組合創(chuàng)建復(fù)合索引，但不宜超過(guò)3個(gè)字段。

（4）索引包含了庫(kù)表所有字段時(shí)，可直接將表數(shù)據(jù)存放在索引塊中，構(gòu)成唯一索引表(index-only table)。

（5）被索引的列數(shù)據(jù)具有低基數(shù)(cardinality)時(shí)(即索引列具有相對(duì)較少的確定值)，通過(guò)創(chuàng)建位圖索引(bitmap index)可大幅改善性能。

（6）若對(duì)某個(gè)表字段的操作全是固定函數(shù)操作，不是原始操作，可考慮建立函數(shù)索引。

（7）對(duì)于需要經(jīng)常與其他庫(kù)表進(jìn)行連接的庫(kù)表，在連接字段上應(yīng)建立索引。

（8）通過(guò)定時(shí)任務(wù)定期重建索引，減少索引碎片。

2.1.3內(nèi)存優(yōu)化

對(duì)系統(tǒng)全局區(qū)(SGA)進(jìn)行合理配置。系統(tǒng)全局區(qū)包括四個(gè)部分：數(shù)據(jù)緩沖區(qū)；共享池；重做日志緩沖區(qū)；大型共享池。其中數(shù)據(jù)緩沖區(qū)和共享池是SGA的兩個(gè)最重要的優(yōu)化區(qū)域。在系統(tǒng)內(nèi)存足夠時(shí)，增大參數(shù)DB_BLOCK_BUFFERS的值可以提高訪問(wèn)數(shù)據(jù)緩沖區(qū)的性能；增大參數(shù)SHARED_POOL_SIZE的值可以提高訪問(wèn)數(shù)據(jù)字典及共享SQL和PL/SQL語(yǔ)句的性能。

Oracle在執(zhí)行類(lèi)似建立索引和執(zhí)行帶子句的查詢(xún)（例如Order By）的操作時(shí)，需要內(nèi)存空間來(lái)對(duì)數(shù)據(jù)進(jìn)行排序，合理配置SORT_AREA_RESERVED_SIZE和SORT_AREA_SIZE參數(shù)不僅能明顯提高需要排序的查詢(xún)性能，而且還能釋放I/O帶寬等系統(tǒng)資源，從而提高性能。

另外，為日志(redo log)緩沖區(qū)分配更多的內(nèi)存，也可有效減少磁盤(pán)I/O，特別是對(duì)于事務(wù)特別長(zhǎng)或事物數(shù)量較多的OLTP數(shù)據(jù)庫(kù)，性能提升效果顯著。

2.2　物理架構(gòu)優(yōu)化

2.2.1磁盤(pán)讀寫(xiě)能力的優(yōu)化

ORACLE存儲(chǔ)與訪問(wèn)數(shù)據(jù)非常依賴(lài)磁盤(pán)訪問(wèn)，而磁盤(pán)訪問(wèn)是任何計(jì)算機(jī)系統(tǒng)上最慢的操作，是性能的最大瓶頸，可參照以下指引進(jìn)行優(yōu)化：

（1）盡量將輸入/輸出操作分離到不同的磁盤(pán)上，避免串行等待。例如回滾段和日志文件在同一磁盤(pán)上的話，會(huì)導(dǎo)致寫(xiě)完回滾記錄再將磁盤(pán)磁頭移動(dòng)至日志文件存放的部分，非常耗時(shí)。

（2）把高輸入/輸出的磁盤(pán)放在不同的控制器上。單個(gè)控制器可以處理有限的并發(fā)操作，但應(yīng)該盡可能利用更多的控制器提高并行度，消除等候時(shí)間。

（3）把操作最頻繁的數(shù)據(jù)庫(kù)對(duì)象(如日志文件、回滾段、索引表空間)放在速度最快的磁盤(pán)上。

（4）通過(guò)RAID-0+1技術(shù)實(shí)現(xiàn)ORACLE條帶化，既可消除等待磁盤(pán)頭定位的延遲，也可通過(guò)磁盤(pán)鏡像實(shí)現(xiàn)數(shù)據(jù)保護(hù)，特別在多CPU并行查詢(xún)的情況下，能充分發(fā)揮硬件潛力。

2.2.2基礎(chǔ)運(yùn)行環(huán)境優(yōu)化

（1）應(yīng)充分利用物理內(nèi)存，但同時(shí)應(yīng)避免過(guò)多交換(swapping)發(fā)生，因?yàn)閷?nèi)存交換到磁盤(pán)的過(guò)程非常緩慢。對(duì)于系統(tǒng)全局區(qū)(SGA)的大小應(yīng)合理控制，切勿占據(jù)過(guò)多物理內(nèi)存，否則會(huì)引起過(guò)多SGA交換，會(huì)嚴(yán)重降低ORACLE性能。

（2）盡量用主干專(zhuān)用網(wǎng)絡(luò)將服務(wù)器連接在一起，并盡量將服務(wù)器-服務(wù)器的通信與服務(wù)器-客戶(hù)端的通信分離。

（3）盡量將ORACLE數(shù)據(jù)庫(kù)部署在單獨(dú)的機(jī)器上，與其他服務(wù)系統(tǒng)分離。

2.3　優(yōu)化效果評(píng)價(jià)

為驗(yàn)證數(shù)據(jù)庫(kù)性能的優(yōu)化設(shè)計(jì)效果，本文以一張記錄數(shù)200萬(wàn)、占用空間500M的單表為對(duì)象，對(duì)其進(jìn)行不同方法優(yōu)化后再對(duì)其執(zhí)行相同查詢(xún)語(yǔ)句的時(shí)間進(jìn)行了對(duì)比觀察，驗(yàn)證結(jié)果如下：

（1）未作任何優(yōu)化時(shí)，執(zhí)行時(shí)間為21.7s。

（2）索引建立后，執(zhí)行時(shí)間為10.4s。（3）表結(jié)構(gòu)優(yōu)化后，執(zhí)行時(shí)間為6.7s。（4）讀寫(xiě)優(yōu)化后，執(zhí)行時(shí)間為4.0s。（5）綜合優(yōu)化后，執(zhí)行時(shí)間下降到了2.4s。

3　結(jié)論

綜上所述，本文以O(shè)RACLE數(shù)據(jù)庫(kù)為研究對(duì)象，從數(shù)據(jù)庫(kù)表設(shè)計(jì)、索引利用、內(nèi)存配置、磁盤(pán)讀寫(xiě)能力、基礎(chǔ)環(huán)境等方面進(jìn)行了性能優(yōu)化設(shè)計(jì)，使查詢(xún)執(zhí)行時(shí)間得到了明顯的降低，數(shù)據(jù)庫(kù)性能得到明顯提升，證實(shí)了優(yōu)化方法的有效性，為數(shù)據(jù)庫(kù)管理與應(yīng)用工作提供了指引。