当前位置:  数据库>sqlserver

sql server 2005创建-修改-查看分区表的方法介绍

    来源: 互联网  发布时间:2014-08-29

    本文导语:  在sql server 2005中,表分区分为水平分区和垂直分区。 水平分区将表分为多个表。每个表包含的列数相同,但是行更少。例如,可以将一个包含十亿行的表水平分区成 12 个表,每个小表表示特定年份内一个月的数据。任何需要特...

在sql server 2005中,表分区分为水平分区和垂直分区。

水平分区将表分为多个表。每个表包含的列数相同,但是行更少。例如,可以将一个包含十亿行的表水平分区成 12 个表,每个小表表示特定年份内一个月的数据。任何需要特定月份数据的查询只需引用相应月份的表。

而垂直分区则是将原始表分成多个只包含较少列的表。水平分区是最常用分区方式,本文以水平分区来介绍具体实现方法。

水平分区常用的方法是根据时期和使用对数据进行水平分区。
例如本文例子,一个短信发送记录表包含最近一年的数据,但是只定期访问本季度的数据。在这种情况下,可考虑将数据分成四个区,每个区只包含一个季度的数据。

创建文件组

建立分区表先要创建文件组,而创建多个文件组主要是为了获得好的 I/O 平衡。一般情况下,文件组数最好与分区数相同,并且这些文件组通常位于不同的磁盘上。每个文件组可以由一个或多个文件构成,而每个分区必须映射到一个文件组。一个文件组可以由多个分区使用。为了更好地管理数据(例如,为了获得更精确的备份控制),对分区表应进行设计,以便只有相关数据或逻辑分组的数据位于同一个文件组中。使用 ALTER DATABASE,添加逻辑文件组名:
 

ALTER DATABASE [DeanDB] ADD FILEGROUP [FG1]

DeanDB为数据库名称,FG1文件组名。创建文件组后,再使用 ALTER DATABASE 将文件添加到该文件组中:
 

