【MVS】PG 聚簇物化视图 && 普通物化视图

PG 中 聚簇物化视图 && 普通物化视图 创建有何区别

创建过程比较，性能测试分析；

聚簇物化视图的作用与意义

聚簇物化视图（Clustered Materialized View）是PostgreSQL中一种特殊的物化视图，它在存储数据时按照指定的聚簇索引进行物理排序。这种设计具有以下优势：

• 查询性能提升：数据按照查询模式物理排序，减少I/O操作

• 预计算优势：预先计算并存储复杂查询结果

• 数据本地性：相关数据物理上相邻存储，提高缓存命中率

• 减少计算开销：避免重复执行相同查询

创建测试表并初始化数据

-- 创建测试表

CREATE TABLE sales (

    id SERIAL PRIMARY KEY,

    product_id INT,

    sale_date DATE,

    amount DECIMAL(10,2),

    region VARCHAR(50)

);

 

-- 插入测试数据(100万条)

INSERT INTO sales (product_id, sale_date, amount, region)

SELECT 

    (random()*100)::INT,

    CURRENT_DATE - (random()*365)::INT,

    (random()*1000)::DECIMAL(10,2),

    CASE (random()*5)::INT

        WHEN 0 THEN 'North'

        WHEN 1 THEN 'South'

        WHEN 2 THEN 'East'

        WHEN 3 THEN 'West'

        ELSE 'Central'

    END

FROM generate_series(1, 1000000);

创建普通物化视图 vs 聚簇物化视图

-- 创建普通物化视图

CREATE MATERIALIZED VIEW mv_sales_region AS

SELECT region, SUM(amount) as total_sales, COUNT(*) as transactions

FROM sales

GROUP BY region;

 

-- 创建聚簇物化视图(没有直接的CLUSTERED关键字，需要两步)

CREATE MATERIALIZED VIEW mv_clustered_sales_region AS

SELECT region, SUM(amount) as total_sales, COUNT(*) as transactions

FROM sales

GROUP BY region;

 

-- 为聚簇物化视图创建聚簇索引

CREATE INDEX idx_mv_clustered_region ON mv_clustered_sales_region(region);

 

-- 指定聚簇操作所依据的索引名。

CLUSTER mv_clustered_sales_region USING idx_mv_clustered_region;

备注： 1。指定聚簇操作所依据的索引名。执行过以后，系统就会自动记下该表是依据哪个索引进行聚簇排序的，后面再次执行聚簇操作时系统会自动使用该索引，所以索引名仅在首次聚簇操作时需要，后续不再需要。 2. 每次刷新过物化视图后，都需要重新对其进行一次聚簇排序操作。

性能对比测试

-- 测试普通物化视图查询

test=# EXPLAIN ANALYZE SELECT * FROM mv_sales_region WHERE region = 'North';

                                                 QUERY PLAN

------------------------------------------------------------------------------------------------------------

 Seq Scan on mv_sales_region  (cost=0.00..15.38 rows=2 width=158) (actual time=0.024..0.028 rows=1 loops=1)

   Filter: ((region)::text = 'North'::text)

   Rows Removed by Filter: 4

 Planning Time: 0.186 ms

 Execution Time: 0.063 ms

(5 rows)

 

Time: 1.607 ms

 

-- 测试聚簇物化视图查询

test=# EXPLAIN ANALYZE SELECT * FROM mv_clustered_sales_region WHERE region = 'North';

                                                     QUERY PLAN

---------------------------------------------------------------------------------------------------------------------

 Seq Scan on mv_clustered_sales_region  (cost=0.00..1.06 rows=1 width=158) (actual time=0.025..0.028 rows=1 loops=1)

   Filter: ((region)::text = 'North'::text)

   Rows Removed by Filter: 4

 Planning Time: 0.425 ms

 Execution Time: 0.065 ms

(5 rows)

 

Time: 1.414 ms

 

** 实验分析： **

1. 聚簇物化视图的查询计划会显示更少的I/O操作

2. 聚簇版本通常会显示更低的执行时间

3. 对于范围查询，聚簇版本的优势会更加明显

物化视图刷新时间对比

更新开销：聚簇物化视图的刷新操作需要额外时间进行重新聚簇

-- 刷新普通物化视图

 

test=# EXPLAIN ANALYZE REFRESH MATERIALIZED VIEW mv_sales_region;

                QUERY PLAN

-------------------------------------------

 Utility statements have no plan structure

(1 row)

 

Time: 0.577 ms

 

-- 刷新聚簇物化视图(需要先删除聚簇属性)

test=# ALTER MATERIALIZED VIEW mv_clustered_sales_region SET WITHOUT CLUSTER;

ALTER MATERIALIZED VIEW

Time: 2.670 ms

test=# EXPLAIN ANALYZE REFRESH MATERIALIZED VIEW mv_clustered_sales_region;

                QUERY PLAN

-------------------------------------------

 Utility statements have no plan structure

(1 row)

 

Time: 0.535 ms

 

-- 刷新后重新聚簇

test=# CLUSTER mv_clustered_sales_region USING idx_mv_clustered_region;

CLUSTER

Time: 10.694 ms

最佳实践建议

1. 为频繁查询且模式固定的报表类应用使用聚簇物化视图

2. 选择高选择性的列作为聚簇键

3. 在低峰期进行物化视图刷新

4. 对于频繁更新的场景，评估聚簇带来的维护开销