物化视图的更新
作者： djshow832-PingCAP 原文来源：https://tidb.net/blog/ff00d958
MV 介绍
MV 是什么
物化视图（Materialized Views，以下简称 MV）是一种特殊的视图，它的数据会持久化。那么在查询 MV 时，并不会去访问基表，而是直接从 MV 里读数据。
当然，基表的数据会修改，那么 MV 的数据也要跟着修改。
创建 MV 与普通视图类似，只是要加一个 MATERIALIZED 关键字，如下：
创建 sales_by_item 时，会把 SELECT 的结果查询出来并写到表中。
查询 sales_by_item 时，数据库不读基表 sales，而是直接读 sales_by_item。
更新 sales 时，sales_by_item 要能被同步或异步地更新。
典型使用场景
加速查询
有些查询需要访问大量的数据，而且每天要查询数次，那么就可以利用 MV。这样不仅查询更快，还降低了系统的 CPU 和 IO 开销。
同时，还可以在 MV 上建索引、分区以进一步提高性能。
数据仓库
把多个数据源的数据收集并存储的数据库，就叫数据仓库（Data Warehousing）。
MV 的作用是把多个数据源的数据收集起来，而不是直接复制数据。这样在数据仓库查询时，只用访问 MV ，而不用访问远程的数据源。
PostgreSQL 文档有一个在远程表上建 MV 的例子：
words 是一张远程表，wrd 是本地建的 MV。虽然不能在 file_fdw 上建索引，但是可以在 wrd 上建索引来加速。测试表明，对 wrd 的查询性能远高于直接查询 words。
远程数据源的更新都会同步到数据仓库的 MV 中。由于访问远程数据源的代价较高， MV 最好是 self-maintainable 的，即更新时只需要读取增量数据以及 MV 自身的数据，而不需要读取远程数据源。
数据集成
数据集成（Data Integration）是把多个数据源的相同对象进行匹配（类似于 outer join），组合为更大的对象。
因为匹配条件很难计算，而 MV 可以保存组合后的对象，所以复用的代价就很低了。
数据备份
Oracle 设计 MV 的目的主要有两个：数据仓库、备份（Replication）。
一些场景下（例如 field sales），副本在固定时刻会断开网络，要在重新连接网络时同步数据，那么需要在需要时更新副本或者定期更新。
这种需要恰巧符合 MV 的能力，所以有一种备份叫 MV 备份。这些场景的 MV 包含了基表的全部或部分数据。
当成快照使用
MV 不一定要实时更新，那么每次 MV 更新完，就相当于是基表的一个快照。可以利用这个特定，来实现特定的业务逻辑。
实际上，Oracle 最初的 MV 就叫 snapshot。
举个例子，有个显示文章的系统，用户可以通过前端查看文章。后台可以随时修改文章并保存，但是希望前端不要马上看到，而是修改完以后一次刷新到前端显示。
这样我们就可以用 MV 来实现：后台修改的是基表，前端读的是 MV。后台修改完后，再手动刷新 MV，让前端看到。
MV 的属性
MV 的实现方式和应用场景非常灵活。按 Oracle 论文的定义，MV 在四个属性上有所区别：
timing，即更新的时机
type，即更新的方式是全量还是增量
location，即基表是 local 还是 remote 的
rewrite，即查询时是否可以重写 SQL
下面分别来详细介绍。
更新时机
写入时更新
写入基表后立即更新 MV，通常用于写入频率不高的场景，因为它会显著增大写入延迟。
如果使用写入时更新，那么必然是使用增量更新。这种方式不需要 MV Log。
查询时更新
假如写入的频率比较高，写入时可以先不更新，等下次查询时再更新。
如果使用查询时更新，那么必然是使用增量更新。
用命令更新
不保证数据的实时性，通常只能用于数据实时性不高的场景，例如数据分析。
可以使用定时任务来执行更新命令，例如在半夜更新。
用命令更新并不影响更新方式，可以是全量更新也可以是增量更新。
更新方式
全量更新
每次更新时把 MV 清空，再查询并把结果全部插入到 MV 中。
这是一种较为简单的实现方式。
增量更新
不用每次把 MV 清空再重新插入，而是获取对基表的增量修改，再计算并应用到 MV 上。
如果更新触发方式不是写入时更新，那么要把两次更新间对基表的修改记录下来，通常使用日志，这里称为 MV Log。
基表的位置
本地
如果基表在本地，那么更新时可以随时访问基表。这样对 MV 没有技术上的限制，MV 几乎可以支持所有类型的查询和更新时机。
远程
数据仓库的基表通常是远程的，因为读基表的代价太大，会选择两种方式：
更新时机选择查询时更新或定时更新，降低更新频率
不允许访问基表的数据，只能得到增量数据和访问 MV 自身的数据，这限制了 MV 包含的算子类型，所以某些 MV 不允许用增量更新
查询方式
SQL 中显式引用 MV
把 MV 当作一张表来查询。
这是一种较为简单的实现方式。
自动匹配 MV 并改写 SQL
既然已经有了 MV，有些 SQL 可以把 MV 当作中间结果，直接利用 MV。
是否要查 MV 需要基于代价来判断。
商业数据库中的 MV
Oracle
Oracle 的 MV 做得非常强大。以上列举的各种属性，Oracle 全都做成了选项，供用户选择。甚至还有其他选项，例如创建 MV 有两种策略：
IMMEDIATE：建完 MV 后立即把数据写进去
DEFERRED：第一次查询时再把数据写进去
Oracle 的开发也是循序渐进的，其他数据库新开发 MV 可以借鉴：
1992 年开始引入 MV，当时叫 snapshot，只能全量更新。MV 也只能定义为单表的 select-project 查询。
Oracle 8.1 支持三种 MV 的增量更新：MJV( 包含 Join)、MAV( 包含 Agg)、MSV( 包含 Subquery)。其他类型的查询只能全量更新。
PostgreSQL
PostgreSQL 的 MV 做得很简单。
MV 不保证数据最新，只能手动更新，例如：
所以它适用于对数据实时性要求不高的场景。
PostgreSQL 也支持在 MV 上建索引。
MySQL
MySQL 没有原生 MV，要借助 FlexViews 去实现，它可以创建 MV 和增量更新 MV。
它的增量更新分为几个部分：
感知基表的数据更新
计算哪些数据有更新
增量更新如何应用到 MV 上
如何把 FlexViews 应用于 Online Schema Change 和 ETL
其他数据库在开发过程中，如果没有精力开发 MV，也可以考虑兼容 FlexViews。
增量更新算法
增量更新算法分为几类：
Outer-Join Views 是 MV 的一个子集，它只包含 Outer-Join。Outer-Join Views 用于做数据集成，因为数据集成就是把多个数据库做 Outer-Join。
不能读基表，只能读增量数据和 MV 自身的数据。这种主要用于数据仓库，因为读基表需要访问远程数据源，代价太高。
其他类型的 MV，没有其他限制。
本文主要讲第三种，即通用的 MV。
在所有更新算法中，基表的 UPDATE 总是被转化为 DELETE + INSERT 以简化流程。所以只需要考虑基表上的 INSERT 和 DELETE 两种操作。
论文中的更新算法
1999 年的综述性论文《Maintenance of Materialized Views: Problems, Techniques, and Applications》描述了很多种更新算法，以下列举了最典型的几个。
这里不对每种算法做详细展开，只介绍大概思路。
counting 算法
对 MV 的每行记录出现的次数进行计数。在 INSERT 时增加计数，DELETE 时减少计数。
这种算法可以扩展到 UNION、Agg、outer join。
不适用于有 duplicate 语义的 SQL，比如有 distinct 算子。
Outer-Join View
Outer-Join 常用于数据集成，因此数据集成里的 MV 多研究 Outer-Join 的算法。
R ✕ S (full outer join) 可以由 Query(a) 和 Query(b) 求出来。
Query(a) = ΔR ⋉ S
Query(b) = R’ ⋊ ΔS
其中 R’ 是更新之后的 R。
DRed 算法
应用于 Datalog 和 SQL View，包括递归的 view，但是不能应用于有 duplicate 语义的 view。
它分为三步：
根据在基表上删除的记录，从 MV 中删除所有匹配的记录，这一步可能会多删。
对于在 MV 上删除的记录，在基表上查找是否有其他记录可以求出相同的值，如果有，就把它们还原回去。
对于主表上新增的记录，往 MV 中插入。
Oracle 的更新算法
上面论文的算法不一定适用于商业数据库，商业数据库采用的方法才是最实用的。下面将重点介绍 Oracle 的算法实现。
下面的内容根据 Oracle 1998 年在 VLDB 上发表的论文《Materialized Views In Oracle》整理。
log
用两种日志来记录增量数据：row-DML log 和 direct-loader log。
MV 的每个基表都有这两种日志，并且日志在不同的 MV 之间共享。
row-DML log
类似于 binlog，每一行还记录了 DML 的 timestamp，有两个用途：
在刷新 MV 时根据 timestamp 决定要不要应用到 MV 上
清除不再需要的 log
通过 row-DML log 很难计算出基表更新之前的数据，所以只能利用新表的数据、增量数据来更新 MV。
direct-loader log
类似于 load data 批量导数据，新的数据是物理上连续的，所以 log 也是连续的。direct-loader 适用于数据仓库这样经常批量导数据的场景。
通过 direct-loader log 很容易算出基表更新之前的数据、增量数据。
一条语句只会记一种 log，普通 DML 不会记 direct-loader log，批量导数据也不会记 row-DML log。
更新 MV 的难点在于要同时考虑两种 log。
视图类型
MJV (Materialized Join View)
MJV 是 deferred 更新的，就是攒一批增量数据一起更新。
R 和 S 两表做 join，两张表都可能有更新，下面展示如何把一批更新应用到 MV 上去。
R ⋈ S 计算 MV 上的增量更新的公式为：
公式 1：ΔM = R ⋈ ΔS + ΔR ⋈ S + ΔR ⋈ ΔS = ΔR ⋈ S’ + R ⋈ ΔS
由于读了更新前的基表 R，所以只适用于 row-DML log 为空的情况。
如果 row-DML log 有数据，那么较难计算出更新前的 R 和 S，只能利用 delta 和更新后的表：
公式 2：ΔM = R’ ⋈ ΔS - ΔR ⋈ ΔS + ΔR ⋈ S’
R ☓ S 只需要把公式改为：
公式 3：ΔM = R’ ⋈ ΔS - ΔR ⋈ ΔS + ΔR ☓ S’
把 UPDATE 拆成 DELETE + INSERT，所以只有 DELETE 和 INSERT 两种操作。
以 R’ ⋈ ΔS 为例，分为两步：
DELETE 阶段，对于 ΔS 中的删除：
inner-join：在 M 中根据 row id 找到行 r，把 r 从 M 中删除
outer-join：在 M 中根据 row id 找到行 r，把 r 中引用 S 的列改为 null
INSERT 阶段，对于 ΔS 中的插入：
inner-join：新生成行 r，把 r 插入到 M 中
outer-join：在 M 中根据 row id 找到行 r，把 r 中引用 S 的列改为 ΔS 的值
MAV (Materialized Aggregate View)
MAV 一定包含 Aggregation，也可以包含 Join，这两种情况要分开研究。
不包含 Join
MAV 不包含 Join 且没有 MIN/MAX 函数时，情况很简单。有两种算法：self-maintenance 和 memoryless refresh。
self-maintenance：只用 row-DML log 来更新，不访问基表，因为 MAV 的结果集往往远小于基表的数据量。它只适用于 direct-loader log 为空的情况。
memoryless refresh：与更新 MJV 一样，包含 DELETE 阶段和 INSERT 阶段。它适用于任何情况。
包含 Join
对于包含 Join 的 MAV，同样按上面的公式先计算 Join 的 delta，并把每一项（例如 ΔR ⋈ S’）的计算结果聚合到 delta summary 中，最后再把 delta summary 合并到 MAV 中。
刷新的原理不难。其实重点不是怎么刷新，而是怎么优化。
数据仓库里常有带 Join 的 MAV，而数据仓库一般要求所有 MAV 能在固定的时间窗口内刷新完成，所以 Oracle 要做全局的优化，以缩短刷新所有 MAV 的总时间。
Oracle 用这些技术来优化：
MAV-based refresh：一个 MAV 可能不读基表，而是读另一个 MAV 的数据，并且刷新用 parallel-DML 来加速。这就要求 MAV-based refresh 能找出 MAV 之间的关联关系，并且决定它们的刷新顺序。
负载均衡调度算法：用负载均衡调度算法并行地刷新 MAV。
refresh-dependent 功能：有些 MV，即使部分基表被修改了，它也不一定要刷新。refresh-dependent 就是找出这种关系，决定要不要去刷新它们。
下面以 MAV-based refresh 举例。
MAV1 统计每个供应商每件商品的销量：
MAV2 统计每个供应商的总销量：
MAV1 对 provider + item_id 做 group by，MAV2 只对 provider 做 group by。可见，如果 MAV2 直接在 MAV1 的基础上再做一次 group by 就可以了。而 MAV1 的数据量必然远低于基表，所以效率会有大幅提升。
MAV-based refresh 就是做这件事。
MSV (Materialized Subquery View)
支持的 Subquery 只能是 EXISTS 查询。
MSV 与 MJV 实现相似，因为关联性查询还是会被转成 Join。
版权声明: 本文为 InfoQ 作者【TiDB 社区干货传送门】的原创文章。
原文链接:【http://xie.infoq.cn/article/445c6e36de81fe01ab36f583f】。文章转载请联系作者。
TiDB 社区官网:https://tidb.net/ 2021.12.15 加入
TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目，旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/
评论