MySQL 視圖(View)原理解析
MySQL 5.0以后引入了視圖。視圖實(shí)際是一個(gè)自身不存儲(chǔ)數(shù)據(jù)的虛擬數(shù)據(jù)表。實(shí)際這個(gè)虛擬表的數(shù)據(jù)來(lái)自于訪問(wèn)視圖的 SQL 查詢的結(jié)果。MySQL 處理視圖和處理數(shù)據(jù)表差不多,通過(guò)這種方式來(lái)滿足很多需求。視圖和數(shù)據(jù)表在 MySQL 中共享命名空間,然而 ,MySQL 處理而二者的方式并不相同,例如,視圖沒(méi)有觸發(fā)器,并且無(wú)法使用 DROP TABLE 移除視圖。
下面以 world 樣例數(shù)據(jù)庫(kù)為例來(lái)展示視圖的工作機(jī)制。
CREATE VIEW Oceania AS SELECT * FROM Country WHERE Continent = 'Oceania' WITH CHECK OPTION;
實(shí)現(xiàn)視圖最簡(jiǎn)單的方式是執(zhí)行SELECT查詢語(yǔ)句并將結(jié)果放入到一張臨時(shí)表中。之后,就可以在視圖出現(xiàn)的地方引用這張臨時(shí)表。例如下面的查詢語(yǔ)句:
SELECT Code, Name FROM Oceania WHERE Name = 'Australia';
下面是服務(wù)端執(zhí)行上面語(yǔ)句可能的形式(臨時(shí)表名稱是隨意取的,實(shí)際內(nèi)部不知道是什么):
CREATE TEMPORARY TABLE TMP_Oceania_123 AS SELECT * FROM Country WHERE Continent = 'Oceania'; SELECT Code, Name FROM TMP_Oceania_123 WHERE NAME = 'Australia';
這種形式顯然存在性能問(wèn)題,最好的方式是將視圖和查詢的分布查詢改為一句 SQL 語(yǔ)句,如下所示:
SELECT Code, Name FROM Country WHERE Continent = 'Oceania' AND Name = 'Australia';
在 MySQL 中會(huì)使用兩種算法,稱之為 MERGE 和 TEMTABLE,而且會(huì)盡可能地使用 MERGE 算法。甚至,MySQL 能夠?qū)⑶短滓晥D進(jìn)行合并。下圖是兩種算法的區(qū)別:
當(dāng)視圖中有 GROUP BY,DISTINCT,聚集函數(shù),UNION,子查詢或其他數(shù)據(jù)表之間不是一對(duì)一的關(guān)系時(shí),MySQL 會(huì)使用 TEMPTABLE算法。如果想知道視圖是使用 MERGE 還是 TEMPTABLE,可以使用 EXPLAIN 指令檢查:
EXPLAIN SELECT * FROM <視圖名稱>;
如果在 select_type 中有 DERIVED 的話,則表示使用了 TEMPTABLE 算法。因此,如果隱藏的衍生表需要很高的代價(jià)產(chǎn)生,EXPLAIN 就會(huì)變得性能很低并且執(zhí)行起來(lái)很慢,這是因?yàn)樗枰獙?shí)際執(zhí)行和構(gòu)建衍生表。這個(gè)算法是視圖的屬性而不會(huì)受到查詢類型的影響。例如,假設(shè)創(chuàng)建視圖的時(shí)候指定了算法,那么以后針對(duì)這個(gè)視圖的查詢都不會(huì)更改算法,即便有優(yōu)化的空間:
CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM Country;
可更新視圖
可更新視圖可以通過(guò)視圖更新隱藏的基礎(chǔ)表,只要指定的條件保持,就可以使用 UPDATE,DELETE 甚至是 INSERT 操作,就像操作普通表一樣,例如下面的操作是有效的:
UPDATE Oceania SET Population = Population * 1.1 WHERE NAME = 'Australia';
如果視圖包括 GROUP BY,UNION,聚合函數(shù)或其他的一些概念,那么該視圖就不可更新。所有使用了 TEMPTABLE 算法的視圖都不可以更新。
CHECK OPTION 子句用于保證任何通過(guò)視圖更改的數(shù)據(jù)行在更改后需要保持與視圖的 WHERE條件匹配。例如上面的例子,如果插入了一條 Continent 值不同的行,服務(wù)端就會(huì)報(bào)錯(cuò)。
視圖的性能
很多人不會(huì)考慮使用視圖提升性能,但是在某些情況下視圖是可以提高性能的。而且還可以用視圖去提升其他方面的性能,例如,在表結(jié)構(gòu)重構(gòu)時(shí),被修改的數(shù)據(jù)表的視圖不經(jīng)修改也可以使用。還可以使用視圖實(shí)現(xiàn)字段權(quán)限控制而不增加創(chuàng)建列權(quán)限的負(fù)荷:
CREATE VIEW public.employeeinfo AS SELECT firstname, lastname --不包含身份證號(hào) FROM private.employeeinfo; GRANT SELECT ON public.* to public_user;
使用 TEMPTABLE 算法的視圖性能可能很糟糕(雖然也有可能比等效的 SQL 查詢性能高)。這種視圖可優(yōu)化的空間不高。
視圖可能讓開(kāi)發(fā)者誤以為視圖很簡(jiǎn)單,而事實(shí)上視圖非常復(fù)雜。如果開(kāi)發(fā)者不懂的試圖的復(fù)雜性,那么就不會(huì)注意到視圖與普通表查詢之間的差別。如果使用EXPLAIN 指令的話有時(shí)候會(huì)發(fā)現(xiàn)產(chǎn)生上百行的分析結(jié)果輸出,這是因?yàn)閷?shí)際看起來(lái)是數(shù)據(jù)表的查詢實(shí)際是視圖,而視圖可能引用其他數(shù)據(jù)表甚至是其他視圖。
在使用視圖改進(jìn)性能時(shí),需要仔細(xì)分析和測(cè)試。即便是 MERGE 算法的視圖也會(huì)增加額外的負(fù)擔(dān),而且很難預(yù)測(cè)對(duì)性能的影響。視圖實(shí)際在 MySQL 中使用了另外的優(yōu)化途徑。在高并發(fā)場(chǎng)景,視圖可能導(dǎo)致查詢優(yōu)化器耗費(fèi)大量時(shí)間在做計(jì)劃和統(tǒng)計(jì),甚至導(dǎo)致服務(wù)端卡頓。這個(gè)時(shí)候需要使用普通的 SQL 來(lái)替代視圖。
視圖的限制
MySQL 不像其他數(shù)據(jù)庫(kù)服務(wù)器那樣支持物理視圖(物理視圖即產(chǎn)生并將結(jié)果存在一個(gè)不可見(jiàn)的數(shù)據(jù)表中,并周期性地更新以從源數(shù)據(jù)刷新視圖)。MySQL 也不支持視圖的索引。MySQL 也不會(huì)保留視圖的原始 SQL,如果我們視圖通過(guò)執(zhí)行 SHOW CREATE VIEW 指令去編輯視圖,并且更改返回結(jié)果 SQL,會(huì)發(fā)現(xiàn)結(jié)果很奇特。查詢SQL會(huì)按規(guī)范展開(kāi),并且使用內(nèi)部的格式包裹,且沒(méi)有格式化、注釋和縮進(jìn)。
以上就是MySQL 視圖(View)原理解析的詳細(xì)內(nèi)容,更多關(guān)于MySQL 視圖(View)原理的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
解析mysql二進(jìn)制日志處理事務(wù)與非事務(wù)性語(yǔ)句的區(qū)別
本篇文章是對(duì)mysql二進(jìn)制日志處理事務(wù)與非事務(wù)性語(yǔ)句的區(qū)別進(jìn)行了詳細(xì)的分析介紹,需要的朋友參考下2013-06-06mysql 獲取規(guī)定時(shí)間段內(nèi)的統(tǒng)計(jì)數(shù)據(jù)
這篇文章主要介紹了mysql 獲取規(guī)定時(shí)間段內(nèi)的統(tǒng)計(jì)數(shù)據(jù)的相關(guān)資料,需要的朋友可以參考下2017-05-05ubuntu20.04?安裝?MySQL5.7過(guò)程記錄
這篇文章主要介紹了ubuntu20.04?安裝?MySQL5.7過(guò)程記錄的相關(guān)資料,需要的朋友可以參考下2022-10-10VMware中Linux共享mysql數(shù)據(jù)庫(kù)的方法
VMware中Linux共享mysql數(shù)據(jù)庫(kù)的方法,需要的朋友可以參考下。2010-11-11Mysql升級(jí)到5.7后遇到的group by查詢問(wèn)題解決
這篇文章主要給大家介紹了關(guān)于Mysql升級(jí)到5.7后遇到的group by查詢問(wèn)題的解決方法,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)同樣遇到這個(gè)問(wèn)題的朋友們具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧。2017-09-09MySQL 獲得當(dāng)前日期時(shí)間 函數(shù)
這篇文章主要介紹了MySQL 獲得當(dāng)前日期時(shí)間 函數(shù) 非常不錯(cuò),具有參考借鑒價(jià)值,需要的朋友可以參考下2017-07-07