數據庫優化實踐

 　　優化技巧主要是面向DBA的，但我認為即使是開發人員也應該掌握這些技巧，因為不是每個開發團隊都配有專門的DBA的。

　　第九步：合理組織數據庫文件組和文件

　　創建SQL Server數據庫時，數據庫服務器會自動在文件系統上創建一系列的文件，之後創建的每一個數據庫對象實際上都是存儲在這些文件中的。SQL Server有下面三種文件：

　　1).mdf文件

　　這是最主要的數據文件，每個數據庫只能有一個主數據文件，所有系統對象都存儲在主數據文件中，如果不創建次要數據文件，所有用戶對象(用戶創建的數據庫對象)也都存儲在主數據文件中。

　　2).ndf文件

　　這些都是次要數據文件，它們是可選的，它們存儲的都是用戶創建的對象。

　　3).ldf文件

　　這些是事務日志文件，數量從一到幾個不等，它裡面存儲的是事務日志。

　　默認情況下，創建SQL Server數據庫時會自動創建主數據文件和事務日志文件，當然也可以修改這兩個文件的屬性，如保存路徑。

　　文件組

　　為了便於管理和獲得更好的性能，數據文件通常都進行了合理的分組，創建一個新的SQL Server數據庫時，會自動創建主文件組，主數據文件就包含在主文件組中，主文件組也被設為默認組，因此所有新創建的用戶對象都自動存儲在主文件組中(具體說就是存儲在主數據文件中)。

　　如果你想將你的用戶對象(表、視圖、存儲過程和函數等)存儲在次要數據文件中，那需要：

　　1)創建一個新的文件組，並將其設為默認文件組;

　　2)創建一個新的數據文件(.ndf)，將其歸於第一步創建的新文件組中。

　　以後創建的對象就會全部存儲在次要文件組中了。

　　注意：事務日志文件不屬於任何文件組。

　　文件/文件組組織最佳實踐

　　如果你的數據庫不大，那麼默認的文件/文件組應該就能滿足你的需要，但如果你的數據庫變得很大時(假設有1000MB)，你可以(應該)對文件/文件組進行調整以獲得更好的性能，調整文件/文件組的最佳實踐內容如下：

　　1)主文件組必須完全獨立，它裡面應該只存儲系統對象，所有的用戶對象都不應該放在主文件組中。主文件組也不應該設為默認組，將系統對象和用戶對象分開可以獲得更好的性能;

　　2)如果有多塊硬盤，可以將每個文件組中的每個文件分配到每塊硬盤上，這樣可以實現分布式磁盤I/O，大大提高數據讀寫速度;

　　3)將訪問頻繁的表及其索引放到一個單獨的文件組中，這樣讀取表數據和索引都會更快;

　　4)將訪問頻繁的包含Text和Image數據類型的列的表放到一個單獨的文件組中，最好將其中的Text和Image列數據放在一個獨立的硬盤中，這樣檢索該表的非Text和Image列時速度就不會受Text和Image列的影響;

　　5)將事務日志文件放在一個獨立的硬盤上，千萬不要和數據文件共用一塊硬盤，日志操作屬於寫密集型操作，因此保證日志寫入具有良好的I/O性能非常重要;

　　6)將“只讀”表單獨放到一個獨立的文件組中，同樣，將“只寫”表單獨放到一個文件組中，這樣只讀表的檢索速度會更快，只寫表的更新速度也會更快;

　　7)不要過度使用SQL Server的“自動增長”特性，因為自動增長的成本其實是很高的，設置“自動增長”值為一個合適的值，如一周，同樣，也不要過度頻繁地使用“自動收縮”特性，最好禁用掉自動收縮，改為手工收縮數據庫大小，或使用調度操作，設置一個合理的時間間隔，如一個月。

　　第十步：在大表上應用分區

　　什麼是表分區?

　　表分區就是將大表拆分成多個小表，以免檢索數據時掃描的數據太多，這個思想參考了“分而治之”的理論。

　　當你的數據庫中有一個大表(假設有上百萬行記錄)，如果其它優化技巧都用上了，但查詢速度仍然非常慢時，你就應該考慮對這個表進行分區了。首先來看一下分區的類型：

　　水平分區：假設有一個表包括千萬行記錄，為了便於理解，假設表有一個自動增長的主鍵字段(如id)，我們可以將表拆分成10個獨立的分區表，每個分區包含100萬行記錄，分區就要依據id字段的值實施，即第一個分區包含id值從1-1000000的記錄，第二個分區包含1000001-2000000的記錄，以此類推。這種以水平方向分割表的方式就叫做水平分區。

　　垂直分區：假設有一個表的列數和行數都非常多，其中某些列被經常訪問，其余的列不是經常訪問。由於表非常大，所有檢索操作都很慢，因此需要基於頻繁訪問的列進行分區，這樣我們可以將這個大表拆分成多個小表，每個小表由大表的一部分列組成，這種垂直拆分表的方法就叫做垂直分區。

