SqlServer数据库分区分表实例分享(有详细代码和解释)

来源：cnblogs　　作者：我家的小萌珊　　时间：2019/7/8 8:46:32　　对本文有异议

数据库单表数据量太大可能会导致数据库的查询速度大大下降（感觉都是千万级以上的数据表了），可以采取分区分表将大表分为小表解决（当然这只是其中一种方法），比如数据按月、按年分表，最后可以使用视图将小表重新并为总的虚拟表，其实并不影响上层程序的使用（程序也许都不知道分表了）。

主要步骤：

1、新建文件组，将数据表文件保存路径指向相应文件组（应将文件组和文件放入不同的磁盘中，甚至不同服务器形成分布式数据库，因为数据的读取瓶颈很大程度在于磁盘的的读写速度，多个磁盘存放一个表可以负载均衡）

2、设置分区函数（声明分区的标准）

3、设置分区方案（即哪些区域使用哪个分区函数，形成完整的分区方案）

4、给新表或现有表设置分区方案

5、建立视图

详细步骤（看需求可选）：

一、数据库状态备份和恢复

USE master
-- 备份
BACKUP DATABASE AdventureWorks
    TO DISK = 'AdventureWorks.bak'
    WITH FORMAT
 
---- 恢复
RESTORE DATABASE AdventureWorks
    FROM DISK = 'AdventureWorks.bak'
    WITH REPLACE
GO

二、文件组和文件操作

添加文件组

USE [master]
GO
ALTER DATABASE ZHH ADD FILEGROUP [文件组名称]
Go

添加文件并把其指向指定文件组

USE master;
GO
ALTER DATABASE 数据库名
ADD FILE(
NAME=N'文件名',
FILENAME='存放路径', //如：E:\201109.NDF（精确到文件名）文件组存放与不同磁盘可以提高IO读写效率（多个磁头并发）
SIZE=3MB,
MAXSIZE=100MB,
FILEGROWTH=5MB
)TO FILEGROUP [文件组名]
Go

修改文件（可选）

USE master;
GO
ALTER DATABASE 数据库名
MODIFY FILE
(NAME = 文件名,
SIZE = 20MB);   //可以修改所有属性，列举即可
GO

删除文件（可选）

ALTER DATABASE 数据库名 REMOVE FILE [文件组名]

三、分区函数和分区方案

分区函数

用于规范如何分区的标准，如已哪列进行为标准分区、分区的方式（按时间、ID等）、分区的具体界限（一般来说，界限指标数要比分区数少1，一刀则有两段）

USE 数据库名   
GO
CREATE PARTITION FUNCTION 分区函数名 (指标列的数据类型)   //如：datetime、int
AS RANGE RIGHT   //右边界切分，默认为LEFT
FOR VALUES (划分界限)  //如时间划分('2003/01/01', '2004/01/01')，两个时间界限可划分出三个分区
GO

分区方案

用于将已经建立好的分区函数组织成完整的方案，为每个分区分配存储位置

Use 数据库名
go
create  partition  scheme 分区方案名
as partition  分区函数
to(文件组1,文件组2,文件组3,...) //注意分区数要与实际分区一致
go

在原有的基础上添加分区（可选）

use 数据库名
go
alter partition scheme ps_OrderDate  next used [FG4]    //修改分区方案ps_OrderDate，定义新新分区使用FG4文件组
alter partition function pf_OrderDate() split range('2005/01/01')  //修改分区函数pf_OrderDate，在末尾添加界限'2005/01/01'
go

为现有表设置分区方案（可选）

//为AutoBench表的InsertTime列创建新聚集索引，并绑定Scheme_DateTime分区方案
CREATE CLUSTERED INDEX IX_CreateDate ON AutoBench (InsertTime)
ON Scheme_DateTime (InsertTime)

注：如原来主键有聚众索引要将其改为非聚集索引，才可添加新聚众索引

//删除原主键上的聚集索引PK_Product
ALTER TABLE Product DROP CONSTRAINT PK_Product
//重新创建主键非聚集索引PK_Product
ALTER TABLE Product ADD CONSTRAINT PK_Product PRIMARY KEY NONCLUSTERED (ProductID ASC)

上面语句也可直接在索引属性中将聚集改为非聚集

为新建表设置分区方案（可选）

