編者按：數(shù)據(jù)庫(kù)性能優(yōu)化和數(shù)據(jù)庫(kù)管理系統(tǒng)密切相關(guān)，不同的數(shù)據(jù)庫(kù)管理系統(tǒng)在具體操作上有很大不同。繼本報(bào)連續(xù)在2003年第48期、49期上刊登《Sybase數(shù)據(jù)庫(kù)性能調(diào)優(yōu)》和《Oracle服務(wù)器性能調(diào)整攻略》，分別討論了Sybase和Oracle數(shù)據(jù)庫(kù)管理系統(tǒng)以后，本期我們將具體介紹SQL Server數(shù)據(jù)庫(kù)的性能優(yōu)化方法。

數(shù)據(jù)庫(kù)是企業(yè)信息的核心，其應(yīng)用水平的高低直接影響到企業(yè)管理水平。選擇了一個(gè)高性能的數(shù)據(jù)庫(kù)產(chǎn)品不等于就有一個(gè)好的數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)，如果數(shù)據(jù)庫(kù)系統(tǒng)設(shè)計(jì)不合理，不僅會(huì)增加客戶端和服務(wù)器端程序的編程和維護(hù)的難度，而且還會(huì)影響系統(tǒng)實(shí)際運(yùn)行的性能。一般來(lái)講，在一個(gè)管理信息系統(tǒng)的分析、設(shè)計(jì)、測(cè)試和試運(yùn)行階段，因?yàn)閿?shù)據(jù)量較小，設(shè)計(jì)人員和測(cè)試人員往往只注意到功能的實(shí)現(xiàn)，而很少會(huì)注意到性能的不足，等到系統(tǒng)投入實(shí)際運(yùn)行一段時(shí)間后，才發(fā)現(xiàn)系統(tǒng)的性能在降低，這時(shí)再來(lái)考慮提高系統(tǒng)性能則要花費(fèi)更多的人力、物力，而其最終結(jié)果就是給整個(gè)系統(tǒng)又打上了一個(gè)補(bǔ)丁，所以設(shè)計(jì)階段是優(yōu)化的重點(diǎn)。本文以SQL Server數(shù)據(jù)庫(kù)為例，從以下幾個(gè)方面討論如何實(shí)現(xiàn)數(shù)據(jù)庫(kù)系統(tǒng)的性能優(yōu)化。

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

實(shí)現(xiàn)SQL Server數(shù)據(jù)庫(kù)的優(yōu)化，首先要有一個(gè)好的數(shù)據(jù)庫(kù)設(shè)計(jì)方案。在實(shí)際工作中，許多SQL Server方案往往是由于數(shù)據(jù)庫(kù)設(shè)計(jì)得不好導(dǎo)致性能很差。實(shí)現(xiàn)良好的數(shù)據(jù)庫(kù)設(shè)計(jì)必須考慮這些問(wèn)題:

1. 邏輯數(shù)據(jù)庫(kù)規(guī)范化問(wèn)題

一般來(lái)說(shuō)，邏輯數(shù)據(jù)庫(kù)設(shè)計(jì)會(huì)滿足規(guī)范化的前3級(jí)標(biāo)準(zhǔn)：

第1規(guī)范：沒(méi)有重復(fù)的組或多值的列;

第2規(guī)范: 每個(gè)非關(guān)鍵字段必須依賴于主關(guān)鍵字，不能依賴于一個(gè)組合式主關(guān)鍵字的某些組成部分;

第3規(guī)范: 一個(gè)非關(guān)鍵字段不能依賴于另一個(gè)非關(guān)鍵字段。

遵守這些規(guī)則的數(shù)據(jù)庫(kù)設(shè)計(jì)會(huì)產(chǎn)生較少的列和更多的表，因而也就減少了數(shù)據(jù)冗余，也減少了用于存儲(chǔ)數(shù)據(jù)的頁(yè)。

2. 生成物理數(shù)據(jù)庫(kù)

要想正確選擇基本物理實(shí)現(xiàn)策略，必須了解和利用好數(shù)據(jù)庫(kù)訪問(wèn)格式和硬件資源的操作特點(diǎn)，特別是內(nèi)存和磁盤子系統(tǒng)I/O。以下是一些常用技巧：

與每個(gè)表列相關(guān)的數(shù)據(jù)類型應(yīng)該反映數(shù)據(jù)所需的最小存儲(chǔ)空間，特別是對(duì)于被索引的列更是如此。比如能使用smallint類型就不要用integer類型，這樣索引字段可以被更快地讀取，而且可以在一個(gè)數(shù)據(jù)頁(yè)上放置更多的數(shù)據(jù)行，因而也就減少了I/O操作。

