全部栏目

首页 旅游资讯 线路攻略 景点大全 国内游 境外游 美食特产

sqlserver2008怎么实现自动分区表

发布网友 发布时间：2022-04-22 23:58

我来回答

共2个回答

懂视网 时间：2022-04-30 12:38

　　优化思路：采用分区表来实现日志表的自动随时间窗口滚动，即每天新增明天分区，并将7天前数据归档至日志表。以8月份为例子，当日日期为8号，流程如下图：

　　技术分享

　　具体步骤：

　　1.建立32个文件组，32个数据库文件，对应于每月31天，每月对31个文件进行复用

-- 创建文件组
USE [master]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY00]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY01]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY02]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY03]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY04]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY05]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY06]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY07]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY08]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY09]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY10]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY11]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY12]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY13]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY14]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY15]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY16]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY17]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY18]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY19]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY20]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY21]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY22]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY23]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY24]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY25]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY26]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY27]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY28]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY29]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY30]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY31]
GO

-- 创建和文件组相对应的文件，由于只有3个盘
USE [master]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY01‘, FILENAME = N‘E:partfileFDAY01.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY01]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY02‘, FILENAME = N‘E:partfileFDAY02.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY02]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY03‘, FILENAME = N‘E:partfileFDAY03.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY03]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY04‘, FILENAME = N‘E:partfileFDAY04.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY04]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY05‘, FILENAME = N‘E:partfileFDAY05.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY05]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY06‘, FILENAME = N‘E:partfileFDAY06.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY06]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY07‘, FILENAME = N‘E:partfileFDAY07.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY07]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY08‘, FILENAME = N‘E:partfileFDAY08.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY08]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY09‘, FILENAME = N‘E:partfileFDAY09.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY09]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY10‘, FILENAME = N‘E:partfileFDAY10.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY10]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY11‘, FILENAME = N‘E:partfileFDAY11.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY11]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY12‘, FILENAME = N‘E:partfileFDAY12.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY12]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY13‘, FILENAME = N‘E:partfileFDAY13.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY13]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY14‘, FILENAME = N‘E:partfileFDAY14.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY14]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY15‘, FILENAME = N‘E:partfileFDAY15.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY15]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY16‘, FILENAME = N‘E:partfileFDAY16.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY16]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY17‘, FILENAME = N‘E:partfileFDAY17.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY17]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY18‘, FILENAME = N‘E:partfileFDAY18.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY18]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY19‘, FILENAME = N‘E:partfileFDAY19.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY19]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY20‘, FILENAME = N‘E:partfileFDAY20.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY20]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY21‘, FILENAME = N‘E:partfileFDAY21.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY21]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY22‘, FILENAME = N‘E:partfileFDAY22.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY22]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY23‘, FILENAME = N‘E:partfileFDAY23.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY23]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY24‘, FILENAME = N‘E:partfileFDAY24.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY24]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY25‘, FILENAME = N‘E:partfileFDAY25.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY25]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY26‘, FILENAME = N‘E:partfileFDAY26.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY26]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY27‘, FILENAME = N‘E:partfileFDAY27.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY27]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY28‘, FILENAME = N‘E:partfileFDAY28.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY28]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY29‘, FILENAME = N‘E:partfileFDAY29.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY29]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY30‘, FILENAME = N‘E:partfileFDAY30.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY30]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY31‘, FILENAME = N‘E:partfileFDAY31.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY31]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY00‘, FILENAME = N‘E:partfileFDAY00.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY00]
GO

 

 

 

　　注意：为什么是32个文件组，32个数据库文件。这和分区函数和分区方案有关系，比如分区函数以DATETIME为分区类型，建立20150801和20150802两个边界，那么就会产生如下3个分区。那相应的分区方案中就要有3个文件组和其对应。

技术分享

      在建立分区函数时，边界的归属。请看上图，以20150801为例，如果是right，边界归属于右边，所分的两个区间:数据<20150801和20150801<=数据<20150802；如果是left，边界归属左边，所分区间为:数据<=20150801和20150801<数据<=20150802;总结一句话，关键字是那边，那么边界值就归属于所分区域的那边。

