曾明霏+劉強

摘 要：企業(yè)級信息系統(tǒng)的數(shù)據(jù)庫表設(shè)計和SQL語句優(yōu)化，對提高信息系統(tǒng)性能和穩(wěn)定性、可靠性有著重要意義?；跀?shù)據(jù)庫表設(shè)計和SQL語句優(yōu)化技術(shù)，提出了一套數(shù)據(jù)庫開發(fā)審計標準，根據(jù)標準對一個企業(yè)級信息系統(tǒng)進行了多次數(shù)據(jù)庫開發(fā)審計，發(fā)現(xiàn)了系統(tǒng)潛在的性能隱患，采取相應(yīng)措施，改善了信息系統(tǒng)性能。

關(guān)鍵詞：SQL優(yōu)化；企業(yè)級信息系統(tǒng)；表設(shè)計；索引優(yōu)化；數(shù)據(jù)庫開發(fā)審計

DOIDOI：10.11907/rjdk.162063

中圖分類號：TP391

文獻標識碼：A文章編號：1672-7800（2016）012-0136-03

0 引言

隨著計算機技術(shù)和網(wǎng)絡(luò)技術(shù)的普及，數(shù)據(jù)庫技術(shù)得到了長足發(fā)展，成為現(xiàn)代計算機應(yīng)用與信息系統(tǒng)的核心技術(shù)。隨著信息系統(tǒng)用戶規(guī)模和應(yīng)用范圍的不斷擴展，數(shù)據(jù)庫性能問題越來越突出，越來越難以優(yōu)化，需要在信息系統(tǒng)開發(fā)及試運行階段進行庫表設(shè)計、索引優(yōu)化和SQL優(yōu)化來解決系統(tǒng)性能瓶頸，以確保數(shù)據(jù)庫和信息系統(tǒng)穩(wěn)定、高效運行。

Oracle數(shù)據(jù)庫是當前市場占有率最高、使用范圍最廣的關(guān)系型數(shù)據(jù)庫。對于使用關(guān)系型數(shù)據(jù)庫的信息系統(tǒng)而言，SQL語句的好壞直接影響系統(tǒng)性能。例如：某公司Call center系統(tǒng)坐席登錄非常慢，嚴重影響了日常工作，原因是SQL選錯執(zhí)行計劃，走全表掃描導(dǎo)致[1]；某房地產(chǎn)開發(fā)商ERP系統(tǒng)因為一條全表掃描的SQL語句，導(dǎo)致在業(yè)務(wù)繁忙時段引起大量的讀寫等待事件，最終數(shù)據(jù)庫失去響應(yīng)；某政府系統(tǒng)因為一個SQL語句的索引設(shè)計不當導(dǎo)致大量的IO讀，引起數(shù)據(jù)庫性能緩慢，最終信息系統(tǒng)失去響應(yīng)。從上述事例可以看出，一條性能低下的SQL語句就可以拖垮整個系統(tǒng)，甚至導(dǎo)致數(shù)據(jù)庫服務(wù)器失去響應(yīng)或整個數(shù)據(jù)庫掛起。

基于Oracle數(shù)據(jù)庫建設(shè)經(jīng)驗，本文研究了數(shù)據(jù)庫的表設(shè)計和SQL語句優(yōu)化，提出了數(shù)據(jù)庫開發(fā)審計標準，以指導(dǎo)大型信息系統(tǒng)在開發(fā)及試運行階段的性能優(yōu)化工作。在實踐過程中發(fā)現(xiàn)了多處信息系統(tǒng)性能隱患，完善了審計標準。

1 數(shù)據(jù)庫性能指標

業(yè)界通用的Oracle數(shù)據(jù)庫性能評價指標主要有系統(tǒng)吞吐量、用戶平均響應(yīng)時間、磁盤IO、數(shù)據(jù)庫AWR報告中的AAS/CPU_Count（DB time/Elapsed）比值4個指標。

