MaxCompute分区表和非分区表使用对比

本文我们将通过对有同样数据量、表结构除分区列其他都一模一样的表,从查询计算、写入、删除数据几个简单操作进行对比，了解MaxCompute分区表和非分区表在使用上有什么差异。

在介绍之前，需要大家先了解MaxCompute分区的概念。

数据准备

分区表：取公共数据集中的表dwd_prouduct_house_basic_info_out（二手房产数据集）；

非分区表：执行建表语句：

create table dwd_prouduct_house_basic_info_out_npt as select * from public_data.dwd_prouduct_house_basic_info_out;
    

创建表的同时将源表的所有数据都复制到新表dwd_prouduct_house_basic_info_out_npt中。
由于create table … as select … 语句创建的表不会复制分区属性，只会把源表的分区列作为目标表的一般列处理，所以新表dwd_prouduct_house_basic_info_out_npt为非分区表。

可以分别执行select count(*) from public_data.dwd_prouduct_house_basic_info_out; 和 select count(*) from dwd_prouduct_house_basic_info_out_npt;查看这两个表的记录数会是一样。

本次操作表的记录数为1147676063条。

计算对比

我们执行一个简单的查询某个分区数据的job：

Select * from public_data.dwd_prouduct_house_basic_info_out where ds= '20170113';--分区表查询
Select * from dwd_prouduct_house_basic_info_out_npt where ds= '20170113';--非分区表查询

计算时长对比

计算资源充足的情况下进行操作。

• 分区表里查询使用时间1秒，：

  ![image](https://yqfile.alicdn.com/f0b14b6c41c665568e53cff9ce2bf01fd29ae212.png)
  
  直接在对应分区中取出该分区所有数据。
  

• 非分区里表查询：

  加上job等待时间共1分15秒
  ![image](https://yqfile.alicdn.com/470444f8b68cf80ce9d4e11e48f59a5b5f17c549.png)
  真正执行时长53秒
  ![image](https://yqfile.alicdn.com/cf8dfc1ef56a50b6be4eeda8055491cea96f9530.png)
  
  需要在整个表1147676063条记录中取出满足条件的数据。
  ![image](https://yqfile.alicdn.com/157dad8d5793761cbaa95e30d8cc340f23e0bccc.png)
  

计算费用对比

我们可以直接通过大数据开发套件->“数据开发”工作区中的“成本估计”对两条查询语句进行费用预估，该预估功能采用的计费公式可参考“[计量计费->I/O后付费](https://help.aliyun.com/document_detail/27989.html
)”。

若采用计算预付费模式，可不用在意该计算费用。

下图是通过成本估计功能预估的费用，结果显示在非分区表中查询一样条件的数据会花费更多，当然最终花费还得看最后的账单。

table size对比

由于MaxCompute存储压缩比不一定完全一致，两个表数据在MaxCompute的size也会有一定的差异。

desc public_data.dwd_prouduct_house_basic_info_out; 分区表执行结果如下图：

desc dwd_prouduct_house_basic_info_out_npt; 非分区表执行结果如下图：

写入对比

创建三个表，表结构除了分区列，其他都一致：

• 非分区表，

  create  table   house_test_npt(house_id string  , house_total_price string , house_unit_price string , house_type string , house_floor string , house_direction string , house_deckoration string , house_area string , house_community_name string , house_region string  , house_city string, ds string)
  

• 以时间为分区的分区表

  create  table   house_test_pt_1(house_id string  , house_total_price string , house_unit_price string , house_type string , house_floor string , house_direction string , house_deckoration string , house_area string , house_community_name string , house_region string  , house_city string ) partitioned by ( ds string)
  

• 以时间为一级分区，城市为二级分区的分区表

  create  table   house_test_pt(house_id string  , house_total_price string , house_unit_price string , house_type string , house_floor string , house_direction string , house_deckoration string , house_area string , house_community_name string , house_region string ) partitioned by (ds string , house_city string)
  

分别执行3个insert语句，源表取一模一样的数据，即select分句一模一样：

insert overwrite table house_test_npt select house_id,house_total_price,house_unit_price,house_type,house_floor,house_direction,house_deckoration,house_area,house_community_name,house_region,house_city,ds from public_data.dwd_prouduct_house_basic_info_out where ds ='20170114';--写入非分区表

insert overwrite table house_test_pt_1 partition (ds='20170114') select house_id,house_total_price,house_unit_price,house_type,house_floor,house_direction,house_deckoration,house_area,house_community_name,house_region,house_city from public_data.dwd_prouduct_house_basic_info_out where ds ='20170114';--写入一级分区表

insert overwrite table house_test_pt partition (ds='20170114',house_city) select house_id,house_total_price,house_unit_price,house_type,house_floor,house_direction,house_deckoration,house_area,house_community_name,house_region,house_city from public_data.dwd_prouduct_house_basic_info_out where ds ='20170114';--使用动态分区方式写入二级分区表

对比执行过程：

• 非分区表，一个task，耗时41s：

  ![image](https://yqfile.alicdn.com/d1ac9fe2ebcfc6c664a872a20490d937ffb2391a.png)
  

• 一级分区表，一个task，耗时41s：

  ![image](https://yqfile.alicdn.com/6e3ef3aa8672220c78f7d5ca708a4784b9458011.png)
  

• 二级分区表，第二级分区值为动态输入，2个task，最长task耗时1分31秒：

  ![image](https://yqfile.alicdn.com/359afdc276eb8b2d3552e79649204048a9309b2c.png)
  

数据删除操作对比

假设要删除表某一天的数据，由于MaxCompute不支持delete语法，我们需要通过别的方式进行数据删除。

• 分区表只需要通过alter语句drop对应分区即可，如下删除分区为20170112的数据：

  ALTER TABLE public_data.ods_enterprise_share_basic DROP IF EXISTS PARTITION (ds='20170112');
  

• 非分区表，需要将不需要删除的数据查询出来并覆盖写入方式写回表中，增加了计算量。

  INSERT OVERWRITE TABLE dwd_prouduct_house_basic_info_out_npt SELECT * FROM dwd_prouduct_house_basic_info_out_npt WHERE ds<>'20170112';