关于数据库:一文详解物化视图改写

简介：本文次要介绍什么是物化视图，以及如何实现基于物化视图的查问改写。
作者：阿里云数据库 OLAP 产品部 云曦

预计算和缓存是计算机领域进步性能以及降低成本的最常见的伎俩之一。对于那些常常反复的申请，如果能够通过缓存答复，比从新计算结果或从速度较慢的数据存储中读取要快得多，耗费更少的系统资源。在数据库畛域中，物化视图是预计算和缓存的天然体现。

本文次要介绍什么是物化视图，以及如何实现基于物化视图的查问改写。
在第一局部，咱们会简略介绍物化视图，并介绍基于物化视图的查问改写的用处。在第二局部，咱们将介绍查问优化器应用物化视图进行查问改写的匹配和改写过程。最初，咱们将介绍查问改写的几种实现形式，及其优缺点。

背景介绍

物化视图

物化视图是将查问后果事后计算并存储的一张非凡的表。” 物化 ”(Materialized) 这个词是绝对于一般视图而言。一般视图较一般的表提供了易用性和灵活性，但无奈放慢数据拜访的速度。物化视图像是视图的缓存，它不是在运行时构建和计算数据集，而是在创立的时候事后计算、存储和优化数据拜访，并主动刷新来保证数据的实时性。

对于数据仓库，物化视图最重要的性能就是查问减速。数据仓库中存在大量在大型表上执行简单的查问，这些查问会耗费大量资源和工夫。物化视图能够通过预计算的后果答复查问，打消低廉的联接和聚合所带来的开销，大幅度改善查询处理工夫，升高零碎负载。对于能够预感并重复应用雷同子查问后果的查问，物化视图特地有用。

为了实现物化视图的后劲，须要解决三个问题：

1. 物化视图抉择：抉择哪些查问和表构建物化视图；
2. 物化视图保护：缩小物化视图更新老本和工夫；
3. 物化视图使用：如何应用物化视图减速查问。
4. 本文次要从查问优化器的角度，介绍应用物化视图减速查问背地的技术实现。

基于物化视图的查问改写

间接查问物化视图能够大幅度改善查询处理工夫，然而须要用户批改查问语句。应用物化视图减速查问的一个重要问题是，如何采纳一种系统化和自动化的办法，主动应用物化视图答复查问。通过这种通明改写，物化视图能够像索引一样增加或删除，而不会影响已有 SQL。

查问改写使得物化视图具备宽泛的用处：

1. 物化视图能够通明地改写查问，无需革新业务就能应用物化减速查问；
2. 不便地利用缓存公共后果集，以及预计算等跨查问优化伎俩；
3. 对于数据仓库，数据集成场景，物化视图能够物化表面后果，屏蔽多个数据源的差别，实现本地正本或读写拆散；
4. 查问改写联合主动构建物化视图，实现数据库自治减速。

查问改写的问题定义

为了实现更大范畴的改写，查问改写通常被集成在优化器规定中。这有几个方面的益处。

首先查问改写能够利用优化器其余规定。依附优化器其余规定将查问转换成规范和对立的模式，简化匹配流程，减少改写范畴。其中比拟重要的规定是列打消，谓词下推，解关联子查问等。解关联子查问规定容许物化视图对蕴含关联子查问的查问进行改写减速。

其次，优化器能够递归每一个子树是否被某个视图进行改写。每个相干视图都会对每个子树产生屡次改写，一个查问语句不同局部可能被不同的视图改写。最终所有的改写都进入基于老本的选择器中，与原始查问一起选出最优的查问打算。
查问改写算法只须要思考给定的查问表达式和视图，判断这个查问表达式是否从视图中计算出来，而后从视图上结构一个等价的弥补表达式，与原查问表达式等价。查问改写的范畴应该尽可能大，查问改写的指标是应用大量物化视图改写大量查问。最终由优化器抉择出一个最优的查问打算。

查问改写查看

优化器通过多种形式来改写查问。最简略的一种状况是物化视图的查问与查问齐全匹配，合乎这种查问重写类型的查问数量很少。为了进行更通用的匹配，优化器会尝试应用各种规定结构一个等价表达式改写查问。

查问改写查看蕴含两个步骤，改写匹配检查和构建等价表达式。一个查问能被视图答复须要满足上面两个条件：

