PostgreSQL 中的物化视图

如果您经常使用 SQL（最好是 PostgreSQL）来查询数据库，那么您很可能写过不少复杂查询来回答关键问题。有时，您还会频繁地在工作中使用这些查询的结果。多次运行复杂查询可能会很昂贵。如果有一种方法可以缓存复杂查询的结果，从而提升性能，会怎样？答案就是 物化视图。

您可能会想到，还有简单的 视图 也能存储查询结果。但简单视图与物化视图在多方面有所不同。以下是 PostgreSQL 官方文档 对简单视图的说明：

视图并不会以物理形式物化。相反，每次在查询中引用该视图时，都会运行视图中的查询。

这意味着简单视图并不会以物理形式存储在数据库中。每次在查询中使用视图，都会执行创建该视图的查询。这正是简单视图在访问时间方面稍显低效的原因。

另一方面，物化视图提供了更大的灵活性，允许您将视图以物理形式持久化到数据库中。您可以像操作简单视图一样操作物化视图（但访问时间更短）。在本教程的其余部分，您将学习 PostgreSQL 中的物化视图。在他的某场网络研讨会中，DataCamp 首席数据科学家 David Robinson 展示了他如何使用物化视图高效存储复杂查询的结果。

注意：为便于跟进本教程，您应当熟悉 Joining Data in SQL 课程中涵盖的内容。

初始数据库设置

最好先在 PostgreSQL 中设置一个数据库，并在其中创建一个基础表，以便您能够有效地对其进行查询。这也会帮助您完成本教程后续部分。您可以恢复这个数据库备份，其中包含了本教程将要使用的表。如果您想学习如何在 PostgreSQL 中恢复数据库备份，可以参考本教程的第一部分。

如果您已成功恢复备份，应该能在数据库中看到一个名为 international_debt 的表（如果您还没有数据库，需要先创建一个）。我们快速查看一下该表的前几行（使用一个简单的 select 查询即可）——

该表包含全球不同国家在本年度不同类别（参见列 indicator_name、indicator_code）下的债务统计信息。debt 列显示某个国家在某个类别下的债务金额（以美元计）。这些数据属于经济学领域，常用于分析不同国家的经济状况。数据来自世界银行。

现在您已成功设置数据库，可以继续进入本教程的下一部分。

创建物化视图

让我们为下面的查询创建一个物化视图——

select country_name,
sum(debt) as total_debt
from international_debt
group by country_name order by country_name;

该查询返回各国名称及其债务总额（按字母顺序）。查询输出大致如下——

现在创建物化视图——

CREATE MATERIALIZED VIEW country_total_debt
as
  select country_name,
  sum(debt) as total_debt
  from international_debt
  group by country_name order by country_name;

您应会看到如下成功信息：Query returned successfully: 124 rows affected, 242 ms execution time.

刷新物化视图

您也可以在创建物化视图时通过指定 WITH NO DATA 选项，指示 PostgreSQL 不向该物化视图加载任何数据——

CREATE MATERIALIZED VIEW country_total_debt_2
as
  select country_name,
  sum(debt) as total_debt
  from international_debt
  group by country_name order by country_name
WITH NO DATA;

您会看到这样的信息：Query returned successfully with no result in 41 ms. 请注意这两条输出信息之间的差异。如果添加了 WITH NO DATA 选项，就会创建一个空的物化视图。您无法查询该物化视图。不过，您可以通过执行以下命令来填充物化视图：

REFRESH MATERIALIZED VIEW country_total_debt_2;

查询物化视图

此时，您应该已经创建了两个物化视图（country_total_debt、country_total_debt_2）。让我们用其中任意一个执行一个简单的 select 查询——

select * from country_total_debt_2;

查询应返回类似如下的结果——

输出与我们封装进物化视图的查询结果完全相同。您也可以在物化视图上运行带聚合函数的查询——

select max(total_debt) from country_total_debt_2;

您应会得到——

删除物化视图

物化视图可以像在 PostgreSQL 中删除表那样被删除。您只需在 Drop 之后指定 Materialized View——

Drop Materialized View country_total_debt_2;

若要将 MySQL 与其第二大热门竞争对手进行比较，请查看我们的深度对比文章 PostgreSQL 与 MySQL。

结论

本教程到此结束，感谢您读到最后。在本教程中，您学习了 PostgreSQL 中的物化视图，以及如何对其进行操作。由于其高效性，物化视图在数据仓库中被广泛使用。如果您希望学习更复杂的查询，可以查看以下 DataCamp 课程：

• Intermediate SQL
• Reporting in SQL