SQL Server分区表.docx

SQL Server分区表.docx

《SQL Server分区表.docx》由会员分享，可在线阅读，更多相关《SQL Server分区表.docx（17页珍藏版）》请在冰豆网上搜索。

SQLServer分区表

SQLServerPartitionTable

目录

1.分区表简介2

1.1什么是分区表2

1.2分区表使用条件3

1.3分区表的优势3

2.创建分区表3

2.1创建文件组和数据文件3

2.1.1添加文件组3

2.1.2添加数据库文件4

2.2创建分区函数4

2.3创建分区方案4

2.4创建分区表5

3.操作分区表6

3.1查询分区表编号7

3.2查询每个分区中的数据7

3.3统计各个分区中记录数8

4.将普通表转换为分区表9

4.1通过向导将普通表转换为分区表9

4.2通过SQL脚本将普通表转换为分区表13

5.分区表管理14

5.1合并（删除）分区14

5.2新曾分区14

5.2.1修改文件组14

5.2.2修改分区函数14

6.将分区表转换为普通表15

6.1删除分区索引15

6.2在原来列上新建索引15

6.3取消分区表脚本合并15

SQLServerPartitionTable（分区表）

1.分区表简介

数据库结构和索引的是否合理在很大程度上影响了数据库的性能，但是随着数据库信息负载的增大，对数据库的性能也发生了很大的影响。

可能我们的数据库在一开始有着很高的性能，但是随着数据存储量的急速增长，数据的性能也受到了极大的影响，一个很明显的结果就是查询的反应会非常慢。

在这个时候，除了可以优化索引及查询外，建立分区表（TablePartition）可以在某些场合下提高数据库的性能，在SQLServer2005中也可以通过SQL语句来创建表分区，但在SQLServer2008以及SQLServer2012中提供了向导形式来创建分区表。

1.1什么是分区表

分区表是把数据按某种标准划分成区域存储在不同（或者相同）的文件组中，使用分区可以快速而有效地管理和访问数据子集，从而使大型表或索引更易于管理。

简单的说就是把一张大型数据表分成若干个小的数据表，这些小的数据表在物理存储上是分散的（分散在不同的文件组的不同的数据文件中，或者不同的磁盘中分区中），但是在逻辑上是统一的，它还是一个数据表。

对于编程来说，程序员无需关系这些小的数据表，只需要像操作普通表一样操作这个大型的逻辑表即可，SQLServer会自动的到对应的数据分区（即数据子集）中操作数据。

1.2分区表使用条件

决定是否实现分区主要取决于数据表当前的大小或将来的大小、如何使用表以及对表执行用户查询和维护操作的完善程度。

并非所有的表都适用分区表，通常，如果某个大型表同时满足下列两个条件，则可能适于进行分区：

Ø该表包含（或将包含）以多种不同方式使用的大量数据

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

1.3分区表的优势

①提高可伸缩性和可管理性

在SQLserver2005中建立分区,改善大型表以及具有各种访问模式的表的可伸缩性和可管理性。

②提高性能

③只有将数据分区分到不同的磁盘上，才会有较大的提升

④因为在运行涉及表间联接的查询时，多个磁头可以同时读取数据

2.创建分区表

分区表的创建主要有以下四个步骤：

Ø创建文件组和数据文件

Ø创建分区函数

Ø创建分区方案

Ø创建分区表

2.1创建文件组和数据文件

创建文件组这一步并非是必须的，因为可以直接使用数据库的PRIMARY文件，但是，为了方便管理，还是可以先创建几个文件组，这样可以将不同的小表放在不同的文件组里，既便于理解又可以提高运行速度。

2.1.1添加文件组

创建文件组的方法很简单。

打开SQLServerManagementStudio，找到需要创建分区表的数据库，右击鼠标，在弹出的菜单中选择属性，在属性页中选择文件组，再点击添加按钮即可。

如图所示：

图1

2.1.2添加数据库文件

添加文件组之后，再分别为每一个文件组建立对应的数据文件，为什么要创建数据库文件呢，道理很简单，因为分区后的小表数据要存储到磁盘上。

建立数据库文件时，将不同的文件组指定到不同的数据库文件中，当然一个文件组中也可以包含多个数据库文件。

如果条件允许的话，可以将不同的文件放在不同的硬盘分区里，最好是放在不同的独立硬盘里。

因为IO的速度往往是影响SQLServer运行速度的重要条件之一。

将不同的文件放在不同的硬盘上，可以加快SQLServer的运行速度。

现在仅以将文数据库文件放到同一个磁盘上为例，如图所示：

图2

2.2创建分区函数

创建分区函数的目的是告诉SQLServer以什么方式对分区表进行分区，这一步是必须的。

例如有一张销售表（Sale），按年份划分为四张小表，分别为：

第1个小表：

2009-01-01之前的数据（不包含2009-01-01）

第2个小表：

2009-01-01（包含2009-01-01）到2009-12-31之间的数据

第3个小表：

2010-01-01（包含2010-01-01）到2010-12-31之间的数据

第4个小表：

2011-01-01（包含2011-01-01）之后的数据

