mgm6608美高梅app下载-mgm集团美高梅登录

mgm6608美高梅app下载以独家报道、社区、博客和专题策划等方式引领产业潮流,mgm集团美高梅登录为足球爱好者以及体育爱好者提供足球上网,让你无后顾之忧!,拥有多位香港乐坛红星。

如果将某个表放置在一个物理驱动器上,循环利

日期:2019-11-23编辑作者:mgm集团美高梅登录

概述

大家应该都知道在很多业务场景下我们需要对一些记录量比较大的表进行分区,同时为了保证性能需要将一些旧的数据进行归档。在分区表很多的情况下如果每一次归档都需要人工干预的话工程量是比较大的而且也容易发生纰漏。接下来分享一个自己编写的自动归档分区数据的脚本,原理是分区表和归档表使用相同的分区方案,循环利用当前的文件组,话不多说了,来一起看看详细的介绍吧。

当我们数据量比较大的时候,我们需要将大型表拆分为多个较小的表,则只访问部门数据的查询就可以更快的运行,基本原理就是,因为要扫描的数据变的更小。维护任务(例如,重新生成索引或备份表)也可以更快的运行。

一、创建测试数据

我 们可以再不通过将表物理放置在多个磁盘驱动器上来拆分表的情况下获取分区。如果将某个表放置在一个物理驱动器上,将相关表放置在另一个驱动器上,则可以提 高查询性能,因为当运行涉及表间连接的查询时,多个磁盘头同时读取数据。可以使用SQL Server文件组来指定放置表的磁盘。

