创建分区函数
CREATE PARTITION FUNCTION [PF_PartID](int) AS RANGE LEFT FOR VALUES (100, 200, 300, 400, 500, 600, 700, 800, 900)
创建分区方案
CREATE PARTITION SCHEME [PS_PartID] AS PARTITION [PF_PartID] TO ([Group_100], [Group_200], [Group_300], [Group_400], [Group_500], [Group_600], [Group_700], [Group_800], [Group_900], [Group_000])
创建普通表:NormalTable和分区表:PartitionTable
两张表字段相同,如下:
[ID] [int] IDENTITY(1,1) NOT NULL,
[PartID] [int] NOT NULL,
[PartData] [varchar](8) NULL,
[AddTime] [datetime] NULL
分区表用PartID字段进行分区
各自导入两千万数据,数据相同,其中分区表PartitionTable十个分区各两百万数据
具体测试如下:
模拟生产环境,由于数据更新快,不建聚集索引,分区表建成后,删掉聚集索引
两个表都只建非聚集索引,创建语句如下:
create index IDX_NormalTable#PartID#PartData on NormalTable(PartID,PartData);
create index IDX_PartitionTable#PartID#PartData on PartitionTable(PartID,PartData) on PS_PartID(PartID);
测试两张表查询性能,语句如下:
select * from NormalTable where partid=34 and partdata='FA43A9FB'
select * from PartitionTable where partid=34 and partdata='FA43A9FB'
执行情况如下:
表 'NormalTable'。扫描计数 1,逻辑读取 116 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'PartitionTable'。扫描计数 1,逻辑读取 121 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
分区表比普通表要差一点,汗!!!
猜测可能因为索引都是新建的,在索引碎片非常多时,分区表的优势才能体现出来,试一试看:
两个表各自删掉400W数据,删除语句如下:
delete from NormalTable where id % 5 = 0;
delete from PartitionTable where id % 5 = 0;
再各自导入400W新数据,其数据相同,分区表PartitionTable十个分区各40W
此时查询两个表的索引碎片都达到80%以上,执行前面的测试语句
select * from NormalTable where partid=34 and partdata='FA43A9F'
select * from PartitionTable where partid=34 and partdata='FA43A9FB'
执行情况如下:
表 'NormalTable'。扫描计数 1,逻辑读取 3142 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'PartitionTable'。扫描计数 1,逻辑读取 3217 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
看来猜错了,分区表仍然却于劣势,再汗!!!
网上查了很多文档,都没有具体说分区表到底如何比普通表性能优越
有网页上说分区表必须有聚集索引才行
在上面测试前给两个表都建上聚集索引,语句如下:
create clustered index IDX_NormalTable#PartID on NormalTable(PartID);
create clustered index IDX_PartitionTable#PartID on PartitionTable(PartID) on PS_PartID(PartID);
建完后truncate两个表,将上面测试重新再来一次
执行查询时,两个表的用时和逻辑读取次数也是相差不大,多数情况下分区表都比普通表要差一点点。
不知是分区表性能本来如此还是本人测试方法不对?
希望有同行能指正
15 个解决方案
#1
没有用过分区表,如果每次要查询的数据加上条件,通常都会位于同一个分区表中,那这个分区表就是有用的,效率自然是高的,因为不需要读取其他分区表了.
#2
分情况,不同场景效率有好有差,断章取意的多
#3
没用过分区表,但是我觉得分区表的目的:应该是将表中热点数据根据规则分散至不同的文件组。而文件组可以利用硬件优势,比如将不同的文件组放在不同的硬盘上,以降低访问不同热点数据的I/O竞争。
我认为,假如将所有的文件组放到同一块硬盘上,然后建立分区表。这和创建一个索引差不也不大,也没有意义。
我认为,假如将所有的文件组放到同一块硬盘上,然后建立分区表。这和创建一个索引差不也不大,也没有意义。
#4
楼上说的有些道理
我记得分区表文档上说过原理:数据被分成若干组后,当查询时指明了分区字段时,查询只需在指定的数据文件中查找,这比在原来未分区整个数据文件中就要少查好多数据,其性能效率必然要提高的。
我这里测试有指明分区字段,但是效果不明显,不明白是不是哪弄错了。
另外再说明一下,测试我做过很多次,很多的场景,并非只是上面问题中提及到的情况而己。
我记得分区表文档上说过原理:数据被分成若干组后,当查询时指明了分区字段时,查询只需在指定的数据文件中查找,这比在原来未分区整个数据文件中就要少查好多数据,其性能效率必然要提高的。
我这里测试有指明分区字段,但是效果不明显,不明白是不是哪弄错了。
另外再说明一下,测试我做过很多次,很多的场景,并非只是上面问题中提及到的情况而己。
#5
自己顶一下,没高手能解答吗?
#6
每个分区必须在不同的
物理硬盘上,这是首要前提。
你找了10各快递员,却只有一辆助动车,会比1个快递员效率高?
你找了10各快递员,却只有一辆助动车,会比1个快递员效率高?
#7
原来是这样!
我就说我之前也尝试了一个600万左右纪录的表,以年分区之后,查找里面的一年内的部分时间段数据,都没见有多大的效能提升了。还以为是我索引沒设置好,又重新设计了几次不同的索引,都跟一个表沒分区时差不多了。。。
#8
其实分区表主要的作用不是提高性能,分区表有4个特别:
1. 提高数据的可用性。
比如,原来是1个表,如果其中有一条数据损坏了,那么会导致查询数据的时候,直接报错。
而如果用了分区表,那么只是包含了损坏数据的那个分区会有问题,其他的分区没有问题,因为分区表本质上只是逻辑上一个表,而物理上已经是分成了多个表了,表和表之间都相互独立。
2.易于维护。
在迁移数据,删除数据,维护数据时,非常方便,比如你要删除一个分区的数据,直接把数据交换到另一个表中就可以,类似于truncate,秒级就完成,你要建索引,可以在某个分区上建,不会影响别的分区,这个和第1点很像。
我之前帮一个网友做的一个方案就是,在1分钟左右导入800万数据到一个表里,还有建索引,还不能影响原有表的数据,可就是用的分区表方案。
3.通过硬件的并行,来提高性能。
我们现在用的系统,特别是数据库,最大的瓶颈就是在磁盘,因为磁盘的速度比内存慢好几个数量级,而内存也比cpu慢好几个数据量级,这就导致 性能很难提高。
那么,通过增加多个物理硬盘以及相应的连接总线和控制器,同时让多个磁盘同时工作,比如你select多个分区,而这个多个分区分别放到了不同的硬盘,你查询数据的时候,这些硬盘同时访问数据,速度就能有明显的提高。
4.通过所谓的分区消除,来提高查询的性能。
这个是最后要提的,所以分区消除,就是值从一个分区里访问数据,这样能较快查询的速度。
一般意义上,大家讲到的分区提高性能,就是这一点,那为什么速度反而没有原来的普通表快呢?
这个我觉得首先要明确一点,就是这个分区消除看上去一本万利,都是好处,真的是这样吗?
开动你的大脑想一想,就知道肯定不是这么回事
为啥呢? 因为分区表不是白用的,是有开销的。如果你仔细看看分区表的执行计划,就会发现,里面会用到一个分区判断函数,也就是说,根据你语句中的where字段值,来判断你要的数据是哪个分区的,然后再从哪个分区里找数据。
而显然,你现在的问题就是,这种开销本身比分区表所带来的性能上的提高还要大,所以导致比普通表还要差一些。
#9
还有一个就是,和数据本身的特性也有一定的关系,比如,数据量,你现在导入了2000多万的数据到表里,看似数据量很大了,但实际上像我所在的公司一个表里有6亿条数据,没有建分区表,速度还是很快的。
#10
1. 不同的分区是否在两块不同的物理硬盘上?
2. 你的测试是不是并行的?建议用 sqlquerystress
2. 你的测试是不是并行的?建议用 sqlquerystress
#11
可以在分區表的一個分區建立索引?
#12
楼上说的有些道理
我记得分区表文档上说过原理:数据被分成若干组后,当查询时指明了分区字段时,查询只需在指定的数据文件中查找,这比在原来未分区整个数据文件中就要少查好多数据,其性能效率必然要提高的。
我这里测试有指明分区字段,但是效果不明显,不明白是不是哪弄错了。
另外再说明一下,测试我做过很多次,很多的场景,并非只是上面问题中提及到的情况而己。
其实分区表主要的作用不是提高性能,分区表有4个特别:
1. 提高数据的可用性。
比如,原来是1个表,如果其中有一条数据损坏了,那么会导致查询数据的时候,直接报错。
而如果用了分区表,那么只是包含了损坏数据的那个分区会有问题,其他的分区没有问题,因为分区表本质上只是逻辑上一个表,而物理上已经是分成了多个表了,表和表之间都相互独立。
2.易于维护。
在迁移数据,删除数据,维护数据时,非常方便,比如你要删除一个分区的数据,直接把数据交换到另一个表中就可以,类似于truncate,秒级就完成,你要建索引,可以在某个分区上建,不会影响别的分区,这个和第1点很像。
我之前帮一个网友做的一个方案就是,在1分钟左右导入800万数据到一个表里,还有建索引,还不能影响原有表的数据,可就是用的分区表方案。
3.通过硬件的并行,来提高性能。
我们现在用的系统,特别是数据库,最大的瓶颈就是在磁盘,因为磁盘的速度比内存慢好几个数量级,而内存也比cpu慢好几个数据量级,这就导致 性能很难提高。
那么,通过增加多个物理硬盘以及相应的连接总线和控制器,同时让多个磁盘同时工作,比如你select多个分区,而这个多个分区分别放到了不同的硬盘,你查询数据的时候,这些硬盘同时访问数据,速度就能有明显的提高。
4.通过所谓的分区消除,来提高查询的性能。
这个是最后要提的,所以分区消除,就是值从一个分区里访问数据,这样能较快查询的速度。
一般意义上,大家讲到的分区提高性能,就是这一点,那为什么速度反而没有原来的普通表快呢?
这个我觉得首先要明确一点,就是这个分区消除看上去一本万利,都是好处,真的是这样吗?
开动你的大脑想一想,就知道肯定不是这么回事
为啥呢? 因为分区表不是白用的,是有开销的。如果你仔细看看分区表的执行计划,就会发现,里面会用到一个分区判断函数,也就是说,根据你语句中的where字段值,来判断你要的数据是哪个分区的,然后再从哪个分区里找数据。
而显然,你现在的问题就是,这种开销本身比分区表所带来的性能上的提高还要大,所以导致比普通表还要差一些。
可以在分區表的一個分區建立索引?
不能,索引是对整个表建立的,但是维护的时候,可以单独一个分区维护。
#13
谢谢各位回复
现在数据文件都是放在磁盘阵列上,也类似于放在不同硬盘上了吧。
看来分区表主要是还在维护管理方面比普通表有优势,结贴
现在数据文件都是放在磁盘阵列上,也类似于放在不同硬盘上了吧。
看来分区表主要是还在维护管理方面比普通表有优势,结贴
#14
学习了,我也建立了分区表,但是感觉和直接查也是一样的,本帖解决了我的疑问了。
#15
看情况吧,我也做跟你一样的测试。 分区后查询效率差了点,要不同硬盘,不过分区表还有个特别。应该也可以实现一个读写分离(如读是01分区的写是02分区的。这样就分开了)
#1
没有用过分区表,如果每次要查询的数据加上条件,通常都会位于同一个分区表中,那这个分区表就是有用的,效率自然是高的,因为不需要读取其他分区表了.
#2
分情况,不同场景效率有好有差,断章取意的多
#3
没用过分区表,但是我觉得分区表的目的:应该是将表中热点数据根据规则分散至不同的文件组。而文件组可以利用硬件优势,比如将不同的文件组放在不同的硬盘上,以降低访问不同热点数据的I/O竞争。
我认为,假如将所有的文件组放到同一块硬盘上,然后建立分区表。这和创建一个索引差不也不大,也没有意义。
我认为,假如将所有的文件组放到同一块硬盘上,然后建立分区表。这和创建一个索引差不也不大,也没有意义。
#4
楼上说的有些道理
我记得分区表文档上说过原理:数据被分成若干组后,当查询时指明了分区字段时,查询只需在指定的数据文件中查找,这比在原来未分区整个数据文件中就要少查好多数据,其性能效率必然要提高的。
我这里测试有指明分区字段,但是效果不明显,不明白是不是哪弄错了。
另外再说明一下,测试我做过很多次,很多的场景,并非只是上面问题中提及到的情况而己。
我记得分区表文档上说过原理:数据被分成若干组后,当查询时指明了分区字段时,查询只需在指定的数据文件中查找,这比在原来未分区整个数据文件中就要少查好多数据,其性能效率必然要提高的。
我这里测试有指明分区字段,但是效果不明显,不明白是不是哪弄错了。
另外再说明一下,测试我做过很多次,很多的场景,并非只是上面问题中提及到的情况而己。
#5
自己顶一下,没高手能解答吗?
#6
每个分区必须在不同的
物理硬盘上,这是首要前提。
你找了10各快递员,却只有一辆助动车,会比1个快递员效率高?
你找了10各快递员,却只有一辆助动车,会比1个快递员效率高?
#7
每个分区必须在不同的 物理硬盘上
原来是这样!
我就说我之前也尝试了一个600万左右纪录的表,以年分区之后,查找里面的一年内的部分时间段数据,都没见有多大的效能提升了。还以为是我索引沒设置好,又重新设计了几次不同的索引,都跟一个表沒分区时差不多了。。。
#8
楼上说的有些道理
我记得分区表文档上说过原理:数据被分成若干组后,当查询时指明了分区字段时,查询只需在指定的数据文件中查找,这比在原来未分区整个数据文件中就要少查好多数据,其性能效率必然要提高的。
我这里测试有指明分区字段,但是效果不明显,不明白是不是哪弄错了。
另外再说明一下,测试我做过很多次,很多的场景,并非只是上面问题中提及到的情况而己。
其实分区表主要的作用不是提高性能,分区表有4个特别:
1. 提高数据的可用性。
比如,原来是1个表,如果其中有一条数据损坏了,那么会导致查询数据的时候,直接报错。
而如果用了分区表,那么只是包含了损坏数据的那个分区会有问题,其他的分区没有问题,因为分区表本质上只是逻辑上一个表,而物理上已经是分成了多个表了,表和表之间都相互独立。
2.易于维护。
在迁移数据,删除数据,维护数据时,非常方便,比如你要删除一个分区的数据,直接把数据交换到另一个表中就可以,类似于truncate,秒级就完成,你要建索引,可以在某个分区上建,不会影响别的分区,这个和第1点很像。
我之前帮一个网友做的一个方案就是,在1分钟左右导入800万数据到一个表里,还有建索引,还不能影响原有表的数据,可就是用的分区表方案。
3.通过硬件的并行,来提高性能。
我们现在用的系统,特别是数据库,最大的瓶颈就是在磁盘,因为磁盘的速度比内存慢好几个数量级,而内存也比cpu慢好几个数据量级,这就导致 性能很难提高。
那么,通过增加多个物理硬盘以及相应的连接总线和控制器,同时让多个磁盘同时工作,比如你select多个分区,而这个多个分区分别放到了不同的硬盘,你查询数据的时候,这些硬盘同时访问数据,速度就能有明显的提高。
4.通过所谓的分区消除,来提高查询的性能。
这个是最后要提的,所以分区消除,就是值从一个分区里访问数据,这样能较快查询的速度。
一般意义上,大家讲到的分区提高性能,就是这一点,那为什么速度反而没有原来的普通表快呢?
这个我觉得首先要明确一点,就是这个分区消除看上去一本万利,都是好处,真的是这样吗?
开动你的大脑想一想,就知道肯定不是这么回事
为啥呢? 因为分区表不是白用的,是有开销的。如果你仔细看看分区表的执行计划,就会发现,里面会用到一个分区判断函数,也就是说,根据你语句中的where字段值,来判断你要的数据是哪个分区的,然后再从哪个分区里找数据。
而显然,你现在的问题就是,这种开销本身比分区表所带来的性能上的提高还要大,所以导致比普通表还要差一些。
#9
还有一个就是,和数据本身的特性也有一定的关系,比如,数据量,你现在导入了2000多万的数据到表里,看似数据量很大了,但实际上像我所在的公司一个表里有6亿条数据,没有建分区表,速度还是很快的。
#10
1. 不同的分区是否在两块不同的物理硬盘上?
2. 你的测试是不是并行的?建议用 sqlquerystress
2. 你的测试是不是并行的?建议用 sqlquerystress
#11
楼上说的有些道理
我记得分区表文档上说过原理:数据被分成若干组后,当查询时指明了分区字段时,查询只需在指定的数据文件中查找,这比在原来未分区整个数据文件中就要少查好多数据,其性能效率必然要提高的。
我这里测试有指明分区字段,但是效果不明显,不明白是不是哪弄错了。
另外再说明一下,测试我做过很多次,很多的场景,并非只是上面问题中提及到的情况而己。
其实分区表主要的作用不是提高性能,分区表有4个特别:
1. 提高数据的可用性。
比如,原来是1个表,如果其中有一条数据损坏了,那么会导致查询数据的时候,直接报错。
而如果用了分区表,那么只是包含了损坏数据的那个分区会有问题,其他的分区没有问题,因为分区表本质上只是逻辑上一个表,而物理上已经是分成了多个表了,表和表之间都相互独立。
2.易于维护。
在迁移数据,删除数据,维护数据时,非常方便,比如你要删除一个分区的数据,直接把数据交换到另一个表中就可以,类似于truncate,秒级就完成,你要建索引,可以在某个分区上建,不会影响别的分区,这个和第1点很像。
我之前帮一个网友做的一个方案就是,在1分钟左右导入800万数据到一个表里,还有建索引,还不能影响原有表的数据,可就是用的分区表方案。
3.通过硬件的并行,来提高性能。
我们现在用的系统,特别是数据库,最大的瓶颈就是在磁盘,因为磁盘的速度比内存慢好几个数量级,而内存也比cpu慢好几个数据量级,这就导致 性能很难提高。
那么,通过增加多个物理硬盘以及相应的连接总线和控制器,同时让多个磁盘同时工作,比如你select多个分区,而这个多个分区分别放到了不同的硬盘,你查询数据的时候,这些硬盘同时访问数据,速度就能有明显的提高。
4.通过所谓的分区消除,来提高查询的性能。
这个是最后要提的,所以分区消除,就是值从一个分区里访问数据,这样能较快查询的速度。
一般意义上,大家讲到的分区提高性能,就是这一点,那为什么速度反而没有原来的普通表快呢?
这个我觉得首先要明确一点,就是这个分区消除看上去一本万利,都是好处,真的是这样吗?
开动你的大脑想一想,就知道肯定不是这么回事
为啥呢? 因为分区表不是白用的,是有开销的。如果你仔细看看分区表的执行计划,就会发现,里面会用到一个分区判断函数,也就是说,根据你语句中的where字段值,来判断你要的数据是哪个分区的,然后再从哪个分区里找数据。
而显然,你现在的问题就是,这种开销本身比分区表所带来的性能上的提高还要大,所以导致比普通表还要差一些。
可以在分區表的一個分區建立索引?
#12
楼上说的有些道理
我记得分区表文档上说过原理:数据被分成若干组后,当查询时指明了分区字段时,查询只需在指定的数据文件中查找,这比在原来未分区整个数据文件中就要少查好多数据,其性能效率必然要提高的。
我这里测试有指明分区字段,但是效果不明显,不明白是不是哪弄错了。
另外再说明一下,测试我做过很多次,很多的场景,并非只是上面问题中提及到的情况而己。
其实分区表主要的作用不是提高性能,分区表有4个特别:
1. 提高数据的可用性。
比如,原来是1个表,如果其中有一条数据损坏了,那么会导致查询数据的时候,直接报错。
而如果用了分区表,那么只是包含了损坏数据的那个分区会有问题,其他的分区没有问题,因为分区表本质上只是逻辑上一个表,而物理上已经是分成了多个表了,表和表之间都相互独立。
2.易于维护。
在迁移数据,删除数据,维护数据时,非常方便,比如你要删除一个分区的数据,直接把数据交换到另一个表中就可以,类似于truncate,秒级就完成,你要建索引,可以在某个分区上建,不会影响别的分区,这个和第1点很像。
我之前帮一个网友做的一个方案就是,在1分钟左右导入800万数据到一个表里,还有建索引,还不能影响原有表的数据,可就是用的分区表方案。
3.通过硬件的并行,来提高性能。
我们现在用的系统,特别是数据库,最大的瓶颈就是在磁盘,因为磁盘的速度比内存慢好几个数量级,而内存也比cpu慢好几个数据量级,这就导致 性能很难提高。
那么,通过增加多个物理硬盘以及相应的连接总线和控制器,同时让多个磁盘同时工作,比如你select多个分区,而这个多个分区分别放到了不同的硬盘,你查询数据的时候,这些硬盘同时访问数据,速度就能有明显的提高。
4.通过所谓的分区消除,来提高查询的性能。
这个是最后要提的,所以分区消除,就是值从一个分区里访问数据,这样能较快查询的速度。
一般意义上,大家讲到的分区提高性能,就是这一点,那为什么速度反而没有原来的普通表快呢?
这个我觉得首先要明确一点,就是这个分区消除看上去一本万利,都是好处,真的是这样吗?
开动你的大脑想一想,就知道肯定不是这么回事
为啥呢? 因为分区表不是白用的,是有开销的。如果你仔细看看分区表的执行计划,就会发现,里面会用到一个分区判断函数,也就是说,根据你语句中的where字段值,来判断你要的数据是哪个分区的,然后再从哪个分区里找数据。
而显然,你现在的问题就是,这种开销本身比分区表所带来的性能上的提高还要大,所以导致比普通表还要差一些。
可以在分區表的一個分區建立索引?
不能,索引是对整个表建立的,但是维护的时候,可以单独一个分区维护。
#13
谢谢各位回复
现在数据文件都是放在磁盘阵列上,也类似于放在不同硬盘上了吧。
看来分区表主要是还在维护管理方面比普通表有优势,结贴
现在数据文件都是放在磁盘阵列上,也类似于放在不同硬盘上了吧。
看来分区表主要是还在维护管理方面比普通表有优势,结贴
#14
学习了,我也建立了分区表,但是感觉和直接查也是一样的,本帖解决了我的疑问了。
#15
看情况吧,我也做跟你一样的测试。 分区后查询效率差了点,要不同硬盘,不过分区表还有个特别。应该也可以实现一个读写分离(如读是01分区的写是02分区的。这样就分开了)