ＳＱＬ?。樱澹颍觯澹驍?shù)據(jù)庫(kù)性能優(yōu)化

梁　銘

[摘要]從數(shù)據(jù)庫(kù)設(shè)計(jì)人員的角度出發(fā)討論SQL Server數(shù)據(jù)庫(kù)性能優(yōu)化的問(wèn)題，非常詳細(xì)地從數(shù)據(jù)庫(kù)的邏輯設(shè)計(jì)到物理設(shè)計(jì)，再到數(shù)據(jù)庫(kù)查詢?nèi)齻€(gè)層次詳細(xì)的描述數(shù)據(jù)庫(kù)設(shè)計(jì)中性能優(yōu)化的實(shí)現(xiàn)。

[關(guān)鍵詞]數(shù)據(jù)庫(kù)設(shè)計(jì) SQL Server 優(yōu)化

中圖分類號(hào)：TP3文獻(xiàn)標(biāo)識(shí)碼：A文章編號(hào)：1671－7597（2009）0120079－01

數(shù)據(jù)庫(kù)系統(tǒng)是企業(yè)整個(gè)管理信息系統(tǒng)的核心和基礎(chǔ)，它的任務(wù)就是把系統(tǒng)中大量的數(shù)據(jù)按一定模型組織起來(lái)，以便及時(shí)、準(zhǔn)確地提供給用戶使用。

設(shè)計(jì)一個(gè)數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)似乎并不難，但是要想使系統(tǒng)達(dá)到最優(yōu)化的性能并不是一件容易的事。在開(kāi)發(fā)工具、數(shù)據(jù)庫(kù)設(shè)計(jì)、應(yīng)用程序的結(jié)構(gòu)、查詢?cè)O(shè)計(jì)、接口選擇等方面有多種選擇?？傮w來(lái)看，要提高數(shù)據(jù)庫(kù)的應(yīng)用系統(tǒng)運(yùn)行效率，必須從數(shù)據(jù)庫(kù)系統(tǒng)級(jí)優(yōu)化、數(shù)據(jù)庫(kù)設(shè)計(jì)級(jí)優(yōu)化、程序?qū)崿F(xiàn)級(jí)優(yōu)化這三個(gè)層次上同時(shí)下工夫。本文主要以SQL Server為例，從后臺(tái)數(shù)據(jù)庫(kù)的設(shè)計(jì)角度討論數(shù)據(jù)庫(kù)應(yīng)用程序性能優(yōu)化技巧。數(shù)據(jù)庫(kù)性能的優(yōu)化主要從以下幾個(gè)方面入手。

一、邏輯數(shù)據(jù)庫(kù)設(shè)計(jì)的規(guī)范化問(wèn)題

合理運(yùn)用規(guī)范化設(shè)計(jì)邏輯數(shù)據(jù)庫(kù)。規(guī)范化是數(shù)據(jù)庫(kù)設(shè)計(jì)的基本指導(dǎo)原則。我們知道，從最基本的1NF到2NF、3NF是從不同的層次描述了關(guān)系中存在的函數(shù)依賴關(guān)系，那么我們?cè)谠O(shè)計(jì)數(shù)據(jù)庫(kù)的時(shí)候范式的級(jí)別到底怎么來(lái)把握呢？范式級(jí)別越高邏輯數(shù)據(jù)庫(kù)就越好嗎？下面我們針對(duì)這兩個(gè)問(wèn)題進(jìn)行論述。

數(shù)據(jù)庫(kù)的目的是進(jìn)行數(shù)據(jù)處理的，它是為應(yīng)用系統(tǒng)服務(wù)的。一個(gè)應(yīng)用系統(tǒng)是為了解決現(xiàn)實(shí)中生產(chǎn)、生活需要的，所以，我們首先必須明白的一個(gè)問(wèn)題就是數(shù)據(jù)庫(kù)的設(shè)計(jì)必須緊密?chē)@實(shí)際的應(yīng)用需求，應(yīng)用需求包括功能需求，性能需求，甚至用戶的操作需求。功能需求解決的是系統(tǒng)需要那些數(shù)據(jù)；而性能需求和用戶的操作需求涉及的就是這些數(shù)據(jù)如何科學(xué)、合理的組織的問(wèn)題。所以數(shù)據(jù)庫(kù)的邏輯設(shè)計(jì)必須要解決好這兩個(gè)問(wèn)題。由于我們討論的是數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)的優(yōu)化問(wèn)題，那么后者就是我們要重點(diǎn)討論的。