//创建表格Order,并设置Scheme_DateTime分区方案，指标列为OrderDate
CREATE TABLE [Order]
(
    OrderID INT IDENTITY(1,1) NOT NULL,
    UserID INT NOT NULL,
    TotalAmount DECIMAL(18,2) NULL,
    OrderDate DATETIME NOT NULL
) ON Scheme_DateTime (OrderDate)
查询分区数据

四、其他操作

查询分区数据

$partition函数--为任何指定的分区函数返回分区号，一组分区列值将映射到该分区号中

语法： [ database_name. ] $PARTITION.partition_function_name(expression)

参数： database_name 包含分区函数的数据库的名称。

partition_function_name 对其应用一组分区列值的任何现有分区函数的名称。

expression 其数据类型必须匹配或可隐式转换为其对应分区列数据类型的表达式。 expression 也可以是当前参与partition_function_name 的分区列的名称。

返回类型： int (分区号)

//筛选使用Function_DateTime作为分区函数的AutoBench表，以InsertTime作为指标列的第二个分区的所有数据
select * from AutoBench WHERE $PARTITION.Function_DateTime(InsertTime) = 2

合并分区

//删除Sales数据库下的分区函数pf_OrderDate中的'2003/01/01'界限，以次界限划分的两个分区合并，分区号一次减1
use Sales
go
alter partition function pf_OrderDate() merge range('2003/01/01')
go

查看系统视图

select * from sys.partition_functions   //分区函数
select * from sys.partition_range_values    //分区方案
select * from sys.partition_schemes     //边界值点

五、自动分区

可以采用SQL Server代理中的作业定期自动执行分区脚本，实现自动分区（如每月结束自动执行按月分区的操作）

自动分区测试脚本

DECLARE 
    @fileGroupName VARCHAR(20),    --文件组名（格式为:FG+@Month）
    @fileName VARCHAR(20),    --文件名（格式为:F+@Month）
    @filePath VARCHAR(100),        --文件存放路径（格式为:存放目录路径+@fileName.ndf）
    @dataBaseName VARCHAR(20),    --数据库名
    @Month VARCHAR(10),        --当前时间年月（格式为:yyyymm）
    @schemeName VARCHAR(20),    --分区方案名
    @partFunctionName VARCHAR(20),    --分区函数名
    @limit VARCHAR(10)    --分区界限（以时间分区则为时间字符串，格式为:mm/dd/yyyy)
SET @fileGroupName='FG201805'
SET @Month=CONVERT(varchar(10),GETDATE(),112)
SET @fileName=N'F201805'
SET @filePath='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\F201805.ndf'
SET @dataBaseName='Chassis'
SET @schemeName='Scheme_DateTime'
SET @partFunctionName='Function_DateTime'
SET @limit=CONVERT(varchar(10),GETDATE(),101) 
--语句要指明需要操作的数据库
if exists(select * from Chassis.sys.filegroups where name=@fileGroupName)
    begin
        print '文件组存在,不需添加'
    end
else
    begin
        exec('ALTER DATABASE '+@dataBaseName+' ADD FILEGROUP ['+@fileGroupName+']')
        print '新增文件组'+@fileGroupName
    end
if exists(select * from Chassis.sys.database_files where [state]=0 and (name=@fileName or physical_name=@filePath))
    begin
        print 'ndf文件存在,不需添加'
    end
else
    begin
        exec('ALTER DATABASE '+@dataBaseName+' ADD FILE(NAME ='''+@fileName+''',FILENAME = '''+@filePath+''')TO FILEGROUP ['+@fileGroupName+']')
        print '添加文件'+@fileName+'至文件组'+@fileGroupName
    end
if exists(select * from sys.partition_schemes where name=@schemeName)
    begin
        exec('alter partition scheme '+@schemeName+' next used ['+@fileGroupName+']')
        print '修改分区方案，指定下一分区的文件组'
    end
else
    begin
        print '分区方案不存在'
    end
if exists(select * from sys.partition_range_values where function_id=(select function_id from sys.partition_functions where name=@partFunctionName))
    begin
        if exists(select * from sys.partition_range_values where function_id=(select function_id from sys.partition_schemes where name='Scheme_DateTime') and value=CONVERT(datetime,''+@limit+'',101))
            begin
                print '界限已存在'
            end
        else
            begin
                exec('alter partition function '+@partFunctionName+'() split range('''+@limit+''')')
                print '修改分区函数，添加划分界限为：'+@limit
            end
    end
else
    begin
        print '分区函数不存在'
    end