sql server 小記——分割槽表（上）

一線碼農發表於2014-02-25
SQLServer

　我們知道很多事情都存在一個分治的思想，同樣的道理我們也可以用到資料表上，當一個表很大很大的時候，我們就會想到將表拆

分成很多小表，查詢的時候就到各個小表去查，最後進行彙總返回給呼叫方來加速我們的查詢速度，當然切分可以使用橫向切分，縱向

切分，比如我們最熟悉的訂單表，通常會將三個月以外的訂單放到歷史訂單表中，這裡的三個月就是將訂單表進行切分的依據。

　

  好了，分割槽表的好處我想大家都很清楚了，下面我們看看如何實現。

一：分割槽表

  這裡我們做個例子，建立一個test資料庫，表名為shop，以createtime作為分割槽依據。

1：確定分割槽依據

     怎麼分割槽的話，這個要看具體業務邏輯了，你可以按照時間，地區，求模等等都可以。

 

2：建立檔案組

　  既然是檔案組，肯定是對檔案進行分類管理的，預設情況下就一個mdf和ldf檔案，當所有的資料都擠壓在mdf上，確實不是一個

很好的事情，降低我們的查詢速度，當用到檔案組的時候就可以建立多個ndf來分攤mdf中的資料，甚至還可以將ndf分攤到幾個磁碟

上，充分利用伺服器多核處理能力，說了這麼多，我們看看sql語句咋搞，這裡我建立四個檔案組，分別存放2013之前，2013，2014

和2014年之後的資料。

1 alter database Test add filegroup Before2013
2 alter database Test add filegroup T2013
3 alter database Test add filegroup T2014
4 alter database Test add filegroup After2014


3：建立檔案

　　

　　根據上面在檔案組上的概述，檔案的作用大家都知道了，這裡我們要做的是，將次檔案.ndf附加到檔案組上，因為我建立了4個檔案組，

所以我也建立4個檔案分別存放在這4個檔案組中。

 

 1 alter database Test add file
 2 (Name=N'Before2013',filename='D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Before20131.ndf',size=5mb,maxsize=100Mb,filegrowth=5mb) 
 3 to filegroup Before2013
 4 alter database Test add file
 5 (Name=N'T2013',filename='D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\T20131.ndf',size=5mb,maxsize=100Mb,filegrowth=5mb) 
 6 to filegroup T2013
 7 alter database Test add file
 8 (Name=N'T2014',filename='D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\T20141.ndf',size=5mb,maxsize=100Mb,filegrowth=5mb) 
 9 to filegroup T2014
10 alter database Test add file
11 (Name=N'After2014',filename='D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\After20141.ndf',size=5mb,maxsize=100Mb,filegrowth=5mb) 
12 to filegroup After2014


4：編寫分割槽函式

   剛才也說了，我們是按照時間進行切分的，將資料表資料分成：

① 2013年之前

② 2013-2014

③ 2014-2015

④ 2015之後

既然都知道依據了，我們分割槽函式也方便寫了。

1 create partition function RangeTime (datetime)
2 as range left for values ('2012-12-31','2013-12-31','2014-12-31')

從上面的sql，我們可以看到三個點將時間軸分成了4段
第一：rangeTime 為分組函式名。

第二：left 其實就是當時間點在邊界時到底屬於左側還是右側，因為這裡是left，所以屬於左側，如果是right關鍵詞，那就屬於右側了。

 

5：編寫分割槽方案

    分割槽方案也就是將分割槽函式與檔案組進行一個關聯，剛才也說了，3個時間點將一個時間軸分成了4部分，剛好對應了4個檔案組。

那麼具體的sql寫法如下：

1 create partition scheme RangeSchema_CreateTime
2 as partition RangeTime
3 to (before2013,T2013,T2014,after2014)

6：建立分割槽表
　　

    跟普通表建立有點不一樣，分割槽表的建立還需要指定這個分割槽需要使用哪個分割槽方案下的分割槽欄位，那麼這裡就是RangeSchema_CreateTime

中的CreateTime欄位。

1 create table Shop
2 (
3   ID  varchar(50),
4   ShopName varchar(50),
5   CreateTime datetime 
6 ) on RangeSchema_CreateTime(CreateTime)

這裡要注意，如果在建立表的時候指定了ID為主鍵的話，這個時候需要指定ID為分割槽欄位，否則會報錯的。

這時候可以在不要主鍵的情況下先建立表，然後再指定ID為主鍵。

 

7：插入測試資料並統計

　　

    這裡我先插入10w條資料，然後來看看資料在各個分割槽的情況。‘

<1>插入資料

 

<2> 統計每個分割槽的資料量

      這裡主要有一個查詢分割槽的關鍵字“$partition”，非常的有用。

好了，到這個我們通過sql語句來實現分割槽表就已經完成了。

 

二：使用管理介面建立分割槽表

1：首先我們建立test1資料庫和shop表

 

2：建立檔案組和檔案

 

 3:建立分割槽

    ①：右鍵Shop表，彈出選單中選擇 “儲存” => "建立分割槽"

 

  ②：建立“分割槽函式”名 和 “分割槽方案”名。

 

③：建立分割槽對映，也就是將”分割槽函式“和“檔案組”進行關聯。

 

④：  最後我們可以看一下介面給我生成的分割槽函式以及分割槽方案，蠻有意思的。

 1 USE [Test1]
 2 GO
 3 BEGIN TRANSACTION
 4 CREATE PARTITION FUNCTION [MyRangeCreatTime](datetime) AS RANGE LEFT FOR VALUES (N'2012-12-31T00:00:00', N'2013-12-31T00:00:00', N'2014-12-31T00:00:00')
 5 
 6 
 7 CREATE PARTITION SCHEME [MySchemeCreateTime] AS PARTITION [MyRangeCreatTime] TO ([Before2013], [T2013], [T2014], [After2014])
 8 
 9 
10 ALTER TABLE [dbo].[Shop] DROP CONSTRAINT [PK__Shop__3214EC277F60ED59]
11 
12 
13 ALTER TABLE [dbo].[Shop] ADD PRIMARY KEY NONCLUSTERED 
14 (
15     [ID] ASC
16 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
17 
18 
19 CREATE CLUSTERED INDEX [ClusteredIndex_on_MySchemeCreateTime_635288828144372217] ON [dbo].[Shop] 
20 (
21     [CreateTime]
22 )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [MySchemeCreateTime]([CreateTime])
23 
24 
25 DROP INDEX [ClusteredIndex_on_MySchemeCreateTime_635288828144372217] ON [dbo].[Shop] WITH ( ONLINE = OFF )
26 
27 
28 
29 
30 COMMIT TRANSACTION


從圖中可以看到生成好的分割槽函式名”[MyRangeCreatTime]“ 和分割槽架構名“[MySchemeCreateTime]”，最後我們執行下該sql就ok了。

 

⑤ 插入測試資料並進行簡單的測試

    這裡測試下“2013-1-1”是在哪個分割槽下。

 

　　

 

相關文章