官场小说排行榜,大主宰

主頁 > 知識庫 > SQL Server 數(shù)據(jù)庫優(yōu)化

SQL Server 數(shù)據(jù)庫優(yōu)化

在開發(fā)工具、數(shù)據(jù)庫設(shè)計、應(yīng)用程序的結(jié)構(gòu)、查詢設(shè)計、接口選擇等方面有多種選擇，這取決于特定的應(yīng)用需求以及開發(fā)隊伍的技能。本文以SQL Server為例，從后臺數(shù)據(jù)庫的角度討論應(yīng)用程序性能優(yōu)化技巧，并且給出了一些有益的建議。
1　數(shù)據(jù)庫設(shè)計

　　要在良好的SQL Server方案中實現(xiàn)最優(yōu)的性能，最關(guān)鍵的是要有1個很好的數(shù)據(jù)庫設(shè)計方案。在實際工作中，許多SQL Server方案往往是由于數(shù)據(jù)庫設(shè)計得不好導(dǎo)致性能很差。所以，要實現(xiàn)良好的數(shù)據(jù)庫設(shè)計就必須考慮這些問題。

　　1.1　邏輯庫規(guī)范化問題

　　一般來說，邏輯數(shù)據(jù)庫設(shè)計會滿足規(guī)范化的前3級標準:

　　1.第1規(guī)范:沒有重復(fù)的組或多值的列。

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

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

　　遵守這些規(guī)則的設(shè)計會產(chǎn)生較少的列和更多的表，因而也就減少了數(shù)據(jù)冗余，也減少了用于存儲數(shù)據(jù)的頁。但表關(guān)系也許需要通過復(fù)雜的合并來處理，這樣會降低系統(tǒng)的性能。某種程度上的非規(guī)范化可以改善系統(tǒng)的性能，非規(guī)范化過程可以根據(jù)性能方面不同的考慮用多種不同的方法進行，但以下方法經(jīng)實踐驗證往往能提高性能。

　　1.如果規(guī)范化設(shè)計產(chǎn)生了許多4路或更多路合并關(guān)系，就可以考慮在數(shù)據(jù)庫實體(表)中加入重復(fù)屬性(列)。

　　2.常用的計算字段(如總計、最大值等)可以考慮存儲到數(shù)據(jù)庫實體中。

　　比如某一個項目的計劃管理系統(tǒng)中有計劃表，其字段為:項目編號、年初計劃、二次計劃、調(diào)整計劃、補列計劃…，而計劃總數(shù)(年初計劃+二次計劃+調(diào)整計劃+補列計劃)是用戶經(jīng)常需要在查詢和報表中用到的，在表的記錄量很大時，有必要把計劃總數(shù)作為1個獨立的字段加入到表中。這里可以采用觸發(fā)器以在客戶端保持數(shù)據(jù)的一致性。

　　3.重新定義實體以減少外部屬性數(shù)據(jù)或行數(shù)據(jù)的開支。相應(yīng)的非規(guī)范化類型是:

　　(1)把1個實體(表)分割成2個表(把所有的屬性分成2組)。這樣就把頻繁被訪問的數(shù)據(jù)同較少被訪問的數(shù)據(jù)分開了。這種方法要求在每個表中復(fù)制首要關(guān)鍵字。這樣產(chǎn)生的設(shè)計有利于并行處理，并將產(chǎn)生列數(shù)較少的表。

　　(2)把1個實體(表)分割成2個表(把所有的行分成2組)。這種方法適用于那些將包含大量數(shù)據(jù)的實體(表)。在應(yīng)用中常要保留歷史記錄，但是歷史記錄很少用到。因此可以把頻繁被訪問的數(shù)據(jù)同較少被訪問的歷史數(shù)據(jù)分開。而且如果數(shù)據(jù)行是作為子集被邏輯工作組(部門、銷售分區(qū)、地理區(qū)域等)訪問的，那么這種方法也是很有好處的。

　　1.2　生成物理數(shù)據(jù)庫

　　要想正確選擇基本物理實現(xiàn)策略，必須懂得數(shù)據(jù)庫訪問格式和硬件資源的操作特點，主要是內(nèi)存和磁盤子系統(tǒng)I/O。這是一個范圍廣泛的話題，但以下的準則可能會有所幫助。

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

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

　　3.用SQL Server段把一個頻繁使用的大表分割開，并放在2個單獨的智能型磁盤控制器的數(shù)據(jù)庫設(shè)備上，這樣也可以提高性能。因為有多個磁頭在查找，所以數(shù)據(jù)分離也能提高性能。

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

