亚马逊AWS官方博客

使用 Amazon Redshift 物化视图,加快查询执行速度

在 AWS,我们为有能力构建前沿的虚拟化技术,简化对网络、计算资源或对象存储等云服务的管理和访问而感到自豪。

在关系数据库管理系统 (RDBMS) 中,视图就是应用于表的虚拟化技术:表示数据库查询结果的虚拟表。在设计架构时,我们常常使用视图来表示数据的子集、汇总数据(例如聚合或转换数据),或者用来简化跨多个表的数据访问。使用数据仓库(如 Amazon Redshift)时,视图可以为一些商业智能 (BI) 工具(例如 Amazon QuickSightTableau)简化对来自多个表的聚合数据的访问。

视图提供了易用性和灵活性,但无法加快数据访问的速度。在您的应用程序每次访问视图时,数据库系统都必须评估代表该视图的底层查询。在性能至关重要的应用中,数据工程师会转而使用 create table as (CTAS)。CTAS 是一个由查询定义的表。该查询在创建表时执行,可供应用程序像正常的表一样使用,但缺点是 CTAS 数据集在底层数据更新时不会刷新。而且,CTAS 定义并非存储在数据库系统中。我们无从知晓一个表是否由 CTAS 创建,因而很难跟踪哪个 CTAS 需要刷新,哪个已经是最新的。

今天我们介绍用于 Amazon Redshift物化视图。物化视图 (MV) 是一个包含查询数据的数据库对象。物化视图像是视图的缓存,它不是在运行时构建和计算数据集,而是在创建的时候预先计算、存储和优化数据访问。数据如同常规表数据一样,随时可供查询使用。

在分析查询中使用物化视图能够以几何倍数加快查询执行速度,因为定义该物化视图的查询已经执行过,数据随时可供数据库系统使用。

对于可以预见并反复使用的查询,物化视图特别有用。应用程序不必对大型的表执行资源密集型查询,而是可以查询存储在物化视图中的预计算数据。

当基表中的数据发生更改时,可通过发出 Redshift SQL 语句“refresh materialized view”来刷新物化视图。发出刷新语句后,物化视图将包含与常规视图返回的数据相同的数据。刷新可以增量执行,也可以是完全刷新(重新计算)。可能的情况下,Redshift 增量将刷新自上次刷新物化视图后基表中发生更改的数据。

下面我们来看它的工作原理。我创建一个示例架构来存储销售信息:每一个销售交易以及销售发生时有关商店的详情。

为了查看每个城市的总销售额,我用 SQL 语句 create materialized view 创建一个物化视图。然后,连接到 Redshift 控制台,选择查询编辑器,并键入以下语句创建物化视图 (city_sales),从而连接两个表中的记录并按城市 (group by city) 聚合销售额 (sum(sales.amount)):

CREATE MATERIALIZED VIEW city_sales AS (
  SELECT st.city, SUM(sa.amount) as total_sales
  FROM sales sa, store st
  WHERE sa.store_id = st.id
  GROUP BY st.city
);

得到的架构如下:

现在我可以像常规视图或表一样查询该物化视图,发出类似“SELECT city, total_sales FROM city_sales”的语句获得以下结果。两个表之间的连接以及聚合(sumgroup by)已经计算过,因此扫描的数据量显著减少。

当底层基表中的数据发生更改时,物化视图不会自动反映这些更改。可根据需要,使用 SQL 命令 refreshmaterialized view 来刷新物化视图中存储的数据,以反映基表中的最新更改。我们来看一个实际示例:

!-- 在 sales 基表中添加一行
INSERT INTO sales (id, item, store_id, customer_id, amount)
VALUES(8, 'Gaming PC Super ProXXL', 1, 1, 3000);

SELECT city, total_sales FROM city_sales WHERE city = 'Paris'

city |total_sales|
-----|-----------|
Paris|        690|

!-- 计算中纳入了新的销售记录!

!-- 刷新物化视图
REFRESH MATERIALIZED VIEW city_sales;

SELECT city, total_sales FROM city_sales WHERE city = 'Paris'

city |total_sales|
-----|-----------|
Paris|       3690|

!-- 视图中现在包含最新的销售数据

这个简单演示的完整代码在此以 gist 形式提供

即日起,您可以在所有 AWS 区域开始使用物化视图

使用物化视图无需对现有集群做任何更改,您现在就可以开始创建物化视图,无任何额外费用。

祝大家构建顺利!