实时物化视图有哪些功能 - 云原生数据库 PolarDB

本文介绍了PolarDB PostgreSQL版的实时物化视图功能。

适用范围

支持的PolarDB PostgreSQL版的版本如下：

PostgreSQL 14（内核小版本2.0.14.8.11.0及以上）
PostgreSQL 11（内核小版本2.0.11.9.27.0及以上）

说明

您可在控制台查看内核小版本号，也可以通过SHOW polardb_version;语句查看。如未满足内核小版本要求，请升级内核小版本。

背景信息

物化视图区别于普通视图，可以直接存储查询的结果，在复杂查询的场景下，使用物化视图保存查询的结果可以大幅度加速查询的效率。但物化视图的数据不会随着依赖表数据的变化而变化，导致使用物化视图查询的结果可能不是最新的。

对于这种情况，PolarDB推出了实时物化视图的功能，实时物化视图对于物化视图具有以下优势：

实时物化视图支持语句级别更新，当依赖表进行DML（插入/删除/更新）操作，在DML语句结束时，实时物化视图会自动更新物化视图内的数据，让实时物化视图的数据始终和依赖表的数据保持一致。
实时物化视图最大限度的利用基表的增量数据，刷新时无需全量执行视图查询，相较于频繁的执行物化视图刷新，实时物化视图的性能更好。
使用实时物化视图可以在大幅度提升查询性能的同时，确保查询结果和依赖表数据的一致性。

术语

基表（Base Tables）：指物化视图定义中使用到的普通表。
增量（Delta）：指基表的数据发生变化时，与物化视图中的数据相比，增加和删除的数据集合。
刷新（Refresh）：指物化视图的维护操作，使得物化视图的数据和根据视图定义查询当前基表获得的数据一致。
应用增量（Apply Delta）：指对实时物化视图插入/删除计算出来的物化视图增量数据，以保持实时物化视图与基表的数据一致性。

使用限制

实时物化视图的视图定义具有以下限制：

基表必须是普通表，不能是分区表和继承表。
仅支持使用IMMUTABLE的函数。
仅支持包含简单的查询、投影、DISTINCT、部分聚合函数的视图定义；不支持带有子查询、[NOT] EXISTS、[NOT] IN、LIMIT、HAVING、DISTINCT ON、WITH（CTE）、ORDER BY、窗口函数、GROUPING SETS、CUBE、ROLLUP、UNION、INTERSECT、EXCEPT等复杂查询的视图定义。
使用GROUP BY语句时，GROUP BY指定的分组必须出现在投影中。
仅支持特定的聚合函数，即系统内置的MIN、MAX、SUM、AVG和COUNT函数。
支持包含INNER JOIN的视图定义。
支持OUTER JOIN的视图定义，包括LEFT JOIN/RIGHT JOIN/FULL JOIN。但有如下限制：
说明
仅内核小版本为2.0.14.18.37.0及以上版本支持。
- 不支持同时带有OUTER JOIN和DISTINCT。
- 不支持同时带有OUTER JOIN和GROUP BY。
- 不支持同时带有OUTER JOIN和聚合函数。
- 不支持OUTER JOIN和FROM语句混用。
- 如果查询语句包含OUTER JOIN，则仅支持简单的等值连接条件，不支持AND/OR连接，以及等值条件的左右列来自同一张表的情况。
- 如果查询语句包含OUTER JOIN，则输出列需包含JOIN条件中的所有列。
当您在基表上创建实时物化视图后，对该基表执行的DDL操作将受到以下限制：
- 仅在指定CASCADE选项的情况下，才能对基表执行DROP TABLE命令。
- 对基表执行ALTER TABLE命令时，无法删除或修改物化视图所引用的字段。

性能影响

实时物化视图大幅度提升查询性能的同时，对基表写入性能会有较大的影响，建议在读多写少的场景下使用。
实时更新物化视图对基表写入性能的影响程度受实时物化视图定义、基表写入负载、基表结构和基表索引等因素的影响，建议您在生产环境创建实时物化视图前，在测试环境测试带有实时物化视图的基表的写入性能，确认写入性能满足要求的情况下再在生产环境使用实时物化视图。
以下方式有助于降低维护实时物化视图的代价：
- 同一个基表上的实时物化视图不要太多。
- 对基表批量写入。例如，使用COPY/INSERT INTO SELECT的方式批量导入数据。
- 基表都有主键，并且实时物化视图定义中的投影列中包含所有基表的主键。

