SqlServer2019数据库分表

场景：MSSql数据库中某个表数据量大，需要进行分表操作

主备说明：摘抄：https://blog.csdn.net/songjuntao8/article/details/123215567

分库

垂直切分(纵向切分)

比如说一个电商平台，里面包含了订单系统，物流系统，仓储系统等，原先是用的同一个数据库，现在我们可以设计成每个系统都有自己的数据库服务器，不公用同一个数据库，不同系统之间的数据可以通过API调用来，这样压力就会小很多。这可以理解为垂直切分。

水平切分(横向切分)

比如订单系统中数据量很大，导致了数据库的性能问题，我们可以对订单系统数据库进行水平切分。水平分库之后数据库的结构是完全一致的，只是存储的数据不一样而已，比如说一个存储去年的，一个存储今年，，，，可以按照时间来进行切分，如果业务要求查询的数据中一部分在去年，一部分在今年，那就需要程序员在程序中进行处理了。当然实际情况根据实际业务，这里只是做一个假设。还可以按照区域进行切分，比如外卖平台，A数据库存储A区域的数据，B数据库存储B区域的数据。

还可以简单总结一下就是垂直切分就是把不同的表组合成不同的库，水平分库就是将相同的表分成不同的库

分表

垂直分表(纵向分表)

某些字段的数据不常用，但是又包含在这张表中，而且这些字段很损耗性能(因为存在IO压力)；就可以进行垂直切分，一个表存储这个很损耗性能或者不常用的字段，其他比较常用的字段存储在另一个表，两个表可以直接通过两个主键关联（建议使用Guid作为主键）；也可以通过主外键关系来关联！

水平分表

比如说一个订单表包含的数据量非常大；有一些数据（两年前的订单数据）也存储在这张表；就可以水平切分，多个表的表结构是完全一致，但是数据不一样，每个表记录不同时间段的数据(这是以时间为维度进行切分的)；比如房产系统（外卖系统），这都可以以区域为维度来进行切分，比如不同的表存储不同区域的数据；还会可以通过指定的算法来进行表的水品切分--把数据均匀分配，慎用。

1.基础数据（新建一个Test数据库和User数据表，往里头循环插入数据,这个我是要准备按照CloseDate去分割数据的）

DECLARE @num INT = 1

WHILE(@num < 1000)
BEGIN
    INSERT dbo.[User]
    (
        Name,
        DellreceiveDate,
        CloseDate
    )
    VALUES
    (   '张三' + CONVERT(VARCHAR(50),@num), -- Name - nchar(10)
        DATEADD(DAY,@num,'2019-01-01'), -- DellreceiveDate - date
        DATEADD(DAY,@num,'2020-06-01')  -- CloseDate - date
        )
    SET @num = @num + 1
END

方式一：1.SQL创建（这个弄到后面，我发现由于表已经创建了，执行报错，然后还可以进行使徒的创建-这个比较稳，还有计划任务）

2.SQL语句（注：这里后面）

--创建文件组
ALTER DATABASE TEST ADD FILEGROUP TEST_User_FileGroup2020
ALTER DATABASE TEST ADD FILEGROUP TEST_User_FileGroup2021
ALTER DATABASE TEST ADD FILEGROUP TEST_User_FileGroup2022
ALTER DATABASE TEST ADD FILEGROUP TEST_User_FileGroup2023

--给文件组添加文件
ALTER DATABASE TEST ADD 
FILE (NAME=N'TEST_User_FileGroup2020',FILENAME=N'D:\SQL\UserSQL\TEST_User_FileGroup2020.mdf',SIZE=5MB,
FILEGROWTH=1MB,MAXSIZE=UNLIMITED) TO FILEGROUP TEST_User_FileGroup2020
ALTER DATABASE TEST ADD 
FILE(NAME=N'TEST_User_FileGroup2021',FILENAME=N'D:\SQL\UserSQL\TEST_User_FileGroup2021.mdf',SIZE=5MB,
FILEGROWTH=1MB,MAXSIZE=UNLIMITED) TO FILEGROUP TEST_User_FileGroup2021
ALTER DATABASE TEST ADD 
FILE(NAME=N'TEST_User_FileGroup2022',FILENAME=N'D:\SQL\UserSQL\TEST_User_FileGroup2022.mdf',SIZE=5MB,
FILEGROWTH=1MB,MAXSIZE=UNLIMITED) TO FILEGROUP TEST_User_FileGroup2022
ALTER DATABASE TEST ADD 
FILE(NAME=N'TEST_User_FileGroup2023',FILENAME=N'D:\SQL\UserSQL\TEST_User_FileGroup2023.mdf',SIZE=5MB,
FILEGROWTH=1MB,MAXSIZE=UNLIMITED) TO FILEGROUP TEST_User_FileGroup2023

--查看数据库中文件情况
SELECT name,type_desc,physical_name,state_desc,size,growth 
FROM sys.database_files

--创建分区函数
CREATE PARTITION FUNCTION partition_function_user(DATETIME)
AS RANGE RIGHT FOR VALUES(
'2020-01-01',
'2021-01-01',
'2022-01-01'
)

--查询分区函数
SELECT * FROM sys.partition_functions

--删除分区函数partition_function_user
DROP PARTITION FUNCTION partition_function_user


---创建分区方案
CREATE PARTITION SCHEME TestUserScheme
AS PARTITION partition_function_user 
TO(
[PRIMARY],
TEST_User_FileGroup2020,
TEST_User_FileGroup2021,
TEST_User_FileGroup2022
)

--查询分区方案
SELECT * FROM sys.partition_schemes

--删除分区方案 Scheme_DateTime
DROP PARTITION SCHEME Scheme_DateTime


