KingBaseES 物化视图

物化视图概述

物化视图是一种特殊的数据库对象，它将查询结果存储为物理表，定期或手动刷新，以提供快速的数据访问。与普通视图相比，物化视图可以显著提高复杂查询的性能，特别是在大数据量和复杂查询场景下。

物化视图与普通视图的区别

特性	普通视图	物化视图
存储方式	只存储查询定义，不存储数据	存储查询结果数据
查询性能	查询时动态执行，性能取决于查询复杂度	直接读取预计算结果，性能高
数据新鲜度	总是返回最新数据	数据可能过时，需要定期刷新
存储空间	几乎不占用存储空间	占用存储空间存储数据
维护成本	无维护成本	需要定期刷新，有维护成本
适用场景	简单查询，需要最新数据	复杂查询，数据更新不频繁

物化视图的适用场景

复杂查询优化：对复杂的查询（如多表关联、聚合计算、分析函数等）创建物化视图，提高查询性能。
数据仓库和数据分析：在数据仓库环境中，为常用的分析查询创建物化视图，加速报表生成。
只读数据访问：对只读或读写比较低的数据创建物化视图，提高读取性能。
跨数据库查询：对跨数据库的查询创建物化视图，减少跨数据库访问的开销。
历史数据查询：对历史数据创建物化视图，加速历史数据的查询。

物化视图的创建与管理

创建物化视图

基本语法

sql