ALTER DATABASE [DeanDB] ADD FILE ( NAME = N'FG1', FILENAME = N'C:DeanDataFG1.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG1]

类似的建立四个文件和文件组,并把每一个存储数据的文件放在不同的磁盘驱动器里。

创建分区函数

创建分区表必须先确定分区的功能机制,表进行分区的标准是通过分区函数来决定的。创建数据分区函数有RANGE “LEFT | / RIGHT”两种选择。代表每个边界值在局部的哪一边。例如存在四个分区,则定义三个边界点值,并指定每个值是第一个分区的上边界 (LEFT) 还是第二个分区的下边界 (RIGHT)[1]。代码如下:
 

CREATE PARTITION FUNCTION [SendSMSPF](datetime) AS RANGE RIGHT FOR VALUES ('20070401', '20070701', '20071001')

创建分区方案

创建分区函数后,必须将其与分区方案相关联,以便将分区指向至特定的文件组。就是定义实际存放数据的媒体与各数据块的对应关系。多个数据表可以共用相同的数据分区函数,一般不共用相同的数据分区方案。可以通过不同的分区方案,使用相同的分区函数,使不同的数据表有相同的分区条件,但存放在不同的媒介上。创建分区方案的代码如下:
 

CREATE PARTITION SCHEME [SendSMSPS] AS PARTITION [SendSMSPF] TO ([FG1], [FG2], [FG3], [FG4])

创建分区表

建立好分区函数和分区方案后,就可以创建分区表了。分区表是通过定义分区键值和分区方案相联系的。插入记录时,SQL SERVER会根据分区键值的不同,通过分区函数的定义将数据放到相应的分区。从而把分区函数、分区方案和分区表三者有机的结合起来。
创建分区表的代码如下:
 

  CREATE TABLE SendSMSLog
  ([ID] [int] IDENTITY(1,1) NOT NULL,
  [IDNum] [nvarchar](50) NULL,
  [SendContent] [text] NULL
  [SendDate] [datetime] NOT NULL,
  ) ON SendSMSPS(SendDate)

查看分区表信息

系统运行一段时间或者把以前的数据导入分区表后,我们需要查看数据的具体存储情况,即每个分区存取的记录数,那些记录存取在那个分区等。我们可以通过$partition.SendSMSPF来查看,代码如下:
 

SELECT $partition.SendSMSPF(o.SendDate)
  AS [Partition Number]
  , min(o.SendDate) AS [Min SendDate]
  , max(o.SendDate) AS [Max SendDate]
  , count(*) AS [Rows In Partition]
  FROM dbo.SendSMSLog AS o
  GROUP BY $partition.SendSMSPF(o.SendDate)
  ORDER BY [Partition Number]  

维护分区

分区的维护主要设计分区的添加、减少、合并和在分区间转换。可以通过ALTER PARTITION FUNCTION的选项SPLIT,MERGE和ALTER TABLE的选项SWITCH来实现。SPLIT会多增加一个分区,而MEGRE会合并或者减少分区,SWITCH则是逻辑地在组间转换分区。

性能对比

我们对2650万数据,存储空间占用约4G的单表进行性能对比,测试环境为IBM365,CPU 至强2.7G*2、内存 16G、硬盘 136G*2,系统平台为Windows 2003 SP1+SQL Server 2005 SP1。
测试结果如表1:
表1:分区和未分区性能对比表(单位:毫秒)
 

测试项目 分区 未分区
1 16546 61466
2 13 33
3 20140 61546
4 17140 61000

说明:
 

1:根据时间检索某一天记录所耗时间
2:单条记录插入所耗时间
3:根据时间删除某一天记录所耗时间
4:统计每月的记录数所需时间

从表1可以看出,对分区表进行操作比未分区的表要快,这是因为对分区表的操作采用了CPU和I/O的并行操作,检索数据的数据量也变小了,定位数据所耗时间变短。


    
 
 
 
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • sql2005创建远程登录帐户的sql语句分享
  • 创建 sql server 链接服务器的sql代码
  • sql server 2008:您对无法重新创建的表进行了更改或者启用了“阻止保存重新创建表的更改”
  • sql server 创建主键(uuid和自增长)
  • 为什么用sqlserver生成的创建表的sql语句中都不包含主键信息???
  • sql server创建复合主键的2种方法
  • Sql Server 创建数据库脚本Create DATABASE
  • 关于创建数据库(SQL)和表的问题
  • SQL创建的几种存储过程
  • SQL Server创建链接服务器的存储过程示例分享
  • 使用sql语句创建和删除约束示例代码
  • 比较详细的完美解决安装sql2000时出现以前的某个程序安装已在安装计算机上创建挂起的文件操作。 原创
  • Oracle创建主键自增表(sql语句实现)及触发器应用
  • SQL SERVER先判断视图是否存在然后再创建视图的语句
  • SQL学习笔记二 创建表、插入数据的语句
  • sql2008数据库创建与删除链接服务器
  • sql视图创建索引的方法
  • T-SQL语句创建数据库的例子(图文)
  • SQL语句创建Oracle Spatial的图层方法简介
  • sql server创建表、删除表的语句
  • java命名空间java.sql接口statement的类成员方法: executeupdate定义及介绍
  • 请问,这是什么错误!java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][Named Pipes]??????? SQL Server?虽然分少,但一定给,只要您是前5名回复者中最好的以为!
  • java命名空间java.sql接口connection的类成员方法: nativesql定义及介绍
  • SQL查询分析工具 SQL Workbench/J
  • java命名空间java.sql接口preparedstatement的类成员方法: executeupdate定义及介绍
  • oracle导出sql语句的结果集和保存执行的sql语句(深入分析)
  • java命名空间java.sql接口rowid的类成员方法: getbytes定义及介绍
  • SQL Server统计SQL语句执行时间的脚本
  • java命名空间java.sql接口ref的类成员方法: getbasetypename定义及介绍
  • SQL客户端软件 PKLite SQL Client
  • java命名空间java.sql接口databasemetadata的类成员方法: getsqlkeywords定义及介绍


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,,E-mail:www_#163.com(请将#改为@)

    浙ICP备11055608号-3