首页 > 代码库 > Sqlserver2008 表分区教程

Sqlserver2008 表分区教程

2024-11-13 15:33:02   203人阅读

先声明..Sql2008只有企业版才能够搞这个表分区.其他版本请自觉更改..

哥在这里费了很长时间劲..结果还是老老实实的重装..

  表分区定义

一般情况下，我们建立数据库表时，表数据都存放在一个文件里。

但是如果是分区表的话，表数据就会按照你指定的规则分放到不同的文件里，把一个大的数据文件拆分为多个小文件，还可以把这些小文件放在不同的磁盘下由多个cpu进行处理。这样文件的大小随着拆分而减小，还得到硬件系统的加强，自然对我们操作数据是大大有利的。

所以大数据量的数据表，对分区的需要还是必要的，因为它可以提高select效率，还可以对历史数据经行区分存档等。但是数据量少的数据就不要凑这个热闹啦，因为表分区会对数据库产生不必要的开销，除啦性能还会增加实现对象的管理费用和复杂性。

   分区是要把一个表数据拆分为若干子集合，也就是把把一个数据文件拆分到多个数据文件中，然而这些文件的存放可以依托一个文件组或这多个文件组，由于多个文件组可以提高数据库的访问并发量，还可以把不同的分区配置到不同的磁盘中提高效率，所以创建时建议分区跟文件组个数相同。

  开始做 

 1..建立文件组   

alterdatabase<数据库名>add filegroup <文件组名>
例:
alter database XStock add filegroup fg_Xtock_User_1
alter database XStock add filegroup fg_Xtock_User_2
alter database XStock add filegroup fg_Xtock_User_3 
要建多少个分区就有多少文件组
我自己比较喜欢命名明了一些..因为同一个数据库可能会有多个表都要建立分区..所以在名称上采取fg_数据库_表_编号的格式..个人请随意..

  2..添加文件进文件组

alter database <数据库名称> add file <数据标识> to filegroup <文件组名称>

例:
alterdatabase XStock addfile (name=N‘f_Xtock_User_1‘,filename=N‘E:\Work\数据库\XStock\f_Xtock_User_1.ndf‘,size=5Mb,filegrowth=5mb) to filegroup fg_Xtock_User_1
alter database XStock add file (name=N‘f_Xtock_User_2‘,filename=N‘E:\Work\数据库\XStock\f_Xtock_User_2.ndf‘,size=5Mb,filegrowth=5mb) to filegroup fg_Xtock_User_2
alter database XStock add file (name=N‘f_Xtock_User_3‘,filename=N‘E:\Work\数据库\XStock\f_Xtock_User_3.ndf‘,size=5Mb,filegrowth=5mb) to filegroup fg_Xtock_User_3

文件数量也是跟文件组一样的..
文件地址要注意..我的习惯是在当前数据库的物理文件地址上新建一个文件夹..以数据库命名..然后分组的文件件在这个文件夹里..
这样做有一个好处..当你的数据库要搬迁的时候..譬如普通的备份操作..在备份的时候..系统并不能把这些分组文件一起打包..
这些文件是要随着备份文件一起复制到其他地方的..在那边还原数据库的时候要有的..不然是还原不了的

 3..使用向导做最后一步

 右键到要分区的表--- >> 存储 --- >> 创建分区 --- >>显示向导视图 --- >> 下一步 --- >> 下一步。。

 技术分享

这里举例说下选择列的意思：

 

假如你选择的是int类型的列：那么你的分区可以指定为1--100W是一个分区，100W--200W是一个分区....

假如你选择的是datatime类型：那么你的分区可以指定为：2014-01-01--2014-01-31一个分区，2014-02-01--2014-02-28一个分区...

根据这样的列数据规则划分，那么在那个区间的数据，在插入数据库时就被指向那个分区存储下来。

 

我这里选用orderid int类型 --- >> 下一步 --- >>

技术分享

技术分享

技术分享

左边界右边界：就是把临界值划分给上一个分区还是下一个分区。一个小于号，一个小于等于号。

然后下一步下一步最后你会得到分区函数和分区方案。

USE [testSplit]
GO
BEGIN TRANSACTION

--创建分区函数
CREATE PARTITION FUNCTION [bgPartitionFun](int) AS RANGE LEFT FOR VALUES (N‘1000000‘, N‘2000000‘, N‘3000000‘, N‘4000000‘, N‘5000000‘, N‘6000000‘, N‘7000000‘, N‘8000000‘, N‘9000000‘, N‘10000000‘)

--创建分区方案
CREATE PARTITION SCHEME [bgPartitionSchema] AS PARTITION [bgPartitionFun] TO ([PRIMARY], [ByIdGroup1], [ByIdGroup2], [ByIdGroup3], [ByIdGroup4], [ByIdGroup5], [ByIdGroup6], [ByIdGroup7], [ByIdGroup8], [ByIdGroup9], [ByIdGroup10])

--创建分区索引
CREATE CLUSTERED INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165] ON [dbo].[BigOrder] 
(
    [OrderId]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [bgPartitionSchema]([OrderId])

--删除分区索引
DROP INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165] ON [dbo].[BigOrder] WITH ( ONLINE = OFF )

COMMIT TRANSACTION

 执行上面向导生成的语句。分区完成。。

 4..看速度

        在查询语句前后加上反应查询详细信息的语句,可以清楚的反应出查询的效率

set statistics io on   --显示性能  开启
--查询语句
set statistics io off   --关闭

-- 或者

set statistics time on   --显示时间  开启
--查询语句
set statistics time off   --关闭

--也可以两个一起套用

    记得在看之前.清除数据库的缓存..

    数据库在每一次查询之后都会有缓存区..那么在第二次查询的时候就会快很多.. 

--从缓冲池中删除所有清除缓冲区。 
DBCC DROPCLEANBUFFERS

--删除计划高速缓存中的所有元素
DBCC FREEPROCCACHE
--从所有缓存中释放所有未使用的缓存条目
DBCC FREESYSTEMCACHE( ‘ALL‘ )

到了这里数据库表分区就建完了..

     如果还想有更加延伸的学习..可以访问博友 张龙豪 的博文《SQL Server表分区》..

     http://www.cnblogs.com/knowledgesea/p/3696912.html  

     本文也只能算是此文章的学习笔记罢了..

 

Sqlserver2008 表分区教程


联系
我们