　　对于是否有必要将每个文件建立到不同的磁盘上，我这里没有建立，因为应用中的查询一般不会跨分区，都查近几个小时的，所以没有必要将文件建立到不同的磁盘文件中。

　　如果对于分区表的基础概念还不清楚，请看SQL Server表分区。

　　2.建立相应的分区函数和分区方案

　　

USE TClientLog;
CREATE PARTITION FUNCTION part_day_rang_func(DATETIME)
AS RANGE right FOR VALUES 
(
‘2015-08-01 00:00:00‘,
‘2015-08-02 00:00:00‘,
‘2015-08-03 00:00:00‘,
‘2015-08-04 00:00:00‘,
‘2015-08-05 00:00:00‘,
‘2015-08-06 00:00:00‘,
‘2015-08-07 00:00:00‘,
‘2015-08-08 00:00:00‘,
‘2015-08-09 00:00:00‘,
‘2015-08-10 00:00:00‘,
‘2015-08-11 00:00:00‘,
‘2015-08-12 00:00:00‘,
‘2015-08-13 00:00:00‘,
‘2015-08-14 00:00:00‘,
‘2015-08-15 00:00:00‘,
‘2015-08-16 00:00:00‘,
‘2015-08-17 00:00:00‘,
‘2015-08-18 00:00:00‘,
‘2015-08-19 00:00:00‘,
‘2015-08-20 00:00:00‘,
‘2015-08-21 00:00:00‘,
‘2015-08-22 00:00:00‘,
‘2015-08-23 00:00:00‘,
‘2015-08-24 00:00:00‘,
‘2015-08-25 00:00:00‘,
‘2015-08-26 00:00:00‘,
‘2015-08-27 00:00:00‘,
‘2015-08-28 00:00:00‘,
‘2015-08-29 00:00:00‘,
‘2015-08-30 00:00:00‘,
‘2015-08-31 00:00:00‘);

CREATE PARTITION SCHEME part_day_rang_scheme
AS PARTITION part_day_rang_func
TO (
FGDAY00,
FGDAY01,
FGDAY02,
FGDAY03,
FGDAY04,
FGDAY05,
FGDAY06,
FGDAY07,
FGDAY08,
FGDAY09,
FGDAY10,
FGDAY11,
FGDAY12,
FGDAY13,
FGDAY14,
FGDAY15,
FGDAY16,
FGDAY17,
FGDAY18,
FGDAY19,
FGDAY20,
FGDAY21,
FGDAY22,
FGDAY23,
FGDAY24,
FGDAY25,
FGDAY26,
FGDAY27,
FGDAY28,
FGDAY29,
FGDAY30,
FGDAY31
);

 

 

 

　　注意：分区方案指定的是文件组不是文件，FGDAY00文件组对应的分区是‘分区数据<20150801‘，FGDAY31对应的分区是‘分区数据>=20150831‘。

 

　　3.建立主日志分区表和历史日志分区表，以表内的时间字段为分区键，索引采用与表分区对齐方式，分区自动化管理脚本如下：

 

