浅谈SQL Server 2005 分区表(一)

分区可以使大型表和索引更易于管理并且更灵活。

主要体现在，快速高效地管理和访问数据子集，同时维护数据集合的完整性，由于对数据子集执行的维护操作只是针对所需数据而不是整个表，因此效率也提高了。

 

已分区表和已分区索引的数据划分为分布于一个数据库中多个文件组的单元。数据是按水平方式分区的，因此多组行映射到单个的分区。对数据进行查询或更新时，表或索引将被视为单个逻辑实体。单个索引或表的所有分区都必须位于同一个数据库中。

 

决定是否实现分区主要取决于表当前的大小或将来的大小、如何使用表以及对表执行用户查询和维护操作的完善程度。如果符合下面2点，则可以对该表进行分区

1.    该表包含(或将包含)以多种不同方式使用的大量数据。

2.    不能按预期对表执行查询或更新，或维护开销超过了预定义的维护期。

 

对表或索引分区前，必须先创建下列数据库对象。

1，分区函数 分区函数定义如何根据[分区依据列]的值将表或索引的行映射到一组分区。

 

2，分区方案 分区方案将分区函数指定的每个分区映射到文件组。

 

计划分区函数需要考虑2个因素：确定分区依据列(仅能制定一列，该列类型除timestamp，ntext、text、image、xml、varchar(max)、nvarchar(max) , varbinary(max)CLR用户定义数据类型，别名数据类型外) 和 分区依据列值的范围(此值的范围确定分区的数量)。一张表的分区最多有1000个，所以该用的时候就用，别节约。

 

计划分区方案时，必须决定分区放在哪个文件组中。主要原因是为了确保可以在分区上独立执行备份操作（文件备份）。

 

创建已分区表

主要步骤分3部。

CREATE PARTITION FUNCTION 创建分区函数

CREATE PARTITION SCHEME 创建分区方案

创建使用分区方案的表或索引

一个分区方案仅可以使用一个分区函数。但是，一个分区函数可以参与多个分区方案。

 

下面附上一个简单的demo.因为分区需要多个文件组，下面的create database我随便建了7个要文件组（包含primary文件组）,主文件组包含主数据文件，其他6个文件组各自包含一个次要数据文件，因为稍后会往这些文件写入数据。

USE [master]

GO

/****** 对象: Database [mydb]    脚本日期: 07/06/2011 23:31:18 ******/

CREATE DATABASE [mydb]

ON PRIMARY(

NAME = N'mydb_data',

FILENAME = N'F:\Example Databse\mydb_data.mdf',

SIZE = 10240KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 10%

),

 FILEGROUP [fg1]

(

    NAME = N'mydb1',

    FILENAME = N'F:\Example Databse\mydb1.ndf',

    SIZE = 3072KB,

    MAXSIZE = UNLIMITED,

    FILEGROWTH = 1024KB

),

 FILEGROUP [fg2]

(

    NAME = N'mydb2',

    FILENAME = N'F:\Example Databse\mydb2.ndf',

    SIZE = 3072KB,

    MAXSIZE = UNLIMITED,

    FILEGROWTH = 1024KB

),

 FILEGROUP [fg3]

(

    NAME = N'mydb3',

    FILENAME = N'F:\Example Databse\mydb3.ndf',

    SIZE = 3072KB,

    MAXSIZE = UNLIMITED,

    FILEGROWTH = 1024KB

),

 FILEGROUP [fg4]

(

    NAME = N'mydb4',

    FILENAME = N'F:\Example Databse\mydb4.ndf',

    SIZE = 3072KB,

    MAXSIZE = UNLIMITED,

    FILEGROWTH = 1024KB

),

 FILEGROUP [fg5]

(

    NAME = N'mydb5',

    FILENAME = N'F:\Example Databse\mydb5.ndf',

    SIZE = 3072KB,

    MAXSIZE = UNLIMITED,

    FILEGROWTH = 1024KB

),

 FILEGROUP [fg6]

(

    NAME = N'mydb6',

    FILENAME = N'F:\Example Databse\mydb6.ndf',

    SIZE = 3072KB,

    MAXSIZE = UNLIMITED,

    FILEGROWTH = 1024KB

)

 LOG ON

