【讨论】SQL server 2005分区后的效率

hmlhmlb 2008-03-09 02:44:39
请问各位问题:
1. 建立93个文件组和对应的文件
2. 分区函数:CREATE PARTITION FUNCTION [GpsRangePFN](int) AS RANGE LEFT FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92)
3. 分区方案:CREATE PARTITION SCHEME [GpsRangePScheme] AS PARTITION [GpsRangePFN] TO ([FG1], [FG2], [FG3], [FG4], [FG5], [FG6], [FG7], [FG8], [FG9], [FG10], [FG11], [FG12], [FG13], [FG14], [FG15], [FG16], [FG17], [FG18], [FG19], [FG20], [FG21], [FG22], [FG23], [FG24], [FG25], [FG26], [FG27], [FG28], [FG29], [FG30], [FG31], [FG32], [FG33], [FG34], [FG35], [FG36], [FG37], [FG38], [FG39], [FG40], [FG41], [FG42], [FG43], [FG44], [FG45], [FG46], [FG47], [FG48], [FG49], [FG50], [FG51], [FG52], [FG53], [FG54], [FG55], [FG56], [FG57], [FG58], [FG59], [FG60], [FG61], [FG62], [FG63], [FG64], [FG65], [FG66], [FG67], [FG68], [FG69], [FG70], [FG71], [FG72], [FG73], [FG74], [FG75], [FG76], [FG77], [FG78], [FG79], [FG80], [FG81], [FG82], [FG83], [FG84], [FG85], [FG86], [FG87], [FG88], [FG89], [FG90], [FG91], [FG92], [FG93], [PRIMARY])
4. 表结构CREATE TABLE [dbo].[RealTime](
[id] [bigint] IDENTITY(-9223372036854775808,1) NOT NULL,
[telephone] [varchar](20) NULL,
[OrderTime] [datetime] NOT NULL,
[Distance] [bigint] NULL,
[pic] [image] NULL,
[DutyStr] [varchar](10) NULL,
[DayFlag] AS (datediff(day,CONVERT([datetime],'1900-01-01',(111)),[OrderTime])%(93)) PERSISTED
) ON GpsRangePScheme(DayFlag)
GO
CREATE CLUSTERED INDEX [IX_RealTime_Partition] ON [dbo].[RealTime]([telephone], [OrderTime], [DayFlag]) on GpsRangePScheme(DayFlag)
GO
 测试过程和结果
1. Select * from RealTime where OrderTime >= '2008-01-01 00:00:00' and OrderTime <= '2008-01-05 23:59:59'和Select * from RealTime where OrderTime >= '2008-01-01 00:00:00' and OrderTime <= '2008-01-05 23:59:59' and dayFlag >= 14 and dayFlag <= 18,后者加上dayFlag字段的速度远远大于前者。
2. Select * from RealTime where telephone = '1301120120' and OrderTime >= '2008-01-01 00:00:00' and OrderTime <= '2008-01-05 23:59:59' 和 Select * from RealTime where telephone = '861301120120' and OrderTime >= '2008-01-01 00:00:00' and OrderTime <= '2008-01-05 23:59:59' and dayFlag >= 14 and dayFlag <= 18,这两句的速度是一样的。但是看查询计划中的常量扫描(Constant Scan)的值,前一个SQL语句是把93个分区全部扫描,而后一个只是把14~18分区扫描了一遍,但是为什么两个SQL语句所用的时间都是一样的呢??是因为聚集索引??还是因为我做的分区有问题??
3. 分区表的SQL语句(select * from realTime where telephone = '1301120120' and OrderTime >= '2008-01-01 00:00:00' and OrderTime <= '2008-01-05 23:59:59' )查询花费时间和未分区表的花费时间相同。。。
...全文
546 24 打赏 收藏 转发到动态 举报
写回复
用AI写文章
24 条回复
切换为时间正序
请发表友善的回复…
发表回复
seai 2008-08-01
  • 打赏
  • 举报
回复
mark
w2jc 2008-03-11
  • 打赏
  • 举报
回复
TO w2jc:我们客户那边实际上已经做了RAID5了。。这样的IO是不是够了??一定要RAID 1 + 0????我对硬件上的这些东西不是太理解。。
---------------------
我觉得有RAID5的话,分区基本没什么必要。除非是对历史数据按时间做纵向的分割。
RAID 1+0 自然是最好的,比RAID5还好,但成本太高了,没有多少用户舍得这么做,除非钱实在太多...

hmlhmlb 2008-03-11
  • 打赏
  • 举报
回复
结贴了。。。
flairsky 2008-03-10
  • 打赏
  • 举报
回复
想来想去,你这数据集太大,估计怎么都不会兼顾时间与[telephone]的查询

看你自己状况了,如果针对单个的[telephone]要求多一点,就按[telephone]分区,如果你针对总体统计多一点,就按时间分。

总之数据量太大,sql并不合适
flairsky 2008-03-10
  • 打赏
  • 举报
回复
[telephone] [varchar]

为什么是varchar?难道会出现非数字?
flairsky 2008-03-10
  • 打赏
  • 举报
回复
建议你按Telephone做分区。我相信93个区能很好区别Telephone了,再按时间做聚集索引
flairsky 2008-03-10
  • 打赏
  • 举报
回复
每天一个分区固然直观简单,但对于你自己的查询语句不一定是最好的

但你的第2句sql明显就没有利用好分区,Telephone需要直接搜索93张表,因为,Telephone在分区中是离散的。
同理第3句sql,Telephone条件先搜索相应结果集,再滤出时间对应集。

第一句就符合你分区状况。理论上时间会短很多
w2jc 2008-03-10
  • 打赏
  • 举报