CREATE TABLE [dbo].[ClientLog](
 [SynID] [nchar](38) NOT NULL,
 [ParkingId] [int] NOT NULL,
 [ParkingBoxId] [int] NOT NULL,
 [Message] [varchar](max) NULL,
 [OccurTime] [datetime] NOT NULL,
 [UpdateTime] [datetime] NOT NULL,
 [ErrorLevel] [int] NOT NULL,
 [State] [int] NULL,
 [IsSend] [int] NULL,
 CONSTRAINT [PK_ClientLog] PRIMARY KEY NONCLUSTERED 
(
 [SynID] ASC,
 [OccurTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [part_day_rang_scheme]([OccurTime])
) ON [part_day_rang_scheme]([OccurTime])
GO
CREATE NONCLUSTERED INDEX [idx_clientlog_otime] ON [dbo].[ClientLog] 
(
 [ParkingId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [part_day_rang_scheme]([OccurTime])
GO

 

 

　　注意：主键中必须包含分区键；分区索引必须使用和表一致的分区方案，即索引必须与表对齐，才能进行分区切换；

 

　　4.主日志分区表保留7天，每天增加后数第7天的分区，前数第7天的数据进行归档，与日志历史表进行分区交换，流程图参考优化思路的图。

-- =============================================
-- Author: zhangkun
-- Create date: <2015.08.07>
-- Description: <根据日志的滑动窗口业务，进行自动化分区管理>
-- =============================================
-- 1.修改分区方案和分区函数
-- 2.进行分区交换，将归档数据放入历史表
alter PROCEDURE [dbo].[sp_PartitionManage] @td DATETIME
AS 
 BEGIN
 DECLARE @flag CHAR(1) --标志位
 IF @td IS NULL --如果@td为null，则默认当天
  SET @td = GETDATE()
-- 1.修改分区方案和分区函数,当天新增后数第七天的日期
 BEGIN 
  DECLARE @td_next7 DATETIME
  DECLARE @day_next7 VARCHAR(2)
  DECLARE @sql NVARCHAR(MAX) --动态sql字符串
  SET @td_next7 = DATEADD(DAY, 7, @td) --7天后日期
  SET @day_next7 = CASE WHEN LEN(DATENAME(DAY, @td_next7)) = 1
     THEN ‘0‘ + DATENAME(DAY, @td_next7)
     ELSE DATENAME(DAY, @td_next7)
    END; --7天后是当月第几天SELECT @flag = COUNT(1)
  FROM sys.partition_functions a ,
   sys.partition_range_values b
  WHERE a.name = ‘part_day_rang_func‘
   AND a.function_id = b.function_id
   AND CONVERT(DATETIME, b.value) = CONVERT(VARCHAR(10), @td_next7, 120)
   + ‘ 00:00:00.000‘; 
  PRINT @flag;  
  IF ( @flag != ‘1‘ ) 
  BEGIN
   SET @sql = ‘alter partition scheme part_day_rang_scheme next used FGDAY‘
   + @day_next7 + ‘;
  alter partition function part_day_rang_func() split range(‘‘‘
   + CONVERT(VARCHAR(10), @td_next7, 120) + ‘‘‘)‘ 
   EXEC sp_executesql @sql;
  END
 END
  
-- 2.进行分区交换，将归档数据放入历史表
 DECLARE @td_before7 DATETIME
 DECLARE @day_before7 VARCHAR(2) 
 SET @td_before7 = DATEADD(DAY, -7
                                                            

                        
                        热心网友
                        时间：2022-04-30 09:46
                    

                    
/*第一步：创建分区函数*/

Create partition function Part_func_Bag(varchar(20)) as range right

/*正式区间

for values(N'01100923909760', N'01100936207030', N'6311001806524',N'92451400060101');*/for values(N'91701311710807', N'07201280707101', N'80110110884',N'80241001430714');goselect * from Bag where BagCode in(N'91701311710807', N'07201280707101', N'80110110884',N'80241001430714')

/*第二步：创建文件组和文件*/

alter database ZXAutoCode add filegroup [Bag_1]; alter database ZXAutoCode add filegroup [Bag_2];alter database ZXAutoCode add filegroup [Bag_3];alter database ZXAutoCode add filegroup [Bag_4];goalter database ZXAutoCode add file (name = Bag1_data,filename = 'E:\MSSQL\TESTDATA\Bag1_data.ndf',size = 3MB) to filegroup [Bag_1];alter database ZXAutoCode add file (name = Bag2_data,filename = 'E:\MSSQL\TESTDATA\Bag2_data.ndf',size = 3MB) to filegroup [Bag_2];alter database ZXAutoCode add file (name = Bag3_data,filename = 'E:\MSSQL\TESTDATA\Bag3_data.ndf',size = 3MB) to filegroup [Bag_3]; alter database ZXAutoCode add file (name = Bag4_data,filename = 'E:\MSSQL\TESTDATA\Bag4_data.ndf',size = 3MB) to filegroup [Bag_4]; go

/*第三步：创建分区方案并关联到分区函数*/

Create partition scheme Part_func_Bag_scheme as partition Part_func_Bag to ([Bag_1],[Bag_2],[Bag_3],[Bag_4],[Primary]); go

/*第四步 重建索引（删除聚集索引以及需要分区字段的索引后重建该类索引，表被按分区值将分配到各文件组。数据在这一步开始转移。）*/ 

EXEC sp_helpindex N'Bag' --查看orders中使用的索引 drop index idx_cl_od on Bag;gocreate clustered index idx_cl_od on Bag(bagcode) on Part_func_Bag_scheme(bagcode); go
声明声明：本网页内容为用户发布，旨在传播知识，不代表本网认同其观点，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。E-MAIL:11247931@qq.com