【正文】 VER數(shù)據(jù)庫(kù)性能調(diào)整與優(yōu)化的相關(guān)技術(shù)，包括數(shù)據(jù)庫(kù)邏輯結(jié)構(gòu)和物理結(jié)構(gòu)的優(yōu)化、SQL語(yǔ)句優(yōu)化、合適索引的創(chuàng)建、SQL SERVER分區(qū)技術(shù)等等。第五章開(kāi)發(fā)了一個(gè)商品訂單管理系統(tǒng)作為測(cè)試用例，對(duì)如何優(yōu)化數(shù)據(jù)庫(kù)的性能進(jìn)行具體的測(cè)試和分析。第六章總結(jié)全文，肯定了數(shù)據(jù)庫(kù)性能調(diào)整的應(yīng)用價(jià)值。2 數(shù)據(jù)庫(kù)性能優(yōu)化架構(gòu)性能調(diào)整(performance tuning)是一項(xiàng)活動(dòng)，這項(xiàng)活動(dòng)通過(guò)優(yōu)化應(yīng)用程序、修改系統(tǒng)參數(shù)、改變系統(tǒng)配置(硬件調(diào)整)來(lái)改變系統(tǒng)性能。性能調(diào)整包括對(duì)硬件配置操作系統(tǒng)(OS)與關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)(RDBMS)配置，以及對(duì)訪問(wèn)這些組件的應(yīng)用的詳細(xì)分析與優(yōu)化。系統(tǒng)性能的判定標(biāo)準(zhǔn)依賴于性能衡量的研究，通常認(rèn)為性能的指標(biāo)可以通過(guò)處理一個(gè)事務(wù)所需的I/O量、CPU時(shí)間量、以及響應(yīng)時(shí)間來(lái)衡量。性能會(huì)隨特定環(huán)境的不同而變化，并受應(yīng)用程序、體系結(jié)構(gòu)和資源、服務(wù)器以及并發(fā)活動(dòng)的具體情況影響。性能調(diào)整的一個(gè)主要目標(biāo)是消除瓶頸。瓶頸(bottle neck)是性能限制的重要因素，它可以是硬件或軟件，如果不是在正確配置和調(diào)整的系統(tǒng)中，就會(huì)嚴(yán)重地影響系統(tǒng)性能。減少瓶頸可以最大地發(fā)揮一個(gè)系統(tǒng)的性能。為有效地對(duì)系統(tǒng)進(jìn)行調(diào)整，必須遵循一套特定的步驟或方法，去調(diào)整和優(yōu)化系統(tǒng)中的所有組件，包括應(yīng)用、硬件與SQL SERVER。首先，討論將涉及系統(tǒng)調(diào)整的不同組件。在本章后面部分，著重介紹性能調(diào)整的方法論。有不同的方式確定性能調(diào)整工作的目標(biāo)。數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)可以用各種定量的方法分析，其中最重要指標(biāo)如下：吞吐量是指給定時(shí)間內(nèi)服務(wù)器能夠處理的查詢總數(shù)，即每個(gè)單元時(shí)間完成的工作，以每秒鐘的事務(wù)量(tps)表示；該值越高越好。提高系統(tǒng)的吞吐量也有兩種方法：l 在同樣的資源環(huán)境下做更多的工作(通過(guò)減少服務(wù)時(shí)間)；l 通過(guò)減少總的響應(yīng)時(shí)間使工作做得更快。要做到這一點(diǎn)，可以將用戶等待的資源加倍，如系統(tǒng)受到CPU限制，則可以增加CPU的數(shù)量。在任何一個(gè)系統(tǒng)中，吞吐量和反應(yīng)時(shí)間作為調(diào)整目標(biāo)通常是互相對(duì)立的。如果反應(yīng)時(shí)間長(zhǎng)(壞)，吞吐量或許高(好)。如果吞吐量低(壞)，反應(yīng)時(shí)間或許短(好)。多數(shù)的并發(fā)用戶在一定時(shí)間內(nèi)使用一個(gè)系統(tǒng)，每個(gè)用戶很有可能比平時(shí)要經(jīng)歷更長(zhǎng)的延遲，但是通過(guò)系統(tǒng)的事務(wù)數(shù)量將會(huì)更大。相反，假如減少在某一個(gè)時(shí)間窗口中訪問(wèn)系統(tǒng)的并發(fā)用戶數(shù)量，以在該時(shí)間內(nèi)完成的全部事務(wù)量減少為代價(jià)，每個(gè)用戶將會(huì)享受到更快的反應(yīng)時(shí)間?，F(xiàn)在考慮一個(gè)為客戶預(yù)定體育比賽門票的公司。如果該公司的營(yíng)業(yè)時(shí)間為每天8小時(shí)，并且在銷售市場(chǎng)體育比賽門票的第一天必須賣出25000張門票，那么該公司的門票預(yù)定系統(tǒng)的吞吐量必須等于或大于每小時(shí)3125筆事務(wù)。吞吐量=(25000筆事務(wù)/8小時(shí))=3125筆事務(wù)/小時(shí)。當(dāng)然，以8小時(shí)的時(shí)間間隔來(lái)計(jì)算系統(tǒng)需要達(dá)到的吞吐量是不現(xiàn)實(shí)的，因?yàn)椴⒉皇撬行枨蠖际莿蛩俚竭_(dá)系統(tǒng)。在設(shè)計(jì)系統(tǒng)軟硬件規(guī)模和規(guī)劃系統(tǒng)能力時(shí)，我們是根據(jù)系統(tǒng)最忙碌的時(shí)間段來(lái)計(jì)算系統(tǒng)所要達(dá)到的吞吐量的。這是設(shè)計(jì)數(shù)據(jù)庫(kù)系統(tǒng)的一條著名規(guī)律：為最糟糕的情況設(shè)計(jì)系統(tǒng)的軟硬件規(guī)模。系統(tǒng)響應(yīng)時(shí)間是指從用戶按下最后一個(gè)提交按鈕到所有數(shù)據(jù)都顯示在顯示設(shè)備上所花費(fèi)的時(shí)間。以毫秒或秒表示，該值越低越好。系統(tǒng)響應(yīng)時(shí)間的實(shí)質(zhì)就是終端用戶為等待后臺(tái)事務(wù)處理所花費(fèi)時(shí)間。響應(yīng)時(shí)間通常也稱為用戶收到查詢正被處理的直觀確認(rèn)時(shí)刻所感知的時(shí)間長(zhǎng)度。數(shù)據(jù)庫(kù)用戶響應(yīng)時(shí)間又可以分為系統(tǒng)服務(wù)時(shí)間和用戶等待時(shí)間兩項(xiàng)，即：數(shù)據(jù)庫(kù)用戶響應(yīng)時(shí)間=系統(tǒng)服務(wù)時(shí)間+用戶等待時(shí)間。因此，獲得滿意的用戶響應(yīng)時(shí)間有兩個(gè)途徑：l 減少系統(tǒng)服務(wù)時(shí)間，即提高數(shù)據(jù)庫(kù)的吞吐量。l 減少用戶等待時(shí)間，即減少用戶訪問(wèn)同一數(shù)據(jù)庫(kù)資源的沖突率。盡管某些數(shù)據(jù)庫(kù)系統(tǒng)需要實(shí)現(xiàn)系統(tǒng)可能達(dá)到的最高吞吐量，但另一些系統(tǒng)可能有嚴(yán)格的系統(tǒng)響應(yīng)時(shí)間要求。在這種環(huán)境中，漫長(zhǎng)的系統(tǒng)響應(yīng)時(shí)間常常意味著客戶只能在線等待，這將阻礙其他用戶使用系統(tǒng)。這種情況往往意味著失掉商機(jī)，所以縮短系統(tǒng)響應(yīng)時(shí)間是非常重要的。對(duì)于那些對(duì)響應(yīng)時(shí)間有嚴(yán)格要求的系統(tǒng)，必須采取不同的方法對(duì)系統(tǒng)進(jìn)行調(diào)整。為了縮短系統(tǒng)響應(yīng)時(shí)間，可以使系統(tǒng)以遠(yuǎn)低于其實(shí)際吞吐能力的吞吐量運(yùn)行或是將批理作業(yè)放在非營(yíng)業(yè)時(shí)間內(nèi)處理。不少人認(rèn)為數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的性能調(diào)整是在系統(tǒng)運(yùn)行出現(xiàn)問(wèn)題時(shí)才進(jìn)行的，其實(shí)，這種看法是片面的。性能調(diào)整和優(yōu)化工作是在進(jìn)行系統(tǒng)計(jì)劃和設(shè)計(jì)階段就已經(jīng)開(kāi)始了，并且一直貫穿系統(tǒng)的運(yùn)行周期。在計(jì)劃階段就仔細(xì)地考慮性能調(diào)整工作，這會(huì)大大減輕系統(tǒng)運(yùn)行后的性能調(diào)整工作。在數(shù)據(jù)庫(kù)的性能調(diào)整過(guò)程中需要應(yīng)用程序設(shè)計(jì)人員、應(yīng)用程序開(kāi)發(fā)人員、數(shù)據(jù)庫(kù)管理員以及系統(tǒng)管理員共同完成。下面以優(yōu)先順序給出性能調(diào)整的步驟：為了得到最佳的性能，在數(shù)據(jù)庫(kù)設(shè)計(jì)過(guò)程中要采用業(yè)務(wù)邏輯。這就關(guān)系到對(duì)整個(gè)系統(tǒng)的高層分析和設(shè)計(jì)。像是否在系統(tǒng)范圍內(nèi)采用線程服務(wù)器這樣的配置問(wèn)題就是這一階段需要考慮的問(wèn)題。這樣設(shè)計(jì)者將性能要求同具體的業(yè)務(wù)需求直接聯(lián)系起來(lái)。在數(shù)據(jù)設(shè)計(jì)階段，設(shè)計(jì)人員必須決定哪些數(shù)據(jù)是應(yīng)用所需的，同時(shí)還要考慮哪些關(guān)系是重要的，以及它們的屬性是什么，還要確定哪些屬性可以作為主鍵(Primary Key)或外鍵(Foreign Key)。最后，要構(gòu)造好信息來(lái)更好地滿足性能目標(biāo)。這一階段需要對(duì)數(shù)據(jù)進(jìn)行仔細(xì)分析，避免數(shù)據(jù)冗余，同時(shí)要考慮合理分布數(shù)據(jù)，以防止數(shù)據(jù)存取的擁擠，如是否使用數(shù)據(jù)庫(kù)的分區(qū)功能，以及對(duì)于經(jīng)常訪問(wèn)的數(shù)據(jù)庫(kù)表是否需要建立局部還是全局索引等。這一階段就是通過(guò)增加、減少或調(diào)整邏輯結(jié)構(gòu)，如考慮在主鍵和外鍵上建立索引之外增加其它索引以提高系統(tǒng)性能。這一階段主要是在優(yōu)化數(shù)據(jù)庫(kù)服務(wù)器之前，首先確保在應(yīng)用中已充分利用了為了提高性能而設(shè)計(jì)的SQL以及其中的一些特殊功能。如在SQL Server數(shù)據(jù)庫(kù)中的數(shù)組的處理、SQL優(yōu)化器、行級(jí)鎖管理以及SQL Server查詢分析器等。應(yīng)用程序的執(zhí)行最終將歸結(jié)為數(shù)據(jù)庫(kù)中的SQL語(yǔ)句執(zhí)行，因此SQL語(yǔ)句的執(zhí)行效率最終決定了數(shù)據(jù)庫(kù)的性能。3 性能優(yōu)化技術(shù)介紹一個(gè)數(shù)據(jù)庫(kù)系統(tǒng)的生命周期可以分成：設(shè)計(jì)、開(kāi)發(fā)和成品三個(gè)階段。在設(shè)計(jì)階段進(jìn)行數(shù)據(jù)庫(kù)性能優(yōu)化的成本最低，收益最大。在成品階段進(jìn)行數(shù)據(jù)庫(kù)性能優(yōu)化的成本最高，收益最小。要在SQL Server方案中實(shí)現(xiàn)最優(yōu)的性能，最關(guān)鍵的是要有一很好的數(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)題。一般來(lái)說(shuō)，邏輯數(shù)據(jù)庫(kù)設(shè)計(jì)的不合理容易產(chǎn)生以下問(wèn)題，數(shù)據(jù)冗余、更新異常、插入異常、刪除異常。所以邏輯數(shù)據(jù)庫(kù)設(shè)計(jì)至少應(yīng)滿足規(guī)范化的前3級(jí)標(biāo)準(zhǔn)：第1規(guī)范：沒(méi)有重復(fù)的元組或多值的列。第2規(guī)范：每個(gè)非關(guān)鍵字段必須依賴于主關(guān)鍵字，不能依賴于1個(gè)組合式主關(guān)鍵字的某些組成部分。第3規(guī)范：每個(gè)非關(guān)鍵字段必須依賴于主關(guān)鍵字，不能依賴于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ìn)行數(shù)據(jù)庫(kù)邏輯結(jié)構(gòu)設(shè)計(jì)時(shí)應(yīng)綜合考慮數(shù)據(jù)冗余和基于連接的查詢帶來(lái)的性能問(wèn)題。數(shù)據(jù)文件和日志文件的位置和分布對(duì)系統(tǒng)的性能非常重要。數(shù)據(jù)庫(kù)設(shè)計(jì)的兩條關(guān)鍵性的指導(dǎo)原則如下：將序列訪問(wèn)的文件分離到專用的磁盤上，并通過(guò)將數(shù)據(jù)文件分散到各個(gè)磁盤上而允許并行I/O。，特別是對(duì)于被索引的列更是如此。比如能使用smallint類型就不要用int類型，這樣索引字段可以被更快地讀取，而且可以在1個(gè)數(shù)據(jù)頁(yè)上放置更多的數(shù)據(jù)行，因而也就減少了I/O操作。 Server分區(qū)技術(shù)把一個(gè)頻繁使用的大表分割開(kāi)，并放在多個(gè)單獨(dú)的智能型磁盤控制器的數(shù)據(jù)庫(kù)設(shè)備上，這樣也可以提高性能。因?yàn)橛卸鄠€(gè)磁頭在查找，所以數(shù)據(jù)分離也能提高性能,SQL SERVER的分區(qū)技術(shù)將在下面講到。 Server段把文本或圖像列的數(shù)據(jù)存放在1個(gè)單獨(dú)的物理設(shè)備上可以提高性能。1個(gè)專用的智能型的控制器能進(jìn)一步提高性能。 超大型數(shù)據(jù)庫(kù)的大小常常達(dá)到數(shù)百GB，有時(shí)甚至要用TB來(lái)計(jì)算。而單表的數(shù)據(jù)量往往會(huì)達(dá)到上億的記錄，并且記錄數(shù)會(huì)隨著時(shí)間而增長(zhǎng)。這不但影響著數(shù)據(jù)庫(kù)的運(yùn)行效率，也增大數(shù)據(jù)庫(kù)的維護(hù)難度。除了表的數(shù)據(jù)量外，對(duì)表不同的訪問(wèn)模式也可能會(huì)影響性能和可用性。這些問(wèn)題都可以通過(guò)對(duì)大表進(jìn)行合理分區(qū)得到很大的改善。當(dāng)表和索引變得非常大時(shí)，分區(qū)可以將數(shù)據(jù)分為更小、更容易管理的部分來(lái)提高系統(tǒng)的運(yùn)行效率。如果系統(tǒng)有多個(gè)CPU或是多個(gè)磁盤子系統(tǒng)，可以通過(guò)并行操作獲得更好的性能。所以對(duì)大表進(jìn)行分區(qū)是處理海量數(shù)據(jù)的一種十分高效的方法。下面通過(guò)一個(gè)具體實(shí)例，介紹如何創(chuàng)建和修改分區(qū)表，以及如何查看分區(qū)表。 分區(qū)功能是SQL SERVER 2005新增的一個(gè)功能，表分區(qū)分為水平分區(qū)和垂直分區(qū)。水平分區(qū)將表分為多個(gè)表。每個(gè)表包含的列數(shù)相同，但是行更少。例如，可以將一個(gè)包含十億行的表水平分區(qū)成12個(gè)表，每個(gè)小表表示特定年份內(nèi)一個(gè)月的數(shù)據(jù)。任何需要特定月份數(shù)據(jù)的查詢只需引用相應(yīng)月份的表。而垂直分區(qū)則是將原始表分成多個(gè)只包含較少列的表。水平分區(qū)是最常用分區(qū)方式，本文以水平分區(qū)來(lái)介紹具體實(shí)現(xiàn)方法。 平分區(qū)常用的方法是根據(jù)時(shí)期和使用對(duì)數(shù)據(jù)進(jìn)行水平分區(qū)。例如本文例子，一個(gè)短信發(fā)送記錄表包含最近一年的數(shù)據(jù)，但是只定期訪問(wèn)本季度的數(shù)據(jù)。在這種情況下，可考慮將數(shù)據(jù)分成四個(gè)區(qū)，每個(gè)區(qū)只包含一個(gè)季度的數(shù)據(jù)。下面具體介紹一下使用分區(qū)表的方法。 立分區(qū)表先要?jiǎng)?chuàng)建文件組，而創(chuàng)建多個(gè)文件組主要是為了獲得好的I/O平衡。一般情況下，文件組數(shù)最好與分區(qū)數(shù)相同，并且這些文件組通常位于不同的磁盤上。每個(gè)文件組可以由一個(gè)或多個(gè)文件構(gòu)成，而每個(gè)分區(qū)必須映射到一個(gè)文件組。一個(gè)文件組可以由多個(gè)分區(qū)使用。為了更好地管理數(shù)據(jù)（例如，為了獲得更精確的備份控制），對(duì)分區(qū)表應(yīng)進(jìn)行設(shè)計(jì)，以便只有相關(guān)數(shù)據(jù)或邏輯分組的數(shù)據(jù)位于同一個(gè)文件組中。使用ALTER DATABASE，添加邏輯文件組名： AER DATABASE[DeanDB]ADD FILEGROUP[FG1] DanDB為數(shù)據(jù)庫(kù)名稱，F(xiàn)G1文件組名。創(chuàng)建文件組后，再使用ALTER DATABASE將文件添加到該文件組中。 ATER DATABASE[DeanDB]ADD FILE(NAME=39。FG139。，F(xiàn)ILENAME=39。C:\DeanData\39。，SIZE=3072KB，F(xiàn)ILEGROWTH=1024KB)TO FILEGROUP[FG1] 類似的建立四個(gè)文件和文件組，并把每一個(gè)存儲(chǔ)數(shù)據(jù)的文件放在不同的磁盤驅(qū)動(dòng)器里。 創(chuàng)建分區(qū)表必須先確定分區(qū)的功能機(jī)制，表進(jìn)行分區(qū)的標(biāo)準(zhǔn)是通過(guò)分區(qū)函數(shù)來(lái)決定的。創(chuàng)建數(shù)據(jù)分區(qū)函數(shù)有RANGE“LEFT|/RIGHT”兩種選擇。代表每個(gè)邊界值在局部的哪一邊。例如存在四個(gè)分區(qū)，則定義三個(gè)邊界點(diǎn)值，并指定每個(gè)值是第一個(gè)分區(qū)的上邊界(LEFT)還是第二個(gè)分區(qū)的下邊界(RIGHT)。代碼如下： CREATE PARTITION FUNCTION[SendSMSPF](datetime)AS RANGE RIGHT FOR VALUES(39。2007040139。，39。2007070139。，39。2007100139。) 創(chuàng)建分區(qū)函數(shù)后，必須將其與分區(qū)方案相關(guān)聯(lián)，以便將分區(qū)指向至特定的文件組。就是定義實(shí)際存放