原理介绍

创建实时物化视图
- 对物化视图查询改写，额外计算出维护实时物化视图所需要的隐藏列。
- 对基表创建Trigger，以便实现实时物化视图的增量刷新。
- 在满足一定条件的时候，对实时物化视图创建唯一索引，加速实时物化视图的增量刷新。
增量刷新实时物化视图
- 基表的数据变化触发相应的Trigger。
- 通过Trigger获取到基表的增量数据。
- 根据实时物化视图的定义和当前基表的增量数据计算实时物化视图的增量。
- 将计算出的增量应用到实时物化视图上，完成实时物化视图的增量刷新。
删除实时物化视图
- 删除实时物化视图对应基表上的增量刷新Trigger。
- 删除实时物化视图本身。

使用指南

准备工作
在需要使用实时物化视图的数据库中创建polar_ivm插件。
```
CREATE EXTENSION polar_ivm WITH SCHEMA pg_catalog;
```

创建实时物化视图

CREATE MATERIALIZED VIEW table_name[ (column_name [, ...] ) ]
REFRESH FAST
ON COMMIT
AS query
[ WITH [ NO ] DATA ]

参数说明

参数	说明
table_name	需要创建的实时物化视图的名称（可以被模式限定）。
column_name	新物化视图中的一个列名。如果没有提供列名，则会从查询的输出列名中获取。
WITH DATA	默认选项，立刻创建完整的实时物化视图。
WITH NO DATA	只创建出实时物化视图的结构，但物化视图上没有数据，也不会在该视图上保持实时刷新。查询该实时物化视图会报错，直到对该实时物化视图执行`REFRESH MATERIALIZED VIEW`命令。
query	实时物化视图的视图定义，一个SELECT、TABLE或者VALUES命令。该查询将在一个安全受限的操作中运行。

增量刷新实时物化视图
```
REFRESH MATERIALIZED VIEW table_name
```
说明
其中。
- table_name：表示需要刷新的实时物化视图的名称（可以被模式限定）
- 通过WITH DATA方式创建的实时物化视图不需要手动执行刷新来确保视图和基表的一致性，因此会跳过手动刷新。
- 通过WITH NO DATA方式创建的实时物化视图执行刷新后，会通过视图定义生成实时物化视图的数据，并对后续基表的更改开启实时刷新。

删除实时物化视图

DROP MATERIALIZED VIEW [ IF EXISTS ] table_name [, ...] [ CASCADE | RESTRICT ]

参数说明：

参数	说明
IF EXISTS	如果该实时物化视图不存在则不抛出一个错误，而是发出一个提示。
table_name	需要移除的实时物化视图的名称（可以是模式限定的）。
CASCADE	自动删除依赖于该实时物化视图的对象（例如，其他物化视图或常规视图），然后再删除所有依赖于自动删除对象的对象。
RESTRICT	如果有任何对象依赖于该实时物化视图，则拒绝删除该实时物化视图。默认选择该参数。

使用示例

使用聚合函数的实时物化视图

创建实时物化视图的依赖插件。

CREATE EXTENSION IF NOT EXISTS polar_ivm WITH SCHEMA pg_catalog ;

创建基表并导入初始数据。

CREATE TABLE t( a INT, b VARCHAR);
INSERT INTO t VALUES
  (1,'a'),
  (2,'b'),
  (3,'c'),
  (4,'d'),
  (5,'e');

创建实时物化视图。

CREATE MATERIALIZED VIEW mv
REFRESH FAST
ON COMMIT
AS
SELECT max(a),min(a),b FROM t GROUP BY b;

对基表进行DML操作。

查询实时物化视图数据。

SELECT * FROM mv ORDER BY b;

显示结果如下：

 max | min | b
