数据表分区实际应用

数据表分区实际应用

数据量不到百万，体积不超过2G完全没有必要分区

分区是为了应对大数据优化处理的，数据量不大就不要瞎折腾了

---

先说下大概步骤：

1. 为数据库创建文件组
2. 为数据库创建文件
3. 创建分区函数
4. 创建分区方案
5. 创建索引应用于分区方案，所谓分区就是根据索引将不同数据写入不同数据库文件组，需注意这里的分区索引必须是聚集索引

至此分区工作结束

---

数据不断增长，新的数据超出了分区方案的边界，这时需要再次添加新的文件组以及文件，然后修改分区函数，分区方案

所有代码如下请根据需要取用

--创建分区前的准备工作
--创建文件组 创建文件 用于存放分区数据
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