四、物理优化（5）表和索引分区

最新推荐文章于 2024-05-23 23:49:04 发布

weixin_33896069

最新推荐文章于 2024-05-23 23:49:04 发布

阅读量112

点赞数

CC 4.0 BY-SA版权

文章标签：数据库运维

本文链接：https://blog.youkuaiyun.com/weixin_33896069/article/details/85034664

本文介绍如何使用 SQL Server 2005 及以上版本创建和管理分区表和索引，包括分区的概念、创建步骤、调整分区以及相关限制。通过分区可以显著提高大型数据库的性能和管理效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、概念

　　可以使用 SSMS或 Transact-SQL 在 SQL Server 2005 及后续版本中创建已分区表或索引。已分区表和索引中的数据水平划分到可分散到数据库的多个文件组的单元中。分区可以使大型表和索引更易于管理并且更灵活。

　　通过对大型表或索引进行分区，可以具有以下可管理性和性能优点。

（1）可以快速、高效地传输或访问数据的子集，同时又能维护数据收集的完整性。例如，将数据从 OLTP 加载到 OLAP 系统之类的操作仅需几秒钟即可完成，而如果不对数据进行分区，执行此操作需要几分钟或几小时。

（2）可以更快地对一个或多个分区执行维护操作。这些操作的效率更高，因为它们仅针对这些数据子集，而非整个表。例如，您可以选择在一个或多个分区中压缩数据，或者重新生成索引的一个或多个分区。

（3）可以根据经常执行的查询类型和硬件配置，提高查询性能。例如，在两个或更多的已分区表中的分区列相同时，查询优化器可以更快地处理这些表之间的同等联接查询，因为可以联接这些分区本身。

　　当 SQL Server 针对 I/O 操作执行数据排序时，它会首先按分区对数据进行排序。 SQL Server 每次访问一个驱动器，这样可能会降低性能。为了提高数据排序性能，可以通过设置 RAID 将多个磁盘中的分区数据文件条带化。这样一来，尽管 SQL Server 仍按分区对数据进行排序，但它可以同时访问每个分区的所有驱动器。

　　此外，可以通过对在分区级别而不是整个表启用锁升级来提高性能。这可以减少表上的锁争用。

二、创建表和索引分区

　　本例中，将对一个名为“销售订单表”的基表创建表和索引分区。创建已分区表或索引通常包含四个操作：

1. 准备文件组和文件

　　创建将持有分区方案所指定的分区的文件组和相应的文件。

（1）使用SSMS

（2）使用T-SQL

USE [master]
GO
ALTER DATABASE [db01] ADD FILEGROUP [FG2012]
GO
ALTER DATABASE [db01] ADD FILEGROUP [FG2011]
GO
ALTER DATABASE [db01] ADD FILEGROUP [FG2010]
GO
ALTER DATABASE [db01] ADD FILEGROUP [FGHistory]
GO

ALTER DATABASE [db01] ADD FILE ( NAME = N'FG2012_data', FILENAME = N'C:\test\FG2012_data.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG2012]
GO
ALTER DATABASE [db01] ADD FILE ( NAME = N'FG2011_data', FILENAME = N'C:\test\FG2011_data.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG2011]
GO
ALTER DATABASE [db01] ADD FILE ( NAME = N'FG2010_data', FILENAME = N'C:\test\FG2010_data.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG2010]
GO

ALTER DATABASE [db01] ADD FILE ( NAME = N'FGHistory_data', FILENAME = N'C:\test\FGHistory_data.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FGHistory]
GO

2. 分区函数

　　创建一个分区函数，该函数根据指定列的值将表或索引的各行映射到分区。每个分区函数都要指定一个名称和一个数据类型。

USE db01

CREATE PARTITION FUNCTION partfunc(datetime) AS
RANGE RIGHT FOR VALUES('2010-1-1', '2011-1-1', '2012-1-1')

　　注意：如果分区函数中的任何行具有带 Null 值的分区列，会将这些行分配到最左侧分区。但是，如果将 NULL 指定为边界值并指示 RIGHT，则最左侧的分区仍为空，NULL 值位于第二个分区。

　　可以通过以下命令来查看上述分区函数的结果。

select * from sys.partition_range_values

3. 分区方案

　　创建一个将已分区表或已分区索引的分区映射到新文件组的分区方案。分区方案为存储提供了一个替代定义。可以定义一个分区方案来包含一个或多个文件组。

USE db01

CREATE PARTITION SCHEME partscheme AS
PARTITION partfunc TO
(FGHistory, FG2010, FG2011, FG2012)

　　可以通过以下命令查看分区架构。

select * from sys.partition_schemes

4. 创建分区表

　　创建表，并指定分区方案作为存储位置。

CREATE TABLE 销售订单表
  (订单编号      INT,
   客户编号      INT      NOT NULL,
   订单日期      DATETIME      NOT NULL,
   CONSTRAINT pk_orders PRIMARY KEY CLUSTERED(订单日期,订单编号) )
ON partscheme(订单日期)

5.创建索引

　　和创建分区表相似，为了对索引进行分区，要在ON子句中指定一个分区方案。

CREATE NONCLUSTERED INDEX idx_orderID
ON 销售订单表(订单编号) ON partscheme(订单日期)

三、管理分区

　　在前面的练习中，已经将销售订单按年份分割为“2010年之前”、“2010年”、“2011年”、“2012年及以后”，共4个分区。

　　本例中，要将最后一个分区拆分为“2012年”和“2013年及以后”；然后将“2010年之前”的数据移动到另一个“销售历史表”，最后将最前面2个分区合并为“2011年”之前。

1. 准备文件组和文件

ALTER DATABASE [db01] ADD FILEGROUP [FG2013]
ALTER DATABASE [db01] ADD FILE ( NAME = N'FG2013_data', FILENAME = N'C:\test\FG2013_data.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG2013]

2. 修改分区架构

　　修改分区方案，在文件组设置Next Used标志，

ALTER PARTITION SCHEME partscheme Next Used FG2013

3. SPLIT

　　SPLIT从分区函数中引入一个新的边界点。

ALTER PARTITION FUNCTION partfunc() SPLIT RANGE('2013-1-1')
select * from sys.partition_range_values

4. 创建“销售历史表”

CREATE TABLE 销售历史表
  (订单编号      INT,
   客户编号      INT      NOT NULL,
   订单日期      DATETIME      NOT NULL
   CONSTRAINT ck_orderdate CHECK (订单日期<'2010-1-1')
   CONSTRAINT pk_orderhistory PRIMARY KEY CLUSTERED(订单日期,订单编号) )
ON FG2010

　　注意：源表和目标表必须共享同一个文件组。

5. SWITCH

　　使用SWITCH运算符将“2010年之前”分区从“销售订单表”分离，把它连接到“销售历史表”。 http://technet.microsoft.com/zh-cn/library/ms191160(v=sql.105).aspx

ALTER TABLE 销售订单表
SWITCH PARTITION 2 TO 销售历史表

6. MERGE

　　MERGE从分区函数中删除‘2010-1-1’边界点。

ALTER PARTITION FUNCTION partfunc()
MERGE RANGE ('2010-1-1')
select * from sys.partition_range_values

四、限制和局限

1. 命名空间

　　分区函数和方案的作用域被限制为在其中创建它们的数据库。在该数据库内，分区函数驻留在与其他函数的命名空间不同的一个单独命名空间内。

2. 版本限制

　　仅Enterprise Edition允许表和索引分区。