sql server中filegroup與partition怎么使用

本篇內(nèi)容介紹了“sql server中filegroup與partition怎么使用”的有關(guān)知識(shí)，在實(shí)際案例的操作過程中，不少人都會(huì)遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

1.基礎(chǔ)知識(shí)

一直對于表分區(qū)和filegroup的概念不是很清晰，今天通過具體的實(shí)例來學(xué)習(xí)什么事filegroup和partition，以及他們的作用。

1.1通過文件組來管理文件的特性

對于用戶角度來說，需對創(chuàng)建的對象指定存儲(chǔ)的文件組只有三種數(shù)據(jù)對象：表,索引和大對象（LOB）
使用文件組可以隔離用戶和文件，使得用戶針對文件組來建立表和索引，而不是實(shí)際磁盤中的文件。也就是可以指定將表和索引存儲(chǔ)在不同的文件上面。
使用文件組來管理文件可以使得同一文件組內(nèi)的不同文件分布在不同的硬盤中，極大的提高了IO性能.
SQL SERVER會(huì)根據(jù)每個(gè)文件設(shè)置的初始大小和增長量會(huì)自動(dòng)分配新加入的空間，假設(shè)在同一文件組中的文件A設(shè)置的大小為文件B的兩倍，新增一個(gè)數(shù)據(jù)占用三頁(Page),則按比例將2頁分配到文件A中，1頁分配到文件B中.

1.2文件的分類

• 首要文件:這個(gè)文件是必須有的，而且只能有一個(gè)。這個(gè)文件額外存放了其他文件的位置等信息.擴(kuò)展名為.mdf

• 次要文件:可以建任意多個(gè)，用于不同目的存放.擴(kuò)展名為.ndf，用于存放數(shù)據(jù)，而不是日志。

• 日志文件：存放日志,擴(kuò)展名為.ldf

   在SQL SERVER 2008之后，還新增了文件流數(shù)據(jù)文件和全文索引文件.
   我們可以通過sys.database_files這個(gè)視圖查看數(shù)據(jù)庫中的文件情況:

1.3創(chuàng)建filegroup，并將索引創(chuàng)建在指定的filegroup中

可 以通過TSQL語句來創(chuàng)建文件組，也可以通過SSMS來創(chuàng)建文件組，這個(gè)在后面會(huì)提到。這里不再重復(fù)。下面我們重點(diǎn)來介紹如何將索引創(chuàng)建在指定的 filegroup中，而不跟數(shù)據(jù)放在一起。首先來看我創(chuàng)建好的filegroup，已經(jīng)這些filegroup所對應(yīng)的files，如下圖所示：

然后我們通過如下TSQL語句來測試

總結(jié)：

• 在分區(qū)上創(chuàng)建聚集索引，聚集索引不要放在IndexStorage這個(gè)filegroup當(dāng)中，因?yàn)榫奂饕褪菙?shù)據(jù)本身。如果將聚集索引on IndexStorage的話，那么所有數(shù)據(jù)都將會(huì)在IndexStorage這個(gè)filegroup所對應(yīng)的文件上。

• 在創(chuàng)建非聚集索引的時(shí)候，通過在創(chuàng)建索引語句的最后加上 on [filegroup]指定需要將這個(gè)索引放在哪一個(gè)filegroup當(dāng)中，如果不加的話會(huì)使用默認(rèn)filegroup，我們這里的默認(rèn)filegroup是priamry。

1.4使用多個(gè)文件的好處

使用多個(gè)文件分布數(shù)據(jù)到多個(gè)硬盤中可以極大的提高IO性能.放在一個(gè)磁盤中基本沒有效果。

場景描述

應(yīng)用程序發(fā)來大量的并發(fā)語句在修改同一張表格里的記錄，而表格架構(gòu)設(shè)計(jì)以及用戶業(yè)務(wù)邏輯使得這些修改都集中在同一個(gè)頁面，或者數(shù)量不多的幾個(gè)頁面上。這些頁面有的時(shí)候也被稱為Hot Page。這樣的瓶頸通常只會(huì)發(fā)生在并發(fā)用戶比較多的、典型的OLTP系統(tǒng)上。這種瓶頸是無法通過提高硬件配置解決的，只有通過修改表格設(shè)計(jì)或者業(yè)務(wù)邏輯，讓修改分散到盡可能多的頁面上，才能提高并發(fā)性能。
在現(xiàn)實(shí)環(huán)境里，可以試想下面的情形。一個(gè)股票交易系統(tǒng)，每一筆交易都會(huì)有一個(gè)流水號，是遞增且不可重復(fù)的。而客戶發(fā)過來的交易請求，都要存儲(chǔ)在同一張交易表里。每一個(gè)新的交易，都要插入一條新記錄。如果設(shè)計(jì)者選擇在流水號上建聚集索引（這也是很自然的），就容易遇到Hot Page的PAGELATCH資源瓶頸。在同一時(shí)間，只能有一個(gè)用戶插入一筆交易。
怎樣才能解決或者緩解這種瓶頸呢？

1. 最簡單的方法，是換一個(gè)數(shù)據(jù)列建聚集索引，而不要建在Identity的字段上。這樣表格里的數(shù)據(jù)就按照其他方式排序，同一時(shí)間的插入就有機(jī)會(huì)分散在不同的頁面上。

2. 如果實(shí)在是一定要在Identity的字段上建聚集索引，建議根據(jù)其他某個(gè)數(shù)據(jù)列在表格上建立若干個(gè)分區(qū)（Partition）。把一個(gè)表格分成若干個(gè)分區(qū)，可以使得接受新數(shù)據(jù)的頁面數(shù)目增加。

還是以上面那個(gè)股票交易系統(tǒng)為例子。不同的股票屬于不同的行業(yè)。開發(fā)者可以根據(jù)股票的行業(yè)屬性，將一張交易表分成若干個(gè)分區(qū)。在SQL Server里，已分區(qū)表（Partitioned Table）的每個(gè)分區(qū)都是一個(gè)獨(dú)立的存儲(chǔ)單位。分屬不同分區(qū)的數(shù)據(jù)行是嚴(yán)格分開存儲(chǔ)的。所以同一個(gè)時(shí)間發(fā)生的交易記錄，因其行業(yè)不同，也會(huì)被分別保存在不同的分區(qū)里。這樣，在同一個(gè)時(shí)間點(diǎn)，可以插入不同行業(yè)的交易記錄。每個(gè)分區(qū)上的Hot Page（接受新數(shù)據(jù)插入的page）就不那么hot了。
在我的事例中，是有一張SalesOrderDetail表，其數(shù)據(jù)量很大，我希望按照UnitPrice這個(gè)字段進(jìn)行分區(qū)。下面來看具體步驟。

step1：創(chuàng)建filegroup

在sql server中好像沒有create filegroup的說法，只是在現(xiàn)成的數(shù)據(jù)庫中添加filegroup而已。下面的代碼中首先創(chuàng)建數(shù)據(jù)庫，然后添加四個(gè)filegroup，tsql代碼如下所示：

“sql server中filegroup與partition怎么使用”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！