【是否存算分离】是
【StarRocks版本】3.1.4 docker + minio安装
【集群规模】docker单机
【机器信息】4C32G
【联系方式】18292085476
【附件】
建表语句:
CREATE TABLE tb_order (
id BIGINT NOT NULL,
create_date DATE NOT NULL,
user_id int NOT NULL DEFAULT ‘0’,
finished_date DATE DEFAULT NULL,
order_no varchar(32) NOT NULL DEFAULT ‘’,
third_order_no varchar(32) NOT NULL DEFAULT ‘’,
inviter_id int NOT NULL DEFAULT ‘0’,
shop_id int NOT NULL DEFAULT ‘0’,
goods_id int NOT NULL DEFAULT ‘0’,
goods_cate_id int NOT NULL DEFAULT ‘0’,
goods_name varchar(64) NOT NULL DEFAULT ‘’,
price decimal(14,2) NOT NULL DEFAULT ‘0.00’,
amount int NOT NULL DEFAULT ‘0’,
total_price decimal(14,2) NOT NULL DEFAULT ‘0.00’,
created_time DATETIME DEFAULT NULL,
coupon_name varchar(64) NOT NULL DEFAULT ‘’,
pay_channel int NOT NULL DEFAULT ‘0’,
coupon_id int NOT NULL DEFAULT ‘0’,
status int NOT NULL DEFAULT ‘0’,
user_remark varchar(256) NOT NULL DEFAULT ‘’,
freight_charge decimal(14,2) NOT NULL DEFAULT ‘0.00’,
pay_amount decimal(14,2) NOT NULL DEFAULT ‘0.00’,
pay_order_no varchar(32) NOT NULL DEFAULT ‘’,
address_id int NOT NULL DEFAULT ‘0’,
pay_time DATETIME DEFAULT NULL,
updated_time DATETIME DEFAULT NULL,
send_out_time DATETIME DEFAULT NULL,
finished_time DATETIME DEFAULT NULL,
coupon_amount decimal(14,2) NOT NULL DEFAULT ‘0.00’,
order_month varchar(16) DEFAULT ‘’,
is_deleted int NOT NULL DEFAULT ‘0’
) PRIMARY KEY(id,create_date,user_id) COMMENT “OLAP”
PARTITION BY date_trunc(‘month’, create_date)
DISTRIBUTED BY HASH(user_id);
#物化视图
CREATE
MATERIALIZED VIEW tb_order_date_mv
PARTITION BY date_trunc(“MONTH”, create_date)
DISTRIBUTED BY HASH(user_id)
REFRESH ASYNC EVERY (interval 5 second)
AS
SELECT create_date,
finished_date,
shop_id,
user_id,
inviter_id,
is_deleted,
status,
sum(amount) as amount,
sum(total_price) as total_price,
sum(coupon_amount) as coupon_amount,
count(*) as row_count
FROM tb_order GROUP BY create_date, finished_date, shop_id, user_id, inviter_id,is_deleted, status;
ALTER MATERIALIZED VIEW tb_order_date_mv SET (“mv_rewrite_staleness_second” = “30”);
问题:
trace rewrite select count() from tb_order where status=0; 无法进行改写。
trace rewrite select count() from tb_order where is_deleted=0; 无法进行改写。
trace rewrite select count() from tb_order; 可以进行改写。
trace rewrite select count() from tb_order where is_deleted=0 and create_date>=‘2023-11-01’; 可以进行改写。
无法进行改写时信息都是如下:
mysql> trace rewrite select count(*) from tb_order where status=0;
±-----------------------------------------------------------------------------------------------------------------+
| Explain String |
±-----------------------------------------------------------------------------------------------------------------+
| – [TRACE: tb_order_date_mv] |
| [SYNC=false] Prepare MV tb_order_date_mv success |
| – [TRACE: PREPARE GLOBAL] |
| [SYNC=false] RelatedMVs: [tb_order_date_mv], CandidateMVs: [tb_order_date_mv] |
| [SYNC=true] There are no related mvs for the query plan |
| – [TRACE: REWRITE GLOBAL] |
| [TF_MV_AGGREGATE_SCAN_RULE] Compensate query expression’s partition predicates from pruned partitions failed. |
| [TF_MV_AGGREGATE_SCAN_RULE] Query expression’s partition compensate failed |
| [TF_MV_ONLY_SCAN_RULE] Compensate query expression’s partition predicates from pruned partitions failed. |
| [TF_MV_ONLY_SCAN_RULE] Query expression’s partition compensate failed |
| – [TRACE: Summary] |
| Query cannot be rewritten, please check the trace logs to find more information. |
±-----------------------------------------------------------------------------------------------------------------+