Apache Doris使用部分日期函数导致分区裁剪失效总结

码猿小站

已于 2023-06-27 22:31:23 修改

阅读量1.1k

点赞数 1

分类专栏： Doris 文章标签： apache 大数据数据仓库 java 开源软件

于 2023-06-27 18:22:16 首次发布

本文链接：https://blog.csdn.net/weixin_43914798/article/details/131422953

版权

Doris 专栏收录该内容

12 篇文章 2 订阅

订阅专栏

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:OlapScanNode
     TABLE: ods_test_sub_partition_demo
     PREAGGREGATION: OFF. Reason: No AggregateInfo
     PREDICATES: `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__` = 0
     partitions=7/9
     rollup: ods_test_sub_partition_demo
     tabletRatio=7/7
     tabletList=21968907,21968903,21968899,21968895,21968891,21968887,21968883,21968947,21968943,21968939 ...
     cardinality=25010954
     avgRowSize=75.90796
     numNodes=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:OlapScanNode
     TABLE: ods_test_sub_partition_demo
     PREAGGREGATION: OFF. Reason: No AggregateInfo
     PREDICATES: `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__` = 0
     partitions=1/9
     rollup: ods_test_sub_partition_demo
     tabletRatio=1/1
     tabletList=21968919
     cardinality=539336
     avgRowSize=71.08554
     numNodes=3

总结：

虽然Doris内置了很多日期函数，但在分区字段上使用部分日期函数会导致分区裁剪异常，导致谓词无法下推，全量扫描数据。我们在实际应用中，合理使用日期函数会提高查询效率和系统稳定性，本文只是总结了部分不生效的情况，但其实很多函数还是可以正常，比如date_sub,date_add,days_sub,days_add,months_sub,months_add等，大家在使用时，可以选择正确的函数即可。