iT邦幫忙

DAY 10
3

只談MySQL系列 第 10

只談MySQL (第十天) View...

View在很多資料庫系統中, 是相當有應用彈的物件, 基本上, 我們常利用View來:

  1. 把常用的SELECT命令句寫成View, 這樣可以減少每次執行相同View時的解析時間, 從而提升程式運作效率
  2. 在不實體改變資料表結構下, 將資料表的結構重新組合, 以達到前端應用程式的需要
    我們拿銷貨單為例子說明, 銷貨單一般會分為表頭資料表, 如Sales_Order_Header, 及表身資料表, 如Sales_Order_Detail, 這兩個資料表的內容為:
  3. 銷貨單表頭Sales_Order_Header
    mysql> CREATE TABLE Sales_Order_Header
    (SalesID CHAR(10) NOT NULL PRIMARY KEY (SalesID),
    CustomerName VARCHAR(30) NOT NULL, SalesDate DATETIME NOT NULL,
    DueDate DATE, ShipDate DATETIME, Status CHAR(1) NOT NULL);
    其中Status欄位的內容值有: N:新單, P:處理中, R:退件, X:取消, S:交貨中, C:結案
    SalesID是索引主鍵
  4. 銷貨單表身Sales_Order_Detail
    mysql> CREATE TABLE Sales_Order_Detail
    (SalesID CHAR(10) NOT NULL, ItemID CHAR(10) NOT NULL,
    ItemName VARCHAR(50), Unit VARCHAR(10), Quantity INT,
    Price DECIMAL(10, 2), Amount DECIMAL(12, 2));
    建一個惟一Index
    mysql> CREATE UNIQUE INDEX SOD ON Sales_Order_Detail(SalesID, ItemID);
    我們常會用這樣的SELECT命令來建立查詢銷貨單的表單(WindwosForms, 有Windows程式開發經驗的網友會瞭解我在說什麼):
    SELECT SH.SalesID, SH.CustomerName, SH.DueDate, SH.ShipDate, SH.Status,
    SD.ItemID, SD.ItemName, SD.Unit, SD.Quantity, SD.Price, SD.Amount
    FROM Sales_Order_Header SH, Sales_Order_Detail SD
    WHERE SH.SalesID = SD.SalesID
    AND SH.Status NOT IN ("R", "X", "C");
    這個SELECT命令句查詢目前仍在處理過程中的銷貨訂單, 每次一執行到, MySQL的引擎就會先看其Cache還有沒有已解析的命令在Cache中, 如果沒有, 就再做一次解析, 放到Cache中再執行, 當很多前端應用程式執行相同SELECT命令時, 就會導致系效能變差.
  5. 建立View: v_Sales_Order_In_Process
    mysql> CREATE View v_Sales_Order_In_Process
    AS
    SELECT SH.SalesID, SH.CustomerName, SH.DueDate, SH.ShipDate,
    SH.Status, SD.ItemID, SD.ItemName, SD.Unit, SD.Quantity,
    SD.Price, SD.Amount
    FROM Sales_Order_Header SH, Sales_Order_Detail SD
    WHERE SH.SalesID = SD.SalesID
    AND SH.Status NOT IN ("R", "X", "C");
    由於放在View中, 所以View建立時語法就已經解析完成, 每次叫用v_Sales_Order_In_Process時, 就由系統暫存區把解析過的SELECT命令取出執行, 就不用再解析了, 從而提升系效能.
    此外, 我們用View重組了Sales_Order_Header和Sales_Order_Detail兩個資料表, 而且沒有實體建立任何新的資料表, 資料表內容也不會重覆建立. 因為View本身是空集, 只有等到被叫用時, 才像是個過濾器把資料篩選出來.
    CREATE VIEW指令的完整語法如下:
    CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]
    提供給各位參考...
    在MySQL中, View的限制為:
  6. SELECT命令句中, 在FROM裏面不能有Subquery
    即 SELECT .... FROM (SELECT .... FROM ...) 這樣是不行使用的
    2 SELECT命今句中不能參照到任何變數
  7. CREATE VIEW時, 所有參照到的物件都必需存在, 但有可能在View建立好後, 把參照的Table或View給刪除掉了, 這樣還是OK, 只是View的執行就會出錯誤, 要避免這種錯誤, 要用CHECK TABLE指令, 其語法為:
    CHECK TABLE tbl_name [, tbl_name] ... [option] ...
    option = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
  8. 不能參照到Temporary Tables.
  9. 不能和任何Trigger相關
  10. View中定義欄位別名, 長度必需在64個字元內
    今天對於View的分享就談到這裏, 我們明天再見...

