StarRocks 物化视图最佳实践

文档：https://docs.starrocks.io/zh/docs/using_starrocks/async_mv/Materialized_view/

物化视图管理

不同版本对物化视图支持的特性不一样，查看当前StarRocks版本。

select current_version();

创建异步自动刷新物化视图

CREATE MATERIALIZED VIEW order_mv
DISTRIBUTED BY HASH(`order_id`)
REFRESH ASYNC START('2022-09-01 10:00:00') EVERY (interval 1 day)
AS SELECT
    order_list.order_id,
    sum(goods.price) as total
FROM order_list INNER JOIN goods ON goods.item_id1 = order_list.item_id2
GROUP BY order_id;

注意，REFRESH ASYNC后必须要跟刷新的间隔时间，且间隔时间最少60s或1分钟。

物化视图的刷新方式。该参数支持如下值：

• ASYNC: 自动刷新模式。每当基表数据发生变化时，物化视图会自动刷新。
• ASYNC [START (<start_time>)] EVERY(INTERVAL <interval>): 定时刷新模式。物化视图将按照定义的间隔定时刷新。您可以使用 DAY（天）、HOUR（小时）、MINUTE（分钟）和 SECOND（秒）作为单位指定间隔，格式为 EVERY (interval n day/hour/minute/second)。默认值为 10 MINUTE（10 分钟）。您还可以进一步指定刷新起始时间，格式为 START('yyyy-MM-dd hh:mm:ss')。如未指定起始时间，默认使用当前时间。示例：ASYNC START ('2023-09-12 16:30:25') EVERY (INTERVAL 5 MINUTE)。
• MANUAL: 手动刷新模式。除非手动触发刷新任务，否则物化视图不会刷新。

如果不指定该参数，则默认使用 MANUAL 方式。

参考：https://docs.starrocks.io/zh/docs/sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW/

注意事项

• 当前 StarRocks 不对嵌套层数进行限制。生产环境中建议嵌套层数不超过三层。
• 关于外部数据目录异步物化视图：
  • 外部数据目录物化视图仅支持异步定时刷新和手动刷新。
  • 物化视图中的数据不保证与外部数据目录的数据强一致。
  • 目前暂不支持基于资源（Resource）构建物化视图。
  • StarRocks 目前无法感知外部数据目录基表数据是否发生变动，所以每次刷新会默认刷新所有分区。您可以通过手动刷新方式指定刷新部分分区。

基于MySQL创建物化视图

2.5版本不支持基于MySQL外表、Resource、JDBC Catalog创建物化视图，分别报错如下，其中基于Resource报错是账户权限不够，如果权限够的话，报错应该和v3.3版本报错一致。

ERROR 1064 (HY000): Create/Rebuild materialized view do not support the table type: MYSQL

ERROR 1064 (HY000): Access denied; you need (at least one of) the ADMIN privilege(s) for this operation

ERROR 1064 (HY000): Create/Rebuild materialized view do not support the table type: JDBC

3.3版本基于MySQL外表创建的物化视图，触发刷新物化视图报错：

ERROR 1064 (HY000): execute task mv-364074 failed: Refresh materialized view mysql_external_table_role_mv failed after retrying 1 times(try-lock 0 times), error-msg : java.lang.NullPointerException: traits not supported: MYSQL
        at com.google.common.base.Preconditions.checkNotNull(Preconditions.java:921)
        at com.starrocks.connector.ConnectorPartitionTraits.build(ConnectorPartitionTraits.java:93)
        at com.starrocks.connector.ConnectorPartitionTraits.buildWithoutCache(ConnectorPartitionTraits.java:125)
        at com.starrocks.connector

3.3版本基于MySQL Resource创建外表时报错，源表的所有字段不能为非空。

ERROR 1064 (HY000): Getting analyzing error. Detail message: All columns must be nullable for external table. Column role is not nullable, You can rebuild the external table and We strongly recommend that you use catalog to access external data.

查看异步物化视图

注意，2.5版本的命令中VIEWS应该 替换为VIEW。

SHOW MATERIALIZED VIEWS;

SHOW MATERIALIZED VIEWS WHERE NAME = "order_mv";

SHOW MATERIALIZED VIEWS WHERE NAME LIKE "order%";

SELECT * FROM information_schema.materialized_views;

-- 查看物化视图创建语句
SHOW CREATE MATERIALIZED VIEW order_mv;

修改异步物化视图

-- 启用被禁用的异步物化视图
ALTER MATERIALIZED VIEW order_mv ACTIVE;

-- 修改异步物化视图名称
ALTER MATERIALIZED VIEW order_mv RENAME order_total;

-- 修改异步物化视图的最大刷新间隔
ALTER MATERIALIZED VIEW order_mv REFRESH ASYNC EVERY(INTERVAL 2 DAY);

删除异步物化视图

DROP MATERIALIZED VIEW order_mv;

手动刷新异步物化视图

-- 异步调用刷新任务。
REFRESH MATERIALIZED VIEW order_mv;

-- 同步调用刷新任务。
REFRESH MATERIALIZED VIEW order_mv WITH SYNC MODE;

-- 指定分区同步调用刷新任务
REFRESH MATERIALIZED VIEW order_mv 
PARTITION START ("2024-08-21") END ("2024-08-22")
WITH SYNC MODE;

