相关资料推荐: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_CharSixAS PARTITION PF_MusicBIDW_CharSixTO (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_CharSixNEXT 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_IncomeGROUP BY $PARTITION.PF_MusicBIDW_CharTen(DateTimeKey)