資料庫效能最佳化一：資料庫自身最佳化（大資料量）

　　資料庫最佳化包含以下三部分，資料庫自身的最佳化，資料庫表最佳化，程式操作最佳化.此文為第一部分　　資料庫效能最佳化一：資料庫自身最佳化　　最佳化①：增加次資料檔案，設定檔案自動成長（粗略資料分區）  1.1：增加次資料檔案

　　從SQL SERVER 2005開始，資料庫不預設產生NDF資料檔案，一般情況下有一個主要資料檔案（MDF）就夠了，但是有些大型的資料庫，由於資訊很多，而且查詢頻繁，所以為了提高查詢速度，可以把一些表或者一些表中的部分記錄分開儲存在不同的資料檔案裡
由於CPU和記憶體的速度遠大於硬碟的讀寫速度，所以可以把不同的資料檔案放在不同的物理硬碟裡，這樣執行查詢的時候，就可以讓多個硬碟同時進行查詢，以充分利用CPU和記憶體的效能，提高查詢速度。 在這裡詳細介紹一下其寫入的原理，資料檔案（MDF、NDF）和記錄檔（LDF）的寫入方式是不一樣的：

　　資料檔案：SQL Server按照同一個檔案組裡面的所有檔案現有空閑空間的大小，按這個比例把新的資料分布到所有有空間的資料檔案裡，如果有三個資料檔案A.MDF，B.NDF，C.NDF，空閑大小分別為200mb，100mb，和50mb，那麼寫入一個70mb的東西，他就會向ABC三個檔案中一次寫入40、20、10的資料，如果某個記錄檔已滿，就不會向其寫入

　　記錄檔：記錄檔是按照順序寫入的，一個寫滿，才會寫入另外一個

　　由上可見，如果能增加其資料檔案NDF,有利於大資料量的查詢速度，但是增加記錄檔卻沒什麼用處。

1.2：設定檔案自動成長（大資料量，小資料量無需設定）

　　在SQL Server 2005中，預設MDF檔案初始大小為5MB，自增為1MB，不限增長，LDF初始為1MB，增長為10%，限制檔案增長到一定的數目，一般設計中，使用SQL內建的設計即可，但是大型資料庫設計中，最好親自去設計其增長和初始大小，如果初始值太小，那麼很快資料庫就會寫滿，如果寫滿，在進行插入會是什麼情況呢？當資料檔案寫滿，進行某些操作時，SQL Server會讓操作等待，直到檔案自動成長結束了，原先的那個操作才能繼續進行。如果自增長用了很長時間，原先的操作會等不及就逾時取消了（一般預設的閾值是15秒），不但這個操作會復原，檔案自動成長也會被取消。也就是說，這一次檔案沒有得到任何增大，增長的時間根據自動成長的大小確定的，如果太小，可能一次操作需要連續幾次增長才能滿足，如果太大，就需要等待很長時間，所以設定自動成長要注意一下幾點：

　　1）要設定成按固定大小增長，而不能按比例。這樣就能避免一次增長太多或者太少所帶來的不必要的麻煩。建議對比較小的資料庫，設定一次增長50 MB到100 MB。對大的資料庫，設定一次增長100 MB到200 MB。

　　2）要定期監測各個資料檔案的使用方式，盡量保證每個檔案剩餘的空間一樣大，或者是期望的比例。

　　3）設定檔案最大值，以免SQL Server檔案自增長用盡磁碟空間，影響作業系統。

　　4）發生自增長後，要及時檢查新的資料檔案空間分配情況。避免SQL Server總是往個別檔案寫資料。

　　因此，對於一個比較繁忙的資料庫，推薦的設定是開啟資料庫自動成長選項，以防資料庫空間用盡導致應用程式失敗，但是要嚴格避免自動成長的發生。同時，盡量不要使用自動收縮功能。 

 1.3 資料和記錄檔分開存放在不同磁碟上　

　 資料檔案和記錄檔的操作會產生大量的I/O。在可能的條件下，記錄檔應該存放在一個與資料和索引所在的資料檔案不同的硬碟上以分散I/O，同時還有利於資料庫的災難恢複。

　　最佳化②：表分區，索引分割區 （最佳化①粗略的進行了表分區，最佳化②為精確資料分區）

　　為什麼要表分區？

 　　 當一個表的資料量太大的時候，我們最想做的一件事是什嗎？將這個表一分為二或者更多分，但是表還是這個表，只是將其內容儲存分開，這樣讀取就快了N倍了

　　原理：表資料是無法放在檔案中的，但是檔案組可以放在檔案中，表可以放在檔案組中，這樣就間接實現了表資料存放在不同的檔案中。能分區儲存的還有：表、索引和大型物件資料 。

　　SQL SERVER 2005中，引入了表分區的概念， 當表中的資料量不斷增大，查詢資料的速度就會變慢，應用程式的效能就會下降，這時就應該考慮對錶進行分區,當一個表裡的資料很多時，可以將其分拆到多個的表裡,因為要掃描的資料變得更少 ,查詢可以更快地運行,這樣操作大大提高了效能,表進行分區後，邏輯上表仍然是一張完整的表，只是將表中的資料在物理上存放到多個資料表空間(物理檔案上)，這樣查詢資料時，不至於每次都掃描整張表 

 2.1什麼時候使用分區表：

   1、表的大小超過2GB。 

  2、表中包含曆史資料，新的資料被增加到新的分區中。 

 2.2表分區的優缺點 

