共計 3815 個字符，預計需要花費 10 分鐘才能閱讀完成。
本篇內容介紹了“sql server 中 filegroup 與 partition 怎么使用”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
1. 基礎知識
一直對于表分區和 filegroup 的概念不是很清晰，今天通過具體的實例來學習什么事 filegroup 和 partition，以及他們的作用。
1.1 通過文件組來管理文件的特性
對于用戶角度來說，需對創建的對象指定存儲的文件組只有三種數據對象：表, 索引和大對象（LOB）
使用文件組可以隔離用戶和文件，使得用戶針對文件組來建立表和索引，而不是實際磁盤中的文件。也就是可以指定將表和索引存儲在不同的文件上面。
使用文件組來管理文件可以使得同一文件組內的不同文件分布在不同的硬盤中，極大的提高了 IO 性能.
SQL SERVER 會根據每個文件設置的初始大小和增長量會自動分配新加入的空間，假設在同一文件組中的文件 A 設置的大小為文件 B 的兩倍，新增一個數據占用三頁 (Page), 則按比例將 2 頁分配到文件 A 中，1 頁分配到文件 B 中.
1.2 文件的分類
首要文件: 這個文件是必須有的，而且只能有一個。這個文件額外存放了其他文件的位置等信息. 擴展名為.mdf
次要文件: 可以建任意多個，用于不同目的存放. 擴展名為.ndf，用于存放數據，而不是日志。
日志文件：存放日志, 擴展名為.ldf
在 SQL SERVER 2008 之后，還新增了文件流數據文件和全文索引文件.
我們可以通過 sys.database_files 這個視圖查看數據庫中的文件情況:
雙擊代碼全選
1select*from sys.database_files
1.3 創建 filegroup，并將索引創建在指定的 filegroup 中
可 以通過 TSQL 語句來創建文件組，也可以通過 SSMS 來創建文件組，這個在后面會提到。這里不再重復。下面我們重點來介紹如何將索引創建在指定的 filegroup 中，而不跟數據放在一起。首先來看我創建好的 filegroup，已經這些 filegroup 所對應的 files，如下圖所示：
然后我們通過如下 TSQL 語句來測試
雙擊代碼全選
28use TESTDB
–step1. 插入數據
select * into OrderDetail from AdventureWorks2008R2.Sales.SalesOrderDetail
–step2: 查看表的索引信息，發現所有頁都在 pagefid= 1 上面，并且 indexid 都為 0. 因為沒有創建聚集索引之前是堆表
dbcc ind (TESTDB, [dbo.OrderDetail], -1)
–step3: 在分區上創建聚集索引，聚集索引不要放在 IndexStorage 這個 filegroup 當中，因為聚集索引就是數據本身。– 如果將聚集索引 on IndexStorage 的話，那么所有數據都將會在 IndexStorage 這個 filegroup 所對應的文件上 create clustered index idx_c_SSalesOrderDetailID on OrderDetail(SalesOrderDetailID)
–step4: 此時發現原先 indexid= 0 的都變成了 index=1 dbcc ind (TESTDB, [dbo.OrderDetail], -1)
–step5: 在 IndexStorage 這個 file group 上面創建非聚集索引 CREATE NONCLUSTERED INDEX idx_nc_SalesOrderID ON dbo.OrderDetail(SalesOrderID) on IndexStorage CREATE NONCLUSTERED INDEX idx_nc_CarrierTrackingNumber ON dbo.OrderDetail(CarrierTrackingNumber) on IndexStorage CREATE NONCLUSTERED INDEX idx_nc_UnitPrice ON dbo.OrderDetail(UnitPrice) on IndexStorage
–step6: 再次查看頁信息我們發現只有 indexid= 1 的 pagefid=1，也就是說聚集索引都在 TESTDB.MDF 這個文件上，– 而 indexid=2,3,4 所對應的 pagefid=3，表明已經將索引建立到 IndexStorage 這個 filegroup 上面去了，對應的是 IndexStorage.ndf 這個文件。dbcc ind (TESTDB, [dbo.OrderDetail], -1)
–step7: 創建復合索引，CREATE NONCLUSTERED INDEX idx_nc_com ON dbo.OrderDetail(SalesOrderID,CarrierTrackingNumber,UnitPrice)
–step8: 默認情況下會使用 Primary 這個 filegroup，filefid=1. dbcc ind (TESTDB, [dbo.OrderDetail], -1)
總結：
在分區上創建聚集索引，聚集索引不要放在 IndexStorage 這個 filegroup 當中，因為聚集索引就是數據本身。如果將聚集索引 on IndexStorage 的話，那么所有數據都將會在 IndexStorage 這個 filegroup 所對應的文件上。
在創建非聚集索引的時候，通過在創建索引語句的最后加上 on [filegroup] 指定需要將這個索引放在哪一個 filegroup 當中，如果不加的話會使用默認 filegroup，我們這里的默認 filegroup 是 priamry。
1.4 使用多個文件的好處
使用多個文件分布數據到多個硬盤中可以極大的提高 IO 性能. 放在一個磁盤中基本沒有效果。
場景描述
應用程序發來大量的并發語句在修改同一張表格里的記錄，而表格架構設計以及用戶業務邏輯使得這些修改都集中在同一個頁面，或者數量不多的幾個頁面上。這些頁面有的時候也被稱為 Hot Page。這樣的瓶頸通常只會發生在并發用戶比較多的、典型的 OLTP 系統上。這種瓶頸是無法通過提高硬件配置解決的，只有通過修改表格設計或者業務邏輯，讓修改分散到盡可能多的頁面上，才能提高并發性能。
在現實環境里，可以試想下面的情形。一個股票交易系統，每一筆交易都會有一個流水號，是遞增且不可重復的。而客戶發過來的交易請求，都要存儲在同一張交易表里。每一個新的交易，都要插入一條新記錄。如果設計者選擇在流水號上建聚集索引（這也是很自然的），就容易遇到 Hot Page 的 PAGELATCH 資源瓶頸。在同一時間，只能有一個用戶插入一筆交易。
怎樣才能解決或者緩解這種瓶頸呢？
最簡單的方法，是換一個數據列建聚集索引，而不要建在 Identity 的字段上。這樣表格里的數據就按照其他方式排序，同一時間的插入就有機會分散在不同的頁面上。
如果實在是一定要在 Identity 的字段上建聚集索引，建議根據其他某個數據列在表格上建立若干個分區（Partition）。把一個表格分成若干個分區，可以使得接受新數據的頁面數目增加。
還是以上面那個股票交易系統為例子。不同的股票屬于不同的行業。開發者可以根據股票的行業屬性，將一張交易表分成若干個分區。在 SQL Server 里，已分區表（Partitioned Table）的每個分區都是一個獨立的存儲單位。分屬不同分區的數據行是嚴格分開存儲的。所以同一個時間發生的交易記錄，因其行業不同，也會被分別保存在不同的分區里。這樣，在同一個時間點，可以插入不同行業的交易記錄。每個分區上的 Hot Page（接受新數據插入的 page）就不那么 hot 了。
在我的事例中，是有一張 SalesOrderDetail 表，其數據量很大，我希望按照 UnitPrice 這個字段進行分區。下面來看具體步驟。
step1：創建 filegroup
在 sql server 中好像沒有 create filegroup 的說法，只是在現成的數據庫中添加 filegroup 而已。下面的代碼中首先創建數據庫，然后添加四個 filegroup，tsql 代碼如下所示：
雙擊代碼全選
12–step1—— – 創建數據庫 create database TEST USE MASTER GO –40 萬行分成 5 個文件組，PRIMARY 加下面四個文件組，– 命名規則:FG_數據庫名_表名_字段名_流水號 ALTER DATABASE TEST ADD FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_1; ALTER DATABASE TEST ADD FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_2; ALTER DATABASE TEST ADD FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_3; ALTER DATABASE TEST ADD FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_4; GO
“sql server 中 filegroup 與 partition 怎么使用”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注丸趣 TV 網站，丸趣 TV 小編將為大家輸出更多高質量的實用文章！
