：從基礎(chǔ)語法到物化視圖的完整指南)
做了這么多年P(guān)ostgreSQL的項目開發(fā)和運維我越來越覺得視圖是個被嚴(yán)重低估的功能。很多人只把它當(dāng)成一個存起來的SELECT需要臨時查點什么就建一個用完就忘。但實際深入到生產(chǎn)環(huán)境中你會發(fā)現(xiàn)視圖用好了不僅能大幅度簡化業(yè)務(wù)代碼還能承擔(dān)權(quán)限隔離、接口穩(wěn)定、架構(gòu)解耦甚至部分場景下的性能優(yōu)化工作。這篇文章就把我在多個項目里創(chuàng)建、使用、管理PostgreSQL視圖的經(jīng)驗完整梳理一遍從基礎(chǔ)語法到物化視圖的選型邏輯再到踩過的坑挨個講透。不管你之前是否系統(tǒng)學(xué)過視圖只要實際用PostgreSQL寫過查詢這篇文章都能幫你把視圖相關(guān)的知識補成一張完整的拼圖。我會盡量從為什么要這樣用的角度來講而不是單純列一堆命令。1. 視圖的本質(zhì)理解它你才能真正用好它1.1 一張不落地的表視圖在PostgreSQL里本質(zhì)上就是一條被命名的SELECT查詢。數(shù)據(jù)庫沒有為它單獨存儲數(shù)據(jù)它只是把一段查詢邏輯以表的形式暴露給外部調(diào)用。你查詢視圖的時候數(shù)據(jù)庫會在底層把視圖的定義展開合并到你輸入的查詢語句里然后去執(zhí)行真正的掃描和計算。用一個日常類比來理解視圖就像Excel里另存的一個透視表頁簽。底下的原始數(shù)據(jù)工作表還在那里你看到的透視結(jié)果并不是另一份獨立拷貝而是對源數(shù)據(jù)的動態(tài)匯總。源表數(shù)據(jù)一變視圖查出來的結(jié)果自然跟著變不需要你手動同步。有一個關(guān)鍵點需要明確普通視圖是動了源數(shù)據(jù)它就變這是它和物化視圖最大的區(qū)別。物化視圖會把查詢結(jié)果實實在在落到磁盤上源表變了它不會自動跟著變必須手動刷新。兩者的取舍后面我專門用一節(jié)來講這里先記住這個根本差異。1.2 視圖到底幫你解決了什么問題拋開理論我梳理一下自己在真實項目里最常使用視圖的四個場景也是視圖存在的核心價值第一復(fù)雜查詢的復(fù)用與簡化。業(yè)務(wù)中經(jīng)常會有一些多表JOIN、多層聚合的查詢比如每個客戶最近30天的訂單金額、退款金額、凈支付額。如果每個接口、每張報表都重寫一遍這段邏輯不僅代碼冗余后續(xù)字段口徑一旦調(diào)整就要改很多處。而把這段邏輯封進一個視圖所有應(yīng)用只需要SELECT * FROM customer_recent_stats口徑天然統(tǒng)一。第二權(quán)限控制與數(shù)據(jù)安全。這是被很多人忽略的一個強大用途。通過視圖可以只向某個應(yīng)用賬戶暴露特定的列和特定的行。比如人事系統(tǒng)里員工表有身份證號、工資、手機號等敏感字段你可以建一個視圖只包含姓名、部門、職級等非敏感列把查詢權(quán)限授給普通業(yè)務(wù)賬號。這樣應(yīng)用層即使被拖庫敏感字段也不會直接暴露。第三架構(gòu)解耦和接口穩(wěn)定。應(yīng)用是移動端的、服務(wù)端的、報表的底層表結(jié)構(gòu)卻不可避免會調(diào)整。比如訂單表要拆分成訂單主表和訂單明細(xì)表如果應(yīng)用直接查詢原訂單表改動波及面很大。此時如果有一層視圖擋在中間視圖對外輸出的字段結(jié)構(gòu)保持不變底層表隨便拆應(yīng)用代碼一行都不用動只要把視圖定義重寫一遍。第四數(shù)據(jù)一致性邏輯的統(tǒng)一。比如有效訂單的定義是status paid AND deleted_at IS NULL所有模塊都要用這個過濾條件。與其在十幾個SQL里各寫各的不如在視圖里統(tǒng)一吃掉這個條件。后面口徑調(diào)整時你只改一個地方而不是痛苦地搜全項目。1.3 視圖適合誰來用我的判斷是后端開發(fā)、數(shù)據(jù)分析師、數(shù)據(jù)庫管理員、運維工程師都值得系統(tǒng)掌握。后端開發(fā)用視圖減少業(yè)務(wù)代碼里的重復(fù)SQL數(shù)據(jù)分析師用視圖固化常用取數(shù)口徑DBA用視圖做權(quán)限控制和架構(gòu)解耦運維則要能在排查故障時看懂視圖的依賴關(guān)系。甚至你只是自己搞個小項目學(xué)會視圖也能讓代碼干凈不少。2. 視圖的創(chuàng)建從基礎(chǔ)語法到容易被忽略的選項2.1 創(chuàng)建一個基礎(chǔ)視圖在PostgreSQL中創(chuàng)建視圖的標(biāo)準(zhǔn)語法是CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;這個語法和普通SELECT幾乎一樣。實際項目中我見到最多的用法是把一個高頻的聯(lián)表統(tǒng)計封裝起來CREATE VIEW v_order_summary AS SELECT u.id AS user_id, u.name AS user_name, COUNT(o.id) AS order_count, COALESCE(SUM(o.amount), 0) AS total_amount, COALESCE(AVG(o.amount), 0) AS avg_amount FROM users u LEFT JOIN orders o ON o.user_id u.id AND o.status paid GROUP BY u.id, u.name;這里有幾個細(xì)節(jié)值得新手注意。視圖的列名如果不指定會直接繼承SELECT后面寫的別名。所以字段別名不要偷懶盡量寫清楚否則視圖建出來一堆count、sum、coalesce這種列名后續(xù)調(diào)用時你根本不知道哪個是哪個。還有一種創(chuàng)建方式你可以顯式指定視圖列名列表而不在SELECT里寫別名CREATE VIEW v_user_contact (user_id, user_name, mobile, email) AS SELECT id, name, phone, mail FROM users;這樣做的實際好處是如果底層表字段名改動比如phone改成mobile_phone你只需要重建視圖的SELECT部分對外暴露的列名mobile完全不變所有依賴這個視圖的報表和接口都不受波及。這是視圖做接口穩(wěn)定性的一個重要手段。2.2 必須掌握的創(chuàng)建選項PostgreSQL的CREATE VIEW還有幾個實際項目里非常好用的選項逐個說OR REPLACE。如果視圖已經(jīng)存在加上OR REPLACE可以直接用新定義替換舊定義省去先DROP再CREATE的繁瑣。CREATE OR REPLACE VIEW v_order_summary AS SELECT ...但請注意OR REPLACE不能修改已有視圖的列結(jié)構(gòu)比如不能增加列、刪除列、調(diào)整列的順序。如果你需要改變列結(jié)構(gòu)必須DROP VIEW后重建。這是個很容易踩的坑稍后會細(xì)說。TEMPORARY臨時視圖。CREATE TEMPORARY VIEW tmp_this_for_session AS SELECT * FROM products WHERE stock 0;臨時視圖只在當(dāng)前數(shù)據(jù)庫會話里存在會話結(jié)束自動消失。我一般在做一次性數(shù)據(jù)核對、臨時分析任務(wù)時用它不污染正式的數(shù)據(jù)庫對象。RECURSIVE遞歸視圖。這個適合處理樹形結(jié)構(gòu)比如部門層級、商品分類多級嵌套。把遞歸CTE封裝成視圖業(yè)務(wù)查詢就直接用了CREATE RECURSIVE VIEW v_org_tree AS SELECT id, parent_id, name FROM departments WHERE parent_id IS NULL UNION ALL SELECT d.id, d.parent_id, d.name FROM departments d JOIN v_org_tree v ON d.parent_id v.id;注意遞歸視圖的定義里必須包含一個非遞歸的初始項再加一個遞歸項兩者用UNION ALL連接否則會報錯或者陷入無限遞歸。WITH CHECK OPTION帶檢查選項。這個選項和視圖的寫入有關(guān)。簡單說通過視圖去INSERT或者UPDATE時它保證最終寫入后的數(shù)據(jù)依然滿足視圖的過濾條件。后面講視圖更新時詳細(xì)演示。2.3 創(chuàng)建視圖時的經(jīng)驗細(xì)節(jié)建視圖這件事看起來簡單但我在項目里見到的命名混亂實在太多。有幾個實踐層面的建議直接抄作業(yè)就行給視圖命名時建議統(tǒng)一加前綴。比如v_表示普通視圖mv_表示物化視圖。這樣在幾百個數(shù)據(jù)庫對象里\dv一看就全是視圖和表、函數(shù)、序列分開不會混成一鍋粥。視圖定義里的查詢長度和質(zhì)量。視圖不是越短越好但也不是越長越炫。原則是一個視圖只解決一個核心問題盡量不要搞一個包含七八個JOIN、十幾個子查詢的超級視圖。視圖復(fù)雜到一定程度它的可讀性、可維護性都會急劇下降。創(chuàng)建視圖時給注釋。這個太重要了我說一百遍也不嫌多。視圖是團隊共享資產(chǎn)不寫注釋過三個月你自己都會忘記這個視圖是干嘛的。用COMMENT補上COMMENT ON VIEW v_order_summary IS 訂單匯總視圖口徑statuspaid 的訂單統(tǒng)計維度為用戶;真的某次我接手一個老項目里面幾十個視圖一點注釋都沒有排查業(yè)務(wù)口徑花了一整天。從那以后我對自己管理庫里的所有對象都強制要求寫注釋。3. 視圖的使用查詢執(zhí)行、數(shù)據(jù)更新與權(quán)限邊界3.1 視圖查詢背后到底發(fā)生了什么很多人以為查視圖就是查了一張表其實沒那么簡單。PostgreSQL處理視圖查詢時大體上有兩種執(zhí)行策略這個理解直接關(guān)系到你后續(xù)做性能調(diào)優(yōu)第一種視圖合并view merging。對于定義簡單、不含聚合、DISTINCT、窗口函數(shù)、LIMIT等操作的視圖優(yōu)化器會把視圖定義里的SQL展開到你外層查詢里再把整個查詢統(tǒng)一優(yōu)化。也就是說你寫的SELECT * FROM v_order_summary WHERE user_id 100優(yōu)化器看到的幾乎等同于直接在原始表上做過濾。這種場景下視圖幾乎沒有額外性能損耗甚至因為SQL更整潔優(yōu)化起來更方便。第二種物化子查詢materialized subquery。當(dāng)視圖定義包含聚合函數(shù)、窗口函數(shù)、DISTINCT這類無法被完全展開合并的操作時優(yōu)化器通常會先把視圖里的查詢結(jié)果作為一個內(nèi)部子查詢執(zhí)行然后再與外部查詢做關(guān)聯(lián)。這種情況下視圖就存在一定的額外開銷特別是外部查詢帶有過濾條件時如果優(yōu)化器不能把這個條件下推到視圖內(nèi)部就可能導(dǎo)致先全量計算視圖再挑數(shù)據(jù)這是復(fù)雜視圖性能差的一個根因。用EXPLAIN驗證一下是最直觀的。我拿一個簡單的視圖做過實驗EXPLAIN SELECT * FROM v_order_summary WHERE user_id 100;如果執(zhí)行計劃里直接對users表走了索引掃描說明視圖被合并了如果執(zhí)行計劃里出現(xiàn)一個Subquery Scan或者對orders表的全量掃后再過濾說明視圖是被物化的。遇到后者就需要認(rèn)真審視視圖定義了。3.2 視圖數(shù)據(jù)的更新什么情況下能INSERT、UPDATE、DELETE這是視圖使用中最容易讓人困惑的地方。PostgreSQL允許對滿足特定條件的簡單視圖執(zhí)行INSERT、UPDATE、DELETE。這些條件包括視圖的查詢基于單一基表普通視圖沒有多表JOIN視圖中包含了基表的全部非空列沒有NOT NULL約束的列除外查詢中沒有GROUP BY、HAVING、DISTINCT、聚合函數(shù)、窗口函數(shù)、LIMIT、OFFSET等操作查詢中沒有UNION、INTERSECT、EXCEPT查詢的FROM部分不含子查詢、不含函數(shù)簡單說視圖的定義越像一張普通的表它就越可能支持寫入。舉一個完全支持更新的簡單視圖CREATE VIEW v_active_users AS SELECT id, name, email, status FROM users WHERE status active;這個視圖沒有聚合、沒有JOIN查的就是單表users所以可以通過視圖執(zhí)行更新UPDATE v_active_users SET status inactive WHERE id 1024;這條語句會把底層users表里id為1024的記錄status改成inactive。執(zhí)行完之后你再SELECT * FROM v_active_users WHERE id 1024就是空集了——因為這條記錄已經(jīng)不滿足視圖的過濾條件但你確實通過視圖改了一條數(shù)據(jù)?？吹竭@里你可能會問那我能不能用它直接INSERT一條statusactive的數(shù)據(jù)可以。但麻煩的地方在下面如果INSERT或UPDATE后新數(shù)據(jù)不符合視圖的WHERE過濾條件會發(fā)生什么默認(rèn)情況下PostgreSQL是允許的。這就會造成一個怪異局面你通過v_active_users成功插入一條statuspending的記錄然后再從視圖查詢卻看不到這條數(shù)據(jù)。數(shù)據(jù)顯示消失了。避免這個問題就用WITH CHECK OPTIONCREATE VIEW v_active_users AS SELECT id, name, email, status FROM users WHERE status active WITH CHECK OPTION;加上以后任何通過這個視圖的INSERT或UPDATE只要會導(dǎo)致結(jié)果行不滿足statusactive條件都會被數(shù)據(jù)庫直接拒絕并報錯。這個選項我個人強烈建議在需要寫入的視圖上默認(rèn)開啟它能在源頭堵住很多數(shù)據(jù)異常。對于復(fù)雜視圖多表JOIN、聚合等PostgreSQL默認(rèn)不支持直接寫入。如果業(yè)務(wù)確實需要通常的方案是創(chuàng)建INSTEAD OF觸發(fā)器在觸發(fā)器中自行定義寫入邏輯分發(fā)到各個基表。這個屬于進階用法這次不展開。3.3 視圖是權(quán)限控制的有效工具視圖在權(quán)限管理上的價值經(jīng)常被忽視。我見過不少項目直接給應(yīng)用賬號GRANT ALL ON ALL TABLES這在生產(chǎn)環(huán)境是相當(dāng)危險的。視圖提供了一種更精細(xì)可控的方案。思路很簡單對底層表不授予應(yīng)用賬號任何權(quán)限只向它授權(quán)視圖的SELECT權(quán)限。-- 僅允許應(yīng)用賬號通過視圖讀取非敏感數(shù)據(jù) REVOKE ALL ON users FROM app_rw; GRANT SELECT ON v_user_public_info TO app_rw;這樣應(yīng)用賬號雖然無法直接查詢users表但通過v_user_public_info視圖可以拿到脫敏后的數(shù)據(jù)。視圖定義里可以手動剔除敏感字段甚至可以對字段做脫敏轉(zhuǎn)換比如手機號只顯示前三位和后四位CREATE VIEW v_user_contact_masked AS SELECT id, name, CONCAT(LEFT(mobile, 3), ****, RIGHT(mobile, 4)) AS mobile_masked FROM users;由此即使應(yīng)用出現(xiàn)SQL注入攻擊者能查詢到的也只是視圖暴露的內(nèi)容而不是底層全字段。多了一層防護安全底線就高一級。哪怕你現(xiàn)在的項目沒有嚴(yán)格的分權(quán)要求也強烈建議把敏感字段和業(yè)務(wù)字段用視圖層隔一道。4. 視圖的管理與維護讓幾十個視圖不變成雷區(qū)4.1 如何查看和定位已有視圖維護視圖的第一步是先搞清楚庫里到底有哪些視圖。最常用的命令是psql里的簡寫命令\dv \dm -- 查看物化視圖 \dv schema.*\dv默認(rèn)顯示當(dāng)前schema下所有視圖及其所有者。如果schema太多了還可以精確指定\dv public.*除了psql客戶端命令還有幾個系統(tǒng)視圖和表值得掌握。pg_views提供了視圖名、schema、視圖定義等基礎(chǔ)信息SELECT schemaname, viewname, definition FROM pg_views WHERE schemaname public;這個查詢最實用的地方是可以直接輸出視圖定義的完整SQL文本對于這個視圖當(dāng)初是怎么寫的這種問題一條SQL就見分曉。pg_class配合pg_depend可以查視圖的依賴關(guān)系。我經(jīng)常用它來排查這個表被哪些視圖依賴了SELECT DISTINCT dependent.relname AS dependent_object, dependent.relkind AS dependent_type FROM pg_depend JOIN pg_class target ON pg_depend.refobjid target.oid JOIN pg_class dependent ON pg_depend.objid dependent.oid WHERE target.relname users AND dependent.relname LIKE v_% ORDER BY dependent.relname;查詢結(jié)果能清楚告訴你users表上掛了哪些視圖。在準(zhǔn)備改動表結(jié)構(gòu)之前這個查詢能救命——它能提前告訴你這次改動會影響哪些對象避免上線后被一堆報錯打臉。4.2 修改與刪除視圖的正確姿勢視圖的修改其實很受限。前面提過CREATE OR REPLACE VIEW不能改變列結(jié)構(gòu)如果想增刪列只能DROP再重建DROP VIEW IF EXISTS v_order_summary; CREATE VIEW v_order_summary AS ...這樣刪除重建有一個明顯的風(fēng)險如果有其他對象比如報表SQL、下游視圖引用了這個視圖直接DROP會失敗并報出類似cannot drop view ... because other objects depend on it的錯誤。此時有兩個選擇。一是用CASCADE強制級聯(lián)刪除DROP VIEW v_order_summary CASCADE;CASCADE會把依賴此視圖的其他視圖一并刪除。這個操作效果猛但危險性也高因為一旦刪掉依賴鏈上的所有視圖都沒了而且不可恢復(fù)。我一般只在開發(fā)環(huán)境用CASCADE生產(chǎn)環(huán)境不用寧可先處理下游依賴、一個個重建也不要一把梭把視圖鏈全刪了。二是先備份視圖定義再刪重建目標(biāo)視圖再去修復(fù)下游視圖。實際的執(zhí)行順序大致是通過pg_views導(dǎo)出目標(biāo)視圖的定義文本手動重建新版本的視圖檢查依賴它的其他視圖或函數(shù)逐個調(diào)整這里有一個小技巧當(dāng)你想批量修改一組視圖的定義時可以先把現(xiàn)有定義全部導(dǎo)出保存為SQL腳本改完再重新執(zhí)行。這樣一旦改壞了至少能回滾到改之前的狀態(tài)。4.3 視圖維護的實戰(zhàn)經(jīng)驗在大型項目里管視圖最頭疼的問題就是視圖數(shù)量多了以后沒有約束。我自己整理了一套實踐規(guī)范供參考視圖歸屬schema分離。如果項目比較大可以專門建一個views或reportschema來存放視圖和業(yè)務(wù)表所在的schema隔離。這樣應(yīng)用訪問數(shù)據(jù)庫時一眼就能看出哪些是原始數(shù)據(jù)哪些是加工數(shù)據(jù)。重命名視圖遵循統(tǒng)一模式。比如v_業(yè)務(wù)域_實體_用途v_order_daily_amount、v_user_rank這種格式。挺直白但確實有效。命名規(guī)則一旦建立后續(xù)維護成本會低很多。定期做視圖依賴審查。底層表結(jié)構(gòu)重構(gòu)后總有一些視圖的定義還是舊寫法?？梢远ㄆ谟蒙厦娌橐蕾囮P(guān)系的SQL把每個基礎(chǔ)表關(guān)聯(lián)的視圖列一遍逐個確認(rèn)定義是否還符合當(dāng)前結(jié)構(gòu)。有一類報錯很典型的底層表字段取消或者改名但視圖定義里還引用舊字段一查詢就報column does not exist。這個問題在后面常見問題里我還要專門講。4.4 視圖的權(quán)限與安全視圖的權(quán)限管理和表類似用GRANT和REVOKE控制GRANT SELECT ON v_order_summary TO readonly_user; REVOKE SELECT ON v_order_summary FROM old_user;有個容易被忽略的安全細(xì)節(jié)視圖底層的基表權(quán)限和視圖自身的權(quán)限是疊加的。也就是說即使你只給了用戶視圖的SELECT權(quán)限如果底層表本身也給了這個用戶SELECT權(quán)限用戶完全可以繞過視圖去直接查底層表。所以視圖做權(quán)限控制時底層的權(quán)限必須收緊不能兩頭都開。記住一個原則視圖是暴露層基表是內(nèi)層內(nèi)層的權(quán)限默認(rèn)不給要查數(shù)據(jù)就走外層的視圖。另外PostgreSQL還提供了行級安全策略RLS可以和視圖配合做到更細(xì)粒度的數(shù)據(jù)隔離。多租戶系統(tǒng)里每個租戶只能看自己的數(shù)據(jù)這種場景用RLS配合視圖比較合適。關(guān)于RLS這次不展開但建議用到視圖做多租戶隔離的同學(xué)去了解一下。5. 物化視圖性能扛不住時你需要的進階手段5.1 物化視圖的創(chuàng)建與刷新機制普通視圖每次查詢都實時執(zhí)行底層SQL數(shù)據(jù)實時但大查詢可能很慢。物化視圖則把查詢結(jié)果真正落盤存儲查詢物化視圖相當(dāng)于直接查一張本地物理表速度自然快很多。代價是數(shù)據(jù)不實時必須手動刷新。先看語法CREATE MATERIALIZED VIEW mv_order_monthly AS SELECT DATE_TRUNC(month, created_at) AS month, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders GROUP BY DATE_TRUNC(month, created_at) WITH DATA;WITH DATA表示創(chuàng)建時立即執(zhí)行查詢并填充數(shù)據(jù)。如果數(shù)據(jù)量極大創(chuàng)建時不想等待可以用WITH NO DATA之后在業(yè)務(wù)低谷期再刷新CREATE MATERIALIZED VIEW mv_order_monthly AS ... WITH NO DATA; REFRESH MATERIALIZED VIEW mv_order_monthly;REFRESH MATERIALIZED VIEW在PostgreSQL默認(rèn)會鎖定這個物化視圖刷新期間查詢會阻塞。如果物化視圖被報表系統(tǒng)高頻使用這個鎖可能造成問題。解決辦法是CONCURRENTLY并發(fā)刷新REFRESH MATERIALIZED VIEW CONCURRENTLY mv_order_monthly;使用CONCURRENTLY的前提是物化視圖上必須有唯一索引通常建在主鍵或唯一業(yè)務(wù)鍵上否則報錯。它的原理是創(chuàng)建一份新數(shù)據(jù)和當(dāng)前數(shù)據(jù)做對比只更新差異部分期間舊數(shù)據(jù)可正常查詢。代價是刷新時間比全量刷新長一點也需要更多存儲空間。在報表服務(wù)不要求停機的場景我基本都用CONCURRENTLY。還有一個小問題是物化視圖本身不具備自動刷新能力PostgreSQL內(nèi)核沒有內(nèi)置定時刷新的機制。實際項目里一般靠外部調(diào)度實現(xiàn)要么用pg_cron擴展要么在應(yīng)用層寫定時任務(wù)每天凌晨執(zhí)行一次刷新。5.2 什么場景適合物化視圖我的判斷標(biāo)準(zhǔn)很直接查詢結(jié)果被高頻讀取但底層數(shù)據(jù)的更新頻率不高或者即便延遲一段時間也不會影響業(yè)務(wù)正確性。舉一個實際案例。某電商項目要做每日銷售看板報表上要展示銷售額、訂單量、客單價等指標(biāo)。如果把源數(shù)據(jù)直接聚合查詢每次打開頁面都要掃描幾十萬、上百萬的訂單明細(xì)響應(yīng)時間根本不能看。后來我把日粒度聚合邏輯做成物化視圖每天凌晨刷新一次報表查詢直接從幾十秒降到幾十毫秒。用戶每天早上看到的報表數(shù)據(jù)是前一天的統(tǒng)計完全在可接受范圍內(nèi)?？偨Y(jié)一下適合物化視圖的典型場景報表/看板指標(biāo)固定、滯后性可接受數(shù)據(jù)倉庫層面向分析的大查詢預(yù)計算復(fù)雜查詢的高頻調(diào)用接口內(nèi)部有重JOIN和聚合但數(shù)據(jù)源實時性不強跨庫/跨系統(tǒng)同步后的本地查詢加速搭配外部數(shù)據(jù)包裝器FDW使用5.3 普通視圖和物化視圖到底怎么選我整理了一個決策表遇到具體問題時直接對照對比項普通視圖物化視圖數(shù)據(jù)實時性實時讀取基表刷新時更新有延遲存儲開銷無額外存儲占用實際存儲空間查詢性能簡單視圖接近直查復(fù)雜視圖可能較慢查詢極快相當(dāng)于查本地表是否支持寫入簡單視圖可以復(fù)雜視圖受限不支持直接寫入只能刷新索引能力不能建索引需依賴基表索引可建索引特別是唯一索引刷新機制無刷新概念手動或定時REFRESH使用成本創(chuàng)建簡單維護成本低需要額外維護刷新策略、索引選擇時問自己三個問題數(shù)據(jù)需要多新鮮查詢量有多大維護精力夠不夠如果數(shù)據(jù)天然就是歷史的、只讀的比如報表統(tǒng)計直接物化視圖。如果需要實時查詢最新數(shù)據(jù)且查詢是簡單過濾普通視圖合并到外部查詢后性能并不差。只有查詢復(fù)雜且夠?qū)崟r時還得琢磨別的方案比如改表結(jié)構(gòu)、加索引、用緩存中間件而不是盲目堆物化視圖。物化視圖還有個變體玩法如果某些基礎(chǔ)表數(shù)據(jù)極少變化可以給物化視圖建索引來進一步加速查詢。數(shù)據(jù)量一旦上來物化視圖上的索引比每次都跑聚合查詢劃算太多了。6. 常見問題與排查技巧實錄6.1 視圖查詢變慢怎么定位根因這是最常被問到的問題。很多人的第一反應(yīng)是視圖就是不快沒辦法其實不一定。我通常按照這個順序排查第一步看執(zhí)行計劃是不是發(fā)生了視圖物化。用EXPLAIN ANALYZE跑一遍帶視圖的查詢?nèi)绻媱澙锍霈F(xiàn)Subquery Scan on v_...說明視圖沒有被合并。此時考慮簡化視圖定義或者把視圖里不影響復(fù)雜度的部分拆出去讓優(yōu)化器能夠把外部過濾條件下推到基表上。第二步查視圖涉及的基表是否有合適的索引。視圖慢很多時候不是視圖本身的錯而是基表缺索引。比如視圖里按created_at做范圍過濾但orders表在該字段上沒有索引那再簡單的視圖也快不起來。此時該建索引建索引。第三步評估是否是復(fù)雜查詢本身的問題。在視圖定義里直接跑一遍這條SQL看耗時。如果裸SQL就慢說明問題不在視圖而在查詢設(shè)計——考慮改寫SQL、拆分為多個簡單視圖或者升級為物化視圖。有一個經(jīng)驗值可以參考當(dāng)視圖嵌套超過三層或者一個視圖里JOIN的表超過四五張時就一定要警惕了。嵌套視圖會讓復(fù)雜的SQL變得很難讀優(yōu)化器也可能在多層視圖合并時做出不夠好的計劃。遇到這種視圖套視圖套視圖的情況最好把深層視圖適當(dāng)攤開保持視圖結(jié)構(gòu)扁平。6.2 找不到字段、報錯column does not exist這是我見過最高頻的線下事故。底層表結(jié)構(gòu)剛改完某個視圖就掛了。原因通常是視圖定義里的字段被底層表改名或刪除而視圖是用舊字段名寫的。排查思路用pg_views查出視圖定義文本直接在定義里搜索報錯字段名確認(rèn)它和當(dāng)前表結(jié)構(gòu)是否匹配。如果只是字段改名重建視圖即可。預(yù)防手段更關(guān)鍵在改表結(jié)構(gòu)之前先跑一遍依賴查詢看看這個表被哪些視圖引用SELECT dependent.relname, dependent.relkind FROM pg_depend JOIN pg_class target ON pg_depend.refobjid target.oid JOIN pg_class dependent ON pg_depend.objid dependent.oid WHERE target.relname orders AND dependent.relkind IN (v, m);查出來的每個視圖在修改前都重新拉一遍最新數(shù)據(jù)確認(rèn)不會報錯再正式改表。上線前做這一步能避開絕大多數(shù)改完表服務(wù)掛了的尷尬。6.3 DROP VIEW失敗、視圖刪除不了處理辦法前面已經(jīng)提過先查依賴再用CASCADE。但再強調(diào)一次生產(chǎn)環(huán)境CASCADE要謹(jǐn)慎最好先把依賴鏈的視圖定義導(dǎo)出備份再執(zhí)行。特別是視圖之間的多層依賴CASCADE刪掉的可不止當(dāng)前這個視圖可能連帶刪掉一整串下游視圖。如果真的誤刪了視圖PostgreSQL沒有原生的回收站機制唯一能做的就是靠備份來恢復(fù)或者從之前導(dǎo)出的SQL腳本里重建。所以日常維護時定期導(dǎo)出所有視圖定義保存為版本化的SQL文件是一個投入小、收益極大的習(xí)慣。6.4 視圖性能隱患排查清單用了一段時間視圖后我整理了一個快速自查清單建設(shè)每次變更視圖都過一遍視圖定義里是否包含不必要的ORDER BY沒有LIMIT時排序無意義還浪費時間是否使用SELECT *——視圖應(yīng)明確列出需要的字段否則底層表加字段會導(dǎo)致視圖輸出變化破壞對外穩(wěn)定性是否有嵌套過深的多層視圖物化視圖是否有唯一索引、是否啟用了CONCURRENTLY刷新視圖注釋是否齊全依賴方是否清楚該視圖的業(yè)務(wù)口徑檢查完這幾點大部分視圖相關(guān)的坑都能提前避開。寫在最后的一個實用習(xí)慣最后分享一個我堅持了很久的個人習(xí)慣每次創(chuàng)建視圖我必定順手寫清楚注釋并把視圖名、業(yè)務(wù)口徑、依賴表清單、創(chuàng)建日期記下來。剛開始覺得是浪費時間后來維護第三年的項目時這些注釋幫我節(jié)省了大量溝通成本。視圖這東西時間越久越考驗當(dāng)初的定義質(zhì)量。別嫌麻煩給未來的自己留一條好走的回頭路。如果你剛接觸PostgreSQL視圖建議從一個自己熟悉的查詢開始試著把它封裝成視圖再試著用視圖去更新數(shù)據(jù)進行觀察。動手跑幾次你對視圖執(zhí)行邏輯的理解會比看十篇文章都扎實。有具體問題歡迎在評論區(qū)聊聊你在項目里遇到的視圖案例。 PostgreSQL視圖實戰(zhàn)：從基礎(chǔ)語法到物化視圖的完整指南
做了這么多年P(guān)ostgreSQL的項目開發(fā)和運維我越來越覺得視圖是個被嚴(yán)重低估的功能。很多人只把它當(dāng)成一個存起來的SELECT需要臨時查點什么就建一個用完就忘。但實際深入到生產(chǎn)環(huán)境中你會發(fā)現(xiàn)視圖用好了不僅能大幅度簡化業(yè)務(wù)代碼還能承擔(dān)權(quán)限隔離、接口穩(wěn)定、架構(gòu)解耦甚至部分場景下的性能優(yōu)化工作。這篇文章就把我在多個項目里創(chuàng)建、使用、管理PostgreSQL視圖的經(jīng)驗完整梳理一遍從基礎(chǔ)語法到物化視圖的選型邏輯再到踩過的坑挨個講透。不管你之前是否系統(tǒng)學(xué)過視圖只要實際用PostgreSQL寫過查詢這篇文章都能幫你把視圖相關(guān)的知識補成一張完整的拼圖。我會盡量從為什么要這樣用的角度來講而不是單純列一堆命令。1. 視圖的本質(zhì)理解它你才能真正用好它1.1 一張不落地的表視圖在PostgreSQL里本質(zhì)上就是一條被命名的SELECT查詢。數(shù)據(jù)庫沒有為它單獨存儲數(shù)據(jù)它只是把一段查詢邏輯以表的形式暴露給外部調(diào)用。你查詢視圖的時候數(shù)據(jù)庫會在底層把視圖的定義展開合并到你輸入的查詢語句里然后去執(zhí)行真正的掃描和計算。用一個日常類比來理解視圖就像Excel里另存的一個透視表頁簽。底下的原始數(shù)據(jù)工作表還在那里你看到的透視結(jié)果并不是另一份獨立拷貝而是對源數(shù)據(jù)的動態(tài)匯總。源表數(shù)據(jù)一變視圖查出來的結(jié)果自然跟著變不需要你手動同步。有一個關(guān)鍵點需要明確普通視圖是動了源數(shù)據(jù)它就變這是它和物化視圖最大的區(qū)別。物化視圖會把查詢結(jié)果實實在在落到磁盤上源表變了它不會自動跟著變必須手動刷新。兩者的取舍后面我專門用一節(jié)來講這里先記住這個根本差異。1.2 視圖到底幫你解決了什么問題拋開理論我梳理一下自己在真實項目里最常使用視圖的四個場景也是視圖存在的核心價值第一復(fù)雜查詢的復(fù)用與簡化。業(yè)務(wù)中經(jīng)常會有一些多表JOIN、多層聚合的查詢比如每個客戶最近30天的訂單金額、退款金額、凈支付額。如果每個接口、每張報表都重寫一遍這段邏輯不僅代碼冗余后續(xù)字段口徑一旦調(diào)整就要改很多處。而把這段邏輯封進一個視圖所有應(yīng)用只需要SELECT * FROM customer_recent_stats口徑天然統(tǒng)一。第二權(quán)限控制與數(shù)據(jù)安全。這是被很多人忽略的一個強大用途。通過視圖可以只向某個應(yīng)用賬戶暴露特定的列和特定的行。比如人事系統(tǒng)里員工表有身份證號、工資、手機號等敏感字段你可以建一個視圖只包含姓名、部門、職級等非敏感列把查詢權(quán)限授給普通業(yè)務(wù)賬號。這樣應(yīng)用層即使被拖庫敏感字段也不會直接暴露。第三架構(gòu)解耦和接口穩(wěn)定。應(yīng)用是移動端的、服務(wù)端的、報表的底層表結(jié)構(gòu)卻不可避免會調(diào)整。比如訂單表要拆分成訂單主表和訂單明細(xì)表如果應(yīng)用直接查詢原訂單表改動波及面很大。此時如果有一層視圖擋在中間視圖對外輸出的字段結(jié)構(gòu)保持不變底層表隨便拆應(yīng)用代碼一行都不用動只要把視圖定義重寫一遍。第四數(shù)據(jù)一致性邏輯的統(tǒng)一。比如有效訂單的定義是status paid AND deleted_at IS NULL所有模塊都要用這個過濾條件。與其在十幾個SQL里各寫各的不如在視圖里統(tǒng)一吃掉這個條件。后面口徑調(diào)整時你只改一個地方而不是痛苦地搜全項目。1.3 視圖適合誰來用我的判斷是后端開發(fā)、數(shù)據(jù)分析師、數(shù)據(jù)庫管理員、運維工程師都值得系統(tǒng)掌握。后端開發(fā)用視圖減少業(yè)務(wù)代碼里的重復(fù)SQL數(shù)據(jù)分析師用視圖固化常用取數(shù)口徑DBA用視圖做權(quán)限控制和架構(gòu)解耦運維則要能在排查故障時看懂視圖的依賴關(guān)系。甚至你只是自己搞個小項目學(xué)會視圖也能讓代碼干凈不少。2. 視圖的創(chuàng)建從基礎(chǔ)語法到容易被忽略的選項2.1 創(chuàng)建一個基礎(chǔ)視圖在PostgreSQL中創(chuàng)建視圖的標(biāo)準(zhǔn)語法是CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;這個語法和普通SELECT幾乎一樣。實際項目中我見到最多的用法是把一個高頻的聯(lián)表統(tǒng)計封裝起來CREATE VIEW v_order_summary AS SELECT u.id AS user_id, u.name AS user_name, COUNT(o.id) AS order_count, COALESCE(SUM(o.amount), 0) AS total_amount, COALESCE(AVG(o.amount), 0) AS avg_amount FROM users u LEFT JOIN orders o ON o.user_id u.id AND o.status paid GROUP BY u.id, u.name;這里有幾個細(xì)節(jié)值得新手注意。視圖的列名如果不指定會直接繼承SELECT后面寫的別名。所以字段別名不要偷懶盡量寫清楚否則視圖建出來一堆count、sum、coalesce這種列名后續(xù)調(diào)用時你根本不知道哪個是哪個。還有一種創(chuàng)建方式你可以顯式指定視圖列名列表而不在SELECT里寫別名CREATE VIEW v_user_contact (user_id, user_name, mobile, email) AS SELECT id, name, phone, mail FROM users;這樣做的實際好處是如果底層表字段名改動比如phone改成mobile_phone你只需要重建視圖的SELECT部分對外暴露的列名mobile完全不變所有依賴這個視圖的報表和接口都不受波及。這是視圖做接口穩(wěn)定性的一個重要手段。2.2 必須掌握的創(chuàng)建選項PostgreSQL的CREATE VIEW還有幾個實際項目里非常好用的選項逐個說OR REPLACE。如果視圖已經(jīng)存在加上OR REPLACE可以直接用新定義替換舊定義省去先DROP再CREATE的繁瑣。CREATE OR REPLACE VIEW v_order_summary AS SELECT ...但請注意OR REPLACE不能修改已有視圖的列結(jié)構(gòu)比如不能增加列、刪除列、調(diào)整列的順序。如果你需要改變列結(jié)構(gòu)必須DROP VIEW后重建。這是個很容易踩的坑稍后會細(xì)說。TEMPORARY臨時視圖。CREATE TEMPORARY VIEW tmp_this_for_session AS SELECT * FROM products WHERE stock 0;臨時視圖只在當(dāng)前數(shù)據(jù)庫會話里存在會話結(jié)束自動消失。我一般在做一次性數(shù)據(jù)核對、臨時分析任務(wù)時用它不污染正式的數(shù)據(jù)庫對象。RECURSIVE遞歸視圖。這個適合處理樹形結(jié)構(gòu)比如部門層級、商品分類多級嵌套。把遞歸CTE封裝成視圖業(yè)務(wù)查詢就直接用了CREATE RECURSIVE VIEW v_org_tree AS SELECT id, parent_id, name FROM departments WHERE parent_id IS NULL UNION ALL SELECT d.id, d.parent_id, d.name FROM departments d JOIN v_org_tree v ON d.parent_id v.id;注意遞歸視圖的定義里必須包含一個非遞歸的初始項再加一個遞歸項兩者用UNION ALL連接否則會報錯或者陷入無限遞歸。WITH CHECK OPTION帶檢查選項。這個選項和視圖的寫入有關(guān)。簡單說通過視圖去INSERT或者UPDATE時它保證最終寫入后的數(shù)據(jù)依然滿足視圖的過濾條件。后面講視圖更新時詳細(xì)演示。2.3 創(chuàng)建視圖時的經(jīng)驗細(xì)節(jié)建視圖這件事看起來簡單但我在項目里見到的命名混亂實在太多。有幾個實踐層面的建議直接抄作業(yè)就行給視圖命名時建議統(tǒng)一加前綴。比如v_表示普通視圖mv_表示物化視圖。這樣在幾百個數(shù)據(jù)庫對象里\dv一看就全是視圖和表、函數(shù)、序列分開不會混成一鍋粥。視圖定義里的查詢長度和質(zhì)量。視圖不是越短越好但也不是越長越炫。原則是一個視圖只解決一個核心問題盡量不要搞一個包含七八個JOIN、十幾個子查詢的超級視圖。視圖復(fù)雜到一定程度它的可讀性、可維護性都會急劇下降。創(chuàng)建視圖時給注釋。這個太重要了我說一百遍也不嫌多。視圖是團隊共享資產(chǎn)不寫注釋過三個月你自己都會忘記這個視圖是干嘛的。用COMMENT補上COMMENT ON VIEW v_order_summary IS 訂單匯總視圖口徑statuspaid 的訂單統(tǒng)計維度為用戶;真的某次我接手一個老項目里面幾十個視圖一點注釋都沒有排查業(yè)務(wù)口徑花了一整天。從那以后我對自己管理庫里的所有對象都強制要求寫注釋。3. 視圖的使用查詢執(zhí)行、數(shù)據(jù)更新與權(quán)限邊界3.1 視圖查詢背后到底發(fā)生了什么很多人以為查視圖就是查了一張表其實沒那么簡單。PostgreSQL處理視圖查詢時大體上有兩種執(zhí)行策略這個理解直接關(guān)系到你后續(xù)做性能調(diào)優(yōu)第一種視圖合并view merging。對于定義簡單、不含聚合、DISTINCT、窗口函數(shù)、LIMIT等操作的視圖優(yōu)化器會把視圖定義里的SQL展開到你外層查詢里再把整個查詢統(tǒng)一優(yōu)化。也就是說你寫的SELECT * FROM v_order_summary WHERE user_id 100優(yōu)化器看到的幾乎等同于直接在原始表上做過濾。這種場景下視圖幾乎沒有額外性能損耗甚至因為SQL更整潔優(yōu)化起來更方便。第二種物化子查詢materialized subquery。當(dāng)視圖定義包含聚合函數(shù)、窗口函數(shù)、DISTINCT這類無法被完全展開合并的操作時優(yōu)化器通常會先把視圖里的查詢結(jié)果作為一個內(nèi)部子查詢執(zhí)行然后再與外部查詢做關(guān)聯(lián)。這種情況下視圖就存在一定的額外開銷特別是外部查詢帶有過濾條件時如果優(yōu)化器不能把這個條件下推到視圖內(nèi)部就可能導(dǎo)致先全量計算視圖再挑數(shù)據(jù)這是復(fù)雜視圖性能差的一個根因。用EXPLAIN驗證一下是最直觀的。我拿一個簡單的視圖做過實驗EXPLAIN SELECT * FROM v_order_summary WHERE user_id 100;如果執(zhí)行計劃里直接對users表走了索引掃描說明視圖被合并了如果執(zhí)行計劃里出現(xiàn)一個Subquery Scan或者對orders表的全量掃后再過濾說明視圖是被物化的。遇到后者就需要認(rèn)真審視視圖定義了。3.2 視圖數(shù)據(jù)的更新什么情況下能INSERT、UPDATE、DELETE這是視圖使用中最容易讓人困惑的地方。PostgreSQL允許對滿足特定條件的簡單視圖執(zhí)行INSERT、UPDATE、DELETE。這些條件包括視圖的查詢基于單一基表普通視圖沒有多表JOIN視圖中包含了基表的全部非空列沒有NOT NULL約束的列除外查詢中沒有GROUP BY、HAVING、DISTINCT、聚合函數(shù)、窗口函數(shù)、LIMIT、OFFSET等操作查詢中沒有UNION、INTERSECT、EXCEPT查詢的FROM部分不含子查詢、不含函數(shù)簡單說視圖的定義越像一張普通的表它就越可能支持寫入。舉一個完全支持更新的簡單視圖CREATE VIEW v_active_users AS SELECT id, name, email, status FROM users WHERE status active;這個視圖沒有聚合、沒有JOIN查的就是單表users所以可以通過視圖執(zhí)行更新UPDATE v_active_users SET status inactive WHERE id 1024;這條語句會把底層users表里id為1024的記錄status改成inactive。執(zhí)行完之后你再SELECT * FROM v_active_users WHERE id 1024就是空集了——因為這條記錄已經(jīng)不滿足視圖的過濾條件但你確實通過視圖改了一條數(shù)據(jù)?？吹竭@里你可能會問那我能不能用它直接INSERT一條statusactive的數(shù)據(jù)可以。但麻煩的地方在下面如果INSERT或UPDATE后新數(shù)據(jù)不符合視圖的WHERE過濾條件會發(fā)生什么默認(rèn)情況下PostgreSQL是允許的。這就會造成一個怪異局面你通過v_active_users成功插入一條statuspending的記錄然后再從視圖查詢卻看不到這條數(shù)據(jù)。數(shù)據(jù)顯示消失了。避免這個問題就用WITH CHECK OPTIONCREATE VIEW v_active_users AS SELECT id, name, email, status FROM users WHERE status active WITH CHECK OPTION;加上以后任何通過這個視圖的INSERT或UPDATE只要會導(dǎo)致結(jié)果行不滿足statusactive條件都會被數(shù)據(jù)庫直接拒絕并報錯。這個選項我個人強烈建議在需要寫入的視圖上默認(rèn)開啟它能在源頭堵住很多數(shù)據(jù)異常。對于復(fù)雜視圖多表JOIN、聚合等PostgreSQL默認(rèn)不支持直接寫入。如果業(yè)務(wù)確實需要通常的方案是創(chuàng)建INSTEAD OF觸發(fā)器在觸發(fā)器中自行定義寫入邏輯分發(fā)到各個基表。這個屬于進階用法這次不展開。3.3 視圖是權(quán)限控制的有效工具視圖在權(quán)限管理上的價值經(jīng)常被忽視。我見過不少項目直接給應(yīng)用賬號GRANT ALL ON ALL TABLES這在生產(chǎn)環(huán)境是相當(dāng)危險的。視圖提供了一種更精細(xì)可控的方案。思路很簡單對底層表不授予應(yīng)用賬號任何權(quán)限只向它授權(quán)視圖的SELECT權(quán)限。-- 僅允許應(yīng)用賬號通過視圖讀取非敏感數(shù)據(jù) REVOKE ALL ON users FROM app_rw; GRANT SELECT ON v_user_public_info TO app_rw;這樣應(yīng)用賬號雖然無法直接查詢users表但通過v_user_public_info視圖可以拿到脫敏后的數(shù)據(jù)。視圖定義里可以手動剔除敏感字段甚至可以對字段做脫敏轉(zhuǎn)換比如手機號只顯示前三位和后四位CREATE VIEW v_user_contact_masked AS SELECT id, name, CONCAT(LEFT(mobile, 3), ****, RIGHT(mobile, 4)) AS mobile_masked FROM users;由此即使應(yīng)用出現(xiàn)SQL注入攻擊者能查詢到的也只是視圖暴露的內(nèi)容而不是底層全字段。多了一層防護安全底線就高一級。哪怕你現(xiàn)在的項目沒有嚴(yán)格的分權(quán)要求也強烈建議把敏感字段和業(yè)務(wù)字段用視圖層隔一道。4. 視圖的管理與維護讓幾十個視圖不變成雷區(qū)4.1 如何查看和定位已有視圖維護視圖的第一步是先搞清楚庫里到底有哪些視圖。最常用的命令是psql里的簡寫命令\dv \dm -- 查看物化視圖 \dv schema.*\dv默認(rèn)顯示當(dāng)前schema下所有視圖及其所有者。如果schema太多了還可以精確指定\dv public.*除了psql客戶端命令還有幾個系統(tǒng)視圖和表值得掌握。pg_views提供了視圖名、schema、視圖定義等基礎(chǔ)信息SELECT schemaname, viewname, definition FROM pg_views WHERE schemaname public;這個查詢最實用的地方是可以直接輸出視圖定義的完整SQL文本對于這個視圖當(dāng)初是怎么寫的這種問題一條SQL就見分曉。pg_class配合pg_depend可以查視圖的依賴關(guān)系。我經(jīng)常用它來排查這個表被哪些視圖依賴了SELECT DISTINCT dependent.relname AS dependent_object, dependent.relkind AS dependent_type FROM pg_depend JOIN pg_class target ON pg_depend.refobjid target.oid JOIN pg_class dependent ON pg_depend.objid dependent.oid WHERE target.relname users AND dependent.relname LIKE v_% ORDER BY dependent.relname;查詢結(jié)果能清楚告訴你users表上掛了哪些視圖。在準(zhǔn)備改動表結(jié)構(gòu)之前這個查詢能救命——它能提前告訴你這次改動會影響哪些對象避免上線后被一堆報錯打臉。4.2 修改與刪除視圖的正確姿勢視圖的修改其實很受限。前面提過CREATE OR REPLACE VIEW不能改變列結(jié)構(gòu)如果想增刪列只能DROP再重建DROP VIEW IF EXISTS v_order_summary; CREATE VIEW v_order_summary AS ...這樣刪除重建有一個明顯的風(fēng)險如果有其他對象比如報表SQL、下游視圖引用了這個視圖直接DROP會失敗并報出類似cannot drop view ... because other objects depend on it的錯誤。此時有兩個選擇。一是用CASCADE強制級聯(lián)刪除DROP VIEW v_order_summary CASCADE;CASCADE會把依賴此視圖的其他視圖一并刪除。這個操作效果猛但危險性也高因為一旦刪掉依賴鏈上的所有視圖都沒了而且不可恢復(fù)。我一般只在開發(fā)環(huán)境用CASCADE生產(chǎn)環(huán)境不用寧可先處理下游依賴、一個個重建也不要一把梭把視圖鏈全刪了。二是先備份視圖定義再刪重建目標(biāo)視圖再去修復(fù)下游視圖。實際的執(zhí)行順序大致是通過pg_views導(dǎo)出目標(biāo)視圖的定義文本手動重建新版本的視圖檢查依賴它的其他視圖或函數(shù)逐個調(diào)整這里有一個小技巧當(dāng)你想批量修改一組視圖的定義時可以先把現(xiàn)有定義全部導(dǎo)出保存為SQL腳本改完再重新執(zhí)行。這樣一旦改壞了至少能回滾到改之前的狀態(tài)。4.3 視圖維護的實戰(zhàn)經(jīng)驗在大型項目里管視圖最頭疼的問題就是視圖數(shù)量多了以后沒有約束。我自己整理了一套實踐規(guī)范供參考視圖歸屬schema分離。如果項目比較大可以專門建一個views或reportschema來存放視圖和業(yè)務(wù)表所在的schema隔離。這樣應(yīng)用訪問數(shù)據(jù)庫時一眼就能看出哪些是原始數(shù)據(jù)哪些是加工數(shù)據(jù)。重命名視圖遵循統(tǒng)一模式。比如v_業(yè)務(wù)域_實體_用途v_order_daily_amount、v_user_rank這種格式。挺直白但確實有效。命名規(guī)則一旦建立后續(xù)維護成本會低很多。定期做視圖依賴審查。底層表結(jié)構(gòu)重構(gòu)后總有一些視圖的定義還是舊寫法?？梢远ㄆ谟蒙厦娌橐蕾囮P(guān)系的SQL把每個基礎(chǔ)表關(guān)聯(lián)的視圖列一遍逐個確認(rèn)定義是否還符合當(dāng)前結(jié)構(gòu)。有一類報錯很典型的底層表字段取消或者改名但視圖定義里還引用舊字段一查詢就報column does not exist。這個問題在后面常見問題里我還要專門講。4.4 視圖的權(quán)限與安全視圖的權(quán)限管理和表類似用GRANT和REVOKE控制GRANT SELECT ON v_order_summary TO readonly_user; REVOKE SELECT ON v_order_summary FROM old_user;有個容易被忽略的安全細(xì)節(jié)視圖底層的基表權(quán)限和視圖自身的權(quán)限是疊加的。也就是說即使你只給了用戶視圖的SELECT權(quán)限如果底層表本身也給了這個用戶SELECT權(quán)限用戶完全可以繞過視圖去直接查底層表。所以視圖做權(quán)限控制時底層的權(quán)限必須收緊不能兩頭都開。記住一個原則視圖是暴露層基表是內(nèi)層內(nèi)層的權(quán)限默認(rèn)不給要查數(shù)據(jù)就走外層的視圖。另外PostgreSQL還提供了行級安全策略RLS可以和視圖配合做到更細(xì)粒度的數(shù)據(jù)隔離。多租戶系統(tǒng)里每個租戶只能看自己的數(shù)據(jù)這種場景用RLS配合視圖比較合適。關(guān)于RLS這次不展開但建議用到視圖做多租戶隔離的同學(xué)去了解一下。5. 物化視圖性能扛不住時你需要的進階手段5.1 物化視圖的創(chuàng)建與刷新機制普通視圖每次查詢都實時執(zhí)行底層SQL數(shù)據(jù)實時但大查詢可能很慢。物化視圖則把查詢結(jié)果真正落盤存儲查詢物化視圖相當(dāng)于直接查一張本地物理表速度自然快很多。代價是數(shù)據(jù)不實時必須手動刷新。先看語法CREATE MATERIALIZED VIEW mv_order_monthly AS SELECT DATE_TRUNC(month, created_at) AS month, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders GROUP BY DATE_TRUNC(month, created_at) WITH DATA;WITH DATA表示創(chuàng)建時立即執(zhí)行查詢并填充數(shù)據(jù)。如果數(shù)據(jù)量極大創(chuàng)建時不想等待可以用WITH NO DATA之后在業(yè)務(wù)低谷期再刷新CREATE MATERIALIZED VIEW mv_order_monthly AS ... WITH NO DATA; REFRESH MATERIALIZED VIEW mv_order_monthly;REFRESH MATERIALIZED VIEW在PostgreSQL默認(rèn)會鎖定這個物化視圖刷新期間查詢會阻塞。如果物化視圖被報表系統(tǒng)高頻使用這個鎖可能造成問題。解決辦法是CONCURRENTLY并發(fā)刷新REFRESH MATERIALIZED VIEW CONCURRENTLY mv_order_monthly;使用CONCURRENTLY的前提是物化視圖上必須有唯一索引通常建在主鍵或唯一業(yè)務(wù)鍵上否則報錯。它的原理是創(chuàng)建一份新數(shù)據(jù)和當(dāng)前數(shù)據(jù)做對比只更新差異部分期間舊數(shù)據(jù)可正常查詢。代價是刷新時間比全量刷新長一點也需要更多存儲空間。在報表服務(wù)不要求停機的場景我基本都用CONCURRENTLY。還有一個小問題是物化視圖本身不具備自動刷新能力PostgreSQL內(nèi)核沒有內(nèi)置定時刷新的機制。實際項目里一般靠外部調(diào)度實現(xiàn)要么用pg_cron擴展要么在應(yīng)用層寫定時任務(wù)每天凌晨執(zhí)行一次刷新。5.2 什么場景適合物化視圖我的判斷標(biāo)準(zhǔn)很直接查詢結(jié)果被高頻讀取但底層數(shù)據(jù)的更新頻率不高或者即便延遲一段時間也不會影響業(yè)務(wù)正確性。舉一個實際案例。某電商項目要做每日銷售看板報表上要展示銷售額、訂單量、客單價等指標(biāo)。如果把源數(shù)據(jù)直接聚合查詢每次打開頁面都要掃描幾十萬、上百萬的訂單明細(xì)響應(yīng)時間根本不能看。后來我把日粒度聚合邏輯做成物化視圖每天凌晨刷新一次報表查詢直接從幾十秒降到幾十毫秒。用戶每天早上看到的報表數(shù)據(jù)是前一天的統(tǒng)計完全在可接受范圍內(nèi)?？偨Y(jié)一下適合物化視圖的典型場景報表/看板指標(biāo)固定、滯后性可接受數(shù)據(jù)倉庫層面向分析的大查詢預(yù)計算復(fù)雜查詢的高頻調(diào)用接口內(nèi)部有重JOIN和聚合但數(shù)據(jù)源實時性不強跨庫/跨系統(tǒng)同步后的本地查詢加速搭配外部數(shù)據(jù)包裝器FDW使用5.3 普通視圖和物化視圖到底怎么選我整理了一個決策表遇到具體問題時直接對照對比項普通視圖物化視圖數(shù)據(jù)實時性實時讀取基表刷新時更新有延遲存儲開銷無額外存儲占用實際存儲空間查詢性能簡單視圖接近直查復(fù)雜視圖可能較慢查詢極快相當(dāng)于查本地表是否支持寫入簡單視圖可以復(fù)雜視圖受限不支持直接寫入只能刷新索引能力不能建索引需依賴基表索引可建索引特別是唯一索引刷新機制無刷新概念手動或定時REFRESH使用成本創(chuàng)建簡單維護成本低需要額外維護刷新策略、索引選擇時問自己三個問題數(shù)據(jù)需要多新鮮查詢量有多大維護精力夠不夠如果數(shù)據(jù)天然就是歷史的、只讀的比如報表統(tǒng)計直接物化視圖。如果需要實時查詢最新數(shù)據(jù)且查詢是簡單過濾普通視圖合并到外部查詢后性能并不差。只有查詢復(fù)雜且夠?qū)崟r時還得琢磨別的方案比如改表結(jié)構(gòu)、加索引、用緩存中間件而不是盲目堆物化視圖。物化視圖還有個變體玩法如果某些基礎(chǔ)表數(shù)據(jù)極少變化可以給物化視圖建索引來進一步加速查詢。數(shù)據(jù)量一旦上來物化視圖上的索引比每次都跑聚合查詢劃算太多了。6. 常見問題與排查技巧實錄6.1 視圖查詢變慢怎么定位根因這是最常被問到的問題。很多人的第一反應(yīng)是視圖就是不快沒辦法其實不一定。我通常按照這個順序排查第一步看執(zhí)行計劃是不是發(fā)生了視圖物化。用EXPLAIN ANALYZE跑一遍帶視圖的查詢?nèi)绻媱澙锍霈F(xiàn)Subquery Scan on v_...說明視圖沒有被合并。此時考慮簡化視圖定義或者把視圖里不影響復(fù)雜度的部分拆出去讓優(yōu)化器能夠把外部過濾條件下推到基表上。第二步查視圖涉及的基表是否有合適的索引。視圖慢很多時候不是視圖本身的錯而是基表缺索引。比如視圖里按created_at做范圍過濾但orders表在該字段上沒有索引那再簡單的視圖也快不起來。此時該建索引建索引。第三步評估是否是復(fù)雜查詢本身的問題。在視圖定義里直接跑一遍這條SQL看耗時。如果裸SQL就慢說明問題不在視圖而在查詢設(shè)計——考慮改寫SQL、拆分為多個簡單視圖或者升級為物化視圖。有一個經(jīng)驗值可以參考當(dāng)視圖嵌套超過三層或者一個視圖里JOIN的表超過四五張時就一定要警惕了。嵌套視圖會讓復(fù)雜的SQL變得很難讀優(yōu)化器也可能在多層視圖合并時做出不夠好的計劃。遇到這種視圖套視圖套視圖的情況最好把深層視圖適當(dāng)攤開保持視圖結(jié)構(gòu)扁平。6.2 找不到字段、報錯column does not exist這是我見過最高頻的線下事故。底層表結(jié)構(gòu)剛改完某個視圖就掛了。原因通常是視圖定義里的字段被底層表改名或刪除而視圖是用舊字段名寫的。排查思路用pg_views查出視圖定義文本直接在定義里搜索報錯字段名確認(rèn)它和當(dāng)前表結(jié)構(gòu)是否匹配。如果只是字段改名重建視圖即可。預(yù)防手段更關(guān)鍵在改表結(jié)構(gòu)之前先跑一遍依賴查詢看看這個表被哪些視圖引用SELECT dependent.relname, dependent.relkind FROM pg_depend JOIN pg_class target ON pg_depend.refobjid target.oid JOIN pg_class dependent ON pg_depend.objid dependent.oid WHERE target.relname orders AND dependent.relkind IN (v, m);查出來的每個視圖在修改前都重新拉一遍最新數(shù)據(jù)確認(rèn)不會報錯再正式改表。上線前做這一步能避開絕大多數(shù)改完表服務(wù)掛了的尷尬。6.3 DROP VIEW失敗、視圖刪除不了處理辦法前面已經(jīng)提過先查依賴再用CASCADE。但再強調(diào)一次生產(chǎn)環(huán)境CASCADE要謹(jǐn)慎最好先把依賴鏈的視圖定義導(dǎo)出備份再執(zhí)行。特別是視圖之間的多層依賴CASCADE刪掉的可不止當(dāng)前這個視圖可能連帶刪掉一整串下游視圖。如果真的誤刪了視圖PostgreSQL沒有原生的回收站機制唯一能做的就是靠備份來恢復(fù)或者從之前導(dǎo)出的SQL腳本里重建。所以日常維護時定期導(dǎo)出所有視圖定義保存為版本化的SQL文件是一個投入小、收益極大的習(xí)慣。6.4 視圖性能隱患排查清單用了一段時間視圖后我整理了一個快速自查清單建設(shè)每次變更視圖都過一遍視圖定義里是否包含不必要的ORDER BY沒有LIMIT時排序無意義還浪費時間是否使用SELECT *——視圖應(yīng)明確列出需要的字段否則底層表加字段會導(dǎo)致視圖輸出變化破壞對外穩(wěn)定性是否有嵌套過深的多層視圖物化視圖是否有唯一索引、是否啟用了CONCURRENTLY刷新視圖注釋是否齊全依賴方是否清楚該視圖的業(yè)務(wù)口徑檢查完這幾點大部分視圖相關(guān)的坑都能提前避開。寫在最后的一個實用習(xí)慣最后分享一個我堅持了很久的個人習(xí)慣每次創(chuàng)建視圖我必定順手寫清楚注釋并把視圖名、業(yè)務(wù)口徑、依賴表清單、創(chuàng)建日期記下來。剛開始覺得是浪費時間后來維護第三年的項目時這些注釋幫我節(jié)省了大量溝通成本。視圖這東西時間越久越考驗當(dāng)初的定義質(zhì)量。別嫌麻煩給未來的自己留一條好走的回頭路。如果你剛接觸PostgreSQL視圖建議從一個自己熟悉的查詢開始試著把它封裝成視圖再試著用視圖去更新數(shù)據(jù)進行觀察。動手跑幾次你對視圖執(zhí)行邏輯的理解會比看十篇文章都扎實。有具體問題歡迎在評論區(qū)聊聊你在項目里遇到的視圖案例。