把一個(gè)表放在某個(gè)物理設(shè)備上，再通過(guò)SQL Server的段把它的不分簇索引放在一個(gè)不同的物理設(shè)備上，這樣能提高性能。尤其是系統(tǒng)采用了多個(gè)智能型磁盤控制器和數(shù)據(jù)分離技術(shù)的情況下，這樣做的好處更加明顯。

用SQL Server段把一個(gè)頻繁使用的大表分割開，并放在多個(gè)單獨(dú)的智能型磁盤控制器的數(shù)據(jù)庫(kù)設(shè)備上，這樣也可以提高性能。因?yàn)橛卸鄠€(gè)磁頭在查找，所以數(shù)據(jù)分離也能提高性能。

用SQL Server段把文本或圖像列的數(shù)據(jù)存放在一個(gè)單獨(dú)的物理設(shè)備上可以提高性能。一個(gè)專用的智能型的控制器能進(jìn)一步提高性能。

應(yīng)用系統(tǒng)設(shè)計(jì)

在應(yīng)用系統(tǒng)的設(shè)計(jì)中，要著重考慮以下幾點(diǎn)：

1．合理使用索引

索引是數(shù)據(jù)庫(kù)中重要的數(shù)據(jù)結(jié)構(gòu)，它的根本目的就是提高查詢效率。索引的使用要恰到好處，其使用原則如下：

在經(jīng)常進(jìn)行連接，但是沒(méi)有指定為外鍵的列上建立索引，而不經(jīng)常連接的字段則由優(yōu)化器自動(dòng)生成索引；在頻繁進(jìn)行排序或分組（即進(jìn)行g(shù)roup by或order by操作）的列上建立索引；在條件表達(dá)式中經(jīng)常用到的不同值較多的列上建立索引，在不同值少的列上不要建立索引。比如在雇員表的“性別列上只有“男與“女兩個(gè)不同值，因此就無(wú)必要建立索引。如果建立索引不但不會(huì)提高查詢效率，反而會(huì)嚴(yán)重降低更新速度。 如果待排序的列有多個(gè)，可以在這些列上建立復(fù)合索引。

2. 避免或簡(jiǎn)化排序

應(yīng)當(dāng)盡量簡(jiǎn)化或避免對(duì)大型表進(jìn)行重復(fù)的排序。當(dāng)能夠利用索引自動(dòng)以適當(dāng)?shù)拇涡虍a(chǎn)生輸出時(shí)，優(yōu)化器就避免了排序這個(gè)步驟。為了避免不必要的排序，就要正確地增建索引，合理地合并數(shù)據(jù)庫(kù)表（盡管有時(shí)可能影響表的規(guī)范化，但相對(duì)于效率的提高是值得的）。如果排序不可避免，那么應(yīng)當(dāng)試圖簡(jiǎn)化它，如縮小排序的列的范圍等。

3．消除對(duì)大型表行數(shù)據(jù)的順序存取

在嵌套查詢中，表的順序存取對(duì)查詢效率可能產(chǎn)生致命的影響。我們有時(shí)可以使用并集來(lái)避免順序存取。盡管也許在所有的檢查列上都有索引，但某些形式的where子句會(huì)強(qiáng)迫優(yōu)化器使用順序存取，這一點(diǎn)也應(yīng)注意。

4. 避免相關(guān)子查詢

如果一個(gè)列同時(shí)在主查詢和where子句中出現(xiàn)，很可能當(dāng)主查詢中的列值改變之后，子查詢必須重新查詢一次。而且查詢嵌套層次越多，效率越低，因此應(yīng)當(dāng)盡量避免子查詢。如果子查詢不可避免，那么要在子查詢中過(guò)濾掉盡可能多的行。

5．避免困難的正規(guī)表達(dá)式

Mathes和Like關(guān)鍵字支持通配符匹配，但這種匹配特別耗時(shí)。例如：SELECT * FROM customer WHERE zipcode LIKE “98_ _ _，即使在zipcode字段上已建立了索引，在這種情況下也還是采用順序掃描的方式。如果把語(yǔ)句改為：SELECT * FROM customer WHERE zipcode >“98000，在執(zhí)行查詢時(shí)就會(huì)利用索引來(lái)查詢，顯然會(huì)大大提高速度。

6．使用臨時(shí)表加速查詢

