SQL Server 大数据管理——表分区

ellipse数据库技术 2019-08-16

351

点击上方

蓝色

文字关注我们吧！

一. 创建分区文件组/文件

--创建分区文件组
alter database test add filegroup test2015
alter database test add filegroup test2016
alter database test add filegroup test2017
alter database test add filegroup test2018
--创建分区文件
alter database test
  add file(name='test2015'
    ,filename='D:\DB\testPartion\test2015.ndf'
    ,size=1mb
    ,filegrowth=1mb)
    to filegroup test2015;
alter database test
  add file(name='test2016'
    ,filename='D:\DB\testPartion\test2016.ndf'
    ,size=1mb
    ,filegrowth=1mb)
    to filegroup test2016;
alter database test 
add file(name=N'test2017'
  ,filename=N'D:\DB\testPartion\test2017.ndf'
  ,size=1mb
  ,filegrowth=1mb)
to filegroup test2017
alter database test 
add file(name=N'test2018'
  ,filename=N'D:\DB\testPartion\test2018.ndf'
  ,size=1mb
  ,filegrowth=1mb)
to filegroup test2018

二. 创建分区函数

（1）创建分区函数，创建分区函数的目的是告诉数据库管理系统以什么方式对表进行分区

（2）创建分区方案，分区方案的作用是将分区函数生成的分区映射到文件组中

（3）使用分区方案创建表

（1）    --创建分区函数
    create partition function f_TestDate(datetime)
    as range right for values('2016-01-01','2017-01-01','2018-01-01')

注意：

1. F_TestDate 为分区函数名，分区的字段是datetime类型

2. Right 表示该分区包含右边界值，上面分区函数会把数据分为

小于2016.1.1

大于等于2016.1.1 且小于2017.1.1

大于等于2017.1.1 且小于2018.1.1

大于等于2018.1.1

四个分区，若把right换为left，则分区变为

小于等于2016.1.1

大于2016.1.1 且小于等于2017.1.1

大于2017.1.1 且小于等于2018.1.1

大于2018.1.1

    （2）--创建分区方案
    create partition scheme s_TestDate
    as partition f_TestDate to (test2015,test2016,test2017,test2018)

注意：

1.分区方案是建立在分区函数的基础上的，所以先建立分区函数，再建立分区方案

2.分区个数比分区边界值多1

3.本分区方案每个分区建在一个文件组上，当然也可以把所有分区建立在一个文件组上

    （3）--创建分区方案,所有分区均建立在主文件组上
    create partition scheme s_TestDate
    as partition f_TestDate all to ([primary])

三. 创建分区表

3.1 新建分区表

    create table tradelog
    (
      ID int,
      productID int,
      tradedate datetime
    ) on s_TestDate(tradedate)

注：创建分区表，用的是s_TestDate分区方案名称

3.2 对已有表分区

若表上没有聚集索引，可以通过创建聚集索引，对表进行分区

CREATE CLUSTERED INDEX [CLI_tn_TestDate] ON [dbo].[tradelog_noClusterIndex]
(
  [tradedate]
) ON [s_TestDate]([tradedate])
 
--如果不需要聚集索引,删除聚集索引
DROP INDEX [CLI_tn_TestDate] ON [dbo].[tradelog_noClusterIndex]

‍若表上已有聚集索引，删除聚集索引，再通过上面脚本重建聚集索引。或者通过WITH（DROP_EXISTING=ON）重建聚集索引，脚本如下：

