SQL Server表分割槽

張龍豪發表於2014-04-29
SQLServer

什麼是表分割槽

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

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

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

跟著做，分割槽如此簡單

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

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

1.建立檔案組

可以點選資料庫屬性在檔案組裡面新增

T-sql語法：

alter database <資料庫名> add filegroup <檔案組名>
---建立資料庫檔案組
alter database testSplit add filegroup ByIdGroup1
alter database testSplit add filegroup ByIdGroup2
alter database testSplit add filegroup ByIdGroup3
alter database testSplit add filegroup ByIdGroup4
alter database testSplit add filegroup ByIdGroup5
alter database testSplit add filegroup ByIdGroup6
alter database testSplit add filegroup ByIdGroup7
alter database testSplit add filegroup ByIdGroup8
alter database testSplit add filegroup ByIdGroup9
alter 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 ByIdGroup1
alter database testSplit add file 
(name=N'ById2',filename=N'J:\Work\資料庫\data\ById2.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup2
alter database testSplit add file 
(name=N'ById3',filename=N'J:\Work\資料庫\data\ById3.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup3
alter database testSplit add file 
(name=N'ById4',filename=N'J:\Work\資料庫\data\ById4.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup4
alter database testSplit add file 
(name=N'ById5',filename=N'J:\Work\資料庫\data\ById5.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup5
alter database testSplit add file 
(name=N'ById6',filename=N'J:\Work\資料庫\data\ById6.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup6
alter database testSplit add file 
(name=N'ById7',filename=N'J:\Work\資料庫\data\ById7.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup7
alter database testSplit add file 
(name=N'ById8',filename=N'J:\Work\資料庫\data\ById8.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup8
alter database testSplit add file 
(name=N'ById9',filename=N'J:\Work\資料庫\data\ById9.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup9
alter 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]
GO
BEGIN 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 <分割槽函式名>
--刪除分割槽函式 bgPartitionFun
drop 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<分割槽方案名稱>
--刪除分割槽方案 bgPartitionSchema
drop 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 recordCount
from bigorder
group 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 Rows
FROM sys.partitions                  AS p
JOIN sys.indexes                     AS i
      ON i.object_id = p.object_id
      AND i.index_id = p.index_id
JOIN sys.data_spaces                 AS ds
      ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes           AS ps
      ON ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions         AS pf
      ON pf.function_id = ps.function_id
JOIN sys.destination_data_spaces     AS dds2
      ON dds2.partition_scheme_id = ps.data_space_id 
      AND dds2.destination_id = p.partition_number
JOIN sys.filegroups                  AS fg
      ON fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left
      ON ps.function_id = prv_left.function_id
      AND prv_left.boundary_id = p.partition_number - 1
LEFT 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') = 0
UNION ALL
SELECT
      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 Rows
FROM sys.partitions     AS p
JOIN sys.indexes        AS i
      ON i.object_id = p.object_id
      AND i.index_id = p.index_id
JOIN sys.data_spaces    AS ds
      ON ds.data_space_id = i.data_space_id
JOIN sys.filegroups           AS fg
      ON fg.data_space_id = i.data_space_id
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
ORDER BY
      ObjectName,
      IndexID,
      PartitionNumber

 

 

相關文章