--- title: "物化视图 - OceanBase 数据库 V4.3.2 | OceanBase 文档中心" description: 物化视图 物化视图存储了视图定义查询的结果。可以加速该视图查询，减少查询的系统资源消耗（cpu, 网络，磁盘等）。利用存储物化视图结果的空间换取的查询时候的时间。 常见用例 数据汇总：汇总每天、每周或每月的销售数据、统计用户行为数据等 统计信息报表数据生成：报表系统需要定期生成固定格式的数据报告 复杂查询优化：对于特… image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 4.3.2 # 物化视图 更新时间：2025-01-17 14:56:51 [编辑](https://github.com/oceanbase/oceanbase-doc/edit/V4.3.2/zh-CN/620.obap/600.obap-query/100.obap-materialized-view.md) 物化视图存储了视图定义查询的结果。可以加速该视图查询，减少查询的系统资源消耗（cpu, 网络，磁盘等）。利用存储物化视图结果的空间换取的查询时候的时间。 ## 常见用例 - 数据汇总：汇总每天、每周或每月的销售数据、统计用户行为数据等 - 统计信息报表数据生成：报表系统需要定期生成固定格式的数据报告 - 复杂查询优化：对于特别消耗资源的查询，可以将结果物化避免查询重复计算 - 分发数据：冗余多份数据放到不同的区域中，便于各个区域的人就近访问数据 - 监控数据的预聚合：监控数据会按照不同的时间间隔进行展示 ## 使用示例 基于给定的表和物化视图创建情况，以下是如何生成一个物化视图来加速查询的具体示例。首先，让我们确保我们有一个适当的表结构和物化视图定义。 ### 表结构定义 ```sql CREATE TABLE orders ( order_id INT primary key, user_id INT, item_id INT, item_count INT, item_price DECIMAL(10, 2) NOT NULL, region VARCHAR(100) ); CREATE TABLE order_items ( order_id INT, product_id INT, quantity INT, price_per_item DECIMAL(10, 2) NOT NULL, primary key (order_id, product_id) ); ``` ### 创建物化视图 #### 注意 - 由于 `REFRESH FAST` 方法利用物化视图日志中的记录信息来确定需要增量刷新的内容，因此在使用增量刷新刷新物化视图时，需要在创建物化视图之前就创建基表的物化视图日志（mlog）。 - 增量刷新物化视图中使用的列都必须在 mlog 里。 有关创建物化视图日志的信息，请参见 [创建物化视图日志](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001053749)。 以下是基于提供的信息创建物化视图的示例。 **汇总物化视图**：计算每个地区的总销售额 ```sql CREATE MATERIALIZED VIEW m1 REFRESH COMPLETE ON DEMAND AS SELECT SUM(item_count * item_price) AS total_sales, count(item_count * item_price) cnt_total_sales, count(*) cnt, region FROM orders GROUP BY region; ``` **宽表物化视图**：将订单与商品详情关联，形成一个宽表 ```sql CREATE MATERIALIZED VIEW wide_orders REFRESH COMPLETE ON DEMAND AS SELECT o.order_id, o.user_id AS customer_id, o.item_id, o.item_count AS quantity, o.item_price AS price_per_item, o.region FROM orders o JOIN order_items oi ON o.order_id = oi.order_id; ``` ### 加速查询示例 假设我们有一个查询，需要快速获取某个地区的订单详情和销售总额，可以使用上述物化视图来加速查询。以下是一个具体的示例查询。 ```sql -- 查询某个地区的所有订单详情和总销售额 SELECT wo.order_id, wo.customer_id, wo.product_id, wo.quantity, wo.price_per_item, m1.total_sales FROM wide_orders wo JOIN m1 ON wo.region = m1.region WHERE wo.region = 'North America'; ``` 在这个查询中，**wide_orders** 物化视图提供了订单与商品详情的宽表数据，**m1** 物化视图提供了每个地区的总销售额。通过将这两个物化视图结合起来，我们可以快速查询特定地区的订单详情和总销售额。 ### 刷新策略 为了保持物化视图的数据最新，我们可以根据需求设置合适的刷新策略。上述示例中，我们使用了 **REFRESH COMPLETE ON DEMAND**，表示需要手动触发刷新。如果需要定期自动刷新，可以修改刷新策略，例如： ```sql -- 每小时自动刷新一次 CREATE MATERIALIZED VIEW m1 REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE + 1/24 AS SELECT SUM(item_count * item_price) AS total_sales, region FROM orders GROUP BY region; -- 每天自动刷新一次 CREATE MATERIALIZED VIEW wide_orders REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE + 1 AS SELECT o.order_id, o.user_id AS customer_id, o.item_id, o.item_count AS quantity, o.item_price AS price_per_item, o.region FROM orders o JOIN order_items oi ON o.order_id = oi.order_id; ``` 通过这种方式，物化视图可以在指定的时间间隔内自动刷新，以确保数据的及时性和准确性，从而加速查询性能。 ## 创建物化视图 ```sql create_mv_stmt := CREATE MATERIALIZED VIEW view_name opt_column_list opt_table_option_list opt_partition_option [refresh_clause] AS view_select_stmt refresh_clause := REFRESH [FAST/COMPLETE/FORCE] [mv_refresh_on_clause] / NEVER REFRESH mv_refresh_on_clause := [ON DEMAND] [[START WITH expr] [NEXT expr]] ``` 语法如上图所示，和创建普通视图类似。最主要的是增加了 refresh 子句来描述刷新的信息。在刷新章节会详细介绍。 在创建物化视图时，可以通过指定刷新子句（refresh_clause）来定义物化视图的定时刷新策略。这包括选择增量刷新（FAST）、全量刷新（COMPLETE）或根据情况自动选择的强制刷新（FORCE）。此外，还可以设置刷新触发条件，如按需（ON DEMAND）、指定开始时间（START WITH expr）和下一次刷新时间（NEXT expr）。以下是定义物化视图定时刷新的语法和示例。 ### 语法 ```sql CREATE MATERIALIZED VIEW view_name REFRESH [FAST | COMPLETE | FORCE] [ON DEMAND | START WITH expr NEXT expr | NEVER REFRESH] AS SELECT ... ``` - FAST：进行增量刷新。 - COMPLETE：进行全量刷新。 - FORCE：根据当前情况自动选择增量或全量刷新。 - ON DEMAND：按需刷新，即手动触发刷新。 - START WITH expr：定义首次刷新的时间。 - NEXT expr：定义下一次刷新的时间。 - NEVER REFRESH：不自动刷新，只能手动刷新。 有关创建物化视图日志的信息，请参见 [创建物化视图日志](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001053741)。 ### 示例 按需增量刷新 ```sql CREATE MATERIALIZED VIEW sales_mv REFRESH FAST ON DEMAND AS SELECT product_id, SUM(amount) AS total_sales FROM sales GROUP BY product_id; ``` 每日全量刷新 ```sql CREATE MATERIALIZED VIEW daily_sales_mv REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE + 1 AS SELECT product_id, SUM(amount) AS total_sales FROM sales GROUP BY product_id; ``` 每小时自动选择刷新类型 ```sql CREATE MATERIALIZED VIEW hourly_sales_mv REFRESH FORCE START WITH SYSDATE NEXT SYSDATE + 1/24 AS SELECT product_id, SUM(amount) AS total_sales FROM sales GROUP BY product_id; ``` 从不自动刷新 ```sql CREATE MATERIALIZED VIEW static_sales_mv REFRESH NEVER AS SELECT product_id, SUM(amount) AS total_sales FROM sales GROUP BY product_id; ``` **解释:** - 按需增量刷新：物化视图 sales_mv 将使用增量刷新，但仅在手动触发时刷新。 - 每日全量刷新：物化视图 daily_sales_mv 每天刷新一次，每次执行全量刷新。 - 每小时自动选择刷新类型：物化视图 hourly_sales_mv 每小时刷新一次，数据库会自动选择使用增量刷新还是全量刷新。 - 从不自动刷新：物化视图 static_sales_mv 不会自动刷新，只能通过手动操作进行刷新。 通过合理设置这些刷新策略，可以根据具体需求和系统性能要求，确保物化视图的数据既及时又高效地保持最新。 ## 删除物化视图 ```sql DROP MATERIALIZED VIEW opt_if_exists table_list opt_drop_behavior ``` 物化视图不能单独进回收站。删除 database 的时候，物化视图能进回收站。 ## 刷新 物化视图刷新（Materialized View Refresh）是确保物化视图中的数据保持最新状态的重要操作。物化视图是一种存储查询结果的数据库对象，可以显著提高查询性能。然而，随着源数据的变化，物化视图的数据也需要更新以反映最新的状态。物化视图的刷新可以分为全量刷新和增量刷新两种方式。 全量刷新（Complete Refresh）指的是重新计算并替换物化视图中的所有数据。这种方式确保了物化视图数据的完整性和准确性，但由于涉及大量数据的重算和写入操作，全量刷新通常会消耗较多的系统资源和时间。 增量刷新（Incremental Refresh），也称为快速刷新（Fast Refresh），则是只更新自上次刷新以来发生变化的数据。这种方式可以显著减少刷新所需的资源和时间，但需要在数据库中维护额外的日志或触发器，以跟踪数据的变化。 由于刷新操作会消耗系统资源，因此需要根据实际需求和系统负载，合理安排物化视图的刷新频率。常见的做法是定期刷新，可以是每天、每小时甚至更频繁地执行，以确保物化视图数据的及时性和准确性。 ### 全量刷新 物化视图全量刷新（Complete Refresh）是一种确保物化视图数据与源数据完全一致的刷新方式。在全量刷新过程中，物化视图会重新执行定义它的查询语句，并完全替换其内部数据。这意味着，不论查询的复杂度和涉及的数据量多大，全量刷新都会执行整个查询并将结果集全部重新存储到物化视图中。因此，全量刷新能够支持任何类型的查询语句，包括复杂的联接、聚合和嵌套查询。 全量刷新的主要优势在于其简单和可靠性。因为它每次都会重建整个数据集，所以能够确保物化视图中的数据绝对准确，与源数据保持完全一致。尤其在源数据变化频繁或数据更新模式复杂的场景下，全量刷新提供了一种无视这些复杂变化、直接获取最新数据的方法。 然而，由于全量刷新涉及对全部数据的重新计算和写入，因此相对来说，它会消耗更多的系统资源和时间，特别是在处理大规模数据集时。这种资源消耗不仅包括计算能力，还包括 I/O 操作的开销。因此，虽然全量刷新能够提供数据准确性和完整性，但在实际应用中需要慎重考虑其执行频率，以平衡系统性能和数据及时性之间的关系。 刷新语法 ```sql DBMS_MVIEW.REFRESH ( IN mv_name VARCHAR(65535), -- 物化视图名称 IN method VARCHAR(65535) DEFAULT NULL, -- 刷新选项 -- f 快速刷新 -- ? 强制刷新 -- C|c 完全刷新 -- A|a 始终刷新，等价于C IN refresh_parallel INT DEFAULT 1); -- 刷新并行度 ``` ### 增量刷新 物化视图增量刷新（Incremental Refresh），也称为快速刷新（Fast Refresh），是一种仅更新自上次刷新以来发生变化的数据的刷新方式。与全量刷新不同，增量刷新只对源数据中发生变更的部分进行处理和更新，从而提高刷新效率，减少系统资源消耗和刷新时间。然而，增量刷新仅支持部分 SELECT 语句，通常需要满足特定的语法和结构要求，如简单的联接和聚合操作。 增量刷新依赖于物化日志（Materialized View Log, 简称 MLOG）的支持。MLOG 是一个特殊的数据库对象，用于记录基表中发生的增量变化。每当基表中的数据被插入、更新或删除时，这些变化都会记录在 MLOG 中。增量刷新时，系统读取 MLOG 中的记录，识别并处理这些变化，从而只更新物化视图中的相关部分数据。 虽然增量刷新显著提高了刷新效率，但 MLOG 的使用会额外占用数据库空间，因为它需要存储基表的变更日志。随着时间推移和数据变更的积累，MLOG 的大小可能不断增长，需定期管理和维护，以避免占用过多存储资源。 ## 物化视图查询改写 通过使用物化视图来加速查询，当输入一个不使用物化视图的查询时，系统会自动将查询重写为使用现有物化视图的查询。该方法的原理是将查询语句与物化视图的定义进行匹配，如果发现匹配的物化视图，则自动将查询重写为使用物化视图的查询，这样可以大大提高查询性能和效率。 ### 物化视图查询改写使用限制 - 物化视图满足以下要求： - 创建物化视图时，指定 `ENABLE QUERY REWRITE` 开启当前物化视图的自动改写。 - 需要物化视图仅包含 `SELECT JOIN` 与 `WHERE` 子句，即 SPJ 查询。对于不满足条件的物化视图，不会报错，但不会被用于改写。 - 当前查询满足以下要求： - 查询为 `SELECT` 查询，不是集合查询或层次查询，并且不包含窗口函数。 - `FROM` 子句与物化视图完全匹配。 - `WHERE` 条件物化视图是当前查询的子集，当前查询有聚合的情况下需要完全匹配。例如，物化视图的 `WHERE` 是 `c1 > 10`，当前查询的 `WHERE` 是 `c1 > 10 AND c2 >20`。这样物化视图的条件 {c1 > 10} 是当前查询 {c1>10, c2>20} 的子集。 - 当前查询所涉及的 `SELECT` 项、`WHERE`、`HAVING` 和 `GROUP BY` 等条件中的列，都需要包含在物化视图的 `SELECT` 语句中的列中。 ### 规则/代价检查 查询改写时，OceanBase 数据库当前版本不进行代价检查，但规则检查要求改写后的 `WHERE` 条件能够使用至少一个物化视图上的索引。此外，当有超过 10 个的物化视图存在时，物化视图查询改写将仅尝试使用前 10 个物化视图。 ### 物化视图改写 控制物化视图查询改写的系统变量如下： - `query_rewrite_enabled`：用于是否开启物化视图改写功能。该变量的详细介绍信息，参见 [query_rewrite_enabled](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001052700)。 **示例如下：** ```sql SET query_rewrite_enabled = 'force'; ``` - `query_rewrite_integrity`：用于指定物化视图改写的数据一致性检查级别。该变量的详细介绍信息，参见 [query_rewrite_integrity](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001052795)。 **示例如下：** ```sql SET query_rewrite_integrity = 'stale_tolerated'; ``` **通过以下示例展示物化视图改写：** 1. 创建表 `test_tbl1`。 ```sql CREATE TABLE test_tbl1 (col1 INT, col2 INT, col3 INT); ``` 2. 创建表 `test_tbl2`。 ```sql CREATE TABLE test_tbl2 (col1 INT, col2 INT, col3 INT); ``` 3. 创建名为 `mv_test_tbl1_tbl2` 的物化视图，并开启当前物化视图的自动改写。 ```sql CREATE MATERIALIZED VIEW mv_test_tbl1_tbl2 ENABLE QUERY REWRITE AS SELECT t1.col1 col1, t1.col2 t1col2, t1.col3 t1col3, t2.col2 t2col2, t2.col3 t2col3 FROM test_tbl1 t1, test_tbl2 t2 WHERE t1.col1 = t2.col1; ``` 4. 当输入查询 `SELECT count(*), test_tbl1.col1 col1 FROM test_tbl1, test_tbl2 WHERE test_tbl1.col1 = test_tbl2.col1 AND test_tbl2.col2 > 10 GROUP BY col1;` 时，发生物化视图查询改写。 ```sql SELECT count(*), test_tbl1.col1 col1 FROM test_tbl1, test_tbl2 WHERE test_tbl1.col1 = test_tbl2.col1 AND test_tbl2.col2 > 10 GROUP BY col1; MV REWRITE ==> SELECT count(*), mv_test_tbl1_tbl2.col1 col1 FROM mv_test_tbl1_tbl2 WHERE mv_test_tbl1_tbl2.t2col2 > 10 GROUP BY mv_test_tbl1_tbl2.col1; ``` 物化视图查询改写要求输入的查询与物化视图的 `FROM` 完全匹配，`WHERE` 条件包含物化视图中的所有 `WHERE` 条件。 ### 物化视图查询改写控制 物化视图查询改写控制包括 `MV_REWRITE`、`NO_MV_REWRITE` 两个 Hint，并且这两个 Hint 的优先级高于系统变量 `query_rewrite_enabled`。 #### MV_REWRITE `MV_REWRITE` 语法如下： ```sql /*+ MV_REWRITE (@ queryblock [mv_name_list]) */ mv_name_list: mv_name [, mv_name ...] ``` 单独使用 `MV_REWRITE` Hint 时可以跳过物化视图查询改写的规则/代价检查，直接使用可以使用的改写。Hint 后指定一个或多个物化视图的情况，除了跳过规则/代价检查外，物化视图查询改写将只使用指定的物化视图尝试改写，忽略所有没被指定的物化视图。 使用 `MV_REWRITE` Hint 指定物化视图时，无法强制使用没有 `ENABLE QUERY REWRITE`（开启当前物化视图的自动改写） 子句的物化视图，无法在系统变量 `query_rewrite_integrity` 设置为 `enforced` 的情况下强制使用非实时物化视图。 #### NO_MV_REWRITE `NO_MV_REWRITE` 语法如下： ```sql /*+ NO_MV_REWRITE (@ queryblock) */ ``` 禁止物化视图查询改写，可以指定 query block。 #### 物化视图查询改写控制 Hint 使用示例 1. 创建基表 `tbl2`。 ```sql CREATE TABLE tbl2 (col1 INT, col2 INT); ``` 2. 向基表 `tbl2` 中插入两条数据。 ```sql INSERT INTO tbl2 VALUES (1,2),(3,4); ``` 返回结果如下： ```shell Query OK, 2 rows affected Records: 2 Duplicates: 0 Warnings: 0 ``` 3. 创建物化视图 `mv1_tbl2`，并开启当前物化视图的自动改写。 ```sql CREATE MATERIALIZED VIEW mv1_tbl2 NEVER REFRESH ENABLE QUERY REWRITE AS SELECT * FROM tbl2; ``` 4. 创建物化视图 `mv2_tbl2`，并开启当前物化视图的自动改写。 ```sql CREATE MATERIALIZED VIEW mv2_tbl2 NEVER REFRESH ENABLE QUERY REWRITE AS SELECT * FROM tbl2 WHERE tbl2.col1 > 1; ``` 5. 设置系统变量 `query_rewrite_integrity` 为 `stale_tolerated`。 #### 说明 `MV_REWRITE` 和 `NO_MV_REWRITE` Hint 的优先级高于系统变量 `query_rewrite_enabled`，因此不需要设置 `query_rewrite_enabled`。但是需要设置 `query_rewrite_integrity` 为 `stale_tolerated` 才可以使用非实时物化视图进行改写。 ``` 6. 使用 `MV_REWRITE` Hint 用物化视图尝试进行改写，并跳过改写代价/规则检查。下面两条查询都将使用物化视图 `mv1_tbl2` 进行改写。 - `/*+mv_rewrite*/` 将尝试使用符合改写条件的物化视图进行改写，一旦找到了符合改写要求的物化视图，后续的物化视图将不再被考虑，并跳过改写代价/规则检查。 ```sql EXPLAIN SELECT /*+mv_rewrite*/ count(*), col1 FROM tbl2 WHERE tbl2.col1 > 1 GROUP BY col1; ``` 返回结果如下： ```shell +------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------+ | ===================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ----------------------------------------------------- | | |0 |HASH GROUP BY | |1 |3 | | | |1 |└─TABLE FULL SCAN|mv1_tbl2|1 |3 | | | ===================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([T_FUN_COUNT(*)], [mv1_tbl2.col1]), filter(nil), rowset=16 | | group([mv1_tbl2.col1]), agg_func([T_FUN_COUNT(*)]) | | 1 - output([mv1_tbl2.col1]), filter([mv1_tbl2.col1 > 1]), rowset=16 | | access([mv1_tbl2.col1]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([mv1_tbl2.__pk_increment]), range(MIN ; MAX)always true | +------------------------------------------------------------------------------------+ 14 rows in set ``` - `/*+mv_rewrite(mv2_tbl2)*/` 将尝试使用 `mv2_tbl2` 进行改写，并跳过改写代价/规则检查。 ```sql EXPLAIN SELECT /*+mv_rewrite(mv2_tbl2)*/ count(*), col1 FROM tbl2 WHERE tbl2.col1 > 1 GROUP BY col1; ``` 返回结果如下： ```shell +-------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------+ | ===================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ----------------------------------------------------- | | |0 |HASH GROUP BY | |1 |3 | | | |1 |└─TABLE FULL SCAN|mv2_tbl2|1 |3 | | | ===================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([T_FUN_COUNT(*)], [mv2_tbl2.col1]), filter(nil), rowset=16 | | group([mv2_tbl2.col1]), agg_func([T_FUN_COUNT(*)]) | | 1 - output([mv2_tbl2.col1]), filter(nil), rowset=16 | | access([mv2_tbl2.col1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([mv2_tbl2.__pk_increment]), range(MIN ; MAX)always true | +-------------------------------------------------------------------------+ 14 rows in set ``` 7. 虽然查询指定使用 `mv2_tbl2` 来查询改写，由于查询语句的 `WHERE` 条件不满足要求，`mv2_tbl2` 无法用于查询改写，所以此查询不会进行物化视图查询改写。 ```sql EXPLAIN SELECT /*+mv_rewrite(mv2_tbl2)*/ count(*), col1 FROM tbl2 WHERE tbl2.col1 < 1 GROUP BY col1; ``` 返回结果如下： ```shell +------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------+ | ================================================= | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ------------------------------------------------- | | |0 |HASH GROUP BY | |1 |3 | | | |1 |└─TABLE FULL SCAN|tbl2|1 |3 | | | ================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([T_FUN_COUNT(*)], [tbl2.col1]), filter(nil), rowset=16 | | group([tbl2.col1]), agg_func([T_FUN_COUNT(*)]) | | 1 - output([tbl2.col1]), filter([tbl2.col1 < 1]), rowset=16 | | access([tbl2.col1]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([tbl2.__pk_increment]), range(MIN ; MAX)always true | +------------------------------------------------------------------------------------+ 14 rows in set ``` 8. 使用 `/*+ no_mv_rewrite*/` Hint，不会进行物化视图查询改写。 ```sql EXPLAIN SELECT /*+no_mv_rewrite*/ count(*), col1 FROM tbl2 WHERE tbl2.col1 > 1 GROUP BY col1; ``` 返回结果如下： ```shell +------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------+ | ================================================= | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ------------------------------------------------- | | |0 |HASH GROUP BY | |1 |3 | | | |1 |└─TABLE FULL SCAN|tbl2|1 |3 | | | ================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([T_FUN_COUNT(*)], [tbl2.col1]), filter(nil), rowset=16 | | group([tbl2.col1]), agg_func([T_FUN_COUNT(*)]) | | 1 - output([tbl2.col1]), filter([tbl2.col1 > 1]), rowset=16 | | access([tbl2.col1]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([tbl2.__pk_increment]), range(MIN ; MAX)always true | +------------------------------------------------------------------------------------+ 14 rows in set ``` ## 物化视图日志 ### 定义 物化视图日志（Materialized View Log）是一种特殊的数据库对象，用于记录在基表（base table）上发生的数据更改。它是实现物化视图（Materialized View）增量刷新机制的关键组件。物化视图日志是关联到基表，用于捕获和存储基表上每次数据修改（插入、更新、删除）的详细信息。这些日志使得物化视图在刷新时无需重新扫描整个基表，而是只需应用自上次刷新以来记录在日志中的更改，从而提高刷新效率。 ### 创建物化视图日志 以下为创建物化视图日志的语法。 ``` CREATE MATERIALIZED VIEW LOG ON [ schema. ] table [ parallel_clause ] [ WITH [ { PRIMARY KEY | ROWID | SEQUENCE } [ { , PRIMARY KEY | , ROWID | , SEQUENCE } ]... ] (column [, column ]...) [ new_values_clause ] ] [ mv_log_purge_clause ] ; ``` 以下为具体的语义解释。 - schema：物化视图日志基表所在的 schema（数据库），不指定就在当前的 schema 中。 - table：物化视图日志对应的基表名称。 - parallel_clause：创建物化视图表的 dop - with_clause：指定物化视图日志中包含的辅助列，primary key 表示基表的所有主键列，rowid 表示基表为无主键表时的隐藏主键列，sequence 表示一个事务内的多行更新序号号（seq_no）。规则如下： - with primary key 只能用在有主键表中，在无主键表中使用不会有任何效果，如果不指定，则系统会自动带上。 - with rowid 只能用在无主键表中，在有主键表中使用不会有任何效果，如果不指定，则系统会自动带上。 - with sequence 会自动加上。 - new_values_clause：是否在物化视图日志中同时记录 update 操作中的旧值和新值。 如果希望 MV 支持快速刷新，则必须指定 INCLUDING NEW VALUES。 ``` { INCLUDING | EXCLUDING } NEW VALUES ``` 规则： - including new values 会自动加上 - excluding new values 会报错 - mv_log_purge_clause：指定物化视图日志中数据的清除时间。 ``` PURGE { IMMEDIATE [ SYNCHRONOUS | ASYNCHRONOUS ] | START WITH datetime_expr [ NEXT datetime_expr ] | [ START WITH datetime_expr ] NEXT datetime_expr } ``` 子选项说明： - IMMEDIATE 表示在每次刷新完物化视图后就立即清除相应的物化视图日志，其中 SYNCHRONOUS 表示同步地执行清除，而 ASYNCHRONOUS 则表示异步地执行清除，但是目前不支持异步清除，用户设置该异步选项会报错。 - START WITH datetime_expr 表示第一次清除物化视图日志的时间，NEXT datetime_expr 则是下一次清除的时间，这两个时间必须是在未来，否则会报错。 - 如果 START WITH datetime_expr 参数没有指定，而只指定了 NEXT datetime_expr，那么第一次清除物化视图日志的时间将被设置为 NEXT datetime_expr 参数值。 示例：“在表 t1 上面创建一个物化视图日志，从创建的时刻开始，每天清除一次”的语句如下。 ```sql --mysql模式 create materialized view log on t1 purge start with sysdate() next sysdate() + interval 1 day; --oracle模式（oracle模式中建议使用current_date表示当前时区时间，因为sysdate不受时区影响） create materialized view log on t1 purge start with current_date next current_date + 1; ``` #### 说明 mlog 暂时不支持指定 partition，现在是类似 local index 的实现，mlog 的 partition 和基表的 partition 是绑定关系。 ### 删除物化视图日志 以下为删除物化视图日志的语法。 表示删除某个基表对应的物化视图日志。 ``` DROP MATERIALIZED VIEW LOG ON [ schema. ] table; ``` 删除物化视图日志时，如果基表正处于某个运行的事务中，则直到该事务结束前，删除操作都会阻塞。 ### 物化视图日志的局限性 虽然物化视图日志在数据库系统中具有重要的作用，特别是在实现增量刷新方面，但它们也存在一些局限性和缺点： - **占用额外空间**： 物化视图日志需要在数据库中存储基表的更改记录，这会占用额外的磁盘空间。特别是在数据更新频繁的场景下，日志表可能会迅速增长，导致大量的磁盘空间被占用。 - **性能影响**： 每次对基表的插入、更新或删除操作，都会在物化视图日志中记录相应的更改。这意味着每次写操作都需要额外的开销，可能会影响基表的写入性能。 - **长期不刷新带来的空间占用**： 如果物化视图长时间不刷新，物化视图日志中的未处理记录会不断积累，占用大量磁盘空间。这样的累积不仅会影响存储资源，还可能影响数据库的整体性能。 - **管理复杂性**： 物化视图日志需要定期维护和管理，例如确定适当的刷新间隔、监控日志大小以及清理过期日志记录等。这增加了数据库管理员的工作负担和管理复杂性。 - **恢复和备份挑战**：目前物化视图日志不支持备份恢复。 ## 相关文档 - 有关物化视图的详细介绍和使用指导，参见 [物化视图概述（MySQL 模式）](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001053755) 和 [物化视图概述（Oracle 模式）](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001053745)。 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线