http://hi.baidu.com/tmxkhd666/blog/item/6b33d616e3071411972b43e6.html
你是否在千方百计优化SQL Server 数据库的性能?如果你的数据库中含有大量的表格,把这些表格分区放入独立的文件组可能会让你受益匪浅。SQL Server 2005引入的表分区技术,让用户能够把数据分散存放到不同的物理磁盘中,提高这些磁盘的并行处理性能以优化查询性能。
SQL Server数据库表分区操作过程由三个步骤组成: 1. 创建分区函数 2. 创建分区架构 3. 对表进行分区 下面将对每个步骤进行详细介绍。 步骤一:创建一个分区函数 此分区函数用于定义你希望SQL Server如何对数据进行分区的参数值([u]how[/u])。这个操作并不涉及任何表格,只是单纯的定义了一项技术来分割数据。 我们可以通过指定每个分区的边界条件来定义分区。例如,假定我们有一份Customers表,其中包含了关于所有客户的信息,以一一对应的客户编号(从1到1,000,000)来区分。我们将通过以下的分区函数把这个表分为四个大小相同的分区:
这些边界值定义了四个分区。第一个分区包括所有值小于250,000的数据,第二个分区包括值在250,000到49,999之间的数据。第三个分区包括值在500,000到7499,999之间的数据。所有值大于或等于750,000的数据被归入第四个分区。 请注意,这里调用的"RANGE RIGHT"语句表明每个分区边界值是右界。类似的,如果使用"RANGE LEFT"语句,则上述第一个分区应该包括所有值小于或等于250,000的数据,第二个分区的数据值在250,001到500,000之间,以此类推。 步骤二:创建一个分区架构 一旦给出描述如何分割数据的分区函数,接着就要创建一个分区架构,用来定义分区位置([u]where[/u])。创建过程非常直截了当,只要将分区连接到指定的文件组就行了。例如,如果有四个文件组,组名从"fg1"到"fg4",那么以下的分区架构就能达到想要的效果:
注意,这里将一个分区函数连接到了该分区架构,但并没有将分区架构连接到任何数据表。这就是可复用性起作用的地方了。无论有多少数据库表,我们都可以使用该分区架构(或仅仅是分区函数)。 步骤三:对一个表进行分区 定义好一个分区架构后,就可以着手创建一个分区表了。这是整个分区操作过程中最简单的一个步骤。只需要在表创建指令中添加一个"ON"语句,用来指定分区架构以及应用该架构的表列。因为分区架构已经识别了分区函数,所以不需要再指定分区函数了。 例如,使用以上的分区架构创建一个客户表,可以调用以下的Transact-SQL指令:
关于SQL Server的表分区功能,你知道上述的相关知识就足够了。记住!编写能够用于多个表的一般的分区函数和分区架构就能够大大提高可复用性。 http://hi.baidu.com/tmxkhd666/blog/item/9d5a4dfb04d9146d034f56f7.html SQL2005 表分区
2007-07-13 22:35
|
http://hi.baidu.com/yylzq/blog/item/c4877a83bc96749af703a650.html
代码加注释,希望对初学者有用。
USE [master]
GO if exists (select * from sys.databases where name = 'Test_1') drop database Test_1 GO --创建新库,要演练分区所以我们会多创建两个文件组Test_A,Test_B,以便在后面的分区方案中使用。 CREATE DATABASE [Test_1] ON PRIMARY ( NAME = N'test_1', FILENAME = N'D:/sqldata/test_1.mdf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), FILEGROUP [test_A] ( NAME = N'Test_A', FILENAME = N'D:/sqldata/test_A.ndf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), FILEGROUP [test_B] ( NAME = N'Test_B', FILENAME = N'D:/sqldata/test_B.ndf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'Test_log', FILENAME = N'D:/sqldata/Test_log.ldf' , SIZE = 7616KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) COLLATE Chinese_PRC_CI_AS GO USE [Test_1] GO --若分区函数存在则先drop掉 IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'test_partition') DROP PARTITION FUNCTION [test_partition] GO /*创建分区函数给后面的分区方案使用,分区函数很简单就是指定一个范围确定在某个值为什么的时候放在那个分区上*/ --新建一个简单的分区函数,该函数以1000为界分两个区 create partition function test_partition(int) AS RANGE LEFT FOR VALUES (1000) go /*看分区方案是否存在,若存在先drop掉*/ IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'test_scheme') DROP PARTITION SCHEME test_scheme GO --创建分区方案,分区方案需要指定一个分区函数,并指定在分区函数中分的区需要放在哪一个文件组上 create partition scheme test_scheme AS PARTITION [test_partition] TO (test_A,test_B) GO --创建分区表 if object_id('student','U') is not null drop table student; go create table student ( id int identity(1,1) not null, name varchar(10) not null, class int not null, grade int ) on test_scheme(class) --在此处指定该表要使用的分区方案,并将指定分区依据列 go --随便插入几条数据 insert into student values ('AQU',10,100); -- 这条数据在A分区上 insert into student values ('AQU_边界',1000,89); -- 这边数据也在A分区上是个边界,因为我们上面在函数中指定的是RANGE LEFT,所以1000在A分区上 insert into student values ('BQU',1001,90); -- 这一条肯定是在B分区上了。 go --最后看看结果。$partition.分区函数(分区列)可以返回某一行所在的分区序号 select *,分区序号 = $partition.test_partition(class) from student GO |
http://hi.baidu.com/mr%5Findigo/blog/item/4d6b4d37490154380b55a90d.html
当一个表里的数据很多时,可以将其分拆到
多个的表里,大大提高了性能。下面举例子说明之 比如,在C盘下建立如下几个目录 其中primary存放的是主数据库文件,其他FG1--FG4存放四个单独的文件组,可以见创立数据库 USE [master] GO /****** Object: Database [Data Partition DB] Script Date: 10/08/2006 23:09:53 ******/ IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Data Partition DB2') DROP DATABASE [Data Partition DB2] GO CREATE DATABASE [Data Partition DB2] ON PRIMARY (NAME='Data Partition DB Primary FG', FILENAME= 'C:/Data2/Primary/Data Partition DB Primary FG.mdf', SIZE=5, MAXSIZE=500, FILEGROWTH=1 ), FILEGROUP [Data Partition DB FG1] (NAME = 'Data Partition DB FG1', FILENAME = 'C:/Data2/FG1/Data Partition DB FG1.ndf', SIZE = 5MB, MAXSIZE=500, FILEGROWTH=1 ), FILEGROUP [Data Partition DB FG2] (NAME = 'Data Partition DB FG2', FILENAME = 'C:/Data2/FG2/Data Partition DB FG2.ndf', SIZE = 5MB, MAXSIZE=500, FILEGROWTH=1 ), FILEGROUP [Data Partition DB FG3] (NAME = 'Data Partition DB FG3', FILENAME = 'C:/Data2/FG3/Data Partition DB FG3.ndf', SIZE = 5MB, MAXSIZE=500, FILEGROWTH=1 ), FILEGROUP [Data Partition DB FG4] (NAME = 'Data Partition DB FG4', FILENAME = 'C:/Data2/FG4/Data Partition DB FG4.ndf', SIZE = 5MB, MAXSIZE=500, FILEGROWTH=1 ) 接下来,我们创建分区表函数,这其实可以理解为一个规则,说明如何以一个规则来将一个表来划分,如下 而VALUES (100,200,300)表明,将把表分为4个区了,是从负数到100,100-200,200-300,大于300。 接下来,我们要创建分区架构,即将分区函数应用到我们分好的四个文件组里面去 USE [Data Partition DB2] go CREATE PARTITION SCHEME [Data Partition Scheme] AS PARTITION [Data Partition Range] TO ([Data Partition DB FG1], [Data Partition DB FG2], [Data Partition DB FG3],[Data Partition DB FG4]); 再创建表的结构 go 这里注意,ON [Data Partition Scheme] (ID);表明,划分时以ID的大小作为划分的根据,ON后要跟分区架购的名称 最后,我们可以填充数据了 USE [Data Partition DB2] go declare @count int set @count =-25 while @count <=100 begin insert into MyTable select @count,getdate(),100.00 set @count=@count+1 end set @count =101 while @count <=200 begin insert into MyTable select @count,getdate(),200.00 set @count=@count+1 end set @count =201 while @count <=300 begin insert into MyTable select @count,getdate(),300.00 set @count=@count+1 end set @count =301 while @count <=400 begin insert into MyTable select @count,getdate(),400.00 set @count=@count+1 end set @count =401 while @count <=800 begin insert into MyTable select @count,getdate(),500.00 set @count=@count+1 end 最后,我们可以查询下,插入的这些数据,是否真的被划分到四个不同的文件组里的表分区了,可以这样看 |
http://blog.csdn.net/wufeng4552/archive/2009/10/26/4728248.aspx
SQL Server2005 表分区三步曲 收藏
--> 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)。这个操作并不涉及任何表格,只是单纯的定义了一项技术来分割数据。
--刪除表
if object_id('tb_partition1')is not null drop table tb_partition1
go
if object_id('tb_partition2')is not null drop table tb_partition2
go
--刪除架構
If exists(Select 1 from sys.partition_schemes where name='my_psch')
drop partition scheme my_psch
go
--刪除分區函數
if exists(select 1 from sys.partition_functions where name='my_pfun')
drop partition function my_pfun
--建立分區函數
create partition function my_Pfun(datetime)
as range left
for values('2007-12-31')
注意:这里调用的"RANGE RIGHT"语句表明每个分区边界值是右界。类似的,如果使用"RANGE LEFT"语句,则上述第一个分区应该包括所有值小于或等于'2004-01-01'数据,以此类推.
(二):创建一个分区架构(物理结构)
一旦给出描述如何分割数据的分区函数,接着就要创建一个分区架构,用来定义分区位置(where)。创建过程非常直截了当,只要将分区连接到指定的文件组就行了。
--建立分區架構
go
create partition scheme my_psch
as partition my_pfun
to([Primary],[Primary])
/*
1,建立分区函数,分区方案是有先后顺序的。
2,分区函数提供的值的数目n,不能超过 999。所创建的分区数等于 n + 1
*/
注意:这里将一个分区函数连接到了该分区架构,但并没有将分区架构连接到任何数据表。这就是可复用性起作用的地方了。无论有多少数据库表,我们都可以使用该分区架构(或仅仅是分区函数)。
(三):对一个表进行分区
定义好一个分区架构后,就可以着手创建一个分区表了。只需要在表创建指令中添加一个"ON"语句,用来指定分区架构以及应用该架构的表列。因为分区架构已经识别了分区函数,所以不需要再指定分区函数了。
create table tb_partition1
(id int identity,
dt datetime,
[name]as 'Name'+ltrim(ID),
constraint pk_tbpartition1 primary key clustered(ID,dt)on my_psch(dt)
)on my_psch(dt)
go
create table tb_partition2
(id int identity,
dt datetime,
[name]as 'Name'+ltrim(ID),
constraint pk_tbpartition2 primary key clustered(ID,dt)on my_psch(dt)
)on my_psch(dt)
--为此表填充数
declare @dt datetime
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
Where object_id In
(Select object_id From sys.tables Where name 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
Alter Table tb_partition1
Switch Partition 2 To tb_partition2 Partition 2
go
select * from tb_partition1
select * from tb_partition2
修改三步曲
1 添加一个文件组到数据库(可選)
2 修改分区Scheme
3 修改分区函数
--1 添加一个文件组到数据库
--2 修改分区Scheme
--把大於/12/31的分区改分为2个分区
--3 修改分区函数
Alter Partition Function My_pfun()
Alter Partition Scheme my_psch
Next Used [Primary]
go
Split Range('2008/12/31')
Go
--把小于2008/12/31的两分区合并
Alter Partition Function My_PFun()
Merge Range('2007/12/31')
http://blog.csdn.net/jia_guijun/archive/2008/09/02/2865748.aspx
对数据记录数量超大的表进行操作,会感到反应非常的慢,而对表进行分区,则是一个较好的解决方法。
set statistics time on
set statistics io on
--创建分区函数
create partition function My_Fun(int) as range right for values(1,100,1000)
go
--创建分区方案
create partition scheme My_Sch as partition my_fun all to ([PRIMARY])
--查看第2个分区内容
select * from t1 where $partition.my_fun(colname)=2
--检查每个分区中有多少行
select $partition.my_fun(colname),count(*) from tablename group by $partition.my_fun(colname)
--确定包含特定分区键值的行位于哪个分区中
select $partition.my_fun(500)
--增加分区
--1.增加分组到分区方案中(如果为每个分区都指定了特定的文件组,则执行此步)
alter partition scheme my_sch next used 'primary'
--2.增加函数的分区值
alter partition function my_fun() split range(500)
--合并分区
alter partition function my_fun() merge range(1)
--创建分区表
create table tablename(col1 int,col2 char(10)) on my_sch(col1)
--创建分区索引
--不在分区列上建
create index ix_col2 on tablename(col2) on my_sch(col1)
--在分区列上建
create index ix_col1 on tablename(col1)
--移动table1表中第一个分区中的数据到table2表的第二个分区
alter table table1 switch partition 1 to table2 partition 2
/************对已经存储大量数据的表进行分区***************/
--准备分区的表必须存在一个聚集索引。如果没有,则用以下脚本建立
CREATE UNIQUE CLUSTERED INDEX ix_t2 ON t2(col1 ASC)
--把原来的表移动到这个表分区里
Drop index T2_IXC on Table1 with (Move To my_sch(col1))
--查看
select * from sys.partitions where object_name(object_id)='t2'