CREATE CLUSTERED INDEX [CLI_tn_TestDate] ON [dbo].[tradelog_noClusterIndex]
(
  [tradedate]
)WITH (DROP_EXISTING = ON) ON [s_TestDate]([tradedate]

四. 增加分区

增加分区的方法是将某个现有的分区“拆分”为两个分区并重新定义新分区的边界。

--向分区表插入1000W行数据
DECLARE @max AS INT, @rc AS INT;  
SET @max = 10000000;  
SET @rc = 1;  
INSERT INTO tradelog(id,productID,tradedate) VALUES(1,1,'2014-01-01');  
WHILE @rc * 2 <= @max  
BEGIN  
    INSERT INTO dbo.tradelog(id,productID,tradedate) SELECT id + @rc,id + @rc+1,DATEADD(mi,id,tradedate) FROM dbo.tradelog;  
    SET @rc = @rc * 2;  
END  
INSERT INTO dbo.tradelog (id,productID,tradedate)
SELECT id + @rc,id + @rc+1,DATEADD(mi,id,tradedate) FROM dbo.tradelog WHERE id + @rc <= @max;  
go  
 
--查看分区表的现状
;with cte as
  (select 
    object_id
    ,OBJECT_NAME(i.object_id) tableName
    ,i.index_id
    ,dds.partition_scheme_id
    ,dds.destination_id as partition_number
    ,fg.groupid
    ,fg.groupname
    ,f.fileid
    ,f.name
    ,f.filename
    --,p.partition_id
    --,p.rows
   from sys.destination_data_spaces dds,sys.indexes i,sys.sysfilegroups fg,sys.sysfiles f
   where dds.partition_scheme_id=i.data_space_id
    and dds.data_space_id=fg.groupid
    and fg.groupid=f.groupid
    )
,cte1 as(
  select
    ps.data_space_id as partition_scheme_id
    ,ps.name partiton_schemes_name
    ,pf.name partition_function_name
    ,pf.function_id
    --,prv.value AS BoundaryValue
  from sys.partition_schemes ps ,sys.partition_functions pf
  where ps.function_id=pf.function_id
    --and pf.function_id=prv.function_id
)
select cte.tableName,cte.groupname,cte.name,cte.filename
  ,cte.partition_number,cte1.partiton_schemes_name,cte1.partition_function_name,p.rows
  ,prv.boundary_id,prv.value BoundaryValue
from cte
inner join cte1  on cte.partition_scheme_id=cte1 .partition_scheme_id
left join sys.partition_range_values prv on cte1.function_id=prv.function_id and cte.partition_number=prv.boundary_id
left join sys.partitions  p on cte.object_id=p.object_id and cte.index_id=p.index_id and cte.partition_number=p.partition_number
 
where 
   cte.object_id=OBJECT_ID('dbo.tradelog','U')

五. 合并分区

减少分区的方法是将两个分区的边界“合并”成一个。减少分区操作将重新填充一个分区而不对另一个分区进行分配。

    --分区合并
    alter partition function f_TestDate()
    merge range('2018-07-01 0:00:00')

分区合并的数据移动方向刚好和增加分区的方向相反，分区合并后，将合并分界点的后一个分区数据移动到前一个分区的文件中。这个结论在数据自动归档中将极为有用，因为数据归档最后一步是将合并后的空文件、文件组回收，这样就可以确定回收的文件名

六. 分区数据移到普通表

create table tradelog_partition1
(
  ID int,
  productID int,
  tradedate datetime
) on test2015
alter table tradelog switch partition 1 to tradelog_partition1

把分区表的某个分区数据转移到普通表，要求

1.普通表必须和对应的分区在同一个文件组下

2.普通表和分区表结构相同，包括字段、数据类型、数据长度、索引等

分区表上在tradedate上有聚集索引，但普通表tradelog_partition1上没有建聚集索引，执行上述脚本就会报如下错误：

把分区表的某个分区数据转移到普通表，要求

1. 普通表必须和对应的分区在同一个文件组下

2. 普通表和分区表结构相同，包括字段、数据类型、数据长度、索引等

分区表上在tradedate上有聚集索引，但普通表tradelog_partition1上没有建聚集索引，执行上述脚本就会报如下错误：

七. 普通表数据移到某一分区

alter table tradelog_partition1 switch to tradelog partition 1

在tradelog_partition1的tradedate上创建聚集索引，重新执行上面的脚本，又报了如下错误

What happen？？这是因为分区1上有CHECK日期要在2014到2016之间，而tradelog_partition1上没有这个检查，所以，在表上加上如下检查：

ALTER TABLE dbo.tradelog_partition1
    ADD CONSTRAINT TradeDate_Switch_CHECK CHECK 
      (TradeDate >= CONVERT(DATE,'2014-01-01') AND TradeDate < CONVERT(DATE,'2016-01-01')
            AND TradeDate IS NOT NULL);
GO

再执行移动数据，数据又重新移回到分区1中

原文链接：https://blog.csdn.net/zhoujunah/article/details/79744590

只是大自然搬运工，略加修改

END

sql server 大数据

文章转载自ellipse数据库技术，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。