sql server 小記——分區表

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

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

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

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

一：分區表

  這裡我們做個例子，創建一個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”是在哪個分區下。