数据库分区表[通俗易懂]

　表分区分为水平分区和垂直分区。水平分区将表分为多个表。每个表包含的列数相同，但是行更少。例如，可以将一个包含十亿行的表水平分区成 12 个表，每个小表表示特定年份内一个月或几个月的数据。任何需要特定月份数据的查询只需引用相应月份的表。而垂直分区则是将原始表分成多个只包含较少列的表。水平分区是最常用分区方式，后面我们以水平分区来介绍具体实现方法。

    简单一点说，分区表就是将一个大表分成若干个小表。这里，我们有一个销售记录表，记录着每个某餐饮店的订单情况，那么你就可以把这个销售记录表按时间分成几个小表，我们这里分成15个小表。2011年以前的记录使用一个表，2011年的记录每两个月使用一个表（2011//01/01-2011/03/01，每个两个月一个表，一共六个表），2012年,2013（）年的的记录同上，2013年的记录使用一个表，2012年以后的记录使用一个表。那么，你想查询哪个年份的记录，就可以去相对应的表里查询，由于每个表中的记录数少了，查询起来时间自然也会减少。

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

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

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

　　分区表的准备工作完事了，下一步就是具体创建分区表了，等不急了吧，看下篇博文你就知道了。

创建分区表必须要经过下面五个步骤。

1）创建文件组

2）创建文件

3）创建分区函数

4）创建分区方案

5）创建分区表

（1）创建文件组，有两种方案，一种是通过手动添加，另外一种就是通过SQL脚本进行添加。下面以两种方案来说明：

       方案一：创建文件组，虽然这一步我们可以省略，因为我们可以直接使用Primary文件（也就是系统主文件）。但是为了方便管理，我们还是要创建几个文件组，这样可以将不同的小表（不同时间段，或者不同数据表）放在不同的文件组里，既便于理解又可以提高运行速度。

       打开SQL Server Management Studio，找到分区表所在的数据库，右键单击选择“属性”，选择“文件组”选项，单击下面的“添加”按钮，添加X个文件组，如下图所示：

       方案二：通过查询分析器SQL脚本执行

              ALTER DATABASE CXFunSche ADD FILEGROUP CXFG2010

ALTER DATABASE CXFunSche ADD FILEGROUP CXFG2011

ALTER DATABASE CXFunSche ADD FILEGROUP CXFG2012

ALTER DATABASE CXFunSche ADD FILEGROUP CXFG2013

（2）创建数据库文件

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

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

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