1. 物化视图 Join 关系在查问中存在
2. 物化视图有足够的数据来答复查问

局部条件下即便不满足条件，视图也可能会被用于改写。例如视图蕴含一部分查问所须要的数据，能够应用物化视图答复局部查问，剩下的数据从原始数据中计算。这部分改写查看会放在高级改写规定中进行介绍。

具体来说改写查看会顺次进行 Join 检查和 Ouput 查看，如果查问或视图中含有 Grouping By 和 Aggregation，还会进行额定的查看，如果须要，会尝试对视图进一步聚合。

Join 查看

当查问和视图的表的 Join 关系雷同时，视图才可能蕴含查问须要的所有的行和列。一种简略的形式是只思考没有子查问的 Inner Join，这时只用比拟查问表和数量是否完全一致即可。或者通过一系列规定查看查问和视图的关系代数树蕴含的 Join 关系是等价的。更通用的一种办法是构建 Join Graph。Join Grpah 是一个以关系为结点，联接为边的图。Inner Join 的条件示意为无向边，Outer Join 的条件示意为有向边。Join Graph 由查问关系代数树构建而来，通过比拟 Join Graph 就能够查看物化视图和查问蕴含雷同的 Join 关系。

例如对于上面的查问

select c_custkey, c_name, l_orderkey, l_partkey, l_quantity
from (
select l_orderkey,l_partkey, l_quantity
from lineitem
where l_orderkey < 1000
and l_shipdate = l_commitdate
) subquery
join orders on subquery.l_orderkey = o_orderkey
left join customer on o_custkey = c_custkey
对于物化视图
create materialized view mview1
enable query rewrite as
select *
from lineitem
join customer On subquery.l_orderkey = o_orderkey
left outer join orders On o_custkey = c_custkey
查问和物化视图具备雷同的 Join 关系，查问能够被改写
select c_custkey, c_name, l_orderkey,l_partkey, l_quantity
from mview1
where l_orderkey < 1000
and l_shipdate = l_commitdate

Output 查看

Ouput 查看保障物化视图有足够的数据答复查问，这包含 3 个步骤：

1. 验证查问所有输入须要的列可能从视图中计算出来
2. 优化器须要查看视图蕴含查问所有须要的行数，也就是视图的谓词的范畴大于查问的范畴
3. 弥补谓词是否从视图中计算得来

等价关系

查问改写可能通过查问中的等价关系扩大改写的范畴。因为等值条件具备传递性，等价关系能够从等值条件或者代数关系推导而来。例如能够从 A=date_format(now(), ‘%Y%m%D’) 和 B=date_format(now(), ‘%Y%m%D’)，因为函数是确定性的，能够失去 A=B，如果咱们还有 inner join 的条件 B=C，能够进一步失去 A=B=C。

可能会有某些其余的条件能够推导出等价关系，例如能够从 A=2，B=3，C=5 推导出 C = A + B。相比等值关系，寻找这种关系会使搜寻过程更加简单，而且咱们无奈实现所有的可能的相等关系，因而很少思考这样的表达式。等价关系推导只搜寻等值条件，即便可能错过一些改写机会，这样的浅层搜寻能够保障速度。

表达式查看

为了确保查问输入的列和弥补谓词须要的列都能从视图中计算出来，咱们须要一种办法确定来自查问的表达式是否和视图中的表达式相等，或者能从中计算出来。表达式查看无奈纯正从语法上实现，两个表达式或者符号的文本雷同，并不阐明他们关系相等。例如别名的存在就会毁坏基于语法的查看。

表达式查看须要通过等价关系和表的对应关系推导而来。如果视图和查问中所有的表都是惟一的，那么来自同一个表的列是等价的；如果视图和查问中有表呈现屡次，即 self join，那么查问到视图的表的映射就存在多种可能，每种可能都须要进行一次改写尝试。有了视图和查问之间列的对应关系和上一节的等价关系，通过代数零碎确定来自查问的表达式是否和视图中的表达式相等，或者是否从中计算出来。

存在一些启发式的规定，容许一个表达式从另外的表达式中计算出来。比方算数规定从 x + 1 中计算出 x，例从 SUM(x) 和 COUNT(x) 计算出 AVG(x)，还存在一些 Function Dependency 规定，例如工夫函数，能够通过返回的天的后果计算年等。

谓词查看