（1）系統(tǒng)吞吐量。系統(tǒng)吞吐量指單位時間內(nèi)數(shù)據(jù)庫完成的SQL語句數(shù)目，以每秒的事務(wù)量（TPS）表示。

（2）用戶平均響應(yīng)時間。響應(yīng)時間指用戶從提交SQL語句開始到獲得結(jié)果集的第一行所需要的時間，是應(yīng)用作出反應(yīng)的時間，以毫秒或秒表示。

（3）磁盤IO。數(shù)據(jù)庫中發(fā)生的每個動作幾乎都將產(chǎn)生某種類型的IO活動，該活動可以是邏輯的（在內(nèi)存中），也可以是物理的（在磁盤上）。通過降低不必要的IO開銷，可增加用戶任務(wù)獲得的吞吐量，縮短用戶“響應(yīng)時間”。其中，磁盤IO操作是數(shù)據(jù)庫性能最重要的方面，是計算機最大的開銷。因此，通過減少不必要的磁盤IO，可大大提高系統(tǒng)性能。

（4）AAS/CPU_Count比值。DB Time為某一時段時間數(shù)據(jù)庫使用的CPU時間的總和，平均活動會話Average Active Session（AAS）=DB Time/elaspsed time（歷時），通過AAS指標可以衡量數(shù)據(jù)庫的繁忙程度。每一個CPU時間由操作系統(tǒng)分成CPU時間片，CPU時間片以輪詢模式分配給線程或進程，計算在最小單位CPU片段內(nèi)整個系統(tǒng)允許的最大CPU個數(shù)。通過比較AAS值與CPU可以衡量數(shù)據(jù)庫繁忙程度。

AAS/CPU_Count ≈0非?？臻e；

AAS/CPU_Count <= 0.5沒堵塞；

AAS/CPU_Count≈1部分進程已達100%，應(yīng)用開始出現(xiàn)緩慢；

AAS/CPU_Count>或>>1出現(xiàn)性能問題或堵死、掛死狀態(tài)。

2 數(shù)據(jù)庫開發(fā)審計

基于Oracle數(shù)據(jù)庫的開發(fā)審計主要對SQL語句和表設(shè)計兩個維度進行分析。

2.1 SQL語句分析

較大的表使用全表掃描的SQL會導(dǎo)致過多的邏輯讀，降低SQL的執(zhí)行效率，引起latch：cache buffer chain、direct path read 等異常等待事件，最終導(dǎo)致數(shù)據(jù)庫主機CPU、IO使用率過高，數(shù)據(jù)庫繁忙。

高耗時的SQL會引起數(shù)據(jù)庫的大部分資源（IO、CPU等）被少數(shù)幾條SQL占用，引起數(shù)據(jù)庫繁忙，大大降低系統(tǒng)的吞吐量。

SELECT *語句雖然獲取了表中的所有列數(shù)據(jù)，但實際情況卻可能只需要其中一列或少許列，其它列數(shù)據(jù)對應(yīng)用功能是多余的，導(dǎo)致數(shù)據(jù)庫采用最大的可能來滿足應(yīng)用需求。如果應(yīng)用代碼能夠明確寫明編程意圖，列出具體要獲取哪些字段值，就可以提高數(shù)據(jù)庫的處理能力。如果所需的列在索引里已存在，Oracle就只要查詢索引即可返回結(jié)果，不需要查詢表數(shù)據(jù)本身，這樣可極大提高SQL的執(zhí)行效率，大幅降低對IO的請求，減少信息系統(tǒng)和數(shù)據(jù)庫通訊的數(shù)據(jù)量。

在條件列進行函數(shù)運算，將會導(dǎo)致列上的索引無法使用，數(shù)據(jù)庫被迫進行全表掃描，大大降低SQL的執(zhí)行效率。

2.2 表設(shè)計分析

