[转]SQL Server表分区

本文转自:http://www.cnblogs.com/knowledgesea/p/3696912.html

 

哎呀是表分区

一般情形下,我们建数据库表时,表数据都存放于一个文件里。

然要是是分区表的话,表数据就会按部就班卿指定的规则分放到不同之公文里,把一个怪的数据文件拆分为多单稍文件,还好将这些多少文件在不同的磁盘下由多个cpu进行处理。这样文件之高低就拆分而减弱多少,还取硬件系统的增进,自然对咱们操作数据是大妈好之。

据此大数据量的数据表,对分区的用还是必需之,因为它可增长select效率,还可针对历史数据经行区分存档等。但是数据量少的多寡就不要凑这个热闹呀,因为表分区会针对数据库有不必要之支出,除啦性能还会多实现目标的管理费用和复杂。

跟着做,分区如此简约

预先跟着做一个分区表(分为11个分区),去除神秘的面罩,然后我们再逐一一碰上破各个要碰要。

分区是设管一个说明数据拆分为若干子集合,也不怕是把管一个数据文件拆分及差不多独数据文件中,然而这些文件之存放可以寄一个文件组或及时多只文件组,由于大多独文件组可以加强数据库的顾并发量,还得把不同之分区配置到不同的磁盘中提高效率,所以创建时提议分区跟文件组个数相同。

1.创文件组

好点击数据库属性在文件组里面长

T-sql语法:

alter database <数据库名> add filegroup <文件组名>

SQL Server 1😉

---创建数据库文件组
alter database testSplit add filegroup ByIdGroup1
alter database testSplit add filegroup ByIdGroup2
alter database testSplit add filegroup ByIdGroup3
alter database testSplit add filegroup ByIdGroup4
alter database testSplit add filegroup ByIdGroup5
alter database testSplit add filegroup ByIdGroup6
alter database testSplit add filegroup ByIdGroup7
alter database testSplit add filegroup ByIdGroup8
alter database testSplit add filegroup ByIdGroup9
alter database testSplit add filegroup ByIdGroup10

SQL Server 2😉

2.创立数据文件到文件组里面

足点击数据库属性在文件之中长

T-sql语法:

alter database <数据库名称> add file <数据标识> to filegroup <文件组名称>

--<数据标识> (name:文件名,fliename:物理路径文件名,size:文件初始大小kb/mb/gb/tb,filegrowth:文件自动增量kb/mb/gb/tb/%,maxsize:文件可以增加到的最大大小kb/mb/gb/tb/unlimited)

SQL Server 3😉

