SQL SERVER性能优化--分区表

本文演示了 SQL Server 2008 分区表实例;

1. 创建测试数据库 ；

2.   创建分区函数;

3.   创建分区架构;

4.  创建分区表;

5.  创建分区索引 ;

6.  分区切换 ;

7.  查询哪些表使用了分区表；

-- 作成者   leno

-- 日期: 2009-06-06 23:50:01.700

-- 创建测试数据库

USE master
GO 
IF OBJECT_ID(N'PartitionDataDB') IS NOT NULL
DROP DATABASE PartitionDataDB
GO

CREATE DATABASE PartitionDataDB
ON PRIMARY
(
NAME = N'File_A_H',
FILENAME ='D:/Microsoft/Microsoft SQL Server 2008/MSSQL10.MSSQLSERVER/MSSQL/DATA/PartitionDataDB_AH.mdf'
),
FILEGROUP FileGroup_I_N
(
NAME = N'File_I_N',
FILENAME ='D:/Microsoft/Microsoft SQL Server 2008/MSSQL10.MSSQLSERVER/MSSQL/DATA/PartitionDataDB_IN.mdf'
),
FILEGROUP FileGroup_M_Z
(
NAME = N'File_M_Z',
FILENAME ='D:/Microsoft/Microsoft SQL Server 2008/MSSQL10.MSSQLSERVER/MSSQL/DATA/PartitionDataDB_MZ.mdf'
)
GO

-- 创建分区函数

USE PartitionDataBase;
GO
CREATE PARTITION FUNCTION StaffNameRangePFN(varchar(100))
AS
RANGE LEFT FOR VALUES ('H','M')
GO

-- 创建分区架构

CREATE PARTITION SCHEME StaffNamePScheme
AS
PARTITION StaffNameRangePFN
TO ([PRIMARY], FileGroup_I_N, FileGorup_M_Z)
GO

-- 创建分区表

CREATE TABLE [dbo].[Staff] 
(
[StaffName] [varchar](100) NOT NULL
)
ON StaffNamePScheme ([StaffName])
GO

-- 插入测试数据1

INSERT INTO [dbo].[Staff]
SELECT FirstName FROM AdventureWorks.Person.Contact

-- 查看结果

SELECT
    $partition.StaffNameRangePFN(StaffName) AS [Partition Number],
    MIN(StaffName) AS [Min StaffName],
    MAX(StaffName) AS [Max StaffName],
    COUNT(StaffName) AS [Rows In Partition]
FROM dbo.staff AS o
GROUP BY $partition.StaffNameRangePFN(StaffName)
ORDER BY [Partition Number]

/*

1 Abigail Guy 364
2 Hannah Lynn 241
3 Maggie Yvette 395

*/

-- 插入测试数据2

INSERT INTO [dbo].[Staff]
SELECT AddressLine1 FROM AdventureWorks.Person.Address

-- 查看结果

SELECT
    $partition.StaffNameRangePFN(StaffName) AS [Partition Number],
    MIN(StaffName) AS [Min StaffName],
    MAX(StaffName) AS [Max StaffName],
    COUNT(StaffName) AS [Rows In Partition]
FROM dbo.staff AS o
GROUP BY $partition.StaffNameRangePFN(StaffName)
ORDER BY [Partition Number]

/*
1 Abigail               Guy                     1364
2 Hannah                Lynn                    241
3 Maggie                Yvette                  395

*/

-- 从结果看出,已经重新分区过了

-- 重新改变布局

Use master
GO
ALTER DATABASE PartitionDataDB ADD FILEGROUP FileGroup_0_9
GO
ALTER DATABASE PartitionDataDB
ADD FILE
(
NAME = N'File_0_9',FileName = 'D:/Microsoft/Microsoft SQL Server 2008/MSSQL10.MSSQLSERVER/MSSQL/DATA/PartitionDataBase_09.mdf'
)
TO FILEGROUP FileGroup_0_9
GO
Use PartitionDataBase
GO
ALTER PARTITION SCHEME StaffNamePScheme
NEXT USED FileGroup_0_9;
GO
ALTER PARTITION FUNCTION StaffNameRangePFN()
SPLIT RANGE ('A');
GO

-- 查看结果

SELECT
    $partition.StaffNameRangePFN(StaffName) AS [Partition Number],
    MIN(StaffName) AS [Min StaffName],
    MAX(StaffName) AS [Max StaffName],
    COUNT(StaffName) AS [Rows In Partition]
FROM dbo.staff AS o
GROUP BY $partition.StaffNameRangePFN(StaffName)
ORDER BY [Partition Number]

/*

1 10 Green Milton Drive 999 Rocky Hague Freeway 1000
2 Abigail               Guy                     364
3 Hannah                Lynn                    241
4 Maggie                Yvette                  395

*/

-- 创建聚集分区索引

CREATE CLUSTERED index IXC_Staff_StaffName on  dbo.Staff ( StaffName )
go

- - 分区切换

6.1切换分区表的一个分区到普通数据表中： Partition to Table

首先建立普通数据表 dbo.StaffName_Num ，该用来存放表StaffName 数字STAFF的数据

-- 创建表

if OBJECT_ID('dbo.StaffName_Num') is not null
drop table dbo.StaffName_Num
go
create table dbo.StaffName_Num
(
   StaffName  varchar ( 100 )   not null
)
on [FileGroup_0_9]
go

--注意这里建表不能为空或prmary,因为我们建的分区表不在primary文件组

--如果这样会出现