數(shù)據(jù)庫(kù)系統(tǒng)性能要求是什么是決定數(shù)據(jù)庫(kù)設(shè)計(jì)規(guī)范化層次的一個(gè)重要依據(jù)。系統(tǒng)的需求分析階段我們必須仔細(xì)分析系統(tǒng)的性能要求。特別是數(shù)據(jù)庫(kù)操作性能要求。

另外，數(shù)據(jù)庫(kù)的邏輯設(shè)計(jì)必須考慮到用戶的操作要求。也就是業(yè)務(wù)處理的流程要求和程序界面設(shè)計(jì)要求。我們知道，數(shù)據(jù)庫(kù)的設(shè)計(jì)是軟件系統(tǒng)需求分析完成以后進(jìn)行的，而程序界面設(shè)計(jì)是在數(shù)據(jù)庫(kù)設(shè)計(jì)之后完成的。那么數(shù)據(jù)庫(kù)的設(shè)計(jì)過(guò)程必須在充分基于需求分析的基礎(chǔ)上、同時(shí)充分考慮后續(xù)程序界面的設(shè)計(jì)來(lái)完成。如果完全脫離用戶的操作要求而從純粹追求高級(jí)別規(guī)范化角度去設(shè)計(jì)數(shù)據(jù)庫(kù)是不合理的。這就是“功能第一、性能第二”的原則。一般來(lái)講對(duì)于事務(wù)型數(shù)據(jù)庫(kù)來(lái)說(shuō)滿足3NF的關(guān)系既能滿足用戶的應(yīng)用需求，也能滿足規(guī)范化普遍要求。但是針對(duì)具體的關(guān)系還得進(jìn)一步具體分析。

二、物理數(shù)據(jù)庫(kù)生成策略

數(shù)據(jù)庫(kù)優(yōu)化的目標(biāo)無(wú)非是避免磁盤(pán)I/O瓶頸、減少CPU利用率和減少資源競(jìng)爭(zhēng)，提高數(shù)據(jù)庫(kù)的讀寫(xiě)效率?；谟布奈锢頂?shù)據(jù)庫(kù)的性能自然離不開(kāi)高性能的硬件支持，特別是需要大容量?jī)?nèi)存和一個(gè)好的磁盤(pán)I/O子系統(tǒng)，智能型SCSI-2磁盤(pán)控制器或磁盤(pán)組控制器是不錯(cuò)的選擇。但是從軟件(DBMS)角度，SQL SERVER數(shù)據(jù)庫(kù)的實(shí)施過(guò)程中以下準(zhǔn)則是我們必須遵循的：

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

2．盡可能地把數(shù)據(jù)庫(kù)的所有基本表分布到多個(gè)不同的磁盤(pán)系統(tǒng)。尤其是系統(tǒng)采用了多個(gè)智能型磁盤(pán)控制器和數(shù)據(jù)分離技術(shù)的情況下，這樣做可以明顯的提高數(shù)據(jù)的操作性能。

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

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

5．合理定義主鍵與外鍵，合理定義索引。在基本表設(shè)計(jì)中，表的主鍵、外鍵、索引設(shè)計(jì)占有非常重要的地位，但系統(tǒng)設(shè)計(jì)人員往往只注重于滿足用戶要求，而沒(méi)有從系統(tǒng)優(yōu)化的高度來(lái)認(rèn)識(shí)和重視它們。實(shí)際上，它們與系統(tǒng)的運(yùn)行性能密切相關(guān)?，F(xiàn)在從系統(tǒng)數(shù)據(jù)庫(kù)優(yōu)化角度討論主鍵和索引的問(wèn)題：

