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

創(chuàng)新互聯(lián)長(zhǎng)期為上1000+客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對(duì)不同對(duì)象提供差異化的產(chǎn)品和服務(wù)；打造開(kāi)放共贏平臺(tái)，與合作伙伴共同營(yíng)造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為湖濱企業(yè)提供專業(yè)的成都網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站，湖濱網(wǎng)站改版等技術(shù)服務(wù)。擁有十余年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開(kāi)發(fā)。

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

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

1.1通過(guò)文件組來(lái)管理文件的特性

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

1.2文件的分類

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

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

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

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

雙擊代碼全選

1	select*from sys.database_files

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

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

然后我們通過(guò)如下TSQL語(yǔ)句來(lái)測(cè)試

雙擊代碼全選

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

use TESTDB  --step1.插入數(shù)據(jù)  select * into OrderDetail from AdventureWorks2008R2.Sales.SalesOrderDetail       --step2:查看表的索引信息，發(fā)現(xiàn)所有頁(yè)都在pagefid=1上面，并且indexid都為0.因?yàn)闆](méi)有創(chuàng)建聚集索引之前是堆表  dbcc ind ( TESTDB, [dbo.OrderDetail], -1)      --step3:在分區(qū)上創(chuàng)建聚集索引，聚集索引不要放在IndexStorage這個(gè)filegroup當(dāng)中，因?yàn)榫奂饕褪菙?shù)據(jù)本身。 --如果將聚集索引on IndexStorage的話，那么所有數(shù)據(jù)都將會(huì)在IndexStorage這個(gè)filegroup所對(duì)應(yīng)的文件上 create clustered index idx_c_SSalesOrderDetailID on  OrderDetail(SalesOrderDetailID)       --step4:此時(shí)發(fā)現(xiàn)原先indexid=0的都變成了index=1 dbcc ind ( TESTDB, [dbo.OrderDetail], -1)      --step5:在IndexStorage這個(gè)file group上面創(chuàng)建非聚集索引 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:再次查看頁(yè)信息我們發(fā)現(xiàn)只有indexid=1的pagefid=1，也就是說(shuō)聚集索引都在TESTDB.MDF這個(gè)文件上， --而indexid=2,3,4所對(duì)應(yīng)的pagefid=3，表明已經(jīng)將索引建立到IndexStorage這個(gè)filegroup上面去了，對(duì)應(yīng)的是IndexStorage.ndf這個(gè)文件。 dbcc ind ( TESTDB, [dbo.OrderDetail], -1)      --step7:創(chuàng)建復(fù)合索引， CREATE NONCLUSTERED INDEX idx_nc_com ON dbo.OrderDetail(SalesOrderID,CarrierTrackingNumber,UnitPrice)      --step8:默認(rèn)情況下會(huì)使用Primary這個(gè)filegroup，filefid=1. dbcc ind ( TESTDB, [dbo.OrderDetail], -1)

總結(jié)：

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

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

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

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

場(chǎng)景描述

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

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

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

還是以上面那個(gè)股票交易系統(tǒng)為例子。不同的股票屬于不同的行業(yè)。開(kāi)發(fā)者可以根據(jù)股票的行業(yè)屬性，將一張交易表分成若干個(gè)分區(qū)。在SQL Server里，已分區(qū)表（Partitioned Table）的每個(gè)分區(qū)都是一個(gè)獨(dú)立的存儲(chǔ)單位。分屬不同分區(qū)的數(shù)據(jù)行是嚴(yán)格分開(kāi)存儲(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ū)。下面來(lái)看具體步驟。

step1：創(chuàng)建filegroup

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

雙擊代碼全選

1 2 3 4 5 6 7 8 9 10 11 12

--step1------ --創(chuàng)建數(shù)據(jù)庫(kù) create database TEST USE MASTER GO --40萬(wàn)行分成5個(gè)文件組，PRIMARY加下面四個(gè)文件組， --命名規(guī)則:FG_數(shù)據(jù)庫(kù)名_表名_字段名_流水號(hào) 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怎么使用”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！

當(dāng)前文章：sqlserver中filegroup與partition怎么使用
鏈接地址：http://www.rwnh.cn/article4/jishie.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供商城網(wǎng)站、、移動(dòng)網(wǎng)站建設(shè)、App開(kāi)發(fā)、外貿(mào)網(wǎng)站建設(shè)、ChatGPT

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)