-----+-----+---
   1 |   1 | a
   2 |   2 | b
   3 |   3 | c
   4 |   4 | d
   5 |   5 | e
(5 rows)

结果显示实时物化视图的数据与基表数据保持一致。

向基表中插入新数据，查询实时物化视图数据。

INSERT INTO t VALUES(6,'f');

SELECT * FROM mv ORDER BY b;

显示结果如下：

 max | min | b
-----+-----+---
   1 |   1 | a
   2 |   2 | b
   3 |   3 | c
   4 |   4 | d
   5 |   5 | e
   6 |   6 | f
(6 rows)

结果显示实时物化视图的数据与基表数据保持一致。

删除基表数据，查询实时物化视图数据。

DELETE FROM t WHERE a = 2;

SELECT * FROM mv ORDER BY b;

显示结果如下：

 max | min | b
-----+-----+---
   1 |   1 | a
   3 |   3 | c
   4 |   4 | d
   5 |   5 | e
   6 |   6 | f
(5 rows)

结果显示实时物化视图的数据与基表数据保持一致。

更新基表数据，查询实时物化视图数据。

UPDATE t SET a = a + 1;

SELECT * FROM mv ORDER BY b;

显示结果如下：

 max | min | b
-----+-----+---
   2 |   2 | a
   4 |   4 | c
   5 |   5 | d
   6 |   6 | e
   7 |   7 | f
(5 rows)

结果显示实时物化视图的数据与基表数据保持一致。

删除实时物化视图。
```
DROP MATERIALIZED VIEW mv;
```

使用 OUTER JOIN 的实时物化视图

说明

仅内核小版本为2.0.14.18.37.0及以上版本支持。

创建实时物化视图的依赖插件。

CREATE EXTENSION IF NOT EXISTS polar_ivm WITH SCHEMA pg_catalog;

创建基表并导入初始数据。

CREATE TABLE t1(a INT, b VARCHAR);
INSERT INTO t1 VALUES
  (1,'a'),
  (2,'b'),
  (3,'c'),
  (4,'d'),
  (5,'e');

CREATE TABLE t2(a INT, c VARCHAR);
INSERT INTO t2 VALUES
  (1,'e'),
  (2,'f');

创建实时物化视图。

CREATE MATERIALIZED VIEW mv
REFRESH FAST
ON COMMIT AS
SELECT t1.a as t1a, t1.b,
       t2.a as t2a, t2.c
FROM t1 LEFT JOIN t2
ON t1.a = t2.a;

对基表进行DML操作。

查询实时物化视图数据。

SELECT * FROM mv ORDER BY b;

显示结果如下：

 t1a | b | t2a | c
-----+---+-----+---
   1 | a |   1 | e
   2 | b |   2 | f
   3 | c |     |
   4 | d |     |
   5 | e |     |

结果显示实时物化视图的数据与基表数据保持一致。

向基表中插入新数据，查询实时物化视图数据。

INSERT INTO t2 VALUES(3,'g');

SELECT * FROM mv ORDER BY b;

显示结果如下：

 t1a | b | t2a | c 
-----+---+-----+---
   1 | a |   1 | e
   2 | b |   2 | f
   3 | c |   3 | g
   4 | d |     | 
   5 | e |     |

结果显示实时物化视图的数据与基表数据保持一致。

删除基表数据，查询实时物化视图数据。

DELETE FROM t1 WHERE a in (1, 4);

SELECT * FROM mv ORDER BY b;

显示结果如下：

 t1a | b | t2a | c
-----+---+-----+---
   2 | b |   2 | f
   3 | c |   3 | g
   5 | e |     |

结果显示实时物化视图的数据与基表数据保持一致。

更新基表数据，查询实时物化视图数据。

UPDATE t2 SET a = a + 1;

SELECT * FROM mv ORDER BY b;

显示结果如下：

 t1a | b | t2a | c
-----+---+-----+---
   2 | b |   2 | e
   3 | c |   3 | f
   5 | e |     |

结果显示实时物化视图的数据与基表数据保持一致。

删除实时物化视图。
```
DROP MATERIALIZED VIEW mv;
```