数据表分区实际应用
数据量不到百万,体积不超过2G完全没有必要分区
分区是为了应对大数据优化处理的,数据量不大就不要瞎折腾了
先说下大概步骤:
- 为数据库创建文件组
- 为数据库创建文件
- 创建分区函数
- 创建分区方案
- 创建索引应用于分区方案,所谓分区就是根据索引将不同数据写入不同数据库文件组,需注意这里的分区索引必须是聚集索引
至此分区工作结束
数据不断增长,新的数据超出了分区方案的边界,这时需要再次添加新的文件组以及文件,然后修改分区函数,分区方案
所有代码如下请根据需要取用
--创建分区前的准备工作
--创建文件组 创建文件 用于存放分区数据
ALTER DATABASE xbest ADD FILEGROUP G2018
ALTER DATABASE xbest ADD FILE (NAME='F2018' ,FILENAME='D:\SqlServer\xbest\F2018.ndf' ,SIZE=8MB ,FILEGROWTH=8MB) TO FILEGROUP G2018
ALTER DATABASE xbest ADD FILEGROUP G2019
ALTER DATABASE xbest ADD FILE (NAME='F2019' ,FILENAME='D:\SqlServer\xbest\F2019.ndf' ,SIZE=8MB ,FILEGROWTH=8MB) TO FILEGROUP G2019
USE [xbest]
--创建分区函数
CREATE PARTITION FUNCTION MyRapid_Depart (DATETIME)
AS RANGE RIGHT
FOR VALUES ('2019-01-01')
--创建分区方案
CREATE PARTITION SCHEME MyRapid_Scheme
AS PARTITION MyRapid_Depart
TO ( G2018 ,G2019)
--修改分区方案
--创建文件组 创建文件 用于存放分区数据
ALTER DATABASE xbest ADD FILEGROUP G2020
ALTER DATABASE xbest ADD FILE (NAME='F2020' ,FILENAME='D:\SqlServer\xbest\F2020.ndf' ,SIZE=8MB ,FILEGROWTH=8MB) TO FILEGROUP G2020
--修改分区方案
USE [xbest]
ALTER PARTITION SCHEME MyRapid_Scheme NEXT USED G2020
--修改分区函数
ALTER PARTITION FUNCTION MyRapid_Depart() SPLIT RANGE ('2020-01-01')
--修改分区方案
--创建文件组 创建文件 用于存放分区数据
ALTER DATABASE xbest ADD FILEGROUP G2021
ALTER DATABASE xbest ADD FILE (NAME='F2021' ,FILENAME='D:\SqlServer\xbest\F2021.ndf' ,SIZE=8MB ,FILEGROWTH=8MB) TO FILEGROUP G2021
--修改分区方案
USE [xbest]
ALTER PARTITION SCHEME MyRapid_Scheme NEXT USED G2021
--修改分区函数
ALTER PARTITION FUNCTION MyRapid_Depart() SPLIT RANGE ('2021-01-01')
--批量删除主键
USE [xbest]
DECLARE CURSOR_DROP_DEFAULT CURSOR SCROLL FOR
SELECT i.Name ,B.name tb
FROM SYS.COLUMNS A
LEFT JOIN SYS.INDEX_COLUMNS AS C ON A.OBJECT_ID = C.OBJECT_ID AND A.COLUMN_ID = C.COLUMN_ID
LEFT JOIN SYS.INDEXES I ON A.OBJECT_ID = I.OBJECT_ID AND C.INDEX_ID = I.INDEX_ID
LEFT JOIN SYS.objects B ON A.object_id = B.object_id
WHERE I.is_primary_key = 1
OPEN CURSOR_DROP_DEFAULT
DECLARE @DefaultName NVARCHAR(100)
DECLARE @TableName NVARCHAR(100)
FETCH NEXT FROM CURSOR_DROP_DEFAULT INTO @DefaultName ,@TableName
WHILE @@FETCH_STATUS=0
BEGIN
IF ISNULL(@DefaultName ,'') != ''
BEGIN
EXEC('ALTER TABLE '+@TableName+' DROP CONSTRAINT '+@DefaultName)
END
FETCH NEXT FROM CURSOR_DROP_DEFAULT INTO @DefaultName ,@TableName
END
CLOSE CURSOR_DROP_DEFAULT
DEALLOCATE CURSOR_DROP_DEFAULT
--批量添加非聚集主键
USE [xbest]
DECLARE CURSOR_ADD_PRIMARY CURSOR SCROLL FOR
SELECT A.NAME
FROM SYSOBJECTS A
WHERE A.XTYPE = 'U'
AND A.name NOT LIKE 'HIS_%'
ORDER BY A.name
OPEN CURSOR_ADD_PRIMARY
DECLARE @Table NVARCHAR(50)
FETCH NEXT FROM CURSOR_ADD_PRIMARY INTO @Table
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE @ColumnName NVARCHAR(50)
SET @ColumnName = SUBSTRING(@Table ,5,LEN(@Table)-4) + '_Id'
EXEC('ALTER TABLE '+@Table+' ADD CONSTRAINT PK_'+@Table+' PRIMARY KEY NONCLUSTERED ( '+ @ColumnName+' ) ON [PRIMARY]')
FETCH NEXT FROM CURSOR_ADD_PRIMARY INTO @Table
END
CLOSE CURSOR_ADD_PRIMARY
DEALLOCATE CURSOR_ADD_PRIMARY
--为所有表添加分区聚集索引
--可以根据需要调整,如:只在业务表上添加,基础表不添加
--一定要是聚集索引,否则没有效果
--这里为了快速添加用的是CreateTime列,实际应用中用单据时间效果更好
--单据时间注意主子表一致,否则会导致跨分区查询,降低效率
--也可以专门添加一个字段:账期期间,用于分区索引效果更好
USE [xbest]
DECLARE CURSOR_ADD_CLUSTERED CURSOR SCROLL FOR
SELECT A.NAME
FROM SYSOBJECTS A
WHERE A.XTYPE = 'U'
--只对业务数据布置分区
AND A.name LIKE 'PSI_%'
OPEN CURSOR_ADD_CLUSTERED
DECLARE @PSITable NVARCHAR(50)
FETCH NEXT FROM CURSOR_ADD_CLUSTERED INTO @PSITable
WHILE @@FETCH_STATUS=0
BEGIN
--索引列添加限制不允许为空
EXEC('ALTER TABLE '+ @PSITable +' ALTER COLUMN CreateTime DATETIME NOT NULL')
--EXEC('DROP INDEX ' + @PSITable + '_DateTime_Depart ON '+ @PSITable)
EXEC('CREATE CLUSTERED INDEX ' + @PSITable + '_DateTime_Depart ON '+ @PSITable +'(CreateTime) ON MyRapid_Scheme(CreateTime)')
FETCH NEXT FROM CURSOR_ADD_CLUSTERED INTO @PSITable
END
CLOSE CURSOR_ADD_CLUSTERED
DEALLOCATE CURSOR_ADD_CLUSTERED
DECLARE CURSOR_CLEAR CURSOR SCROLL FOR
SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U'
OPEN CURSOR_CLEAR
DECLARE @tName VARCHAR(50)
FETCH NEXT FROM CURSOR_CLEAR INTO @tName
WHILE @@FETCH_STATUS=0
BEGIN
EXEC('DBCC DBREINDEX('''+@tName+ ''')')
PRINT @tName
FETCH NEXT FROM CURSOR_CLEAR INTO @tName
END
CLOSE CURSOR_CLEAR
DEALLOCATE CURSOR_CLEAR
ALTER DATABASE [xbest] SET RECOVERY SIMPLE
DBCC SHRINKDATABASE([xbest], 0)
ALTER DATABASE [xbest] SET RECOVERY FULL
--清理日志
USE [master]
ALTER DATABASE xbest SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE xbest SET RECOVERY SIMPLE --简单模式
USE xbest
DBCC SHRINKFILE (N'xbest_log' , 2, TRUNCATEONLY) --设置压缩后的日志大小为2M,可以自行指定
USE[master]
ALTER DATABASE xbest SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE xbest SET RECOVERY FULL --还原为完全模式
--恢复为单文件
--DROP PARTITION SCHEME [MyRapid_Scheme]
--DROP PARTITION FUNCTION [MyRapid_Depart]
--ALTER DATABASE [xbest] REMOVE FILE F2018
--ALTER DATABASE [xbest] REMOVE FILEGROUP G2018
--ALTER DATABASE [xbest] REMOVE FILE F2019
--ALTER DATABASE [xbest] REMOVE FILEGROUP G2019
--ALTER DATABASE [xbest] REMOVE FILE F2020
--ALTER DATABASE [xbest] REMOVE FILEGROUP G2020
--ALTER DATABASE [xbest] REMOVE FILE F2021
--ALTER DATABASE [xbest] REMOVE FILEGROUP G2021