表分区总结

相关资料推荐：http://hi.baidu.com/wcz2008nihao/blog/item/9bd958ec8cd8e530269791e3.html
               Slide Window：http://blogold.chinaunix.net/u2/73798/showart_2524989.html

此文偏重实践。首先，此文要求对表分区相关基本概念、数据库文件、数据库索引等有所了解。

本人使用数据库：MS Sql Server 2008

 

以下内容，仅代表个人理解。

定义：分区功能能够将表、索引细分为段，这些数据库对象的段叫做分区
1）     表分区于性能的好处：

     改善查询性能：对分区对象（索引和表）的查询可以仅搜索自己关心的分区，提高检索速度
     均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善系统性能

 注：有人通过表分区来解决数据文件存储空间相关问题（如：磁盘空间不够、数据库文件超过文件系统类型支持的最大大小），这是没有必要的，通过往文件组中添加文件即可实现

 

2）     适合使用表分区的条件：

     表数据量大，至少百万以上
     表中存在分区字段，如时间

 

3）     表分区的实现——

     新建分区表 & 将已有表修改为分区表

• 分区函数

• 分区方案

• 文件组

     拆分分区
     合并分区，可将分区表转换为普通表
     移动分区

4）     表分区与索引：

     分区列必须包含在聚集索引中
     分区表的索引可以设置为分区索引，使得索引与基表对齐（同一个分区的索引文件和基表数据存储在同一个文件组中）。聚集索引默认为分区索引，非聚集索引需要设定分区方案才能成为分区索引。分区索引使得索引文件更小，更利于快速检索

5）     表分区之于备份：

分区时，可将各个段分布到不同的文件夹下，在还原数据库时，如果分区指向的文件夹不存在，SQL Server会将数据文件存放到系统默认文件夹下

DEMO
1）     建立文件组和文件

     SQL：

--建立文件组

ALTER DATABASE [MusicBI10_DW] ADD FILEGROUP [MusicBIDW_201101]

--往文件组中添加文件

ALTER DATABASE [MusicBI10_DW] ADD FILE

(

     NAME = [File_MusicBIDW_201101],

     FILENAME = 'D:/DBFile/MusicBIDW_201101.ndf',

     SIZE = 10MB,

     FILEGROWTH = 1MB

) TO FILEGROUP [MusicBIDW_201101]

     GUI：

图：文件组管理

图：文件管理

2）     建立分区函数和分区方案

     SQL：

--创建分区函数

CREATE PARTITION FUNCTION PF_MusicBIDW_CharSix (CHAR(6))  

     AS RANGE RIGHT FOR VALUES ('201101','201102')

--创建分区方案

CREATE PARTITION SCHEME PFScheme_MusicBIDW_CharSix

     AS PARTITION PF_MusicBIDW_CharSix

     TO (MusicBIDW_201101,  

          MusicBIDW_201102,  

          MusicBIDW_201103)

     GUI：

图：分区函数和分区方案

3）     建立分区表

     SQL：

•新建表——

     --指定表依分区列存储到分区方案上

     CREATE TABLE [Fact_Income]

     (

          DateTimeKey char(10),

          ***

     ) ON PFScheme_MusicBIDW_CharTen([DateTimeKey])

     --通常应为：ON [PRIMARY]

•修改已有表为分区表——

      --在分区方案上以分区列建立聚集索引，如已有聚集索引/主键需要先将其删除               

     CREATE CLUSTERED INDEX CI_Fact ON Fact_Income(DateTimeKey)

                ON PFScheme_MusicBIDW_CharTen(DateTimeKey)

     GUI：

                    图：已分区表的存储属性

4）     拆分分区

     SQL：

--1、添加文件组

ALTER DATABASE [MusicBI10_DW] ADD FILEGROUP [MusicBIDW_201104]

--2、为文件组添加文件

ALTER DATABASE [MusicBI10_DW] ADD FILE

(

     NAME = File_MusicBIDW_201104,

     FILENAME = 'D:/DBFile/MusicBIDW_201104.ndf',

     SIZE = 10MB,

     FILEGROWTH = 1MB

) TO FILEGROUP [MusicBIDW_201104]

--3、为分区方案指定一个可以使用的文件组

ALTER PARTITION SCHEME PFScheme_MusicBIDW_CharSix

     NEXT USED [MusicBIDW_201104]

--4、修改分区函数，添加分解值

ALTER PARTITION FUNCTION PF_MusicBIDW_CharSix()

     SPLIT RANGE ('201103')

5）     合并分区

     SQL

--删除分区函数中的分界值，SQL Server会自动重新分配数据

ALTER PARTITION FUNCTION PF_MusicBIDW_CharSix()

     MERGE RANGE ('201101')

6）     创建分区索引

     SQL：
     --已分区的表索引默认会建立在分区上
     CREATE NONCLUSTERED INDEX [NCI_Product] ON [dbo].[Fact_Income]
     (
          [ID] ASC
     ) --省略了ON PFScheme_MusicBIDW_CharTen(DateTimeKey)    
GUI：

图：分区索引的存储属性

7）     测试

     SQL：

--查看各个分区中的记录数，分区列值为null时，记录会被存入最"小"的分区中

SELECT $PARTITION.PF_MusicBIDW_CharTen(DateTimeKey)

          AS 分区编号, COUNT(1) AS 记录数

FROM Fact_Income

GROUP BY $PARTITION.PF_MusicBIDW_CharTen(DateTimeKey)