把表的一個(gè)子集進(jìn)行排序并創(chuàng)建臨時(shí)表，有時(shí)能加速查詢。它有助于避免多重排序操作，而且在其他方面還能簡(jiǎn)化優(yōu)化器的工作。臨時(shí)表中的行要比主表中的行少，而且物理順序就是所要求的順序，減少了磁盤I/O，所以查詢工作量可以得到大幅減少。但要注意，臨時(shí)表創(chuàng)建后不會(huì)反映主表的修改。在主表中數(shù)據(jù)頻繁修改的情況下，注意不要丟失數(shù)據(jù)。

操作系統(tǒng)相關(guān)優(yōu)化

操作系統(tǒng)性能的好壞直接影響數(shù)據(jù)庫(kù)的使用性能，如果操作系統(tǒng)存在問(wèn)題，如CPU過(guò)載、過(guò)度內(nèi)存交換、磁盤I/O瓶頸等，在這種情況下，單純進(jìn)行數(shù)據(jù)庫(kù)內(nèi)部性能調(diào)整是不會(huì)改善系統(tǒng)性能的。我們可以通過(guò)Windows NT的系統(tǒng)監(jiān)視器(System Monitor)來(lái)監(jiān)控各種設(shè)備，發(fā)現(xiàn)性能瓶頸。

CPU 一種常見(jiàn)的性能問(wèn)題就是缺乏處理能力。系統(tǒng)的處理能力是由系統(tǒng)的CPU數(shù)量、類型和速度決定的。如果系統(tǒng)沒(méi)有足夠的CPU處理能力，它就不能足夠快地處理事務(wù)以滿足需要。我們可以使用System Monitor確定CPU的使用率，如果以75%或更高的速率長(zhǎng)時(shí)間運(yùn)行，就可能碰到了CPU瓶頸問(wèn)題，這時(shí)應(yīng)該升級(jí)CPU。但是升級(jí)前必須監(jiān)視系統(tǒng)的其他特性，如果是因?yàn)镾QL語(yǔ)句效率非常低，優(yōu)化語(yǔ)句就有助于解決較低的CPU利用率。而當(dāng)確定需要更強(qiáng)的處理能力，可以添加CPU或者用更快的CPU 替換。

內(nèi)存 SQL Server可使用的內(nèi)存量是SQL Server性能最關(guān)鍵因素之一。而內(nèi)存同I/O子系統(tǒng)的關(guān)系也是一個(gè)非常重要的因素。例如，在I/O操作頻繁的系統(tǒng)中，SQL Server用來(lái)緩存數(shù)據(jù)的可用內(nèi)存越多，必須執(zhí)行的物理I/O也就越少。這是因?yàn)閿?shù)據(jù)將從數(shù)據(jù)緩存中讀取而不是從磁盤讀取。同樣，內(nèi)存量的不足會(huì)引起明顯的磁盤讀寫瓶頸，因?yàn)橄到y(tǒng)緩存能力不足會(huì)引起更多的物理磁盤I/O。

可以利用System Monitor檢查SQL Server的Buffer Cache Hit Ratio計(jì)數(shù)器，如果命中率經(jīng)常低于90%，就應(yīng)該添加更多的內(nèi)存。

I/O子系統(tǒng) 由I/O子系統(tǒng)發(fā)生的瓶頸問(wèn)題是數(shù)據(jù)庫(kù)系統(tǒng)可能遇到的最常見(jiàn)的同硬件有關(guān)的問(wèn)題。配置很差的I/O子系統(tǒng)引起性能問(wèn)題的嚴(yán)重程度僅次于編寫很差的SQL語(yǔ)句。I/O子系統(tǒng)問(wèn)題是這樣產(chǎn)生的，一個(gè)磁盤驅(qū)動(dòng)器能夠執(zhí)行的I/O操作是有限的，一般一個(gè)普通的磁盤驅(qū)動(dòng)器每秒只能處理85次I/O操作，如果磁盤驅(qū)動(dòng)器超載，到這些磁盤驅(qū)動(dòng)器的I/O操作就要排隊(duì)，SQL的I/O延遲將很長(zhǎng)。這可能會(huì)使鎖持續(xù)的時(shí)間更長(zhǎng)，或者使線程在等待資源的過(guò)程中保持空閑狀態(tài)，其結(jié)果就是整個(gè)系統(tǒng)的性能受到影響。

解決I/O子系統(tǒng)有關(guān)的問(wèn)題也許是最容易的，多數(shù)情況下，增加磁盤驅(qū)動(dòng)器就可以解決這個(gè)性能問(wèn)題。

當(dāng)然，影響性能的因素很多，而應(yīng)用又各不相同，找出一個(gè)通用的優(yōu)化方案是很困難的，只能是在系統(tǒng)開發(fā)和維護(hù)的過(guò)程中針對(duì)運(yùn)行的具體情況，不斷加以調(diào)整。