回复
I/O并行的程度主要是看硬盘控制器的数量,硬盘控制器本身就相当于一个简单的CPU,
每个物理硬盘都有一个硬盘控制器负责整块硬盘的I/O。

就算在单CPU的机器上,如果有3个物理硬盘,那么有3个硬盘控制器,
这个时候如果把分区放在3个物理硬盘上,就可以有I/O并行处理。

分区要提高性能,就要尽量分布I/O请求到多个物理硬盘上。
如果在一个物理硬盘上建4个逻辑分区,然后将数据库分区建在这些逻辑分区上,
看上去像是4个硬盘,其实所有的I/O请求都需要被一个硬盘控制器去处理。

hmlhmlb 2008-03-10
  • 打赏
  • 举报
回复
to flairsky。93个文件组没有放到不同的IO上面。我是根据每天一个分区的想法去做的93个分区。另外利用索引,肯定是利用上了。因为你看一下我建立的索引的字段和顺序,对比一下where条件的顺序。。实际上,2005也把分区扫描也用上了在加上dayFlag字段之后。
在实际应用中,where条件之后就是Telephone and orderTime这两个组合是最经常用到的。所以,我才一天一个分区。
可能把文件组放在不同的IO上面也很重要吧。。
hmlhmlb 2008-03-10
  • 打赏
  • 举报
回复
这个。我测试的那台服务器是双至强的CPU。
flairsky 2008-03-10
  • 打赏
  • 举报
回复
分区的理论依据是增大查询的命中率,分区就相当于粗略的做了一个索引。

建立93个文件组和对应的文件,这句话就问题来了

93个文件组是否在不同io设备上?如果不在不同io设备上,你那几句查询是否充分用到了索引以及分区优势?

换句话说,你根据什么把数据分成93个区?在查询的时候是否有按此规则去提高查询命中率?

如果最常用的查询并没有利用到分区优势,那考虑换种分区方式。
hmlhmlb 2008-03-10
  • 打赏
  • 举报
回复
to:w2jc ;偶在没有磁盘阵列的机子上试过,在服务器(RAID5)上也试过。


那看来是分区在单CPU、raid5的情况下是没有办法提高性能了。。只能按照微软的说法只能做成raid 1 + 0了。。或者再加一个CPU。。或者把这些东东放在不同的硬盘上。。。
zjcxc 2008-03-10
  • 打赏
  • 举报
回复
分区要在不同的物理磁盘上, 这样I/O才能并行

另外, I/O 并行还跟 CPU 个数相关, 如果你只有一个物理 CPU, 则也没有什么并行 I/O 的效果
flairsky 2008-03-10
  • 打赏
  • 举报
回复
telephone呢??或者是删除了telephone??修改telephone

不知道你telephone是否有规则,某市电信还是某市mobile,还有你telephone用的varchar,匹配起来很麻烦的,用numberic或者int都比varchar要速度快

增删改都不是问题啊

要不弄个则中的,你人为的按每天一个表分开,然后每张表用sql2005自带的分区按telephone分开,比方说按telephone最后两位分成100区或者其他n个区,也可以采取模某数分区。
hmlhmlb 2008-03-10
  • 打赏
  • 举报
回复
TO w2jc:我们客户那边实际上已经做了RAID5了。。这样的IO是不是够了??一定要RAID 1 + 0????我对硬件上的这些东西不是太理解。。
To flairsky:做分区的时候,也曾经想过要做成telephone的分区。但是,如果客户那边增加了telephone呢??或者是删除了telephone??修改telephone,这个时候,用telephone做分区好像就不大合适了。。。
还有就是如果用oracle,那么分区应该是怎么样的??查询效率会提高??
w2jc 2008-03-10
  • 打赏
  • 举报
回复
关注!我一直对于分区的性能比较感兴趣。

请问LZ:
上面的试验是在一个物理硬盘上?还是磁盘阵列上?

我以前也做过一点测试,发现分区和为分区的性能区别不大,当时是在一个物理硬盘上做的。
理论上,如果把文件组放在不同物理磁盘上的话,I/O并行度增加,使用分区的速度会快一些,但当时也没有条件去测试。

我觉得使用分区,在I/O分布比较合理的情况下,能勉强接近和磁盘阵列或者存储网络一样的I/O性能,但是达不到磁盘阵列和存储网络所提供的数据冗余和高可用性。

试想,如果你的93个文件组能分布到4个物理硬盘上,速度能提高,但如果其中一个硬盘坏了,那你的表也就完了。
每个硬盘都可能是单点故障。使用磁盘阵列则不会有这种问题。

不太看好用分区去提高性能,和磁盘阵列(或存储网络)相比,不论从速度和数据安全角度都差得太远了。

不过我觉得是用2005的分区功能保存历史数据是不错的选择。比如,每年的数据放一个分区,可以方便地把老数据换出和换入。

-狙击手- 2008-03-09
  • 打赏
  • 举报
回复
93 个,够暴力的
kelph 2008-03-09
  • 打赏
  • 举报
回复
每个分区对应一个文件组可很好地提高I/O性能了,LZ这样做无可置疑。虽然越多的逻辑磁盘会越好。
hmlhmlb 2008-03-09
  • 打赏
  • 举报
回复
to:m1234.磁盘驱动器,这么讲???是磁盘分区?还是每一块硬盘。。。。。是磁盘分区的可能性比较高吧。。还有就是这句话的根据在哪里呢?好像微软也没有特别强调这个吧。。
M1234 2008-03-09
  • 打赏
  • 举报
回复
除非每个数据文件部署在不同的磁盘驱动器上,否则分区并不能提高数据库的性能.
加载更多回复(4)

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