查询不能命中物化视图

Viewed 105
2.1

1. 创建Duplicate表：

CREATE TABLE `wangdi_log_test` ( `op_time` DATETIME NULL COMMENT ''请求时间'', `op_api` VARCHAR(255) NULL COMMENT ''请求API地址'', `op_ip` VARCHAR(64) NULL COMMENT ''请求ip'', `id` BIGINT NOT NULL COMMENT ''主键id'', `op_application` VARCHAR(128) NULL COMMENT ''APP名称'', `op_category` VARCHAR(128) NULL COMMENT ''业务分类'', `op_module` VARCHAR(128) NULL COMMENT ''模块名称'', `op_func` VARCHAR(128) NULL COMMENT ''功能名称'', `op_code` VARCHAR(128) NULL COMMENT ''功能子项代码'', `op_user` BIGINT NULL COMMENT ''操作人'', `op_action` VARCHAR(32) NULL COMMENT ''操作类型'', `op_status` VARCHAR(16) NULL COMMENT ''状态'', `op_error_msg` VARCHAR(255) NULL COMMENT ''错误信息'', `op_primary_key` BIGINT NULL COMMENT ''操作对象主键'', `op_primary_name` VARCHAR(255) NULL COMMENT ''对象名称'', `op_end_time` DATETIME NULL COMMENT ''操作完成时间'' ) ENGINE=OLAP DUPLICATE KEY(`op_time`, `op_api`) COMMENT 'OLAP' DISTRIBUTED BY HASH(`op_api`) BUCKETS 1 PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "min_load_replica_num" = "-1", "is_being_synced" = "false", "storage_format" = "V2", "compression" = "ZSTD", "light_schema_change" = "true", "compaction_policy" = "time_series", "time_series_compaction_goal_size_mbytes" = "1024", "time_series_compaction_file_count_threshold" = "2000", "time_series_compaction_time_threshold_seconds" = "3600", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "true", "group_commit_interval_ms" = "10000" );

2. 创建物化视图：

create materialized view wangdi_log_test_op_ip 
as 
select op_ip, count(0) from wangdi_log_test group by op_ip

3. explain查询计划：

EXPLAIN select op_ip, count(0) from wangdi_log_test group by op_ip

4. 结果如下：

image.png
image.png
image.png

wangdi29
2 Answers

这个是单表同步物化视图的创建和命中。出问题的这块应该是单表同步物化视图的透明改写逻辑问题。

  1. 单表同步物化视图的透明改写，后面会重构迁移使用新的异步物化视图的透明改写逻辑。就没这个问题了。
  2. 除了如上的解决办法，还有如下的解决办法：
    如果不是一定要用单表同步的物化视图，可以尝试使用异步物化视图的语法来创建和查询。

异步物化视图的创建文档：
https://doris.apache.org/zh-CN/docs/sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW
异步物化视图的查询：
https://doris.apache.org/zh-CN/docs/query/view-materialized-view/query-async-materialized-view

具体语法如下：

CREATE MATERIALIZED VIEW mv1
DISTRIBUTED BY RANDOM BUCKETS 3
PROPERTIES ('replication_num' = '1')
as
select op_ip, count(0) from wangdi_log_test group by op_ip;

打开异步物化视图透明改写开关，再进行查询

set enable_materialized_view_rewrite = true;
seawinde71

你这样创建物化视图：

create materialized view wangdi_log_test_op_ip
as
select op_ip, count(1) from wangdi_log_test group by op_ip;

这块应该是物化视图创建的时候count(0) 这里的问题

徐振超@SelectDB6756