Doris：物化视图

目录

1.优势

2.局限性

3.管理物化视图

3.1 创建测试表

3.2创建物化视图

3.3查询物化视图

3.4删除物化视图

3.5支持聚合函数

4.自动匹物化视图

4.1.总数据量

4.2.可以使用EXPLAIN检查sql是否使用物化视图

4.3.统计各省年度数据

4.4.统计某年各月数据

---

  

      物化视图是将预先计算（根据定义好的 SELECT 语句）好的数据集，存储在 Doris 中的一个特殊的表。物化视图的出现主要是为了满足用户，既能对原始明细数据的任意维度分析，也能快速的对固定维度进行分析查询。

1.优势

• 对于那些经常重复的使用相同的子查询结果的查询性能大幅提升。
• Doris 自动维护物化视图的数据，无论是新的导入，还是删除操作都能保证 Base 表和物化视图表的数据一致性，无需任何额外的人工维护成本。
• 查询时，会自动匹配到最优物化视图，并直接从物化视图中读取数据。

2.局限性

• 如果删除语句的条件列，在物化视图中不存在，则不能进行删除操作。如果一定要删除数据，则需要先将物化视图删除，然后方可删除数据。
• 单表上过多的物化视图会影响导入的效率：导入数据时，物化视图和 Base 表数据是同步更新的，如果一张表的物化视图表超过 10 张，则有可能导致导入速度很慢。这就像单次导入需要同时导入 10 张表数据是一样的。
• 物化视图针对 Unique Key数据模型，只能改变列顺序，不能起到聚合的作用，所以在Unique Key模型上不能通过创建物化视图的方式对数据进行粗粒度聚合操作
• 目前一些优化器对sql的改写行为可能会导致物化视图无法被命中，例如k1+1-1被改写成k1，between被改写成<=和>=，day被改写成dayofmonth，遇到这种情况需要手动调整下查询和物化视图的语句。

3.管理物化视图

3.1 创建测试表

CREATE TABLE `dim_nucleic_duplicate` (
  `id` bigint(20) NOT NULL,
  `idcard` varchar(50) NULL,
  `name` varchar(255) NULL,
  `mobile` varchar(50) NULL,
  `pcode` varchar(3) NULL COMMENT '省份编码',
  `ccode` varchar(3) NULL COMMENT '地市编码',
  `acode` varchar(3) NULL COMMENT '区县编码',
  `tcode` varchar(3) NULL COMMENT '街道/乡镇编码',
  `vcode` varchar(3) NULL COMMENT '社区/行政区编码',
  `sid` bigint(20) NULL COMMENT '采样点id',
  `stime` datetime NULL COMMENT '采样时间',
  `sname` varchar(255) NULL COMMENT '采样点名称',
  `did` bigint(20) NULL COMMENT '检测机构id',
  `dname` varchar(255) NULL COMMENT '检测机构名称',
  `dtime` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '检测时间',
  `result` int(11) NULL COMMENT '检测结果：0未出结果，1阴，2阳'
)
DUPLICATE KEY(`id`,`idcard`,`name`,`mobile`,`pcode`,`ccode`,`acode`,`tcode`,`vcode`,`sid`,`stime`)
PARTITION BY RANGE(`stime`) ()
DISTRIBUTED BY HASH(`pcode`,`ccode`,`acode`,`tcode`,`vcode`) BUCKETS auto
PROPERTIES
(
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "MONTH",
	"dynamic_partition.create_history_partition" = "true",
	"dynamic_partition.history_partition_num" = "21",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "dim_nucleic_p"
)

3.2创建物化视图

        创建五级行政区核酸采用数据月度统计物化视图：

create materialized view dim_nucleic_area as 
select pcode,ccode,acode,tcode,vcode,year(stime),month(stime),count(id) from dim_nucleic_duplicate group by pcode,ccode,acode,tcode,vcode, year(stime),month(stime);

        提示成功后,由于数据量大，可能不会马上创建成功，可使用以下命令查看创建结果：SHOW ALTER TABLE MATERIALIZED VIEW FROM db_name;

当创建物化视图任务的 State 已经变成 FINISHED 后，就说明这个物化视图已经创建成功了。

3.3查询物化视图

        查看当前表都有哪些物化视图，以及他们的表结构都是什么样的。通过下面命令：
desc table_name all;查看已创建的物化视图：

SHOW CREATE MATERIALIZED VIEW mv_name ON table_name 查看详细的创建信息：

3.4删除物化视图

        

DROP MATERIALIZED VIEW mv_name ON table_name

3.5支持聚合函数

目前物化视图创建语句支持的聚合函数有：

SUM, MIN, MAX (Version 0.12)
COUNT, BITMAP_UNION, HLL_UNION (Version 0.13)

4.自动匹物化视图

4.1.总数据量

select count(*) from dim_nucleic_duplicate

dim_nucleic_duplicate表中共 445277354 条数据。

4.2.可以使用EXPLAIN检查sql是否使用物化视图

EXPLAIN
select pcode,count(id) as nums from dim_nucleic_duplicate where year(stime)=2022 group by pcode order by pcode

最后一行中的dim_nucleic_area表示命中视图。

4.3.统计各省年度数据

1）不使用物化视图：

select pcode,count(id) as nums from dim_nucleic_duplicate where stime BETWEEN '2022-01-01' and '2022-12-31 23:59:59' group by pcode order by pcode

2）使用物化适配：

select pcode,count(id) as nums from dim_nucleic_duplicate where year(stime)=2022 group by pcode order by pcode

3）统计耗时

        统计各省年度数据，不使用物化视图花费2.98s，使用物化视图花费0.14s，物化视图优势明显。

4.4.统计某年各月数据

1)不使用物化视图：

select  month(stime),count(id) as nums from dim_nucleic_duplicate where stime BETWEEN '2022-01-01' and '2022-12-31 23:59:59' group by month(stime) order by month(stime)

2)使用物化视图：

select month(stime),count(id) as nums from dim_nucleic_duplicate where year(stime)=2022  group by month(stime) order by month(stime)

3）统计耗时

        统计某年各月数据，不使用物化视图花费3.19s，使用物化视图花费0.11s，物化视图优势明显。