PostgreSQL 實體化視圖提供一個強大的機制，通過預先計算并將查詢結果集存儲為物理表來提高查詢性能。本教程將使用?DVD Rental Database?數據庫作為演示例子，指導你在 PostgreSQL中創建實體化視圖。

了解實體化視圖

實體化視圖是查詢結果集的快照，以物理表的形式存儲。與常規視圖不同，實體化視圖是虛擬的，每次被引用時都會執行底層查詢，實體化視圖能持久化數據，并通過定期刷新來提高查詢性能。

相比于頻繁的查詢執行，實體化視圖對于底層數據變化不頻繁的場景是非常有用的。這使得它們成為報告、數據倉庫和實時數據要求不嚴格的場景的理想選擇。

設置 DVD Rental 數據庫

在深入探討實體化視圖前，讓我們先來設置 DVD Rental 數據庫。它是 MySQL 常用的 Sakila 數據庫樣例的 PostgreSQL 版本。你可以從官方 PostgreSQL 教程網頁（PostgreSQL Sample Database）上下載 DVD Rental 數據庫。

數據庫文件為 ZIP 格式（dvdrental.zip）,所以在加載數據庫樣例到 PostgreSQL 數據庫服務器前，你需要將它解壓縮為 dvdrental.tar。解壓為 .tar 文件后，創建名為“dvdrental”的新數據庫，然后執行 pg_restore 命令以將 .tar 文件內容填充到 dvdrental 數據庫中。

pg_restore -U postgres -d dvdrental D:\sampledb\postgres\dvdrental.tar

將上面的路徑替換為你系統上指向已解壓的 dvdrental.tar 路徑。

你可以點擊?此處?查看詳細的安裝說明。

創建實體化視圖

假設我們想創建一個實體化視圖，顯示每個電影類別產生的總收入。以下是操作步驟：

• 連接你的 PostgreSQL 數據庫
• 通過下面的 DML 語句創建實體化視圖：

CREATE MATERIALIZED VIEW mv_category_revenue AS
SELECTc.name AS category,SUM(p.amount) AS total_revenue
FROMcategory cJOIN film_category fc ON c.category_id = fc.category_idJOIN film f ON fc.film_id = f.film_idJOIN inventory i ON f.film_id = i.film_idJOIN rental r ON i.inventory_id = r.inventory_idJOIN payment p ON r.rental_id = p.rental_id
GROUP BYc.name;

例子中，我們將 DVD Rental 數據庫中多張表進行聯合，以計算每個電影類別的總收入。

在?Navicat For PostgreSQL（或?Navicat Premium）16：

• • 點擊“實體化視圖”按鈕，將顯示實體化對象列表，在對象工具欄中點擊“+ 新建實體化視圖”，將打開視圖設計器：

• • 在定義編輯器中輸入上述語句的 SELECT 部分：

• • 我們可以點擊“預覽”按鈕來檢查語句是否可以如期運行：

• • 點擊“保存”按鈕，完成創建新的實體化視圖。這將會彈出一個提示輸入實體化視圖名稱的對話框，根據上面的 CREATE MATERIALIZED VIEW 語句中的名稱，我們將它命名為“mv_category_revenue”：

• • 點擊對話框中的保存按鈕，Navicat 會將實體化視圖名稱從“無標題”改為我們提供的名稱。同時也會將新建的實體化視圖添加到左側導航窗格的實體化視圖列表中：

總結

在非實時數據場景下，PostgreSQL 實體化視圖是一個優化查詢性能的有用工具。通過預計算和存儲復雜查詢的結果，實體化視圖可以顯著提高分析和報告任務的響應時間。本教程中，我們學習了如何為 DVD Rental 數據庫創建實體化視圖，并展示了在真實場景中的實際應用。

往期回顧?

Navicat 16 已支持 Redis

Navicat 16 已支持華為云 GaussDB

Navicat 16 已支持螞蟻集團 OceanBase 全線數據庫

Navicat 常見技術教程

Navicat 入選中國信通院發布的《中國數據庫產業圖譜（2023）》?