岳彩云 賴曉風

摘要：數(shù)據(jù)庫系統(tǒng)是任何信息系統(tǒng)最重要的組成部分，它涉及信息系統(tǒng)運行效率，影響系統(tǒng)性能。隨著現(xiàn)代信息技術的進步，數(shù)據(jù)庫的規(guī)模越來越龐大，對數(shù)據(jù)處理的操作也越來越復雜。在Oracle數(shù)據(jù)庫系統(tǒng)中，查詢操作是最基本、最復雜、最頻繁的操作，SQL的查詢語句的效率直接影響數(shù)據(jù)庫的整體性能。該文主要介紹了SOL的語句所用優(yōu)化技術，簡要分析了數(shù)據(jù)庫邏輯結構的優(yōu)化、數(shù)據(jù)庫物理存儲結構的優(yōu)化、使用分區(qū)。同時深入研究SOL性能分析及優(yōu)化，其中，手動進行SOLprofile綁定主要涉及要執(zhí)行的SOL文本、計劃出問題后的表現(xiàn)、采用sql lprofile綁定;索引調優(yōu)涉及要執(zhí)行的SOL文本、SQL執(zhí)行的相關統(tǒng)計信息、SQL執(zhí)行計劃、創(chuàng)建索引等。經過研究得出，若想將ORACLE數(shù)據(jù)庫性能提高，必須多角度優(yōu)化SQL語句。

關鍵詞：數(shù)據(jù)庫;SQL;索引;查詢

中圖分類號：TP311 文獻標識碼：A

文章編號：1009-3044（2020）10-0017-03

1數(shù)據(jù)庫結構優(yōu)化

1.1數(shù)據(jù)庫邏輯結構的優(yōu)化

邏輯數(shù)據(jù)庫設計不合理往往易產生數(shù)據(jù)冗余、更新異常、插入異常、刪除異常等問題，所以邏輯數(shù)據(jù)庫設計至少應滿足規(guī)范化BC范式或第三范式。

為降低數(shù)據(jù)冗余、減少用于存儲數(shù)據(jù)的頁，可遵循高級別的范式來減少每張表的列數(shù)，但這將產生更多表，且表間關系會更復雜，這樣會降低系統(tǒng)的性能，特別是查詢性能。從某種意義上說，非規(guī)范化能提高系統(tǒng)效率，非規(guī)范化過程可以結合性能考慮用多種手段實現(xiàn)，所以進行數(shù)據(jù)庫邏輯結構設計時應綜合考慮數(shù)據(jù)冗余和基于連接的查詢性能問題。

1.2數(shù)據(jù)庫物理存儲結構的優(yōu)化

因為數(shù)據(jù)文件和日志文件的位置、分布直接影響到數(shù)據(jù)庫系統(tǒng)性能，所以數(shù)據(jù)庫設計應遵循：一是將序列訪問的文件和數(shù)據(jù)文件分別存放在不同磁盤上，一般，序列訪問文件宜存儲于高速專用磁盤上，數(shù)據(jù)文件分散存儲到不同的磁盤上而實現(xiàn)并行I/O，從而提高訪問速度;二是數(shù)據(jù)類型應盡量使用所需的最小存儲空間，特別是索引列，如能使用Smallint類型的就不用Int型，這樣數(shù)據(jù)頁就能存放更多的數(shù)據(jù)行，以減少I/O操作。

1.3使用分區(qū)

對于數(shù)據(jù)量超過PB級、TB級甚至更大的大型數(shù)據(jù)庫，某些單表的記錄數(shù)往往多達億條，巨大的數(shù)據(jù)量將嚴重影響數(shù)據(jù)庫的運行效率和運維難度。為解決這一問題，可對表進行合理分區(qū)。把大表分為多個更小、更容易管理的部分，充分利用數(shù)據(jù)庫系統(tǒng)中的多個CPU或多個磁盤子系統(tǒng)，以改善數(shù)據(jù)庫系統(tǒng)的運行效率?？梢园凑諛I(yè)務數(shù)據(jù)本身性質進行表分區(qū)，也可按時間進行表分區(qū)，或其他業(yè)務的維度進行分區(qū)。

2SQL性能分析及優(yōu)化

不同的業(yè)務場景、數(shù)據(jù)庫類型、數(shù)據(jù)邏輯結構及不同的網絡、服務器等硬件環(huán)境，實驗結果或有微小差異。以下所有實驗結果都是針對某市某業(yè)務信息系統(tǒng)后臺數(shù)據(jù)庫進行的實驗，且所用數(shù)據(jù)庫為Oracle 12C版本，相關SQL處理后的效果只是一個大致的結果。

2.1手動進行SQL profile綁定

當Oracle面對執(zhí)行計劃失效或跑偏時，執(zhí)行效率就會大大降低，影響數(shù)據(jù)庫的查詢性能甚至整個數(shù)據(jù)庫性能，此時，最好的優(yōu)化手段就是進行SQL profile人工綁定。例如：

