分区剪裁合理性评估-白红宇

1.背景及目的

MaxCompute的是指在创建表时指定分区空间，即指定表内的某几个字段作为分区列。在使用数据时，如果指定了需要访问的分区名称，则只会读取相应的分区，避免全表扫描，提高处理效率，降低费用。

分区剪裁是指对分区列指定过滤条件，使得SQL执行时只用读取表的部分分区数据，避免全表扫描引起的数据错误及资源浪费。看起来非常简单，但是实际情况却常常出人意料，经常会出现分区失效的情况，先看看下面这个例子。

测试表test_part_cut的分区

执行以下SQL代码：

select count(*)from test_part_cutwhere 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) 分区剪裁没生效的效果

explainselect  seller_idfrom    xxxxx_trd_slr_ord_1dwhere   ds=rand();

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

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

explainselect  seller_idfrom    xxxxx_trd_slr_ord_1dwhere   ds='20150801';

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

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

分区剪裁在使用自定义函数或者部分系统函数的时候会失效，在Join关联时的Where条件中也有可能会失效。下面针对这两种场景分别举例说明。

1. 自定义函数导致分区剪裁失效当分区剪裁的条件中使用了用户自定义函数，则分区剪裁会失效，即使是使用系统函数也可能会导致分区剪裁失效。所以，对于分区值的限定，如果使用了非常规函数需要用通过查看执行计划确定分区剪裁是否已经生效。

explainselect  ...from    xxxxx_base2_brd_ind_cwwhere   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中

explainselect  a.seller_id       ,a.pay_ord_pbt_1d_001from    xxxxx_trd_slr_ord_1d aleft outer join        xxxxx_seller bon      a.seller_id=b.user_idand     a.ds='20150801'and     b.ds='20150801';

从上图可以看出两张表都走了分区剪裁。

• 分区剪裁条件均放在where中

explainselect  a.seller_id       ,a.pay_ord_pbt_1d_001from    xxxxx_trd_slr_ord_1d aleft outer join        xxxxx_seller bon      a.seller_id=b.user_idwhere   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中则都不会生效。

3.影响及思考

分区剪裁如果失效会影响比较大，且用户不容易发现。因此，分区剪裁失效最好在代码提交的时候发现比较合适。

对于用户自定义函数不能用于分区剪裁的问题，需要平台再深入思考解决方法。