SQL SERVER 性能优化四： 创建分区表

1、整体介绍

 1.1 分区表概念：分区表值得是逻辑上是一个表，物理上被存储到不同的磁盘文件中。

 1.2 优势：提高查询性能；提高稳定性；便于管理；对于大数据量表备份更方便。

 1.3 建立分区表主要包含三个步骤：

     （1）定义分区函数

     （2）定义分区构架 

     （3）定义分区表

2、具体实例

在创建分区表前，数据库中应存在不同的文件组；若没有，则新增。

假设数据库名为DF17DataPro,创建用户表UserID，将前50万数据放在fg1文件组，50~100万放在primary中，100万以上放在fg2文件组

2.1 添加文件组fg1,fg2

ALTER  DATABASE DF17DataPro
ADD FILEGROUP fg1

ALTER  DATABASE DF17DataPro
ADD FILEGROUP fg2

2.2 为文件组添加文件

ALTER TABLE DF17DataPro
ADD FILE (NAME=DF17DataPro1,FILENAME='D:\DF17DataPro.ndf')
TO FILEGROUP fg1
ALTER TABLE DF17DataPro
ADD FILE (NAME=DF17DataPro2,FILENAME='E:\DF17DataPro.ndf')
TO FILEGROUP fg2

2.3  创建分区函数

CREATE PARTITION FUNCTION MyRangeFunction(int)
AS RANGE RIGHT FOR VALUES(500000,1000000)
GO
--分区函数的作用是，设置分区表的边界；根据给定的边界值，将数据内容划分到不同的表中。
--查看分区表是否创建成功

--SELECT * FROM sys.partition_functions

2.4  基于分区函数创建分区架构

CREATE PARTITION SCHEME MyRangeSchema
AS PARTITION  MyRangeFunction
TO(fg1,[primary],fg2)
--定义完分区函数仅仅知道了根据列的值将数据分配到不同的分区。而每个分区的存储方式，则需要分区构架来定义。

--查看已创建的分区构架
--SELECT * FROM sys.partition_schemes

2.5  指定ID列作为分区方案创建用户表

CREATE TABLE USER
(
UserID int not NULL,
UserName varchar(10) NULL
) ON MyRangeSchema(UserID)

 3、补充：

3.1 删除分区语法

drop partition function <分区函数名>

 需要注意的是，只有没有应用到分区方案中的分区函数才能被删除。

   3.2  删除分区方案

--删除分区方案语法
drop partition scheme <分区方案名称>

只有没有分区表或索引使用该分区方案时，才能对其删除。

建议：最好在建表的时候，就判定好分割点，当分区后，若再重新分割已有分区，系统会先将原先分区删掉再将数据复制到新分区，会极大的增大IO开销。

• 日志类表(操作日志、系统日志、结果记录、任务记录等)的分区处理：按时间进行分区
• 交易类表(流水、明细、对账、差异等)的分区处理：按创建时间或更新时间进行分区
• 通知类表的分区处理：按时间进行分区
• 分区字段的选取：一般是以最经常查询的那个字段来进行分区，这样会有助于提高查询速度。不建议使用 id 进行分区，除非业务系统里固定的 id=? 查询特别多，否则不仅是对分区索引的浪费，而且可能会比没有分区还要慢
• 不管是 range 分区，还是 hash 分区，在做分区的时候都要考虑到分区的可扩展性，原则上分区后的 2 年内不应该再考虑重新分区的事情，分区到期后根据业务量增长情况再加 2 年的分区…以此类推
• 各个分区内的数据要较为均匀，不要太多也不要太少，而且根据分区字段可以很快定位到分区范围
• 具体每个分区内数据量多少合适？原则上是把数据的范围缩小到一个即使做全盘扫描也不会慢的时候为最佳，视具体情况为几十万或上百万不等
• 相关业务操作(SQL)尽量在同一个分区内部完成。必须跨分区提取的话建议并行提取以提高速度
• 此段建议引自http://blog.csdn.net/defonds/article/details/52241432