数据分区实例概述

1，分区作用：将数据分放到多个物理磁盘上，利用并行，多个磁盘同时吞吐以提升IO能力，同时每个分区数据量小，也减少了数据的扫描，提升了命中速度。实际上表逻辑上并未拆分，只是分散存储于不同的物理文件上，相当于把一张表大数据无限极细化到多张表上，多个驱动上，但是访问时却还是一样的访问，因为本身并未新建任何表。
注：即使放到一块磁盘上，因每个分区数据量小，扫描范围减少，也能提高速度。
表分区方法：1，按时间分区：好处，查询速度快。坏处：因当前数据一直写到一块分区上，故写不快。
                       2，HASH分区：数据均匀的分散到各分区，好处：因当前数据写到不同分区，故写快。但在查询方面，要联合多个分区，故查询不快。
注：分区分为硬件与软件分区，此页是软件分区。硬件分区指用磁盘阵列的方式分区，硬件负责将数据将到不同磁盘上，查询与写入速度都会提高。
操作：指定数据要分区的位置-创建文件组及对应文件， 数据以何种规则适移到分区上-创建分区函数， 进行数据迁移。
 
操作：指定数据要分区的位置-创建文件组及对应文件， 数据以何种规则适移到分区上-创建分区函数， 进行数据迁移。
步骤：
/*1,指定数据存放位置:增加文件组,并指定文件存放位置*/
ALTER DATABASE BRM_LVJIAN
ADD FILEGROUP [gf_bdorder_50]
ALTER DATABASE BRM_LVJIAN
ADD FILEGROUP [gf_bdorder_100]
ALTER DATABASE BRM_LVJIAN
ADD FILEGROUP [gf_bdorder_150]
 
ALTER DATABASE BRM_LVJIAN
ADD FILE ( NAME = N'gf_bdorder_50',
FILENAME = N'g:\Mssql_Filegroup\bdorder_50.ndf' , SIZE = 300MB , FILEGROWTH = 10% )
TO FILEGROUP [gf_bdorder_50]
 
ALTER DATABASE BRM_LVJIAN
ADD FILE ( NAME = N'gf_bdorder_100',
FILENAME = N'I:\Mssql_Filegroup\bdorder_100.ndf' , SIZE = 300MB , FILEGROWTH = 10% )
TO FILEGROUP [gf_bdorder_100]
 
ALTER DATABASE BRM_LVJIAN
ADD FILE ( NAME = N'gf_bdorder_150',
FILENAME = N'I:\Mssql_Filegroup\bdorder_150.ndf' , SIZE = 300MB , FILEGROWTH = 10% )
TO FILEGROUP [gf_bdorder_150]
 
/*2,指定数据如何存放:创建分区函数*/
 
--LEFT,RIGHT指定"="是在最左边还是右边
--LEFT: <=1, 1<10<=20, >20
--RIGHT: <1,1<=10<20, >=20
CREATE PARTITION FUNCTION [partitionFunc_bdOrder] (int)
AS RANGE LEFT FOR VALUES ('500000','1000000')
 
select max(id) from bdorder
 
/*3,数据的迁移,应用分区位置与函数,为了方便,将二者合二唯一,提出分区架构的概念*/
CREATE PARTITION SCHEME [partitionScheme_bdOrder]
AS PARTITION [partitionFunc_bdOrder]
TO ([PRIMARY],gf_bdorder_50,gf_bdorder_100,gf_bdorder_150)
 
--数据迁移到分区
ALTER TABLE dbo.bdOrder DROP CONSTRAINT PK_BDOrder
ALTER TABLE  dbo.bdOrder add CONSTRAINT PK_BDOrder PRIMARY KEY CLUSTERED (ID)
ON [partitionScheme_bdOrder](ID)
 
--恢复成原来的默认状态，因原来的分区架构是<span style="color: #ff0000;">PRIMARY</span>,故修改表即可，如下：
ALTER TABLE dbo.bdorder DROP CONSTRAINT PK_BDOrder
ALTER TABLE  dbo.bdorder add CONSTRAINT PK_BDOrder PRIMARY KEY CLUSTERED (ID)
ON [PRIMARY]
 
/*查询*/
--分区
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('dbo.bdOrder')
 
--数据所在分区
SELECT  $PARTITION.[partitionFunc_bdOrder](1000000)
 
SELECT top 20 id,orderno,orderdate,$PARTITION.[partitionFunc_bdOrder](id)  FROM bdOrder
ORDER BY newid()
 
--数据分布
 
SELECT $PARTITION.partitionFunc_bdOrder(id) AS Partition_num,
MIN(Id) AS Min_value,MAX(Id) AS Max_value,COUNT(1) AS Record_num
FROM dbo.[bdorder]
GROUP BY $PARTITION.partitionFunc_bdOrder(id)
ORDER BY $PARTITION.partitionFunc_bdOrder(id)
 
select * from
sys.partition_range_values
 
/*若数据量增大，需要增加分区，扩大范围，操作如下*/
操作方法：增加分区，增加范围
 
ALTER DATABASE [D] ADD FILEGROUP [GF2]
ALTER DATABASE [WSBOOK] ADD FILE ( NAME = N'GF2', FILENAME = N'E:\E\E1\DGF2.ndf' , SIZE = 5MB , FILEGROWTH = 10% )
TO FILEGROUP [GF2]
 
为架构添加范围
ALTER PARTITION SCHEME [D_PARTITION_SHEME]
NEXT USED GF2
这句话就是让下一个分区使用和现在已经存在的分区GF2分区中，
 
2.添加一个范围
ALTER PARTITION FUNCTION [D_PARTITIONFUNC]()
SPLIT RANGE ('700000')
 
可以合并一个范围，其实就是<span style="color: #ff0000;">删除分区</span>，如MERGE RANGE(要删除的原分区界点)
 
ALTER PARTITION FUNCTION [D_PARTITIONFUNC]() MERGE RANGE ('400000')
3.查询分区分布
 
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('dbo.B')
 
4.删除分区
DROP PARTITION SCHEME [D_PARTITION_SHEME]
DROP PARTITION FUNCTION [D_PARTITIONFUNC]