大對象數(shù)據(jù)一般非常大，被databuffer緩存的可能性非常小，因此讀寫大對象數(shù)據(jù)會導(dǎo)致比較多的IO開銷。如大對象數(shù)據(jù)使用獨立表空間就可以獨立進行磁盤管理，甚至分散到多個磁盤來存儲，從而提高IO的吞吐量[3]。

大對象讀寫的數(shù)據(jù)單元一般大于數(shù)據(jù)庫默認的數(shù)據(jù)塊大小。如果大數(shù)據(jù)字段的數(shù)據(jù)塊配置太小，會導(dǎo)致比較多的IO次數(shù)。增加大對象獨立表空間的數(shù)據(jù)塊大小可以減少IO次數(shù)，提高性能。

應(yīng)用程序動態(tài)創(chuàng)建實體表，表定義會保留在數(shù)字字典中。大量動態(tài)實體表創(chuàng)建會導(dǎo)致Oracle數(shù)字字典過大，降低數(shù)據(jù)庫性能。信息系統(tǒng)如大量采用動態(tài)表創(chuàng)建機制而清理不及時，會大大增加數(shù)據(jù)字典數(shù)量，進而增加SQL的執(zhí)行時間。此外，對大數(shù)據(jù)量的表執(zhí)行全表掃描，會導(dǎo)致過多的邏輯讀，導(dǎo)致數(shù)據(jù)庫繁忙、CPU使用率過高。

主鍵是表內(nèi)數(shù)據(jù)唯一性的主要標識，主鍵設(shè)計可以有效保障數(shù)據(jù)質(zhì)量，避免重復(fù)數(shù)據(jù)。在多表關(guān)聯(lián)的SQL語句中，主鍵和外鍵是常見的關(guān)聯(lián)條件列。如果沒有主鍵和外鍵索引存在，就會嚴重影響SQL語句的執(zhí)行計劃生成和執(zhí)行效率，大大降低系統(tǒng)吞吐量，增加用戶響應(yīng)時間。

創(chuàng)建大量的單列索引會導(dǎo)致索引數(shù)量太多，降低DML語句性能和IO效率。單列索引對數(shù)據(jù)的過濾能力（離散度）有限，對識別能力不強的字段創(chuàng)建索引，會導(dǎo)致即使有索引也要讀取大量的無效數(shù)據(jù)。太多的單列索引可能會導(dǎo)致Oracle執(zhí)行計劃困難，甚至產(chǎn)生不恰當?shù)膱?zhí)行計劃，嚴重影響執(zhí)行效率。

如果表上只有主鍵而沒有普通索引，那么這個表發(fā)生全表掃描的可能性非常大。特別是系統(tǒng)用戶數(shù)、數(shù)據(jù)量較大時，會產(chǎn)生嚴重的性能問題，導(dǎo)致過高的磁盤IO和緩慢的用戶響應(yīng)，降低系統(tǒng)吞吐量。

2.3 數(shù)據(jù)庫開發(fā)審計標準

數(shù)據(jù)庫開發(fā)審計由于缺乏定量指標，無法直接用于數(shù)據(jù)庫開發(fā)。根據(jù)上述原理，本文提出一種數(shù)據(jù)庫開發(fā)審計標準，在數(shù)據(jù)庫開發(fā)過程或系統(tǒng)上線前對SQL及表索引設(shè)計進行審計，以避免系統(tǒng)在實際生產(chǎn)過程中出現(xiàn)性能瓶頸。

2.3.1 表審計

表審計是在數(shù)據(jù)庫的所有表中查找設(shè)計不佳的表，并對這些表的數(shù)量進行統(tǒng)計。設(shè)計不佳的表有3種類型：①大對象使用：統(tǒng)計存在大對象但是對大對象字段沒有單獨的表空間存放；②動態(tài)創(chuàng)建表：在應(yīng)用程序運行過程中，動態(tài)創(chuàng)建實體表； ③大數(shù)據(jù)量表：當前（或預(yù)期）數(shù)據(jù)量大于2 000萬行（或大于2GB）的、尚未制定數(shù)據(jù)生命周期策略的表。數(shù)據(jù)生命周期策略包括轉(zhuǎn)儲策略、索引分區(qū)、數(shù)據(jù)分區(qū)、數(shù)據(jù)分離等[2]。