　　另一個垂直分區的原則是按有索引的列無索引列進行拆分，但這種分區法需要小心，因為如果任何查詢都涉及到檢索這兩個分區，SQL引擎不得不連接這兩個分區，那樣的話性能反而會低。

　　本文主要對水平分區做一介紹。

　　分區最佳實踐

　　1)將大表分區後，將每個分區放在一個獨立的文件中，並將這個文件存放在獨立的硬盤上，這樣數據庫引擎可以同時並行檢索多塊硬盤上的不同數據文件，提高並發讀寫速度;

　　2)對於歷史數據，可以考慮基於歷史數據的“年齡”進行分區，例如，假設表中存儲的是訂單數據，可以使用訂單日期列作為分區的依據，如將每年的訂單數據做成一個分區。

　　如何分區?

　　假設Order表中包含了四年(1999-2002)的訂單數據，有上百萬的記錄，那如果要對這個表進行分區，采取的步驟如下：

　　1)添加文件組

　　使用下面的命令創建一個文件組:

　　ALTER DATABASE OrderDB ADD FILEGROUP [1999]

　　ALTER DATABASE OrderDB ADD FILE (NAME = N'1999', FILENAME

　　= N'C:OrderDB1999.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO

　　FILEGROUP [1999]

　　通過上面的語句我們添加了一個文件組1999，然後增加了一個次要數據文件“C:OrderDB1999.ndf”到這個文件組中。

　　使用上面的命令再創建三個文件組2000，2001和2002，每個文件組存儲一年的銷售數據。

　　2)創建分區函數

　　分區函數是定義分界點的一個對象，使用下面的命令創建分區函數：

　　CREATE PARTITION FUNCTION FNOrderDateRange (DateTime) AS

　　RANGE LEFT FOR VALUES ('19991231', '20001231', '20011231')

　　上面的分區函數指定：

　　DateTime<=1999/12/31的記錄進入第一個分區;

　　DateTime > 1999/12/31 且 <= 2000/12/31的記錄進入第二個分區;

　　DateTime > 2000/12/31 且 <= 2001/12/31的記錄進入第三個分區;

　　DateTime > 2001/12/31的記錄進入第四個分區。

　　RANGE LEFT指定應該進入左邊分區的邊界值，例如小於或等於1999/12/31的值都應該進入第一個分區，下一個值就應該進入第二個分區了。如果使用RANGE RIGHT，邊界值以及大於邊界值的值都應該進入右邊的分區，因此在這個例子中，邊界值2000/12/31就應該進入第二個分區，小於這個邊界值的值就應該進入第一個分區。

　　3)創建分區方案

　　通過分區方案在表/索引的分區和存儲它們的文件組之間建立映射關系。創建分區方案的命令如下：

　　CREATE PARTITION SCHEME OrderDatePScheme AS PARTITION FNOrderDateRange

　　TO ([1999], [2000], [2001], [2002])

　　在上面的命令中，我們指定了：

　　第一個分區應該進入1999文件組;

　　第二個分區就進入2000文件組;

　　第三個分區進入2001文件組;

　　第四個分區進入2002文件組。

　　4)在表上應用分區

　　至此，我們定義了必要的分區原則，現在需要做的就是給表分區了。首先使用DROP INDEX命令刪除表上現有的聚集索引，通常主鍵上有聚集索引，如果是刪除主鍵上的索引，還可以通過DROP CONSTRAINT刪除主鍵來間接刪除主鍵上的索引，如下面的命令刪除PK_Orders主鍵：

　　ALTER TABLE Orders DROP CONSTRAINT PK_Orders;

　　在分區方案上重新創建聚集索引，命令如下：

　　CREATE UNIQUE CLUSTERED INDEX PK_Orders ON Orders(OrderDate) ON

　　OrderDatePScheme (OrderDate)

　　假設OrderDate列的數據在表中是唯一的，表將基於分區方案OrderDatePScheme被分區，最終被分成四個小的部分，存放在四個文件組中。如果你對如何分區還有不清楚的地方，建議你去看看微軟的官方文章“SQL Server 2005中的分區表和索引”(地址：http://msdn.microsoft.com/en-us/library/ms345146%28SQL.90%29.aspx)。

　　第十一步：使用TSQL模板更好地管理DBMS對象(額外的一步)

　　為了更好地管理DBMS對象(存儲過程，函數，視圖，觸發器等)，需要遵循一致的結構，但由於某些原因(主要是時間限制)，我們未能維護一個一致的結構，因此後來遇到性能問題或其它原因需要重新調試這些代碼時，那感覺就像是做噩夢。

　　為了幫助大家更好地管理DBMS對象，我創建了一些TSQL模板，利用這些模板你可以快速地開發出結構一致的DBMS對象。

　　如果你的團隊有人專門負責檢查團隊成員編寫的TSQL代碼，在這些模板中專門有一個“審查”段落用來描寫審查意見。

　　我提交幾個常見的DBMS對象模板，它們是：

　　Template_StoredProcedure.txt：存儲過程模板(http://www.codeproject.com/KB/database/OrganizeFilesAndPartition/Template_StoredProcedure.txt)

　　Template_Vi