分区字段带子查询后未能正确指定分区而走了全表扫描

Viewed 60
2.1 query

建表语句

CREATE TABLE `test1` (
  `datatime` datetime NOT NULL COMMENT '数据时间',
  `id` varchar(255) NOT NULL COMMENT 'id',
  `name` varchar(255) REPLACE NULL COMMENT '名称',
  `storage_time` datetime REPLACE NULL DEFAULT CURRENT_TIMESTAMP COMMENT '插入时间'
) ENGINE=OLAP
AGGREGATE KEY(`datatime`,`id`)
COMMENT ''
PARTITION BY RANGE(`datatime`)
(PARTITION p20240729 VALUES [('2024-07-29 00:00:00'), ('2024-07-30 00:00:00')),
PARTITION p20241020 VALUES [('2024-10-20 00:00:00'), ('2024-10-21 00:00:00')),
PARTITION p20241117 VALUES [('2024-11-17 00:00:00'), ('2024-11-18 00:00:00')))
DISTRIBUTED BY HASH(`id`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_format" = "V2",
"inverted_index_storage_format" = "DEFAULT",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);

测试数据

insert into test1 values('2024-07-29 12:10:00','1', 't1', '2024-10-11 12:10:00'),
('2024-10-20 12:10:00','2', 't2', '2024-10-11 12:10:00'),('2024-11-17 12:10:00','3', 't3', '2024-10-11 12:10:00');

问题SQL

explain select * from test1  where datatime = (select max(datatime) from test1 where datatime >= '2024-11-10 00:00:00')

预期是从最新的一个分区中查询数据，但实际scan的是全表三个分区partitions=3/3 (p20240729,p20241020,p20241117) (请忽略图中的分区名称)
image.png

zhangzeqin26
1 Answers

您具体是哪个版本来着，我这边按照您提供的case，用社区2.1.6 进行测试后，发现分区裁剪是正常的，只scan了一个partition：partitions=1/3 (p20241117) ，可能是之前优化器的bug，建议您升级到2.1.6以上版本。

mysql> explain select * from test1  where datatime = (select max(datatime) from test1 where datatime >= '2024-11-10 00:00:00');
+----------------------------------------------------------------------------------------------------------------------------+
| Explain String(Old Planner)                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0                                                                                                            |
|   OUTPUT EXPRS:                                                                                                            |
|     <slot 8> <slot 4>                                                                                                      |
|     <slot 9> <slot 5>                                                                                                      |
|     <slot 10> <slot 6>                                                                                                     |
|     <slot 11> <slot 7>                                                                                                     |
|   PARTITION: UNPARTITIONED                                                                                                 |
|                                                                                                                            |
|   HAS_COLO_PLAN_NODE: false                                                                                                |
|                                                                                                                            |
|   VRESULT SINK                                                                                                             |
|      MYSQL_PROTOCAL                                                                                                        |
|                                                                                                                            |
|   7:VEXCHANGE                                                                                                              |
|      offset: 0                                                                                                             |
|                                                                                                                            |
| PLAN FRAGMENT 1                                                                                                            |
|                                                                                                                            |
|   PARTITION: RANDOM                                                                                                        |
|                                                                                                                            |
|   HAS_COLO_PLAN_NODE: false                                                                                                |
|                                                                                                                            |
|   STREAM DATA SINK                                                                                                         |
|     EXCHANGE ID: 07                                                                                                        |
|     UNPARTITIONED                                                                                                          |
|                                                                                                                            |
|   3:VHASH JOIN                                                                                                             |
|   |  join op: LEFT SEMI JOIN(BROADCAST)[Src column hash been transformed by expr]                                          |
|   |  equal join conjunct: (`datatime` = max(`datatime`))                                                                   |
|   |  runtime filters: RF000[min_max] <- max(`datatime`)(-1/0/2097152), RF001[in_or_bloom] <- max(`datatime`)(-1/0/2097152) |
|   |  cardinality=0                                                                                                         |
|   |  vec output tuple id: 5                                                                                                |
|   |  output tuple id: 5                                                                                                    |
|   |  vIntermediate tuple ids: 6 7                                                                                          |
|   |  output slot ids: 8 9 10 11                                                                                            |
|   |  hash output slot ids: 4 5 6 7                                                                                         |
|   |                                                                                                                        |
|   |----6:VEXCHANGE                                                                                                         |
|   |       offset: 0                                                                                                        |
|   |                                                                                                                        |
|   0:VOlapScanNode                                                                                                          |
|      TABLE: test.test1(test1), PREAGGREGATION: OFF. Reason: No AggregateInfo                                               |
|      runtime filters: RF000[min_max] -> `datatime`, RF001[in_or_bloom] -> `datatime`                                       |
|      partitions=3/3 (p20240729,p20241020,p20241117)                                                                        |
|      tablets=30/30, tabletList=250272,250274,250276 ...                                                                    |
|      cardinality=0, avgRowSize=48.0, numNodes=1                                                                            |
|      pushAggOp=NONE                                                                                                        |
|                                                                                                                            |
| PLAN FRAGMENT 2                                                                                                            |
|                                                                                                                            |
|   PARTITION: UNPARTITIONED                                                                                                 |
|                                                                                                                            |
|   HAS_COLO_PLAN_NODE: false                                                                                                |
|                                                                                                                            |
|   STREAM DATA SINK                                                                                                         |
|     EXCHANGE ID: 06                                                                                                        |
|     UNPARTITIONED                                                                                                          |
|                                                                                                                            |
|   5:VAGGREGATE (merge finalize)                                                                                            |
|   |  output: max(max(`datatime`))                                                                                          |
|   |  group by:                                                                                                             |
|   |  cardinality=-1                                                                                                        |
|   |                                                                                                                        |
|   4:VEXCHANGE                                                                                                              |
|      offset: 0                                                                                                             |
|                                                                                                                            |
| PLAN FRAGMENT 3                                                                                                            |
|                                                                                                                            |
|   PARTITION: RANDOM                                                                                                        |
|                                                                                                                            |
|   HAS_COLO_PLAN_NODE: false                                                                                                |
|                                                                                                                            |
|   STREAM DATA SINK                                                                                                         |
|     EXCHANGE ID: 04                                                                                                        |
|     UNPARTITIONED                                                                                                          |
|                                                                                                                            |
|   2:VAGGREGATE (update serialize)                                                                                          |
|   |  output: max(`datatime`)                                                                                               |
|   |  group by:                                                                                                             |
|   |  cardinality=1                                                                                                         |
|   |                                                                                                                        |
|   1:VOlapScanNode                                                                                                          |
|      TABLE: test.test1(test1), PREAGGREGATION: ON                                                                          |
|      PREDICATES: (`datatime` >= '2024-11-10 00:00:00')                                                                     |
|      partitions=1/3 (p20241117)                                                                                            |
|      tablets=10/10, tabletList=250292,250294,250296 ...                                                                    |
|      cardinality=0, avgRowSize=8.0, numNodes=1                                                                             |
|      pushAggOp=NONE                                                                                                        |
+----------------------------------------------------------------------------------------------------------------------------+
88 rows in set (0.05 sec)
阿渊@SelectDB5460