|
SQL Server數(shù)據(jù)庫(kù)性能優(yōu)化 設(shè)計(jì)1個(gè)應(yīng)用系統(tǒng)似乎并不難,，但是要想使系統(tǒng)達(dá)到最優(yōu)化的性能并不是一件容易的事,。在開發(fā)工具、數(shù)據(jù)庫(kù)設(shè)計(jì),、應(yīng)用程序的結(jié)構(gòu),、查詢?cè)O(shè)計(jì)、接口選擇等方面有多種選擇,，這取決于特定的應(yīng)用需求以及開發(fā)隊(duì)伍的技能,。本文以SQL Server為例，從后臺(tái)數(shù)據(jù)庫(kù)的角度討論應(yīng)用程序性能優(yōu)化技巧,，并且給出了一些有益的建議,。 1 數(shù)據(jù)庫(kù)設(shè)計(jì) 要在良好的SQL Server方案中實(shí)現(xiàn)最優(yōu)的性能，最關(guān)鍵的是要有1個(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.1 邏輯庫(kù)規(guī)范化問(wèn)題 一般來(lái)說(shuō)，邏輯數(shù)據(jù)庫(kù)設(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ù)的頁(yè),。但表關(guān)系也許需要通過(guò)復(fù)雜的合并來(lái)處理,，這樣會(huì)降低系統(tǒng)的性能。某種程度上的非規(guī)范化可以改善系統(tǒng)的性能,，非規(guī)范化過(guò)程可以根據(jù)性能方面不同的考慮用多種不同的方法進(jìn)行,，但以下方法經(jīng)實(shí)踐驗(yàn)證往往能提高性能。 1.如果規(guī)范化設(shè)計(jì)產(chǎn)生了許多4路或更多路合并關(guān)系,，就可以考慮在數(shù)據(jù)庫(kù)實(shí)體(表)中加入重復(fù)屬性(列),。 2.常用的計(jì)算字段(如總計(jì),、最大值等)可以考慮存儲(chǔ)到數(shù)據(jù)庫(kù)實(shí)體中。 比如某一個(gè)項(xiàng)目的計(jì)劃管理系統(tǒng)中有計(jì)劃表,，其字段為:項(xiàng)目編號(hào),、年初計(jì)劃、二次計(jì)劃,、調(diào)整計(jì)劃,、補(bǔ)列計(jì)劃…，而計(jì)劃總數(shù)(年初計(jì)劃+二次計(jì)劃+調(diào)整計(jì)劃+補(bǔ)列計(jì)劃)是用戶經(jīng)常需要在查詢和報(bào)表中用到的,，在表的記錄量很大時(shí),，有必要把計(jì)劃總數(shù)作為1個(gè)獨(dú)立的字段加入到表中。這里可以采用觸發(fā)器以在客戶端保持?jǐn)?shù)據(jù)的一致性,。 3.重新定義實(shí)體以減少外部屬性數(shù)據(jù)或行數(shù)據(jù)的開支,。相應(yīng)的非規(guī)范化類型是: (1)把1個(gè)實(shí)體(表)分割成2個(gè)表(把所有的屬性分成2組)。這樣就把頻繁被訪問(wèn)的數(shù)據(jù)同較少被訪問(wèn)的數(shù)據(jù)分開了,。這種方法要求在每個(gè)表中復(fù)制首要關(guān)鍵字,。這樣產(chǎn)生的設(shè)計(jì)有利于并行處理，并將產(chǎn)生列數(shù)較少的表,。 (2)把1個(gè)實(shí)體(表)分割成2個(gè)表(把所有的行分成2組),。這種方法適用于那些將包含大量數(shù)據(jù)的實(shí)體(表)。在應(yīng)用中常要保留歷史記錄,，但是歷史記錄很少用到,。因此可以把頻繁被訪問(wèn)的數(shù)據(jù)同較少被訪問(wèn)的歷史數(shù)據(jù)分開。而且如果數(shù)據(jù)行是作為子集被邏輯工作組(部門,、銷售分區(qū),、地理區(qū)域等)訪問(wèn)的，那么這種方法也是很有好處的,。 1.2 生成物理數(shù)據(jù)庫(kù) 要想正確選擇基本物理實(shí)現(xiàn)策略,，必須懂得數(shù)據(jù)庫(kù)訪問(wèn)格式和硬件資源的操作特點(diǎn)，主要是內(nèi)存和磁盤子系統(tǒng)I/O,。這是一個(gè)范圍廣泛的話題,，但以下的準(zhǔn)則可能會(huì)有所幫助。 1.與每個(gè)表列相關(guān)的數(shù)據(jù)類型應(yīng)該反映數(shù)據(jù)所需的最小存儲(chǔ)空間,，特別是對(duì)于被索引的列更是如此,。比如能使用smallint類型就不要用integer類型，這樣索引字段可以被更快地讀取,，而且可以在1個(gè)數(shù)據(jù)頁(yè)上放置更多的數(shù)據(jù)行,，因而也就減少了I/O操作。 2.把1個(gè)表放在某個(gè)物理設(shè)備上，再通過(guò)SQL Server段把它的不分簇索引放在1個(gè)不同的物理設(shè)備上,，這樣能提高性能,。尤其是系統(tǒng)采用了多個(gè)智能型磁盤控制器和數(shù)據(jù)分離技術(shù)的情況下，這樣做的好處更加明顯,。 3.用SQL Server段把一個(gè)頻繁使用的大表分割開,，并放在2個(gè)單獨(dú)的智能型磁盤控制器的數(shù)據(jù)庫(kù)設(shè)備上，這樣也可以提高性能,。因?yàn)橛卸鄠€(gè)磁頭在查找,，所以數(shù)據(jù)分離也能提高性能。 4.用SQL Server段把文本或圖像列的數(shù)據(jù)存放在1個(gè)單獨(dú)的物理設(shè)備上可以提高性能,。1個(gè)專用的智能型的控制器能進(jìn)一步提高性能,。 2 與SQL Server相關(guān)的硬件系統(tǒng) 與SQL Server有關(guān)的硬件設(shè)計(jì)包括系統(tǒng)處理器、內(nèi)存,、磁盤子系統(tǒng)和網(wǎng)絡(luò)，這4個(gè)部分基本上構(gòu)成了硬件平臺(tái),，Windows NT和SQL Server運(yùn)行于其上,。 2.1 系統(tǒng)處理器(CPU) 根據(jù)自己的具體需要確定CPU結(jié)構(gòu)的過(guò)程就是估計(jì)在硬件平臺(tái)上占用CPU的工作量的過(guò)程。從以往的經(jīng)驗(yàn)看,，CPU配置最少應(yīng)是1個(gè)80586/100處理器,。如果只有2～3個(gè)用戶，這就足夠了,，但如果打算支持更多的用戶和關(guān)鍵應(yīng)用,，推薦采用Pentium Pro或PⅡ級(jí)CPU。 2.2 內(nèi)存(RAM) 為SQL Server方案確定合適的內(nèi)存設(shè)置對(duì)于實(shí)現(xiàn)良好的性能是至關(guān)重要的,。SQL Server用內(nèi)存做過(guò)程緩存,、數(shù)據(jù)和索引項(xiàng)緩存、靜態(tài)服務(wù)器開支和設(shè)置開支,。SQL Server最多能利用2GB虛擬內(nèi)存,，這也是最大的設(shè)置值。還有一點(diǎn)必須考慮的是Windows NT和它的所有相關(guān)的服務(wù)也要占用內(nèi)存,。 Windows NT為每個(gè)WIN32應(yīng)用程序提供了4GB的虛擬地址空間,。這個(gè)虛擬地址空間由Windows NT虛擬內(nèi)存管理器(VMM)映射到物理內(nèi)存上，在某些硬件平臺(tái)上可以達(dá)到4GB,。SQL Server應(yīng)用程序只知道虛擬地址,，所以不能直接訪問(wèn)物理內(nèi)存，這個(gè)訪問(wèn)是由VMM控制的,。Windows NT允許產(chǎn)生超出可用的物理內(nèi)存的虛擬地址空間,，這樣當(dāng)給SQL Server分配的虛擬內(nèi)存多于可用的物理內(nèi)存時(shí)，會(huì)降低SQL的性能。 這些地址空間是專門為SQL Server系統(tǒng)設(shè)置的,，所以如果在同一硬件平臺(tái)上還有其它軟件(如文件和打印共享,，應(yīng)用程序服務(wù)等)在運(yùn)行，那么應(yīng)該考慮到它們也占用一部分內(nèi)存,。一般來(lái)說(shuō)硬件平臺(tái)至少要配置32MB的內(nèi)存,，其中，Windows NT至少要占用16MB,。1個(gè)簡(jiǎn)單的法則是,，給每一個(gè)并發(fā)的用戶增加100KB的內(nèi)存。例如,，如果有100個(gè)并發(fā)的用戶,，則至少需要32MB+100用戶*100KB=42MB內(nèi)存，實(shí)際的使用數(shù)量還需要根據(jù)運(yùn)行的實(shí)際情況調(diào)整,?？梢哉f(shuō)，提高內(nèi)存是提高系統(tǒng)性能的最經(jīng)濟(jì)的途徑,。 2.3 磁盤子系統(tǒng) 設(shè)計(jì)1個(gè)好的磁盤I/O系統(tǒng)是實(shí)現(xiàn)良好的SQL Server方案的一個(gè)很重要的方面,。這里討論的磁盤子系統(tǒng)至少有1個(gè)磁盤控制設(shè)備和1個(gè)或多個(gè)硬盤單元，還有對(duì)磁盤設(shè)置和文件系統(tǒng)的考慮,。智能型SCSI-2磁盤控制器或磁盤組控制器是不錯(cuò)的選擇,，其特點(diǎn)如下: (1)控制器高速緩存。 (2)總線主板上有處理器,，可以減少對(duì)系統(tǒng)CPU的中斷,。 (3)異步讀寫支持。 (4)32位RAID支持,。 (5)快速SCSI—2驅(qū)動(dòng),。 (6)超前讀高速緩存(至少1個(gè)磁道)。 3 檢索策略 在精心選擇了硬件平臺(tái),，又實(shí)現(xiàn)了1個(gè)良好的數(shù)據(jù)庫(kù)方案,，并且具備了用戶需求和應(yīng)用方面的知識(shí)后，現(xiàn)在應(yīng)該設(shè)計(jì)查詢和索引了,。有2個(gè)方面對(duì)于在SQL Server上取得良好的查詢和索引性能是十分重要的,，第1是根據(jù)SQL Server優(yōu)化器方面的知識(shí)生成查詢和索引;第2是利用SQL Server的性能特點(diǎn)，加強(qiáng)數(shù)據(jù)訪問(wèn)操作,。 3.1 SQL Server優(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的查詢，如SELECT,、DELETE和UPDATE,?；谫M(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中的查詢工具或isql/w,，可以設(shè)定配置選項(xiàng)來(lái)提供這一信息。 SQL Server的優(yōu)化通過(guò)3個(gè)階段完成:查詢分析,、索引選擇,、合并選擇。 1.查詢分析 在查詢分析階段,，SQL Server優(yōu)化器查看每一個(gè)由正規(guī)查詢樹代表的子句,，并判斷它是否能被優(yōu)化。SQL Server一般會(huì)盡量?jī)?yōu)化那些限制掃描的子句,。例如,，搜索和/或合并子句。但是不是所有合法的SQL語(yǔ)法都可以分成可優(yōu)化的子句,，如含有SQL不等關(guān)系符“”的子句,。因?yàn)?#8220;”是1個(gè)排斥性的操作符，而不是1個(gè)包括性的操作符,，所在掃描整個(gè)表之前無(wú)法確定子句的選擇范圍會(huì)有多大。當(dāng)1個(gè)關(guān)系型查詢中含有不可優(yōu)化的子句時(shí),，執(zhí)行計(jì)劃用表掃描來(lái)訪問(wèn)查詢的這個(gè)部分,，對(duì)于查詢樹中可優(yōu)化的SQL Server子句，則由優(yōu)化器執(zhí)行索引選擇,。 2.索引選擇 對(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è)姓氏的條目一樣，排序基本上沒有什么用,，因?yàn)槟氵€是得查看每一行以確定它是否符合條件,。如果1個(gè)子句有可用的索引，那么優(yōu)化器就會(huì)為它確定選擇性,。 所以在設(shè)計(jì)過(guò)程中,，要根據(jù)查詢?cè)O(shè)計(jì)準(zhǔn)則仔細(xì)檢查所有的查詢，以查詢的優(yōu)化特點(diǎn)為基礎(chǔ)設(shè)計(jì)索引,。 (1)比較窄的索引具有比較高的效率,。對(duì)于比較窄的索引來(lái)說(shuō)，每頁(yè)上能存放較多的索引行,，而且索引的級(jí)別也較少,。所以，緩存中能放置更多的索引頁(yè),，這樣也減少了I/O操作,。 (2)SQL Server優(yōu)化器能分析大量的索引和合并可能性。所以與較少的寬索引相比,，較多的窄索引能向優(yōu)化器提供更多的選擇。但是不要保留不必要的索引,，因?yàn)樗鼈儗⒃黾哟鎯?chǔ)和維護(hù)的開支,。對(duì)于復(fù)合索引、組合索引或多列索引,，SQL Server優(yōu)化器只保留最重要的列的分布統(tǒng)計(jì)信息,，這樣，索引的第1列應(yīng)該有很大的選擇性,。 (3)表上的索引過(guò)多會(huì)影響UPDATE,、INSERT和DELETE的性能，因?yàn)樗械乃饕急仨氉鱿鄳?yīng)的調(diào)整,。另外,，所有的分頁(yè)操作都被記錄在日志中，這也會(huì)增加I/O操作,。 (4)對(duì)1個(gè)經(jīng)常被更新的列建立索引,，會(huì)嚴(yán)重影響性能。 (5)由于存儲(chǔ)開支和I/O操作方面的原因,，較小的自組索引比較大的索引性能更好一些,。但它的缺點(diǎn)是要維護(hù)自組的列。 (6)盡量分析出每一個(gè)重要查詢的使用頻度,，這樣可以找出使用最多的索引,，然后可以先對(duì)這些索引進(jìn)行適當(dāng)?shù)膬?yōu)化。 (7)查詢中的WHERE子句中的任何列都很可能是個(gè)索引列,，因?yàn)閮?yōu)化器重點(diǎn)處理這個(gè)子句,。 (8)對(duì)小于1個(gè)范圍的小型表進(jìn)行索引是不劃算的,，因?yàn)閷?duì)于小表來(lái)說(shuō)表掃描往往更快而且費(fèi)用低。 (9)與“ORDER BY”或“GROUP BY”一起使用的列一般適于做分族索引,。如果“ORDER BY”命令中用到的列上有分簇索引,，那么就不會(huì)再生成1個(gè)工作表了，因?yàn)樾幸呀?jīng)排序了,。“GROUP BY”命令則一定產(chǎn)生1個(gè)工作表,。 (10)分簇索引不應(yīng)該構(gòu)造在經(jīng)常變化的列上，因?yàn)檫@會(huì)引起整行的移動(dòng),。在實(shí)現(xiàn)大型交易處理系統(tǒng)時(shí),，尤其要注意這一點(diǎn)，因?yàn)檫@些系統(tǒng)中數(shù)據(jù)往往是頻繁變化的,。 3.合并選擇 當(dāng)索引選擇結(jié)束,，并且所有的子句都有了一個(gè)基于它們的訪問(wèn)計(jì)劃的處理費(fèi)用時(shí)，優(yōu)化器開始執(zhí)行合并選擇,。合并選擇被用來(lái)找出一個(gè)用于合并子句訪問(wèn)計(jì)劃的有效順序,。為了做到這一點(diǎn)，優(yōu)化器比較子句的不同排序,，然后選出從物理磁盤I/O的角度看處理費(fèi)用最低的合并計(jì)劃,。因?yàn)樽泳浣M合的數(shù)量會(huì)隨著查詢的復(fù)雜度極快地增長(zhǎng)，SQL Server查詢優(yōu)化器使用樹剪枝技術(shù)來(lái)盡量減少這些比較所帶來(lái)的開支,。當(dāng)這個(gè)合并選擇階段結(jié)束時(shí),，SQL Server查詢優(yōu)化器已經(jīng)生成了1個(gè)基于費(fèi)用的查詢執(zhí)行計(jì)劃，這個(gè)計(jì)劃充分利用了可用的索引,，并以最小的系統(tǒng)開支和良好的執(zhí)行性能訪問(wèn)原來(lái)的數(shù)據(jù),。 3.2 高效的查詢選擇 從以上查詢優(yōu)化的3個(gè)階段不難看出，設(shè)計(jì)出物理I/O和邏輯I/O最少的方案并掌握好處理器時(shí)間和I/O時(shí)間的平衡,，是高效查詢?cè)O(shè)計(jì)的主要目標(biāo)。也就是說(shuō),，希望設(shè)計(jì)出這樣的查詢:充分利用索引,、磁盤讀寫最少、最高效地利用了內(nèi)存和CPU資源,。 以下建議是從SQL Server優(yōu)化器的優(yōu)化策略中總結(jié)出來(lái)的,，對(duì)于設(shè)計(jì)高效的查詢是很有幫助的。 1.如果有獨(dú)特的索引,，那么帶有“=”操作符的WHERE子句性能最好,，其次是封閉的區(qū)間(范圍)，再其次是開放的區(qū)間,。 2.從數(shù)據(jù)庫(kù)訪問(wèn)的角度看,，含有不連續(xù)連接詞(OR和IN)的WHERE子句一般來(lái)說(shuō)性能不會(huì)太好,。所以，優(yōu)化器可能會(huì)采用R策略,，這種策略會(huì)生成1個(gè)工作表,，其中含有每個(gè)可能匹配的執(zhí)行的標(biāo)識(shí)符，優(yōu)化器把這些行標(biāo)志符(頁(yè)號(hào)和行號(hào))看做是指向1個(gè)表中匹配的行的“動(dòng)態(tài)索引”,。優(yōu)化器只需掃描工作表,，取出每一個(gè)行標(biāo)志符，再?gòu)臄?shù)據(jù)表中取得相應(yīng)的行,，所以R策略的代價(jià)是生成工作表,。 3.包含NOT、,、或! =的WHERE子句對(duì)于優(yōu)化器的索引選擇來(lái)說(shuō)沒有什么用處,。因?yàn)檫@樣的子句是排斥性的，而不是包括性的,，所以在掃描整個(gè)原來(lái)數(shù)據(jù)表之前無(wú)法確定子句的選擇性,。 4.限制數(shù)據(jù)轉(zhuǎn)換和串操作，優(yōu)化器一般不會(huì)根據(jù)WHERE子句中的表達(dá)式和數(shù)據(jù)轉(zhuǎn)換式生成索引選擇,。例如: paycheck * 12>36000 or substring(lastname,1,1)=“L” 如果該表建立了針對(duì)paycheck和lastname的索引,，就不能利用索引進(jìn)行優(yōu)化，可以改寫上面的條件表達(dá)式為: paycheck 5.WHERE子句中的本地變量被認(rèn)為是不被優(yōu)化器知道和考慮的,，例外的情況是定義為儲(chǔ)備過(guò)程輸入?yún)?shù)的變量,。 6.如果沒有包含合并子句的索引，那么優(yōu)化器構(gòu)造1個(gè)工作表以存放合并中最小的表中的行,。然后再在這個(gè)表上構(gòu)造1個(gè)分簇索引以完成一個(gè)高效的合并,。這種作法的代價(jià)是工作表的生成和隨后的分族索引的生成，這個(gè)過(guò)程叫REFORMATTING,?！ ∷詰?yīng)該注意RAM中或磁盤上的數(shù)據(jù)庫(kù)tempdb的大小(除了SELECT INTO語(yǔ)句)。另外,，如果這些類型的操作是很常見的,，那么把tempdb放在RAM中對(duì)于提高性能是很有好處的。 4 性能優(yōu)化的其他考慮 上面列出了影響SQL Server的一些主要因素,，實(shí)際上遠(yuǎn)不止這些,。操作系統(tǒng)的影響也很大，在Windows NT下,，文件系統(tǒng)的選擇,、網(wǎng)絡(luò)協(xié)議、開啟的服務(wù),、SQL Server的優(yōu)先級(jí)等選項(xiàng)也不同程度上影響了SQL Server的性能,。 影響性能的因素是如此的多,，而應(yīng)用又各不相同，找出1個(gè)通用的優(yōu)化方案是不現(xiàn)實(shí)的,，在系統(tǒng)開發(fā)和維護(hù)的過(guò)程中必須針對(duì)運(yùn)行的情況,，不斷加以調(diào)整。事實(shí)上,，絕大部分的優(yōu)化和調(diào)整工作是在與客戶端獨(dú)立的服務(wù)器上進(jìn)行的,，因此也是現(xiàn)實(shí)可行的。 |
|
|
來(lái)自： 荷露叮咚 > 《數(shù)據(jù)庫(kù)》
