MyException - 我的异常网
当前位置:我的异常网» 数据库 » 第十三章——表跟索引分区(1)——使用Range Left

第十三章——表跟索引分区(1)——使用Range Left进行表分区

www.myexceptions.net  网友分享于:2013-04-10  浏览:1次
第十三章——表和索引分区(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定义每个分区的边界值,指定每个范围值是当前分区中最高值,也就是说分区的边界是属于左边值。

 

文章评论

软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有