SQLSERVER2005中分区表的设计

发布时间：2020-12-12 15:54:38 所属栏目：MsSql教程来源：网络整理

导读：一、 ??????? 概要 1.1 ????? 实现目标 SQL Server 2005 中基于表的分区功能简化了分区表的创建和维护过程 , 给数据库提供了灵活性和更好的性能 ; 如果能良好应用将改善数据库大型表性能 , 设计人员管理性的的设计和实现 ; 并方便于 DBA 相关维护工作 1.2 ??

一、??????? 概要

1.1????? 实现目标

SQL Server 2005 中基于表的分区功能简化了分区表的创建和维护过程,给数据库提供了灵活性和更好的性能;如果能良好应用将改善数据库大型表性能,设计人员管理性的的设计和实现;并方便于DBA相关维护工作

1.2????? 参考资料

1 微软msdn

http://www.microsoft.com/china/msdn/library/data/sqlserver/sql2k5partition.mspx?mfr=true

2 WebCasts

SQL Server2005中的表分区功能和索引

???? http://www.microsoft.com/china/msdn/events/webcasts/shared/webcast/episode.aspx?newsID=1242313

3 sqlserver2005 中文帮助

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/tsqlref9/html/f1745145-182d-4301-a334 -18f 799d361d1.htm

二、??????? 需求描述

2.1主要参与者

2.1.1 数据库管理人员DBA

2.1.2 负责相关模块的SA

2.2系统相关人员及其兴趣

2.2.1 User,希望系统能快速准确的新增,修改,删除,查询,到业务数据;同時因為業務需要和審計需要更多的數據

2.2.2 PM ? 使管理设计维护功能进一步分开,让数据库维护部分交给DBA进行性能调优;同时系统能更好的相应用户操作;

2.2.3 SA? 简化对数据库表的设计,并把可以把数据库设计,可把优化的工作交给DBA处理

2.2.3 Develper 简化开发工作

2.2.4 DBA减少备份,聚合数据带来的用户响应等待;充分利用多硬盘,多CPU能并发处理数据相关工作;利用分區,减能少磁盘I/O读取;方便实现负载平衡,均衡I/O;建立起良好的数据库架构工作,方便数据库服务器性扩展,減少約束和表的結構的維護;

2.3前置条件

2.3.1 了解基础环境,包括数据库,硬件的准备和基本配置情况

数据库是SQLServer2005 SQL Server Enterprise的版本

硬件? 系统是多硬盘,多CPU为佳

2.3.2 现在业务中存在需要性能优化和管理方便的地方

可以优化的有

A. 如何處理 transaction 數據多的 Table

1. 按定時抄去 _Arc 的 database，只留有用(e.g. report要用) 的 data 在現行的DB
2. 按年份月份開 Table

上面的方法存在的问题有

每年需要对该表进行定时清理数据,在清理数据的时候会使得该表的索引变得和/或索引变得支离破碎和/或被锁定,同时处理这些数据的时候将会使系统阻塞的情况,容易使得正常的操作无法正常运行,处理后还需要重修复索引;整个工作比较毫时和影响业务模组的正常运行(虽然可以把这个工作放在周末进行,但是随着业务数据的增加将会变得庞大)

B 查询速度由于Data较多的原因,查询和修改数据比较慢,一次查詢會掃描整個表的數據

2.3.3 DBA需要充分了解分区技术每一个关键点; 包括创建,维护方法

2.3.4 用SQL PROFILER找出性能存在的瓶颈的SQL

2.4成功后的保证

2.4.1 建立分区函数,分区架构,和分区表;

2.4.2 DBA应知道如何维护其架构；

2.4.3 SA了解其技术；

2.5基本流程

[描述能够满足项目相关人员兴趣的典型的成功路径]

2.5.1 确定系统存在的瓶颈问题

2.5.2 确定需要分区的表

2.5.3 确定分区键和分区数目;

2.5.4 建立分区函数,分区表；

2.5.5 维护分区函数,架构, 分区表

2.5.6 在删除数据和备份中使用分区表

2.6扩展流程（替代流程）

扩展流程主要在,分区表设置前后的对比

2.6.1 查询成本的比例变化

2.6.2 windows性能检视器看看对磁盘i/o,和cpu内存变化(能在测试服务器里测试需要压力测试才能查看结果)

2.7特殊流程

??????

2.8技术与数据的变化列表

2.9发生频率

经常??????

2.10待解决的问题.

2.10.1 建立完分区表后，建立不指定分区的聚集索引，可能会导致分区表变成非分区表

?????????????

2.10.2 如果以DataTime类型做函数区分,可能带来3微秒的误差,参考msdn提出

http://www.microsoft.com/china/msdn/library/data/sqlserver/sql2k5partition.mspx?mfr=true

必须更改日期范围。因为您要处理的是 datetime 数据，而在时间的存储方式方面又存在舍入问题，所以必须能够通过编程方式确定正确的毫秒值。要确定月末最后的 datetime 值，最容易的方法是将正在处理的月份加上 1 个月，然后再减去 2 或 3 毫秒。不能只减去 1 毫秒，因为 59.999 会上舍入为 .000，即下个月的第一天。可以减去 2 或 3 毫秒，因为 2 毫秒将向下舍入为 .997，而 3 毫秒等于 .997；.997 是可以存储的有效值。这样即可确定 datetime 范围的正确结束值：

三、??????? 系统设计

3.1系统流程设计

建立分区函数,分区架构,分区表,维护分区表结构,数据备份SQL脚本

3.2数据库设计

先处理StLotLdg表

3.3模块功能详细设计

3.3.1 建立多个文件组