视图改写须要物化视图中存在查问所有须要的行数，即视图的谓词的范畴大于或等于查问的范畴。应用 Wq 示意查问的谓词，Wv 示意视图的谓词，咱们须要查看 Wq => Wv，其中 => 示意 Wq 满足 Wv 的含意。

优化器提取所有的谓词，并将他们转换为 CNF 的模式，即 W=P1 ^ P2 ^ … ^ Pn。将谓词依照等值谓词，范畴谓词和残余谓词进一步分为 W= PE ^ PR ^ PU。PE，PR，PU 别离代表等值谓词，范畴谓词和残余谓词。谓词查看变成了 PEq ^ PRq ^ PUq => PEv ^ PRv ^ PUv，因为正交的性质，最终将问题分解成 PEq => PEv，PEq ^ PRq => PRv，PEq ^ PUq => PUv 三类查看。

查问的等值谓词用于推导等价关系。查问中任何视图不满足等价关系的谓词都形成弥补谓词，视图中存在查问无奈满足的等价关系则无奈改写。

范畴谓词在优化器中能够存储为范畴的模式，能够不便的计算差集。对于查问中每一个范畴谓词，如果视图存在对应的范畴精准匹配，不须要进行弥补，否则视图范畴必须大于查问的范畴，并通过差集结构一个弥补谓词。

查问表达式和试图残余的谓词独特形成残余谓词 PE，只能进行精准匹配。查问中任何与视图不匹配的谓词都形成弥补谓词。查问与视图不匹配则无奈改写。
所有的弥补谓词都须要通过表达式查看，确保能够从视图的输入中计算出来。

例如查问

select l_orderkey, o_custkey, l_partkey,l_quantity*l_extendedprice
from lineitem, orders, part
where l_orderkey = o_orderkey
and l_partkey = p_partkey
and l_partkey >= 150 and l_partkey <= 160
and o_custkey = 123
and o_orderdate = l_shipdate
and p_name like‘%abc%’
and l_quantity*l_extendedprice > 100

物化视图

create materialized view mview2
Enable Query Rewrite
as select l_orderkey, o_custkey, l_partkey,l_shipdate, o_orderdate,l_quantity*l_extendedprice as gross_revenue
from lineitem, orders, part
where l_orderkey = o_orderkey
and l_partkey = p_partkey
and p_partkey >= 150
and o_custkey >= 50 and o_custkey <= 500
and p_name like‘%abc%’

查问能够被改写为

select l_orderkey, o_custkey, l_partkey, gross_revenue
from mview2
where l_partkey <= 160
and o_custkey = 123
and o_orderdate = l_shipdate
and gross_revenue > 100

Grouping 和 Aggregation 查看

如果视图和查问带有 GroupBy 或 Aggregation 函数，须要进行额定查看：

1. 查看查问申请的数据分组是否与物化视图中存储的数据分组雷同，如果不同，优化器会尝试对物化视图进行汇总。
2. 如果须要进一步汇总计算，所有须要的列的都能够从视图输入中计算出来。

例如查问

select c_nationkey, sum(l_quantity*l_extendedprice)
from lineitem, orders, customer
where l_orderkey = o_orderkey
and o_custkey = c_custkey
group by c_nationkey

物化视图

create materialized view mview3
enable Query rewrite as
select o_custkey, count_big() as cnt, sum(l_quantityl_extendedprice) as revenue
from lineitem, orders
where l_orderkey = o_orderkey
group by o_custkey

查问能够被改写为

select c_nationkey, sum(revenue)
from customer join mview3 on c_custkey = o_custkey
group by c_nationkey

如果分组列表不同，只能改写 Group By 在查问最内部的状况，否则无奈进行进一步聚合，无奈满足第二个改写要求。只有特定的聚合函数反对进一步聚合，常见的聚合函数有 MIN，MAX，SUM，COUNT。

例如如下查问

select o_custkey, count() as cnt, sum(l_quantityl_extendedprice) as revenue
from lineitem, orders
where l_orderkey = o_orderkey
group by o_custkey

物化视图

create materialized view mview4
enable Query rewrite as
select o_custkey, l_partkey, count() as cnt, sum(l_quantityl_extendedprice) as revenue
from lineitem, orders
where l_orderkey = o_orderkey
group by o_custkey, l_partkey

能够被改写为

select c_nationkey, sum(cnt) as cnt, sum(revenue) as revenue
from mview4
where c_custkey = o_custkey
group by o_custkey

