概述

本节主要分享物化视图(Materialized View)的基础知识，分为原理机制，创建，数据刷新以及删除等内容。

在原理机制部分，会对比普通视图(View)与物化视图(Materialized View)的不同点，分析它们的执行机制差异；后续部分将结合实际案例，展示如何创建、使用、刷新和删除物化视图。

原理机制

1. 普通视图 (View)

普通视图仅记录视图定义时的查询语句。当查询视图时，SQL会被重写，用视图定义的语句进行替换，然后执行查询，实际是从数据表中查询。

执行流程如下：

SELECT * FROM vw_employee; -> 查询优化器 -> 重写为 SELECT * FROM (SELECT * FROM employee) AS vw_employee; -> 执行器 -> 返回结果

每次从视图查询时，都会执行一次视图定义的查询语句，视图本身不会存储数据。

2. 物化视图 (Materialized View)

物化视图在定义时会将数据保存一份，并有自己存储的数据，但不会自动与主表进行数据同步，需要手动进行同步。

执行流程如下：

SELECT * FROM mvw_employee; -> 查询优化器 -> 执行器 -> 返回结果

如果对某视图访问频繁，结果集只需生成一次，之后的查询都是直接查结果集，大大节省时间，尤其是对于大数据分析。

由于物化视图会保存源表的数据，所以它不能被修改，以避免数据的分叉。

物化视图创建

语法说明

物化视图的创建语法如下：

CREATE MATERIALIZED VIEW view_name
AS
query_sql
WITH [NO] DATA;

物化视图创建语法与普通视图类似，区别有：

使用关键字 MATERIALIZED 来指示这是一个物化视图。
定义的最后使用 WITH DATA 或 WITH NO DATA 来指定创建后的物化视图是否需要同步数据。不指定时默认是 WITH DATA。

使用案例

下面通过一个案例展示如何使用物化视图来提高大数据分析的查询效率。

初始化数据

创建产品与订单表：

-- 创建产品表
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    category VARCHAR(255)
);

-- 创建订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    quantity INT NOT NULL,
    region VARCHAR(255) NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

存储过程：生成随机字符串

CREATE OR REPLACE FUNCTION generate_random_string(length INTEGER)
RETURNS VARCHAR 
LANGUAGE plpgsql
AS $
DECLARE
    result VARCHAR(255) := '';
    chars TEXT[] := ARRAY['a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'];
BEGIN
    FOR i IN 1..length LOOP
        result := result || chars[1 + (random() * (array_length(chars, 1) - 1))::int];
    END LOOP;
    RETURN result;
END $;

生成10万条商品数据和100万条订单数据：

-- 生成商品数据
INSERT INTO products (product_id, product_name, price, category)
SELECT
    id, generate_random_string(10),
    (random() * 1000)::numeric(10,2),
    'Category' || (random() * 10)::int
FROM generate_series(1, 100000) AS id;

-- 生成订单数据
INSERT INTO orders (order_id, product_id, quantity, region, order_date)
SELECT
    id, 1 + random()*99999,
    (random() * 10000)::int,
    'region' || (random() * 22)::int,
    current_date - floor((random() * 360))::int rand_date
FROM generate_series(1, 1000000) AS id;

物化视图使用

创建物化视图：

CREATE MATERIALIZED VIEW mvw_region_sales AS
SELECT region, SUM(quantity) AS total_sales
FROM orders
GROUP BY region;

查询各区域的销量：

SELECT * FROM mvw_region_sales;

经过测试，多次查询销量，可以看到查询速度显著加快。

物化视图刷新数据

当主表数据变化后，需要手动刷新物化视图以同步数据。例如，在订单表中新增一条记录然后刷新物化视图：

INSERT INTO orders VALUES (1000001, 1000, 10000, 'region100', '2023-12-03');

REFRESH MATERIALIZED VIEW mvw_region_sales;

查看新数据：

SELECT * FROM mvw_region_sales WHERE region = 'region100';

如需并发刷新物化视图，可以使用 CONCURRENTLY 关键字，但前提是物化视图有唯一性索引列：

REFRESH MATERIALIZED VIEW CONCURRENTLY mvw_region_sales;

物化视图删除

删除物化视图的SQL语法：

DROP MATERIALIZED VIEW view_name;

如需级联删除，可以增加 CASCADE 关键字。

总结

在大数据分析场景下，物化视图能高效地缓存中间结果，避免重复计算，显著提升查询效率。

四时宝库

程序员的知识宝库

PostgreSQL初级教程:使用物化视图实现高效大数据查询分析

目录

概述