上一篇
只談MySQL (第九天) Index...
下一篇
只談MySQL (第十一天) MySQL的資料處理指令
系列文
只談MySQL30

2 則留言

0
fireflybug
iT邦研究生 5 級 ‧ 2009-10-23 10:16:53

mysql> CREATE INDEX v_Sales_Order_In_Process
AS
SELECT SH.SalesID, SH.CustomerName, SH.DueDate, SH.ShipDate,
SH.Status, SD.ItemID, SD.ItemName, SD.Unit, SD.Quantity,
SD.Price, SD.Amount
FROM Sales_Order_Header SH, Sales_Order_Detail SD
WHERE SH.SalesID = SD.SalesID
AND SH.Status NOT IN ("R", "X", "C");

CREATE INDEX v_Sales_Order_In_Process <==請問這是不是打錯了? Create VIEW?還是INDEX?

小弟是外行人,想請教啥時候,比較適合使用VIEW,能否舉幾個實例?

目前小弟的理解是:如果今天公司內部程式,每個使用者都會用到報單資料查詢列表,而這些資料列表預設都是列出今日的資料,現今有10位使用者同時使用報單查詢列表,這時候程式內使用CREATE VIEW會比直接使用SELECT查詢資料庫效能好?不知道觀念是否正確,還望大大提點一二,謝謝。

fireflybug iT邦研究生 5 級‧ 2009-10-23 10:18:46 檢舉

另外想請問,當我們建立了CREATE VIEW,如果不使用了,它會佔用記憶體嗎?需要手動消毀嗎?還是程式會自動FREE它?

賽門 iT邦超人 1 級‧ 2009-10-23 13:06:44 檢舉

感謝指正, 已訂正, 謝謝!
一般來說, 用View的時機有兩個, 一個就是您說的在做報表的時候, 另一個是因為前端應用程式需要 一個就是您說的在做報表的時候, 另一個是因為前端應用程式需要與現在Table內容排列不同的結構, 就用View來建一個需要的結構出來..

0
賽門
iT邦超人 1 級 ‧ 2009-10-23 13:03:33

基本上View是資料庫中的定義, 不會佔用記憶體, 但佔用硬碟一點空間存放其定義及己解析過的語法...這樣如有前端應用叫用讓View, 就會載入Cache(記憶體)開始運作, 運作完畢後, 過一段時間, 資料庫引擎判斷不會再用到, 就會把佔用的Cache空間釋放出來作其他用途..

fireflybug iT邦研究生 5 級‧ 2009-10-23 14:21:18 檢舉

感謝您的回覆,讓小弟對VIEW的應用有了進一步的認識。對詳細運做流程還是有點疑慮,就是如果今天第一個使用者開啟程式,程式第一次建立了VIEW,然後第二個使用者再開程式,資料庫就會直接存取在記憶體中的VIEW,而不會再做重建的動作,也就自然的少了再次直接查詢資料庫的查詢動作,而讓系統負載較低是嗎?
再請問,您知道系統釋放記憶體的時間是依據那裡的設定?還有預設大概是多久?

fireflybug iT邦研究生 5 級‧ 2009-10-23 14:43:13 檢舉

小弟剛試了一下,如果程式一開始建立了VIEW,第二次執行程式,sql會出錯(資料庫中已經有一個名為 'V_REGION_SALES' 的物件。)
請問sql或是程式中有辦法判定這個VIEW已經存在而不再去CREATE一次嗎?

另外測試過了,建立後的VIEW TABLE資料,會隨著主TABLE資料的新增,VIEW TABLE也會自動跟著新增!

fireflybug iT邦研究生 5 級‧ 2009-10-23 14:52:39 檢舉

弟查了GOOGLE,使用下面語法判定有無值:
SELECT OBJECT_ID('V_REGION_SALES')
若有值代表已經建立了VIEW TABLE,若NULL則需要做建立VIEW TABLE的動作。

我要留言

立即登入留言