--创建分区表
CREATE TABLE [dbo].[User](
    Id INT,
    Name varchar(10),
    DellreceiveDate DATETIME,
    CloseDate DATETIME
) ON Scheme_DateTime(CloseDate)

--这里把分区函数的4个段对应到4个文件组上。
--然后把表链接到分区方案，这里使用CloseDate主键作为分区列。
 --ALTER TABLE dbo.[User] add CONSTRAINT [PK_CloseDate] PRIMARY KEY CLUSTERED (CloseDate)
 --ON TestSPScheme(SPNo)

2.视图创建

1）右键数据库=》属性=》文件组=》添加文件组（这步可以直接参考1中的创建文件组命令，我是按照名字结尾为年份的）

注：查看文件对应的【文件组】（这个一创建就无法修改文件组，会导致分表执行报错）

2）右键需要分区的表=》存储=》创建分区（这个就按向导下一步填写相关数据了，开始比较卡的样子，阿西吧）

选择根据分区的字段

选择分区函数

选择分区方案

设置边界

选择前面1）创建的文件组（注：这里地下好像需要多选个【PRIMARY】？为下一个做准备的？）

创建完成，也可以勾选立即运行（我这里另存为脚本桌面瞅瞅）

3.查看结果【$PARTITION.分区函数(分区字段)】

SELECT $PARTITION.partition_function_user001(CloseDate) AS 分区编号,COUNT(1) AS 记录数
FROM dbo.[User] 
GROUP BY $PARTITION.partition_function_user001(CloseDate)

--查询已存在的分区方案

select * from sys.partition_schemes

--查询已存在的分区函数

select * from sys.partition_functions

--根据临界值和分区函数名称查询某个时间的分区函数是否已存在
select b.value,* from sys.partition_functions a,sys.partition_range_values b 
WHERE a.function_id=b.function_id 
AND name='f_RECHARGE_INFO' 
AND CONVERT(varchar(100), b.value, 23)='2019-10-01'

--查询某个时间的分区编号
select $PARTITION.partition_function_user001('2022-12-01')

--根据分区编号查询表数据,CREATEDATE表示分区表的时间属性字段
select * from dbo.[User] 
WHERE $PARTITION.partition_function_user001(CloseDate)=3

--查询分区编号和对应的记录数
 SELECT $PARTITION.partition_function_user001(CloseDate) AS 分区编号,COUNT(1) AS 记录数
FROM dbo.[User] 
GROUP BY $PARTITION.partition_function_user001(CloseDate)

补充动态生成：

--新建存储过程，每年新增一个文件组
CREATE PROC CtiUser_AutoExted_Partition_Year
AS
BEGIN
    DECLARE @filePath VARCHAR(100),--文件路径
    @fileName VARCHAR(100),    --文件名称
    @fileSize VARCHAR(100),    --文件大小
    @fileGrowth VARCHAR(100),    --文件增长
    @fileMaxLimit VARCHAR(100),    --文件最大限制
    @fileGroupName VARCHAR(100),    --文件组名称
    @database VARCHAR(100),    --操作数据库
    @currentDateTimeByDay VARCHAR(100), --当前时间，日
    @schemeName VARCHAR(100),    --分区方案名称
    @partitionName VARCHAR(100), --分区函数名称
    @sql VARCHAR(400) -- 赋值文件属性

    SET @fileSize = '5MB';
    SET @fileGrowth = '1MB';
    SET @fileMaxLimit = 'unlimited';
    SET @currentDateTimeByDay = CONVERT(varchar(10),datepart(YYYY,GETDATE()));
    SET @fileName = 'TEST_User_FileGroup' + @currentDateTimeByDay;
    SET @filePath = 'D:\SQL\UserSQL\';
    --赋值数据库属性
    SET @database = 'TEST';
    --赋值文件组属性
    SET @fileGroupName = 'TEST_User_FileGroup' + @currentDateTimeByDay
    --赋值分区属性
    SET @schemeName='TestUserScheme001'
    SET @partitionName = 'partition_function_user001()'

        --创建文件组
        SET @sql =  'ALTER DATABASE ' + @database + ' add filegroup ' + @fileGroupName;
        EXEC(@sql);

        --创建文件，并绑定文件组
        SET @sql = 'ALTER DATABASE ' + @database  + ' add file (name=''' + @fileName + ''',' 
        + 'filename=''' + @filePath + '\' 
        + @fileName + '.mdf'',' + 'size = ' + @fileSize + ',' + 'filegrowth =' + @fileGrowth + ','
        + 'maxsize =' + @fileMaxLimit + '' + ')' + 'to filegroup ' + @fileGroupName;
        EXEC(@sql);

        --修改分区方案
        SET @sql = 'alter partition scheme ' + @schemeName + ' next used ' + @fileGroupName + '';
        EXEC(@sql);

        --修改分区函数
        SET @sql = 'alter partition function ' + @partitionName 
        + ' split range (N''' + CONVERT(varchar(10),datepart(YYYY,GETDATE())) + ''')';
        EXEC(@sql);

END
GO

摘自：https://blog.csdn.net/shanghaimoon/article/details/123344746

感谢：https://blog.csdn.net/songling418/article/details/13596707

https://www.cnblogs.com/kissdodog/p/3156166.html

https://blog.csdn.net/lanxingbudui/article/details/84792123

https://www.csdn.net/tags/MtzaAg5sMzcyNzYtYmxvZwO0O0OO0O0O.html

posted @ 2022-06-06 16:42 蜗牛的礼物阅读(343) 评论(0) 编辑收藏举报

刷新页面返回顶部

dzw159

SqlServer2019数据库分表

公告