SQL Server表分區【轉】

標籤：

什麼是表分區

一般情況下，我們建立資料庫表時，表資料都存放在一個檔案裡。

但是如果是分區表的話，表資料就會按照你指定的規則分放到不同的檔案裡，把一個大的資料檔案拆分為多個小檔案，還可以把這些小檔案放在不同的磁碟下由多個cpu進行處理。這樣檔案的大小隨著拆分而減小，還得到硬體系統的加強，自然對我們操作資料是大大有利的。

所以大資料量的資料表，對分區的需要還是必要的，因為它可以提高select效率，還可以對曆史資料經行區分存檔等。但是資料量少的資料就不要湊這個熱鬧啦，因為表分區會對資料庫產生不必要的開銷，除啦效能還會增加實現對象的管理費用和複雜性。

跟著做，分區如此簡單

先跟著做一個分區表（分為11個分區），去除神秘的面紗，然後咱們再逐一擊破各個要點要害。

分區是要把一個表資料拆分為若干子集合，也就是把把一個資料檔案拆分到多個資料檔案中，然而這些檔案的存放可以依託一個檔案組或這多個檔案組，由於多個檔案組可以提高資料庫的訪問並發量，還可以把不同的分區配置到不同的磁碟中提高效率，所以建立時建議分區跟檔案組個數相同。

1.建立檔案組

可以點擊資料庫屬性在檔案組裡面添加

T-sql文法：

alter database <資料庫名> add filegroup <檔案組名>

---建立資料庫檔案組alter database testSplit add filegroup ByIdGroup1alter database testSplit add filegroup ByIdGroup2alter database testSplit add filegroup ByIdGroup3alter database testSplit add filegroup ByIdGroup4alter database testSplit add filegroup ByIdGroup5alter database testSplit add filegroup ByIdGroup6alter database testSplit add filegroup ByIdGroup7alter database testSplit add filegroup ByIdGroup8alter database testSplit add filegroup ByIdGroup9alter database testSplit add filegroup ByIdGroup10

2.建立資料檔案到檔案組裡面

可以點擊資料庫屬性在檔案裡面添加

T-sql文法：

alter database <資料庫名稱> add file <資料標識> to filegroup <檔案組名稱>--<資料標識> （name:檔案名稱，fliename:實體路徑檔案名稱，size:檔案初始大小kb/mb/gb/tb，filegrowth:檔案自動增量kb/mb/gb/tb/%,maxsize:檔案可以增加到的最大大小kb/mb/gb/tb/unlimited）

alter database testSplit add file (name=N‘ById1‘,filename=N‘J:\Work\資料庫\data\ById1.ndf‘,size=5Mb,filegrowth=5mb)to filegroup ByIdGroup1alter database testSplit add file (name=N‘ById2‘,filename=N‘J:\Work\資料庫\data\ById2.ndf‘,size=5Mb,filegrowth=5mb)to filegroup ByIdGroup2alter database testSplit add file (name=N‘ById3‘,filename=N‘J:\Work\資料庫\data\ById3.ndf‘,size=5Mb,filegrowth=5mb)to filegroup ByIdGroup3alter database testSplit add file (name=N‘ById4‘,filename=N‘J:\Work\資料庫\data\ById4.ndf‘,size=5Mb,filegrowth=5mb)to filegroup ByIdGroup4alter database testSplit add file (name=N‘ById5‘,filename=N‘J:\Work\資料庫\data\ById5.ndf‘,size=5Mb,filegrowth=5mb)to filegroup ByIdGroup5alter database testSplit add file (name=N‘ById6‘,filename=N‘J:\Work\資料庫\data\ById6.ndf‘,size=5Mb,filegrowth=5mb)to filegroup ByIdGroup6alter database testSplit add file (name=N‘ById7‘,filename=N‘J:\Work\資料庫\data\ById7.ndf‘,size=5Mb,filegrowth=5mb)to filegroup ByIdGroup7alter database testSplit add file (name=N‘ById8‘,filename=N‘J:\Work\資料庫\data\ById8.ndf‘,size=5Mb,filegrowth=5mb)to filegroup ByIdGroup8alter database testSplit add file (name=N‘ById9‘,filename=N‘J:\Work\資料庫\data\ById9.ndf‘,size=5Mb,filegrowth=5mb)to filegroup ByIdGroup9alter database testSplit add file (name=N‘ById10‘,filename=N‘J:\Work\資料庫\data\ById10.ndf‘,size=5Mb,filegrowth=5mb)to filegroup ByIdGroup10

執行完成後，右鍵資料庫看檔案組跟檔案裡面是不是多出來啦這些檔案組跟檔案。

3.使用嚮導建立分區表

右鍵到要分區的表--- >> 儲存 --- >> 建立分區 --- >>顯示嚮導視圖 --- >> 下一步 --- >> 下一步。。

這裡舉例說下選擇列的意思：

假如你選擇的是int類型的列：那麼你的分區可以指定為1--100W是一個分區，100W--200W是一個分區....