alter database testSplit add file 
(name=N'ById1',filename=N'J:\Work\数据库\data\ById1.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup1
alter database testSplit add file 
(name=N'ById2',filename=N'J:\Work\数据库\data\ById2.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup2
alter database testSplit add file 
(name=N'ById3',filename=N'J:\Work\数据库\data\ById3.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup3
alter database testSplit add file 
(name=N'ById4',filename=N'J:\Work\数据库\data\ById4.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup4
alter database testSplit add file 
(name=N'ById5',filename=N'J:\Work\数据库\data\ById5.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup5
alter database testSplit add file 
(name=N'ById6',filename=N'J:\Work\数据库\data\ById6.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup6
alter database testSplit add file 
(name=N'ById7',filename=N'J:\Work\数据库\data\ById7.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup7
alter database testSplit add file 
(name=N'ById8',filename=N'J:\Work\数据库\data\ById8.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup8
alter database testSplit add file 
(name=N'ById9',filename=N'J:\Work\数据库\data\ById9.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup9
alter database testSplit add file 
(name=N'ById10',filename=N'J:\Work\数据库\data\ById10.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup10

SQL Server 4😉

推行好后,右键数据库看文件组跟文件中凡是匪是基本上下呀这些文件组跟文件。

3.以引导创建分区表

右键到要分区的表—
>> 存储 — >> 创建分区 — >>显示为导视图 —
>> 下同样步 — >> 下同样步。。

SQL Server 5

这边举例说下抉择列的意思:

假使你拣的凡int类型的排:那么您的分区可以指定为1–100W是一个分区,100W–200W是一个分区….

万一你拣的是datatime类型:那么你的分区可以指定为:2014-01-01–2014-01-31一个分区,2014-02-01–2014-02-28一个分区…

基于这样的排列数据规则划分,那么在挺区间的数,在插入数据库时便吃针对十分分区存储下来。

 

自我这里选用orderid
int类型 — >> 下一样步 — >>

SQL Server 6

SQL Server 7

SQL Server 8

错误边界右边界:就是将临界值划分给上一个分区还是生一个分区。一个仅次于如泣如诉,一个低于等于号。

下一场下一样步下一样步最后你会取得分区函数和分区方案。

SQL Server 9😉

USE [testSplit]
GO
BEGIN TRANSACTION

--创建分区函数
CREATE PARTITION FUNCTION [bgPartitionFun](int) AS RANGE LEFT FOR VALUES (N'1000000', N'2000000', N'3000000', N'4000000', N'5000000', N'6000000', N'7000000', N'8000000', N'9000000', N'10000000')

--创建分区方案
CREATE PARTITION SCHEME [bgPartitionSchema] AS PARTITION [bgPartitionFun] TO ([PRIMARY], [ByIdGroup1], [ByIdGroup2], [ByIdGroup3], [ByIdGroup4], [ByIdGroup5], [ByIdGroup6], [ByIdGroup7], [ByIdGroup8], [ByIdGroup9], [ByIdGroup10])

--创建分区索引
CREATE CLUSTERED INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165] ON [dbo].[BigOrder] 
(
    [OrderId]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [bgPartitionSchema]([OrderId])

--删除分区索引
DROP INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165] ON [dbo].[BigOrder] WITH ( ONLINE = OFF )

COMMIT TRANSACTION

SQL Server 10😉

推行方向导生成的言辞。分区完成。。

4.秀转眼进度。

率先我于表中插入啦1千万实践数据。给表分啦11独分区。前十个分区里面一个是100W长条数。。

SQL Server 11

说两句:

看得出反常现象,扫描次数及逻辑读取次数都是不管分区表的2加倍之多,但询问速度也是抢啊不少哟。这就是分区的神奇的处在呀,所以一旦相信这世界任何皆有或。

分区函数,分区方案,分区表,分区索引

1.分区函数

指定分根据区列(依据列唯一),分区数据范围规则,分区数量,然后将数据映射到同组分区上。

开创语法: 

create partition function 分区函数名(<分区列类型>) as range [left/right] 
for values (每个分区的边界值,....) 

--创建分区函数
CREATE PARTITION FUNCTION [bgPartitionFun](int) AS RANGE LEFT FOR VALUES (N'1000000', N'2000000', N'3000000', N'4000000', N'5000000', N'6000000', N'7000000', N'8000000', N'9000000', N'10000000')

然而,分区函数只定义了分区的不二法门,此方式具体用当哪个表的那么同样排列上,则用以开创表要索引是点名。 

删除语法:

--删除分区语法
drop partition function <分区函数名>

--删除分区函数 bgPartitionFun
drop partition function bgPartitionFun

亟待小心的凡,只有无起以及分区方案受到之分区函数才能够吃剔除。

2.分区方案

指定分区对应的文件组。

创语法: 

--创建分区方案语法
create partition scheme <分区方案名称> as partition <分区函数名称> [all]to (文件组名称,....) 

--创建分区方案,所有分区在一个组里面
CREATE PARTITION SCHEME [bgPartitionSchema] AS PARTITION [bgPartitionFun] TO ([ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1])

分区函数必须关联分区方案才会使得,然而分区方案指定的文本组数要跟分区数量一样,哪怕多单分区存放于一个文本组中。

删去语法:

--删除分区方案语法
drop partition scheme<分区方案名称>

--删除分区方案 bgPartitionSchema
drop partition scheme bgPartitionSchema1

一味生没有产生分区表,或索引使用该分区方案是,才能够针对那除去。

3.分区表

开创语法:

--创建分区表语法
create table <表名> (
  <列定义>
)on<分区方案名>(分区列名)

SQL Server 12😉

--创建分区表
create table BigOrder (
   OrderId              int                  identity,
   orderNum             varchar(30)          not null,
   OrderStatus          int                  not null default 0,
   OrderPayStatus       int                  not null default 0,
   UserId               varchar(40)          not null,
   CreateDate           datetime             null default getdate(),
   Mark                 nvarchar(300)        null
)on bgPartitionSchema(OrderId)

SQL Server 13😉

假设在说明中创造主键或唯一索引,则分区依据列必须也该列。

4.分区索引

缔造语法: 

--创建分区索引语法
create <索引分类> index <索引名称> 
on <表名>(列名)
on <分区方案名>(分区依据列名)

--创建分区索引
CREATE CLUSTERED INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165] ON [dbo].[BigOrder] 
(
    [OrderId]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [bgPartitionSchema]([OrderId])

用分区索引查询,可以避多单cpu操作多单磁盘时生的撞。

分区标明细信息

此的语法,我就算不写啊,自己扣报告句子分析吧。简单的不可开交。。

1.翻看分区依据列的指定值所在的分区 

--查询分区依据列为10000014的数据在哪个分区上
select $partition.bgPartitionFun(2000000)  --返回值是2,表示此值存在第2个分区 

2.查分区表中,每个非空分区存在的行数

--查看分区表中,每个非空分区存在的行数
select $partition.bgPartitionFun(orderid) as partitionNum,count(*) as recordCount
from bigorder
group by  $partition.bgPartitionFun(orderid)

SQL Server 14

3.翻指定分区中之数码记录 

---查看指定分区中的数据记录
select * from bigorder where $partition.bgPartitionFun(orderid)=2

结果:数据从1000001开始到200W结束

分区的拆分和合和数移动

 1.拆分分区

在分区函数中新增一个境界值,即可将一个分区变为2单。

--分区拆分
alter partition function bgPartitionFun()
split range(N'1500000')  --将第二个分区拆为2个分区

在意:如果分区函数已经指定了分区方案,则分区数得同分区方案遭指定的文书组个数保持对应一致。

 2.联结分区

 与拆分分区相反,去除一个边界值即可。

--合并分区
alter partition function bgPartitionFun()
merge range(N'1500000')  --将第二第三分区合并

3.分区中之数移动

 你可能会逢这样的需要,将普通表数据复制到分区表中,或者将分区表中的多寡复制到日常表中。

 那么移动多少立马点儿只说明,则必须满足下面的渴求。

  • 配段数量同样,对承诺位置的字段相同
  • 如出一辙位置的字段要起一样之性,相同之花色。
  • 少单说明在一个文书组中

1.创办表时指定文件组

--创建表
create table <表名> (
  <列定义>
)on <文件组名>

2.由分区表中复制数据及普通表

--将bigorder分区表中的第一分区数据复制到普通表中
alter table bigorder switch partition 1 to <普通表名>

3.起一般标中复制数据及分区表中

这里而留意的是要事先以分区表中的目录删除,即便普通表中是跟分区表中相同之目。

--将普通表中的数据复制到bigorder分区表中的第一分区
alter table <普通表名> switch to bigorder partition 1 

分区视图

分区视图是预先成立涵盖字段约束的相同表,而约不同,例如,第一个表的id约束为0–100W,第二说明SQL Server也101万及200万…..顺序类推。

开创了一系列之申后,用union
all 连接起来创建一个视图,这个视图就形成啦分区视同。

挺简单的,这里我重点是说分区表,就未说分区视图啦。。

 查看数据库分区信息

SQL Server 15😉

SELECT OBJECT_NAME(p.object_id) AS ObjectName,
      i.name                   AS IndexName,
      p.index_id               AS IndexID,
      ds.name                  AS PartitionScheme,   
      p.partition_number       AS PartitionNumber,
      fg.name                  AS FileGroupName,
      prv_left.value           AS LowerBoundaryValue,
      prv_right.value          AS UpperBoundaryValue,
      CASE pf.boundary_value_on_right
            WHEN 1 THEN 'RIGHT'
            ELSE 'LEFT' END    AS Range,
      p.rows AS Rows
FROM sys.partitions                  AS p
JOIN sys.indexes                     AS i
      ON i.object_id = p.object_id
      AND i.index_id = p.index_id
JOIN sys.data_spaces                 AS ds
      ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes           AS ps
      ON ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions         AS pf
      ON pf.function_id = ps.function_id
JOIN sys.destination_data_spaces     AS dds2
      ON dds2.partition_scheme_id = ps.data_space_id 
      AND dds2.destination_id = p.partition_number
JOIN sys.filegroups                  AS fg
      ON fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left
      ON ps.function_id = prv_left.function_id
      AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right
      ON ps.function_id = prv_right.function_id
      AND prv_right.boundary_id = p.partition_number 
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
UNION ALL
SELECT
      OBJECT_NAME(p.object_id)    AS ObjectName,
      i.name                      AS IndexName,
      p.index_id                  AS IndexID,
      NULL                        AS PartitionScheme,
      p.partition_number          AS PartitionNumber,
      fg.name                     AS FileGroupName,  
      NULL                        AS LowerBoundaryValue,
      NULL                        AS UpperBoundaryValue,
      NULL                        AS Boundary, 
      p.rows                      AS Rows
FROM sys.partitions     AS p
JOIN sys.indexes        AS i
      ON i.object_id = p.object_id
      AND i.index_id = p.index_id
JOIN sys.data_spaces    AS ds
      ON ds.data_space_id = i.data_space_id
JOIN sys.filegroups           AS fg
      ON fg.data_space_id = i.data_space_id
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
ORDER BY
      ObjectName,
      IndexID,
      PartitionNumber

SQL Server 16😉

相关文章