1、视图
视图(逻辑视图，logical views)是封装了一个或多个SELECT语句的存储查询（stored queries）。视图在执行时动态访问和计算数据库数据。视图是只读的，可以引用任何表和其他视图的组合。
视图可用于以下目的:
- 通过对用户隐藏复杂的SELECT语句来简化访问或提供安全访问。例如，您可以创建一个视图，该视图只显示各种表中用户需要的数据，同时在这些表中隐藏敏感数据。
- 将可能随时间变化的表结构的细节封装在一致的用户界面后面。
与物化视图不同，逻辑视图不是物化的，这意味着它们不将数据存储在磁盘上。因此，它们具有以下局限性:
- 当底层表数据发生变化时，Doris不需要刷新视图数据。但是，通过视图访问和计算数据可能会产生一些开销。
- 视图不支持插入、删除或更新操作。
创建视图
创建逻辑视图的语法如下:
CREATE VIEW [IF NOT EXISTS]
[db_name.]view_name
(column1[ COMMENT "col comment"][, column2, ...])
AS query_stmt
解释：
- 视图是逻辑的，没有物理存储。视图上的所有查询都等同于视图对应子查询上的查询。
query_stmt是任何受支持的SQL语句。
Example:
在example_db数据库中创建名为example_view的视图:
CREATE VIEW example_db.example_view (k1, k2, k3, v1)
AS
SELECT c1 as k1, k2, k3, SUM(v1) FROM example_table
WHERE k1 = 20160112 GROUP BY k1,k2,k3;
创建带有注释的视图:
CREATE VIEW example_db.example_view
(
k1 COMMENT "first key",
k2 COMMENT "second key",
k3 COMMENT "third key",
v1 COMMENT "first value"
)
COMMENT "my first view"
AS
SELECT c1 as k1, k2, k3, SUM(v1) FROM example_table
WHERE k1 = 20160112 GROUP BY k1,k2,k3;
2、物化视图
物化视图是预先计算(根据定义的SELECT语句)并存储在Doris中的一个特殊表中的数据集。
物化视图的出现主要是为了满足用户。它可以分析任意维度的原始详细数据，也可以快速分析和查询固定维度。
2.1 何时使用物化视图
- 分析需求，包括详细的数据查询和固定维度的查询。
- 查询只涉及表中一小部分列或行。
- 查询包含一些耗时的处理操作，如长时间的聚合操作。
- 查询需要匹配不同的前缀索引。
2.2 优点
- 对于那些经常重复使用相同子查询结果的查询，性能得到了很大的提高
- Doris自动维护物化视图的数据，无论是新的导入还是删除操作，都可以保证基表和物化视图表的数据一致性。不需要任何额外的人工维护成本。
- 查询时，自动匹配最优的物化视图，直接从物化视图读取数据。
对物化视图数据的自动维护将导致一些维护开销，稍后将在物化视图的限制中解释这一点。
2.3 物化视图VS Rollup
在物化视图功能出现之前，用户通常使用Rollup功能通过预聚合来提高查询效率。但是，Rollup有一定的局限性。它不能基于明细模型进行预聚合。
物化视图涵盖了Rollup的功能，同时还支持更丰富的聚合功能。物化视图实际上是Rollup的超集。
换句话说，以前由ALTER TABLE ADD ROLLUP语法支持的功能现在可以通过CREATE MATERIALIZED VIEW实现。
2.4 使用物化视图
Doris系统为物化视图提供了一套完整的DDL语法，包括创建、查看和删除。DDL的语法与PostgreSQL和Oracle一致。
2.4.1 创建一个物化视图
在这里，您必须首先根据查询语句的特征决定创建哪种物化视图。这并不是说您的物化视图定义与您的查询语句之一完全相同。这里有两个原则:
- 从查询语句中抽象出来，使用多个查询共享的分组和聚合方法作为物化视图的定义。
- 没有必要为所有维度组合创建实体化视图。
首先，第一点，如果一个物化视图被抽象，并且多个查询可以匹配到这个物化视图。这种物化视图效果最好。因为维护物化视图本身也会消耗资源。
如果物化视图只适合特定查询，而其他查询不使用此物化视图。物化视图不仅占用集群的存储资源，而且无法提供更多的查询服务，因此成本效益不高。
因此，用户需要将自己的查询语句和数据维度信息结合起来，抽象出一些物化视图的定义。
第二点是，在实际的分析查询中，并不是所有的维度分析都会被覆盖。因此，对于常用的维度组合，创建一个物化的视图就足够了，从而达到空间和时间的平衡。
创建物化视图是一个异步操作，这意味着在用户成功提交创建任务后，Doris将在后台计算现有数据，直到创建成功。
具体语法可以通过以下命令查看:
HELP CREATE MATERIALIZED VIEW
在Doris 2.0中，我们对物化视图做了一些增强(在本文的最佳实践4中进行了描述)。我们建议用户在正式的生产环境中使用物化视图之前，先在测试环境中检查预期的查询是否能够命中期望的物化视图。
如果您不知道如何验证查询是否命中物化视图，可以阅读本文的最佳实践1。
同时，我们不建议用户在同一个表上创建多个形状相似的物化视图，这可能会导致多个物化视图之间的冲突，导致查询命中失败(这个问题将在新的优化器中得到改善)。建议用户首先验证物化视图和查询是否满足需求，是否可以在测试环境中正常使用。
2.4 2 支持聚合函数
物化视图函数目前支持的聚合函数有:
-
SUM, MIN, MAX (Version 0.12)
-
COUNT, BITMAP_UNION, HLL_UNION (Version 0.13)
-
AGG_STATE (Version 2.0)
一些最初不支持的聚合函数将被转换为agg_state类型以实现预聚合。
2.4.3 更新策略
为了保证物化视图表和基表之间的数据一致性，Doris将基表上的导入、删除等操作都同步到物化视图表上。并通过增量更新来提高更新效率。通过事务确保原子性。
例如，如果用户通过INSERT命令将数据插入到基表中，则该数据将同步插入到物化视图中。当基表和物化视图表都被成功写入时，INSERT命令将成功返回。
2.4.4 查询自动匹配
成功创建物化视图后，不需要更改用户的查询，也就是说，它仍然是查询的基表。Doris会根据当前查询语句自动选择最优的物化视图，从物化视图中读取数据并进行计算。
用户可以使用EXPLAIN命令检查当前查询是否使用了物化视图。
物化视图中的聚合与查询中的聚合的匹配关系:
当位图和hll的聚合函数在查询中匹配物化视图后，将根据物化视图的表结构重写查询的聚合操作符。有关详细信息，请参见示例2。
2.4.5 查询物化视图
检查当前表有哪些物化视图，以及它们的表结构是什么。通过以下命令:
MySQL [test]> desc mv_test all;
+-----------+---------------+-----------------+----------+------+-------+---------+--------------+
| IndexName | IndexKeysType | Field | Type | Null | Key | Default | Extra |
+-----------+---------------+-----------------+----------+------+-------+---------+--------------+
| mv_test | DUP_KEYS | k1 | INT | Yes | true | NULL | |
| | | k2 | BIGINT | Yes | true | NULL | |
| | | k3 | LARGEINT | Yes | true | NULL | |
| | | k4 | SMALLINT | Yes | false | NULL | NONE |
| | | | | | | | |
| mv_2 | AGG_KEYS | k2 | BIGINT | Yes | true | NULL | |
| | | k4 | SMALLINT | Yes | false | NULL | MIN |
| | | k1 | INT | Yes | false | NULL | MAX |
| | | | | | | | |
| mv_3 | AGG_KEYS | k1 | INT | Yes | true | NULL | |
| | | to_bitmap(`k2`) | BITMAP | No | false | | BITMAP_UNION |
| | | | | | | | |
| mv_1 | AGG_KEYS | k4 | SMALLINT | Yes | true | NULL | |
| | | k1 | BIGINT | Yes | false | NULL | SUM |
| | | k3 | LARGEINT | Yes | false | NULL | SUM |
| | | k2 | BIGINT | Yes | false | NULL | MIN |
+-----------+---------------+-----------------+----------+------+-------+---------+--------------+
您可以看到当前mv_test表有三个物化视图:mv_1、mv_2和mv_3，以及它们的表结构。
2.4.6 删除物化视图
如果用户不再需要物化视图，您可以通过**DROP 删除物化视图**。 DROP MATERIALIZED VIEW
用户可以通过命令查看已创建的物化视图:
SHOW CREATE MATERIALIZED VIEW
取消创建物化视图
CANCEL ALTER TABLE MATERIALIZED VIEW FROM db_name.table_name
2.5 最佳实践1
物化视图的使用一般分为以下几个步骤:
- 创建一个物化视图
- 异步检查物化视图是否已被构造
- 查询和自动匹配物化视图
首先是第一步:创建一个物化视图
假设用户有一个销售记录列表，其中存储了交易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");
insert into sales_records values(1,1,1,"2020-02-02",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表创建一个物化视图，对销售商店进行分组，并对相同销售商店的销售额求和。创建语句如下:
MySQL [test]> create materialized view store_amt
as select store_id, sum(sale_amt)
from sales_records
group by store_id;
后端返回到下图，表示创建物化视图的任务已成功提交。
Query OK, 0 rows affected (0.012 sec)
步骤2:检查物化视图是否已构建完成
由于创建物化视图是一个异步操作，用户提交创建物化视图的任务后，需要通过命令异步检查物化视图是否已经构造完成。命令如下:
SHOW ALTER TABLE ROLLUP FROM db_name; (Version 0.12)
SHOW ALTER TABLE MATERIALIZED VIEW FROM db_name; (Version 0.13)
其中db_name为参数，需要使用实际的数据库名称代替。该命令的结果是显示该数据库下创建物化视图的所有任务。结果如下:
+-------+---------------+---------------------+--- ------------------+---------------+--------------- --+----------+---------------+-----------+-------- -------------------------------------------------- -------------------------------------------------- -------------+----------+---------+
| JobId | TableName | CreateTime | FinishedTime | BaseIndexName | RollupIndexName | RollupId | TransactionId | State | Msg | Progress | Timeout |
+-------+---------------+---------------------+--- ------------------+---------------+--------------- --+----------+---------------+-----------+-------- -------------------------------------------------- -------------------------------------------------- -------------+----------+---------+
| 22036 | sales_records | 2020-07-30 20:04:28 | 2020-07-30 20:04:57 | sales_records | store_amt | 22037 | 5008 | FINISHED | | NULL | 86400 |
+-------+---------------+---------------------+--- ------------------+---------------+--------------- --+----------+---------------+-----------+-------- ----------------------------------------
其中，TableName指的是物化视图的数据来自哪个表，RollupIndexName指的是物化视图的名称。一个比较重要的指标是State。
当创建物化视图的任务State 变为FINISHED时，意味着物化视图已经成功创建。这意味着在查询时可以自动匹配这个物化视图。
第三步:查询
创建物化视图后，当用户查询不同商店的销售额时，将直接从刚刚创建的物化视图store_amt中读取聚合的数据。达到提高查询效率的效果。
用户的查询仍然指定查询sales_records表，例如:
SELECT store_id, sum(sale_amt) FROM sales_records GROUP BY store_id;
上面的查询将自动匹配store_amt。用户可以使用以下命令检查当前查询是否与相应的物化视图匹配。
EXPLAIN SELECT store_id, sum(sale_amt) FROM sales_records GROUP BY store_id;
+----------------------------------------------------------------------------------------------+
| Explain String |
+----------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS: |
| <slot 4> `default_cluster:test`.`sales_records`.`mv_store_id` |
| <slot 5> sum(`default_cluster:test`.`sales_records`.`mva_SUM__`sale_amt``) |
| PARTITION: UNPARTITIONED |
| |
| VRESULT SINK |
| |
| 4:VEXCHANGE |
| offset: 0 |
| |
| PLAN FRAGMENT 1 |
| |
| PARTITION: HASH_PARTITIONED: <slot 4> `default_cluster:test`.`sales_records`.`mv_store_id` |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 04 |
| UNPARTITIONED |
| |
| 3:VAGGREGATE (merge finalize) |
| | output: sum(<slot 5> sum(`default_cluster:test`.`sales_records`.`mva_SUM__`sale_amt``)
最低0.47元/天 解锁文章
扫一扫
667
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
