【StarRocks 查询性能测试】

准备数据

数据量：7600 w条，58个字段

CREATE TABLE `rpt_qdas_channelver_mix_daily_bak_test` (
  `day_id` int(11) NULL COMMENT "日期",
  `appkey` varchar(32) NULL COMMENT "appkey",
  `type` int(11) NULL COMMENT "类型",
  `ver` varchar(64) NULL COMMENT "版本",
  `ch1_id` varchar(64) NULL COMMENT "一级渠道",
  `ch1_name` varchar(64) NULL COMMENT "一级渠道",
  `ch2_id` varchar(64) NULL COMMENT "二级渠道",
  `ch2_name` varchar(64) NULL COMMENT "二级渠道",
  `new_mids` int(11) NULL COMMENT "新增",
  `all_new_mids` int(11) NULL COMMENT "产品新增",
  `pre1_new_mids` int(11) NULL COMMENT "上日新增",
  `pre7_new_mids` int(11) NULL COMMENT "7日前新增",
  `sum7_new_mids` int(11) NULL COMMENT "7日新增",
  `sum30_new_mids` int(11) NULL COMMENT "30日新增",
  `total` bigint(20) NULL COMMENT "累计",
  `frontnew_mids` int(11) NULL COMMENT "主动新增",
  `all_frontnew_mids` int(11) NULL COMMENT "产品主动新增",
  `pre1_frontnew_mids` int(11) NULL COMMENT "上日主动新增",
  `pre7_frontnew_mids` int(11) NULL COMMENT "7日前主动新增",
  `sum7_frontnew_mids` int(11) NULL COMMENT "7日主动新增",
  `sum30_frontnew_mids` int(11) NULL COMMENT "30日主动新增",
  `fronttotal_mids` bigint(20) NULL COMMENT "主动累计",
  `act_times` bigint(20) NULL COMMENT "活跃次数",
  `act_mids` int(11) NULL COMMENT "活跃人数",
  `all_act_times` bigint(20) NULL COMMENT "产品活跃次数",
  `all_act_mids` int(11) NULL COMMENT "产品活跃",
  `pre1_act_mids` int(11) NULL COMMENT "上日活跃",
  `pre7_act_mids` int(11) NULL COMMENT "7日前活跃",
  `sum7_act_mids` int(11) NULL COMMENT "7日活跃",
  `sum30_act_mids` int(11) NULL COMMENT "30日活跃",
  `front_act_times` int(11) NULL COMMENT "主动活跃次数",
  `front_act_mids` bigint(20) NULL COMMENT "主动活跃人数",
  `all_front_act_times` int(11) NULL COMMENT "产品主动活跃次数",
  `all_front_act_mids` bigint(20) NULL COMMENT "产品主动活跃人数",
  `pre1_front_act_mids` int(11) NULL COMMENT "上日主动活跃",
  `pre7_front_act_mids` int(11) NULL COMMENT "7日前主动活跃",
  `sum7_front_act_mids` int(11) NULL COMMENT "7日主动活跃",
  `sum30_front_act_mids` int(11) NULL COMMENT "30日主动活跃",
  `potential_times` int(11) NULL COMMENT "潜在用户数次数",
  `potential_mids` int(11) NULL COMMENT "潜在用户数人数",
  `startup_times` bigint(20) NULL COMMENT "启动次数",
  `startup_mids` int(11) NULL COMMENT "启动人数",
  `avg_startup_times` float NULL COMMENT "人均启动次数",
  `all_startup_times` int(11) NULL COMMENT "产品启动次数",
  `all_startup_mids` int(11) NULL COMMENT "产品启动人数",
  `all_avg_startup_times` float NULL COMMENT "产品人均启动次数",
  `pre1_startup_times` bigint(20) NULL COMMENT "上日启动次数",
  `pre1_startup_mids` int(11) NULL COMMENT "上日启动人数",
  `pre1_avg_startup_times` float NULL COMMENT "上日人均启动次数",
  `pre7_startup_times` bigint(20) NULL COMMENT "7日前启动次数",
  `pre7_startup_mids` int(11) NULL COMMENT "7日前启动人数",
  `pre7_avg_startup_times` float NULL COMMENT "7日前人均启动次数",
  `sum7_startup_times` bigint(20) NULL COMMENT "7日启动次数",
  `sum7_startup_mids` int(11) NULL COMMENT "7日启动人数",
  `sum7_avg_startup_times` float NULL COMMENT "7日人均启动次数",
  `sum30_startup_times` bigint(20) NULL COMMENT "30日启动次数",
  `sum30_startup_mids` int(11) NULL COMMENT "30日启动人数",
  `sum30_avg_startup_times` float NULL COMMENT "30日人均启动次数"
) ENGINE=OLAP 
DUPLICATE KEY(`day_id`, `appkey`, `type`, `ver`, `ch1_id`, `ch1_name`, `ch2_id`, `ch2_name`)
COMMENT "OLAP"
PARTITION BY RANGE(`day_id`)
(PARTITION p202111 VALUES [("20211101"), ("20211201")),
PARTITION p202112 VALUES [("20211201"), ("20220101")),
PARTITION p202201 VALUES [("20220101"), ("20220201")),
PARTITION p202202 VALUES [("20220201"), ("20220301")),
PARTITION p202203 VALUES [("20220301"), ("20220401")),
PARTITION p202204 VALUES [("20220401"), ("20220501")))
DISTRIBUTED BY HASH(`appkey`) BUCKETS 10 
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "false",
"dynamic_partition.time_unit" = "month",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-2147483648",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "10",
"dynamic_partition.replication_num" = "3",
"dynamic_partition.start_day_of_month" = "1",
"in_memory" = "false",
"storage_format" = "V2",
"enable_persistent_index" = "false"
);