　　2　與SQL Server相關(guān)的硬件系統(tǒng)

　　與SQL Server有關(guān)的硬件設(shè)計包括系統(tǒng)處理器、內(nèi)存、磁盤子系統(tǒng)和網(wǎng)絡(luò)，這4個部分基本上構(gòu)成了硬件平臺，Windows NT和SQL Server運行于其上。

　　2.1　系統(tǒng)處理器(CPU)

　　根據(jù)自己的具體需要確定CPU結(jié)構(gòu)的過程就是估計在硬件平臺上占用CPU的工作量的過程。從以往的經(jīng)驗看，CPU配置最少應(yīng)是1個80586/100處理器。如果只有2～3個用戶，這就足夠了，但如果打算支持更多的用戶和關(guān)鍵應(yīng)用，推薦采用Pentium Pro或PⅡ級CPU。

　　2.2　內(nèi)存(RAM)

　　為SQL Server方案確定合適的內(nèi)存設(shè)置對于實現(xiàn)良好的性能是至關(guān)重要的。SQL Server用內(nèi)存做過程緩存、數(shù)據(jù)和索引項緩存、靜態(tài)服務(wù)器開支和設(shè)置開支。SQL Server最多能利用2GB虛擬內(nèi)存，這也是最大的設(shè)置值。還有一點必須考慮的是Windows NT和它的所有相關(guān)的服務(wù)也要占用內(nèi)存。

　　Windows NT為每個WIN32應(yīng)用程序提供了4GB的虛擬地址空間。這個虛擬地址空間由Windows NT虛擬內(nèi)存管理器(VMM)映射到物理內(nèi)存上，在某些硬件平臺上可以達到4GB。SQL Server應(yīng)用程序只知道虛擬地址，所以不能直接訪問物理內(nèi)存，這個訪問是由VMM控制的。Windows NT允許產(chǎn)生超出可用的物理內(nèi)存的虛擬地址空間，這樣當(dāng)給SQL Server分配的虛擬內(nèi)存多于可用的物理內(nèi)存時，會降低SQL Server的性能。

　　這些地址空間是專門為SQL Server系統(tǒng)設(shè)置的，所以如果在同一硬件平臺上還有其它軟件(如文件和打印共享，應(yīng)用程序服務(wù)等)在運行，那么應(yīng)該考慮到它們也占用一部分內(nèi)存。一般來說硬件平臺至少要配置32MB的內(nèi)存，其中，Windows NT至少要占用16MB。1個簡單的法則是，給每一個并發(fā)的用戶增加100KB的內(nèi)存。例如，如果有100個并發(fā)的用戶，則至少需要32MB+100用戶*100KB=42MB內(nèi)存，實際的使用數(shù)量還需要根據(jù)運行的實際情況調(diào)整。可以說，提高內(nèi)存是提高系統(tǒng)性能的最經(jīng)濟的途徑。

　　2.3　磁盤子系統(tǒng)

　　設(shè)計1個好的磁盤I/O系統(tǒng)是實現(xiàn)良好的SQL Server方案的一個很重要的方面。這里討論的磁盤子系統(tǒng)至少有1個磁盤控制設(shè)備和1個或多個硬盤單元，還有對磁盤設(shè)置和文件系統(tǒng)的考慮。智能型SCSI-2磁盤控制器或磁盤組控制器是不錯的選擇，其特點如下:

　　(1)控制器高速緩存。

　　(2)總線主板上有處理器，可以減少對系統(tǒng)CPU的中斷。

　　(3)異步讀寫支持。

　　(4)32位RAID支持。

　　(5)快速SCSI—2驅(qū)動。

　　(6)超前讀高速緩存(至少1個磁道)。

　　3　檢索策略

　　在精心選擇了硬件平臺，又實現(xiàn)了1個良好的數(shù)據(jù)庫方案，并且具備了用戶需求和應(yīng)用方面的知識后，現(xiàn)在應(yīng)該設(shè)計查詢和索引了。有2個方面對于在SQL Server上取得良好的查詢和索引性能是十分重要的，第1是根據(jù)SQL Server優(yōu)化器方面的知識生成查詢和索引;第2是利用SQL Server的性能特點，加強數(shù)據(jù)訪問操作。

　　3.1　SQL Server優(yōu)化器

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

　　了解優(yōu)化器數(shù)據(jù)處理過程的簡單方法是檢測SHOWPLAN命令的輸出結(jié)果。如果用基于字符的工具(例如isql)，可以通過鍵入SHOW SHOWPLAN ON來得到SHOWPLAN命令的輸出。如果使用圖形化查詢，比如SQL Enterprise Manager中的查詢工具或isql/w，可以設(shè)定配置選項來提供這一信息。

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

