异步物化视图未命中

[jeason]

【详述】创建物化视图后，测试sql查询未命中创建的物化视图
【背景】相关分页查询比较慢，希望通过物化视图加速查询
【业务影响】测试中，无影响
【StarRocks版本】2.4.0
【集群规模】例如：3fe（1 follower+2observer）+9be（fe与be独立）
【机器信息】CPU虚拟核/内存/网卡，例如：32C/128G/万兆
【联系方式】为了在解决问题过程中能及时联系到您获取一些日志信息，请补充下您的联系方式，例如：社区群12-金谡 jinsu@moojing.com，谢谢
【附件】

• fe.log/beINFO/相应截图
• 慢查询：
  • Profile信息，获取Profile，通过Profile分析查询瓶颈
  • 并行度：show variables like ‘%parallel_fragment_exec_instance_num%’;
  • pipeline是否开启：show variables like ‘%pipeline%’;
  • be节点cpu和内存使用率截图
• 查询报错：
  • query_dump，怎么获取query_dump文件
• be crash
  • be.out

1. 创建物化视图的sql
   CREATE MATERIALIZED VIEW taobao_sku_viw_16 DISTRIBUTED BY HASH(item_id) BUCKETS 4 as select max(t1.shop_id) shop_id, max(t3.shop_name) shop_name, max(t1.shop_type) shop_type, max(t1.shop_type) plat, max(t1.loc) loc, max(t1.item_id) item_id, coalesce(max(p2.sku_id), ‘’) sku_id, max(p2.sales) sales, max(p2.sold) sold, max(t4.fb_id) brand_id, max(t4.fbrand_name) brand_name, max(t1.title) title, coalesce(max(p2.price_min), max(p2.price)) min_price, max(p2.price) price2, coalesce(max(p2.ori_price), max(p2.price)) price, max(t1.comment) comment, max(t1.img) img, max(t1.cat1) ori_cat1, max(t1.time) time, max(t1.cat1) cat1, max(t1.cat2) cat2, max(t1.cat3) cat3, max(t1.cat4) as cat4, coalesce(max(t1.cat4), max(t1.cat3), max(t1.cat2), max(t1.cat1)) as leaf_id ,’’ as rule from item2 t1 left join shops_tmall t3 on t1.shop_id=t3.shop_id left join fix_brands_v2 t4 on t1.brand_id=t4.bid left join itemattr2_pic p2 on t1.cat1 = p2.cat1 and t1.item_id = p2.item_id and t1.time = p2.time where (((t1.cat1=‘16’))) and (t1.time in (‘2023-04-01’)) and (t4.plat = ‘taobao’ or t4.plat is null) and t1.item_id is not null and p2.sku_id is not null group by p2.item_id, p2.sku_id, p2.time order by sales desc nulls last limit 1000;

2. 查询sql:
   select shop_id, shop_name, shop_type, plat, loc, item_id, sku_id, brand_id, brand_name, title, sales, sold,
   case when yoy_sales is not null and yoy_sales != 0 then (sales-yoy_sales)/yoy_sales else ‘-’ end yoy_sales,
   case when yoy_sold is not null and yoy_sold != 0 then (sold-yoy_sold)/yoy_sold else ‘-’ end yoy_sold,
   case when qoq_sales is not null and qoq_sales != 0 then (sales-qoq_sales)/qoq_sales else ‘-’ end qoq_sales,
   case when qoq_sold is not null and qoq_sold != 0 then (sold-qoq_sold)/qoq_sold else ‘-’ end qoq_sold,

   case when sold != 0 then sales/sold else 0 end as price2, case when sold != 0 then sales/sold else 0 end as price, comment,
   img, ori_cat1, ‘2023-04 至 2023-04’ time, cat1, leaf_id, rule , cat2, cat3, cat4, min_price from (select max(shop_id) shop_id, max(shop_name) shop_name, max(shop_type) shop_type,
   max(shop_type) plat, max(loc) loc, item_id, sku_id,

                sum(case when time in ('2022-04-01') then sales end) as yoy_sales, 
                sum(case when time in ('2023-03-01') then sales end) as qoq_sales, 
                sum(case when time in ('2023-04-01') then sales end) as sales, 
                sum(case when time in ('2022-04-01') then sold end) as yoy_sold, 
                sum(case when time in ('2023-03-01') then sold end) as qoq_sold, 
                sum(case when time in ('2023-04-01') then sold end) as sold, 
   
                max(brand_id) brand_id, max(brand_name) brand_name, max(title) title,  max(min_price) min_price, 
                 max(price) price, max(comment) comment, max(img) img, max(cat1) ori_cat1,
                max(time) time, max(cat1) cat1, max(cat2) cat2, max(cat3) cat3, max(cat4) cat4, max(leaf_id) leaf_id , GROUP_CONCAT(rule, ';') rule   from (select max(t1.shop_id) shop_id, max(t3.shop_name) shop_name, max(t1.shop_type) shop_type, max(t1.shop_type) plat, max(t1.loc) loc, max(t1.item_id) item_id, coalesce(max(p2.sku_id), '') sku_id, max(p2.sales) sales, max(p2.sold) sold,   max(t4.fb_id) brand_id, max(t4.fbrand_name) brand_name, max(t1.title) title,  coalesce(max(p2.price_min), max(p2.price)) min_price,  max(p2.price) price2, coalesce(max(p2.ori_price), max(p2.price)) price, 
            max(t1.comment) comment, max(t1.img) img, max(t1.cat1) ori_cat1, max(t1.time) time, max(t1.cat1) cat1, max(t1.cat2) cat2, max(t1.cat3) cat3, max(t1.cat4) as cat4, coalesce(max(t1.cat4), max(t1.cat3), max(t1.cat2), max(t1.cat1)) as leaf_id ,'' as rule   from item2 t1 left join shops_tmall t3 on  t1.shop_id=t3.shop_id left join  fix_brands_v2  t4 on  t1.brand_id=t4.bid left join itemattr2_pic p2 on  t1.cat1 = p2.cat1 and t1.item_id = p2.item_id and t1.time = p2.time where (((t1.cat1='16'))) and (t1.time in ('2023-04-01'))   and (t4.plat = 'taobao' or t4.plat is null)   and t1.item_id is not null  and p2.sku_id is not null  group by p2.item_id, p2.sku_id, p2.time) tt0
                 group by item_id, sku_id) t 
   

   where sales is not null order by sales desc nulls last limit 10 offset 0;

3. explain结果
   2:OlapScanNode |
   | TABLE: item2 |
   | PREAGGREGATION: ON |
   | PREDICATES: 11: cat1 = 16, 2: time = ‘2023-04-01’, 1: item_id IS NOT NULL |
   | partitions=1/2016 |
   | rollup: item2 |
   | tabletRatio=32/32 |
   | tabletList=32578,32582,32586,32590,32594,32598,32602,32606,32610,32614 … |
   | cardinality=2535 |
   | avgRowSize=191.74098 |
   | numNodes=0
   mv.txt (94.6 KB)

[jingdan]

2.4版本还不支持异步物化视图自动查询改写

[jeason]

哦，明白了，谢谢！