CREATE MATERIALIZED VIEW [IF NOT EXISTS] view_name
    [ (column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    AS query
    [ WITH [ NO ] DATA ];

创建示例

sql

-- 创建基本物化视图
CREATE MATERIALIZED VIEW mv_sales_summary AS
SELECT 
    product_id,
    SUM(quantity) AS total_quantity,
    SUM(amount) AS total_amount,
    COUNT(*) AS order_count,
    EXTRACT(MONTH FROM sale_date) AS sale_month,
    EXTRACT(YEAR FROM sale_date) AS sale_year
FROM 
    sales
GROUP BY 
    product_id, EXTRACT(YEAR FROM sale_date), EXTRACT(MONTH FROM sale_date);

-- 创建带有索引的物化视图
CREATE MATERIALIZED VIEW mv_sales_by_region AS
SELECT 
    region,
    product_id,
    SUM(quantity) AS total_quantity,
    SUM(amount) AS total_amount
FROM 
    sales s
JOIN 
    customers c ON s.customer_id = c.customer_id
GROUP BY 
    region, product_id;

-- 为物化视图创建索引
CREATE INDEX idx_mv_sales_by_region ON mv_sales_by_region(region, product_id);

-- 创建不包含数据的物化视图
CREATE MATERIALIZED VIEW mv_sales_pending WITH NO DATA AS
SELECT 
    *
FROM 
    sales
WHERE 
    status = 'pending';

刷新物化视图

物化视图的刷新是指将物化视图的数据更新为最新的查询结果。KingBaseES 支持多种刷新方式：

1. 完全刷新（Complete Refresh）

完全刷新会删除物化视图中的所有数据，然后重新执行查询生成新的数据。

sql

-- 完全刷新物化视图
REFRESH MATERIALIZED VIEW mv_sales_summary;

-- 完全刷新物化视图，不锁定数据（KingBaseES V8R3+支持）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_sales_summary;

2. 增量刷新（Incremental Refresh）

增量刷新只更新物化视图中发生变化的数据，而不是重新生成所有数据。增量刷新需要物化视图有一个唯一索引，并且查询支持增量更新。

sql

-- 增量刷新物化视图
REFRESH MATERIALIZED VIEW mv_sales_summary WITH DATA;

-- 增量刷新物化视图，使用唯一索引
CREATE UNIQUE INDEX idx_mv_sales_summary ON mv_sales_summary(product_id, sale_year, sale_month);
REFRESH MATERIALIZED VIEW mv_sales_summary WITH INCREMENTAL;

3. 自动刷新

可以通过触发器或定时任务实现物化视图的自动刷新。

使用触发器刷新

sql

-- 创建触发器函数
CREATE OR REPLACE FUNCTION refresh_mv_sales_summary()
RETURNS TRIGGER AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_sales_summary;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER trg_refresh_mv_sales_summary
AFTER INSERT OR UPDATE OR DELETE ON sales
FOR EACH STATEMENT
EXECUTE FUNCTION refresh_mv_sales_summary();

使用定时任务刷新

可以使用 KingBaseES 的定时任务功能（如 pg_cron 扩展）定期刷新物化视图。

sql

-- 安装 pg_cron 扩展
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- 设置定时任务，每天凌晨 2 点刷新物化视图
SELECT cron.schedule('0 2 * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_sales_summary');

-- 查看定时任务
SELECT * FROM cron.job;

管理物化视图

查看物化视图

sql

-- 查看所有物化视图
SELECT 
    oid, 
    relname AS view_name, 
    relowner::regrole AS owner, 
    relnamespace::regnamespace AS schema_name,
    reloptions AS storage_options
FROM 
    sys_class
WHERE 
    relkind = 'm';

-- 查看物化视图的定义
SELECT 
    viewname AS view_name,
    definition
FROM 
    sys_matviews
WHERE 
    viewname = 'mv_sales_summary';

-- 查看物化视图的状态
SELECT 
    oid,
    relname AS view_name,
    last_refresh
FROM 
    sys_matviews;

修改物化视图

sql

-- 修改物化视图的存储参数
ALTER MATERIALIZED VIEW mv_sales_summary
    SET (fillfactor = 70, autovacuum_enabled = true);

-- 重命名物化视图
ALTER MATERIALIZED VIEW mv_sales_summary RENAME TO mv_sales_summary_old;

-- 修改物化视图的所有者
ALTER MATERIALIZED VIEW mv_sales_summary OWNER TO new_owner;

-- 添加注释
COMMENT ON MATERIALIZED VIEW mv_sales_summary IS '销售汇总物化视图';

删除物化视图

sql

-- 删除物化视图
DROP MATERIALIZED VIEW IF EXISTS mv_sales_summary;

-- 删除物化视图及其依赖对象
DROP MATERIALIZED VIEW IF EXISTS mv_sales_summary CASCADE;

重命名物化视图

sql

-- 重命名物化视图
ALTER MATERIALIZED VIEW mv_sales_summary RENAME TO mv_sales_summary_new;

物化视图的优化

1. 选择合适的刷新策略

根据业务需求和数据更新频率选择合适的刷新策略：

完全刷新：适用于数据更新不频繁、物化视图较小的场景。
增量刷新：适用于数据更新频繁、物化视图较大的场景。
并发刷新：适用于需要持续访问物化视图的场景，刷新时不阻塞查询。
定时刷新：适用于对数据新鲜度要求不高的场景。

2. 创建合适的索引

为物化视图创建合适的索引可以显著提高查询性能：

sql

-- 为物化视图创建主键索引
ALTER MATERIALIZED VIEW mv_sales_summary ADD PRIMARY KEY (product_id, sale_year, sale_month);

-- 为常用查询列创建索引
CREATE INDEX idx_mv_sales_summary_product ON mv_sales_summary(product_id);
CREATE INDEX idx_mv_sales_summary_date ON mv_sales_summary(sale_year, sale_month);

3. 优化物化视图查询

物化视图的查询性能取决于其定义的查询复杂度和数据量。优化物化视图查询的方法：

简化查询逻辑：避免在物化视图中使用过于复杂的查询。
限制数据量：使用 WHERE 子句限制物化视图的数据量。
合理分组：根据查询需求合理设计分组和聚合方式。
避免过度连接：尽量减少物化视图中的表连接数量。

4. 监控物化视图性能

定期监控物化视图的性能，包括刷新时间、查询性能和存储空间使用情况：

sql

-- 监控物化视图的刷新时间
SELECT 
    viewname AS view_name,
    last_refresh,
    pg_size_pretty(pg_total_relation_size(viewname)) AS size
FROM 
    sys_matviews;

-- 监控物化视图的查询性能
SELECT 
    relname AS view_name,
    idx_scan AS index_scans,
    seq_scan AS sequential_scans,
    idx_tup_read AS index_tuples_read,
    idx_tup_fetch AS index_tuples_fetched,
    n_tup_ins AS tuples_inserted,
    n_tup_upd AS tuples_updated,
    n_tup_del AS tuples_deleted
FROM 
    sys_stat_user_tables
WHERE 
    relname LIKE 'mv_%';

物化视图的高级特性

1. 分区物化视图

KingBaseES 支持创建分区物化视图，将物化视图的数据按照一定的规则分区存储，提高查询和维护性能。

sql

-- 创建分区物化视图
CREATE MATERIALIZED VIEW mv_sales_partitioned
PARTITION BY RANGE (sale_date)
AS
SELECT 
    *
FROM 
    sales
ORDER BY 
    sale_date;

-- 创建分区
CREATE MATERIALIZED VIEW PARTITION mv_sales_2023 PARTITION OF mv_sales_partitioned
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE MATERIALIZED VIEW PARTITION mv_sales_2024 PARTITION OF mv_sales_partitioned
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

2. 物化视图日志

物化视图日志用于支持物化视图的增量刷新，记录基表的变化信息。

sql

-- 创建物化视图日志
CREATE MATERIALIZED VIEW LOG ON sales
    WITH (primary key, rowid) 
    INCLUDING NEW VALUES;

-- 查看物化视图日志
SELECT * FROM mlog$_sales;

3. 嵌套物化视图

嵌套物化视图是指基于其他物化视图创建的物化视图，可以用于构建复杂的数据分析模型。

sql

-- 创建基础物化视图
CREATE MATERIALIZED VIEW mv_sales_daily AS
SELECT 
    product_id,
    sale_date,
    SUM(quantity) AS daily_quantity,
    SUM(amount) AS daily_amount
FROM 
    sales
GROUP BY 
    product_id, sale_date;

-- 创建嵌套物化视图
CREATE MATERIALIZED VIEW mv_sales_monthly AS
SELECT 
    product_id,
    EXTRACT(MONTH FROM sale_date) AS sale_month,
    EXTRACT(YEAR FROM sale_date) AS sale_year,
    SUM(daily_quantity) AS monthly_quantity,
    SUM(daily_amount) AS monthly_amount
FROM 
    mv_sales_daily
GROUP BY 
    product_id, EXTRACT(YEAR FROM sale_date), EXTRACT(MONTH FROM sale_date);

物化视图的版本差异

V8 与 V7 版本的差异

物化视图支持：
- V7 版本基本支持物化视图，但功能有限。
- V8 版本增强了物化视图的功能，支持并发刷新和增量刷新。
刷新策略：
- V7 版本只支持完全刷新。
- V8 版本支持完全刷新、并发刷新和增量刷新。
性能优化：
- V8 版本优化了物化视图的刷新性能，减少了刷新时间。
- V8 版本支持物化视图的并行刷新，提高了刷新效率。

V8R3 与 V8R2 版本的差异

并发刷新：
- V8R3 版本支持物化视图的并发刷新，刷新时不阻塞查询。
- V8R2 版本刷新物化视图时会阻塞查询。
增量刷新增强：
- V8R3 版本增强了增量刷新的功能，支持更多类型的查询。
- V8R3 版本优化了增量刷新的性能，减少了增量刷新的时间。
新特性：
- V8R3 版本支持分区物化视图，提高了大物化视图的管理和查询性能。
- V8R3 版本支持物化视图的自动刷新，通过定时任务实现。

常见问题（FAQ）

如何选择物化视图的刷新方式？

完全刷新：适用于数据量小、更新频率低的物化视图。
并发刷新：适用于需要持续访问的物化视图，刷新时不阻塞查询。
增量刷新：适用于数据量、更新频率高的物化视图，只更新变化的数据。
自动刷新：适用于对数据新鲜度要求高的物化视图，通过触发器或定时任务自动刷新。

如何优化物化视图的刷新性能？

使用增量刷新：对于大物化视图，使用增量刷新可以显著减少刷新时间。
使用并发刷新：对于需要持续访问的物化视图，使用并发刷新可以避免阻塞查询。
优化基表索引：为基表创建合适的索引，提高物化视图刷新时的查询性能。
选择合适的刷新时间：在系统负载低的时间段刷新物化视图，减少对系统的影响。
使用并行刷新：对于大物化视图，启用并行刷新可以提高刷新效率。

物化视图占用大量存储空间怎么办？

限制数据量：在物化视图查询中使用 WHERE 子句限制数据量。
定期清理旧数据：创建分区物化视图，定期删除或归档旧分区。
压缩物化视图：启用物化视图的压缩功能，减少存储空间占用。
使用增量刷新：减少物化视图的全量刷新次数，降低存储空间的临时占用。

如何监控物化视图的刷新状态？

sql

-- 查看物化视图的刷新状态
SELECT 
    viewname AS view_name,
    last_refresh,
    pg_size_pretty(pg_total_relation_size(viewname)) AS size
FROM 
    sys_matviews;

-- 查看物化视图的刷新历史（如果有审计日志）
SELECT 
    *
FROM 
    sys_audit_log
WHERE 
    object_name = 'mv_sales_summary' AND action = 'REFRESH';

物化视图可以用于实时数据查询吗？

物化视图不适合实时数据查询，因为它的数据需要定期或手动刷新，存在一定的延迟。对于实时数据查询，建议使用普通视图或直接查询基表。

总结

物化视图是 KingBaseES 中用于提高复杂查询性能的重要技术，可以显著减少查询时间，提高系统响应速度。DBA 需要根据业务需求和数据特点，合理设计和管理物化视图，选择合适的刷新策略和优化方法。

在实际生产环境中，DBA 需要：

理解物化视图的特点和适用场景
合理设计物化视图的查询和刷新策略
为物化视图创建合适的索引
定期监控物化视图的性能和存储空间使用情况
根据业务需求调整物化视图的设计和刷新策略
利用物化视图的高级特性，如分区物化视图和嵌套物化视图，提高系统的整体性能

通过合理使用物化视图，可以提高 KingBaseES 数据库的查询性能，减少系统负载，提升用户体验。

KingBaseES 物化视图 ​

物化视图概述 ​

物化视图与普通视图的区别 ​

物化视图的适用场景 ​

物化视图的创建与管理 ​

创建物化视图 ​

基本语法 ​

创建示例 ​

刷新物化视图 ​

1. 完全刷新（Complete Refresh） ​

2. 增量刷新（Incremental Refresh） ​

3. 自动刷新 ​

使用触发器刷新 ​

使用定时任务刷新 ​

管理物化视图 ​

查看物化视图 ​

修改物化视图 ​

删除物化视图 ​

重命名物化视图 ​

物化视图的优化 ​

1. 选择合适的刷新策略 ​

2. 创建合适的索引 ​

3. 优化物化视图查询 ​

4. 监控物化视图性能 ​

物化视图的高级特性 ​

1. 分区物化视图 ​

2. 物化视图日志 ​

3. 嵌套物化视图 ​

物化视图的版本差异 ​

V8 与 V7 版本的差异 ​

V8R3 与 V8R2 版本的差异 ​

常见问题（FAQ） ​

如何选择物化视图的刷新方式？ ​

如何优化物化视图的刷新性能？ ​

物化视图占用大量存储空间怎么办？ ​

如何监控物化视图的刷新状态？ ​

物化视图可以用于实时数据查询吗？ ​

总结 ​