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”是在哪个分区下。

 

 

SQL Server文件组

在一个最简化的SQL Server数据库中，包含两种操作系统文件：数据文件和日志文件。

数据文件包含数据和一些对象，如表，索引，存储过程，视图等；

日志文件包含恢复数据库事务的信息。

 

数据库文件

SQL Server数据库有三种文件。

Primary文件，每个数据库都有一个主要文件，使用.mdf扩展名。

Secondary文件，次要文件是可选的，用户定义的。次要文件可以分布在不同的磁盘，不同的硬盘，可用用来突破系统对文件大小的限制，也可以用来提升I/O，使用.ndf扩展名。

Transaction Log文件，每个数据库至少有一个日志文件，保存的日志信息用来恢复数据库，使用.ldf扩展名。

举例来说，一个简单的数据库可以创建为包含一个主要文件和一个日志文件，所有的数据和对象都放在主要文件；一个稍微复杂的数据库可以创建一个主要文件，五个次要文件，所有的数据和对象分布在这六个文件之中，并由多个日志文件记录事务信息。

默认情况下，数据和日志是存放在同一硬盘的同一路径，这是用来对应单硬盘系统。然而，在生产环境中，这并不是最优的选择，建议将数据和日志放在不同的硬盘。

 

文件组

每个数据库都含有一个primary文件组，这个主文件组包含主要数据文件和一些没有放入其他文件组的次要文件。

举例来说，有三个数据文件data1.ndf，data2.ndf，data3.ndf，被放置在三个独立的硬盘上，都属于文件组group1，这个文件组里有数据表table1，对table1中数据的查询会在这三个硬盘上执行，这种方式会提升性能。

所有的数据文件需要被存储在下列文件组中：

Primary文件组，这个组里含有primary文件，另外，所有的系统表也都分配在这个文件组。这是默认文件组，对象在创建时，如果没有指定其他文件组，将被分配在这里。

User-defined文件组，用户创建的文件组。

 

使用文件组对SQL Server的优化

1、如果有多个独立硬盘，将文件组的每个文件分开放在每一个硬盘，提高I/O

2、将日志文件放在一个独立的硬盘上，与数据文件分开，日志文件属于写密集型操作，提高I/O

3、频繁访问的表和索引放到一个单独的文件组

4、包含text和image数据列的表放到一个单独的文件组，其中的text列和image列还可以放到独立的硬盘上（表分区）

5、只读表和只写表分别放入一个文件组，提高查询和更新速度

 

SQL Server分区

SQL Server支持表分区和索引分区，分区表和分区索引可以被放置在数据库的多个文件组中。数据是水平划分的，因此每组数据可以被映射到独立的分区。

企业版才支持分区，不同版本的SQL Server对分区的限制不同，具体参照msdn

 

分区的优势

1、快速高效地访问数据子集

2、更高效地对一个或多个分区进行维护操作

当SQL Server对数据进行sort操作的时候，首先是在分区里进行sort。当SQL Server同一时间只对一个硬盘进行访问的话，是有可能会降低性能的。为了提升性能，将数据分区并放在不同的硬盘，这种情况下，虽然SQL Server还是首先在分区里sort，但可以同时对多个硬盘进行操作，提高性能。

 

组件和概念

分区函数

数据库在定义怎样对表和索引分区的时候，是依据某个特定列的值，这个列称为分区列。也就是说，根据分区列，分区函数定义数据有多少个分区，以及这些分区的边界是什么。

 

分区方案

数据库要将一个分区函数产生的分区映射到一系列的文件组，将分区放置在独立文件组的主要原因是可以给每个分区独立的进行备份操作，每个文件组可以进行单独备份。

 

分区列

可以用来做索引列的数据类型就可以用来做分区列，除了timestamp，text，ntext，image，xml，varchar(max)，nvarchar(max)，varbinary(max)。CLR中用户定义的类型和别名数据类型也不可以被用作分区列。

 

对齐索引

对齐索引是索引与它所对应的表使用相同的分区方案。如果表与索引对齐，SQL Server则可以快速高效地切换分区，同时又能维护表及其索引的分区结构。索引要与其基表对齐，并不需要使用相同命名的分区函数。但是，索引和基表的分区函数必须在本质上相同，即

1）分区函数的参数必须具有相同的数据类型

2）分区函数定义了相同的分区数量

3）分区函数定义了相同的边界值

 

非对齐索引

下列情况中，设计非聚集索引是有用的：

1）基表未分区

2）索引键是唯一的，不包含表的分区列

3）需要基表与使用不同联接列的多个表一起参与组合联接

 

应用分区

创建分区函数

1 CREATE PARTITION FUNCTION pfYear (datetime)
2 AS RANGE Left  FOR VALUES ('20130101','20140101','20150101')

 

创建分区方案

1 CREATE PARTITION SCHEME psArea
2 AS PARTITION pfYear
3 TO (
4     Area01,
5     Area02,
6     Area03,
7     Area04)

 

使用分区方案

两种做法都需要重建分区聚集索引，也就是都需要最数据进行迁移，效率上差不多。

1）创建新表，指定分区方案

1 CREATE TABLE Users(
2     [Id] [int] IDENTITY(1,1) NOT NULL,
3     [Name] nvarchar(50),
4     [CreateTime] [datetime]
5 ) ON psArea([CreateTime])

2）对现有表分区

在分区方案上重建聚集索引。（现有表移到另一个filegroup需要删除聚集索引，在新filegroup上重建。）

1 CREATE UNIQUE CLUSTERED INDEX PK_Users ON Users(UserId) ON
2 PartitionSchemeName (UserId)