如果聚合函数在视图中不存在，能够通过一些规定进行计算，例如从 SUM(x) 和 COUNT(x) 计算出 AVG(x)，从 SUM(x) + SUM(y) 中计算出 SUM(x + y)，这些依赖表达式改写查看中启发式的规定。
高级改写规定

如果物化视图和查问不满足上一节的改写规定，还能够通过其余规定进行转换。

Join 弥补

如果只思考 Inner Join，视图和查问的 Join 关系 不统一有两种状况：查问比视图蕴含更多的联接，或者视图蕴含更多的联接。

如果查问比视图蕴含更多的表，咱们能够简略把短少的 Join 增加在视图之上，使其满足改写要求。Join 条件会通过谓词改写正确的增加进来，优化器也能够通过后续规定调整计划。

Join 弥补使优化器能够更早的进行匹配改写，缩小改写的尝试次数。

例如如下查问

select c_custkey, c_name, l_orderkey,l_partkey, l_quantity
From lineitem, customer, orders
Where l_orderkey = o_orderkey
And o_custkey = c_custkey
Where l_orderkey between 1000 and 1500
And l_shipdate = l_commitdate

物化视图

Create materialized view mview5
Enable query rewrite as
Select l_orderkey,l_partkey, l_quantity
From lineitem, orders
Where l_orderkey = o_orderkey
And o_orderkey >= 500

能够被改写为

Select l_orderkey, l_partkey, l_quantity
From mview5 join customer on o_custkey = c_custkey
Where l_orderkey between 1000 and 1500
And l_shipdate = l_commitdate

Join 打消

如果一个 Join 呈现在视图中然而没有呈现在查问中，能够尝试应用 Join 打消规定。Join 打消是优化器优化中的一项常见办法，如果 Join 满足以下 5 个条件，则表 T1 在与表 T2 的联接中时不变的：

1. 联接条件是一个简略的等值条件
2. T1.fk 是 T2.pk 的外键
3. T1.fk 满足非 null 束缚
4. T2 没有任何的谓词
5. T2 在与 T1 以外的表的联接是不变的

这意味着表 T1 在与 T2 的 Join 关系不会影响 T1 的后果，视图中的 T2 能够在 Join Graph 中疏忽。不变联接的存在容许在根底物化视图上创立更大的并集或超集，从而容许物化视图蕴含更大的预计算，也能够改写更多的查问。

例如查问

Select l_orderkey, l_partkey, l_quantity
From lineitem
Where l_orderkey between 1000 and 1500
And l_shipdate = l_commitdate

物化视图

Create materialized view mview6
Enable query rewrite as
Select c_custkey, c_name, l_orderkey,l_partkey, l_quantity
From lineitem, orders, customer
Where l_orderkey = o_orderkey
And o_custkey = c_custkey
And o_orderkey >= 500

能够被改写成

select l_orderkey, l_partkey, l_quantity
from mview6
where l_orderkey between 1000 and 1500
and l_shipdate = l_commitdate

Join 派生

如果存在 Outer Join，视图和查问 Join 关系不统一，能够尝试利用 Join 派生性，从物化视图中的联接从新计算查问中的联接。例如，能从物化视图中的 left Outer Join 的后果里，计算 Inner Join，Anti Join 的后果，Inner Join 又能进一步计算 Semi Join，这样就能应用物化视图过滤某些行来答复不同具备不同 Join 关系的查问。

Join 派生能够扩大改写的范畴，容许优化器将基于物化视图的改写与解关联的规定规定联合，改写带有 IN，EXISTS 等的查问，也能够防止其余优化规定对 Join 的调整，例如 EliminateOuterJoin 和 PredicatePushDown 可能会将 outer join 优化成 inner join。

Union 改写

物化视图只蕴含一部分查问所需的数据，也能够用于查问改写。在很多场景下，物化视图不会也无奈存储全副的数据。

一个典型的状况是，数据在一直的写入，然而写入只产生在最近一段时间内。在继续刷新的表上构建全量物化视图，这可能导致因为数据插入视图频繁刷新，产生昂扬的刷新老本，甚至视图因为继续刷新而齐全不可用。更好的做法是构建一个 T+1 条件刷新的物化视图，存储不变的数据，能够升高刷新老本。

