如何自己免费做网站,网站开发笔试题,襄阳市建设工程质量监督站网站,做it的兼职网站简介#xff1a; 本文主要介绍什么是物化视图#xff0c;以及如何实现基于物化视图的查询改写。
作者#xff1a;阿里云数据库OLAP产品部 云曦
预计算和缓存是计算机领域提高性能以及降低成本的最常见的手段之一。对于那些经常重复的请求#xff0c;如果可以通过缓存回答…简介 本文主要介绍什么是物化视图以及如何实现基于物化视图的查询改写。
作者阿里云数据库OLAP产品部 云曦
预计算和缓存是计算机领域提高性能以及降低成本的最常见的手段之一。对于那些经常重复的请求如果可以通过缓存回答比重新计算结果或从速度较慢的数据存储中读取要快得多消耗更少的系统资源。在数据库领域中物化视图是预计算和缓存的自然体现。
本文主要介绍什么是物化视图以及如何实现基于物化视图的查询改写。
在第一部分我们会简单介绍物化视图并介绍基于物化视图的查询改写的用途。在第二部分我们将介绍查询优化器使用物化视图进行查询改写的匹配和改写过程。最后我们将介绍查询改写的几种实现方式及其优缺点。
背景介绍
物化视图
物化视图是将查询结果预先计算并存储的一张特殊的表。物化(Materialized) 这个词是相对于普通视图而言。普通视图较普通的表提供了易用性和灵活性但无法加快数据访问的速度。物化视图像是视图的缓存它不是在运行时构建和计算数据集而是在创建的时候预先计算、存储和优化数据访问并自动刷新来保证数据的实时性。 对于数据仓库物化视图最重要的功能就是查询加速。数据仓库中存在大量在大型表上执行复杂的查询这些查询会消耗大量资源和时间。物化视图可以通过预计算的结果回答查询消除昂贵的联接和聚合所带来的开销大幅度改善查询处理时间降低系统负载。对于可以预见并反复使用相同子查询结果的查询物化视图特别有用。
为了实现物化视图的潜力需要解决三个问题
物化视图选择选择哪些查询和表构建物化视图物化视图维护减少物化视图更新成本和时间物化视图运用如何使用物化视图加速查询。
本文主要从查询优化器的角度介绍使用物化视图加速查询背后的技术实现。
基于物化视图的查询改写
直接查询物化视图可以大幅度改善查询处理时间但是需要用户修改查询语句。使用物化视图加速查询的一个重要问题是如何采用一种系统化和自动化的方法自动使用物化视图回答查询。通过这种透明改写物化视图可以像索引一样添加或删除而不会影响已有 SQL。
查询改写使得物化视图具有广泛的用途
物化视图可以透明地改写查询无需改造业务就能使用物化加速查询方便地应用缓存公共结果集以及预计算等跨查询优化手段对于数据仓库数据集成场景物化视图可以物化外表结果屏蔽多个数据源的差异实现本地副本或读写分离查询改写结合自动构建物化视图实现数据库自治加速。
查询改写的问题定义
为了实现更大范围的改写查询改写通常被集成在优化器规则中。这有几个方面的好处。
首先查询改写可以利用优化器其他规则。依靠优化器其他规则将查询转换成标准和统一的形式简化匹配流程增加改写范围。其中比较重要的规则是列消除谓词下推解关联子查询等。解关联子查询规则允许物化视图对包含关联子查询的查询进行改写加速。
其次优化器可以递归每一个子树能否被某个视图进行改写。每个相关视图都会对每个子树产生多次改写一个查询语句不同部分可能被不同的视图改写。最终所有的改写都进入基于成本的选择器中与原始查询一起选出最优的查询计划。
查询改写算法只需要考虑给定的查询表达式和视图判断这个查询表达式能否从视图中计算出来然后从视图上构造一个等价的补偿表达式与原查询表达式等价。查询改写的范围应该尽可能大查询改写的目标是使用少量物化视图改写大量查询。最终由优化器选择出一个最优的查询计划。
查询改写检查
优化器通过多种方式来改写查询。最简单的一种情况是物化视图的查询与查询完全匹配符合这种查询重写类型的查询数量很少。为了进行更通用的匹配优化器会尝试使用各种规则构造一个等价表达式改写查询。
查询改写检查包含两个步骤改写匹配检查和构建等价表达式。一个查询能被视图回答需要满足下面两个条件
物化视图 Join 关系在查询中存在物化视图有足够的数据来回答查询
部分条件下即使不满足条件视图也可能会被用于改写。例如视图包含一部分查询所需要的数据可以使用物化视图回答部分查询剩下的数据从原始数据中计算。这部分改写检查会放在高级改写规则中进行介绍。
具体来说改写检查会依次进行 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 1000and 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_commitdateOutput 检查
Ouput 检查保证物化视图有足够的数据回答查询这包括 3 个步骤
验证查询所有输出需要的列能够从视图中计算出来优化器需要检查视图包含查询所有需要的行数也就是视图的谓词的范围大于查询的范围补偿谓词能否从视图中计算得来
等价关系
查询改写能够通过查询中的等价关系扩展改写的范围。因为等值条件具有传递性等价关系可以从等值条件或者代数关系推导而来。例如可以从 Adate_format(now(), %Y%m%D) 和 Bdate_format(now(), %Y%m%D)因为函数是确定性的可以得到 AB如果我们还有 inner join 的条件 BC可以进一步得到 ABC。
可能会有某些其他的条件可以推导出等价关系例如可以从 A2B3C5 推导出 C A B。相比等值关系寻找这种关系会使搜索过程更加复杂而且我们无法实现所有的可能的相等关系因此很少考虑这样的表达式。等价关系推导只搜索等值条件即使可能错过一些改写机会这样的浅层搜索可以保证速度。
表达式检查
为了确保查询输出的列和补偿谓词需要的列都能从视图中计算出来我们需要一种方法确定来自查询的表达式是否和视图中的表达式相等或者能从中计算出来。表达式检查无法纯粹从语法上实现两个表达式或者符号的文本相同并不说明他们关系相等。例如别名的存在就会破坏基于语法的检查。
表达式检查需要通过等价关系和表的对应关系推导而来。如果视图和查询中所有的表都是唯一的那么来自同一个表的列是等价的如果视图和查询中有表出现多次即 self join那么查询到视图的表的映射就存在多种可能每种可能都需要进行一次改写尝试。有了视图和查询之间列的对应关系和上一节的等价关系通过代数系统确定来自查询的表达式是否和视图中的表达式相等或者能否从中计算出来。
存在一些启发式的规则允许一个表达式从另外的表达式中计算出来。比如算数规则从 x 1 中计算出 x例从 SUM(x) 和 COUNT(x) 计算出 AVG(x)还存在一些 Function Dependency 规则例如时间函数可以通过返回的天的结果计算年等。
谓词检查
视图改写需要物化视图中存在查询所有需要的行数即视图的谓词的范围大于或等于查询的范围。使用 Wq 表示查询的谓词Wv表示视图的谓词我们需要检查 Wq Wv其中 表示 Wq 满足 Wv 的含义。
优化器提取所有的谓词并将他们转换为 CNF 的形式即 WP1 ^ P2 ^ ... ^ Pn。将谓词按照等值谓词范围谓词和剩余谓词进一步分为 W PE ^ PR ^ PU。PEPRPU 分别代表等值谓词范围谓词和剩余谓词。谓词检查变成了 PEq ^ PRq ^ PUq PEv ^ PRv ^ PUv由于正交的性质最终将问题分解成 PEq PEvPEq ^ PRq PRvPEq ^ 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 100Grouping 和 Aggregation 检查
如果视图和查询带有 GroupBy 或 Aggregation 函数需要进行额外检查
检查查询请求的数据分组是否与物化视图中存储的数据分组相同如果不同优化器会尝试对物化视图进行汇总。如果需要进一步汇总计算所有需要的列的都可以从视图输出中计算出来。
例如查询
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_quantity*l_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 在查询最外部的情况否则无法进行进一步聚合无法满足第二个改写要求。只有特定的聚合函数支持进一步聚合常见的聚合函数有 MINMAXSUMCOUNT。
例如如下查询
select o_custkey, count(*) as cnt, sum(l_quantity*l_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_quantity*l_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_commitdateJoin 消除
如果一个 Join 出现在视图中但是没有出现在查询中可以尝试使用 Join 消除规则。Join 消除是优化器优化中的一项常见方法如果 Join 满足以下5个条件则表 T1 在与表 T2 的联接中时不变的
联接条件是一个简单的等值条件T1.fk 是 T2.pk 的外键T1.fk 满足非 null 约束T2 没有任何的谓词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_commitdateJoin 派生
如果存在 Outer Join视图和查询 Join 关系不一致可以尝试利用 Join 派生性从物化视图中的联接重新计算查询中的联接。例如能从物化视图中的 left Outer Join 的结果里计算 Inner JoinAnti Join 的结果Inner Join 又能进一步计算 Semi Join这样就能使用物化视图过滤某些行来回答不同具有不同 Join 关系的查询。
Join 派生可以扩展改写的范围允许优化器将基于物化视图的改写与解关联的规则规则结合改写带有 INEXISTS 等的查询也可以避免其他优化规则对 Join 的调整例如 EliminateOuterJoin 和 PredicatePushDown 可能会将 outer join 优化成 inner join。
Union 改写
物化视图只包含一部分查询所需的数据也可以用于查询改写。在很多场景下物化视图不会也无法存储全部的数据。
一个典型的情况是数据在不断的写入但是写入只发生在最近一段时间内。在持续刷新的表上构建全量物化视图这可能导致因为数据插入视图频繁刷新产生高昂的刷新成本甚至视图因为持续刷新而完全不可用。更好的做法是构建一个 T1 条件刷新的物化视图存储不变的数据可以降低刷新成本。
另一种常见的情况是数据仓库存储全量的数据而查询集中在最近几个月的数据构建全部数据的物化视图成本过于高昂物化视图只构建最近几个月数据也能改写绝大多数查询。 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查询改写的实现
视图改写通常有三种查询改写的实现方式
基于语法的改写基于规则的改写基于结构的改写
基于语法的改写
文本匹配或者语法匹配是最简单的改写方法将查询的文本与物化视图的文本或语法树进行比较完全匹配可以进行改写。这种改写只能匹配完整的查询语句或子语句细微的变化就会导致查询无法改写适用的范围很小。基于语法的改写虽然简单但是效率很高改写的成本可以忽略不计。
基于规则的改写
基于规则的改写和其他优化器规则相同针对不同 Pattern 的查询和视图编写不同的规则寻找等价的替代关系树。
最简单的一条规则就是直接比较子查询和视图的计划如果相同就能改写。高级的改写规则不需要物化视图等同于被替换的计划会尝试计算补偿谓词构建等价查询表达式。例如 Join 改写比较 Join 查询的子表达式是否和视图 Join 的某个子表达相同或者能否从中计算出来每一个Join子表达式都存在映射关系最后检查补偿表达式能否从视图中计算得到。
基于规则的改写可以实现大量重写实现也比较简单改写匹配速度快但是也存在局限性。这种改写依赖转换规则来寻找等价关系因此需要穷举所有可能的转换关系来实现复杂视图的重写。一些复杂的视图不可能穷举所有的等价关系例如存在很多的 Join 联接或者复杂的 Project 关系基于规则的改写适用的范围取决于规则的数量。
基于结构的改写
基于结构的改写与基于规则的改写相反通过提取查询中的特征使用一套规则进行匹配改写。优化器将查询表示为 SPJG 标准形式 (Join-Select-Project-GroupBy)提取查询中的 JoinProjectsFiltersGrouping 和 Aggregations 五种表达式分别与物化视图对应的表达式进行匹配和改写。
这个方法是由微软在 2001 年 SIGMOD 论文《Optimizing queries using materialized views: A practical, scalable solution》系统化的提出。这种方法可以改写包含可以改写包含 JoinFilterProject 的任意查询的方法运用一系列的步骤匹配并得到补偿表达式。还可以进一步改写含有 Aggreagtion 的查询在需要时添加 Aggregation 节点返回进一步汇总的结果。
基于结构的改写很容易扩展例如改写 Outer Join 和子查询等可以完成几乎全部的改写。但是搜索成本较高尤其是在查询复杂改写尝试次数很多的情况下。 关于我们
AnalyticDB 是阿里巴巴自主研发、唯一经过超大规模以及核心业务验证的 PB 级云原生数据仓库。
AnalyticDB 在去年10月份推出了物化视图功能目前的版本支持定时全量刷新和查询改写。AnalyticDB 完整实现了基于文本的匹配和基于结构的匹配可以改写包含 JoinFilterProjectGroup By 的任意查询支持 Aggregation RollupUnionSubqueryOuter join 等高级改写规则。ADB 会在未来的几个版本内上线基于规则的匹配提高改写的效率。并在未来扩展更多的改写手段例如 Grouping sets 改写支持使物化视图有传统数仓中 Cube 的能力。
原文链接
本文为阿里云原创内容未经允许不得转载。