假如你選擇的是datatime類型：那麼你的分區可以指定為：2014-01-01--2014-01-31一個分區，2014-02-01--2014-02-28一個分區...

根據這樣的列資料規則劃分，那麼在那個區間的資料，在插入資料庫時就被指向那個分區儲存下來。

 

我這裡選用orderid int類型 --- >> 下一步 --- >>

左邊界右邊界：就是把臨界值劃分給上一個分區還是下一個分區。一個小於符號，一個小於等號。

然後下一步下一步最後你會得到分區函數和資料分割配置。

USE [testSplit]GOBEGIN TRANSACTION--建立分區函數CREATE PARTITION FUNCTION [bgPartitionFun](int) AS RANGE LEFT FOR VALUES (N‘1000000‘, N‘2000000‘, N‘3000000‘, N‘4000000‘, N‘5000000‘, N‘6000000‘, N‘7000000‘, N‘8000000‘, N‘9000000‘, N‘10000000‘)--建立資料分割配置CREATE PARTITION SCHEME [bgPartitionSchema] AS PARTITION [bgPartitionFun] TO ([PRIMARY], [ByIdGroup1], [ByIdGroup2], [ByIdGroup3], [ByIdGroup4], [ByIdGroup5], [ByIdGroup6], [ByIdGroup7], [ByIdGroup8], [ByIdGroup9], [ByIdGroup10])--建立分區索引CREATE CLUSTERED INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165] ON [dbo].[BigOrder] (    [OrderId])WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [bgPartitionSchema]([OrderId])--刪除分區索引DROP INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165] ON [dbo].[BigOrder] WITH ( ONLINE = OFF )COMMIT TRANSACTION

執行上面嚮導產生的語句。分區完成。。

4.秀一下速度。

首先我在表中插入啦1千萬行資料。給表分啦11個分區。前十個分區裡面一個是100W條資料。。

說兩句：

可見反常現象，掃描次數跟邏輯讀取次數都是無分區表的2倍之多，但查詢速度卻是快啦不少啊。這就是分區的神奇之處啊，所以要相信這世界一切皆有可能。

分區函數，資料分割配置，分區表，分區索引

1.分區函數

指定分依據區列（依據列唯一），分區資料範圍規則，分區數量，然後將資料對應到一組分區上。

建立文法： 

create partition function 分區函數名(<分區列類型>) as range [left/right] for values (每個分區的邊界值,....) 

--建立分區函數CREATE PARTITION FUNCTION [bgPartitionFun](int) AS RANGE LEFT FOR VALUES (N‘1000000‘, N‘2000000‘, N‘3000000‘, N‘4000000‘, N‘5000000‘, N‘6000000‘, N‘7000000‘, N‘8000000‘, N‘9000000‘, N‘10000000‘)

然而，分區函數只定義了分區的方法，此方法具體用在哪個表的那一列上，則需要在建立表或索引是指定。 

刪除文法：

--刪除分區文法drop partition function <分區函數名>

--刪除分區函數 bgPartitionFundrop partition function bgPartitionFun

需要注意的是，只有沒有應用到資料分割配置中的分區函數才能被刪除。

2.資料分割配置

指定分區對應的檔案組。

建立文法： 

--建立資料分割配置文法create partition scheme <資料分割配置名稱> as partition <分區函數名稱> [all]to (檔案組名稱,....) 

--建立資料分割配置,所有分區在一個組裡面CREATE PARTITION SCHEME [bgPartitionSchema] AS PARTITION [bgPartitionFun] TO ([ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1])

分區函數必須關聯資料分割配置才能有效，然而資料分割配置指定的檔案組數量必須與分區數量一致，哪怕多個分區存放在一個檔案組中。

刪除文法：

--刪除資料分割配置文法drop partition scheme<資料分割配置名稱>

--刪除資料分割配置 bgPartitionSchemadrop partition scheme bgPartitionSchema1

只有沒有分區表，或索引使用該資料分割配置是，才能對其刪除。

3.分區表

建立文法：

--建立分區表文法create table <表名> (  <列定義>)on<資料分割配置名>(分區列名)

--建立分區表create table BigOrder (   OrderId              int                  identity,   orderNum             varchar(30)          not null,   OrderStatus          int                  not null default 0,   OrderPayStatus       int                  not null default 0,   UserId               varchar(40)          not null,   CreateDate           datetime             null default getdate(),   Mark                 nvarchar(300)        null)on bgPartitionSchema(OrderId)

如果在表中建立主鍵或唯一索引，則分割資料行必須為該列。

4.分區索引

建立文法： 

--建立分區索引文法create <索引分類> index <索引名稱> on <表名>(列名)on <資料分割配置名>(分割資料行名)

--建立分區索引CREATE CLUSTERED INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165] ON [dbo].[BigOrder] (    [OrderId])WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [bgPartitionSchema]([OrderId])

使用分區索引查詢，可以避免多個cpu操作多個磁碟時產生的衝突。

分區表明細資訊

這裡的文法，我就不寫啦，自己看語句分析吧。簡單的很。。

1.查看分割資料行的指定值所在的分區 