（1）主鍵(Primary Key)：主鍵被用于復(fù)雜的SQL語(yǔ)句時(shí)，頻繁地在數(shù)據(jù)訪問(wèn)中被用到。一個(gè)表只有一個(gè)主鍵。設(shè)計(jì)主鍵時(shí)短主鍵最佳（小于25bytes），主鍵的長(zhǎng)短影響索引的大小，索引的大小影響索引頁(yè)的大小，從而影響磁盤(pán)I/O。如果使用復(fù)合主鍵，要求主鍵列不能太多，復(fù)合主鍵使得Join操作復(fù)雜化、也增加了外鍵表的大小。

（2）索引(Index)：利用索引優(yōu)化系統(tǒng)性能是顯而易見(jiàn)的，索引的主要優(yōu)點(diǎn)是對(duì)所有常用于查詢中的Where子句的列和所有用于排序的列創(chuàng)建索引，可以避免整表掃描或訪問(wèn)，在不改變表的物理結(jié)構(gòu)的情況下，直接訪問(wèn)特定的數(shù)據(jù)列，這樣減少數(shù)據(jù)存取時(shí)間；利用索引可以優(yōu)化或排除耗時(shí)的分類操作。索引碼越小，定位就越直接，索引性能越好。在進(jìn)行Insert、Delete和Update操作時(shí)，必須對(duì)索引進(jìn)行維護(hù)，所以定期更新索引非常必要。當(dāng)然索引也要付出代價(jià)，一是增加存儲(chǔ)空間開(kāi)銷(xiāo)，二是建立索引也要花費(fèi)時(shí)間。所以一個(gè)基本表建立索引的多少、索引碼的選擇（除聚集索引外）就非常關(guān)鍵。

三、查詢優(yōu)化技巧討論

查詢是數(shù)據(jù)庫(kù)的一個(gè)最為重要的操作，查詢的優(yōu)化對(duì)整個(gè)數(shù)據(jù)庫(kù)系統(tǒng)的性能來(lái)講最為關(guān)鍵。一個(gè)性能優(yōu)良的數(shù)據(jù)庫(kù)系統(tǒng)必然在查詢優(yōu)化方面做得很優(yōu)秀。

Microsoft SQL Server數(shù)據(jù)庫(kù)內(nèi)核用1個(gè)基于費(fèi)用的查詢優(yōu)化器自動(dòng)優(yōu)化向SQL提交的數(shù)據(jù)查詢操作。數(shù)據(jù)操作查詢是指支持SQL關(guān)鍵字WHERE或HAVING的查詢，如Selece、Delete和Update。基于費(fèi)用的查詢優(yōu)化器根據(jù)統(tǒng)計(jì)信息產(chǎn)生子句的費(fèi)用估算。

了解優(yōu)化器數(shù)據(jù)處理過(guò)程的簡(jiǎn)單方法是檢測(cè)ShowPlan命令的輸出結(jié)果。如果用基于字符的工具（例如ISQL），可以通過(guò)鍵入Show ShowPlan ON來(lái)得到SHOWPLAN命令的輸出。如果使用圖形化查詢，比如SQL Enterprise Manager中的查詢工具或LSQL/W，可以設(shè)定配置選項(xiàng)來(lái)提供這一信息。

SQL Server的優(yōu)化通過(guò)3個(gè)階段完成：查詢分析、索引選擇、合并選擇。

（一）查詢分析

