[TOC] ### 6.3.1 背景介绍 物化视图是一种预先计算的技术,同RollUp表,预先计算是为了减少查询时现场计算量,从而降低查询延迟。为什么已有RollUp表,还要引入物化视图呢?在RollUp表的介绍章节,我们说明了RollUp表在明细模型中的缺陷:虽然可以添加维度列新的排序方式,以命中更多的前缀查询条件,  然后无法实现对明细表的卷维后的粗粒度预先聚合。明细表包含所有原始的导入数据,因此可对明细表进行任意维度分析,而物化视图正是为了解决明细表的任意维度分析的预先聚合而提出,同时物化视图的功能是RollUp表的超集,原有的RollUp功能都可通过物化视图来实现。 <br> 物化视图的使用场景有: * 分析需求覆盖明细数据查询以及固定维度聚合查询两方面。 * 需要做对排序键前缀之外的其他列组合形式做范围条件过滤. * 需要对明细表的任意维度做粗粒度聚合分析. ### 6.3.2 原理 物化视图的数据组织形式和基表,RollUp表相同;用户可以在新建的基表时添加物化视图,也可以对已有表添加物化视图,这种情况下,基表的数据会自动以异步方式填充到物化视图中。基表可以拥有多张物化视图,向基表导入数据时,会同时更新基表的所有物化视图,数据导入操作具有原子性,因此基表和它的物化视图保持数据一致。 <br> 物化视图创建成功后,用户的原有的查询基表的SQL语句保持不变, DorisDB 会根据自动选择一个最优的物化视图,从物化视图中读取数据并计算。用户可以通过 EXPLAIN 命令来检查当前查询是否使用了物化视图。 <br> 物化视图中的聚合和查询中聚合的匹配关系: <br> 物化视图聚合查询中聚合 | 物化视图聚合 | 查询中聚合 | | :-: | :-: | | sum | sum | | min | min | | max | max | | count | count | | bitmap\_union | bitmap\_union, bitmap\_union\_count, count(distinct) | | hll\_union | hll\_raw\_agg, hll\_union\_agg, ndv, approx\_count\_distinct | 其中 bitmap 和 hll 的聚合函数在查询匹配到物化视图后,查询的聚合算子会根据物化视图的表结构进行改写。 ### 6.3.3 使用方式 #### 6.3.3.1 创建物化视图 通过下面命令就可以创建物化视图了。创建物化视图是一个异步的操作,也就是说用户成功提交创建任务后,DorisDB会在后台对存量的数据进行计算,直到创建成功。 ~~~ CREATE MATERIALIZED VIEW ~~~ 具体的语法可以通过下面命令查看: ~~~ HELP CREATE MATERIALIZED VIEW ~~~ 假设用户有一张销售记录明细表,存储了每个交易的交易id,销售员,售卖门店,销售时间,以及金额。建表语句为: ~~~ CREATE TABLE sales_records( record_id int, seller_id int, store_id int, sale_date date, sale_amt bigint ) distributed BY hash(record_id) properties("replication_num" = "1"); ~~~ 表sales\_records的结构为: ~~~ MySQL [test]> desc sales_records; +-----------+--------+------+-------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------+------+-------+---------+-------+ | record_id | INT | Yes | true | NULL | | | seller_id | INT | Yes | true | NULL | | | store_id | INT | Yes | true | NULL | | | sale_date | DATE | Yes | false | NULL | NONE | | sale_amt | BIGINT | Yes | false | NULL | NONE | +-----------+--------+------+-------+---------+-------+ ~~~ 如果用户经常对不同门店的销售量做分析,则可以为 sales\_records 表创建一张以售卖门店为分组,对相同售卖门店的销售额求和的一个物化视图。创建语句如下: ~~~sql CREATE MATERIALIZED VIEW store_amt AS SELECT store_id, SUM(sale_amt) FROM sales_records GROUP BY store_id; ~~~ 更详细物化视图创建语法请参考本手册的"SQL语法"章节, 或者在MySQL客户端使用命令help create materialized view获得帮助. <br> #### 6.3.3.2 查看物化视图 由于创建物化视图是一个异步的操作,用户在提交完创建物化视图任务后,需要通过命令检查物化视图是否构建完成,  命令如下: `SHOW ALTER MATERIALIZED VIEW FROM db_name;` 或 `SHOW ALTER TABLE ROLLUP FROM db_name;` > db\_name, 替换成真实的db\_name, 比如"test". 查询结果为: ~~~ +-------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+ | JobId | TableName | CreateTime | FinishedTime | BaseIndexName | RollupIndexName | RollupId | TransactionId | State | Msg | Progress | Timeout | +-------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+ | 22324 | sales_records | 2020-09-27 01:02:49 | 2020-09-27 01:03:13 | sales_records | store_amt | 22325 | 672 | FINISHED | | NULL | 86400 | +-------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+ ~~~ 如果State为"FINISHED"说明基表到物化视图已经创建完成. 查看物化视图的表结果, 需用通过基表名进行 ~~~ mysql> desc sales_records all; +---------------+---------------+-----------+--------+------+-------+---------+-------+ | IndexName | IndexKeysType | Field | Type | Null | Key | Default | Extra | +---------------+---------------+-----------+--------+------+-------+---------+-------+ | sales_records | DUP_KEYS | record_id | INT | Yes | true | NULL | | | | | seller_id | INT | Yes | true | NULL | | | | | store_id | INT | Yes | true | NULL | | | | | sale_date | DATE | Yes | false | NULL | NONE | | | | sale_amt | BIGINT | Yes | false | NULL | NONE | | | | | | | | | | | store_amt | AGG_KEYS | store_id | INT | Yes | true | NULL | | | | | sale_amt | BIGINT | Yes | false | NULL | SUM | +---------------+---------------+-----------+--------+------+-------+---------+-------+ ~~~ #### 6.3.3.3 查询命中物化视图 当物化视图创建完成后,用户再查询不同门店的销售量时,就会直接从刚才创建的物化视图 store\_amt 中读取聚合好的数据。达到提升查询效率的效果。 用户的查询依旧指定查询 sales\_records 表,比如: `SELECT store_id, SUM(sale_amt) FROM sales_records GROUP BY store_id;` <br> 使用EXPLAIN命令查询物化视图是否命中 `EXPLAIN SELECT store_id, SUM(sale_amt) FROM sales_records GROUP BY store_id;` <br> 结果为: ~~~ | Explain String | +-----------------------------------------------------------------------------+ | PLAN FRAGMENT 0 | | OUTPUT EXPRS:<slot 2> `store_id` | <slot 3> sum(`sale_amt`) | | PARTITION: UNPARTITIONED | | | | RESULT SINK | | | | 4:EXCHANGE | | use vectorized: true | | | | PLAN FRAGMENT 1 | | OUTPUT EXPRS: | | PARTITION: HASH_PARTITIONED: <slot 2> `store_id` | | | | STREAM DATA SINK | | EXCHANGE ID: 04 | | UNPARTITIONED | | | | 3:AGGREGATE (merge finalize) | | | output: sum(<slot 3> sum(`sale_amt`)) | | | group by: <slot 2> `store_id` | | | use vectorized: true | | | | | 2:EXCHANGE | | use vectorized: true | | | | PLAN FRAGMENT 2 | | OUTPUT EXPRS: | | PARTITION: RANDOM | | | | STREAM DATA SINK | | EXCHANGE ID: 02 | | HASH_PARTITIONED: <slot 2> `store_id` | | | | 1:AGGREGATE (update serialize) | | | STREAMING | | | output: sum(`sale_amt`) | | | group by: `store_id` | | | use vectorized: true | | | | | 0:OlapScanNode | | TABLE: sales_records | | PREAGGREGATION: ON | | partitions=1/1 | | rollup: store_amt | | tabletRatio=10/10 | | tabletList=22326,22328,22330,22332,22334,22336,22338,22340,22342,22344 | | cardinality=0 | | avgRowSize=0.0 | | numNodes=1 | | use vectorized: true | +-----------------------------------------------------------------------------+ ~~~ 查询计划树中的OlapScanNode显示PREAGGREGATION: ON和rollup: store\_amt说明使用物化视图store\_amt的预先聚合计算结果. 也就是说查询已经命中到物化视图 store\_amt, 并直接从物化视图中读取数据了。 #### 6.3.3.4 删除物化视图 下列两种情形需要删除物化视图: * 用户误操作创建物化视图, 需要撤销该操作. * 用户创建了大量的物化视图, 导致数据导入速度过慢不满足业务需求, 并且部分物化视图的相互重复, 查询频率极低, 可容忍较高的查询延迟,  此时需要删除部分物化视图. 删除已经创建完成的物化视图: `DROP MATERIALIZED VIEW IF EXISTS store_amt from sales_records;` <br> 删除处于创建中的物化视图, 需要先取消异步任务, 然后删除物化视图, 以表db0.table0上的物化视图mv为例: 首先使用获得JobId, 执行命令: `show alter table rollup from db0;` 结果为: ~~~ +-------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+-------------+------+----------+---------+ | JobId | TableName | CreateTime | FinishedTime | BaseIndexName | RollupIndexName | RollupId | TransactionId | State | Msg | Progress | Timeout | | 22478 | table0 | 2020-09-27 01:46:42 | NULL | table0 | mv | 22479 | 676 | WAITING_TXN | | NULL | 86400 | +-------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+-------------+------+----------+---------+ ~~~ 其中JobId为22478, 取消该Job, 执行命令: `cancel alter table rollup from db0.table0 (22478);` ### 6.3.4 最佳实践 #### 6.3.4.1 精确去重 用户可以在明细表上使用表达式bitmap\_union(to\_bitmap(col))创建物化视图, 实现原来聚合表才支持的基于bitmap的预先计算的精确去重功能. <br> 比如, 用户有一种计算广告业务相关的明细表, 每条记录包含的信息有点击日期, 点击的是什么广告,通过什么渠道点击,以及点击的用户是谁。 ~~~ CREATE TABLE advertiser_view_record( TIME date, advertiser varchar(10), channel varchar(10), user_id int ) distributed BY hash(TIME) properties("replication_num" = "1"); ~~~ 用户查询广告UV, 使用下面查询语句. ~~~ SELECT advertiser, channel, count(distinct user_id) FROM advertiser_view_record GROUP BY advertiser, channel; ~~~ 这种情况下, 可以创建物化视图, 使用bitmap\_union预先聚合: ~~~ CREATE MATERIALIZED VIEW advertiser_uv AS SELECT advertiser, channel, bitmap_union(to_bitmap(user_id)) FROM advertiser_view_record GROUP BY advertiser, channel; ~~~ 物化视图创建完毕后, 查询语句中的count(distinct user\_id), 会自动改写为bitmap\_union\_count (to\_bitmap(user\_id))以命中物化视图. #### 6.3.4.2 近似去重 用户可以在明细表上使用表达式hll\_union(hll\_hash(col))创建物化视图, 实现近似去重的预计算. 在3.4.1的场景中, 用户创建如下物化视图: ~~~ CREATE MATERIALIZED VIEW advertiser_uv AS SELECT advertiser, channel, hll_union(hll_hash(user_id)) FROM advertiser_view_record GROUP BY advertiser, channel; ~~~ #### 6.3.4.3 匹配更丰富的前缀索引 用户的基表tableA有(k1, k2, k3) 三列。其中 k1, k2 为排序键。这时候如果用户查询条件中包含 where k1=1 and k2=2 就能通过shortkey索引加速查询。但是用户查询语句中使用条件k3=3, 则无法通过shortkey索引加速. 此时, 可创建以k3作为第一列的物化视图: ~~~ CREATE MATERIALIZED VIEW mv_1 AS SELECT k3, k2, k1 FROM tableA ORDER BY k3; ~~~ 这时候查询就会直接从刚才创建的 mv\_1 物化视图中读取数据。物化视图对 k3 是存在前缀索引的,查询效率也会提升。 ### 6.3.5 注意事项 1. 物化视图的聚合函数的参数仅支持单列, 比如: sum(a+b)不支持。 2. 如果删除语句的条件列,在物化视图中不存在,则不能进行删除操作。如果一定要删除数据,则需要先将物化视图删除,然后方可删除数据。 3. 单表上过多的物化视图会影响导入的效率:导入数据时,物化视图和 base 表数据是同步更新的,如果一张表的物化视图表超过10张,则有可能导致导入速度很慢。这就像单次导入需要同时导入10张表数据是一样的。 4. 相同列,不同聚合函数,不能同时出现在一张物化视图中,比如:select sum(a), min(a) from table 不支持。 5. 物化视图的创建语句目前不支持JOIN和WHERE, 也不支持GROUP BY的HAVING子句. 6. 不能同时创建多个物化视图, 只能等待上一个物化视图创建完成, 才能创建下一个物化视图.