----01创建文件组USE [master]GOALTER DATABASE [chenmh] ADD FILEGROUP [Group1]GOALTER DATABASE [chenmh] ADD FILEGROUP [Group2]GOALTER DATABASE [chenmh] ADD FILEGROUP [Group3]GOALTER DATABASE [chenmh] ADD FILEGROUP [Group4]GOUSE [master]GOALTER DATABASE [chenmh] ADD FILE ( NAME = N'datafile1', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAdatafile1.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [Group1]GOALTER DATABASE [chenmh] ADD FILE ( NAME = N'datafile2', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAdatafile2.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [Group2]GOALTER DATABASE [chenmh] ADD FILE ( NAME = N'datafile3', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAdatafile3.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [Group3]GOALTER DATABASE [chenmh] ADD FILE ( NAME = N'datafile4', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAdatafile4.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [Group4]GO----02创建分区函数USE [chenmh]GOCREATE PARTITION FUNCTION [Pt_Range] AS RANGE RIGHT FOR VALUES (1000000, 2000000, 3000000)GO----03创建分区方案,分区方案对应的文件组数是分区函数指定的数量+1CREATE PARTITION SCHEME Ps_RangeAS PARTITION Pt_RangeTO (Group1, Group2, Group3, Group4);---04创建表,指定的分区列的数据类型一定要和分区函数指定的列类型一致。CREATE TABLE [dbo].[News]( [id] [bigint] NOT NULL, [status] [int] NULL, CONSTRAINT [PK_News] PRIMARY KEY CLUSTERED WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Ps_Range] ON [Ps_Range]-----创建归档分区表CREATE TABLE [dbo].[NewsArchived]( [id] [bigint] NOT NULL, [status] [int] NULL, CONSTRAINT [PK_NewsArchived] PRIMARY KEY CLUSTERED WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Ps_Range] ON [Ps_Range]----插入测试数据DECLARE @id INT SET @id=1WHILE @id<5001000BEGIN INSERT INTO News VALUES SET @id=@id+1END

对于分区的方式,基本就三种方式:硬件分区、水平分区、垂直分区。相关方案可以参考SQL联机丛书。

可以看到当前总共有4个分区,每一个分区定义的范围区间是100万,分区4我故意多插入了200多万的数据来验证自动归档分区。

这里我们介绍分区表的具体实战方法:

二、自动归档分区脚本

第一步,首先建立我们要使用的数据库,最重要的是建立多个文件组。

CREATE PROCEDURE Pro_Partition_AutoArchiveData(@PartitionTable VARCHAR,@SwitchTable VARCHARASBEGINDECLARE @FunName VARCHAR,@SchemaName VARCHAR,@MaxPartitionValue sql_variant---根据归档表查找对应的分区方案、分区函数、最小分区数、最大分区范围值SELECT DISTINCT@FunName=MAX,@SchemaName=MAX, @MaxPartitionValue=maxFROM sys.partitions p inner join sys.indexes i ON p.object_id=i.object_id and p.index_id=i.index_idinner join sys.partition_schemes ps ON i.data_space_id=ps.data_space_idinner join sys.destination_data_spaces dds ON ps.data_space_id=dds.partition_scheme_id and dds.destination_id=p.partition_numberinner join sys.data_spaces ds ON dds.data_space_id=ds.data_space_idinner join sys.partition_functions pf ON ps.function_id=pf.function_idLEFT join sys.partition_range_values prv ON pf.function_id=prv.function_id AND prv.boundary_id=p.partition_number-pf.boundary_value_on_rightLEFT join sys.partition_parameters pp ON prv.function_id=pp.function_id and prv.parameter_id=pp.parameter_idLEFT join sys.types t ON pp.system_type_id=t.system_type_id and pp.user_type_id=t.user_type_idWHERE OBJECT_NAME=@PartitionTableDECLARE @MaxId BIGINT,@MinId BIGINT,@Sql NVARCHAR,@GroupName VARCHAR,@MinPartitionNumber INTSET @Sql= N'SELECT @MaxId=MAX FROM '+@PartitionTableEXEC sp_executesql @Sql,N'@MaxId BIGINT out,@MinId BIGINT out',@MaxId OUT,@MinId OUTSELECT @FunName AS FunName,@SchemaName AS SchemaName,@MaxPartitionValue AS MaxPartitionValue ,@MaxId AS MaxId,@MinId AS MinId---判断当前表的最大的id是否已经在最大的分区中IF @MaxId>=@MaxPartitionValue BEGIN ----归档分区数据,根据表的最小值找到它所属的分区. SET @Sql= N'SELECT @MinPartitionNumber=$PARTITION.'+@FunName+N',@MinId)+N')'; EXEC sp_executesql @Sql,N'@MinPartitionNumber INT out',@MinPartitionNumber OUT SET @Sql=N'ALTER TABLE ' +@PartitionTable+ N' SWITCH PARTITION '+CONVERT,@MinPartitionNumber)+ N' TO ' +@SwitchTable+ N' PARTITION ' +CONVERT,@MinPartitionNumber); --PRINT @Sql EXEC  ---修改分区方案,增加新的分区对应的文件组,根据最小的分区id找到对应的文件组。 SELECT DISTINCT @GroupName=ds.name FROM sys.partitions p inner join sys.indexes i ON p.object_id=i.object_id and p.index_id=i.index_id inner join sys.partition_schemes ps ON i.data_space_id=ps.data_space_id inner join sys.destination_data_spaces dds ON ps.data_space_id=dds.partition_scheme_id and dds.destination_id=p.partition_number inner join sys.data_spaces ds ON dds.data_space_id=ds.data_space_id inner join sys.partition_functions pf ON ps.function_id=pf.function_id WHERE pf.name=@FunName AND ps.name=@SchemaName AND p.partition_number=@MinPartitionNumber SET @Sql=N'ALTER PARTITION SCHEME '+@SchemaName+N' NEXT USED '+@GroupName --PRINT @Sql EXEC  ---修改分区函数,增加新的分区,增加新的分区范围值,在现有的最大的值的基础上加100万 SET @MaxPartitionValue=CONVERT(BIGINT,@MaxPartitionValue)+1000000 SET @Sql=N'ALTER PARTITION FUNCTION '+@FunName+N''+N' SPLIT RANGE ,@MaxPartitionValue)+N')' --PRINT @Sql EXEC  ENDEND

我们先新建立四个目录,来组成文件组,一个用来存放主文件的目录:Primary

三、自动归档分区数据

三个数据文件目录:FG1、FG2、FG3

1.首次测试

建立库:

EXEC Pro_Partition_AutoArchiveData 'news','NewsArchived';

 

注意:每调用一次归档一个最小分区的数据。

View Row Code

分区表的News分区1的数据被归档到了NewsArchived表中,且创建了分区5,分区5使用的是已归档的分区1的文件组,达到了循环利用文件组的效果。

1 create database Sales on primary
2 (
3 name=N'Sales',
4 filename=N'G:dataPrimarySales.mdf',
5 size=3MB,
6 maxsize=100MB,
7 filegrowth=10%
8 ),
9 filegroup FG1
10 (
11 NAME = N'File1',
12 FILENAME = N'G:dataFG1File1.ndf',
13 SIZE = 1MB,
14 MAXSIZE = 100MB,
15 FILEGROWTH = 10%
16 ),
17 FILEGROUP FG2
18 (
19 NAME = N'File2',
20 FILENAME = N'G:dataFG2File2.ndf',
21 SIZE = 1MB,
22 MAXSIZE = 100MB,
23 FILEGROWTH = 10%
24 ),
25 FILEGROUP FG3
26 (
27 NAME = N'File3',
28 FILENAME = N'G:dataFG3File3.ndf',
29 SIZE = 1MB,
30 MAXSIZE = 100MB,
31 FILEGROWTH = 10%
32 )
33 LOG ON
34 (
35 NAME = N'Sales_Log',
36 FILENAME = N'G:dataPrimarySales_Log.ldf',
37 SIZE = 1MB,
38 MAXSIZE = 100MB,
39 FILEGROWTH = 10%
40 )
41 GO

2.再调用一次归档分区脚本

 

当分区表最大的id小于最大的分区值时自动归档分区脚本就不会生效。所以当前的测试表数据还可以再归档分区3的数据。

第二步:建立分区函数,目的是用来规范不同数据存放到不同目录的标准,简单讲就是如何分区。

3.经过一段时间的运行归档数据可能是这样的效果

 

Group1→Group4→Group1→.......

View Row Code

四、脚本注意事项

1 USE Sales
2 GO
3 CREATE PARTITION FUNCTION pf_OrderDate (datetime)
4 AS RANGE RIGHT
5 FOR VALUES ('2003/01/01', '2004/01/01')
6 GO

1.@PartitionTable和@SwitchTable表必须使用同名的分区方案和分区函数,否则@SwitchTable就需要单独修改分区方案和函数,且表结构完全一致。

 

2.归档的表分区列数据类型必须是INT类型,且值是自增规律.

我们创建了一个用于数据类型为datetime的分区函数,按照时间段来划分。

3.分区归档作业在备份作业后执行

文件组 分区 取值范围
FG1 1 (过去某年, 2003/01/01)
FG2 2 [2003/01/01, 2004/01/01)
FG3 3 [2004/01/01,未来某年)

4.建议使用Right分区,Left分区会出现有的最后一个分区文件组不会循环替换,一直处于分区的最后,比如Group1,Group2,Group3,Group1,Group2,Group3,Group1,Group4。期望的应该是Group1,Group2,Group3,Group4,Group1,Group2,Group3,Group4,Group1

第三步:创建分区方案,关联到分区函数。目的就是我们将已经建立好的分区函数组织成一套方案,简单点将就是我们在哪里对数据进行分区。

5.注意我当前的每个分区大小是100万和分区函数保持一致,如果范围值不同,需要修改最末尾代码的"修改分区函数"处代码.

 

mgm集团美高梅登录 ,总结

View Row Code

当前自动归档分区脚本如果要拷贝去用还是得能完全理解每一段代码,根据自己的业务做适当的修改,毕竟数据是无价的!!!。最后只需要创建一个作业定期跑作业就行,重复执行也不影响。

1 Use Sales
2 go
3 create partition scheme ps_OrderDate
4 as partition pf_OrderDate
5 to(FG2,FG2,FG3)
6 go

好了,以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对脚本之家的支持。

 

很简单,就是将第二步建立的分区函数应用已经建立的分区组中。

第四步:创建分区表。创建表并将其绑定到分区方案上。我们首先建立两个表,一张原始表另一张用来归档数据,保存归档数据。

 

View Row Code

1 Use Sales
2 go
3 create table Orders
4 (
5 OrderID int identity(10000,1),
6 OrderDate datetime not null,
7 CustomerID int not null,
8 constraint PK_Orders primary key(OrderID,OrderDate)
9 )
10 on ps_OrderDate(OrderDate)
11 go
12 create table OrdersHistory
13 (
14 OrderID int identity(10000,1),
15 OrderDate datetime not null,
16 CustomerID int not null,
17 constraint PK_OrdersHistory primary key(OrderID,OrderDate)
18 )
19 on ps_OrderDate(OrderDate)
20 go

 

到这里,通过上面的四步我们已经完整的搭建好了一个带有分区表的库,我们来插入一些数据,来测试下我们建立是否好用。

首先,因为是用2003年1月1号作为区分点的,我们先向数据表中写入2002年的规范数据:

 

View Row Code

1 USE Sales
2 GO
3 INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2002/6/25', 1000)
4 INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2002/8/13', 1000)
5 INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2002/8/25', 1000)
6 INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2002/9/23', 1000)
7 GO

 

同样我们写入2003年四条数据:

 

View Row Code

1 USE Sales
2 GO
3 INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2003/6/25', 1000)
4 INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2003/8/13', 1000)
5 INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2003/8/25', 1000)
6 INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2003/9/23', 1000)
7 GO

 

我们来查看这些数据是否完整录入:

mgm集团美高梅登录 1

因为OrdersHistory表我们还没有归档数据,所以为空。

我们来分条件查询下:

1、查询某个分区

这里我们要用到$partition函数。这个函数在联机丛书中是这样解释的:

用法:
为任何指定的分区函数返回分区号,一组分区列值将映射到该分区号中。
语法: 
[ database_name. ] $PARTITION.partition_function_name(expression)
参数:
database_name 
包含分区函数的数据库的名称。
partition_function_name 
对其应用一组分区列值的任何现有分区函数的名称。
expression 
其数据类型必须匹配或可隐式转换为其对应分区列数据类型的表达式。expression 也可以是当前参与 partition_function_name 的分区列的名称。
返回类型:
int 
备注:
$PARTITION 返回从 1 到分区函数的分区数之间的 int 值。
$PARTITION 将针对任何有效值返回分区号,无论此值当前是否存在于使用分区函数的分区表或索引中。

我们来查询分区表Order的第一个分区,代码如下:

mgm集团美高梅登录 2

可以看到我们查询出来的数据全部为2002年的,也就是说在第一分区中我们存入的数据都是小于2003年,按照此推断2003年的数据,就应该存在第二分区中:

mgm集团美高梅登录 3

结果如我们所料,我们可以按照这个分区进行分组来查看各个分区的数据行多少,代码如下:

 

View Row Code

1 select $partition.pf_OrderDate(OrderDate) as Patition,COUNT(*) countRow from dbo.Orders
2 group by $partition.pf_OrderDate(OrderDate)

 

还可以通过$Partition函数获得一组分区标识列值的分区号,例如获得2002属于哪个分区,代码如下:

mgm集团美高梅登录 4

2、归档数据

本文由mgm6608美高梅app下载发布于mgm集团美高梅登录,转载请注明出处:如果将某个表放置在一个物理驱动器上,循环利

关键词:

redhat下MySQL主从备份,具体代码如下所示

mgm6608美高梅app下载 ,废话不多说了,直接给大家贴代码了,具体代码如下所示: redhat下MySQL主从备份 mgm集团美高梅...

详细>>

DQL语句的基本使用,首先我们复习第一天的主要

首先我们复习第一天的主要内容: SQL语句的种类 数据定义语句(DDL:Data Definition Language) 包括create和drop等操作;...

详细>>

索引已经是性能优化中大家常常提到而说到的问

一.概述 索引填充因子作用:提供填充因子选项是为了优化索引数据存储和性能。 当创建或重新生成索引时,填充因...

详细>>

//创建数据库 (create 内部命令),它不会复制与表

(一)将旧表复制到新表 本文给大家分享了好几种复制表结构、表数据的示例介绍,具体详情请看下文吧。 CREA...

详细>>