在查詢分析階段，SQL Server優(yōu)化器查看每一個(gè)由正規(guī)查詢樹(shù)代表的子句，并判斷它是否能被優(yōu)化。SQL Server一般會(huì)盡量?jī)?yōu)化那些限制掃描的子句。如含有SQL不等關(guān)系符“<>”的子句。因?yàn)椤?>”是1個(gè)排斥性的操作符，而不是1個(gè)包括性的操作符，所在掃描整個(gè)表之前無(wú)法確定子句的選擇范圍會(huì)有多大。當(dāng)1個(gè)關(guān)系型查詢中含有不可優(yōu)化的子句時(shí)，執(zhí)行計(jì)劃用表掃描來(lái)訪問(wèn)查詢的這個(gè)部分，對(duì)于查詢樹(shù)中可優(yōu)化的SQL Server子句，則由優(yōu)化器執(zhí)行索引選擇。

（二）索引選擇

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

（三）合并選擇

當(dāng)索引選擇結(jié)束，并且所有的子句都有了一個(gè)基于它們的訪問(wèn)計(jì)劃的處理費(fèi)用時(shí)，優(yōu)化器開(kāi)始執(zhí)行合并選擇。合并選擇被用來(lái)找出一個(gè)用于合并子句訪問(wèn)計(jì)劃的有效順序。為了做到這一點(diǎn)，優(yōu)化器比較子句的不同排序，然后選出從物理磁盤(pán)I/O的角度看處理費(fèi)用最低的合并計(jì)劃。因?yàn)樽泳浣M合的數(shù)量會(huì)隨著查詢的復(fù)雜度極快地增長(zhǎng)，SQL Server查詢優(yōu)化器使用樹(shù)剪枝技術(shù)來(lái)盡量減少這些比較所帶來(lái)的開(kāi)支。當(dāng)這個(gè)合并選擇階段結(jié)束時(shí)，SQL Server查詢優(yōu)化器已經(jīng)生成了1個(gè)基于費(fèi)用的查詢執(zhí)行計(jì)劃，這個(gè)計(jì)劃充分利用了可用的索引，并以最小的系統(tǒng)開(kāi)支和良好的執(zhí)行性能訪問(wèn)原來(lái)的數(shù)據(jù)。

從以上查詢優(yōu)化的3個(gè)階段不難看出，設(shè)計(jì)出物理I/O和邏輯I/O最少的方案并掌握好處理器時(shí)間和I/O時(shí)間的平衡，是高效查詢?cè)O(shè)計(jì)的主要目標(biāo)。也就是說(shuō)，希望設(shè)計(jì)出這樣的查詢：充分利用索引、磁盤(pán)讀寫(xiě)最少、最高效地利用了內(nèi)存和CPU資源。

經(jīng)驗(yàn)告訴我們，在優(yōu)化查詢時(shí)，必須做到：

1．盡可能少的記錄行；

2．避免排序或盡可能少的排序，若要做大量數(shù)據(jù)排序，最好將相關(guān)數(shù)據(jù)放在臨時(shí)表中操作；用簡(jiǎn)單的鍵（列）排序，如整型或短字符串排序；

3．避免表內(nèi)的相關(guān)子查詢，多表查詢盡可能使用連接查詢；

4．避免在Where子句中使用復(fù)雜的表達(dá)式或非起始的子字符串、用長(zhǎng)字符串連接；

5．在Where子句中多使用“與”(And)連接，少使用“或”(Or)連接；

6．利用臨時(shí)數(shù)據(jù)庫(kù)。在查詢多表、有多個(gè)連接、查詢復(fù)雜、數(shù)據(jù)要過(guò)濾時(shí)，可以建臨時(shí)表（索引）以減少I(mǎi)/O，但缺點(diǎn)是增加了空間開(kāi)銷(xiāo)；

7．除非每個(gè)列都有索引支持，否則在有連接的查詢時(shí)分別找出兩個(gè)動(dòng)態(tài)索引，放在工作表中重新排序。

參考文獻(xiàn)：

[1]鄭阿奇，《SQL Server實(shí)用教程》.

[2]王珊、薩師瑄，《數(shù)據(jù)庫(kù)系統(tǒng)概論》.

作者簡(jiǎn)介：

梁銘，講師，十堰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)工程系，主研領(lǐng)域：數(shù)據(jù)庫(kù)應(yīng)用技術(shù)。