当您需要优化重复且耗时较长的复杂查询时，您可以使用云数据库 SelectDB 版中的物化视图功能来缓存复杂查询，实现快速访问和高效数据处理。
概述
物化视图是将预先计算（根据定义好的SELECT语句）的数据集，存储在云数据库 SelectDB 版中一个特殊的表中。物化视图的主要作用是在原始明细数据的任意维度进行分析，同时也能在固定维度上进行快速分析和查询。
优势
对于使用相同子查询结果的查询语句，当子查询被物化视图命中时，会提升其性能。
无论是新导入数据，还是删除数据云数据库 SelectDB 版都能保证目标表和物化视图表数据的一致性，因为其可以自动维护物化视图的数据，无需任何额外的人工维护成本。
查询时，会自动匹配到最优物化视图，并直接从物化视图中读取数据。
自动维护物化视图的数据会造成维护开销。
适用场景
同时满足查询明细数据和分析固定维度两个方面的查询。
仅需涉及表中很少一部分列或行的查询。
包含耗时处理操作且适合预先计算的查询，例如：耗时很久的聚合操作等。
仅需匹配不同的前缀索引的查询。
使用物化视图
云数据库SelectDB提供了对物化视图的DDL语法，包括创建、查看和删除。DDL的语法与PostgreSQL和Oracle保持一致。
创建物化视图
创建物化视图是异步操作，您成功提交创建任务后，SelectDB会在后台对存量数据进行计算，直到创建成功。
创建原则
在创建物化视图之前，首先需要根据查询语句的特点来确定应创建何种类型的物化视图。并不是物化视图的定义和某个查询语句完全一致就是最理想的情况。创建物化视图有以下两个原则。
从查询语句中抽象出多个查询共有的分组和聚合方式，作为物化视图的定义。
一个抽象出来的物化视图，如果能被多个查询匹配到，说明该物化视图的效果是最好的。因为维护物化视图也需要消耗资源。如果该物化视图只和某个特殊的查询一致，而其他查询均用不到这个物化视图。这将导致该物化视图的性价比不高，既占用了集群的存储资源，同时也无法为被更多的查询所使用。
不需要为所有的维度组合都创建物化视图。
在实际的分析查询中，并不会覆盖到所有的维度分析。因此只需要给常用的维度组合创建物化视图，便可以达到空间和时间上的平衡。
支持的聚合函数
目前物化视图创建语句支持的聚合函数有：
SUM，MIN，MAX。
COUNT。
BITMAP_UNION，BITMAP_UNION的形式必须为：
BITMAP_UNION(TO_BITMAP(COLUMN))，且列（COLUMN）的类型只能是整数（largeint也不支持）。或者为BITMAP_UNION(COLUMN)且目标表为Aggregate模型。HLL_UNION，HLL_UNION的形式必须为：
HLL_UNION(HLL_HASH(COLUMN))，且列（COLUMN）的类型不能是DECIMAL。或者为HLL_UNION(COLUMN)且目标表为Aggregate模型。
语法
CREATE MATERIALIZED VIEW <mv_name> as <query>
[PROPERTIES ("key" = "value")]参数说明
参数名称 | 参数说明 |
mv_name | 物化视图的名称，必填项。相同表的物化视图名称不可重复。 |
query | 用于构建物化视图的查询语句，查询语句的结果即物化视图中的数据。具体参见query参数说明。 |
properties | 声明物化视图的一些配置，选填项。格式如下。 以下配置，可以在该属性中定义。 |
query参数说明
目前支持的query格式如下。
SELECT select_expr[, select_expr ...]
FROM [base_view_name]
GROUP BY column_name[, column_name ...]
ORDER BY column_name[, column_name ...]参数说明如下。
参数名称 | 参数说明 |
select_expr | 物化视图结构（Schema）中所有的列。至少包含一个单列。 |
base_view_name | 物化视图的原始表名，必填项。必须是单表，且非子查询。 |
group by | 物化视图的分组列，选填项。不填则数据不进行分组。 |
order by | 物化视图的排序列，选填项。
|
示例
创建示例表duplicate_table，示例如下。
CREATE TABLE duplicate_table(
k1 int null,
k2 int null,
k3 bigint null,
k4 bigint null
)
DUPLICATE KEY (k1,k2,k3,k4)
DISTRIBUTED BY HASH(k4) BUCKETS 3;查询示例表duplicate_table的表结构如下。
DESC duplicate_table;
+-------+--------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+------+---------+-------+
| k1 | INT | Yes | true | N/A | |
| k2 | INT | Yes | true | N/A | |
| k3 | BIGINT | Yes | true | N/A | |
| k4 | BIGINT | Yes | true | N/A | |
+-------+--------+------+------+---------+-------+如果物化视图包含了创建表的分区列和分桶列，那么这些列必须作为物化视图中的Key列。
创建一个仅包含原始表（k1，k2）列的物化视图，示例如下。
CREATE MATERIALIZED VIEW k1_k2 AS SELECT k2, k1 FROM duplicate_table;物化视图的结构（Schema）如下，物化视图仅包含两列k1，k2且不带任何聚合。
+-----------------+-------+--------+------+------+---------+-------+ | IndexName | Field | Type | Null | Key | Default | Extra | +-----------------+-------+--------+------+------+---------+-------+ | k2_k1 | k2 | INT | Yes | true | N/A | | | | k1 | INT | Yes | true | N/A | | +-----------------+-------+--------+------+------+---------+-------+创建一个以k2为排序列的物化视图，示例如下。
CREATE MATERIALIZED VIEW k2_order AS SELECT k2, k1 FROM duplicate_table ORDER BY k2;物化视图的结构（Schema）如下，物化视图仅包含两列k2，k1，其中k2列为排序列且不带任何聚合。
+-----------------+-------+--------+------+-------+---------+-------+ | IndexName | Field | Type | Null | Key | Default | Extra | +-----------------+-------+--------+------+-------+---------+-------+ | k2_order | k2 | INT | Yes | true | N/A | | | | k1 | INT | Yes | false | N/A | NONE | +-----------------+-------+--------+------+-------+---------+-------+创建一个以k1，k2分组，k3列为SUM聚合的物化视图，示例如下。
CREATE MATERIALIZED VIEW k1_k2_sumk3 AS SELECT k1, k2, sum(k3) FROM duplicate_table GROUP BY k1, k2;物化视图的结构（Schema）如下，包含两列k1，k2，sum(k3)。其中k1，k2为分组列，sum(k3)为根据k1，k2分组后的k3列的求和值。由于物化视图没有声明排序列，且物化视图带聚合数据，系统默认补充分组列k1，k2为排序列。
+-----------------+-------+--------+------+-------+---------+-------+ | IndexName | Field | Type | Null | Key | Default | Extra | +-----------------+-------+--------+------+-------+---------+-------+ | k1_k2_sumk3 | k1 | INT | Yes | true | N/A | | | | k2 | INT | Yes | true | N/A | | | | k3 | BIGINT | Yes | false | N/A | SUM | +-----------------+-------+--------+------+-------+---------+-------+
查询物化视图
语法
DESC table_name ALL;示例
查询表duplicate_table的物化视图。
DESC duplicate_table ALL;查询结果如下。
+-----------------+---------------+---------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+
| IndexName | IndexKeysType | Field | Type | InternalType | Null | Key | Default | Extra | Visible | DefineExpr | WhereClause |
+-----------------+---------------+---------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+
| duplicate_table | DUP_KEYS | k1 | INT | INT | Yes | true | NULL | | true | | |
| | | k2 | INT | INT | Yes | true | NULL | | true | | |
| | | k3 | BIGINT | BIGINT | Yes | true | NULL | | true | | |
| | | k4 | BIGINT | BIGINT | Yes | true | NULL | | true | | |
| | | | | | | | | | | | |
| k2_order | DUP_KEYS | mv_k2 | INT | INT | Yes | true | NULL | | true | `k2` | |
| | | mv_k1 | INT | INT | Yes | false | NULL | NONE | true | `k1` | |
| | | | | | | | | | | | |
| k1_k2 | DUP_KEYS | mv_k2 | INT | INT | Yes | true | NULL | | true | `k2` | |
| | | mv_k1 | INT | INT | Yes | true | NULL | | true | `k1` | |
| | | | | | | | | | | | |
| k1_k2_sumk3 | AGG_KEYS | mv_k1 | INT | INT | Yes | true | NULL | | true | `k1` | |
| | | mv_k2 | INT | INT | Yes | true | NULL | | true | `k2` | |
| | | mva_SUM__`k3` | BIGINT | BIGINT | Yes | false | NULL | SUM | true | `k3` | |
+-----------------+---------------+---------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+根据查询结果得出示例表duplicate_table一共有三张物化视图：k1_k2，k2_order和k1_k2_sumk3，以及它的表结构。
删除物化视图
如果不需要物化视图，可以通过命令删除物化视图。
语法
DROP MATERIALIZED VIEW [IF EXISTS] <mv_name> ON <table_name>;参数说明
参数名称 | 参数说明 |
IF EXISTS | 如果物化视图不存在，不要抛出错误。如果不声明此关键字，物化视图不存在则报错。 |
mv_name | 待删除的物化视图的名称。必填项。 |
table_name | 待删除的物化视图所属的表名。必填项。 |
示例
查看当前示例表
duplicate_table的物化视图以及它的表结构，示例如下。DESC duplicate_table ALL;查询结果如下。
+-----------------+---------------+-------+--------+--------------+------+------+---------+-------+---------+------------+-------------+ | IndexName | IndexKeysType | Field | Type | InternalType | Null | Key | Default | Extra | Visible | DefineExpr | WhereClause | +-----------------+---------------+-------+--------+--------------+------+------+---------+-------+---------+------------+-------------+ | duplicate_table | DUP_KEYS | k1 | INT | INT | Yes | true | NULL | | true | | | | | | k2 | INT | INT | Yes | true | NULL | | true | | | | | | k3 | BIGINT | BIGINT | Yes | true | NULL | | true | | | | | | k4 | BIGINT | BIGINT | Yes | true | NULL | | true | | | | | | | | | | | | | | | | | k1_k2 | DUP_KEYS | mv_k2 | INT | INT | Yes | true | NULL | | true | `k2` | | | | | mv_k1 | INT | INT | Yes | true | NULL | | true | `k1` | | +-----------------+---------------+-------+--------+--------------+------+------+---------+-------+---------+------------+-------------+删除示例表
duplicate_table中名为k1_k2的物化视图。DROP MATERIALIZED VIEW k1_k2 ON duplicate_table;查看删除物化视图后，示例表
duplicate_table的物化视图以及它的表结构，示例如下。DESC duplicate_table ALL;查询结果如下。
+-----------------+---------------+-------+--------+--------------+------+------+---------+-------+---------+------------+-------------+ | IndexName | IndexKeysType | Field | Type | InternalType | Null | Key | Default | Extra | Visible | DefineExpr | WhereClause | +-----------------+---------------+-------+--------+--------------+------+------+---------+-------+---------+------------+-------------+ | duplicate_table | DUP_KEYS | k1 | INT | INT | Yes | true | NULL | | true | | | | | | k2 | INT | INT | Yes | true | NULL | | true | | | | | | k3 | BIGINT | BIGINT | Yes | true | NULL | | true | | | | | | k4 | BIGINT | BIGINT | Yes | true | NULL | | true | | | +-----------------+---------------+-------+--------+--------------+------+------+---------+-------+---------+------------+-------------+
查看创建物化视图的语句
通过命令SHOW CREATE MATERIALIZED VIEW查看创建物化视图使用的语句。
语法
SHOW CREATE MATERIALIZED VIEW <mv_name> ON <table_name>参数说明
参数名称 | 参数说明 |
mv_name | 物化视图的名称。必填项。不能查询已经删除的物化视图。 |
table_name | 物化视图所属的表名。必填项。 |
示例
创建物化视图语句示例如下。
CREATE MATERIALIZED VIEW id_col1 AS SELCET id,col1 FROM table3;查看已创建的物化视图创建语句，示例如下。
SHOW CREATE MATERIALIZED VIEW id_col1 ON table3;查询结果如下。
SHOW CREATE MATERIALIZED VIEW id_col1 on table3;
+-----------+----------+----------------------------------------------------------------+
| TableName | ViewName | CreateStmt |
+-----------+----------+----------------------------------------------------------------+
| table3 | id_col1 | create materialized view id_col1 as select id,col1 from table3 |
+-----------+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)更新策略
为保证物化视图表和目标表的数据一致性，SelectDB会将导入、删除等对目标表的操作都同步到物化视图表中。并且通过增量更新的方式来提升更新效率。通过事务方式来保证原子性。
例如：当通过INSERT命令插入数据到目标表中，则这条数据会同步插入到物化视图中。当目标表和物化视图表均写入成功后，INSERT命令才会成功返回。
查询自动匹配
物化视图创建成功后，查询不需要发生任何改变，仍然是查询目标表。SelectDB会根据当前的查询语句自动选择一个最优的物化视图，从物化视图中读取数据并计算。
您可以通过EXPLAIN命令来检查当前查询是否使用了物化视图。
物化视图的聚合和查询中聚合的匹配关系如下。
物化视图聚合 | 查询中聚合 |
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的聚合函数在查询匹配到物化视图后，查询的聚合算子将根据物化视图的表结构进行重写。
最佳实践
物化视图的使用分为以下3个步骤。
创建物化视图。
异步检查物化视图是否构建完成。
查询并自动匹配物化视图。
例如业务场景是计算广告的PV和UV，原始广告点击数据存储在SelectDB，因此广告PV，UV查询就可以通过创建聚合类型为bitmap_union的物化视图来提升查询速度。
通过如下语句首先创建一个存储广告点击数据明细的表advertiser_view_record。包含每条点击的点击时间，点击的是什么广告，通过什么渠道点击，以及点击的用户是谁。
CREATE TABLE advertiser_view_record(
time date,
advertiser varchar(10),
channel varchar(10),
user_id int
)
DISTRIBUTED BY HASH(time);查询原始的广告点击数据表的表结构，示例如下。
DESC advertiser_view_record ALL;
+------------------------+---------------+------------+-------------+--------------+------+-------+---------+-------+---------+------------+-------------+
| IndexName | IndexKeysType | Field | Type | InternalType | Null | Key | Default | Extra | Visible | DefineExpr | WhereClause |
+------------------------+---------------+------------+-------------+--------------+------+-------+---------+-------+---------+------------+-------------+
| advertiser_view_record | DUP_KEYS | time | DATE | DATEV2 | Yes | true | NULL | | true | | |
| | | advertiser | VARCHAR(10) | VARCHAR(10) | Yes | true | NULL | | true | | |
| | | channel | VARCHAR(10) | VARCHAR(10) | Yes | false | NULL | NONE | true | | |
| | | user_id | INT | INT | Yes | false | NULL | NONE | true | | |
+------------------------+---------------+------------+-------------+--------------+------+-------+---------+-------+---------+------------+-------------+
4 rows in set (0.02 sec)创建物化视图
查询广告的UV值，需要对相同广告的用户进行精确去重，通常的查询方法如下。
SELECT advertiser, channel, COUNT(distinct user_id)
FROM advertiser_view_record
GROUP BY advertiser, channel;对于这种求UV的场景，可以创建一个带bitmap_union的物化视图从而达到一个预先精确去重的效果。在SelectDB中，count(distinct)聚合的结果和bitmap_union_count聚合的结果是完全一致的。而bitmap_union_count等于对bitmap_union的结果求count，所以如果查询中涉及到count(distinct)，通过创建带bitmap_union聚合的物化视图即可加快查询速度。对于这个案例，则可以创建一个根据广告和渠道分组，对user_id进行精确去重的物化视图。
CREATE MATERIALIZED VIEW advertiser_uv AS SELECT advertiser, channel, bitmap_union(to_bitmap(user_id)) FROM advertiser_view_record GROUP BY advertiser, channel;
Query OK, 0 rows affected (0.012 sec)因为本身user_id是一个INT类型，所以在SelectDB中需要先将字段通过函数to_bitmap转换为bitmap类型，然后才可以进行bitmap_union聚合。
创建物化视图完成后查询广告点击明细表的物化视图和它的表结构，示例如下。
DESC advertiser_view_record ALL;
+------------------------+---------------+-------------------------------------------------------------------+-------------+--------------+------+-------+---------+--------------+---------+-------------------------------------------------+-------------+
| IndexName | IndexKeysType | Field | Type | InternalType | Null | Key | Default | Extra | Visible | DefineExpr | WhereClause |
+------------------------+---------------+-------------------------------------------------------------------+-------------+--------------+------+-------+---------+--------------+---------+-------------------------------------------------+-------------+
| advertiser_view_record | DUP_KEYS | time | DATE | DATEV2 | Yes | true | NULL | | true | | |
| | | advertiser | VARCHAR(10) | VARCHAR(10) | Yes | true | NULL | | true | | |
| | | channel | VARCHAR(10) | VARCHAR(10) | Yes | false | NULL | NONE | true | | |
| | | user_id | INT | INT | Yes | false | NULL | NONE | true | | |
| | | | | | | | | | | | |
| advertiser_uv | AGG_KEYS | mv_advertiser | VARCHAR(*) | VARCHAR(*) | Yes | true | NULL | | true | `advertiser` | |
| | | mv_channel | VARCHAR(*) | VARCHAR(*) | Yes | true | NULL | | true | `channel` | |
| | | mva_BITMAP_UNION__to_bitmap_with_check(CAST(`user_id` AS BIGINT)) | BITMAP | BITMAP | No | false | NULL | BITMAP_UNION | true | to_bitmap_with_check(CAST(`user_id` AS BIGINT)) | |
+------------------------+---------------+-------------------------------------------------------------------+-------------+--------------+------+-------+---------+--------------+---------+-------------------------------------------------+-------------+
8 rows in set (0.03 sec)查询自动匹配
当物化视图表创建完成后，查询广告UV时，SelectDB就会自动从刚才创建好的物化视图advertiser_uv中查询数据。原始的查询语句，示例如下。
SELECT advertiser, channel, COUNT(distinct user_id) FROM advertiser_view_record GROUP BY advertiser, channel;在命中物化视图后，实际的查询会转化为如下查询。
SELECT advertiser, channel, bitmap_union_count(to_bitmap(user_id)) FROM advertiser_uv GROUP BY advertiser, channel;通过EXPLAIN命令可以检验到SelectDB是否匹配到了物化视图，示例如下。
EXPLAIN SELECT advertiser, channel, count(distinct user_id) FROM advertiser_view_record GROUP BY advertiser, channel;
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS: |
| advertiser[#13] |
| channel[#14] |
| count(DISTINCT user_id)[#15] |
| PARTITION: UNPARTITIONED |
| |
| VRESULT SINK |
| |
| 4:VEXCHANGE |
| offset: 0 |
| |
| PLAN FRAGMENT 1 |
| |
| PARTITION: HASH_PARTITIONED: mv_advertiser[#7], mv_channel[#8] |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 04 |
| UNPARTITIONED |
| |
| 3:VAGGREGATE (merge finalize) |
| | output: bitmap_union_count(partial_bitmap_union_count(mva_BITMAP_UNION__to_bitmap_with_check(cast(user_id as BIGINT)))[#9])[#12] |
| | group by: mv_advertiser[#7], mv_channel[#8] |
| | cardinality=1 |
| | projections: mv_advertiser[#10], mv_channel[#11], bitmap_union_count(mva_BITMAP_UNION__to_bitmap_with_check(cast(user_id as BIGINT)))[#12] |
| | project output tuple id: 4 |
| | |
| 2:VEXCHANGE |
| offset: 0 |
| |
| PLAN FRAGMENT 2 |
| |
| PARTITION: HASH_PARTITIONED: time[#3] |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 02 |
| HASH_PARTITIONED: mv_advertiser[#7], mv_channel[#8] |
| |
| 1:VAGGREGATE (update serialize) |
| | STREAMING |
| | output: partial_bitmap_union_count(mva_BITMAP_UNION__to_bitmap_with_check(cast(user_id as BIGINT))[#2])[#9] |
| | group by: mv_advertiser[#0], mv_channel[#1] |
| | cardinality=1 |
| | |
| 0:VOlapScanNode |
| TABLE: default_cluster:test.advertiser_view_record(advertiser_uv), PREAGGREGATION: ON |
| partitions=1/1, tablets=10/10, tabletList=13531,13533,13535 ... |
| cardinality=1, avgRowSize=2745.0, numNodes=1 |
| pushAggOp=NONE |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
49 rows in set (0.11 sec)在EXPLAIN的结果中，首先可以看到OlapScanNode的rollup属性值为advertiser_uv。表明查询会直接扫描物化视图的数据。说明匹配成功。
其次对于user_id字段求count(distinct)被改写为bitmap_union_count(to_bitmap)。表明查询会通过Bitmap的方式来达到精确去重的效果。
物化视图使用须知
局限性
物化视图中聚合函数的参数不支持表达式仅支持单列，例如：不支持
sum(a+b)。如果删除语句的条件列，在物化视图中不存在，则不能进行删除操作。如果一定要删除数据，则需要先将物化视图删除，然后删除数据。
单表上过多的物化视图会影响数据导入的效率。导入数据时，物化视图和目标表数据是同步更新的。例如：一张表创建的物化视图表超过10张，则有可能导致导入速度很慢。因为这相当于单次导入需要同时向10张表中导入数据。
物化视图中不允许相同列出现不同聚合函数。例如：不支持
select sum(a), min(a) from table。物化视图针对Unique Key数据模型，只能改变列顺序，不能起到聚合的作用，所以在Unique Key模型上不能通过创建物化视图的方式对数据进行粗粒度聚合操作。
物化视图 VS ROLLUP
在没有物化视图功能之前，用户一般都是使用ROLLUP功能通过预聚合方式提升查询效率的。但是ROLLUP具有一定的局限性，它不能基于明细模型做预聚合。
物化视图在覆盖了ROLLUP功能的同时，还支持更丰富的聚合函数。物化视图实际是ROLLUP功能的超集，建议采用物化视图方案来提升查询效率。
常见问题
Q：报错：DATA_QUALITY_ERR："The data quality does not satisfy, please check your data."
A：由于数据质量问题或者Schema变更，使得内存使用超出限制，导致物化视图创建失败。如果是内存问题，调大
memory_limitation_per_thread_for_schema_change_bytes参数即可。重要Bitmap类型仅支持正整型,如果原始数据中存在负数，会导致物化视图创建失败。
String类型的字段可使用bitmap_hash或bitmap_hash64计算Hash值，并返回Hash值的bitmap。
- 本页导读 （1）