跳至主要內容
日沐 SunBathe

Appearance

檢視表與物化檢視 ​

檢視表讓你把複雜的查詢封裝起來，像操作真實表一樣使用。本篇介紹普通檢視和物化檢視的用法與差異。

一、 檢視表 (VIEW) ​

檢視表是「儲存的查詢」，不存實際資料。

建立檢視表 ​

sql
CREATE VIEW active_users AS
SELECT id, name, email, created_at
FROM users
WHERE is_active = true AND deleted_at IS NULL;

-- 使用
SELECT * FROM active_users WHERE created_at > '2024-01-01';

優點 ​

  1. 簡化複雜查詢：封裝 JOIN 和條件
  2. 權限控制：只暴露部分欄位
  3. 邏輯抽象：業務邏輯集中管理

範例：訂單統計 ​

sql
CREATE VIEW order_summary AS
SELECT
    o.id AS order_id,
    c.name AS customer_name,
    c.email AS customer_email,
    o.total,
    o.status,
    o.created_at,
    COUNT(oi.id) AS item_count
FROM orders o
JOIN customers c ON o.customer_id = c.id
LEFT JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id, c.name, c.email;

-- 直接查詢
SELECT * FROM order_summary WHERE status = 'pending';

二、 可更新檢視 (Updatable View) ​

符合條件的檢視可以直接 INSERT/UPDATE/DELETE。

條件 ​

  • 只來自單一表
  • 沒有聚合函數、DISTINCT、GROUP BY、UNION
  • 沒有視窗函數
  • 沒有 LIMIT/OFFSET
sql
-- 這個檢視可更新
CREATE VIEW us_customers AS
SELECT * FROM customers WHERE country = 'US';

-- 可以直接更新
UPDATE us_customers SET name = 'John' WHERE id = 1;
INSERT INTO us_customers (name, country) VALUES ('Jane', 'US');

WITH CHECK OPTION ​

確保透過檢視插入的資料符合檢視條件：

sql
CREATE VIEW us_customers AS
SELECT * FROM customers WHERE country = 'US'
WITH CHECK OPTION;

-- 這會失敗
INSERT INTO us_customers (name, country) VALUES ('Pierre', 'FR');
-- ERROR: new row violates check option for view "us_customers"

三、 物化檢視 (MATERIALIZED VIEW) ​

物化檢視實際儲存查詢結果，犧牲即時性換取效能。

建立物化檢視 ​

sql
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(amount) AS total_sales,
    COUNT(*) AS order_count,
    AVG(amount) AS avg_order
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- 查詢（非常快，直接讀取預計算結果）
SELECT * FROM monthly_sales WHERE month >= '2024-01-01';

刷新資料 ​

物化檢視不會自動更新，需要手動刷新：

sql
-- 完全刷新（阻塞讀取）
REFRESH MATERIALIZED VIEW monthly_sales;

-- 並行刷新（不阻塞，需要唯一索引）
CREATE UNIQUE INDEX idx_monthly_sales ON monthly_sales(month);
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

刷新策略 ​

策略方式適用
手動需要時才刷新不常變動的資料
定時pg_cron 定時任務報表、統計
事件觸發器觸發刷新即時性要求中等
sql
-- 使用 pg_cron 定時刷新
SELECT cron.schedule('refresh_sales', '0 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales');

四、 VIEW vs MATERIALIZED VIEW ​

特性VIEWMATERIALIZED VIEW
儲存資料否是
查詢速度即時執行直接讀取
資料即時性即時需刷新
佔用空間無有
可建索引否是
可更新視情況否

選擇建議 ​

情況推薦
簡化查詢、權限控制VIEW
複雜計算、報表MATERIALIZED VIEW
即時性要求高VIEW
查詢效能優先MATERIALIZED VIEW

五、 遞迴檢視 ​

sql
CREATE RECURSIVE VIEW employee_hierarchy (id, name, manager_id, level) AS
    SELECT id, name, manager_id, 1
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id;

SELECT * FROM employee_hierarchy ORDER BY level;

六、 實用範例 ​

權限控制 ​

sql
-- 隱藏敏感欄位
CREATE VIEW public_users AS
SELECT id, name, country, created_at
FROM users;
-- email、password 不暴露

GRANT SELECT ON public_users TO readonly_role;

版本化資料 ​

sql
-- 只顯示最新版本
CREATE VIEW current_documents AS
SELECT DISTINCT ON (document_id) *
FROM document_versions
ORDER BY document_id, version DESC;

多表聚合 ​

sql
CREATE MATERIALIZED VIEW product_stats AS
SELECT
    p.id AS product_id,
    p.name,
    COALESCE(SUM(oi.quantity), 0) AS total_sold,
    COALESCE(AVG(r.rating), 0) AS avg_rating,
    COUNT(DISTINCT r.id) AS review_count
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN reviews r ON p.id = r.product_id
GROUP BY p.id, p.name;

CREATE UNIQUE INDEX idx_product_stats ON product_stats(product_id);

七、 效能優化 ​

為物化檢視建立索引 ​

sql
CREATE MATERIALIZED VIEW user_stats AS
SELECT user_id, COUNT(*) as order_count, SUM(amount) as total_spent
FROM orders GROUP BY user_id;

-- 加上索引
CREATE INDEX idx_user_stats_user ON user_stats(user_id);
CREATE INDEX idx_user_stats_spent ON user_stats(total_spent DESC);

部分刷新技巧 ​

PostgreSQL 不支援增量刷新，但可以用技巧模擬：

sql
-- 建立分時段的物化檢視
CREATE MATERIALIZED VIEW daily_sales AS
SELECT DATE(created_at) as date, SUM(amount) as total
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(created_at);

-- 只保留近期資料，刷新更快

八、 管理檢視 ​

查看檢視 ​

sql
-- 列出所有檢視
\dv

-- 查看檢視定義
\d+ view_name

-- SQL 查詢
SELECT viewname, definition FROM pg_views WHERE schemaname = 'public';

修改檢視 ​

sql
-- 重新建立
CREATE OR REPLACE VIEW active_users AS
SELECT id, name, email, phone, created_at  -- 新增 phone
FROM users
WHERE is_active = true;

刪除檢視 ​

sql
DROP VIEW IF EXISTS active_users;

-- 刪除物化檢視
DROP MATERIALIZED VIEW IF EXISTS monthly_sales;

九、 依賴管理 ​

檢視依賴底層表，修改表結構可能影響檢視：

sql
-- 查看檢視依賴
SELECT
    view_name,
    table_name
FROM information_schema.view_column_usage
WHERE view_name = 'order_summary';

-- CASCADE 刪除相關檢視
DROP TABLE orders CASCADE;
-- 會一起刪除依賴 orders 的檢視

總結 ​

概念說明
VIEW儲存的查詢，不存資料
MATERIALIZED VIEW儲存查詢結果，需刷新
Updatable View可透過檢視修改資料
WITH CHECK OPTION確保插入資料符合檢視條件
REFRESH CONCURRENTLY不阻塞的刷新

使用建議：

  • 簡單抽象用 VIEW
  • 複雜報表用 MATERIALIZED VIEW
  • 記得為物化檢視建立索引

進階挑戰 ​

  1. 建立一個物化檢視，預先計算每月銷售報表，並設定每日自動刷新
  2. 使用 REFRESH MATERIALIZED VIEW CONCURRENTLY 測試在高流量下的刷新效能
  3. 比較相同查詢在 VIEW 和 MATERIALIZED VIEW 下的執行計畫差異

延伸閱讀與資源 ​

← 上一章：觸發器實戰 | 返回專題首頁 | 下一章：資料表分區 →