【Doris 使用环境】测试
【Doris 版本】2.0.0(2023-08-11)
【问题描述】
具体表现：
sql执行较慢，cg没有生效
--执行时间 2m15s
select a.order_no,a.create_dtme,a.start_time,a.end_time,a.elect,a.order_type
,COALESCE(mtda.audit_status,0) as taxi_audit_status
,a.parking_fee_discount
,a.order_state,bp.lastup_dtme as pay_time
,mc.operator_id,mc.station_id,mc.equipment_id,mc.connector_id,bos.settlement_time
,ms.station_name,meo.full_name,meo.short_name,mo.operator_name,a.total_money,a.elect_money,a.service_money -- 占位费
,bp.actual_pay_total_money,bp.card_pay_money,bp.coupon_pay_money
,CASE WHEN a.order_type = 6 THEN COALESCE(bp.taxi_discount_money, a.taxi_discount_money) ELSE bp.taxi_discount_money END AS taxi_discount_money
,COALESCE(a.total_money,0) - COALESCE(bp.account_pay_money,0) - COALESCE(bp.pay_money, 0) as discount
,COALESCE(meobo.balance_id,meob.balance_id) AS balance_id,COALESCE(meobo.balance_name,meob.balance_name) AS balance_name
,a.app_type,mu.mobile,a.plate_num,a.vin_code,mb.enterprise_name,bp.coupon_property
,bosp.split_amount_lian_pre,bosp.split_amount_operator_pre,bosp.split_amount_lian_aft,bosp.split_amount_operator_aft
from bl_platform_order a
JOIN md_user mu on a.create_user_id = mu.user_id
JOIN md_connector mc ON a.connector_id = mc.id
JOIN md_equipment me ON me.station_id = mc.station_id AND me.operator_id = mc.operator_id AND me.equipment_id = mc.equipment_id
JOIN md_station ms ON me.station_id = ms.station_id AND me.operator_id = ms.operator_id
JOIN md_equipment_owner meo ON ms.equipment_owner_id = meo.equipment_owner_id
JOIN md_operator mo ON ms.operator_id = mo.operator_id
LEFT JOIN md_taxi_driver_auth mtda ON mtda.user_id = mu.user_id AND mtda.audit_status = 1
LEFT JOIN (
SELECT order_no
,SUM(case when settlement_type = 0 then split_amount_lian else 0 end) as split_amount_lian_pre
,SUM(case when settlement_type = 0 then split_amount_operator else 0 end) as split_amount_operator_pre
,SUM(case when settlement_type = 1 then split_amount_lian else 0 end) as split_amount_lian_aft
,SUM(case when settlement_type = 1 then split_amount_operator else 0 end) as split_amount_operator_aft
FROM bl_order_split
GROUP BY order_no
) bosp ON a.order_no = bosp.order_no
LEFT JOIN bl_platform_order_static_info bposi on a.order_no = bposi.order_no
LEFT JOIN
(
SELECT a.order_no, a.create_dtme, a.lastup_dtme, a.pay_money, a.account_pay_money
, a.coupon_pay_money, a.taxi_discount_money, a.operator_discount_money, a.couponid, a.lian_pay_money, a.card_pay_money
,COALESCE(a.account_pay_money,0) + COALESCE(a.pay_money, 0) AS actual_pay_total_money
,bc.coupon_property
,ROW_NUMBER() OVER(PARTITION BY a.order_no ORDER BY a.lastup_dtme desc) AS rn
FROM bl_pay a
LEFT JOIN bl_coupon_user bcu on a.couponid = bcu.id
LEFT JOIN bl_coupon bc ON bcu.coupon_id = bc.id
WHERE a.state_id = 1
) bp ON a.order_no = bp.order_no and bp.rn = 1
LEFT JOIN bl_order_settlement_detail bos on a.order_no = bos.order_no
LEFT JOIN bl_bigcustomer_recharge br ON a.order_no = br.order_no AND br.type = 2
LEFT JOIN md_bigcustomer mb ON mb.id = br.bigcustomer_id
LEFT JOIN md_equipment_owner_balance meobo ON bposi.equipment_owner_id = meobo.equipment_owner_id and bposi.operator_id = meobo.operator_id
LEFT JOIN md_equipment_owner_balance meob ON ms.equipment_owner_id = meob.equipment_owner_id and meob.operator_id = ms.operator_id
WHERE a.start_time between '2023-06-01 00:00:00' AND '2023-07-01 23:59:59';
sql中涉及的表数据量如下：
md_user 58W
md_bigcustomer 90
md_connector 25W
md_equipment 25W
md_equipment_owner_balance 300
md_station 3W
md_equipment_owner 900
md_operator 405
md_taxi_driver_auth 5W
md_charging_compartment 1600
bl_platform_order 3800w
bl_order_split 3000W
bl_platform_order_static_info 3400W
bl_pay 4500W
bl_coupon 4000
bl_coupon_user 44W
bl_order_settlement_detail 3300W
bl_bigcustomer_recharge 240W
colocation_group如下：
group1:
bl_platform_order 3800w,
bl_pay 4500W,
bl_order_settlement_detail 3300W,
bl_platform_order_static_info 3400W
group2:
md_station 3W,
md_equipment_owner_balance 300,
md_equipment 25W,
md_charging_compartment 1600,
md_connector 25W,
bl_coupon 4000,
bl_coupon_user 44W
【操作系统】Centos7
【机器配置】包括：CPU核数、内存、磁盘
1FE(100G,35C,50T) + 4BE (100G,35C,50T)
【复现路径】查询较慢
【附件】
表属性设置均如下，数据模型均为UNIQUE_KEYS ：
PROPERTIES (
"replication_allocation" = "tag.location.default: 4",
"is_being_synced" = "false",
"colocate_with" = "buckets20_order_no",
"storage_format" = "V2",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"store_row_column" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);