SQL研究-分区表

所谓分区表就是当表太大的时候，比如1百万行，按照某个特殊的列分成10份，每份10万行左右，要求这一列中的值是有限的，并且每个值对应的行数基本相等。使用分区表可以方便的进行整个分区的增减，只涉及元数据而不涉及具体的数据写入操作。

 

创建一个分区表需要经过以下几个步骤：

1. 创建分区函数，它附则将插入的数据放到相应的分区里面去； 

2. 创建分区方案，它附则确定每个分区的数据存放在什么地方；

3. 创建分区表，将表和分区方案关联起来。

 

下面是一个简单的例子：

        create partition function DayIdPartFunc(int)

as range left

for values(1)

创建一个分区函数，接受一个整数作为输入，边界值是1，1属于左边的分区

 

可以使用下面的函数来查看分区函数将特定的值映射到哪个分区： 

select $partition.DayIdPartFunc(0)

select $partition.DayIdPartFunc(1)

select $partition.DayIdPartFunc(2)

 

创建分区方案，将分区数据存储在Primary文件组上。 

create partition scheme DayIdPartSch

as Partition  DayIdPartFunc

all to ([primary])

 

然后创建表 

create table DayTransactions

(

DayId int,

customer int,

balance int

)

on DayIdPartSch(DayId)

 

通过sys.partitions可以查看表DayTransactions有多少分区。通常一个表不创建索引的话是一个分区，但现在是两个。这就是分区表的效果。

72057594042580992        1573580644        0        1        72057594042580992        0

72057594042646528        1573580644        0        2        72057594042646528        0

 

对于一个已存在的分区函数，知道其边界值是有用的，可以如下查看分区的边界值：

select a.name,b.value

from sys.partition_functions a join sys.partition_range_values b on a.function_id=b.function_id

where a.name='DayIdPartFunc'

 

其中b.value是sql_variant类型，需要cast成需要的类型。

 

如何向分区表加载数据呢？ 

1. 建一个同结构的临时表；
2. 加入数据
3. Alter table DayTransactions_Daily add constraint x check(dayid=1)， 限制待加入表的值是等于目标分区允许的值的；
4. alter table DayTransactions_Daily switch to DayTransactions partition(1)， 将表加入目标分区中；
5. select * from DayTransactions，可以看到已经包含了源表的数据，而源表的数据被清空了

 现在再向目标表加一个分区，同样首先删除掉dayid的限制： 

alter table DayTransactions_Daily drop x

再加入几行数据

insert into DayTransactions_Daily values(2,1,100)

insert into DayTransactions_Daily values(2,2,120)

insert into DayTransactions_Daily values(2,3,220)

再加上新的限制

Alter table DayTransactions_Daily add constraint x check(dayid=2 and dayid is not null)

请注意到，多了一个限制，因为dayid为null会被映射到分区1上面去，而我们是想把这些数据放入第二个分区。

alter table DayTransactions_Daily switch to DayTransactions partition(2)

 

好吧，这样我们就把dayid为2的数据放入分区表中。但是需要注意如果我们加入dayid为3的数据，它仍然会放入第二个分区。因为分区2存放所有dayid大于1 的数据。

 

为了平衡分区的数据量，我们需要在原来分区的基础上再增加一个分区，

alter partition function DayIdPartFunc() split range (2)

 

然后我们通过视图检查的时候可以看到新增分区的效果。

select * from sys.partitions where object_id=object_id('DayTransactions')。

 

正如我们之前讲过的那样，在分区表上创建聚集索引有助于提高性能。例如我们可以在Customer上创建聚集索引，如下所示：

create clustered index idx_cust on dbo.DayTransactions(DayId,Customer)

此时必须要在源表上创建相同的索引，否则switch时会有以下错误：

Msg 4913, Level 16, State 2, Line 1
ALTER TABLE SWITCH statement failed. The table 'test.dbo.DayTransactions' has clustered index 'idx_cust' while the table 'test.dbo.DayTransactions_Daily' does not have clustered index.