Apache Doris使用部分日期函数导致分区裁剪失效总结
背景:
Apache Doris 是一个分布式的MPP分析型数据仓库,能够实现数据的高并发查询和亚秒级响应需求。在FE和BE两个节点中,FE节点主要功能是与客户端进行交互,元数据管理及查询计划规划等;BE则主要负责数据的存储和查询计划的执行等。
BE节点数据存储划分主要分为两级:分区和分桶。
分区(Partition):主要有日期分区和枚举List分区两种形式,是一种较粗粒度的数据裁剪。
分桶(Bucket): 是一种更细粒度的数据裁剪,建立在分区基础之上,根据指定的分桶列字段, 通过哈希函数,将分区中的数据散列在不同的Bucket中。
在实际环境中,对分区表而言,通常会采用日期分区的方式,根据预期的分区字段,将一个周,一个月或者一年的数据存放在一个分区中,这样,在进行数据查询时,我们可以根据分区字段来过滤数据,减少数据的扫描量,避免大查询给集群带来较大的计算压力,从而影响集群的整体稳定性。
通过分区和分桶策略在很大程度上能够提高查询性能。按照date或者datetime字段类型分区的数据表,可能我们在查询时需要对分区字段的数据进行一些特殊处理。
比如:如果分区字段类型为datetime类型,但我们想查询整月的数据,那我们可能更倾向于将datetime先转换为date类型,然后再根据create_date = ”2023-06“这种形式来查询6月份的数据。
对此,Doris官网为我们提供了很多日期函数,让我们能够更好的操作日期字段。但在实际应用过程中,也发现了一些问题:
比如:如果我们根据create_date字段按月分区创建一个分区表,当我们在查询create_date = ”2023-06“的数据时,我们期望的是只扫描2023-06分区的数据,而不是全部的数据,但实际发现,在SQL中使用部分日期函数后发现会导致分区裁剪失效,导致全表扫描数据,这完全不符合我们的预期效果。下面会列举一些导致分区裁剪失效的日期函数,大家在使用过程中,要谨慎使用。
结论:
根据测试,会导致分区裁剪异常的情况主要有以下几种:
-
如果符号两边同时对分区字段使用函数,分区不会进行裁剪:
DATE_FORMAT(order_create_time,'%Y-%m') = DATE_FORMAT(curdate(),'%Y-%m')
-
如果WHERE条件中对分区字段使用
DATE_FORMAT
,不管是单个使用还是组合使用DATE_FORMAT
,分区都不会进行裁剪:order_create_date = DATE_FORMAT(curdate(),'%Y-%m')order_create_time = DATE_SUB(DATE_FORMAT(curdate(),'%Y-%m-%d') , INTERVAL 1 day )
-
如果WHERE条件中对分区字段使用
DAY_FLOOR
,DAY_CEIL
,MONTH_FLOOR
,MONTH_CEIL
,YEAR_FLOOR
,YEAR_CEIL
这些函数,数据也不会分区裁剪:DAY_FLOOR(CURDATE());DAY_CEIL(CURDATE()) MONTH_FLOOR(CURDATE());MONTH_CEIL(CURDATE()); YEAR_FLOOR(CURDATE());YEAR_CEIL(CURDATE())
-
如果WHERE条件中对分区字段使用部分组合函数,可能也会导致分区裁剪失效,导致全表扫描:
DATE_SUB(curdate(), INTERVAL DAYOFMONTH(curdate()) -1 DAY)DATE_SUB(CURDATE(), dayofyear(CURDATE())-1)
-
如果WHERE条件中对分区字段使用
IF
条件判断,目前只能使用DATE_FORMAT
,其他函数替代函数暂时没办法解决:DATE_FORMAT(if(hour(now()) < 8 ,DATE_SUB(curdate(),1),curdate()),'yyyy-MM-dd')
-
在使用
CURDATE()
和CURRENT_DATE()
这个两个函数时,发现同样的SQL语句,使用CURDATE()
可以走分区裁剪,使用CURRENT_DATE()
这个函数不会走分区裁剪,这块要注意:str_to_date(CURDATE(),'%Y-%m-%d') --走分区裁剪 str_to_date(CURRENT_DATE(),'%Y-%m-%d') --不走分区裁剪
-
使用Doris 1.2版本中最新的日期函数
LAST_DAY()
也不会进行分区裁剪。
解决方案:
我们可以通过一些其他函数,来代替以上异常函数,避免全表扫描:
1.格式化日期:
date -> datetime | SELECT str_to_date(CURDATE(), ‘%Y-%m-%d %H:%i:%s’) | 2023-06-25 00:00:00 |
datetime ->date | SELECT str_to_date(‘2023-06-25 00:00:00’, ‘%Y-%m-%d’) | 2023-06-25 |
2.计算今天
返回datetime类型
函数 | 返回结果 | |
---|---|---|
今天开始 | select str_to_date(CURDATE(), ‘%Y-%m-%d %H:%i:%s’) | 2023-06-25 00:00:00 |
今天结束 | select str_to_date(date_add(CURDATE(),1), ‘%Y-%m-%d %H:%i:%s’) | 2023-06-26 00:00:00 |
今天结束 | select str_to_date(days_add(CURDATE(),1), ‘%Y-%m-%d %H:%i:%s’) | 2023-06-26 00:00:00 |
3.计算本月
返回date类型。
函数 | 返回结果 | |
---|---|---|
本月开始 | SELECT date_sub(CURDATE(), day(CURDATE()) -1) | 2023-06-01 |
本月结束 | SELECT months_add(date_sub(CURDATE(), day(CURDATE()) -1) , 1) | 2023-07-01 |
本月结束 | SELECT months_add(days_sub(CURDATE(), day(CURDATE()) -1) , 1) | 2023-07-01 |
4.计算上个月及下个月
函数 | 返回结果 | |
---|---|---|
上个月1号 | SELECT months_sub(days_sub(CURDATE(), day(CURDATE()) -1) , 1) | 2023-05-01 |
下个月今天 | SELECT months_add(CURDATE() , 1 ) | 2023-07-25 |
5.计算今年
函数 | 返回结果 | |
---|---|---|
今年年初 | SELECT months_sub(days_sub(CURDATE(), day(CURDATE()) -1), month(CURDATE())-1) | 2023-01-01 |
今年年初 | select date_sub(days_sub(CURDATE(), day(CURDATE()) -1), INTERVAL MONTH(CURDATE()) -1 month) | 2023-01-01 |
今年年底 | select years_add(months_sub(days_sub(CURDATE(), day(CURDATE()) -1), month(CURDATE())-1), 1) | 2024-01-01 |
如何判断一个SQL是否进行分区裁剪
举例:
表创建语句:
CREATE TABLE `ods_test_sub_partition_demo` (`create_time` datetime NULL COMMENT "创建时间",`execution_id` varchar(96) NULL COMMENT "执行单id",`user_id` bigint(20) NULL COMMENT "用户id",`_is_delete` varchar(5) NULL DEFAULT "1" COMMENT "删除标识:1:未删除,0:已删除",`updateStamp` datetime NULL COMMENT "落库时间"
) ENGINE=OLAP
UNIQUE KEY(`create_time`, `execution_id`, `user_id`)
COMMENT "测试分区裁剪表"
PARTITION BY RANGE(`create_time`)
(PARTITION P_000000 VALUES [('0000-01-01 00:00:00'), ('2023-01-01 00:00:00')),
PARTITION P_202301 VALUES [('2023-01-01 00:00:00'), ('2023-02-01 00:00:00')),
PARTITION P_202302 VALUES [('2023-02-01 00:00:00'), ('2023-03-01 00:00:00')),
PARTITION P_202303 VALUES [('2023-03-01 00:00:00'), ('2023-04-01 00:00:00')),
PARTITION P_202304 VALUES [('2023-04-01 00:00:00'), ('2023-05-01 00:00:00')),
PARTITION P_202305 VALUES [('2023-05-01 00:00:00'), ('2023-06-01 00:00:00')),
PARTITION P_202306 VALUES [('2023-06-01 00:00:00'), ('2023-07-01 00:00:00')),
PARTITION P_202307 VALUES [('2023-07-01 00:00:00'), ('2023-08-01 00:00:00')),
PARTITION P_202308 VALUES [('2023-08-01 00:00:00'), ('2023-09-01 00:00:00')))
DISTRIBUTED BY HASH(`execution_id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "MONTH",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-2147483648",
"dynamic_partition.end" = "2",
"dynamic_partition.prefix" = "P_",
"dynamic_partition.replication_allocation" = "tag.location.default: 3",
"dynamic_partition.buckets" = "1",
"dynamic_partition.create_history_partition" = "false",
"dynamic_partition.history_partition_num" = "-1",
"dynamic_partition.hot_partition_num" = "0",
"dynamic_partition.reserved_history_periods" = "NULL",
"dynamic_partition.start_day_of_month" = "1",
"in_memory" = "false",
"storage_format" = "V2"
);
查询本月的数据:
explain SELECT * from test.ods_test_sub_partition_demo where create_time > month_floor(curdate()) and create_time < month_ceil(curdate())
从SQL中可以看到,只是查询了create_time为当前月的所有数据,该表按月分区,理论上只会扫描P_202306这一个分区,但实际情况我们通过explain看到,这个表一共9个分区,该SQL扫描了7个分区,扫描基数25010954,全表扫描所有数据。
0:OlapScanNodeTABLE: ods_test_sub_partition_demoPREAGGREGATION: OFF. Reason: No AggregateInfoPREDICATES: `create_time` > month_floor('2023-06-25'), `create_time` < month_ceil('2023-06-25'), `default_cluster:test.ods_test_sub_partition_demo`.`__DORIS_DELETE_SIGN__` = 0partitions=7/9rollup: ods_test_sub_partition_demotabletRatio=7/7tabletList=21968907,21968903,21968899,21968895,21968891,21968887,21968883,21968947,21968943,21968939 ...cardinality=25010954avgRowSize=75.90796numNodes=10
这个执行计划明显是不正确的,我们可以修改一下SQL语句:
explain SELECT * from test.ods_test_sub_partition_demo where create_time > str_to_date(date_sub(CURDATE(), day(CURDATE()) -1), '%Y-%m-%d %H:%i:%s') and create_time < str_to_date(month_ceil(curdate()), '%Y-%m-%d %H:%i:%s')
我们再看一下执行计划:
从下面这个执行计划可以看到,SQL总共只扫描了1个分区,扫描基数只有539336,扫描数据量为原来的1/50,符合我们的预期。
0:OlapScanNodeTABLE: ods_test_sub_partition_demoPREAGGREGATION: OFF. Reason: No AggregateInfoPREDICATES: `create_time` > '2023-06-01 00:00:00', `create_time` < str_to_date(month_ceil('2023-06-25'), '%Y-%m-%d %H:%i:%s'), `default_cluster:test.ods_test_sub_partition_demo`.`__DORIS_DELETE_SIGN__` = 0partitions=1/9rollup: ods_test_sub_partition_demotabletRatio=1/1tabletList=21968919cardinality=539336avgRowSize=71.08554numNodes=3
总结:
虽然Doris内置了很多日期函数,但在分区字段上使用部分日期函数会导致分区裁剪异常,导致谓词无法下推,全量扫描数据。我们在实际应用中,合理使用日期函数会提高查询效率和系统稳定性,本文只是总结了部分不生效的情况,但其实很多函数还是可以正常,比如date_sub
,date_add
,days_sub
,days_add
,months_sub
,months_add
等,大家在使用时,可以选择正确的函数即可。