所谓分区表就是当表太大的时候,比如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成需要的类型。
如何向分区表加载数据呢?
- 建一个同结构的临时表;
- 加入数据
- Alter table DayTransactions_Daily add constraint x check(dayid=1), 限制待加入表的值是等于目标分区允许的值的;
- alter table DayTransactions_Daily switch to DayTransactions partition(1), 将表加入目标分区中;
- 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.