--查詢分割資料行為10000014的資料在哪個分區上select $partition.bgPartitionFun(2000000)  --傳回值是2，表示此值存在第2個分區 

2.查看分區表中，每個非空分區存在的行數

--查看分區表中，每個非空分區存在的行數select $partition.bgPartitionFun(orderid) as partitionNum,count(*) as recordCountfrom bigordergroup by  $partition.bgPartitionFun(orderid)

3.查看指定分區中的資料記錄 

---查看指定分區中的資料記錄select * from bigorder where $partition.bgPartitionFun(orderid)=2

結果：資料從1000001開始到200W結束

分區的拆分與合并以及資料移動

 1.拆分分區

在分區函數中新增一個邊界值，即可將一個分區變為2個。

--分區拆分alter partition function bgPartitionFun()split range(N‘1500000‘)  --將第二個分區拆為2個分區

注意：如果分區函數已經指定了資料分割配置，則分區數需要和資料分割配置中指定的檔案組個數保持對應一致。

 2.合并分區

 與拆分分區相反，去除一個邊界值即可。

--合并分區alter partition function bgPartitionFun()merge range(N‘1500000‘)  --將第二第三分區合并

3.分區中的資料移動

 你或許會遇到這樣的需求，將普通表資料複製到分區表中，或者將分區表中的資料複製到普通表中。

 那麼移動資料這兩個表，則必須滿足下面的要求。

• 欄位數量相同，對應位置的欄位相同
• 相同位置的欄位要有相同的屬性，相同的類型。
• 兩個表在一個檔案組中

1.建立表時指定檔案組

--建立表create table <表名> (  <列定義>)on <檔案組名>

2.從分區表中複製資料到普通表

--將bigorder分區表中的第一分區資料複製到普通表中alter table bigorder switch partition 1 to <普通表名>

3.從普通標中複製資料到分區表中

這裡要注意的是要先將分區表中的索引刪除，即便普通表中存在跟分區表中相同的索引。

--將普通表中的資料複製到bigorder分區表中的第一分區alter table <普通表名> switch to bigorder partition 1 

分區視圖

分區視圖是先建立帶有欄位約束的相同表,而約束不同,例如,第一個表的id約束為0--100W,第二表為101萬到200萬.....依次類推。

建立完一系列的表之後，用union all 串連起來建立一個視圖，這個視圖就形成啦分區視同。

很簡單的，這裡我主要是說分區表，就不說分區視圖啦。。

 查看資料庫分區資訊

SELECT OBJECT_NAME(p.object_id) AS ObjectName,      i.name                   AS IndexName,      p.index_id               AS IndexID,      ds.name                  AS PartitionScheme,         p.partition_number       AS PartitionNumber,      fg.name                  AS FileGroupName,      prv_left.value           AS LowerBoundaryValue,      prv_right.value          AS UpperBoundaryValue,      CASE pf.boundary_value_on_right            WHEN 1 THEN ‘RIGHT‘            ELSE ‘LEFT‘ END    AS Range,      p.rows AS RowsFROM sys.partitions                  AS pJOIN sys.indexes                     AS i      ON i.object_id = p.object_id      AND i.index_id = p.index_idJOIN sys.data_spaces                 AS ds      ON ds.data_space_id = i.data_space_idJOIN sys.partition_schemes           AS ps      ON ps.data_space_id = ds.data_space_idJOIN sys.partition_functions         AS pf      ON pf.function_id = ps.function_idJOIN sys.destination_data_spaces     AS dds2      ON dds2.partition_scheme_id = ps.data_space_id       AND dds2.destination_id = p.partition_numberJOIN sys.filegroups                  AS fg      ON fg.data_space_id = dds2.data_space_idLEFT JOIN sys.partition_range_values AS prv_left      ON ps.function_id = prv_left.function_id      AND prv_left.boundary_id = p.partition_number - 1LEFT JOIN sys.partition_range_values AS prv_right      ON ps.function_id = prv_right.function_id      AND prv_right.boundary_id = p.partition_number WHERE      OBJECTPROPERTY(p.object_id, ‘ISMSShipped‘) = 0UNION ALLSELECT      OBJECT_NAME(p.object_id)    AS ObjectName,      i.name                      AS IndexName,      p.index_id                  AS IndexID,      NULL                        AS PartitionScheme,      p.partition_number          AS PartitionNumber,      fg.name                     AS FileGroupName,        NULL                        AS LowerBoundaryValue,      NULL                        AS UpperBoundaryValue,      NULL                        AS Boundary,       p.rows                      AS RowsFROM sys.partitions     AS pJOIN sys.indexes        AS i      ON i.object_id = p.object_id      AND i.index_id = p.index_idJOIN sys.data_spaces    AS ds      ON ds.data_space_id = i.data_space_idJOIN sys.filegroups           AS fg      ON fg.data_space_id = i.data_space_idWHERE      OBJECTPROPERTY(p.object_id, ‘ISMSShipped‘) = 0ORDER BY      ObjectName,      IndexID,      PartitionNumber

SQL Server表分區【轉】