第十三章——表和索引分区（1）——使用Range Left进行表分区

前言：

如果数据表的数据持续增长，并且表中的数据量已经达到数十亿甚至更多，数据的查询和操作将非常困难，面对非常庞大的表，几时简单的增删改操作都会花费非常多的时间，如删除某个数据然后重建索引这些操作，会很难实现。在这种情况下，管理和维护查询性能就成为了一种挑战。

在过去的日子，也就是2005之前，你可能需要使用分区视图来处理大数据量的数据，从2005开始，微软引入了叫做表分区的新特性。允许水平分割数据成为多个分区。并且也允许把这些分区放到不同的文件组从然后放到不同的磁盘上。由于在访问的时候只需要访问部分的分区，从而减少了不必要的查询范围。

通过表分区，下面的操作将能很好地实现：

1、 查找一定范围的数据。

2、 删除和归档旧数据。

3、 加载大量数据。

4、 重建和重组索引。

同样可以把大表上的索引进行分区。在SQLServer2012中，最多能达到15000个分区，但是不能对text,ntext,image,xml,timestamp,varchar(max),nvarchar(max)或varbinary(max)数据列进行分区。

 

带有Range Left的表分区：

假设需要设计一个数据库，并且有一个表需要存放几百万数据，为了提高性能，你决定基于ID列来分区，一开始，暂定分4个区：

1、 ID大于等于0

2、 ID从1~1000000

3、 ID从1000001~2000000

4、 ID从2000001~3000000

但是，在这里，分区1将永远不会有任何数据，因为ID值是从1开始，并以1增长的。此时，需要有一个好的分区范围来达到数据要求。

表分区有两个配置选项去设定分区范围：RANGE LEFT 和RANGE RIGHT。本文中将演示使用RANGE LEFT来实现分区。

为了把表分区，有两个重要的对象需要创建：分区函数和分区架构。首先，使用分区函数定义范围值，然后使用分区架构定义物理存储位置。

本例中，将会演示如何创建一个分区函数和分区架构，并应用到分区表中。

 

准备工作：

本文需要一个示例数据库，创建在C:\SQLData上的Sample_DB，确保有这个路径。

步骤：

1、 打开SQLServer

2、 执行下面语句创建Sample_DB：

USE master
GO
IF DB_ID('Sample_DB') IS NOT NULL 
    DROP DATABASE [Sample_DB]
	
CREATE DATABASE [Sample_DB] ON PRIMARY
(
	NAME =N'Sample_DB',FILENAME=N'C:\SQLData\Sample_DB.mdf',
	SIZE=3072KB,FILEGROWTH=1024KB
), FILEGROUP [FG_1]
(
	NAME =N'FG_1_DataFile',FILENAME=N'C:\SQLData\FG_1_DataFile.ndf',
	SIZE=3072KB,FILEGROWTH=1024KB
), FILEGROUP [FG_2]
(
	NAME =N'FG_2_DataFile',FILENAME=N'C:\SQLData\FG_2_DataFile.ndf',
	SIZE=3072KB,FILEGROWTH=1024KB
), FILEGROUP [FG_3]
(
	NAME =N'FG_3_DataFile',FILENAME=N'C:\SQLData\FG_3_DataFile.ndf',
	SIZE=3072KB,FILEGROWTH=1024KB
), FILEGROUP [FG_N]
(
	NAME =N'FG_N_DataFile',FILENAME=N'C:\SQLData\FG_N_DataFile.ndf',
	SIZE=3072KB,FILEGROWTH=1024KB
) LOG ON 
(
NAME =N'Sample_DB_log',FILENAME=N'C:\SQLData\Sample_DB_log.ldf',
	SIZE=3072KB,FILEGROWTH=10%
)
GO

3、 创建一个带有RANGE LEFT的分区函数pf_OneMillion_LeftRange：

USE Sample_DB
GO

CREATE PARTITION FUNCTION pf_OneMillion_LeftRange(INT)
AS RANGE LEFT FOR VALUES(0,1000000,2000000,3000000)
GO

4、 验证分区函数： 

USE Sample_DB
GO
SELECT  name ,
        function_id ,
        type ,
        type_desc ,
        fanout ,
        boundary_value_on_right ,
        create_date
FROM    sys.partition_functions

SELECT  function_id ,
        boundary_id ,
        parameter_id ,
        value
FROM    sys.partition_range_values
GO

5、 得到下面的结果：

6、 现在运行下面的脚本，创建并验证分区架构：

USE Sample_DB
GO

CREATE PARTITION SCHEME ps_OneMillion_LeftRange
AS PARTITION pf_OneMillion_LeftRange
TO ([primary],[FG_1],[FG_2],[FG_3],[FG_N])

SELECT  name ,
        data_space_id ,
        type ,
        type_desc ,
        function_id
FROM    sys.partition_schemes
GO

7、 下面是结果： 

8、 现在创建一个表tbl_SampleRecords，并插入500万数据：

USE Sample_DB
GO

IF OBJECT_ID('tbl_SampleRecords') IS NOT NULL 
    DROP TABLE tbl_SampleRecords
	
CREATE TABLE tbl_SampleRecords
    (
      id INT ,
      SomeDate SYSNAME ,
      CONSTRAINT pk_tbl_SampleRecords_id PRIMARY KEY CLUSTERED ( id )
    )
ON  ps_OneMillion_LeftRange(id)
GO

INSERT  INTO tbl_SampleRecords
        SELECT TOP 5000000
                id = ROW_NUMBER() OVER ( ORDER BY C1.name ) ,
                somedata = c1.NAME
        FROM    sys.columns AS C1
                CROSS JOIN sys.columns AS C2
                CROSS JOIN sys.columns AS C3
GO

9、 现在来验证一下分区数和行数： 

 USE Sample_DB
GO

SELECT  partition_id ,
        object_id ,
        index_id ,
        partition_number ,
        rows
FROM    sys.partitions
WHERE   object_id = OBJECT_ID('tbl_SampleRecords')
GO

10、             下面是截图：

 

 

RANGE LEFT定义每个分区的边界值，指定每个范围值是当前分区中最高值，也就是说分区的边界是属于左边值。