(

    NAME = N'mydb_log',

    FILENAME = N'F:\Example Databse\mydb_log.ldf',

    SIZE = 2304KB,

    MAXSIZE = 2048GB,

    FILEGROWTH = 10%

)

 COLLATE Chinese_PRC_CI_AS

GO

 

执行完t-sql，已经建立完mydb .

 

1.建立分区函数。通常的分区依据列为数字(int,decimal),字符串(char,nvarchar,nchar,nvarchar),日期 datetime。下面就已最简单的int类型来创建分区函数（此示例为 LEFT 分区）。注意：分区函数作用域限制为创建该分区函数的DB，并且放置在与一般的函数(标量函数， 表值函数)不同的命名空间内。

CREATE PARTITION FUNCTION myPF1 (INT)

AS RANGE LEFT

FOR VALUES (1,500,2000,10000);

GO

语法很简单，msdn下就明白了。

上列分区函数将表分为了5个分区，以为分区参数为INT类型，for values 内指定的值正是对INT类型值的划分。

分区1. 分区列值 <= 1

分区2. 分区列值 > 1 AND分区列值 <= 500

分区3. 分区列值 > 500 AND分区列值 <= 2000

分区4. 分区列值 > 2000 AND分区列值 <= 10000

分区5. 分区列值 >  10000

看了上面5个区， OK，分区函数就到此结束，可以看到建立分区函数非常简单。

 

 

2.建立分区方案。因为区分方案的目的是将分区函数创建的分区映射到文件组。创建数据库时除primary外还有6个文件组，并且创建的myPF1分区函数已经确定了分区数量，我就随便把分区映射到某某文件组，希望大家在生产环境中根据实际情况创建分区方案，在这只是方便演示。同样，分区方案的demo也很简洁。

CREATE PARTITION SCHEME myPS1

AS PARTITION myPF1

    TO (fg1,fg2,fg3,fg4,fg5);

GO

根据分区依据列使用分区函数myPF1的表分区会按下列分配：

 

文件组	Fg1	Fg2	Fg3	Fg4	Fg5
分区	1	2	3	4	5
分区依据列值范围	V <= 1	V > 1 AND V <= 500	V > 500 AND V <= 2000	V > 2000 AND V<= 1000	V > 10000

 

 

至此分区函数，分区方案创建完毕。执行

USE mydb;

GO

SELECT '分区函数',* FROM sys.partition_functions;

GO

SELECT '分区方案',* FROM sys.partition_schemes;

GO

3．创建已分区表，说白了就是将表建立在已存在的分区方案上。

CREATE TABLE mydb.dbo.t1

(

    [id] INT NOT NULL,

    [name] NVARCHAR(10) NULL

) ON myPS1 ([id]);

GO

执行完毕， dbo.t1 也就已经进行了分区。是不是比想象中简单的很多呢。

 

 

测试数据

DECLARE @i AS INT;

SET @i = 1;

SET NOCOUNT ON;

WHILE @i < 10001

BEGIN

    INSERT INTO dbo.t1 ([id],[name]) VALUES (@i,N'sql server');

    SET @i = @i + 1;

END;

GO

对表进行了填充了10000条数据，大家记住虽然对table进行了分区，也就是说某范围到某范围的数据分别存在于不同的文件组中，（如果文件组分别在不同的磁盘，那么数据也就是水平分散到不同的磁盘），但是操作已分区表跟普通表并没有两样。

 

若要着重查询单个分区， 使用$PARTITION 。下面介绍3种$PARTITION的用法。

 

1，确定即将插入的行将会存储在某个特定的分区，如：

INSERT INTO dbo.t1 ([id],[name]) VALUES (20000,N'sql server');

[id] 为 分区依据列，根据范围，我们知道该行，会存放在分区5，文件组fg5之中。

2，统计所有分区内的记录数量，如:

SELECT $PARTITION.myPF1([id]) AS [Partition],

COUNT(*) AS [COUNT] FROM dbo.t1

GROUP BY $PARTITION.myPF1([id])

ORDER BY [Partition];

GO

返回结果

3.返回分区表或索引的一个分区所有行:如：

SELECT * FROM dbo.t1 WHERE $PARTITION.myPF1 ([id]) = 5 ;

返回结果: id 20000 name sql server

就条数据就是我们刚才运行的INSERT INTO 插入的行。

 

分区表建立起来并不难，难点是理解它的原理并使用它使数据库性能最大化，如果有什么理解不对，希望大家多多纠正，补充。