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. 对于频繁更新的场景，评估聚簇带来的维护开销