分区表2

 

 

转:http://hi.baidu.com/wcaowxd/blog/item/68b8222eb817d3e38b139922.html

 

如果你的数据库中某一个表中的数据满足以下几个条件，那么你就要考虑创建分区表了。

   1、数据库中某个表中的数据很多。很多是什么概念？一万条？两万条？还是十万条、一百万条？这个，我觉得是仁者见仁、智者见智的问题。当然数据表中的数据多到查询时明显感觉到数据很慢了，那么，你就可以考虑使用分区表了。如果非要我说一个数值的话，我认为是100万条。

   2、但是，数据多了并不是创建分区表的惟一条件，哪怕你有一千万条记录，但是这一千万条记录都是常用的记录，那么最好也不要使用分区表，说不定会得不偿失。只有你的数据是分段的数据，那么才要考虑到是否需要使用分区表。

   3、什么叫数据是分段的？这个说法虽然很不专业，但很好理解。比如说，你的数据是以年为分隔的，对于今年的数据而言，你常进行的操作是添加、修改、删除和查询，而对于往年的数据而言，你几乎不需要操作，或者你的操作往往只限于查询，那么恭喜你，你可以使用分区表。换名话说，你对数据的操作往往只涉及到一部分数据而不是所有数据的话，那么你就可以考虑什么分区表了。

   那么，什么是分区表呢？

   简单一点说，分区表就是将一个大表分成若干个小表。假设，你有一个销售记录表，记录着每个每个商场的销售情况，那么你就可以把这个销售记录表按时间分成几个小表，例如说5个小表吧。2009年以前的记录使用一个表，2010年的记录使用一个表，2011年的记录使用一个表，2012年的记录使用一个表，2012年以后的记录使用一个表。那么，你想查询哪个年份的记录，就可以去相对应的表里查询，由于每个表中的记录数少了，查询起来时间自然也会减少。

   但将一个大表分成几个小表的处理方式，会给程序员增加编程上的难度。以添加记录为例，以上5个表是独立的5个表，在不同时间添加记录的时候，程序员要使用不同的SQL语句，例如在2011年添加记录时，程序员要将记录添加到2011年那个表里；在2012年添加记录时，程序员要将记录添加到2012年的那个表里。这样，程序员的工作量会增加，出错的可能性也会增加。

   使用分区表就可以很好的解决以上问题。分区表可以从物理上将一个大表分成几个小表，但是从逻辑上来看，还是一个大表。

   接着上面的例子，分区表可以将一个销售记录表分成五个物理上的小表，但是对于程序员而言，他所面对的依然是一个大表，无论是2010年添加记录还是 2012年添加记录，对于程序员而言是不需要考虑的，他只要将记录插入到销售记录表——这个逻辑中的大表里就行了。SQL Server会自动地将它放在它应该呆在的那个物理上的小表里。

   同样，对于查询而言，程序员也只需要设置好查询条件，OK，SQL Server会自动将去相应的表里查询，不用管太多事了。

   这一切是不是很诱人？

   的确，那么我们就可以开始动手创建分区表了。

   第一、创建分区表的第一步，先创建数据库文件组，但这一步可以省略，因为你可以直接使用PRIMARY文件。但我个人认为，为了方便管理，还是可以先创建几个文件组，这样可以将不同的小表放在不同的文件组里，既便于理解又可以提高运行速度。创建文件组的方法很简单，打开SQL Server Management Studio，找到分区表所在数据库，右键单击，在弹出的菜单里选择“属性”。然后选择“文件组”选项，再单击下面的“添加”按钮，如下图所示：

   第二，创建了文件组之后，还要再创建几个数据库文件。为什么要创建数据库文件，这很好理解，因为分区的小表必须要放在硬盘上，而放在硬盘上的什么地方呢？当然是文件里啦。再说了，文件组中没有文件，文件组还要来有啥用呢？还是在上图的那个界面，选择“文件”选项，然后添加几个文件。在添加文件的时候要注意以下几点：

   1、不要忘记将不同的文件放在文件组中。当然一个文件组中也可以包含多个不同的文件。

   2、如果可以的话，将不同的文件放在不同的硬盘分区里，最好是放在不同的独立硬盘里。要知道IQ的速度往往是影响SQL Server运行速度的重要条件之一。将不同的文件放在不同的硬盘上，可以加快SQL Server的运行速度。

   在本例中，为了方便起见，将所有数据库文件都放在了同一个硬盘下，并且每个文件组中只有一个文件。如下图所示。

   第三、创建一个分区函数。这一步是必须的了，创建分区函数的目的是告诉SQL Server以什么方式对分区表进行分区。这一步必须要什么SQL脚本来完成。以上面的例子，我们要将销售表按时间分成5个小表。假设划分的时间为：

   第1个小表：2010-1-1以前的数据（不包含2010-1-1）。

   第2个小表：2010-1-1（包含2010-1-1）到2010-12-31之间的数据。

   第3个小表：2011-1-1（包含2011-1-1）到2011-12-31之间的数据。

   第4个小表：2012-1-1（包含2012-1-1）到2012-12-31之间的数据。

   第5个小表：2013-1-1（包含2013-1-1）之后的数据。

   那么分区函数的代码如下所示：