那么分区函数如下:

CREATEPARTITIONFUNCTIONpartfunSale（datetime）

ASRANGERIGHTFORVALUES（'20090101','20100101','20110101'）

注:

CREATEPARTITIONFUNCTION:

创建分区函数

partfunSale:

分区函数名

ASRANGERight:

设置分区范围的方式，Right:

右置式（即<）,Left:

左置式（即<=）

FORVALUES:

分区值，即按这些值对表进行分区

2.3创建分区方案

分区方案的作用是将分区函数生成的分区映射到文件组中去。

分区函数的作用是告诉SQLServer如何将数据进行分区，而分区方案的作用则是告诉SQLServer将已分区的数据放在哪个文件组中。

分区方案的代码如下所示：

CREATEPARTITIONSCHEMEpartschSaleASPARTITIONpartfunSale

TO（Sale2008,Sale2009,Sale2010,Sale2011）

注:

CREATEPARTITIONSCHEME:

创建分区方案

partschSale:

分区方案名

ASPARTITION:

使用的分区函数

TO:

partfunSale分区函数划分出来的数据对应存放的文件组

至此，分区函数和分区方案已经创建完毕，此时分区函数和分区方案可以在数据库的’存储’中看到，如图所示

图3

2.4创建分区表

创建分区表和创建普通表很类似，如下所示:

CREATETABLESale（

[Id][int]IDENTITY（1,1）NOTNULL,

[Name][varchar]（16）NOTNULL,

[SaleTime][datetime]NOTNULL）ONpartschSale（[SaleTime]）

注:

这里的内容和创建普通数据表没有什么区别，惟一需要注意的是不能再创建聚集索引了。

道理很简单，聚集索引可以将记录在物理上顺序存储的，而分区表是将数据分别存储在不同的表中，这两个概念是冲突的，所以，在创建分区表的时候就不能再创建聚集索引了。

ONpartschSale:

使用的分区方案

[SaleTime]:

用于分区条件的字段是SaleTime

查看Sale表属性，如图所示:

图4

3.操作分区表

向Sale表中添加有一些数据

insertSale（[Name],[SaleTime]）values

（'李四','2008-12-1'）,

（'王五','2008-12-1'）,

（'张三','2009-1-1'）,

（'李四','2009-2-1'）,

（'王五','2009-3-1'）,

（'钱六','2010-4-1'）,

（'赵七','2010-5-1'）,

（'张三','2011-6-1'）,

（'李四','2011-7-1'）,

（'王五','2011-8-1'）,

（'钱六','2012-9-1'）,

（'赵七','2012-10-1'）,

（'张三','2012-11-1'）

从SQL语句中可以看出，在向分区表中插入数据方法和在普遍表中插入数据的方法是完全相同的，对于程序员而言，不需要去理会这13条记录研究放在哪个数据表中。

当然，在查询数据时，也可以不用理会数据到底是存放在哪个物理上的数据表中。

如使用以下SQL语句进行查询：

select*fromSale

查询结果如图所示:

图5

从上面两个步骤中，根本就感觉不到数据是分别存放在几个不同的物理表中，因为在逻辑上，这些数据都属于同一个数据表。

如果想知道哪条记录是放在哪个物理上的分区表中，那么就必须使用到$PARTITION函数，这个函数的可以调用分区函数，并返回数据所在物理分区的编号。

3.1查询分区表编号

假设想知道2010年10月1日的数据会放在哪个物理分区表中，可以使用以下语句来查看

select$partition.partfunSale（'2010-10-01'）

在以上语句中，partfunSale（）为分区函数名，括号中的表达式必须是日期型的数据或可以隐式转换成日期型的数据，这是因为在定义分区函数时已经确定了。

查询结果如图所示:

图6

在该图中可以看出，分区函数返回的结果为3，也就是说，2010年10月1日的数据会放在第3个物理分区表中。

3.2查询每个分区中的数据

再进一步考虑，如果想具体知道每个物理分区表中存放了哪些记录，也可以使用$PARTITION函数。

因为$PARTITION函数可以得到物理分区表的编号，那么只要将$PARTITION.partfunSale（SaleTime）做为where的条件使用即可，如以下代码所示：

select*fromSalewhere$PARTITION.partfunSale（SaleTime）=1

select*fromSalewhere$PARTITION.partfunSale（SaleTime）=2

select*fromSalewhere$PARTITION.partfunSale（SaleTime）=3

select*fromSalewhere$PARTITION.partfunSale（SaleTime）=4

查询结果如图所示:

图7

从上图中我们可以看到每个分区表中的数据记录情况——和我们插入时设置的情况完全一致。

3.3统计各个分区中记录数

如果要统计每个物理分区表中的记录数，可以使用如下代码：

select$PARTITION.partfunSale（SaleTime）as分区编号,count（id）as记录数

fromSalegroupby$PARTITION.partfunSale（SaleTime）

查询结果如图:

图8

除了在插入数据时程序员不需要去考虑分区表的物理情况之外，就是连修改数据也不需要考虑。

SQLServer会自动将记录从一个分区表移到另一个分区表中。

4.将普通表转换为分区表

