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等，大家在使用时，可以选择正确的函数即可。
