PostgreSQL 物化视图-未完纪

物化视图是将查询结果作为表保存在数据库中的一种方式，可以像普通表一样查询。通常用于避免反复执行高成本查询，或者保存经常需要使用的数据。

物化视图的优点

提高性能。由于数据已经预先计算，物化视图通常比现场执行连接或汇总查询响应更快。无论原查询多复杂、涉及多少张表，PostgreSQL 都会将结果保存为一张简单的表。

简化查询编写。物化视图可以与其他数据直接连接，从而简化复杂查询。对于经常参与连接的数据，它尤其有用，可以避免在多个不同查询中重复编写相同连接逻辑。

物化视图的缺点

结果是静态的。必须刷新物化视图才能纳入新数据，可以通过 cron 定期执行。但对于银行业务等要求数据精确到当前秒的场景，物化视图并不理想。

占用磁盘空间。物化视图和普通表一样存储在数据库磁盘上，相当于将部分数据保存了两份。

适合使用物化视图的场景

经常需要汇总数据。

不要求实时精确的数据。

电子商务示例

假设一个电商网站使用 PostgreSQL 数据库，包含 products、orders 和 product_orders 三张表。

SELECT * FROM products LIMIT 10;
SELECT * from orders LIMIT 10;
SELECT * from product_orders LIMIT 10;

网站希望显示每件商品最近被购买的次数。营销团队认为，这些信息有助于买家决策。问题是，目前只保存独立的商品和订单记录，没有现成的商品订单总数。我们不希望每位访客打开商品页面时，数据库都重新计算一次。这个数据也不需要绝对实时，足够接近实际即可。

创建物化视图的 SQL

下面的物化视图示例按 SKU 汇总已发货数量，并按 qty 降序排列，使销售最多的 SKU 显示在前面。

CREATE MATERIALIZED VIEW recent_product_sales AS
SELECT p.sku, SUM(po.qty) AS total_quantity
FROM products p
JOIN product_orders po ON p.sku = po.sku
JOIN orders o ON po.order_id = o.order_id
WHERE o.status = 'Shipped'
GROUP BY p.sku
ORDER BY 2 DESC;

创建物化视图后，通常还应为它建立索引，以避免数据库全表扫描。

CREATE INDEX sku_qty ON recent_product_sales(total_quantity);

使用物化视图

可以像查询其他表一样查询这个视图。

SELECT * FROM recent_product_sales;

也可以快速查看销售量最高的 10 件商品，无需另外编写子查询进行求和或排名。

SELECT sku
FROM recent_product_sales
LIMIT 10;

物化视图还可以参与其他查询。下面的查询显示商品 SKU、名称、价格、促销价及近期销量。

SELECT
    p.sku,
    p.name,
    p.price,
    p.sale_price,
    COALESCE(rps.total_quantity, 0) AS recent_sales_quantity
FROM products p
LEFT JOIN recent_product_sales rps ON p.sku = rps.sku;

例如，可以将查询结果用于电商网站的热门商品列表。由于读取的是物化视图，每次有人查看列表时，不必重新执行高成本数据库查询来计算销量。

如果有新数据，需要更新视图，可以执行：

REFRESH MATERIALIZED VIEW recent_product_sales;

这种刷新方式会阻塞物化视图上的并发 SELECT。若要在线刷新，应使用 CONCURRENTLY，但必须先为视图创建唯一索引：

CREATE UNIQUE INDEX recent_product_sales_sku_uidx
  ON recent_product_sales (sku);

REFRESH MATERIALIZED VIEW CONCURRENTLY recent_product_sales;

原文：Materialized Views。作者／来源：Crunchy Data。本文依据所列原文整理为中文，代码、命令与配置示例保留原文。

文章版权归作者所有，未经允许请勿转载。

THE END

技术译文

PostgreSQL 物化视图