1、1數(shù)據(jù)庫設(shè)計(jì)要在良好的SQLServer方案中實(shí)現(xiàn)最優(yōu)的性能,最關(guān)鍵的是要有1個(gè)很好的數(shù)據(jù)庫設(shè)計(jì)方案。在實(shí)際工作中,許多SQLServer方案往往是由于數(shù)據(jù)庫設(shè)計(jì)得不好導(dǎo)致性能很差。所以,要實(shí)現(xiàn)良好的數(shù)據(jù)庫設(shè)計(jì)就必須考慮這些問題。1.1邏輯庫規(guī)范化問題一般來說,邏輯數(shù)據(jù)庫設(shè)計(jì)會(huì)滿足規(guī)范化的前3級(jí)標(biāo)準(zhǔn):1.第1規(guī)范:沒有重復(fù)的組或多值的列。2.第2規(guī)范:每個(gè)非關(guān)鍵字段必須依賴于主關(guān)鍵字,不能依賴于1個(gè)組合式主關(guān)鍵字的某些組成部分。3.第3規(guī)范:1個(gè)非關(guān)鍵字段不能依賴于另1個(gè)非關(guān)鍵字段。遵守這些規(guī)則的設(shè)計(jì)會(huì)產(chǎn)生較少的列和更多的表,因而也就減少了數(shù)據(jù)冗余,也減少了用于存儲(chǔ)數(shù)據(jù)的頁。但表關(guān)系也許需要通

2、過復(fù)雜的合并來處理,這樣會(huì)降低系統(tǒng)的性能。某種程度上的非規(guī)范化可以改善系統(tǒng)的性能,非規(guī)范化過程可以根據(jù)性能方面不同的考慮用多種不同的方法進(jìn)行,但以下方法經(jīng)實(shí)踐驗(yàn)證往往能提高性能。1.如果規(guī)范化設(shè)計(jì)產(chǎn)生了許多4路或更多路合并關(guān)系,就可以考慮在數(shù)據(jù)庫實(shí)體中加入重復(fù)屬性。2.常用的計(jì)算字段可以考慮存儲(chǔ)到數(shù)據(jù)庫實(shí)體中。比如某一個(gè)項(xiàng)目的計(jì)劃中有計(jì)劃表,其字段為:項(xiàng)目編號(hào)、年初計(jì)劃、二次計(jì)劃、調(diào)整計(jì)劃、補(bǔ)列計(jì)劃,而計(jì)劃總數(shù)是用戶經(jīng)常需要在查詢和報(bào)表中用到的,在表的記錄量很大時(shí),有必要把計(jì)劃總數(shù)作為1個(gè)獨(dú)立的字段加入到表中。這里可以采用觸發(fā)器以在客戶端保持?jǐn)?shù)據(jù)的一致性。3.重新定義實(shí)體以減少外部屬性數(shù)據(jù)或行

3、數(shù)據(jù)的開支。相應(yīng)的非規(guī)范化類型是:把1個(gè)實(shí)體分割成2個(gè)表。這樣就把頻繁被訪問的數(shù)據(jù)同較少被訪問的數(shù)據(jù)分開了。這種方法要求在每個(gè)表中復(fù)制首要關(guān)鍵字。這樣產(chǎn)生的設(shè)計(jì)有利于并行處理,并將產(chǎn)生列數(shù)較少的表。把1個(gè)實(shí)體分割成2個(gè)表。這種方法適用于那些將包含大量數(shù)據(jù)的實(shí)體。在應(yīng)用中常要保留歷史記錄,但是歷史記錄很少用到。因此可以把頻繁被訪問的數(shù)據(jù)同較少被訪問的歷史數(shù)據(jù)分開。而且如果數(shù)據(jù)行是作為子集被邏輯工作組訪問的,那么這種方法也是很有好處的。1.2生成物理數(shù)據(jù)庫要想正確選擇基本物理實(shí)現(xiàn)策略,必須懂得數(shù)據(jù)庫訪問格式和硬件資源的操作特點(diǎn),主要是內(nèi)存和磁盤子系統(tǒng)I/O。這是一個(gè)范圍廣泛的話題,但以下的準(zhǔn)則可能

4、會(huì)有所幫助。1.與每個(gè)表列相關(guān)的數(shù)據(jù)類型應(yīng)該反映數(shù)據(jù)所需的最小存儲(chǔ)空間,特別是對于被索引的列更是如此。比如能使用smallint類型就不要用integer類型,這樣索引字段可以被更快地讀取,而且可以在1個(gè)數(shù)據(jù)頁上放置更多的數(shù)據(jù)行,因而也就減少了I/O操作。2.把1個(gè)表放在某個(gè)物理設(shè)備上,再通過SQLServer段把它的不分簇索引放在1個(gè)不同的物理設(shè)備上,這樣能提高性能。尤其是系統(tǒng)采用了多個(gè)智能型磁盤控制器和數(shù)據(jù)分離技術(shù)的情況下,這樣做的好處更加明顯。3.用SQLServer段把一個(gè)頻繁使用的大表分割開,并放在2個(gè)單獨(dú)的智能型磁盤控制器的數(shù)據(jù)庫設(shè)備上,這樣也可以提高性能。因?yàn)橛卸鄠€(gè)磁頭在查找,所

5、以數(shù)據(jù)分離也能提高性能。4.用SQLServer段把文本或圖像列的數(shù)據(jù)存放在1個(gè)單獨(dú)的物理設(shè)備上可以提高性能。1個(gè)專用的智能型的控制器能進(jìn)一步提高性能。2與SQLServer相關(guān)的硬件系統(tǒng)與SQLServer有關(guān)的硬件設(shè)計(jì)包括系統(tǒng)處理器、內(nèi)存、磁盤子系統(tǒng)和網(wǎng)絡(luò),這4個(gè)部分基本上構(gòu)成了硬件平臺(tái),和SQLServer運(yùn)行于其上。2.1系統(tǒng)處理器根據(jù)自己的具體需要確定CPU結(jié)構(gòu)的過程就是估計(jì)在硬件平臺(tái)上占用CPU的工作量的過程。從以往的經(jīng)驗(yàn)看,CPU配置最少應(yīng)是1個(gè)80586/100處理器。如果只有23個(gè)用戶,這就足夠了,但如果打算支持更多的用戶和關(guān)鍵應(yīng)用,推薦采用PentiumPro或P級(jí)CPU。