2.3.2 索引審計

索引審計是在數(shù)據(jù)庫的所有索引中查找設(shè)計不佳的索引，并對這些索引數(shù)量進行統(tǒng)計。設(shè)計不佳的索引類型有：①主鍵設(shè)計：統(tǒng)計沒有設(shè)計主鍵且沒有唯一索引的表。在數(shù)據(jù)模型設(shè)計過程中，主鍵是一張表的重要設(shè)計項目，原則上每張表必須確保有合適的主鍵；②組合索引：統(tǒng)計擁有5個或以上索引的表的數(shù)量。一個表的索引多于5個就要進行審核和監(jiān)控，判斷這些索引設(shè)計是否合理；③無普通索引：統(tǒng)計所有只有主鍵沒有其它索引的表的數(shù)量。當SQL使用非主鍵的列作為條件列來查詢數(shù)據(jù)時，如果沒有合適的索引，就會導(dǎo)致全表掃描。

2.3.3 SQL審計

SQL審計是在數(shù)據(jù)庫的所有執(zhí)行記錄中查找性能不佳的SQL，并對這些SQL數(shù)量進行統(tǒng)計。性能不佳的SQL類型：①全表掃描：統(tǒng)計出現(xiàn)全表掃描的SQL數(shù)量。全表掃描是指Oracle在訪問目標表里的數(shù)據(jù)時，會從該表所占用的第一個區(qū)（EXTENT）的第一個塊（BLOCK）開始掃描，一直掃描到該表的高水位線（HWM，High Water Mark），這段范圍內(nèi)所有的數(shù)據(jù)塊Oracle都必須讀到。在OLTP類型的數(shù)據(jù)庫中，對大表的全表掃描所耗費的IO資源和時間，隨著數(shù)據(jù)量的增加而增加；②條件列函數(shù)運算：統(tǒng)計SQL語句中對表的查詢條件字段添加了計算函數(shù)（如substr，length等）的SQL數(shù)量。這類SQL導(dǎo)致該字段上的索引無法使用，從而導(dǎo)致低效的全表掃描，并可能改變執(zhí)行計劃，這種現(xiàn)象必須盡量在應(yīng)用程序中避免；③SELECT *：統(tǒng)計所有含有SELECT *的SQL語句數(shù)量。Oracle在SQL解析過程中，會將“*”依次轉(zhuǎn)換成所有的列名。這個工作通過查詢數(shù)據(jù)字典完成，意味著將耗費更多的時間。為提高SQL語句解釋速度，應(yīng)避免在SQL語句使用“*”代替所有字段名；④高耗時SQL：統(tǒng)計所有執(zhí)行時間大于2秒的SQL語句數(shù)量。實時監(jiān)控信息系統(tǒng)中正常執(zhí)行的Top SQL，將這些SQL不間斷優(yōu)化，以保持信息系統(tǒng)數(shù)據(jù)庫的良好性能狀況。

2.3.4 數(shù)據(jù)庫開發(fā)審計實施

為防止不穩(wěn)定或不成熟的系統(tǒng)上線，必須建立一套準確有效的數(shù)據(jù)庫開發(fā)審計標準，在測試環(huán)境或準正式環(huán)境中模擬運行系統(tǒng)所有功能，使數(shù)據(jù)庫審計能有效獲取系統(tǒng)執(zhí)行痕跡，估算各種痕跡可能存在的性能隱患，并進行評定，根據(jù)評定分值衡量一個系統(tǒng)的性能健康程度。

