作者：ShunWah
在运维管理领域，我拥有多年深厚的专业积累，兼具坚实的理论基础与广泛的实践经验。我始终站在技术前沿，致力于推动运维自动化，不懈追求运维效率的最大化。
我精通运维自动化流程，对于OceanBase、MySQL等多种数据库的部署与运维，具备从初始部署到后期维护的全链条管理能力。凭借OceanBase的OBCA和OBCP认证、OpenGauss社区认证结业证书，以及崖山DBCA、亚信AntDBCA、翰高HDCA、GBase 8a | 8c | 8s、Galaxybase GBCA、Neo4j Graph Data Science Certification、NebulaGraph NGCI等多项权威认证，我不仅展现了自己的专业技能，也彰显了对技术的深厚热情与执着追求。
在OceanBase & 墨天轮的技术征文大赛中，我凭借卓越的技术实力和独特的见解，多次荣获一、二、三等奖。同时，在OpenGauss第五届、第六届、第七届技术征文大赛，TiDB社区第三届专栏征文大赛，金仓数据库有奖征文活动，以及首批YashanDB「产品体验官」尝鲜征文等活动中，我也屡获殊荣。此外，我还活跃于墨天轮、CSDN等技术平台，经常发布原创技术文章，并多次被首页推荐，积极与业界同仁分享我的运维经验和独到见解。
前言
OceanBase 4.3系列：物化视图技术的革新与实践
在当今数据驱动的时代，数据库系统作为存储和管理数据的核心组件，扮演着至关重要的角色。OceanBase 作为蚂蚁集团自主研发的分布式关系数据库，凭借其卓越的性能和持续的技术创新，在金融、电信、政府等多个领域赢得了广泛的认可。在OceanBase 4.3系列版本中，物化视图技术的革新更是让我印象深刻，它不仅提升了查询性能，还降低了业务手工改写的成本，为企业的数据分析提供了强有力的支持。
OceanBase的分布式架构是其核心竞争力的基础。通过水平扩展，OceanBase能够轻松应对海量数据和高并发请求，这在传统数据库中是难以想象的。每个数据分片都有多个副本，分布在不同的物理节点上，保证了数据的高可用性和一致性。这种架构不仅提高了系统的灵活性，还大大降低了硬件成本。
在最新版本中，OceanBase推出的物化视图功能，无疑为数据分析和查询优化提供了强有力的支持。物化视图是一种特殊的视图，它存储了视图定义中查询执行的结果。通过保存某些耗时操作的结果，物化视图能够在查询时直接查询已经预计算好的数据，避免重复执行这些耗时耗资源的操作。这种预先计算的方式大大提升了查询性能，对于需要频繁检索特定信息并伴随复杂计算的数据分析师来说，让我尤为惊喜。
测试环境说明
在本次实际测试中，我使用了OceanBase推出的OB Cloud云数据库全年365天免费试用版作为测试环境。参考：布道师计划」OceanBase OB Cloud 云数据库：免费试用实战指南 https://www.modb.pro/db/1856143094144577536 申请流程简单便捷，只需注册/登录OceanBase并填写申请信息，即可选择配置自助开通。OB Cloud云数据库提供了多种类型的实例试用，包括共享实例和集群实例，兼容Oracle与MySQL，满足了不同用户的需求。在测试过程中，我深刻感受到了OceanBase的高性能、高可用性以及其与主流数据库的高兼容性。无论是复杂的SQL查询、数据汇总还是实时分析，OceanBase都能够轻松应对，提供了卓越的数据处理能力。
一、物化视图：从概念到实践
物化视图，简而言之，就是将视图查询的结果以物理表的形式存储在数据库中。与普通的虚拟视图不同，物化视图存储了实际的数据，因此可以在查询时直接访问这些数据，而无需每次都执行视图定义中的查询操作。这一特性使得物化视图在提升查询性能、降低数据库负载方面具有明显的优势。
物化视图被用来优化查询性能，尤其是在处理大量数据和复杂查询时。物化视图可以包含聚合，连接和子查询等操作，并且可以被索引和分区，以进一步提高性能。
在OceanBase 4.3系列版本中，物化视图技术得到了进一步的完善和创新。特别是从V4.3.1版本开始，OceanBase支持了物化视图改写能力。这一功能的引入，极大地减少了业务手工改写的成本，提高了数据查询的效率和准确性。通过系统变量QUERY_REWRITE_ENABLED的设置，我们可以在创建物化视图时指定enable query rewrite，从而启用自动改写能力。
在测试操作中，我使用OceanBase的物化视图功能，对数据进行了汇总和分析。通过创建物化视图，我能够快速地获取销售数据，而无需每次都执行复杂的查询语句。这不仅提高了工作效率，还保证了数据的准确性和一致性。
1、创建销售数据表
CREATE TABLE sales (
sale_id INT PRIMARY KEY AUTO_INCREMENT,
sale_date DATETIME NOT NULL,
product_id INT NOT NULL,
sale_amount DECIMAL(10, 2) NOT NULL
);
2、插入示例数据
INSERT INTO sales (sale_date, product_id, sale_amount) VALUES
('2023-10-01 10:00:00', 1, 100.00),
('2023-10-01 12:30:00', 2, 150.75),
('2023-10-02 09:15:00', 1, 200.00),
('2023-10-02 14:45:00', 3, 250.50),
('2023-10-03 11:00:00', 2, 120.25),
('2023-10-03 17:30:00', 1, 80.00),
('2023-10-04 08:30:00', 3, 130.75),
('2023-10-04 16:00:00', 2, 90.00),
('2023-10-05 13:15:00', 1, 110.50),
('2023-10-05 19:45:00', 3, 160.00);
请根据您的实际需求调整表结构和数据。
3、创建物化视图
一旦源表和数据准备就绪，您就可以创建物化视图了。以下是一个创建物化视图的例子，该视图按天和产品ID对销售数据进行汇总，并计算总销售额：
创建物化视图
CREATE MATERIALIZED VIEW sales_summary_mv AS
SELECT DATE(sale_date) AS sale_day, -- 使用DATE函数提取日期部分（如果date_trunc不可用）
SUM(sale_amount) AS total_sales
FROM sales
GROUP BY DATE(sale_date); -- 使用DATE函数提取日期部分进行分组
请注意，我使用了DATE(sale_date)来提取日期部分，这样我们就可以按天进行汇总。如果您的sale_date列已经是日期类型（而不是日期时间类型），则不需要使用DATE()函数。
注意事项
权限：确保您有足够的权限来创建表和物化视图。
存储：物化视图会占用额外的存储空间，因为它们是存储查询结果的物理表。
刷新：物化视图的数据不会自动更新以反映源表中的更改。您可能需要定期刷新物化视图以确保其数据的准确性。OceanBase可能提供了自动刷新物化视图的选项，或者您可能需要手动执行刷新操作。
性能：物化视图可以显著提高查询性能，特别是对于那些需要频繁执行且计算成本高的查询。
验证物化视图
最后，您可以通过查询物化视图来验证其是否按预期工作：
4、查询物化视图以验证结果
SELECT * FROM sales_summary_mv;
这将显示物化视图中的所有数据，您可以检查数据是否正确汇总并按预期显示。
5、物化视图的刷新：
在实际操作中，我体验到了OceanBase物化视图功能的强大。通过简单的命令行，我可以轻松地创建、刷新和删除物化视图。其中，refresh_clause可以指定物化视图的刷新策略，包括全量刷新（COMPLETE）、快速刷新（FAST，即增量刷新）、强制刷新（FORCE）等。这种灵活的刷新策略使得物化视图能够根据不同的应用场景和需求，保持数据的时效性和准确性。
5.1 全量刷新
使用 REFRESH COMPLETE 子句设置物化视图的刷新方法为全量刷新。
创建名为 mv1_tbl1 的物化视图。指定物化视图的刷新方法为全量刷新，并且可以根据需要手动触发刷新操作；指定从 tbl1 表中选择满足 age 大于等于 20 的 id 和 name 列作为物化视图的数据源。
CREATE MATERIALIZED VIEW mv1_tbl1(id, name)
REFRESH COMPLETE ON DEMAND
AS SELECT id, name
FROM tbl1
WHERE age >= 20;
5.2 增量刷新
使用 REFRESH FAST 子句设置物化视图的刷新方法为增量刷新。
在 tbl3 表上创建物化视图日志。指定物化视图日志的选项为 SEQUENCE，用于指示使用序列号来标识变化的数据，列部分指定了要记录的列，其中包括了 col2 和 col3。
CREATE MATERIALIZED VIEW LOG ON tbl3 WITH SEQUENCE (col2, col3) INCLUDING NEW VALUES;
创建名为 mv_tbl3 的物化视图。指定物化视图的刷新方法为增量刷新，并且可以根据需要手动触发刷新操作；在查询部分，指定了从 tbl3 表中按照 col2 列进行分组，并计算每个分组中的记录数（cnt）、非空 col3 列的记录数（cnt_col3）和 col3 列的总和（sum_col3）作为物化视图的结果。
CREATE MATERIALIZED VIEW mv_tbl3
REFRESH FAST ON DEMAND
AS SELECT col2, COUNT(*) cnt, COUNT(col3) cnt_col3, SUM(col3) sum_col3
FROM tbl3
GROUP BY col2;
二、启用物化视图查询改写
通过使用物化视图来加速查询，当输入一个不使用物化视图的查询时，系统会自动将查询重写为使用现有物化视图的查询。该方法的原理是将查询语句与物化视图的定义进行匹配，如果发现匹配的物化视图，则自动将查询重写为使用物化视图的查询，这样可以大大提高查询性能和效率。
1、查看物化视图改写状态
控制物化视图查询改写的系统变量如下：
创建物化视图后，可以通过视图 DBA_MVIEWS 查看物化视图是否开启自动改写。
SELECT MVIEW_NAME, REWRITE_ENABLED FROM oceanbase.DBA_MVIEWS WHERE MVIEW_NAME = 'mv1_tbl1';
返回结果如下：REWRITE_ENABLED 为 N 未开启自动改写功能
query_rewrite_enabled：用于是否开启物化视图改写功能。该变量的详细介绍信息，参见 query_rewrite_enabled。
SET query_rewrite_enabled = 'force';
query_rewrite_integrity：用于指定物化视图改写的数据一致性检查级别。该变量的详细介绍信息，参见 query_rewrite_integrity。
示例如下：
SET query_rewrite_integrity = 'stale_tolerated';
2、通过以下示例展示物化视图改写：
2.1 创建表 test_tbl1。
CREATE TABLE test_tbl1 (col1 INT, col2 INT, col3 INT);
2.2 创建表 test_tbl2。
CREATE TABLE test_tbl2 (col1 INT, col2 INT, col3 INT);
2.3 创建名为 mv_test_tbl1_tbl2 的物化视图，并开启当前物化视图的自动改写。
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;
2.4 创建物化视图后，可以通过视图 DBA_MVIEWS 查看物化视图是否开启自动改写。
SELECT MVIEW_NAME, REWRITE_ENABLED FROM oceanbase.DBA_MVIEWS WHERE MVIEW_NAME = 'mv_test_tbl1_tbl2';
返回结果如下：REWRITE_ENABLED 为 Y 已经开启自动改写功能
3、 使用视图查看物化视图信息
可以通过系统视图来查看物化视图的相关信息，可查看的视图有：
视图名 功能描述
DBA_MVIEWS 展示物化视图信息。
DBA_MVREF_STATS_SYS_DEFAULTS 物化视图刷新历史统计属性的系统范围默认值。
DBA_MVREF_STATS_PARAMS 展示与每个物化视图关联的刷新统计信息属性。
DBA_MVREF_RUN_STATS 展示物化视图的每次刷新运行的信息，每次运行均由 REFRESH_ID 标识。
DBA_MVREF_STATS 展示物化视图刷新的基本计时统计信息。
DBA_MVREF_CHANGE_STATS 展示物化视图刷新相关的统计信息。
DBA_MVREF_STMT_STATS 展示刷新语句关联的信息。
DBA_MVIEWS 展示物化视图信息。
SELECT owner, mview_name, container_name, query, query_len FROM oceanbase.DBA_MVIEWS;
三、快速实操使用物化视图
通过学习物化视图后，下面我将快速介绍如何在OceanBase中创建一个带有示例数据的订单表 orders11，并在此基础上创建和使用物化视图。
1、创建订单表
首先，我们需要创建一个订单表 orders11。假设该表包含以下字段：
order_id：订单ID，主键
customer_id：客户ID
store_id：商场ID
product_id：商品ID
order_date：订单日期
order_amount：订单金额
payment_method：支付方式
status：订单状态
CREATE TABLE orders11 (
order_id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
store_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
order_date DATE NOT NULL,
order_amount DECIMAL(10, 2) NOT NULL,
payment_method VARCHAR(50) NOT NULL,
status VARCHAR(50) NOT NULL
);
2、插入示例数据
接下来，我们插入一些示例数据。为了简化，这里只插入一部分数据，你可以根据需要扩展。
INSERT INTO orders11 (order_id, customer_id, store_id, product_id, order_date, order_amount, payment_method, status) VALUES
(1, 101, 1, 1001, '2023-01-01', 150.00, 'Credit Card', 'Completed'),
(2, 102, 2, 1002, '2023-01-02', 200.00, 'PayPal', 'Completed'),
(3, 103, 3, 1003, '2023-01-03', 180.00, 'Bank Transfer', 'Pending'),
(4, 101, 4, 1004, '2023-01-04', 250.00, 'Credit Card', 'Completed'),
(5, 102, 5, 1005, '2023-01-05', 100.00, 'PayPal', 'Completed'),
(6, 103, 6, 1006, '2023-01-06', 120.00, 'Bank Transfer', 'Completed'),
(7, 104, 7, 1007, '2023-01-07', 280.00, 'Credit Card', 'Completed'),
(8, 105, 8, 1008, '2023-01-08', 300.00, 'PayPal', 'Completed'),
(9, 106, 9, 1009, '2023-01-09', 220.00, 'Bank Transfer', 'Completed'),
(10, 107, 10, 1010, '2023-01-10', 190.00, 'Credit Card', 'Completed'),
(11, 108, 11, 1011, '2023-01-11', 160.00, 'PayPal', 'Completed'),
(12, 109, 12, 1012, '2023-01-12', 210.00, 'Bank Transfer', 'Completed'),
(13, 110, 13, 1013, '2023-01-13', 190.00, 'Credit Card', 'Completed'),
(14, 111, 14, 1014, '2023-01-14', 170.00, 'PayPal', 'Completed'),
(15, 112, 15, 1015, '2023-01-15', 230.00, 'Bank Transfer', 'Completed'),
(16, 113, 16, 1016, '2023-01-16', 140.00, 'Credit Card', 'Completed'),
(17, 114, 17, 1017, '2023-01-17', 240.00, 'PayPal', 'Completed'),
(18, 115, 18, 1018, '2023-01-18', 180.00, 'Bank Transfer', 'Completed'),
(19, 116, 19, 1019, '2023-01-19', 260.00, 'Credit Card', 'Completed'),
(20, 117, 20, 1020, '2023-01-20', 150.00, 'PayPal', 'Completed'),
(21, 118, 21, 1021, '2023-01-21', 270.00, 'Bank Transfer', 'Completed'),
(22, 119, 22, 1022, '2023-01-22', 160.00, 'Credit Card', 'Completed'),
(23, 120, 23, 1023, '2023-01-23', 280.00, 'PayPal', 'Completed'),
(24, 121, 24, 1024, '2023-01-24', 190.00, 'Bank Transfer', 'Completed'),
(25, 122, 25, 1025, '2023-01-25', 200.00, 'Credit Card', 'Completed'),
(26, 123, 26, 1026, '2023-01-26', 210.00, 'PayPal', 'Completed'),
(27, 124, 27, 1027, '2023-01-27', 220.00, 'Bank Transfer', 'Completed'),
(28, 125, 28, 1028, '2023-01-28', 230.00, 'Credit Card', 'Completed'),
(29, 126, 29, 1029, '2023-01-29', 240.00, 'PayPal', 'Completed'),
(30, 127, 30, 1030, '2023-01-30', 250.00, 'Bank Transfer', 'Completed');
3、查询 orders11 表的数据
为了验证数据是否正确插入，我们可以执行以下查询语句：
SELECT * FROM orders01;
4、创建物化视图
假设我们要创建一个物化视图 category_sales，用于统计每个商品类别的总销售额。以下是创建物化视图的 SQL 语句：
CREATE MATERIALIZED VIEW monthly_sales_mv
AS
SELECT
EXTRACT(YEAR FROM order_date) AS year,
EXTRACT(MONTH FROM order_date) AS month,
SUM(order_amount) AS total_sales
FROM orders11
GROUP BY
EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date);
为了确保物化视图的数据是最新的，可以定期刷新物化视图。以下是一些刷新物化视图的命令：
5、手动刷新
CALL DBMS_MVIEW.REFRESH('monthly_sales_mv');
6、查询物化视图
我们可以查询物化视图 category_sales 来获取每个商品类别的总销售额：
SELECT * FROM monthly_sales_mv;
7、删除物化视图 如果不再需要某个物化视图，可以将其删除。
DROP MATERIALIZED VIEW monthly_sales_mv;
通过以上步骤，我们成功创建了一个订单表 orders01，插入了一些示例数据，并创建了一个物化视图 monthly_sales_mv用于统计每个商品类别的总销售额。希望这些示例能帮助你更好地理解和使用 OceanBase 的相关功能。
结语：
OceanBase还支持增量刷新物化视图，即只针对自上次刷新以来基表数据发生变动的部分进行刷新。这进一步提高了数据更新的效率，并减少了不必要的资源消耗。
除了物化视图功能外，OceanBase还支持在线扩展和升级、智能容灾策略、高效的事务处理和数据一致性保证等特性。这些特性共同构成了OceanBase强大的技术体系，使其能够在保证数据一致性的前提下，实现高效的数据处理，满足各类高性能应用的需求。
在我看来，OceanBase的成功不仅仅在于其卓越的技术特性，更在于其广泛的应用场景和深厚的行业积累。从支付宝的交易处理系统到金融行业的风控系统，从电信行业的计费系统到政府部门的决策支持系统，OceanBase都能够提供高效、可靠的数据库解决方案，满足各类复杂业务需求。
综上所述，OceanBase凭借其高性能、高可用性和高扩展性，在数据库领域树立了新的标杆。我相信，在未来的数字化转型中，OceanBase将继续发挥其技术优势，为更多行业和企业提供优质的数据库服务。让我们共同期待OceanBase在未来的发展中创造更多的辉煌！
