沈阳网站营销推广,做网站公司做网站公司,专做土特产的网站,网页设计与制作字体设置你是不是也在苦苦寻求优化自己的SQL Server数据库的方法?如果你的数据库里有不少非常大的表格#xff0c;分区功能能够帮到你很大的忙…… 【IT专家网独家】你是不是也在苦苦寻求优化自己的SQL Server数据库的方法?如果你的数据库里有不少非常大的表格#xff0c;分区功能能…你是不是也在苦苦寻求优化自己的SQL Server数据库的方法?如果你的数据库里有不少非常大的表格分区功能能够帮到你很大的忙…… 【IT专家网独家】你是不是也在苦苦寻求优化自己的SQL Server数据库的方法?如果你的数据库里有不少非常大的表格分区功能能够帮到你很大的忙因为它可以把这些大表格分割成独立文件组。这个技术可以让你把数据分布在不同的物理磁盘中并通过调节它们的并行性能来最优化你的查询性能。 对SQL Server数据表进行分区的过程分为三个步骤 1)建立分区函数 2)建立分区方案 3)对表格进行分区 第一个步骤建立分区函数 分区函数定义[u]how[/u]即你想要SQL Server如何对数据进行分区。这里就不以某一个表格作为例子而是总体概括分割数据的技术。 分区是通过指定每个分区的分割界线实现的。例如假定我们有一个Customers表格里面包含了企业所有的客户的信息客户信息以唯一的客户号进行辨识客户号从1到1000000。我们可以运用以下的分区函数(这里称之为customer_Partfunc)把这个表格平均分为四个分区 CREATE PARTITION FUNCTION customer_partfunc (int) AS RANGE RIGHT FOR VALUES (250000, 500000, 750000) 这些分割界线指定了四个分区。第一个分区包含所有值小于250000的记录。第二个分区包含所有值在250000和499999之间的记录。而第三个分区包含所有值在500000和749999之间的记录。其他所有大于或等于750000的记录都包含在第四个分区里。 注意这个例子中使用了“RANGE RIGHT”从句。这说明分界值是在分区的右边。同样如果使用的是“RANGE LEFT”从句那么第一个分区就会包含所有值小于或等于250000的记录;第二个分区就会包含所有值在250001和500000之间的记录如此类推。 第二个步骤建立分区方案 一旦建立完定义如何对数据进行分区的分区函数之后下一步就是建立一个分区方案定义[u]where[/u]即你想在哪里对数据进行分区。这是一个很直接明了的过程例如如果我有四个文件组名称分别从“fg1”到“fg4”那么就可以使用以下分区方案 CREATE PARTITION SCHEME customer_partscheme AS PARTITION customer_partfunc TO (fg1, fg2, fg3, fg4) 注意我们现在把一个分区函数连接到了分区方案但是我们还没有把分区方案连接到任何具体的数据库表格。这就是重复使用功能发挥功能的时候。我们可以通过这个功能把分区方案(或者只是分区函数)用于数据库表格的任何数据上。 第三个步骤对表格进行分区 建立好分区方案之后就可以开始对表格进行分区了。这是最简单的一个步骤只需要在表格创建语句中添加“ON”从句指定表格分区方案和要应用该分区方案的表列。你不需要指定分区函数因为分区方案已经定义了分区函数。 举个例子假设你想要用上述的分区方案来创建一个客户表格你需要使用以下Transact-SQL语句 CREATE TABLE customers (FirstName nvarchar(40), LastName nvarchar(40), CustomerNumber int) ON customer_partscheme (CustomerNumber) 超大型数据库的大小常常达到数百GB有时甚至要用TB来计算。而单表的数据量往往会达到上亿的记录并且记录数会随着时间而增长。这不但影响着数据库的运行效率也增大数据库的维护难度。除了表的数据量外对表不同的访问模式也可能会影响性能和可用性。这些问题都可以通过对大表进行合理分区得到很大的改善。当表和索引变得非常大时分区可以将数据分为更小、更容易管理的部分来提高系统的运行效率。如果系统有多个CPU或是多个磁盘子系统可以通过并行操作获得更好的性能。所以对大表进行分区是处理海量数据的一种十分高效的方法。本文通过一个具体实例介绍如何创建和修改分区表以及如何查看分区表。 1 SQL Server 2005 strong classkgb οnmοuseοverisShowAds false;isShowAds2 false;isShowGg true;InTextAds_GgLayerSQL_20Server;KeyGate_ads.ShowGgAds(this,SQL_20Server,event) styleborder-right: 0px; padding-right: 0px; border-top: 0px; padding-left: 0px; font-weight: normal; padding-bottom: 0px; margin: 0px; border-left: 0px; cursor: hand; color: #0000ff; padding-top: 0px; border-bottom: 0px; text-decoration: underline οnclickjavascript:window.open(http://pagead2.googlesyndication.com/pagead/iclk?salaiBDTQhftmkSOC3NI3-vAOPvKjiB7bmnEG21sHaBcCNtwHAuAIQARgBIKy8kQooFDgAUMKCtur9_____wFgnaHfgdgFsgEPd3d3LmNuYmxvZ3MuY29tyAEB2gE7aHR0cDovL3d3dy5jbmJsb2dzLmNvbS8xMzU5MC9hcmNoaXZlLzIwMDcvMDcvMDkvODEwNzcwLmh0bWypAsAaeNpXkIM-yAKAi_ADqAMB6APXAugDjAPoAw3oAwXoAwOIBAGQBAGYBAAnum1adurlhttp://www.ebackup.com.cn/shujukupro.aspclientca-pub-1681215984289622);GgKwClickStat(SQL Server,www.ebackup.com.cn,afc,2000072864); οnmοuseοutisShowGg false;InTextAds_GgLayerSQL_20ServerSQL Server 2005是微软在推出SQL Server 2000后时隔五年推出的一个 strong classkgb οnmοuseοverisShowAds false;isShowAds2 false;isShowGg true;InTextAds_GgLayer_u6570_u636E_u5E93;KeyGate_ads.ShowGgAds(this,_u6570_u636E_u5E93,event) styleborder-right: 0px; padding-right: 0px; border-top: 0px; padding-left: 0px; font-weight: normal; padding-bottom: 0px; margin: 0px; border-left: 0px; cursor: hand; color: #0000ff; padding-top: 0px; border-bottom: 0px; text-decoration: underline οnclickjavascript:window.open(http://pagead2.googlesyndication.com/pagead/iclk?salaiBDTQhftmkSOC3NI3-vAOPvKjiB7bmnEG21sHaBcCNtwHAuAIQARgBIKy8kQooFDgAUMKCtur9_____wFgnaHfgdgFsgEPd3d3LmNuYmxvZ3MuY29tyAEB2gE7aHR0cDovL3d3dy5jbmJsb2dzLmNvbS8xMzU5MC9hcmNoaXZlLzIwMDcvMDcvMDkvODEwNzcwLmh0bWypAsAaeNpXkIM-yAKAi_ADqAMB6APXAugDjAPoAw3oAwXoAwOIBAGQBAGYBAAnum1adurlhttp://www.ebackup.com.cn/shujukupro.aspclientca-pub-1681215984289622);GgKwClickStat(数据库,www.ebackup.com.cn,afc,2000072864); οnmοuseοutisShowGg false;InTextAds_GgLayer_u6570_u636E_u5E93数据库 平台它的 strong classkgb οnmοuseοverisShowAds false;isShowAds2 false;isShowGg true;InTextAds_GgLayer_u6570_u636E;KeyGate_ads.ShowGgAds(this,_u6570_u636E,event) styleborder-right: 0px; padding-right: 0px; border-top: 0px; padding-left: 0px; font-weight: normal; padding-bottom: 0px; margin: 0px; border-left: 0px; cursor: hand; color: #0000ff; padding-top: 0px; border-bottom: 0px; text-decoration: underline οnclickjavascript:window.open(http://pagead2.googlesyndication.com/pagead/iclk?salaiBDTQhftmkSOC3NI3-vAOPvKjiB7bmnEG21sHaBcCNtwHAuAIQARgBIKy8kQooFDgAUMKCtur9_____wFgnaHfgdgFsgEPd3d3LmNuYmxvZ3MuY29tyAEB2gE7aHR0cDovL3d3dy5jbmJsb2dzLmNvbS8xMzU5MC9hcmNoaXZlLzIwMDcvMDcvMDkvODEwNzcwLmh0bWypAsAaeNpXkIM-yAKAi_ADqAMB6APXAugDjAPoAw3oAwXoAwOIBAGQBAGYBAAnum1adurlhttp://www.ebackup.com.cn/shujukupro.aspclientca-pub-1681215984289622);GgKwClickStat(数据,www.ebackup.com.cn,afc,2000072864); οnmοuseοutisShowGg false;InTextAds_GgLayer_u6570_u636E数据 库引擎为关系型数据和结构化数据提供了更安全可靠的存储功能使 strong classkgb οnmοuseοverisShowAds false;isShowAds2 false;isShowGg true;InTextAds_GgLayer_u7528_u6237;KeyGate_ads.ShowGgAds(this,_u7528_u6237,event) styleborder-right: 0px; padding-right: 0px; border-top: 0px; padding-left: 0px; font-weight: normal; padding-bottom: 0px; margin: 0px; border-left: 0px; cursor: hand; color: #0000ff; padding-top: 0px; border-bottom: 0px; text-decoration: underline οnclickjavascript:window.open(http://pagead2.googlesyndication.com/pagead/iclk?salaiBVtzvftmkSOC3NI3-vAOPvKjiB7TW-0247cDxBsCNtwGgnAEQAxgDIKy8kQooFDgAUIXNkPz______wFgnaHfgdgFsgEPd3d3LmNuYmxvZ3MuY29tyAEB2gE7aHR0cDovL3d3dy5jbmJsb2dzLmNvbS8xMzU5MC9hcmNoaXZlLzIwMDcvMDcvMDkvODEwNzcwLmh0bWyAAgGoAwHoA9cC6AOMA-gDDegDBegDA4gEAZAEAZgEAAnum3adurlhttp://www.macau.com/index.php%3Foption%3Dcom_casinos%26Itemid%3D182%26task%3Dshow_details%26id%3D10%26lang%3Ds_chineseclientca-pub-1681215984289622);GgKwClickStat(用户,www.macau.com,afc,2000072864); οnmοuseοutisShowGg false;InTextAds_GgLayer_u7528_u6237用户 可以构建和管理用于业务的高可用和高性能的数据应用程序。此外SQL Server 2005结合了分析、报表、集成和通知功能。这使企业可以构建和部署经济有效的BI strong classkgb οnmοuseοverisShowAds false;isShowAds2 false;isShowGg true;InTextAds_GgLayer_u89E3_u51B3_u65B9_u6848;KeyGate_ads.ShowGgAds(this,_u89E3_u51B3_u65B9_u6848,event) styleborder-right: 0px; padding-right: 0px; border-top: 0px; padding-left: 0px; font-weight: normal; padding-bottom: 0px; margin: 0px; border-left: 0px; cursor: hand; color: #0000ff; padding-top: 0px; border-bottom: 0px; text-decoration: underline οnclickjavascript:window.open(http://pagead2.googlesyndication.com/pagead/iclk?salaiBtJXaftmkSOC3NI3-vAOPvKjiB9XjqmXFz8myBMCNtwGgnAEQBhgGIKy8kQooFDgAUPCpzdT6_____wFgnaHfgdgFoAGj2vX-A7IBD3d3dy5jbmJsb2dzLmNvbcgBAdoBO2h0dHA6Ly93d3cuY25ibG9ncy5jb20vMTM1OTAvYXJjaGl2ZS8yMDA3LzA3LzA5LzgxMDc3MC5odG1sgAIBqAMB6APXAugDjAPoAw3oAwXoAwOIBAGQBAGYBAAnum6adurlhttp://www.edong.com/v8/delicatedserver/idcadd.phpclientca-pub-1681215984289622);GgKwClickStat(解决方案,www.edong.com,afc,2000072864); οnmοuseοutisShowGg false;InTextAds_GgLayer_u89E3_u51B3_u65B9_u6848解决方案 帮助团队通过记分卡、Dashboard、Web Services和移动设备将数据应用推向业务的各个领域。无论是开发人员、数据库管理员、信息工作者还是决策者SQL Server 2005都可以提供出创新的解决方案并可从数据中获得更多的益处。 它所带来的新特性如T-SQL的增强、数据分区、服务代理和与.Net Framework的集成等在易管理性、可用性、可伸缩性和安全性等方面都有很大的增强。 2 表分区的具体实现方法 表分区分为水平分区和垂直分区。水平分区将表分为多个表。每个表包含的列数相同但是行更少。例如可以将一个包含十亿行的表水平分区成 12 个表每个小表表示特定年份内一个月的数据。任何需要特定月份数据的查询只需引用相应月份的表。而垂直分区则是将原始表分成多个只包含较少列的表。水平分区是最常用分区方式本文以水平分区来介绍具体实现方法。 水平分区常用的方法是根据时期和使用对数据进行水平分区。例如本文例子一个短信发送记录表包含最近一年的数据但是只定期访问本季度的数据。在这种情况下可考虑将数据分成四个区每个区只包含一个季度的数据。 2.1 创建文件组 建立分区表先要创建文件组而创建多个文件组主要是为了获得好的 I/O 平衡。一般情况下文件组数最好与分区数相同并且这些文件组通常位于不同的磁盘上。每个文件组可以由一个或多个文件构成而每个分区必须映射到一个文件组。一个文件组可以由多个分区使用。为了更好地管理数据例如为了获得更精确的备份控制对分区表应进行设计以便只有相关数据或逻辑分组的数据位于同一个文件组中。使用 ALTER DATABASE添加逻辑文件组名 ALTER DATABASE [DeanDB] ADD FILEGROUP [FG1] DeanDB为数据库名称FG1文件组名。创建文件组后再使用 ALTER DATABASE 将文件添加到该文件组中 ALTER DATABASE [DeanDB] ADD FILE ( NAME NFG1, FILENAME NC:\DeanData\FG1.ndf , SIZE 3072KB , FILEGROWTH 1024KB ) TO FILEGROUP [FG1] 类似的建立四个文件和文件组并把每一个存储数据的文件放在不同的磁盘驱动器里。 2.2 创建分区函数 创建分区表必须先确定分区的功能机制表进行分区的标准是通过分区函数来决定的。创建数据分区函数有RANGE “LEFT | / RIGHT”两种选择。代表每个边界值在局部的哪一边。例如存在四个分区则定义三个边界点值并指定每个值是第一个分区的上边界 (LEFT) 还是第二个分区的下边界 (RIGHT)[1]。代码如下 CREATE PARTITION FUNCTION [SendSMSPF](datetime) AS RANGE RIGHT FOR VALUES (20070401, 20070701, 20071001) 2.3 创建分区方案 创建分区函数后必须将其与分区方案相关联以便将分区指向至特定的文件组。就是定义实际存放数据的媒体与各数据块的对应关系。多个数据表可以共用相同的数据分区函数一般不共用相同的数据分区方案。可以通过不同的分区方案使用相同的分区函数使不同的数据表有相同的分区条件但存放在不同的媒介上。创建分区方案的代码如下 CREATE PARTITION SCHEME [SendSMSPS] AS PARTITION [SendSMSPF] TO ([FG1], [FG2], [FG3], [FG4]) 2.4 创建分区表 建立好分区函数和分区方案后就可以创建分区表了。分区表是通过定义分区键值和分区方案相联系的。插入记录时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) 2.5 查看分区表信息 系统运行一段时间或者把以前的数据导入分区表后我们需要查看数据的具体存储情况即每个分区存取的记录数那些记录存取在那个分区等。我们可以通过$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] 在查询分析器里执行以上脚本结果如图1所示 图1 分区表信息 2.6 维护分区 分区的维护主要设计分区的添加、减少、合并和在分区间转换。可以通过ALTER PARTITION FUNCTION的选项SPLITMERGE和ALTER TABLE的选项SWITCH来实现。SPLIT会多增加一个分区而MEGRE会合并或者减少分区SWITCH则是逻辑地在组间转换分区。 3 性能对比 我们对2650万数据存储空间占用约 4G 的单表进行性能对比测试环境为IBM365CPU 至强 2.7G *2、内存 16G 、硬盘 136G *2系统平台为Windows 2003 SP1SQL 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的并行操作检索数据的数据量也变小了定位数据所耗时间变短。 4 结束语 对海量数据的处理一直是一个令人头痛的问题。分离的技术是所有设计者们首先考虑的问题不管是分离应用程序功能还是分离数据访问如果加以了 strong classkgb οnmοuseοverisShowAds false;isShowAds2 false;isShowGg true;InTextAds_GgLayer_u5408_u7406;KeyGate_ads.ShowGgAds(this,_u5408_u7406,event) styleborder-right: 0px; padding-right: 0px; border-top: 0px; padding-left: 0px; font-weight: normal; padding-bottom: 0px; margin: 0px; border-left: 0px; cursor: hand; color: #0000ff; padding-top: 0px; border-bottom: 0px; text-decoration: underline οnclickjavascript:window.open(http://pagead2.googlesyndication.com/pagead/iclk?salaiBAuhPftmkSOC3NI3-vAOPvKjiB6iiile4tqzABMCNtwHQyhYQAhgCIKy8kQooFDgAUK7V_ZADYJ2h34HYBaABnJmw-wOyAQ93d3cuY25ibG9ncy5jb23IAQHaATtodHRwOi8vd3d3LmNuYmxvZ3MuY29tLzEzNTkwL2FyY2hpdmUvMjAwNy8wNy8wOS84MTA3NzAuaHRtbIACAakCwBp42leQgz7IApru0wSoAwHoA9cC6AOMA-gDDegDBegDA4gEAZAEAZgEAAnum2adurlhttp://www.bestengine.com.cnclientca-pub-1681215984289622);GgKwClickStat(合理,www.bestengine.com.cn,afc,2000072864); οnmοuseοutisShowGg false;InTextAds_GgLayer_u5408_u7406合理 规划都能十分有效的解决大数据表的运行效率低和维护成本高等问题。SQL Server 2005新增的表分区功能可以对数据进行合理分区当用户在访问部分数据时SQL Server最佳化引擎可以根据数据的实体存放找出最佳的执行方案而不至于大海捞针。转载于:https://www.cnblogs.com/dfsxh/archive/2009/09/09/1563200.html