--消息 4939，级别 16，状态 1，第 1 行
ALTER TABLE SWITCH 语句失败。索引'PartitionDataDB.dbo.StaffName_Num.IXC_StaffName_Num_StaffName_Num' 在文件组'PRIMARY' 中，而 索引'PartitionDataDB.dbo.Staff.IXC_Staff_StaffName' 的分区 1 在文件组'FileGroup_0_9' 中。

create clustered index IXC_StaffName_Num_StaffName_Num on dbo.StaffName_Num ( StaffName )
go

--这里的索引也要建立一样的,

--不然也会出现这样的提示

--消息 4913，级别 16，状态 1，第 1 行
ALTER TABLE SWITCH 语句失败。表 'PartitionDataDB.dbo.Staff' 具有聚集索引 'IXC_Staff_StaffName'，而表 'PartitionDataDB.dbo.StaffName_Num' 没有聚集索引。

-- 开始切换,将数字开头的StaffName切换到刚才建立的表

alter table dbo.Staff switch partition 1 to dbo.StaffName_Num

-- 查看StaffName_Num表结果

select top 1 * from dbo.StaffName_Num

/*

10 Green Milton Drive
100 East New Way
100 North White First Street
100 West Green First St.
102 East White Nobel St.

*/

-- 查看StaffName表结果

select * from dbo.Staff where StaffName like '100'+'%'

/*

NULL

*/

满足条件： 1. 普通表必须建立在分区表切换分区所在的文件组上。 2. 普通表的表结构跟分区表的一致； 3. 普通表上的索引要跟分区表一致。 4. 普通表必须是空表，不能有任何数据。

-- 查看结果

6.1 切换普通表数据到分区表的一个分区中： Table to Partition

上面我们已经把分区表 切换 了，现在我们再切换回来：

alter table dbo.StaffName_Num switch to dbo.Staff partition 1

但是，此时有错误发生：

消息 4982，级别 16，状态 1，第 1 行
ALTER TABLE SWITCH 语句失败。源表 'PartitionDataDB.dbo.StaffName_Num' 的检查约束所允许的值对于目标表 'PartitionDataDB.dbo.Staff' 中分区 1 定义的范围是不允许的。

这就奇怪了，能把数据从分区切换进来却切换不出去。出错信息中提示我们是普通表的 check constraint 跟分区表不一致。于是在普通表上建立 check constraint ：

alter table dbo.StaffName_Num add constraint CK_Orders1998_StaffName
check ( left(StaffName,1) in (1,2,3,4,5,6,7,8,9) )

切换普通表数据到分区，除了满足上面的 4 个条件外，还要加上一条：普通表必须加上和分区数据范围一致的 check 约束条件。

6.3 切换分区表的分区到另一分区表： Partition to Partition

这里已无再建分区表了,,

实际上，分区表分区切换并没有真正去移动数据，而是 SQL Server 在系统底层改变了表的元数据。因此分区表分区切换是高效、快速、灵活的。利用分区表的分区切换功能，我们可以快速加载数据到分区表。卸载分区数据到普通表，然后 truncate 普通表，以实现快速删除分区表数据。快速归档不活跃数据到历史表。

7.查看哪些表使用了分区表

SELECT OBJECT_NAME (p. object_id ) AS ObjectName,

i.name                   AS IndexName,
      p.index_id               AS IndexID,
      ds.name                  AS PartitionScheme,  
      p.partition_number       AS PartitionNumber,
      fg.name                  AS FileGroupName,
      prv_left.value           AS LowerBoundaryValue,
      prv_right.value          AS UpperBoundaryValue,
CASE pf.boundary_value_on_right

WHEN 1 THEN ' RIGHT '
ELSE ' LEFT ' END AS Range,
      p.rows AS Rows
FROM sys.partitions                  AS p
JOIN sys.indexes                     AS i
ON i. object_id = p. object_id
AND i.index_id = p.index_id
JOIN sys.data_spaces                 AS ds
ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes           AS ps
ON ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions         AS pf
ON pf.function_id = ps.function_id
JOIN sys.destination_data_spaces     AS dds2
ON dds2.partition_scheme_id = ps.data_space_id
AND dds2.destination_id = p.partition_number
JOIN sys.filegroups                  AS fg
ON fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left
ON ps.function_id = prv_left.function_id
AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right
ON ps.function_id = prv_right.function_id
AND prv_right.boundary_id = p.partition_number
WHERE
OBJECTPROPERTY (p. object_id , ' ISMSShipped ' ) = 0
UNION ALL

-- non-partitioned table/indexes
SELECT
OBJECT_NAME (p. object_id )    AS ObjectName,
      i.name                      AS IndexName,
      p.index_id                  AS IndexID,
NULL AS PartitionScheme,
      p.partition_number          AS PartitionNumber,
      fg.name                     AS FileGroupName, 
NULL AS LowerBoundaryValue,
NULL AS UpperBoundaryValue,
NULL AS Boundary,
      p.rows                      AS Rows
FROM sys.partitions     AS p
JOIN sys.indexes        AS i
ON i. object_id = p. object_id
AND i.index_id = p.index_id
JOIN sys.data_spaces    AS ds
ON ds.data_space_id = i.data_space_id
JOIN sys.filegroups           AS fg
ON fg.data_space_id = i.data_space_id
WHERE
OBJECTPROPERTY (p. object_id , ' ISMSShipped ' ) = 0
ORDER BY
      ObjectName,
      IndexID,
      PartitionNumber;

详细的分区表与索引可以阅读:

http://msdn.microsoft.com/zh-cn/library/ms345146(SQL.90).aspx

参考:

http://www.cnblogs.com/goodspeed/archive/2005/11/07/270759.html