该步骤是创建数据库文件分布到多个磁盘中,以获取更好的性能(也可以创建在一个磁盘中)

?????? 1 创建文件组

?????? ALTER DATABASE DC_CECCS

ADD FILEGROUP [DC_CECCS_FG1]

ALTER DATABASE DC_CECCS

ADD FILEGROUP [DC_CECCS_FG2]

GO??

?????? ….

??????

?????? 2 把文件组加入到驱动器中

?????? INSERT dbo.FilegroupInfo VALUES (1, 1, N'C:/SalesDB')

INSERT dbo.FilegroupInfo VALUES (2, 2, N'D:/SalesDB')

?????? …..

?????? 3查看分组信息

?????? 创建函数BaseDB..FnPartitionInfo或sql

?????? 4 更改文件组语句

?????? ALTER DATABASE Dc_ceccs ADD FILE

?????? 5 查看文件组大小

exec sp_helpfile

3.3.2 创建分区表函数,架构,分区表,索引

???? 1创建分区表函数

CREATE PARTITION FUNCTION RangeByMonth (datetime)

???? ?

??? 注:这里有 RANGE 和 Left 的方式

???? ?更改分区函数为

ALTER PARTITION FUNCTION?????

???????????

???? 2创建分区表架构

??? CREATE PARTITION SCHEME [SchemeByMonth] ?? ?????

注:可更改分区架构

?????? ?更改分区架构方式为:

?????? ?ALTER PARTITION SCHEME

?????? 3 在表中使用分区架构

在sqlserver2000 里需要手工把数据分别插入不用时间段的表

在sql 2005里

只要把数据从原表一次插入分区表就可以了,系统自动根据分区函数分别插入数据

(暂不支持直接在原表上创建分区函数)

那么创建步骤如下:

A先建立一个备份表

创建新表上使用分区表

CREATE TABLE dc_ceccs.[dbo].[ StLotLdgBak]? (….) on RangeByMonth

B 再把旧表数据插入至新表中

use dc_ceccs

SELECT *

INTO StLotLdgbak

FROM StLotLdg

C 删除旧表

Drop Table StLotLdg

D 改新表的名称为旧表

sp_rename 'StLotLdgbak1','StLotLdg'

4 查看分区表信息

SELECT $partition.TwoYearDateRangePFN(o.date)

???????????????????? AS [Partition Number]

?????? ,min(o. date) AS [Min Order Date]

?????? ,max(o. date) AS [Max Order Date]

?????? ,count(*) AS [Rows In Partition]

FROM dbo.StLotLdg AS o

GROUP BY $partition.TwoYearDateRangePFN(o. date)

ORDER BY [Partition Number]

5 添加索引

ALTER TABLE StLotLdg

ADD CONSTRAINT StLotLdgPK

?????? PRIMARY KEY CLUSTERED (Date,nvrDocno)

?????? ON SchemeByMonth (Date)

6 使用SQL Server 为分区表提供的各种连接策略的查询

SELECT o. nvrDocno,o.Date

FROM dbo.StLotLdg AS o

?????? INNER JOIN dbo.stocklot AS od ON o.lot_no = od. lot_no?????????????????????????????????

WHERE o. Date >= '20050701'

????????????? AND o. Date <= '20040930 11:59:59.997'

注,该查询已经使用到了分区表,不用做整个表的扫描

7 备份数据操作

A備份

exec xp_cmdshell

'bcp "SELECT * FROM StLotLdg WHERE date < '' 2006/02/01 '' AND date > '' 2001/01/01 '' "? queryout "StLotLdg.txt" -T -c"'

或

备份至本机

或备份至其他表

Create table StLotLdg200601 …? on [DC_CECCS_FG1]

在同一个分区里执行插入数据

INSERT into StLotLdg200601 SELECT ?* from StLotLdg where date = ?< ' 2006/02/01 ' AND date > ' 2001/01/01 ')

插入后建索引

ALTER TABLE [StLotLdg200601]

ADD CONSTRAINT StLotLdg200601PK

?PRIMARY KEY CLUSTERED (Date,nvrDocno)

ON [DC_CECCS_FG1]

此时已经使用到分区表,查询只会进行在相应的历史分区进行查询,对现在进行的分区数据影响较小

B删除原表的数据

truncate table 你的表

或

建立一个作业指定操作时间在晚上进行

while exists (select 1 from StLotLdg where date = ?< ' 2006/02/01 ' AND date > ' 2001/01/01 ')

begin

??? set rowcount 10000

??? delete StLotLdg where here date = ?< ' 2006/02/01 ' AND date > ' 2001/01/01 '??? set rowcount 0

end

注时间可以取 getdate,判断时间为 datediff(Year,date,getdate())<=1

C恢复

exec xp_cmdshell

‘bcp dbname.dbo.table_name in d:/ StLotLdg.txt -c -q -S - Usa –P’

或

insert into StLotLdg select * from StLotLdg200601

或

ALTER TABLE StLotLdg SWITCH PARTITION 1

TO OrdersOctober2002

8修改分区表

A 添加一个文件组到数据库
Use master

ALTER DATABASE Dc_ceccs ADD FILEGROUP DC_CECCS_FG13

ALTER DATABASE Dc_ceccs

ADD FILE

(

NAME = N' DC_CECCS_FG13',FileName = 'D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/Dc_ceccs13.mdf'

)

TO FILEGROUP DC_CECCS_FG13

Use Dc_ceccs

B 修改分区Scheme

ALTER PARTITION SCHEME RangeByMonthScheme

NEXT USED DC_CECCS_FG13;

C 修改分区函数

ALTER PARTITION FUNCTION RangeByMonth()

SPLIT RANGE ('T/N');

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!