　　1.查詢分析

　　在查詢分析階段，SQL Server優(yōu)化器查看每一個由正規(guī)查詢樹代表的子句，并判斷它是否能被優(yōu)化。SQL Server一般會盡量優(yōu)化那些限制掃描的子句。例如，搜索和/或合并子句。但是不是所有合法的SQL語法都可以分成可優(yōu)化的子句，如含有SQL不等關(guān)系符“>”的子句。因為“>”是1個排斥性的操作符，而不是1個包括性的操作符，所在掃描整個表之前無法確定子句的選擇范圍會有多大。當(dāng)1個關(guān)系型查詢中含有不可優(yōu)化的子句時，執(zhí)行計劃用表掃描來訪問查詢的這個部分，對于查詢樹中可優(yōu)化的SQL Server子句，則由優(yōu)化器執(zhí)行索引選擇。

　　2.索引選擇

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

　　所以在設(shè)計過程中，要根據(jù)查詢設(shè)計準則仔細檢查所有的查詢，以查詢的優(yōu)化特點為基礎(chǔ)設(shè)計索引。

　　(1)比較窄的索引具有比較高的效率。對于比較窄的索引來說，每頁上能存放較多的索引行，而且索引的級別也較少。所以，緩存中能放置更多的索引頁，這樣也減少了I/O操作。

　　(2)SQL Server優(yōu)化器能分析大量的索引和合并可能性。所以與較少的寬索引相比，較多的窄索引能向優(yōu)化器提供更多的選擇。但是不要保留不必要的索引，因為它們將增加存儲和維護的開支。對于復(fù)合索引、組合索引或多列索引，SQL Server優(yōu)化器只保留最重要的列的分布統(tǒng)計信息，這樣，索引的第1列應(yīng)該有很大的選擇性。

　　(3)表上的索引過多會影響UPDATE、INSERT和DELETE的性能，因為所有的索引都必須做相應(yīng)的調(diào)整。另外，所有的分頁操作都被記錄在日志中，這也會增加I/O操作。

　　(4)對1個經(jīng)常被更新的列建立索引，會嚴重影響性能。

　　(5)由于存儲開支和I/O操作方面的原因，較小的自組索引比較大的索引性能更好一些。但它的缺點是要維護自組的列。

　　(6)盡量分析出每一個重要查詢的使用頻度，這樣可以找出使用最多的索引，然后可以先對這些索引進行適當(dāng)?shù)膬?yōu)化。

　　(7)查詢中的WHERE子句中的任何列都很可能是個索引列，因為優(yōu)化器重點處理這個子句。

　　(8)對小于1個范圍的小型表進行索引是不劃算的，因為對于小表來說表掃描往往更快而且費用低。

　　(9)與“ORDER BY”或“GROUP BY”一起使用的列一般適于做分族索引。如果“ORDER BY”命令中用到的列上有分簇索引，那么就不會再生成1個工作表了，因為行已經(jīng)排序了?！癎ROUP BY”命令則一定產(chǎn)生1個工作表。

　　(10)分簇索引不應(yīng)該構(gòu)造在經(jīng)常變化的列上，因為這會引起整行的移動。在實現(xiàn)大型交易處理系統(tǒng)時，尤其要注意這一點，因為這些系統(tǒng)中數(shù)據(jù)往往是頻繁變化的。

　　3.合并選擇

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

　　3.2　高效的查詢選擇

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

　　以下建議是從SQL Server優(yōu)化器的優(yōu)化策略中總結(jié)出來的，對于設(shè)計高效的查詢是很有幫助的。

　　1.如果有獨特的索引，那么帶有“=”操作符的WHERE子句性能最好，其次是封閉的區(qū)間(范圍)，再其次是開放的區(qū)間。

　　2.從數(shù)據(jù)庫訪問的角度看，含有不連續(xù)連接詞(OR和IN)的WHERE子句一般來說性能不會太好。所以，優(yōu)化器可能會采用R策略，這種策略會生成1個工作表，其中含有每個可能匹配的執(zhí)行的標識符，優(yōu)化器把這些行標志符(頁號和行號)看做是指向1個表中匹配的行的“動態(tài)索引”。優(yōu)化器只需掃描工作表，取出每一個行標志符，再從數(shù)據(jù)表中取得相應(yīng)的行，所以R策略的代價是生成工作表。

