基于SQL和表設(shè)計(jì)的Oracle數(shù)據(jù)庫(kù)開(kāi)發(fā)審計(jì)研究

曾明霏+劉強(qiáng)

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

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

DOIDOI：10.11907/rjdk.162063

中圖分類號(hào)：TP391

文獻(xiàn)標(biāo)識(shí)碼：A文章編號(hào)：1672-7800（2016）012-0136-03

0 引言

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

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

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

1 數(shù)據(jù)庫(kù)性能指標(biāo)

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

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

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

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

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

AAS/CPU_Count ≈0非?？臻e；

AAS/CPU_Count <= 0.5沒(méi)堵塞；

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

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

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

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

2.1 SQL語(yǔ)句分析

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

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

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

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

2.2 表設(shè)計(jì)分析

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

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

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

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

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

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

2.3 數(shù)據(jù)庫(kù)開(kāi)發(fā)審計(jì)標(biāo)準(zhǔn)

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

2.3.1 表審計(jì)

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

2.3.2 索引審計(jì)

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

2.3.3 SQL審計(jì)

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

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

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

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

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

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

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

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

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

3 結(jié)語(yǔ)

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

參考文獻(xiàn)：

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

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

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

（責(zé)任編輯：杜能鋼）