當系統(tǒng)達標上線后，由于數(shù)據(jù)量或數(shù)據(jù)分布情況的不斷變化，大量的系統(tǒng)功能BUG會不停地變更修復(fù)，同時新的功能也會不斷加入進來。為了保障這些變更或新增功能的有效運行，必須對每次系統(tǒng)的升級變更進行全面的性能審計，及早發(fā)現(xiàn)問題。對于審計后的系統(tǒng)運行也需要不斷監(jiān)控，發(fā)現(xiàn)問題及時通過SQL和Index進行優(yōu)化。上述過程是有效保障系統(tǒng)數(shù)據(jù)庫性能穩(wěn)定的有效方法。

2.4 數(shù)據(jù)庫開發(fā)審計實踐

某大型國企的核心信息系統(tǒng)建設(shè)項目，業(yè)主采用基于SQL語句和表設(shè)計的數(shù)據(jù)庫開發(fā)審計標準進行數(shù)據(jù)庫開發(fā)審計。在系統(tǒng)出廠測試時進行第一次開發(fā)審計，隨后在部署完成、試運行、正式運行3個關(guān)鍵時間節(jié)點進行3次數(shù)據(jù)庫開發(fā)審計復(fù)核。這4次開發(fā)審計的綜合評定結(jié)果如表1所示。

在每次開發(fā)審計完成后，均要求項目開發(fā)商針對評定結(jié)果、詳細問題列表和改善建議進行整改。經(jīng)過開發(fā)商和數(shù)據(jù)庫管理員的多次整改，所有審計違規(guī)項均大幅減少，主要數(shù)據(jù)庫性能指標有明顯改善，詳見表2和圖1。

從圖1可以看出，經(jīng)過多次審計和整改，系統(tǒng)數(shù)據(jù)庫各項性能指標均有大幅提升。系統(tǒng)吞吐量由54上升至186，提升近3倍；用戶響應(yīng)時間明顯下降，第3次復(fù)核后的響應(yīng)時間僅是初次審計前的2%；磁盤IO顯著降低，讀寫不再是系統(tǒng)瓶頸；反應(yīng)數(shù)據(jù)庫繁忙程度的指標AAS/CPU_Count也從0.7下降至0.2。這些性能指標的提升說明數(shù)據(jù)庫開發(fā)審計對信息系統(tǒng)性能改善有極大的促進作用，明確了整改方向，提升了整改效果。

3次整改使信息系統(tǒng)達到上線標準，如期上線投入使用。上線后，系統(tǒng)吞吐量、用戶響應(yīng)時間、磁盤IO、AAS/CPU_Count等各項性能指標均符合預(yù)期，系統(tǒng)運行狀況良好，沒有出現(xiàn)性能問題。

3 結(jié)語

基于SQL和表設(shè)計的Oracle數(shù)據(jù)庫開發(fā)審計標準可及時發(fā)現(xiàn)信息系統(tǒng)建設(shè)過程中的性能隱患、快速定位信息系統(tǒng)問題原因、有效開展具有針對性整改，從而提高信息系統(tǒng)開發(fā)質(zhì)量，提高信息系統(tǒng)的支撐服務(wù)能力和系統(tǒng)穩(wěn)定性。該套數(shù)據(jù)庫開發(fā)審計標準可應(yīng)用在各類大型信息系統(tǒng)開發(fā)、建設(shè)和運維工作中。

參考文獻：

[1] 崔華.基于Oracle的SQL優(yōu)化[M].北京：電子工業(yè)出版社，2014.

[2] 譚懷遠.讓Oracle跑得更快2—基于海量數(shù)據(jù)的數(shù)據(jù)庫設(shè)計與優(yōu)化[M].北京：電子工業(yè)出版社，2011.

[3] THOMAS KYTE.Oracle database 9i/10g/11g編程藝術(shù)[M].蘇金國，王小振，譯.北京：人民郵電出版社，2009.

（責任編輯：杜能鋼）