　　3.包含NOT、>、或!　=的WHERE子句對于優(yōu)化器的索引選擇來說沒有什么用處。因為這樣的子句是排斥性的，而不是包括性的，所以在掃描整個原來數(shù)據(jù)表之前無法確定子句的選擇性。

　　4.限制數(shù)據(jù)轉(zhuǎn)換和串操作，優(yōu)化器一般不會根據(jù)WHERE子句中的表達式和數(shù)據(jù)轉(zhuǎn)換式生成索引選擇。例如:

　　paycheck * 12>36000 or substring(lastname,1,1)=“L”

　　如果該表建立了針對paycheck和lastname的索引，就不能利用索引進行優(yōu)化，可以改寫上面的條件表達式為:

　　paycheck36000/12 or lastname like “L%”

　　5.WHERE子句中的本地變量被認為是不被優(yōu)化器知道和考慮的，例外的情況是定義為儲備過程輸入?yún)?shù)的變量。

　　6.如果沒有包含合并子句的索引，那么優(yōu)化器構(gòu)造1個工作表以存放合并中最小的表中的行。然后再在這個表上構(gòu)造1個分簇索引以完成一個高效的合并。這種作法的代價是工作表的生成和隨后的分族索引的生成，這個過程叫REFORMATTING?！　∷詰?yīng)該注意RAM中或磁盤上的數(shù)據(jù)庫tempdb的大小(除了SELECT INTO語句)。另外，如果這些類型的操作是很常見的，那么把tempdb放在RAM中對于提高性能是很有好處的。

　　4　性能優(yōu)化的其他考慮

　　上面列出了影響SQL Server的一些主要因素，實際上遠不止這些。操作系統(tǒng)的影響也很大，在Windows NT下，文件系統(tǒng)的選擇、網(wǎng)絡(luò)協(xié)議、開啟的服務(wù)、SQL Server的優(yōu)先級等選項也不同程度上影響了SQL Server的性能。

　　影響性能的因素是如此的多，而應(yīng)用又各不相同，找出1個通用的優(yōu)化方案是不現(xiàn)實的，在系統(tǒng)開發(fā)和維護的過程中必須針對運行的情況，不斷加以調(diào)整。事實上，絕大部分的優(yōu)化和調(diào)整工作是在與客戶端獨立的服務(wù)器上進行的，因此也是現(xiàn)實可行的。

您可能感興趣的文章:

海量數(shù)據(jù)庫的查詢優(yōu)化及分頁算法方案
mysql 數(shù)據(jù)庫中my.ini的優(yōu)化 2G內(nèi)存針對站多抗壓型的設(shè)置
開啟SQLSERVER數(shù)據(jù)庫緩存依賴優(yōu)化網(wǎng)站性能
MySQL 聯(lián)合索引與Where子句的優(yōu)化提高數(shù)據(jù)庫運行效率
asp.net程序優(yōu)化盡量減少數(shù)據(jù)庫連接操作
服務(wù)器維護小常識(硬盤內(nèi)容增加、數(shù)據(jù)庫優(yōu)化等)
數(shù)據(jù)庫性能優(yōu)化二：數(shù)據(jù)庫表優(yōu)化提升性能
數(shù)據(jù)庫性能優(yōu)化一：數(shù)據(jù)庫自身優(yōu)化提升性能
Oracle SQL tuning 數(shù)據(jù)庫優(yōu)化步驟分享(圖文教程)
oracle數(shù)據(jù)庫sql的優(yōu)化總結(jié)
優(yōu)化Mysql數(shù)據(jù)庫的8個方法
Postgre數(shù)據(jù)庫Insert 、Query性能優(yōu)化詳解
mysql中優(yōu)化和修復(fù)數(shù)據(jù)庫工具mysqlcheck詳細介紹
Codeigniter操作數(shù)據(jù)庫表的優(yōu)化寫法總結(jié)
MySQL數(shù)據(jù)庫優(yōu)化詳解
用實例詳解Python中的Django框架中prefetch_related()函數(shù)對數(shù)據(jù)庫查詢的優(yōu)化
數(shù)據(jù)庫學(xué)習(xí)建議之提高數(shù)據(jù)庫速度的十條建議

標簽：福建商洛呼和浩特西寧珠海天水泉州美容院

巨人網(wǎng)絡(luò)通訊聲明：本文標題《SQL Server 數(shù)據(jù)庫優(yōu)化》，本文關(guān)鍵詞 SQL,Server,數(shù)據(jù)庫,優(yōu)化,SQL,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。

濮阳杆衣贸易有限公司

SQL Server 數(shù)據(jù)庫優(yōu)化