查询性能 case

1.单表点查

-- 17.243s
SELECT * from rpt_qdas_channelver_mix_daily_bak_test
where day_id=20220116

2.单表聚合

-- 0.461s
SELECT day_id,count(1) from rpt_qdas_channelver_mix_daily_bak_test
GROUP BY day_id

3.多表 join

-- 14.093s
select
b.day_id,b.appkey,count(1)
from  rpt_qdas_channelver_mix_daily_bak_test a
left join rpt_qdas_channelver_mix_daily_bak_test b on a.day_id=b.day_id and a.appkey=b.appkey and a.ver=b.ver and a.ch1_id=b.ch1_id and a.ch2_id=b.ch2_id
group by b.day_id,b.appkey
order by b.day_id,b.appkey

4.业务真实案例-多个亿级别的表 join

-- 0.220s
select
    a.ch2_id,
    COALESCE(a.ver,'其他') as ver,
    frontnew_mids,
    front_act_mids,
    startup_times,
    COALESCE(onceavg_dur,0) as onceavg_dur,
    COALESCE(onceavg_acttimes,0) as onceavg_acttimes,
    COALESCE(dayavg_webtimes,0) as dayavg_webtimes
from 
(
    select 
        ch2_id,
        ver,
        frontnew_mids,  -- 新增用户
        front_act_mids, -- 主动活跃用户
        startup_times -- 启动次数
    from rpt_qdas_channelver_mix_daily 
    where day_id=20231115
    and ver<>'all' and ch1_id='all' and ch2_id<>'all'
    and appkey='3416a75f4cea9109507cacd8e2f2aefc'
)a
left join
(
    select 
        a.ch2_id,
        a.ver,
        a.onceavg_dur*60 as onceavg_dur, -- 平均使用时长
		a.act_times/b.act_mids as onceavg_acttimes -- 平均使用频次
    from rpt_qdas_duration_trend_daily  a 
    join rpt_qdas_channelver_remain_daily b on a.ch2_id=b.ch2_id and a.ver=b.ver and a.day_id=b.day_id and a.appkey=b.appkey and a.ch1_id=b.ch1_id
    where a.day_id=20231115
    and a.ver<>'all' and a.ch1_id='all' and a.ch2_id<>'all'
    and a.appkey='3416a75f4cea9109507cacd8e2f2aefc'
)b on a.ch2_id=b.ch2_id and a.ver=b.ver
left join
(
    select 
        ch2_id,
        ver,
        dayavg_webtimes -- 平均访问深度
    from rpt_qdas_activity_trend_daily 
    where day_id=20231115
    and ver<>'all' and ch1_id='all' and ch2_id<>'all'
    and appkey='3416a75f4cea9109507cacd8e2f2aefc'
)c on a.ch2_id=c.ch2_id and a.ver=c.ver