表分區有以下優點： 
  1、改善查詢效能：對分區對象的查詢可以僅搜尋自己關心的分區，提高檢索速度。 
  2、增強可用性：如果表的某個分區出現故障，表在其他分區的資料仍然可用； 
  3、維護方便：如果表的某個分區出現故障，需要修複資料，只修複該分區即可； 
  4、均衡I/O：可以把不同的分區映射到磁碟以平衡I/O，改善整個系統效能。 
缺點： 
  分區表相關：已經存在的表沒有方法可以直接轉化為分區表。不過 Oracle 提供了線上重定義表的功能.

2.3表分區的操作三步走

   2.31 建立分區函數

CREATE PARTITION FUNCTION xx1(int)

AS RANGE LEFT FOR VALUES (10000, 20000);

注釋：建立分區函數：myRangePF2，以INT類型分區，分三個區間，10000以內在A 區，1W-2W在B區，2W以上在C區.

 2.3.2建立分區架構

CREATE PARTITION SCHEME myRangePS2

AS PARTITION xx1

TO (a, b, c);

注釋：在分區函數XX1上建立分區架構：myRangePS2，分別為A,B,C三個區間

A,B,C分別為三個檔案組的名稱，而且必須三個NDF隸屬於這三個組，檔案所屬檔案組一旦建立就不能修改

2.3.3 對錶進行分區

常用資料規範--資料空間類型修改為：資料分割配置，然後選擇資料分割配置名稱和分區列列表，結果：

也可以用sql語句產生

CREATE TABLE [dbo].[AvCache]( 

[AVNote] [varchar](300) NULL,

[bb] [int] IDENTITY(1,1)

) ON [myRangePS2](bb); --注意這裡使用[myRangePS2]架構，根據bb分區

2.3.4查詢表分區

SELECT *, $PARTITION.[myRangePF2](bb)  FROM dbo.AVCache 

這樣就可以清楚的看到表資料是如何分區的了

2.3.5建立索引分割區

 

　　最佳化③：分散式資料庫設計

　　分散式資料庫系統是在集中式資料庫系統的基礎上發展起來的，理解起來也很簡單，就是將整體的資料庫分開，分布到各個地方，就其本質而言，分散式資料庫系統分為兩種：1.資料在邏輯上是統一的，而在物理上卻是分散的，一個分散式資料庫在邏輯上是一個統一的整體，在物理上則是分別儲存在不同的物理節點上，我們通常說的分散式資料庫都是這種2.邏輯是分布的，物理上也是分布的，這種也成聯邦式分布資料庫，由於組成聯邦的各個子資料庫系統是相對“自治”的，這種系統可以容納多種不同用途的、差異較大的資料庫，比較適宜於大範圍內資料庫的整合。

　　分散式資料庫較為複雜，在此不作詳細的使用和說明，只是舉例說明一下，現在分散式資料庫多用於使用者分區性較強的系統中，如果一個全國連鎖店，一般設計為每個分店都有自己的銷售和庫存等資訊，總部則需要有員工，供應商，分店資訊等資料庫，這類型的分店資料庫可以完全一致，很多系統也可能導致不一致，這樣，各個連鎖店資料存放區在本地，從而提高了影響速度，降低了通訊費用，而且資料分布在不同場地，且存有多個副本，即使個別場地發生故障，不致引起整個系統的癱瘓。 但是他也帶來很多問題，如：資料一致性問題、資料遠程傳遞的實現、通訊開銷的降低等，這使得分散式資料庫系統的開發變得較為複雜，只是讓大家明白其原理，具體的使用方式就不做詳細的介紹了。 

　　最佳化④：整理資料庫片段

　　如果你的表已經建立好了索引，但效能卻仍然不好，那很可能是產生了索引片段，你需要進行索引磁碟重組。

　　什麼是索引片段?

　　由於表上有過度地插入、修改和刪除操作，索引頁被分成多塊就形成了索引片段，如果索引片段嚴重，那掃描索引的時間就會變長，甚至導致索引不可用，因此資料檢索操作就慢下來了。

　　如何知道是否發生了索引片段?

在SQLServer資料庫，通過DBCC ShowContig或DBCC ShowContig(表名)檢查索引片段情況，指導我們對其進行定時重建整理。 

  

通過對掃描密度（過低），掃描片段（過高）的結果分析，判定是否需要索引重建，主要看如下兩個：
Scan Density [Best Count:Actual Count]-掃描密度［最佳值:實際值］：DBCC SHOWCONTIG返回最有用的一個百分比。這是擴充盤區的最佳值和實際值的比率。該百分比應該儘可能靠近100％。低了則說明有外部片段。

Logical Scan Fragmentation-邏輯掃描片段：無序頁的百分比。該百分比應該在0％到10％之間，高了則說明有外部片段。 

解決方式：

一是利用DBCC INDEXDEFRAG整理索引片段

二是利用DBCC DBREINDEX重建索引。

兩者區別調用微軟的原話如下：
DBCC INDEXDEFRAG 命令是聯機操作，所以索引只有在該命令正在運行時才可用，而且可以在不丟失已完成工作的情況下中斷該操作。這種方法的缺點是在重新組織資料方面沒有叢集索引的除去/重新建立操作有效。
重新建立叢集索引將對資料進行重新組織，其結果是使資料頁填滿。填滿程度可以使用 FILLFACTOR 選項進行配置。這種方法的缺點是索引在除去/重新建立周期內為離線狀態，並且操作屬原子級。如果中斷索引建立，則不會重新建立該索引。也就是說，要想獲得好的效果，還是得用重建索引，所以決定重建索引。

 　　　　　　　　　

 　　以上可能部分文章借鑒了其他的網路文章，本文僅為學習使用，轉載請註明出處

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　     --------------AK（老K）：2012-12-28