背景及目的

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 的所有分区。

由上述示例可见,分区剪裁使用尽管简单,但也容易出错。因此,本文将从以下两方面进行介绍:

  • 判断 SQL 中分区剪裁是否生效。

  • 了解常见的导致分区剪裁失效的场景。

判断分区剪裁是否生效

通过 explain 命令查看 SQL 的执行计划,用于发现 SQL 中的分区剪裁是否生效。  

  • 分区剪裁未生效的效果。

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


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

  • 分区剪裁生效的效果。

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


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

分区剪裁失效的场景分析

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

自定义函数导致分区剪裁失效

当分区剪裁的条件中使用了用户自定义函数,则分区剪裁会失效,即使是使用系统函数也可能会导致分区剪裁失效。所以,对于分区值的限定,如果使用了非常规函数需要用 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 因为分区剪裁使用了用户自定义的函数导致全表扫描。   

Join 使用时分区剪裁失效

在 SQL 语句中,使用 Join 进行关联时,如果分区剪裁条件放在 where 中,则分区剪裁会生效,如果放在 on 条件中,从表的分区剪裁会生效,主表则不会生效。下面针对三种 Join 具体说明。

  • 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';


      由上图可见,两张表的分区裁剪都有效果。

  • Right Outer Join

    与 Left Outer Join 类似,分区剪裁条件如果放在 on 中则只有 Right Outer Join的左表生效,如果放在 where 中,则两张表都会生效。

  • Full Outer Join

    分区剪裁条件只有都放在 where 中才会生效,放在 on 中则都不会生效。

影响及思考

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

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