【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. 對於頻繁更新的場景，評估聚簇帶來的維護開銷