在设计数据库时，经常没有考虑到表分区的问题，往往在数据表承重的负担越来越重时，才会考虑到分区方式，这时，就涉及到如何将普通表转换成分区表的问题了。

那么，如何将一个普通表转换成一个分区表呢？

其实只要将该表创建一个聚集索引，并在聚集索引上使用分区方案即可。

在SQLServer2008以后的版本中可以通过向导来创建分区表，在SQL2005中只有通过SQL脚本创建。

4.1通过向导将普通表转换为分区表

假如数据库中已存在表Order，现在要将Order表创建分区表。

我们先看一下Order表的属性，如图所示:

图9

Step1:

选中数据表，右键单击，在弹出菜单中选择存储，创建分区。

如图所示。

图10

Step2:

点击创建分区，将会弹出创建分区向导，如图所示

图11

Step3:

点击下一步，选择分区列，这里选择OrderDate列，如图所示:

图12

Step4:

点击下一步，选择或新建分区函数，这里选择新建，输入分区函数名，如图所示:

图13

Step5:

点击下一步，选择或新建分区方案，这里依然选择新建，输入分区方案名字，如图所示：

图14

Step6:

点击下一步，选择分区范围、设置边界值以及预计存储空间，如图所示:

图15

Step7:

点击下一步，选择输出选项，这里选择立即执行，如图所示:

图16

Step8:

点击完成，成功执行后即可将普通表创建为分区表。

我们再来看一下Order表的属性，如图所示:

图17

至此，使用向导已经完成分区表的创建。

4.2通过SQL脚本将普通表转换为分区表

在使用SQL脚本创建分区表时，需要注意，因为在SQLServer中，如果一个字段既是主键又是聚集索引时，并不能仅仅删除聚集索引。

因此，我们只能将整个主键删除，然后重新创建一个主键，只是在创建主键时，不将其设为聚集索引，如以下代码所示：

Step1:

ALTERTABLE[Order]DROPconstraintPK_Order

ALTERTABLESaleADDCONSTRAINTPK_OrderPRIMARYKEYNONCLUSTERED

（[ID]ASC）ON[PRIMARY]

Step2:

在重新创建非聚集主键之后，就可以为表创建一个新的聚集索引，并且在这个聚集索引中使用分区方案，如以下代码所示：

CREATECLUSTEREDINDEXCT_SaleONSale（[SaleTime]）

ONPartOrder（[OrderDate]）

通过以上代码也可以同样达到与向导创建分区表一样的效果。

5.分区表管理

有时候在创建分区之后发现有些分区中的数据并不多，完全可以和别的分区合并，或者发现一个分区数据过多，需要重新分区，此时就需要对已有分区进行合并（删除）或者新建操作。

5.1合并（删除）分区

在创建分区时我们是通过分界值来进行的，其实合并分区也就是删除原有分区中多余分界值就可实现。

合并分区代码如下:

ALTERPARTITIONFUNCTIONpartfunSale（）MERGERANGE（'20090101'）

在修改分区函数的同时，分区方案也一同被修改，我们现在可以在查看一下个分区中的记录数，如图所示:

图18

由图可以看出Sale表的分区数已经由原来的四个变为了三个。

5.2新曾分区

新增一个分区时不像合并分区那么简单，它不会自动修改分区方案，新增分区时有以下两个关键步骤：

Ø为分区方案指定一个可以使用的文件组

Ø修改分区函数

在为分区方案指定一个可用的文件组时，该分区方案并没有立刻使用这个文件组，只是将文件组先备用着，等修改了分区函数之后分区方案才会使用这个文件组（注意：

如果分区函数没有变，分区方案中的文件组个数就不能变）。

5.2.1修改文件组

ALTERPARTITIONSCHEMEpartschSaleNEXTUSED[Sale2008]

NEXTUSED:

下一个可使用的文件组

Sale2008:

文件组名

5.2.2修改分区函数

ALTERPARTITIONFUNCTIONpartfunSale（）SPLITRANGE（'20090101'）

SPLITRANGE:

分割界限

'20100101':

用于分割的界限值

经过以上两步的操作，我们再来看以分区统计结果如图所示:

图19

从图上可以看出，分区表又被分为了四个分区。

6.将分区表转换为普通表

将分区表转换为普通表需要以下两个步骤：

Ø删除分区索引（聚集索引）

Ø在原来的列上重新建立索引（不执行这一步，不能彻底删除分区表）

6.1删除分区索引

DropIndexdbo.Sale.PK_Sale

使用向导建立分区表时，并未自动创建索引，所以该步骤可以省略。

6.2在原来列上新建索引

CREATECLUSTEREDINDEXPK_SaleONSale（[SaleTime]）ON[PRIMARY]

经过以上两条SQL以后，我们再来看Sale表的属性，它已经变为普通表了，如图所示:

图20

6.3取消分区表脚本合并

可以将6.1和6.2中的脚本合并为一个执行，代码如下:

CREATECLUSTEREDINDEXPK_SaleONdbo.[Sale]（[SaleTime]）

WITH（DROP_EXISTING=ON）

ON[PRIMARY]