在本文的实例中，数据库主文件与分区文件就不在同一个目录下，各个分区文件也可以放置在不同的目录下，建议大家在练习时使用。

    方案二：通过查询分析器SQL脚本执行

    ALTER DATABASE CXFunSche ADD FILE ( NAME = N‘SellLogDetail2010’, FILENAME = N‘D:\program files\Programming Software\SQL Server 2005\MSSQL.1\MSSQL\DATA\SellLogDetail2010.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP CXFG2010

ALTER DATABASE CXFunSche ADD FILE ( NAME = N‘SellLogDetail201102’, FILENAME = N‘D:\program files\Programming Software\SQL Server 2005\MSSQL.1\MSSQL\DATA\SellLogDetail201102.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP CXFG2010

ALTER DATABASE CXFunSche ADD FILE ( NAME = N‘SellLogDetail201104’, FILENAME = N‘D:\program files\Programming Software\SQL Server 2005\MSSQL.1\MSSQL\DATA\SellLogDetail201104.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP CXFG2011

ALTER DATABASE CXFunSche ADD FILE ( NAME = N‘SellLogDetail201106’, FILENAME = N‘D:\program files\Programming Software\SQL Server 2005\MSSQL.1\MSSQL\DATA\SellLogDetail201106.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP CXFG2011

ALTER DATABASE CXFunSche ADD FILE ( NAME = N‘SellLogDetail201108’, FILENAME = N‘D:\program files\Programming Software\SQL Server 2005\MSSQL.1\MSSQL\DATA\SellLogDetail201108.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP CXFG2011

ALTER DATABASE CXFunSche ADD FILE ( NAME = N‘SellLogDetail201110’, FILENAME = N‘D:\program files\Programming Software\SQL Server 2005\MSSQL.1\MSSQL\DATA\SellLogDetail201110.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP CXFG2011

ALTER DATABASE CXFunSche ADD FILE ( NAME = N‘SellLogDetail201112’, FILENAME = N‘D:\program files\Programming Software\SQL Server 2005\MSSQL.1\MSSQL\DATA\SellLogDetail201112.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP CXFG2011

ALTER DATABASE CXFunSche ADD FILE ( NAME = N‘SellLogDetail201202’, FILENAME = N‘D:\program files\Programming Software\SQL Server 2005\MSSQL.1\MSSQL\DATA\SellLogDetail201202.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP CXFG2012

.

.

.

由于创建的数据文件太多了，这里就不一一贴出来了，剩下的几个，留给大家自己锻炼一下吧。创建完分区文件后，你就可以在D盘相应的目录下找到你刚刚创建的数据文件。

数据库分区表（三）如何创建分区表2？  

数据库分区表（四）将普通表转换成分区表  

2011-02-15 16:27:43|  分类： 数据库学习 |  标签： |字号大中小 订阅

　　我们的数据库已经投入使用一段时间了，但是当时没有创建创建分区表，现在我们需要做的是将普通表转换成分区表，但是并不能影响我们数据库里面的数据，那么我们应该如何做呢？只需在该表上创建一个聚集索引，并在该聚集索引中使用分区方案即可。

     说的很简单，但是在实现实现可就没有那么容易了，因为你的数据库中存在主键，外键等约束关系，那么我们在将普通表转换成分区表时，首先就需要解决这些问题。

     我们知道分区表时某个字段为分区条件的，除了这个字段之外的其他字段是不能创建聚集索引的，所以我们将普通表转换成分区表时，必须要删除聚集索引，然后再重新创建一个新的聚集索引，在该聚集索引中使用分区方案。

     但是我们需要修改的t_sellLog表中的orderId既是主键又是聚集索引，而且还是其它表的外键。因此，我们只能先删除外键关联，再删除主键，然后重新创建orderId为主键，但是设置为非聚集索引，然后将我们的sellTime字段设置为聚集索引，最后添加上我们的外键约束，至此普通表转换成分区表的工作结束,代码如下：

     —查看外键约束

use CX_Partiton_Scheme

exec sp_helpconstraint t_SellLog

—删除外键约束

alter table t_sellLog drop constraint FK_t_SellLog_t_User

—删掉主键

ALTER TABLE t_SellLog DROP constraint PK_t_SellLog 

—创建主键，但不设为聚集索引

ALTER TABLE t_SellLog ADD CONSTRAINT PK_t_SellLog PRIMARY KEY NONCLUSTERED ( 

    orderId ASC

)

 ON [PRIMARY]

 —创建一个新的聚集索引，在该聚集索引中使用分区方案

CREATE CLUSTERED INDEX CT_SellLog ON t_SellLog(sellTime) 

ON partsch_CX([sellTime])

 —添加删除掉的外键约束（具体自己根据实际情况自己实现）

     转换成功之后，我们可以通过下面代码查看每个分区表中的记录数：

   —统计所有分区表中的记录总数  

select $PARTITION.partfun_CX([sellTime]) as 分区编号,count(orderId) as 记录数from t_SellLog group by $PARTITION.partfun_CX([sellTime])

     我们还可以通过下面的代码，查看数据库库中的数据在哪个分区中：

   —查看数据库表中的数据在哪个分区中

select $PARTITION.partfun_CX(‘2010-10-1’)  —查询年月日的数据在哪个分区中

select $PARTITION.partfun_CX(‘2011-01-1’)  —查询年月日的数据在哪个分区中如果你想比较一下我们使用分区方案之后和之前程序有多少效率提高，我们可以通过下面的语句来看看一下脚本的执行时间就OK了，我经过测试的数据是快了0.017秒，一方面由于我们的测试数据量比较小，另一方面我的机器配置还是蛮不错的。

     —查看SQL脚本的执行时间

   select getDate()

   select * from t_sellLog

   select getDate()