Mysql視圖的作用及其效能分析

標籤：style   color   使用   os   io   資料   ar   問題   

定義:視圖是從一個或幾個基本表匯出的表，它與基本表不同，是一個虛表。

作用：

  1.簡化操作,不用進行多表查詢。

  2.當不同種類的用使用者共用同一個資料庫時,非常靈活,(使用者以不同的

方式看待同一資料. 

  3.視圖對重構資料庫提供了一定程度的邏輯獨立性。

 資料的邏輯獨立性是指:如增加新的關係或對原有的關係增加新的

欄位,使用者的應用程式不受影響.

 例如：原有一個Student(Sno,Sname,Ssex,Sage,Sdept)這樣一個表.

     後來變動為:Sx(Sno,Sname,Sage)和SY（Sno,Ssex,Sdept)

兩個表。

    這時候原表Student為SX和SY表自然串連的結果。

那麼如果我們一開始建立了一個試圖：

 create view Student(Sno,Sname,Ssex,Sage,Sdept)

as  select SX.Sno,SX.Sname,SY.Ssex,SX,Sage,SY,Sdept

from SX,SY  where SX.Sno=SY.Sno;

 儘管資料庫的邏輯結構改變了，但是應用程式不必修改(因為這個這個

視圖所定義的關係沒有變啊）。 

【注意:試圖只能在一定程度上提供資料的邏輯獨立,比如由於

視圖的更新是有條件的,因此應用程式中修改資料的語句可能仍會

因為基本表構造的改變而改變.

 4. 視圖能夠對機密資料提供安全保護 

   有了視圖機制，就可以在設計資料庫應用系統時，對不同的使用者定義不同的視圖，使機密資料不出現在不應該看到這些資料 的使用者視圖上。這樣視圖機制就自動提供了對機密資料的安全保護功能。例如，Student表涉及全校15個院系學生資料，可以在其上定義15個視圖，每個視圖只包含一個院系的學生資料，並只允許每個院系的主任查詢和修改本原系學生視圖。

  5、適當的利用視圖可以更清晰地表達查詢

    例如經常需要執行這樣的查詢“對每個學生找出他獲得最高成績的課程號”。可以先定義一個視圖，求出每個同學獲得的最高成績：
CREATE VIEW VMGRADE
AS
SELECT Sno，MAX(Grade) Mgrade
FROM SC
GROUP BY Sno；
然後用如下的查詢陳述式完成查詢：

SELECT SC.Sno，Cno FROM SC，VMGRADE WHERE SC.Sno = VMGRADE.Sno AND SC.Grade = VMGRADE.Mgrade；

 MySql視圖的演算法及其效能分析:

        mysql在處理視圖時有兩種演算法,分別稱為merge和temptable。

在執行“create view”語句時可以指定使用哪種演算法,所謂merge是指在

處理涉及到視圖的操作時,將對視圖的操作根據視圖的定義進行展開,有點類似於

c語言中的宏展開. 

   例如設有以下表: 

 CREATE TABLE `comment` (

  `id` int(11) NOT NULL,

  `user_id` int(11) default NULL,

  `content` varchar(255) default NULL,

  PRIMARY KEY  (`id`),

  KEY `idx_comment_uid` (`user_id`)

) ENGINE=InnoDB;

假設user_id < 10000的使用者為VIP使用者，我們可以這樣建立一個視圖來表示VIP使用者的評論：

CREATE VIEW vip_comment AS SELECT * FROM comment WHERE user_id < 10000;這時我們在操作vip_comment視圖時使用的就是MERGE演算法

【一般在能夠使用merge演算法的時候mysql處理視圖上沒什麼效能問題,

但並非在任何時候都能使用merge演算法.事實上,只要視圖的定義稍稍有點複雜,mysql就沒辦法使用merge演算法了.準確的說,只要視圖定義中

使用了一下sql構造塊就無法使用merge演算法:

   (1)聚集合函式（2）distinct (3)group by (4)having 

（5）having (6)集合操作(在mysql只有union，union all，沒有except和intersect）（７）子查詢．】

 

確實，在視圖定義比較複雜的情況下，要對視圖操作進行有效最佳化是非常困難的。因此在這個時候，MySQL使用了一種以不變應萬變的方法，即先執行視圖定義，將其結果使用暫存資料表儲存起來，這樣後續對視圖的操作就轉化為對暫存資料表的操作。不能不說從單從軟體設計的角度看，這樣的方法非常的優雅，然而從效能角度，這一方法也是非常的差。

 

         比如我們希望使用如下的視圖來表示每個使用者的評論數，即：

      CREATE VIEW comment_count AS SELECT user_id, count(*) AS count FROM comment GROUP           BY user_id;

       使用這個視圖的時候，我們可能心裡有個小算盤。目前我們先用這個視圖頂著，如果效能確實有問題，那我們就        再來搞一張comment_count的表，其中就記下來每個使用者的評論數。而我們現在先用這個視圖是為了將來要          是改的話會方便點（這也是視圖--即教科書中所謂的外模式--這個東西存在的主要原因之一，另一主要原因是          便於許可權控制）。但是遇到了MySQL這個蠢貨，我們的算盤鐵定會失敗。

       我們來看一下指定user_id從comment_count選取記錄時的執行策略：

       mysql> explain select count(*) from comment_count where user_id = 90;

  可以看出,mysql首先是先執行comment_count的視圖定義,

將結果儲存在暫存資料表中,選擇出滿足"user_id=90”的那一條

記錄,這樣,雖然我們最終只需要統計90號使用者的評論數,並且comment

表的user_id欄位也有索引，mysql也會掃描整個comment表,並按

user_id分組計算出所有使用者的評論數。

【這裡面要注意的是即使在進行explain時，系統的物化也是要先執行的，

因此若評論很多的話explain也是一樣的慢。這個問題的根源是

mysql的查詢最佳化本來就存在很多問題.對於上述的查詢，要達到比較

好的最佳化效果在資料庫中一般是如下處理的：

1.將視圖的操作轉化為from字句中的子查詢. 

select * from (select user_id,count(*) as count from comment

group by user_id)as comment_count where user_id=90;

2.子查詢提升。因為子查詢中使用了group by,因此先將外面的條件

作為提升後的having條件

select user_id,count(*) as count from comment group by usr_id

having user_id=90;

3.由於having條件中不涉及聚集合函式,轉化為where條件

select user_id ,count(*) as count from comment where user_id=90

group by user_id;

4.由於指定where條件後，user_id已經是一個常數,根據常數group by

沒有意義,因此去掉group by。

select user_id,count(*) as count from comment where user_id=90

 除第4步無法根據EXPLAIN輸出和查詢效能判斷出MySQL是否進行這一最佳化外，前3類最佳化MySQL都不會進行。因此，MySQL要能夠有效處理上述查詢還有很長的路要走。

PS: 相對來說PostgreSQL的查詢最佳化能力就強得多，上面的查詢在PostgreSQL中就能夠產生上述最佳化後的最終執行計畫。PostgreSQL比較關注查詢最佳化估計與PostgreSQL的學院派風格或PostgreSQL中的rule system有關。