另一种常见的状况是，数据仓库存储全量的数据，而查问集中在最近几个月的数据，构建全副数据的物化视图老本过于昂扬，物化视图只构建最近几个月数据，也能改写绝大多数查问。

Union 改写会尝试应用视图答复局部查问，缩小查问中实时计算的数据量。

Union 改写能够进一步利用 Aggregation 改写，反对应用物化视图局部数据答复聚合查问。

例如查问

select l_orderkey, l_partkey, l_quantity
from lineitem
where l_orderkey l_orderkey > 500 and l_orderkey <= 1500
and l_shipdate = l_commitdate

物化视图

create materialized view mview8
enable query rewrite
as
select l_orderkey, l_partkey, l_quantity
from lineitem
where l_orderkey > 1000
and l_shipdate = l_commitdate

改写后果

select l_orderkey, l_partkey, l_quantity
from lineitem
where l_orderkey > 500 and l_orderkey <= 1000
and l_shipdate = l_commitdate
union
select o.shippriority
from mview8
where l_orderkey > 1000 and l_orderkey <= 1500

查问改写的实现

视图改写通常有三种查问改写的实现形式：

1. 基于语法的改写
2. 基于规定的改写
3. 基于构造的改写

基于语法的改写

文本匹配或者语法匹配是最简略的改写办法，将查问的文本与物化视图的文本或语法树进行比拟，齐全匹配能够进行改写。这种改写只能匹配残缺的查问语句或子语句，轻微的变动就会导致查问无奈改写，实用的范畴很小。基于语法的改写尽管简略，然而效率很高，改写的老本能够忽略不计。

基于规定的改写

基于规定的改写和其余优化器规定雷同，针对不同 Pattern 的查问和视图编写不同的规定，寻找等价的代替关系树。

最简略的一条规定就是间接比拟子查问和视图的打算，如果雷同就能改写。高级的改写规定不须要物化视图等同于被替换的打算，会尝试计算弥补谓词，构建等价查问表达式。例如 Join 改写，比拟 Join 查问的子表达式是否和视图 Join 的某个子表白雷同或者是否从中计算出来，每一个 Join 子表达式都存在映射关系，最初查看弥补表达式是否从视图中计算失去。

基于规定的改写能够实现大量重写，实现也比较简单，改写匹配速度快，然而也存在局限性。这种改写依赖转换规则来寻找等价关系，因而须要穷举所有可能的转换关系来实现简单视图的重写。一些简单的视图不可能穷举所有的等价关系，例如存在很多的 Join 联接或者简单的 Project 关系，基于规定的改写实用的范畴取决于规定的数量。

基于构造的改写

基于构造的改写与基于规定的改写相同，通过提取查问中的特色，应用一套规定进行匹配改写。优化器将查问示意为 SPJG 规范模式 (Join-Select-Project-GroupBy)，提取查问中的 Join，Projects，Filters，Grouping 和 Aggregations 五种表达式，别离与物化视图对应的表达式进行匹配和改写。

这个办法是由微软在 2001 年 SIGMOD 论文《Optimizing queries using materialized views: A practical, scalable solution》系统化的提出。这种办法能够改写蕴含能够改写蕴含 Join，Filter，Project 的任意查问的办法，使用一系列的步骤匹配并失去弥补表达式。还能够进一步改写含有 Aggreagtion 的查问，在须要时增加 Aggregation 节点返回进一步汇总的后果。

基于构造的改写很容易扩大，例如改写 Outer Join 和子查问等，能够实现简直全副的改写。然而搜寻老本较高，尤其是在查问简单，改写尝试次数很多的状况下。

对于咱们

AnalyticDB 是阿里巴巴自主研发、惟一通过超大规模以及外围业务验证的 PB 级云原生数据仓库。

AnalyticDB 在去年 10 月份推出了物化视图性能，目前的版本反对定时全量刷新和查问改写。AnalyticDB 残缺实现了基于文本的匹配和基于构造的匹配，能够改写蕴含 Join，Filter，Project，Group By 的任意查问，反对 Aggregation Rollup，Union，Subquery，Outer join 等高级改写规定。ADB 会在将来的几个版本内上线基于规定的匹配，进步改写的效率。并在将来扩大更多的改写伎俩，例如 Grouping sets 改写反对，使物化视图有传统数仓中 Cube 的能力。

原文链接
本文为阿里云原创内容，未经容许不得转载。