SQL Server2005 表分区三步曲
来源:互联网 发布:sql server 表别名 编辑:程序博客网 时间:2024/06/06 09:27
-> Title : SQL Server2005 表分区三步曲
--> Author : wufeng4552
--> Date : 2009-10-26
前言
SQL Server 2005开始支持表分区,这种技术允许所有的表分区都保存在同一台服务器上。每一个表分区都和在某个文件组(filegroup)中的单个文件关联。同样的一个文件/文件组可以容纳多个分区表。在这种设计架构下,数据库引擎能够判定查询过程中应该访问哪个分区,而不用扫描整个表。如果查询需要的数据行分散在多个分区中,SQL Server使用多个处理器对多个分区进行并行查询。你可以为在创建表的时候就定义分区的索引。对小索引的搜索或者扫描要比扫描整个表或者一张大表上的索引要快很多。因此,当对大表进行查询,表分区可以产生相当大的性能提升
通过分别检查同一条返回所有行的、简单SELECT语句在分区表和非分区表上的执行计划,返回的数据范围通过WHERE语句来指定。同一条语句在这两个不同的表上有不同的执行计划。对于分区表的查询显示出一个嵌套的循环和索引的扫描。从本质上来说,SQL Server将两个分区视为独立的表,因此使用一个嵌套循环将它们连接起来。对非分区的表的同一个查询则使用索引扫描来返回同样的列。当你使用同样的分区策略创建多个表,同时在查询中连接这些表,那么性能上的提升会更加明显
分区請三思
1.虽然分区可以带来众多的好处,但是同进也增加了实现对象的管理费用和复杂性。因此在进行分区之前要首先仔细的考虑以确定是否应为对象进行分区。
2.在确定了为对象进行分区后,下一步就要确定分区键和分区数。要确定分区数据,应先评估您的数据中是否存在逻辑分组和模式。
3.确定是否应使用多个文件分组。为了有助于优化性能和维护,应使用文件组分离数据。文件组是数据库数据文件的逻辑组合,它可以对数据文件进行管理和分配,以便提高数据库文件的并发访问效率。
分区三步曲
SQL Server数据库表分区操作过程由三个步骤组成:
1. 创建分区函数
2. 创建分区架构
3. 对表进行分区
(一):创建一个分区函数(逻辑结构)
此分区函数用于定义你希望SQL Server如何对数据进行分区的参数值(how)。这个操作并不涉及任何表格,只是单纯的定义了一项技术来分割数据。
--刪除表
ifobject_id('tb_partition1')isnot null drop table tb_partition1
go
ifobject_id('tb_partition2')isnot null drop table tb_partition2
go
--刪除架構
Ifexists(Select 1 from sys.partition_schemes where name='my_psch')
drop partitionscheme my_psch
go
--刪除分區函數
ifexists(select 1 from sys.partition_functions where name='my_pfun')
drop partitionfunction my_pfun
--建立分區函數
createpartition function my_Pfun(datetime)
asrange left
forvalues('2007-12-31')
注意:这里调用的"RANGE RIGHT"语句表明每个分区边界值是右界。类似的,如果使用"RANGE LEFT"语句,则上述第一个分区应该包括所有值小于或等于'2004-01-01'数据,以此类推.
(二):创建一个分区架构(物理结构)
一旦给出描述如何分割数据的分区函数,接着就要创建一个分区架构,用来定义分区位置(where)。创建过程非常直截了当,只要将分区连接到指定的文件组就行了。
--建立分區架構
go
createpartition scheme my_psch
aspartition my_pfun
to([Primary],[Primary])
/*
1,建立分区函数,分区方案是有先后顺序的。
2,分区函数提供的值的数目n,不能超过 999。所创建的分区数等于 n + 1
*/
注意:这里将一个分区函数连接到了该分区架构,但并没有将分区架构连接到任何数据表。这就是可复用性起作用的地方了。无论有多少数据库表,我们都可以使用该分区架构(或仅仅是分区函数)。
(三):对一个表进行分区
定义好一个分区架构后,就可以着手创建一个分区表了。只需要在表创建指令中添加一个"ON"语句,用来指定分区架构以及应用该架构的表列。因为分区架构已经识别了分区函数,所以不需要再指定分区函数了。
createtable tb_partition1
(idint identity,
dtdatetime,
[name]as'Name'+ltrim(ID),
constraint pk_tbpartition1primary key clustered(ID,dt)on my_psch(dt)
)on my_psch(dt)
go
createtable tb_partition2
(idint identity,
dtdatetime,
[name]as'Name'+ltrim(ID),
constraint pk_tbpartition2primary key clustered(ID,dt)on my_psch(dt)
)on my_psch(dt)
--为此表填充数
declare @dtdatetime
set @dt='2007-01-01'
while @dt<='2009-10-01'
begin
insert tb_partition1 select @dt
set @dt=dateadd(dd,1,@dt)
end
--查询表的分区狀况Select* from sys.partitions
Whereobject_id In
(Selectobject_id Fromsys.tables Wherename In('tb_partition1','tb_partition2'))
----现在我们可以看一下我们刚才插入的行都分布在哪个Partition
select*,$partition.my_pfun(dt)from tb_partition1
--切換分區
--切換前
select* from tb_partition1
select* from tb_partition2
----切換分區tb_partition1-->tb_partition2
AlterTable tb_partition1
SwitchPartition 2 To tb_partition2Partition 2
go
select* from tb_partition1
select* from tb_partition2
修改三步曲
1 添加一个文件组到数据库(可選)
2 修改分区Scheme
3 修改分区函数
--1添加一个文件组到数据库
--2修改分区Scheme
--把大於/12/31的分区改分为2个分区
--3修改分区函数
AlterPartition Function My_pfun()
AlterPartition Scheme my_psch
Next Used [Primary]
go
SplitRange('2008/12/31')
Go
--把小于2008/12/31的两分区合并
AlterPartition Function My_PFun()
MergeRange('2007/12/31')
转自http://blog.csdn.net/wufeng4552/archive/2009/10/26/4728248.aspx
- SQL Server2005 表分区三步曲
- SQL Server2005 表分区三步曲
- SQL Server2005 表分区三步曲
- SQL Server2005 表分区三步曲
- SQL Server2005表分区
- SQL Server2005 表分区
- SQL Server2005 表分区操作详解
- SQL Server2005 表分区操作详解
- SQL Server2005 表分区操作详解
- SQL Server2005 表分区(第一章)
- SQL Server2005 表分区(第二章)
- SQL Server2005 表分区(第三章)
- SQL Server2005 表分区(第四章)
- sql server2005 分区表 分区函数 分区架构
- 海量数据处理(转二)--SQL Server2005表分区法
- Sql Server2005 实现Oracle10g的hash表分区功能
- SQL Server2005表分区处理操作的5步骤
- MS SQL Server2005:分区表、分区索引
- 面向对象的五大原则
- LCD显示原理 && MTK平台LCM显存大小的计算 && mtk6589LCD显示子系统
- like优化2
- ASP.NET中Response的基本用法
- 将 Visual Studio C 和 C++ 项目迁移到 Eclipse CDT
- SQL Server2005 表分区三步曲
- GridView的数据格式
- 快速生成树之通用状态机
- 转载自wiki:Run Nutch In Eclipse on Linux and Windows nutch version 1.0
- Android: INSTALL_FAILED_UPDATE_INCOMPATIBLE
- IIS Tomcat 整合
- 最近
- 华为一道面试题
- 在b/s开发中经常用到的javaScript技术