2.1.1要執(zhí)行的SQL文本

2.1.2計劃出問題后的表現(xiàn)sql單次執(zhí)行時間需要消耗204秒，單次運行產生邏輯讀消耗995k塊次，物理讀323塊次，按照標準塊8k計算，需要消耗邏輯讀7.9G，物理讀2.5G。

系統(tǒng)消耗主要表現(xiàn)在ID=6，ac82_110共約6.4億行數(shù)據(jù)，idex_ac82 110_7的distinct值為509。

2.1.3采用sql profile綁定

綁定后，SQL執(zhí)行時間由207秒縮短至4秒，執(zhí)行效率提高約50倍;物理讀消耗由324k減少至4k，物理讀消耗減少約80倍;邏輯讀消耗由1M塊次減少至108k，邏輯讀減少約10倍。

2.2索引調優(yōu)

能否有效使用索引是數(shù)據(jù)庫是否取得高性能的關鍵。因為查詢主要性能開銷是磁盤I/O，而全表掃描會產生大量的磁盤I/O，而使用索引直接指向數(shù)據(jù)存放位置，則只需少量的磁盤讀取操作，避免了全表掃描帶來的性能開銷，從而加速數(shù)據(jù)的查詢過程。但是，索引也會使數(shù)據(jù)庫在執(zhí)行增、刪、改等操作時增加額外的系統(tǒng)開銷，并且索引本身也會占用數(shù)據(jù)庫的空間。因此，索引并不是越多越好，只有建立合理有效的索引才有助于改善數(shù)據(jù)庫性能。合理有效的索引是建立在對各種業(yè)務場景熟悉，科學的查詢分析和預測基礎上的。

2.2.1要執(zhí)行的SQL文本

2.2.2 SQL執(zhí)行的相關統(tǒng)計信息

平均邏輯讀達87k塊次，采樣期為兩天，兩天執(zhí)行18799次，屬高執(zhí)行頻次，致使總邏輯讀達1.6G塊次。

2.2.3 SQL執(zhí)行計劃

2.2.4創(chuàng)建索引

從上述執(zhí)行計劃可看到，執(zhí)行計劃中存在索引跳躍掃描，而該索引得前導列yaz040的基數(shù)達15萬多，這樣的跳躍掃描很消耗性能，所以建議在AAZ288列上單獨創(chuàng)建索引。創(chuàng)建索引語句如下：

2.2.5 SQL創(chuàng)建后的效果

通過創(chuàng)建索引后，執(zhí)行時間只有0.01s，平均邏輯讀只有幾百塊次甚至更低。

2.3 SQL語句本身優(yōu)化

在數(shù)據(jù)庫系統(tǒng)中，使用SQL不能僅僅關注執(zhí)行結果的正確性，更應關注在不同的軟、硬件及網絡環(huán)境和業(yè)務場景下存在的性能差異，這種性能差異在大型甚至超大型數(shù)據(jù)庫環(huán)境中尤為明顯。本人在工作和學習的實踐中發(fā)現(xiàn)，性能低下的SQL往往來自濫用索引、連接的誤用和無法優(yōu)化的where子句。通過避免以上問題，可以明顯提高SQL運行效率。

2.3.1要執(zhí)行的SQL文本

2.3.2 SQL執(zhí)行的相關統(tǒng)計信息

SQL在統(tǒng)計單次執(zhí)行時間約40分鐘，單次邏輯讀8.9M塊次，按標準塊8k計算，需要消耗約70G的邏輯讀。

2.3.3執(zhí)行計劃

注意到id=8，10，7，id=8通過id=9即KB05K1的自鏈接條件返回了851條結果集，然后作為驅動表同id=10做了一個filter，這意味著要對KB05K1做約800次的掃描。性能消耗就出在這里。

2.3.4 SQL語句優(yōu)化調整

2.3.5 SQL語句優(yōu)化后的執(zhí)行效果

將源語句標亮部分造成的多次大表掃描，用分析函數(shù)替代，可只走一次掃描。邏輯讀由原來的8900k塊次變?yōu)?37k塊次，縮小65倍。查詢由原來的40分鐘變?yōu)?秒。

3結束語

文章對Oracle數(shù)據(jù)庫性能調整和優(yōu)化進行了簡要的分析和研究，對數(shù)據(jù)庫設計、SQL的性能等的優(yōu)化進行了探討。但在實際工作中，針對不同的數(shù)據(jù)量級、不同的軟件、硬件環(huán)境以及網絡環(huán)境，需要綜合考慮各種方法和制定多種措施。Oracle數(shù)據(jù)庫性能優(yōu)化是一項系統(tǒng)工程，需要對數(shù)據(jù)庫系統(tǒng)的運行狀態(tài)做出全面的評估并根據(jù)工作實際情況系統(tǒng)地動態(tài)調整數(shù)據(jù)庫以得到最優(yōu)的性能。