SQL Server分区表实践

数据库开发, SQL Server
目录

建立分区表的步骤：

新建文件组（FileGroups）

新建用于承载具体分区的文件组，数据库默认有只有一个Primary文件组。
也可以在单个文件中建立多个分区。

USE [master]
GO
ALTER DATABASE [TestDB_20170705] ADD FILEGROUP [TestDB_FG_1]
GO
ALTER DATABASE [TestDB_20170705] ADD FILEGROUP [TestDB_FG_2]
GO
-- 增加文件组-物理文件
ALTER DATABASE [TestDB_20170705]   
ADD FILE   
(  
    NAME = TestDB_FG_1,  
    FILENAME = 'E:\DB-File\TestDB_FG_1.ndf',  
    SIZE = 5MB,  
    FILEGROWTH = 5MB  
)  
TO FILEGROUP [TestDB_FG_1]; 
GO
ALTER DATABASE [TestDB_20170705]   
ADD FILE   
(  
    NAME = TestDB_FG_2,  
    FILENAME = 'E:\DB-File\TestDB_FG_2.ndf',  
    SIZE = 5MB,  
    FILEGROWTH = 5MB  
)  
TO FILEGROUP [TestDB_FG_2]; 
GO

创建 分区函数（Partition Function）

分区函数用于决定分区表中数据如何对应到具体的分区

-- 代表以20000为分界点，实际也可以声明多个分界点
-- Left表示 <= 20000,即20000存储在分界点左边分区
CREATE PARTITION FUNCTION MyPF_Test(INT)
AS RANGE LEFT FOR VALUES (20000)
GO

创建 分区方案（Partition Scheme）

根据分区函数将表中数据Map到具体的文件组

-- 分区函数有N个分界点，则引用它的分区方案有N+1个文件组
-- 分区列<=20000的数据放在默认的Primary文件组，其余的放在TestDB_FG_1文件组
CREATE PARTITION SCHEME myPS_Test
AS PARTITION MyPF_Test TO ([PRIMARY],TestDB_FG_1)
GO 

-- 若为单文件多个分区
CREATE PARTITION SCHEME myPS_Test2
AS PARTITION MyPF_Test All TO ([PRIMARY])
GO

创建 分区表（Partition Table）

  • 新建分区表

在创建表时定义分区方案，这个根据EID的值进行分区

CREATE TABLE TestEmp
(
EID INT,
Name NVARCHAR(200),
JoinDate DATETIME
) 
ON MyPS_Test(EID)
  • 将非分区表转换为分区表
    可以先备份原表数据，Drop原表，再将数据插回来。
    若不想这么麻烦，可以直接重建索引来实现。
    若表存在聚集索引，则分区列必须为聚焦索引的一部分。
    下面将Employee（以EID为主键）表转换为分区表：
-- 移除主键约束(SQL会同步移除聚焦索引)
ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [PK_Employee] 
GO
-- 重新创建分区索引
ALTER TABLE [dbo].[Employee] ADD CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([EID]) ON myPS_Test(EID)
GO

分区表元数据查询

-- 分区表数据分布情况
SELECT  OBJECT_NAME(i.object_id) AS ObjectName
       ,c.name AS PartitioningColumn
       ,CONVERT(VARCHAR(50), ps.name) AS partition_scheme
       ,p.partition_number
       ,CONVERT(VARCHAR(10), ds2.name) AS filegroup
       ,CONVERT(VARCHAR(19), ISNULL(v.value, ''), 120) AS range_boundary
       ,p.rows
       ,df.physical_name AS DatabaseFileName
FROM    sys.indexes i
        JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
        JOIN sys.destination_data_spaces dds ON ps.data_space_id = dds.partition_scheme_id
        JOIN sys.data_spaces ds2 ON dds.data_space_id = ds2.data_space_id
        JOIN sys.partitions p ON dds.destination_id = p.partition_number AND p.object_id = i.object_id AND p.index_id = i.index_id
        JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
        JOIN sys.database_files df ON df.data_space_id = ds2.data_space_id
        JOIN sys.index_columns AS ic ON ic.[object_id] = i.[object_id] AND ic.index_id = i.index_id AND ic.partition_ordinal >= 1 
        JOIN sys.columns AS c ON i.[object_id] = c.[object_id] AND ic.column_id = c.column_id
        LEFT JOIN sys.partition_range_values v ON pf.function_id = v.function_id AND v.boundary_id = p.partition_number - pf.boundary_value_on_right
WHERE   i.index_id IN (0, 1)
ORDER BY ObjectName
       ,partition_number