查询SQL：
select user_key from customer_group_data_latest where cg_code = ? and user_key = ?
表结构
CREATE TABLE `customer_group_data_latest_b128` (
`cg_code` varchar(1024) NOT NULL COMMENT '客群code 分区健',
`user_key` varchar(1024) NULL COMMENT ' 档案baseid或者oneid',
`cg_version` varchar(1024) NULL COMMENT ' 客群版本'
) ENGINE=OLAP
UNIQUE KEY(`cg_code`, `user_key`)
PARTITION BY LIST(`cg_code`)
(PARTITION p10013541732702028116 VALUES IN ("1001354-1732702028116"),
PARTITION p2571873353891851732066878043 VALUES IN ("257187335389185-1732066878043"),
PARTITION p2571925572976641732069368672 VALUES IN ("257192557297664-1732069368672"),
PARTITION p2573992263024651732167915920 VALUES IN ("257399226302465-1732167915920"),
PARTITION p2573993605201921732167979305 VALUES IN ("257399360520192-1732167979305"),
PARTITION p2573995199037451732168055877 VALUES IN ("257399519903745-1732168055877"),
PARTITION p2574000420945921732168304561 VALUES IN ("257400042094592-1732168304561"),
PARTITION p2574022462013451732169355329 VALUES IN ("257402246201345-1732169355329"),
PARTITION p2574036030586891732170002730 VALUES IN ("257403603058689-1732170002730"),
PARTITION p2574037750251521732170084859 VALUES IN ("257403775025152-1732170084859"),
PARTITION p2574118742261771732173946285 VALUES IN ("257411874226177-1732173946285"),
PARTITION p2574119245578241732173970636 VALUES IN ("257411924557824-1732173970636"),
PARTITION p2574135540449281732174747068 VALUES IN ("257413554044928-1732174747068"),
PARTITION p2574194596249611732177563578 VALUES IN ("257419459624961-1732177563578"),
PARTITION p2574329841582091732184012002 VALUES IN ("257432984158209-1732184012002"),
PARTITION p2586870512189451732781998084 VALUES IN ("258687051218945-1732781998084"),
PARTITION p2601173570191361733464021102 VALUES IN ("260117357019136-1733464021102"),
PARTITION p2606438086901771733715053127 VALUES IN ("260643808690177-1733715053127"),
PARTITION p2626626745303051734677723554 VALUES IN ("262662674530305-1734677723554"),
PARTITION p2773920363642891741701230140 VALUES IN ("277392036364289-1741701230140"),
PARTITION pp2 VALUES IN ("3254-1716954313760"))
DISTRIBUTED BY HASH(`cg_code`, `user_key`) BUCKETS 128
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"store_row_column" = "true",
"row_store_page_size" = "16384",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);
执行计划，显式走 short-circuit
explain select user_key from customer_group_data_latest_b128 where cg_code = '260117357019136-1733464021102' and user_key = '200000000000003709'
+--------------------------------------------------------------------------------------------------------------------------------------------------+
|Explain String(Nereids Planner) |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
|PLAN FRAGMENT 0 |
| OUTPUT EXPRS: |
| user_key[#6] |
| PARTITION: UNPARTITIONED |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| VRESULT SINK |
| MYSQL_PROTOCAL |
| |
| 1:VEXCHANGE |
| offset: 0 |
| distribute expr lists: user_key[#6] |
| |
|PLAN FRAGMENT 1 |
| |
| PARTITION: HASH_PARTITIONED: cg_code[#0], user_key[#1] |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 01 |
| UNPARTITIONED |
| |
| 0:VOlapScanNode(152) |
| TABLE: cdp_tech_dev.customer_group_data_latest_b128(customer_group_data_latest_b128), PREAGGREGATION: ON |
| PREDICATES: (((cg_code[#0] = '260117357019136-1733464021102') AND (user_key[#1] = '200000000000003709')) AND (__DORIS_DELETE_SIGN__[#3] = 0))|
| partitions=1/21 (p2601173570191361733464021102) |
| tablets=1/128, tabletList=195621471 |
| cardinality=831, avgRowSize=0.0, numNodes=1 |
| pushAggOp=NONE |
| SHORT-CIRCUIT |
| final projections: user_key[#1] |
| final project output tuple id: 1 |
| |
| |
|Statistics |
| planed with unknown column statistics |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
并发点查时CPU40%。
另一张表，结构完全一样，分区数量增大到924时。 相同查询，相同数据量，CPU飙到100%。
查询计划
+--------------------------------------------------------------------------------------------------------------------------------------------------+
|Explain String(Nereids Planner) |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
|PLAN FRAGMENT 0 |
| OUTPUT EXPRS: |
| user_key[#6] |
| PARTITION: UNPARTITIONED |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| VRESULT SINK |
| MYSQL_PROTOCAL |
| |
| 1:VEXCHANGE |
| offset: 0 |
| distribute expr lists: user_key[#6] |
| |
|PLAN FRAGMENT 1 |
| |
| PARTITION: HASH_PARTITIONED: cg_code[#0], user_key[#1] |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 01 |
| UNPARTITIONED |
| |
| 0:VOlapScanNode(152) |
| TABLE: cdp_tech_dev.customer_group_data_latest(customer_group_data_latest), PREAGGREGATION: ON |
| PREDICATES: (((cg_code[#0] = '260117357019136-1733464021102') AND (user_key[#1] = '200000000000003709')) AND (__DORIS_DELETE_SIGN__[#3] = 0))|
| partitions=1/924 (p2601173570191361733464021102) |
| tablets=1/1, tabletList=34943540 |
| cardinality=831, avgRowSize=0.0, numNodes=1 |
| pushAggOp=NONE |
| SHORT-CIRCUIT |
| final projections: user_key[#1] |
| final project output tuple id: 1 |
| |
| |
|Statistics |
| planed with unknown column statistics |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
