关于分区表的初探

来源:互联网 发布:怎么瘦鼻子知乎 编辑:程序博客网 时间:2024/04/29 08:10
http://www.cnblogs.com/wingsless/archive/2012/02/11/2347111.html
 
 
关于分区表的初探

     上周我写了一篇博文,里面有一点关于分区表的论述(见下面:ftefno注)。但是我发现我少写了一点,在你的查询条件和分区列没有太大关系的时候,分区表不会帮助你提高效率。

     

图1

图2

     我是按照area_id分区的,图1的执行计划:

     图2的执行计划:

       建立一张表,这张表的数据和test一样,但是没有分区,执行一下图1中的语句,查看其执行计划:

       

       可以明显的看出来,分区表的执行计划多了一个PARTITION LIST ALL,明显增加了CPU的耗用。再看看图2中SQL在test111中执行的执行计划吧:

       

       确实很明显,这里少了PARTITION LIST SINGLE,但是CPU的耗用却没有变,当然了,我这个表非常非常小,如果数据量超过千万级,那么就能看出好处了。

       从上述对比中可以很明显的看出来,分区表的使用是要看实际应用的需求的。如果存储过程始终是按照某一条件对数据进行查询,就像是图2中那样,每次查询的时候总是要带上area_id,那么建表的时候就可以考虑按照area_id进行分区。但是如果你平时的查询没有什么规律可循,那么你分区了,也许好心办坏事。

      为了这篇博文,小弟在此豁出去了,不停地插表,现在搞出了一张3145728的test表和test111表,两个表数据一样,test有分区,test111没有。再看看执行计划,首先是SQL:

SELECT * FROM TEST a WHERE a.item_id = 1AND a.area_id = 290;

       

SELECT * FROM TEST111 a WHERE a.item_id = 1AND a.area_id = 290;

      然后是执行计划:

      1 

      2 

      看看,用了分区表之后虽说CPU的COST增加了,但是ROWS和BYTES都有了十分可观的降低。再将表扩大一倍,分区表和非分区表的ROWS比达到了2159K:10M,而BYTES比也达到了 121M:594M,CPU COST比:14487:8847。上帝啊,分区表在降低读取量方面堪称出色,但是在增加CPU COST方面堪称令人发指。

      以前看过盖国强的书,里面说优化SQL主要是降低其物理读。但是我想如果能降低这里的ROWS和BYTES,对于一个小机环境的数据库处理器来说,高一点的CPU COST也是可以理解的吧。

      有什么不妥之处,请大家留言指正。

 
 
========================================================================================================================
 
 
 
rank() over(partition)的使用
 
 
有的时候会遇到这样的问题,我们需要查询一张表,而且要按照业务排序,比如我需要如下的结果:

    地区   日期    费用  产品编号   用户编号

     290 201202 258 1              s1
     290 201202 200 1              s5
     290 201202 100 1              s100
     290 201202 90   2              s7
     290 201202 88   2              s9
     290 201202 10   2              s12。 

     领导让我出一张报表,需要看到每一个业务的收费前三名是那些客户。这个时候用rank() over(partition)是一个很不错的选择。

     我的测试表就像上面例子中的表一样,不过数据稍微多一点点。给大家一个截图:

     

      可以看到我每一个项目都有5条记录,我只取前三,那么SQL如下:

      

复制代码
SELECT A.AREA_ID, A.ACCT_MONTH, A.FEE, A.ITEM_ID, A.USER_ID  FROM (SELECT T.AREA_ID,               T.ACCT_MONTH,               T.FEE,               T.ITEM_ID,               T.USER_ID,               RANK() OVER(PARTITION BY T.ITEM_ID ORDER BY T.FEE DESC) RK          FROM TEST T) A WHERE RK < 4;
复制代码

       该语句执行的结果就是上述的情况了。

       一不做二不休,我顺便查看一下该语句的执行计划好了。

       首先写一下我的建表语句:

       

复制代码
CREATE TABLE TEST(  area_id NUMBER,  acct_month NUMBER,  fee NUMBER,  item_id NUMBER)PARTITION BY LIST(area_id)(  PARTITION part_290 VALUES('290'),  PARTITION part_910 VALUES('910'),  PARTITION part_911 VALUES('911'),  partition part_912 values('912'),  partition part_913 values('913'),  partition part_914 values('914'),  partition part_915 values('915'),  partition part_916 values('916'),  partition part_917 values('917'),  partition part_919 values('919'),  partition part_default values(default))
复制代码

      我按照地域进行了分区,其实也可以按照时间进行分区。explain一下plan:

       

      可以看到,只有10条记录的表,COST却高达4,不得不说采取这个办法会极大地降低查询的效率。但是业务上需要的话,rank() over确实是一个很好使的玩意儿。

      加两条数据进去,顺便测测分区表是否真的可以在没有索引的情况下提高一点点效率。加了两条数据,area_id是911,陕西省宝鸡市的区号:0911。语句也稍微变动一下:

复制代码
SELECT A.AREA_ID, A.ACCT_MONTH, A.FEE, A.ITEM_ID, A.USER_ID  FROM (SELECT T.AREA_ID,               T.ACCT_MONTH,               T.FEE,               T.ITEM_ID,               T.USER_ID,               RANK() OVER(PARTITION BY T.ITEM_ID ORDER BY T.FEE DESC) RK          FROM TEST T          WHERE t.area_id = 290) A WHERE RK < 4;
复制代码

      explain一下plan:

      

      发现虽然现在是12行数据,但是因为我只查询西安市(290),所以在rows里仍旧只是10行,没有新添加的宝鸡市的2行。由此可见,在面对非常海量的数据存储时,按照一定的条件建立分区,是十分有必要的。不过按照时间建立分区可能会稍微麻烦点,因为时间在不停的推进,多少年之后,你现在建立的分区就已经不可能再用了,就要添加新的分区进去,这也是一个挺讨厌的事情。

原创粉丝点击