跳轉到

Materialized View

以 PostgreSQL 示範，

CREATE MATERIALIZED VIEW user_video_report AS
SELECT
    vwh.user_id,
    v.id AS video_id,
    v.name AS video_name,
    SUM(vwh.end_time - vwh.start_time) AS time_used
FROM video_watching_history vwh
INNER JOIN videos v ON v.id = vwh.video_id
GROUP BY 1,2,3
ORDER BY 4 DESC;

新增索引

CREATE INDEX user_video_idx ON user_video_report (user_id);

查詢

SELECT * FROM user_video_report WHERE user_id = 6;

刷新資料

REFRESH MATERIALIZED VIEW user_video_report;

如果擔心查詢當下，剛好這個表正在 refresh，則可以用 concurrently 的方式來 refresh，這樣 PostgresQL 就不會直接把整張表鎖住，而是會另外產生一份新的表來做比對，針對有更動的列來更新。但使用 concurrently 有一個前提 — 表內要有一或多個 unique index。

REFRESH MATERIALIZED VIEW CONCURRENTLY user_video_report;

效能優化方案比較

Server Side Caching

是 快取個別 API 查詢的結果。 我們可以針對個別 API 分別設定快取時間。如果同樣結果還在快取就跳過搜尋，直接回傳結果，進而提升 API 速度。

建議用在「對大家來說結果都一樣的 API」，例如：熱門文章列表、排行榜。

Materialized View

是在資料庫中「先苦後甘」，先辛苦算完一次後快取一整個表。 服務的 API 就可改用比較簡短的 SQL 指令來搜尋，加上 index 後，速度又會再提升。之後我們再根據業務場景決定何時 refresh 一次，中間這段空檔，就像快取過期時間。

建議用在「想要個別看長期累積紀錄」報表，像是每個用戶電影看多久、個別用戶每月總支出。

Reference