sql server 小記——分區表（上） - huangxincheng－IT工程師數位筆記本

文章出處

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

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

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

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

一：分區表

這里我們做個例子，創建一個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