CREATE PARTITION FUNCTION partfunSale (datetime)
AS RANGE RIGHT FOR VALUES ('20100101','20110101','20120101','20130101')

 

CREATE PARTITION FUNCTION partfunSale (datetime)
AS RANGE RIGHT FOR VALUES ('20100101','20110101','20120101','20130101')

 

   其中：

   1、CREATE PARTITION FUNCTION意思是创建一个分区函数。

   2、partfunSale为分区函数名称。

   3、AS RANGE RIGHT为设置分区范围的方式为Right，也就是右置方式。

   4、FOR VALUES ('20100101','20110101','20120101','20130101')为按这几个值来分区。

   这里需要说明的一下，在Values中，'20100101'、'20110101'、'20120101'、'20130101'，这些都是分区的条件。“ 20100101”代表2010年1月1日，在小于这个值的记录，都会分成一个小表中，如表1；而小于或等于'20100101'并且小于 '20110101'的值，会放在另一个表中，如表2。以此类推，到最后，所有大小或等于'20130101'的值会放在另一个表中，如表5。

   也许有人会问，为什么值“ 20100101”会放在表2中，而不是表1中呢？这是由AS RANGE RIGHT中的RIGHT所决定的，RIGHT的意思是将等于这个值的数据放在右边的那个表里，也就是表2中。如果您的SQL语句中使用的是Left而不是RIGHT，那么就会放在左边的表中，也就是表1中。

   第四、创建一个分区方案。分区方案的作用是将分区函数生成的分区映射到文件组中去。分区函数的作用是告诉SQL Server，如何将数据进行分区，而分区方案的作用则是告诉SQL Server将已分区的数据放在哪个文件组中。分区方案的代码如下所示：

CREATE PARTITION SCHEME partschSale
AS PARTITION partfunSale
TO (
     Sale2009,
     Sale2010,
     Sale2011,
     Sale2012,
     Sale2013)

 

CREATE PARTITION SCHEME partschSale
AS PARTITION partfunSale
TO (
     Sale2009,
     Sale2010,
     Sale2011,
     Sale2012,
     Sale2013)

 

   其中：

   1、CREATE PARTITION SCHEME意思是创建一个分区方案。

   2、partschSale为分区方案名称。

   3、AS PARTITION partfunSale说明该分区方案所使用的数据划分条件（也就是所使用的分区函数）为partfunSale。

   4、TO后面的内容是指partfunSale分区函数划分出来的数据对应存放的文件组。

   到此为止，分区函数和分区方案就创建完毕了。创建后的分区函数和分区方案在数据库的“存储”中可以看到，如下图所示：

   最后，创建分区表，创建方式和创建普遍表类似，如下所示：

CREATE TABLE Sale(
     [Id] [int] IDENTITY(1,1) NOT NULL,
     [Name] [varchar](16) NOT NULL,
     [SaleTime][datetime] NOT NULL
) ON partschSale([SaleTime])

 

CREATE TABLE Sale(
     [Id] [int] IDENTITY(1,1) NOT NULL,
     [Name] [varchar](16) NOT NULL,
     [SaleTime][datetime] NOT NULL
) ON partschSale([SaleTime])

 

   其中：

   1、CREATE TABLE 意思是创建一个数据表。

   2、Sale为数据表名。

   3、()中为表中的字段，这里的内容和创建普通数据表没有什么区别，惟一需要注意的是不能再创建聚集索引了。道理很简单，聚集索引可以将记录在物理上顺序存储的，而分区表是将数据分别存储在不同的表中，这两个概念是冲突的，所以，在创建分区表的时候就不能再创建聚集索引了。

   4、ON partschSale()说明使用名为partschSale的分区方案。

   5、partschSale()括号中为用于分区条件的字段是SaleTime。

   OK，一个物理上是分离的，逻辑上是一体的分区表就创建完毕了。查看该表的属性，可以看到该表已经属于分区表了。