6、2.2內(nèi)存為SQLServer方案確定合適的內(nèi)存設(shè)置對于實(shí)現(xiàn)良好的性能是至關(guān)重要的。SQLServer用內(nèi)存做過程緩存、數(shù)據(jù)和索引項(xiàng)緩存、靜態(tài)開支和設(shè)置開支。SQLServer最多能利用2GB虛擬內(nèi)存,這也是最大的設(shè)置值。還有一點(diǎn)必須考慮的是WindowsNT和它的所有相關(guān)的服務(wù)也要占用內(nèi)存。WindowsNT為每個(gè)WIN32應(yīng)用程序提供了4GB的虛擬地址空間。這個(gè)虛擬地址空間由WindowsNT虛擬內(nèi)存管理器映射到物理內(nèi)存上,在某些硬件平臺(tái)上可以達(dá)到4GB。SQLServer應(yīng)用程序只知道虛擬地址,所以不能直接訪問物理內(nèi)存,這個(gè)訪問是由VMM控制的。WindowsNT允許產(chǎn)生超出可用的物理內(nèi)

7、存的虛擬地址空間,這樣當(dāng)給SQLServer分配的虛擬內(nèi)存多于可用的物理內(nèi)存時(shí),會(huì)降低SQLServer的性能。這些地址空間是專門為SQLServer系統(tǒng)設(shè)置的,所以如果在同一硬件平臺(tái)上還有其它軟件在運(yùn)行,那么應(yīng)該考慮到它們也占用一部分內(nèi)存。一般來說硬件平臺(tái)至少要配置32MB的內(nèi)存,其中,WindowsNT至少要占用16MB。1個(gè)簡單的法則是,給每一個(gè)并發(fā)的用戶增加100KB 的內(nèi)存。例如,如果有100個(gè)并發(fā)的用戶,則至少需要32MB+100用戶100KB=42MB內(nèi)存,實(shí)際的使用數(shù)量還需要根據(jù)運(yùn)行的實(shí)際情況調(diào)整?？梢哉f,提高內(nèi)存是提高系統(tǒng)性能的最經(jīng)濟(jì)的途徑。2.3磁盤子系統(tǒng)設(shè)計(jì)1個(gè)好的磁盤I

8、/O系統(tǒng)是實(shí)現(xiàn)良好的SQLServer方案的一個(gè)很重要的方面。這里討論的磁盤子系統(tǒng)至少有1個(gè)磁盤控制設(shè)備和1個(gè)或多個(gè)硬盤單元,還有對磁盤設(shè)置和文件系統(tǒng)的考慮。智能型SCSI-2磁盤控制器或磁盤組控制器是不錯(cuò)的選擇,其特點(diǎn)如下:控制器高速緩存。總線上有處理器,可以減少對系統(tǒng)CPU的中斷。異步讀寫支持。32位RAID支持?？焖賁CSI2驅(qū)動(dòng)。超前讀高速緩存。3檢索策略在精心選擇了硬件平臺(tái),又實(shí)現(xiàn)了1個(gè)良好的數(shù)據(jù)庫方案,并且具備了用戶需求和應(yīng)用方面的知識(shí)后,現(xiàn)在應(yīng)該設(shè)計(jì)查詢和索引了。有2個(gè)方面對于在SQLServer上取得良好的查詢和索引性能是十分重要的,第1是根據(jù)SQLServer優(yōu)化器方面的知識(shí)

9、生成查詢和索引;第2是利用SQLServer的性能特點(diǎn),加強(qiáng)數(shù)據(jù)訪問操作。3.1SQLServer優(yōu)化器SQLServer數(shù)據(jù)庫內(nèi)核用1個(gè)基于費(fèi)用的查詢優(yōu)化器自動(dòng)優(yōu)化向SQL提交的數(shù)據(jù)查詢操作。數(shù)據(jù)操作查詢是指支持SQL關(guān)鍵字WHERE或HA VING的查詢,如SELECT、DELETE和?；谫M(fèi)用的查詢優(yōu)化器根據(jù)統(tǒng)計(jì)產(chǎn)生子句的費(fèi)用估算。了解優(yōu)化器數(shù)據(jù)處理過程的簡單方法是檢測SHOWPLAN命令的輸出結(jié)果。如果用基于字符的工具,可以通過鍵入SHOWSHOWPLANON來得到SHOWPLAN命令的輸出。如果使用圖形化查詢,比如SQLEnterpriseManager中的查詢工具或isql/w,可

10、以設(shè)定配置選項(xiàng)來提供這一。SQLServer的優(yōu)化通過3個(gè)階段完成:查詢分析、索引選擇、合并選擇。1.查詢分析在查詢分析階段,SQLServer優(yōu)化器查看每一個(gè)由正規(guī)查詢樹代表的子句,并判斷它是否能被優(yōu)化。SQLServer一般會(huì)盡量優(yōu)化那些限制掃描的子句。例如,搜索和/或合并子句。但是不是所有合法的SQL語法都可以分成可優(yōu)化的子句,如含有SQL不等關(guān)系符“<>”的子句。因?yàn)椤?lt;>”是1個(gè)排斥性的操作符,而不是1個(gè)包括性的操作符,所在掃描整個(gè)表之前無法確定子句的選擇范圍會(huì)有多大。當(dāng)1個(gè)關(guān)系型查詢中含有不可優(yōu)化的子句時(shí),執(zhí)行計(jì)劃用表掃描來訪問查詢的這個(gè)部分,對于查詢樹中可

11、優(yōu)化的SQLServer子句,則由優(yōu)化器執(zhí)行索引選擇。2.索引選擇對于每個(gè)可優(yōu)化的子句,優(yōu)化器都查看數(shù)據(jù)庫系統(tǒng)表,以確定是否有相關(guān)的索引能用于訪問數(shù)據(jù)。只有當(dāng)索引中的列的1個(gè)前綴與查詢子句中的列完全匹配時(shí),這個(gè)索引才被認(rèn)為是有用的。因?yàn)樗饕歉鶕?jù)列的順序構(gòu)造的,所以要求匹配是精確的匹配。對于分簇索引,原來的數(shù)據(jù)也是根據(jù)索引列順序排序的。想用索引的次要列訪問數(shù)據(jù),就像想在電話本中查找所有姓為某個(gè)姓氏的條目一樣,排序基本上沒有什么用,因?yàn)槟氵€是得查看每一行以確定它是否符合條件。如果1個(gè)子句有可用的索引,那么優(yōu)化器就會(huì)為它確定選擇性。所以在設(shè)計(jì)過程中,要根據(jù)查詢設(shè)計(jì)準(zhǔn)則仔細(xì)檢查所有的查詢,以查詢的優(yōu)