延伸

分区定义

在数据库中，分区是一种数据组织方式，它可以帮助管理大量数据，优化查询性能，简化数据维护。根据分区键的不同类型和数据的组织方式，分区可以分为几种不同的类型，如普通分区、列表分区和范围分区。

1. PARTITION BY (statis_hour)

这种表述可能指的是按照某个字段（如 statis_hour）进行的普通分区，但具体的分区类型（如列表、范围等）没有明确指出。通常，这种情况下我们需要更多的上下文来确定是哪种类型的分区。如果仅仅是 PARTITION BY (statis_hour)，这可能是一个简单的散列分区，其中数据根据 statis_hour 的散列值被均匀分布到不同的分区中。

适用场景：

• 当你需要均匀分散数据以平衡负载时，散列分区非常有用。
• 适用于数据量大且访问模式随机的情况。

2. PARTITION BY List (statis_hour)

列表分区允许你根据列值的离散集合来定义分区。每个分区可以明确指定包含哪些特定的值。

适用场景：

• 当列值有明确的分类，且查询经常基于这些分类进行时，列表分区非常有效。
• 例如，如果 statis_hour 代表一天中的小时（0-23），你可以根据业务需求将工作时间和非工作时间分开存储，如将9-17小时的数据存储在一个分区，其余时间的数据存储在另一个分区。

CREATE TABLE logs (
    log_id INT,
    statis_hour INT,
    log_data TEXT
) PARTITION BY LIST (statis_hour) (
    PARTITION work_hours VALUES IN (9, 10, 11, 12, 13, 14, 15, 16, 17),
    PARTITION off_hours VALUES IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 18, 19, 20, 21, 22, 23)
);

3. PARTITION BY Range (statis_hour)

范围分区允许你根据列值的范围来定义分区。每个分区包含一个值范围。

适用场景：

• 当列值是连续的，并且经常需要基于值的范围进行查询时，范围分区非常有用。
• 例如，如果 statis_hour 代表一天中的小时，你可以创建每6小时一个分区的表，这样可以快速访问特定时间段的数据。

CREATE TABLE logs (
    log_id INT,
    statis_hour INT,
    log_data TEXT
) PARTITION BY RANGE (statis_hour) (
    PARTITION morning VALUES LESS THAN (6),
    PARTITION day VALUES LESS THAN (12),
    PARTITION evening VALUES LESS THAN (18),
    PARTITION night VALUES LESS THAN (24)
);

分区刷新

在刷新特定分区的物化视图时，是否会查询基表的对应分区，以及这两种分区是否一一对应，取决于物化视图的设计和数据库系统的实现。通常，物化视图的分区设计旨在映射或对应基表的分区结构，以便优化查询和刷新操作。但具体行为可以根据数据库的具体实现有所不同。

物化视图与基表分区的对应关系

1. 一一对应的分区设计：
   1. 在理想情况下，物化视图的分区会与基表的分区设计一一对应。这意味着每个物化视图分区直接对应基表中的一个或多个分区。这种设计允许在刷新物化视图时，只针对那些已经发生变化的基表分区进行操作，从而提高效率。
   2. 例如，如果基表按日期分区，物化视图也按相同的日期范围分区，那么刷新物化视图的某个分区时，系统只需要查询基表中对应日期的分区。
2. 非一一对应的分区设计：
   1. 在某些情况下，物化视图的分区可能不完全对应基表的分区。这可能是因为物化视图的设计考虑了不同的查询优化需求或存储优化需求。
   2. 在这种情况下，刷新物化视图的一个分区可能需要访问基表的多个分区，或者一个基表分区的数据可能分布在多个物化视图分区中。

刷新操作的实现

• 查询基表的对应分区：
  • 当刷新物化视图的特定分区时，数据库系统通常会尝试只查询那些与物化视图分区相关的基表分区。这是为了优化刷新操作的性能，减少不必要的数据扫描和处理。
  • 如果物化视图和基表的分区是一一对应的，这个过程相对直接和高效。如果不是一一对应，系统可能需要执行更复杂的查询来确定需要刷新的数据。

创建明细表的参数

• 排序键：自 v3.3.0 起，明细表支持使用 ORDER BY 指定排序键，可以是任意列的排列组合。如果同时使用 ORDER BY 和 DUPLICATE KEY，则 DUPLICATE KEY 无效。如果未使用 ORDER BY 和 DUPLICATE KEY，则默认选择表的前三列作为排序键。
• 分桶：
  • 分桶方式：自 v3.1.0 起，StarRocks 支持明细表进行随机分桶（默认分桶方式）。您在建表和新增分区时可以不设置哈希分桶键（即 DISTRIBUTED BY HASH 子句）。在 v3.1.0 之前，StarRocks 仅支持哈希分桶。您在建表和新增分区时必须设置哈希分桶键（即 DISTRIBUTED BY HASH 子句），否则建表失败。哈希分桶键的更多说明，请参见哈希分桶。
  • 分桶数量：自 v2.5.7 起，StarRocks 支持在建表和新增分区时自动设置分桶数量 (BUCKETS)，您无需手动设置分桶数量。更多信息，请参见设置分桶数量。
• 建表时，支持为所有列创建 Bitmap 索引、Bloom Filter 索引。