MySQL 視圖完整指南：建立、管理、算法選擇、權限控制與分表應用

視圖是虛擬表，本質為預定義的 SELECT 查詢結果集。善用視圖可簡化複雜查詢、實現欄位級權限控制、支援大表分片透明訪問。

一、核心語法速查

二、視圖三大核心優勢

1. 簡化複雜查詢

-- 原始：每次需 GROUP BY
SELECT cat_id, AVG(shop_price) FROM goods GROUP BY cat_id;

-- 建立視圖
CREATE VIEW avg_price AS 
SELECT cat_id, AVG(shop_price) AS avg_price FROM goods GROUP BY cat_id;

-- 後續直接查詢
SELECT * FROM avg_price WHERE cat_id = 5;

2. 欄位級權限隔離

-- 隱藏敏感價格欄位
CREATE VIEW public_goods AS 
SELECT goods_id, goods_name, cat_id FROM goods; -- 不含 shop_price

GRANT SELECT ON public_goods TO 'report_user'@'%';
REVOKE SELECT ON goods FROM 'report_user'@'%';

3. 大表分片透明訪問

-- 將 news 分為 news_1~news_4（id%4+1 路由）
CREATE VIEW news AS
SELECT * FROM news_1
UNION ALL SELECT * FROM news_2
UNION ALL SELECT * FROM news_3
UNION ALL SELECT * FROM news_4;

-- 業務端無感知查詢
SELECT * FROM news WHERE id = 10001;

三、視圖可更新性規則

視圖可執行 INSERT/UPDATE/DELETE 需滿足：

• 視圖列與基表列一對一映射（無聚合、無運算、無 DISTINCT/GROUP BY/UNION）。
• 視圖包含基表所有無預設值的 NOT NULL 列（INSERT 時）。
• 基表觸發器不會因視圖操作觸發（MySQL 限制）。

四、ALGORITHM 算法選擇：MERGE vs TEMPTABLE

經典案例：分組取每組第一條

-- 需求：每個分類價格最高的商品
-- MERGE 會合併導致 ORDER BY 失效
CREATE ALGORITHM=TEMPTABLE VIEW top_per_cat AS
SELECT cat_id, goods_id, goods_name, shop_price
FROM goods ORDER BY cat_id, shop_price DESC;

-- 外層 GROUP BY 正確取每組第一行
SELECT * FROM top_per_cat GROUP BY cat_id;

五、視圖元資料查詢

-- 所有視圖
SELECT TABLE_NAME FROM information_schema.VIEWS WHERE TABLE_SCHEMA = 'db_name';

-- 視圖依賴關係
SELECT * FROM information_schema.VIEW_TABLE_USAGE WHERE VIEW_NAME = 'view_name';

六、最佳實踐建議

1. 視圖命名規範：v_ 前綴，語義清晰（如 v_user_order_summary）。
2. 避免嵌套視圖（視圖查視圖），優化器難以展開，易產生 TEMPTABLE 降級。
3. 大數據集視圖禁用 SELECT *，明確列出欄位減少網路與物化開銷。
4. 定期 ANALYZE TABLE 更新基表統計，確保視圖查詢走最優計劃。