12、化特點(diǎn)為基礎(chǔ)設(shè)計(jì)索引。比較窄的索引具有比較高的效率。對于比較窄的索引來說,每頁上能存放較多的索引行,而且索引的級(jí)別也較少。所以,緩存中能放置更多的索引頁,這樣也減少了I/O操作。SQLServer優(yōu)化器能分析大量的索引和合并可能性。所以與較少的寬索引相比,較多的窄索引能向優(yōu)化器提供更多的選擇。但是不要保留不必要的索引,因?yàn)樗鼈儗⒃黾哟鎯?chǔ)和維護(hù)的開支。對于復(fù)合索引、組合索引或多列索引,SQLServer優(yōu)化器只保留最重要的列的分布統(tǒng)計(jì),這樣,索引的第1列應(yīng)該有很大的選擇性。表上的索引過多會(huì)影響UPDA TE、INSERT和DELETE的性能,因?yàn)樗械乃饕急仨氉鱿鄳?yīng)的調(diào)整。另外,所有的分頁-數(shù)

13、據(jù)庫性能優(yōu)化設(shè)計(jì)方案本文首先討論了基于第三范式的數(shù)據(jù)庫表的基本設(shè)計(jì),著重論述了建立主鍵和索引的策略和方案,然后從數(shù)據(jù)庫表的擴(kuò)展設(shè)計(jì)和庫表對象的放置等角度概述了數(shù)據(jù)庫管理系統(tǒng)的優(yōu)化方案。關(guān)鍵詞:優(yōu)化(Optimizing第三范式(3NF冗余數(shù)據(jù)(Redundant Data索引(Index數(shù)據(jù)分割(Data Partitioning對象放置(Object Placement1 引言數(shù)據(jù)庫優(yōu)化的目標(biāo)無非是避免磁盤I/O瓶頸、減少CPU利用率和減少資源競爭。為了便于讀者閱讀和理解,筆者參閱了Sybase、Informix和Oracle等大型數(shù)據(jù)庫系統(tǒng)參考資料,基于多年的工程實(shí)踐經(jīng)驗(yàn),從基本表設(shè)計(jì)、擴(kuò)

14、展設(shè)計(jì)和數(shù)據(jù)庫表對象放置等角度進(jìn)行討論,著重討論了如何避免磁盤I/O瓶頸和減少資源競爭,相信讀者會(huì)一目了然。2 基于第三范式的基本表設(shè)計(jì)在基于表驅(qū)動(dòng)的信息管理系統(tǒng)(MIS中,基本表的設(shè)計(jì)規(guī)范是第三范式(3NF。第三范式的基本特征是非主鍵屬性只依賴于主鍵屬性?；诘谌妒降臄?shù)據(jù)庫表設(shè)計(jì)具有很多優(yōu)點(diǎn):一是消除了冗余數(shù)據(jù),節(jié)省了磁盤存儲(chǔ)空間;二是有良好的數(shù)據(jù)完整性限制,即基于主外鍵的參照完整限制和基于主鍵的實(shí)體完整性限制,這使得數(shù)據(jù)容易維護(hù),也容易移植和更新;三是數(shù)據(jù)的可逆性好,在做連接(Join查詢或者合并表時(shí)不遺漏、也不重復(fù);四是因消除了冗余數(shù)據(jù)(冗余列,在查詢(Select時(shí)每個(gè)數(shù)據(jù)頁存的數(shù)據(jù)

15、行就多,這樣就有效地減少了邏輯I/O,每個(gè)Cash存的頁面就多,也減少物理I/O;五是對大多數(shù)事務(wù)(Transaction而言,運(yùn)行性能好;六是物理設(shè)計(jì)(Physical Design的機(jī)動(dòng)性較大,能滿足日益增長的用戶需求。在基本表設(shè)計(jì)中,表的主鍵、外鍵、索引設(shè)計(jì)占有非常重要的地位,但系統(tǒng)設(shè)計(jì)人員往往只注重于滿足用戶要求,而沒有從系統(tǒng)優(yōu)化的高度來認(rèn)識(shí)和重視它們。實(shí)際上,它們與系統(tǒng)的運(yùn)行性能密切相關(guān)?，F(xiàn)在從系統(tǒng)數(shù)據(jù)庫優(yōu)化角度討論這些基本概念及其重要意義:(1主鍵(Primary Key:主鍵被用于復(fù)雜的SQL語句時(shí),頻繁地在數(shù)據(jù)訪問中被用到。一個(gè)表只有一個(gè)主鍵。主鍵應(yīng)該有固定值(不能為Null或

16、缺省值,要有相對穩(wěn)定性,不含代碼信息,易訪問。把常用(眾所周知的列作為主鍵才有意義。短主鍵最佳(小于25bytes,主鍵的長短影響索引的大小,索引的大小影響索引頁的大小,從而影響磁盤I/O。主鍵分為自然主鍵和人為主鍵。自然主鍵由實(shí)體的屬性構(gòu)成,自然主鍵可以是復(fù)合性的,在形成復(fù)合主鍵時(shí),主鍵列不能太多,復(fù)合主鍵使得Join*作復(fù)雜化、也增加了外鍵表的大小。人為主鍵是,在沒有合適的自然屬性鍵、或自然屬性復(fù)雜或靈敏度高時(shí),人為形成的。人為主鍵一般是整型值(滿足最小化要求,沒有實(shí)際意義,也略微增加了表的大小;但減少了把它作為外鍵的表的大小。(2外鍵(Foreign Key:外鍵的作用是建立關(guān)系型數(shù)據(jù)庫

17、中表之間的關(guān)系(參照完整性,主鍵只能從獨(dú)立的實(shí)體遷移到非獨(dú)立的實(shí)體,成為后者的一個(gè)屬性,被稱為外鍵。(3索引(Index:利用索引優(yōu)化系統(tǒng)性能是顯而易見的,對所有常用于查詢中的Where子句的列和所有用于排序的列創(chuàng)建索引,可以避免整表掃描或訪問,在不改變表的物理結(jié)構(gòu)的情況下,直接訪問特定的數(shù)據(jù)列,這樣減少數(shù)據(jù)存取時(shí)間;利用索引可以優(yōu)化或排除耗時(shí)的分類*作;把數(shù)據(jù)分散到不同的頁面上,就分散了插入的數(shù)據(jù);主鍵自動(dòng)建立了唯一索引,因此唯一索引也能確保數(shù)據(jù)的唯一性(即實(shí)體完整性;索引碼越小,定位就越直接;新建的索引效能最好,因此定期更新索引非常必要。索引也有代價(jià):有空間開銷,建立它也要花費(fèi)時(shí)間,在進(jìn)行

18、Insert、Delete和Update*作時(shí),也有維護(hù)代價(jià)。索引有兩種:聚族索引和非聚族索引。一個(gè)表只能有一個(gè)聚族索引,可有多個(gè)非聚族索引。使用聚族索引查詢數(shù)據(jù)要比使用非聚族索引快。在建索引前,應(yīng)利用數(shù)據(jù)庫系統(tǒng)函數(shù)估算索引的大小。聚族索引(Clustered Index:聚族索引的數(shù)據(jù)頁按物理有序儲(chǔ)存,占用空間小。選擇策略是,被用于Where子句的列:包括范圍查詢、模糊查詢或高度重復(fù)的列(連續(xù)磁盤掃描;被用于連接Join*作的列;被用于Order by和Group by子句的列。聚族索引不利于插入*作,另外沒有必要用主鍵建聚族索引。非聚族索引(Nonclustered Index:與聚族索引

19、相比,占用空間大,而且效率低。選擇策略是,被用于Where子句的列:包括范圍查詢、模糊查詢(在沒有聚族索引時(shí)、主鍵或外鍵列、點(diǎn)(指針類或小范圍(返回的結(jié)果域小于整表數(shù)據(jù)的20%查詢;被用于連接Join*作的列、主鍵列(范圍查詢;被用于Order by和Group by子句的列;需要被覆蓋的列。對只讀表建多個(gè)非聚族索引有利。索引也有其弊端,一是創(chuàng)建索引要耗費(fèi)時(shí)間,二是索引要占有大量磁盤空間,三是增加了維護(hù)代價(jià)(在修改帶索引的數(shù)據(jù)列時(shí)索引會(huì)減緩修改速度。那么,在哪種情況下不建索引呢?對于小表(數(shù)據(jù)小于5頁、小到中表(不直接訪問單行數(shù)據(jù)或結(jié)果集不用排序、單值域(返回值密集、索引列值太長(大于20bi

20、tys、容易變化的列、高度重復(fù)的列、Null值列,對沒有被用于Where子語句和Join查詢的列都不能建索引。另外,對主要用于數(shù)據(jù)錄入的,盡可能少建索引。當(dāng)然,也要防止建立無效索引,當(dāng)Where語句中多于5個(gè)條件時(shí),維護(hù)索引的開銷大于索引的效益,這時(shí),建立臨時(shí)表存儲(chǔ)有關(guān)數(shù)據(jù)更有效。批量導(dǎo)入數(shù)據(jù)時(shí)的注意事項(xiàng):在實(shí)際應(yīng)用中,大批量的計(jì)算(如電信話單計(jì)費(fèi)用C語言程序做,這種基于主外鍵關(guān)系數(shù)據(jù)計(jì)算而得的批量數(shù)據(jù)(文本文件,可利用系統(tǒng)的自身功能函數(shù)(如Sybase的BCP命令快速批量導(dǎo)入,在導(dǎo)入數(shù)據(jù)庫表時(shí),可先刪除相應(yīng)庫表的索引,這有利于加快導(dǎo)入速度,減少導(dǎo)入時(shí)間。在導(dǎo)入后再重建索引以便優(yōu)化查詢。(4鎖

21、:鎖是并行處理的重要機(jī)制,能保持?jǐn)?shù)據(jù)并發(fā)的一致性,即按事務(wù)進(jìn)行處理;系統(tǒng)利用鎖,保證數(shù)據(jù)完整性。因此,我們避免不了死鎖,但在設(shè)計(jì)時(shí)可以充分考慮如何避免長事務(wù),減少排它鎖時(shí)間,減少在事務(wù)中與用戶的交互,杜絕讓用戶控制事務(wù)的長短;要避免批量數(shù)據(jù)同時(shí)執(zhí)行,尤其是耗時(shí)并用到相同的數(shù)據(jù)表。鎖的征用:一個(gè)表同時(shí)只能有一個(gè)排它鎖,一個(gè)用戶用時(shí),其它用戶在等待。若用戶數(shù)增加,則Server的性能下降,出現(xiàn)“假死”現(xiàn)象。如何避免死鎖呢?從頁級(jí)鎖到行級(jí)鎖,減少了鎖征用;給小表增加無效記錄,從頁級(jí)鎖到行級(jí)鎖沒有影響,若在同一頁內(nèi)競爭有影響,可選擇合適的聚族索引把數(shù)據(jù)分配到不同的頁面;創(chuàng)建冗余表;保持事務(wù)簡短;同一批

22、處理應(yīng)該沒有網(wǎng)絡(luò)交互。(5查詢優(yōu)化規(guī)則:在訪問數(shù)據(jù)庫表的數(shù)據(jù)(Access Data時(shí),要盡可能避免排序(Sort、連接(Join和相關(guān)子查詢*作。經(jīng)驗(yàn)告訴我們,在優(yōu)化查詢時(shí),必須做到:盡可能少的行;避免排序或?yàn)楸M可能少的行排序,若要做大量數(shù)據(jù)排序,最好將相關(guān)數(shù)據(jù)放在臨時(shí)表中*作;用簡單的鍵(列排序,如整型或短字符串排序;避免表內(nèi)的相關(guān)子查詢;避免在Where子句中使用復(fù)雜的表達(dá)式或非起始的子字符串、用長字符串連接;在Where子句中多使用“與”(And連接,少使用“或”(Or連接;利用臨時(shí)數(shù)據(jù)庫。在查詢多表、有多個(gè)連接、查詢復(fù)雜、數(shù)據(jù)要過濾時(shí),可以建臨時(shí)表(索引以減少I/O。但缺點(diǎn)是增加了空

23、間開銷。除非每個(gè)列都有索引支持,否則在有連接的查詢時(shí)分別找出兩個(gè)動(dòng)態(tài)索引,放在工作表中重新排序。3 基本表擴(kuò)展設(shè)計(jì)基于第三范式設(shè)計(jì)的庫表雖然有其優(yōu)越性(見本文第一部分,然而在實(shí)際應(yīng)用中有時(shí)不利于系統(tǒng)運(yùn)行性能的優(yōu)化:如需要部分?jǐn)?shù)據(jù)時(shí)而要掃描整表,許多過程同時(shí)競爭同一數(shù)據(jù),反復(fù)用相同行計(jì)算相同的結(jié)果,過程從多表獲取數(shù)據(jù)時(shí)引發(fā)大量的連接*作,當(dāng)數(shù)據(jù)來源于多表時(shí)的連接*作;這都消耗了磁盤I/O和CPU時(shí)間。尤其在遇到下列情形時(shí),我們要對基本表進(jìn)行擴(kuò)展設(shè)計(jì):許多過程要頻繁訪問一個(gè)表、子集數(shù)據(jù)訪問、重復(fù)計(jì)算和冗余數(shù)據(jù),有時(shí)用戶要求一些過程優(yōu)先或低的響應(yīng)時(shí)間。如何避免這些不利因素呢?根據(jù)訪問的頻繁程度對相關(guān)

24、表進(jìn)行分割處理、存儲(chǔ)冗余數(shù)據(jù)、存儲(chǔ)衍生列、合并相關(guān)表處理,這些都是克服這些不利因素和優(yōu)化系統(tǒng)運(yùn)行的有效途徑。3.1 分割表或儲(chǔ)存冗余數(shù)據(jù)分割表分為水平分割表和垂直分割表兩種。分割表增加了維護(hù)數(shù)據(jù)完整性的代價(jià)。水平分割表:一種是當(dāng)多個(gè)過程頻繁訪問數(shù)據(jù)表的不同行時(shí),水平分割表,并消除新表中的冗余數(shù)據(jù)列;若個(gè)別過程要訪問整個(gè)數(shù)據(jù),則要用連接*作,這也無妨分割表;典型案例是電信話單按月分割存放。另一種是當(dāng)主要過程要重復(fù)訪問部分行時(shí),最好將被重復(fù)訪問的這些行單獨(dú)形成子集表(冗余儲(chǔ)存,這在不考慮磁盤空間開銷時(shí)顯得十分重要;但在分割表以后,增加了維護(hù)難度,要用觸發(fā)器立即更新、或存儲(chǔ)過程或應(yīng)用代碼批量更新,這

25、也會(huì)增加額外的磁盤I/O開銷。垂直分割表(不破壞第三范式,一種是當(dāng)多個(gè)過程頻繁訪問表的不同列時(shí),可將表垂直分成幾個(gè)表,減少磁盤I/O(每行的數(shù)據(jù)列少,每頁存的數(shù)據(jù)行就多,相應(yīng)占用的頁就少,更新時(shí)不必考慮鎖,沒有冗余數(shù)據(jù)。缺點(diǎn)是要在插入或刪除數(shù)據(jù)時(shí)要考慮數(shù)據(jù)的完整性,用存儲(chǔ)過程維護(hù)。另一種是當(dāng)主要過程反復(fù)訪問部分列時(shí),最好將這部分被頻繁訪問的列數(shù)據(jù)單獨(dú)存為一個(gè)子集表(冗余儲(chǔ)存,這在不考慮磁盤空間開銷時(shí)顯得十分重要;但這增加了重疊列的維護(hù)難度,要用觸發(fā)器立即更新、或存儲(chǔ)過程或應(yīng)用代碼批量更新,這也會(huì)增加額外的磁盤I/O開銷。垂直分割表可以達(dá)到最大化利用Cache的目的?？傊?為主要過程分割表的方法

26、適用于:各個(gè)過程需要表的不聯(lián)結(jié)的子集,各個(gè)過程需要表的子集,訪問頻率高的主要過程不需要整表。在主要的、頻繁訪問的主表需要表的子集而其它主要頻繁訪問的過程需要整表時(shí)則產(chǎn)生冗余子集表。注意,在分割表以后,要考慮重新建立索引。3.2 存儲(chǔ)衍生數(shù)據(jù)對一些要做大量重復(fù)性計(jì)算的過程而言,若重復(fù)計(jì)算過程得到的結(jié)果相同(源列數(shù)據(jù)穩(wěn)定,因此計(jì)算結(jié)果也不變,或計(jì)算牽扯多行數(shù)據(jù)需額外的磁盤I/O開銷,或計(jì)算復(fù)雜需要大量的CPU時(shí)間,就考慮存儲(chǔ)計(jì)算結(jié)果(冗余儲(chǔ)存?，F(xiàn)予以分類說明:若在一行內(nèi)重復(fù)計(jì)算,就在表內(nèi)增加列存儲(chǔ)結(jié)果。但若參與計(jì)算的列被更新時(shí),必須要用觸發(fā)器更新這個(gè)新列。若對表按類進(jìn)行重復(fù)計(jì)算,就增加新表(一般而

27、言,存放類和結(jié)果兩列就可以了存儲(chǔ)相關(guān)結(jié)果。但若參與計(jì)算的列被更新時(shí),就必須要用觸發(fā)器立即更新、或存儲(chǔ)過程或應(yīng)用代碼批量更新這個(gè)新表。若對多行進(jìn)行重復(fù)性計(jì)算(如排名次,就在表內(nèi)增加列存儲(chǔ)結(jié)果。但若參與計(jì)算的列被更新時(shí),必須要用觸發(fā)器或存儲(chǔ)過程更新這個(gè)新列?？傊?存儲(chǔ)冗余數(shù)據(jù)有利于加快訪問速度;但違反了第三范式,這會(huì)增加維護(hù)數(shù)據(jù)完整性的代價(jià),必須用觸發(fā)器立即更新、或存儲(chǔ)過程或應(yīng)用代碼批量更新,以維護(hù)數(shù)據(jù)的完整性。3.3 消除昂貴結(jié)合對于頻繁同時(shí)訪問多表的一些主要過程,考慮在主表內(nèi)存儲(chǔ)冗余數(shù)據(jù),即存儲(chǔ)冗余列或衍生列(它不依賴于主鍵,但破壞了第三范式,也增加了維護(hù)難度。在源表的相關(guān)列發(fā)生變化時(shí),必須要

28、用觸發(fā)器或存儲(chǔ)過程更新這個(gè)冗余列。當(dāng)主要過程總同時(shí)訪問兩個(gè)表時(shí)可以合并表,這樣可以減少磁盤I/O*作,但破壞了第三范式,也增加了維護(hù)難度。對父子表和1:1關(guān)系表合并方法不同:合并父子表后,產(chǎn)生冗余表;合并1:1關(guān)系表后,在表內(nèi)產(chǎn)生冗余數(shù)據(jù)。4 數(shù)據(jù)庫對象的放置策略數(shù)據(jù)庫對象的放置策略是均勻地把數(shù)據(jù)分布在系統(tǒng)的磁盤中,平衡I/O訪問,避免I/O瓶頸。訪問分散到不同的磁盤,即使用戶數(shù)據(jù)盡可能跨越多個(gè)設(shè)備,多個(gè)I/O運(yùn)轉(zhuǎn),避免I/O 競爭,克服訪問瓶頸;分別放置隨機(jī)訪問和連續(xù)訪問數(shù)據(jù)。分離系統(tǒng)數(shù)據(jù)庫I/O和應(yīng)用數(shù)據(jù)庫I/O。把系統(tǒng)審計(jì)表和臨時(shí)庫表放在不忙的磁盤上。把事務(wù)日志放在單獨(dú)的磁盤上,減少磁盤

29、I/O開銷,這還有利于在障礙后恢復(fù),提高了系統(tǒng)的安全性。把頻繁訪問的“活性”表放在不同的磁盤上;把頻繁用的表、頻繁做Join*作的表分別放在單獨(dú)的磁盤上,甚至把把頻繁訪問的表的字段放在不同的磁盤上,把訪問分散到不同的磁盤上,避免I/O爭奪;利用段分離頻繁訪問的表及其索引(非聚族的、分離文本和圖像數(shù)據(jù)。段的目的是平衡I/O,避免瓶頸,增加吞吐量,實(shí)現(xiàn)并行掃描,提高并發(fā)度,最大化磁盤的吞吐量。利用邏輯段功能,分別放置“活性”表及其非聚族索引以平衡I/O。當(dāng)然最好利用系統(tǒng)的默認(rèn)段。另外,利用段可以使備份和恢復(fù)數(shù)據(jù)更加靈活,使系統(tǒng)授權(quán)更加靈活。-優(yōu)化措施1、1、調(diào)整數(shù)據(jù)結(jié)構(gòu)的設(shè)計(jì)。這一部分在開發(fā)信息系

30、統(tǒng)之前完成,程序員需要考慮是否使用ORACLE數(shù)據(jù)庫的分區(qū)功能,對于經(jīng)常訪問的數(shù)據(jù)庫表是否需要建立索引等。2、2、調(diào)整應(yīng)用程序結(jié)構(gòu)設(shè)計(jì)。這一部分也是在開發(fā)信息系統(tǒng)之前完成,程序員在這一步需要考慮應(yīng)用程序使用什么樣的體系結(jié)構(gòu),是使用傳統(tǒng)的Client/Server兩層體系結(jié)構(gòu),還是使用Browser/Web/Database的三層體系結(jié)構(gòu)。不同的應(yīng)用程序體系結(jié)構(gòu)要求的數(shù)據(jù)庫資源是不同的。3、3、調(diào)整數(shù)據(jù)庫SQL語句。應(yīng)用程序的執(zhí)行最終將歸結(jié)為數(shù)據(jù)庫中的SQL語句執(zhí)行,因此SQL語句的執(zhí)行效率最終決定了ORACLE數(shù)據(jù)庫的性能。ORACLE公司推薦使用ORACLE語句優(yōu)化器(Oracle Opti

31、mizer和行鎖管理器(row-level manager來調(diào)整優(yōu)化SQL 語句。4、4、調(diào)整服務(wù)器內(nèi)存分配。內(nèi)存分配是在信息系統(tǒng)運(yùn)行過程中優(yōu)化配置的,數(shù)據(jù)庫管理員可以根據(jù)數(shù)據(jù)庫運(yùn)行狀況調(diào)整數(shù)據(jù)庫系統(tǒng)全局區(qū)(SGA區(qū)的數(shù)據(jù)緩沖區(qū)、日志緩沖區(qū)和共享池的大小;還可以調(diào)整程序全局區(qū)(PGA區(qū)的大小。需要注意的是,SGA區(qū)不是越大越好,SGA區(qū)過大會(huì)占用操作系統(tǒng)使用的內(nèi)存而引起虛擬內(nèi)存的頁面交換,這樣反而會(huì)降低系統(tǒng)。5、5、調(diào)整硬盤I/O,這一步是在信息系統(tǒng)開發(fā)之前完成的。數(shù)據(jù)庫管理員可以將組成同一個(gè)表空間的數(shù)據(jù)文件放在不同的硬盤上,做到硬盤之間I/O負(fù)載均衡。6、6、調(diào)整操作系統(tǒng)參數(shù),例如:運(yùn)行在U

32、NIX操作系統(tǒng)上的ORACLE數(shù)據(jù)庫,可以調(diào)整UNIX數(shù)據(jù)緩沖池的大小,每個(gè)進(jìn)程所能使用的內(nèi)存大小等參數(shù)。實(shí)際上,上述數(shù)據(jù)庫優(yōu)化措施之間是相互聯(lián)系的。ORACLE數(shù)據(jù)庫性能惡化表現(xiàn)基本上都是用戶響應(yīng)時(shí)間比較長,需要用戶長時(shí)間的等待。但性能惡化的原因卻是多種多樣的,有時(shí)是多個(gè)因素共同造成了性能惡化的結(jié)果,這就需要數(shù)據(jù)庫管理員有比較全面的計(jì)算機(jī)知識(shí),能夠敏感地察覺到影響數(shù)據(jù)庫性能的主要原因所在。另外,良好的數(shù)據(jù)庫管理工具對于優(yōu)化數(shù)據(jù)庫性能也是很重要的。ORACLE數(shù)據(jù)庫性能優(yōu)化工具常用的數(shù)據(jù)庫性能優(yōu)化工具有:1、1、ORACLE數(shù)據(jù)庫在線數(shù)據(jù)字典,ORACLE在線數(shù)據(jù)字典能夠反映出ORACLE動(dòng)態(tài)

33、運(yùn)行情況,對于調(diào)整數(shù)據(jù)庫性能是很有幫助的。2、2、操作系統(tǒng)工具,例如UNIX操作系統(tǒng)的vmstat,iostat等命令可以查看到系統(tǒng)系統(tǒng)級(jí)內(nèi)存和硬盤I/O的使用情況,這些工具對于管理員弄清出系統(tǒng)瓶頸出現(xiàn)在什么地方有時(shí)候很有用。3、3、SQL語言跟蹤工具(SQL TRACE FACILITY,SQL語言跟蹤工具可以記錄SQL語句的執(zhí)行情況,管理員可以使用虛擬表來調(diào)整實(shí)例,使用SQL語句跟蹤文件調(diào)整應(yīng)用程序性能。SQL語言跟蹤工具將結(jié)果輸出成一個(gè)操作系統(tǒng)的文件,管理員可以使用TKPROF工具查看這些文件。4、4、ORACLE Enterprise Manager(OEM,這是一個(gè)圖形的用戶管理界面

34、,用戶可以使用它方便地進(jìn)行數(shù)據(jù)庫管理而不必記住復(fù)雜的ORACLE數(shù)據(jù)庫管理的命令。5、5、EXPLAIN PLANSQL語言優(yōu)化命令,使用這個(gè)命令可以幫助程序員寫出高效的SQL語言。ORACLE數(shù)據(jù)庫的系統(tǒng)性能評估信息系統(tǒng)的類型不同,需要關(guān)注的數(shù)據(jù)庫參數(shù)也是不同的。數(shù)據(jù)庫管理員需要根據(jù)自己的信息系統(tǒng)的類型著重考慮不同的數(shù)據(jù)庫參數(shù)。1、1、在線事務(wù)處理信息系統(tǒng)(OLTP,這種類型的信息系統(tǒng)一般需要有大量的Insert、Update 操作,典型的系統(tǒng)包括民航機(jī)票發(fā)售系統(tǒng)、銀行儲(chǔ)蓄系統(tǒng)等。OLTP系統(tǒng)需要保證數(shù)據(jù)庫的并發(fā)性、可靠性和最終用戶的速度,這類系統(tǒng)使用的ORACLE數(shù)據(jù)庫需要主要考慮下述參數(shù)

35、:l l 數(shù)據(jù)庫回滾段是否足夠?l l 是否需要建立ORACLE數(shù)據(jù)庫索引、聚集、散列?l l 系統(tǒng)全局區(qū)(SGA大小是否足夠?l l SQL語句是否高效?2、2、數(shù)據(jù)倉庫系統(tǒng)(Data Warehousing,這種信息系統(tǒng)的主要任務(wù)是從ORACLE的海量數(shù)據(jù)中進(jìn)行查詢,得到數(shù)據(jù)之間的某些規(guī)律。數(shù)據(jù)庫管理員需要為這種類型的ORACLE數(shù)據(jù)庫著重考慮下述參數(shù):l l 是否采用B*-索引或者bitmap索引?l l 是否采用并行SQL查詢以提高查詢效率?l l 是否采用PL/SQL函數(shù)編寫存儲(chǔ)過程?l l 有必要的話,需要建立并行數(shù)據(jù)庫提高數(shù)據(jù)庫的查詢效率SQL語句的調(diào)整原則SQL語言是一種靈活的

36、語言,相同的功能可以使用不同的語句來實(shí)現(xiàn),但是語句的執(zhí)行效率是很不相同的。程序員可以使用EXPLAIN PLAN語句來比較各種實(shí)現(xiàn)方案,并選出最優(yōu)的實(shí)現(xiàn)方案?？偟脕碇v,程序員寫SQL語句需要滿足考慮如下規(guī)則:1、1、盡量使用索引。試比較下面兩條SQL語句:語句A:SELECT dname, deptno FROM dept WHERE deptno NOT IN(SELECT deptno FROM emp;語句B:SELECT dname, deptno FROM dept WHERE NOT EXISTS(SELECT deptno FROM emp WHERE dept.deptno =

37、 emp.deptno;這兩條查詢語句實(shí)現(xiàn)的結(jié)果是相同的,但是執(zhí)行語句A的時(shí)候,ORACLE會(huì)對整個(gè)emp表進(jìn)行掃描,沒有使用建立在emp表上的deptno索引,執(zhí)行語句B的時(shí)候,由于在子查詢中使用了聯(lián)合查詢,ORACLE只是對emp表進(jìn)行的部分?jǐn)?shù)據(jù)掃描,并利用了deptno列的索引,所以語句B的效率要比語句A的效率高一些。2、2、選擇聯(lián)合查詢的聯(lián)合次序?？紤]下面的例子:SELECT stuff FROM taba a, tabb b, tabc cWHERE a.acol between :alow and :ahighAND b.bcol between :blow and :bhighA

38、ND c.ccol between :clow and :chighAND a.key1 = b.key1AMD a.key2 = c.key2;這個(gè)SQL例子中,程序員首先需要選擇要查詢的主表,因?yàn)橹鞅硪M(jìn)行整個(gè)表數(shù)據(jù)的掃描,所以主表應(yīng)該數(shù)據(jù)量最小,所以例子中表A的acol列的范圍應(yīng)該比表B和表C相應(yīng)列的范圍小。3、3、在子查詢中慎重使用IN或者NOT IN語句,使用where (NOT exists的效果要好的多。4、4、慎重使用視圖的聯(lián)合查詢,尤其是比較復(fù)雜的視圖之間的聯(lián)合查詢。一般對視圖的查詢最好都分解為對數(shù)據(jù)表的直接查詢效果要好一些。5、5、可以在參數(shù)文件中設(shè)置SHARED_POOL

39、_RESERVED_SIZE參數(shù),這個(gè)參數(shù)在SGA 共享池中保留一個(gè)連續(xù)的內(nèi)存空間,連續(xù)的內(nèi)存空間有益于存放大的SQL程序包。6、6、ORACLE公司提供的DBMS_SHARED_POOL程序可以幫助程序員將某些經(jīng)常使用的存儲(chǔ)過程“釘”在SQL區(qū)中而不被換出內(nèi)存,程序員對于經(jīng)常使用并且占用內(nèi)存很多的存儲(chǔ)過程“釘”到內(nèi)存中有利于提高最終用戶的響應(yīng)時(shí)間。CPU 參數(shù)的調(diào)整 CPU 是服務(wù)器的一項(xiàng)重要資源，服務(wù)器良好的工作狀態(tài)是在工作高峰時(shí) CPU 的使用率在 90以上。如果空閑時(shí)間 CPU 使用率就在 90以上，說明服務(wù)器缺乏 CPU 資源，如果工 作高峰時(shí) CPU 使用率仍然很低，說明服務(wù)器 C

40、PU 資源還比較富余。 使用操作相同命令可以看到 CPU 的使用情況，一般 UNIX 操作系統(tǒng)的服務(wù)器，可以使用 sar u 命令查看 CPU 的使用率， 操作系統(tǒng)的服務(wù)器， NT 可以使用 NT 的性能管理器來查看 CPU 的使用率。 數(shù)據(jù)庫管理員可以通過查看 v$sysstat 數(shù)據(jù)字典中“CPU used by this session”統(tǒng)計(jì)項(xiàng)得知 ORACLE 數(shù)據(jù)庫使用的 CPU 時(shí)間，查看“OS User level CPU time”統(tǒng)計(jì)項(xiàng)得知操作系統(tǒng)用 戶態(tài)下的 CPU 時(shí)間，查看“OS System call CPU time”統(tǒng)計(jì)項(xiàng)得知操作系統(tǒng)系統(tǒng)態(tài)下的 CPU 時(shí)間，操作

41、系統(tǒng)總的 CPU 時(shí)間就是用戶態(tài)和系統(tǒng)態(tài)時(shí)間之和，如果 ORACLE 數(shù)據(jù)庫使用的 CPU 時(shí)間占操作系統(tǒng)總的 CPU 時(shí)間 90以上， 說明服務(wù)器 CPU 基本上被 ORACLE 數(shù)據(jù)庫 使用著，這是合理，反之，說明服務(wù)器 CPU 被其它程序占用過多，ORACLE 數(shù)據(jù)庫無法得 到更多的 CPU 時(shí)間。 數(shù)據(jù)庫管理員還可以通過查看 v$sesstat 數(shù)據(jù)字典來獲得當(dāng)前連接 ORACLE 數(shù)據(jù)庫各個(gè)會(huì)話 占用的 CPU 時(shí)間，從而得知什么會(huì)話耗用服務(wù)器 CPU 比較多。 出現(xiàn) CPU 資源不足的情況是很多的：SQL 語句的重解析、低效率的 SQL 語句、鎖沖突都會(huì) 引起 CPU 資源不足。

42、1、數(shù)據(jù)庫管理員可以執(zhí)行下述語句來查看 SQL 語句的解析情況： SELECT * FROM V$SYSSTAT WHERE NAME IN ('parse time cpu', 'parse time elapsed', 'parse count (hard' 這里 parse time cpu 是系統(tǒng)服務(wù)時(shí)間，parse time elapsed 是響應(yīng)時(shí)間，用戶等待時(shí)間 waite time = parse time elapsed parse time cpu 由此可以得到用戶 SQL 語句平均解析等待時(shí)間waite time / par

43、se count。 這個(gè)平均等待時(shí)間 應(yīng)該接近于 0，如果平均解析等待時(shí)間過長，數(shù)據(jù)庫管理員可以通過下述語句 SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA ORDER BY PARSE_CALLS; 來發(fā)現(xiàn)是什么 SQL 語句解析效率比較低。程序員可以優(yōu)化這些語句，或者增加 ORACLE 參 數(shù) SESSION_CACHED_CURSORS 的值。 2、數(shù)據(jù)庫管理員還可以通過下述語句： SELECT BUFFER_GETS, EXECUTIONS, SQL_TEXT FROM V$SQLAREA; 查看低效率的 SQL 語句，優(yōu)

44、化這些語句也有助于提高 CPU 的利用率。 3、 數(shù)據(jù)庫管理員可以通過 v$system_event 數(shù)據(jù)字典中的 3、 “l(fā)atch free” 統(tǒng)計(jì)項(xiàng)查看 ORACLE 數(shù)據(jù)庫的沖突情況，如果沒有沖突的話，latch free 查詢出來沒有結(jié)果。如果沖突太大的話， 數(shù)據(jù)庫管理員可以降低 spin_count 參數(shù)值，來消除高的 CPU 使用率。 內(nèi)存參數(shù)的調(diào)整 內(nèi)存參數(shù)的調(diào)整主要是指 ORACLE 數(shù)據(jù)庫的系統(tǒng)全局區(qū)（SGA）的調(diào)整。SGA 主要由三部 分構(gòu)成：共享池、數(shù)據(jù)緩沖區(qū)、日志緩沖區(qū)。 1、 1、 共享池由兩部分構(gòu)成：共享 SQL 區(qū)和數(shù)據(jù)字典緩沖區(qū)，共享 SQL 區(qū)是存放用 戶

45、SQL 命令的區(qū)域，數(shù)據(jù)字典緩沖區(qū)存放數(shù)據(jù)庫運(yùn)行的動(dòng)態(tài)信息。數(shù)據(jù)庫管理員通過執(zhí)行 下述語句： select (sum(pins - reloads / sum(pins "Lib Cache" from v$librarycache; 來查看共享 SQL 區(qū)的使用率。這個(gè)使用率應(yīng)該在 90以上，否則需要增加共享池的大小。 數(shù)據(jù)庫管理員還可以執(zhí)行下述語句： select (sum(gets - getmisses - usage - fixed / sum(gets "Row Cache" from v$rowcache; 查看數(shù)據(jù)字典緩沖區(qū)的使用率，這個(gè)使用率也應(yīng)該在 90以上，否則需要增加共享池的大 小。 2、 2、 數(shù)據(jù)緩沖區(qū)。數(shù)據(jù)庫管理員可以通過下述語句： SELECT name, value gets','physical reads' FROM v$sysstat WHERE name IN ('db block gets', 'consistent 來查看數(shù)據(jù)庫數(shù)據(jù)緩沖區(qū)的使用情況。 查詢出來的結(jié)果可以計(jì)算出來數(shù)據(jù)緩沖區(qū)的使用命中 率1 - ( physical reads