分区剪裁合理性评估-CSDN博客

1.背景及目的

MaxCompute的分区表是指在创建表时指定分区空间，即指定表内的某几个字段作为分区列。在使用数据时，如果指定了需要访问的分区名称，则只会读取相应的分区，避免全表扫描，提高处理效率，降低费用。
分区剪裁是指对分区列指定过滤条件，使得SQL执行时只用读取表的部分分区数据，避免全表扫描引起的数据错误及资源浪费。看起来非常简单，但是实际情况却常常出人意料，经常会出现分区失效的情况，先看看下面这个例子。
测试表test_part_cut的分区
执行以下SQL代码：

select count(*)
from test_part_cut
where ds= bi_week_dim('20150102');
其中为bi_week_dim自定义函数:返回格式为 （年,第几周）
如果是正常日期，判断日期是所传入参数中年份所属
周，以周四为一周的起始日期，如果碰到20140101因为属于
周三所以算在2013年最后一周返回2013,52
而20150101则返回是2015,1
如果是类似20151231是周四又恰逢与20160101在同一周
则返回2016,1

bi_week_dim('20150102')的返回结果是2015,1，不符合表test_part_cut的分区值，通常我们会认为上面的SQL不会读任何分区，而实际情况却是该SQL读了表test_part_cut的所有分区，看一下LogView截图：

从上图可以看出该SQL在执行的时候读取了表test_part_cut的所有分区。
从上面例子可以看出，分区剪裁使用尽管简单，但也容易造成错误。因此，本文主要的目的有两点：(1) 判断SQL中分区剪裁是否生效。 (2) 了解常见的导致分区剪裁失效的场景。

2.方案

2.1判断分区剪裁是否生效

通过explain命令查看SQL的执行计划，用于发现SQL中的分区剪裁是否生效。
(1) 分区剪裁没生效的效果

explain
select  seller_id
from    xxxxx_trd_slr_ord_1d
where   ds=rand();

看上图的红色框的内容，表示读取了表xxxxx_trd_slr_ord_1d的1344个分区，即该表的所有分区。

(2) 分区剪裁生效的效果

explain
select  seller_id
from    xxxxx_trd_slr_ord_1d
where   ds='20150801';

看上图红色框的内容，表示只读取了表xxxxx_trd_slr_ord_1d的20150801的分区。

2.2 分区剪裁失效的场景分析

分区剪裁在使用自定义函数或者部分系统函数的时候会失效，在Join关联时的Where条件中也有可能会失效。下面针对这两种场景分别举例说明。
1. 自定义函数导致分区剪裁失效当分区剪裁的条件中使用了用户自定义函数，则分区剪裁会失效，即使是使用系统函数也可能会导致分区剪裁失效。所以，对于分区值的限定，如果使用了非常规函数需要用explain命令通过查看执行计划确定分区剪裁是否已经生效。

explain
select  ...
from    xxxxx_base2_brd_ind_cw
where   ds = concat(SPLIT_PART(bi_week_dim(' ${bdp.system.bizdate}'), ',', 1), SPLIT_PART(bi_week_dim(' ${bdp.system.bizdate}'), ',', 2))

可以看出上面的SQL因为分区剪裁使用了用户自定义的函数导致全表扫描。
2. Join使用时分区剪裁失效在SQL语句中使用Join进行关联时，如果分区剪裁条件放在on中则分区剪裁会生效，如果放在where条件中，主表的分区剪裁会生效，从表则不会生效。下面针对三种Join具体说明。
(1) Left Outer Join
• 分区剪裁条件均放在on中

explain
select  a.seller_id
       ,a.pay_ord_pbt_1d_001
from    xxxxx_trd_slr_ord_1d a
left outer join
        xxxxx_seller b
on      a.seller_id=b.user_id
and     a.ds='20150801'
and     b.ds='20150801'
;

从上图可以看出两张表都走了分区剪裁。
• 分区剪裁条件均放在where中

explain
select  a.seller_id
       ,a.pay_ord_pbt_1d_001
from    xxxxx_trd_slr_ord_1d a
left outer join
        xxxxx_seller b
on      a.seller_id=b.user_id
where   a.ds='20150801'
and     b.ds='20150801';

从上图可以看出主表的分区剪裁有效果，而从表则是进行了全表扫描。
(2) Right Outer Join 与Left Outer Join类似，分区剪裁条件如果放在on中则两张表都会生效，如果放在where中，则只有Right Outer Join右侧的主表会生效，不再举例。
(3) Full Outer Join 分区剪裁条件只有都放在on中才会生效，放在where中则都不会生效。