高性能MYSQL（學習筆記）-MySQL進階特性2

視圖

概念和特點：

1、  視圖是一張虛拟表，本身不存放任何資料

2、  視圖傳回資料是從MySQL其他表生成

3、  重用SQL語句、簡化複雜的SQL操作，可以友善重用而隐藏底層查詢細節

4、  使用表的部分列而不是整張表權限

5、  保護資料，提供某些權限給使用者而不是整張表權限

限制

視圖必須唯一命名（名稱不能和表相同）、數量不限、建立視圖需要授權、可以嵌套、視圖不能索引，不能有關聯的觸發器或預設值、可以和表一起使用。

例子：

CREATE VIEW Oceania AS SELECT * FROM country where continent=‘Oceania’ with check option

實作視圖方法是将select 語句結果存放到臨時表中，當需要通路資料時，直接通路此臨時表：selectcode,name from oceania where name=’China’；

下面是使用臨時表模拟視圖的方法：create template table tmp_oceania as select * from country wherecontinent=’oceania’;

Select code,name from tmp_oceania where name = ‘China’;

這樣會有明顯的性能問題，優化器很難優化這個臨時表上的查詢，實作視圖更好的方法是重寫含有視圖的查詢，将視圖定義SQL直接包含進查詢的SQL中。

Select code,name from country where continent = ‘oceania’ and name=’australia’；

MySQL可以使用這兩種方法來處理視圖，成為合并算法（MERGE）和臨時表算法（TEMPTABLE），盡可能使用合并算法，如果采用臨時表算法，EXPLAIN會顯示派生表（DERIVED）。

如果視圖中包含 GROUP BY 、DISTINCT、任何的聚合函數、UNION、子查詢等，隻要無法再原表記錄和視圖記錄中建立一一映射的場景中，MySQL都将使用臨時表算法實作視圖。

可更新視圖

UPDATABLE VIEW 指的是可以通過更新這個視圖來更新視圖涉及的相關表，隻要指定了合适的條件就可以更新、删除甚至寫入資料：

Update oceaniaset population = population*1.1 where name =’australia’;

但是如果視圖定義中包含了group by、union、聚合函數以及其他情況，就不能被更新了，所有臨時表算法實作的視圖都無法被更新！！！

不能更新視圖定義列以外的列！！！

外鍵限制

InnoDB是目前MySQL中唯一支援外鍵的内置存儲引擎，使用外鍵是有成本的，比如外鍵通常都要求每次在修改資料時都要在另外一張表中執行一次查找操作，雖然InnoDB強制外鍵使用索引，但是無法消除這種限制檢查的開銷。如果外鍵列的選擇很低，那麼導緻一個非常大且選擇性很低的索引。

外鍵也會提升一些性能，例如想確定兩個相關表始終有一緻的資料，那麼使用外鍵比在應用程式中檢查一緻性的性能要高很多。外鍵在相關資料的删除和更新上，比在應用中維護高效很多，不過外鍵維護操作是逐行進行的，這樣的更新會比批量删除和更新慢。

如果隻是使用外鍵做限制，那通常在應用程式裡實作限制會更好，外鍵會帶來額外的開銷！

在MySQL内部存儲代碼

MySQL允許通過觸發器、存儲過程、函數的形式來存儲代碼，MySQL5.1開始還能在定時任務中存放代碼，也被稱為“事件”，存儲過程和存儲函數統稱為“存儲程式”。存儲過程和存儲函數都可以接收參數然後傳回值，到那時觸發器和事件卻不行。MySQL中使用存儲代碼的優點：

1、  在伺服器内部執行，離資料近，在伺服器上可以節省帶寬和網絡延遲。

2、  這是一種代碼重用，可以友善地統一業務規則，保證某些行為總是一緻，是以也可以應用提供一定的安全性。

3、  簡化代碼維護和版本更新。

4、  提升安全，提供更細粒度的權限控制，比如銀行用于轉移資金的存儲過程：這個存儲過程可以在一個事務中完成資金轉移和記錄用于審計的日志。這個存儲過程可以再一個事務中完成資金轉移和記錄用于審計的日志。

5、  伺服器端可以緩存存儲過程的執行計劃，對于需要反複調用的過程，會大大降低消耗。

6、  因為是部署在服務端的，是以備份、維護都可以再伺服器端完成，是以存儲程式的維護工作都會很簡單，沒有什麼外部依賴。

7、  可以在應用開發和資料庫開發人員之間更好的分工，不過最好是由資料庫專家來開發存儲過程，因為不是每個應用開發人員都能寫出高效的MySQL查詢。

存儲代碼的缺點：

1、  MySQL本身沒有提供好用的開發和調試工具，編寫MySQL的存儲代碼比其他的資料庫要更難。

2、  存儲代碼比應用程式的代碼效率稍差，使用的函數有限，

3、  存儲代碼給應用程式代碼部署帶來額外的複雜性，需要額外布置額外的MySQL内部存儲代碼

4、  因為存儲程式都在伺服器内，是以有安全隐患，攻擊者隻需要攻破資料庫就可以。

5、  存儲過程會給伺服器增加額外的壓力，資料庫伺服器的擴充性比應用伺服器差。

6、  MySQL沒有選項可以控制存儲程式的資源消耗，存儲過程的一個下問題，可能直接把伺服器拖死。

7、  MySQL較PL/SQL、T-SQL的存儲代碼功能還是比較弱

8、  調試MySQL的存儲過程是一個困難的事

存儲過程和函數

MySQL架構本身和優化器的特性使得存儲代碼有一些天然的限制,他的性能也受限于此:

1、  優化器無法使用關鍵字DETERMINISTIC來優化單個查詢中多次調用存儲函數的情況

2、  優化器無法評估存儲函數的執行成本

3、  每個連接配接器都有獨立的存儲過程的執行計劃緩存，如果有多個連接配接需要調用同一個存儲過程，将會浪費緩存空間來反複緩存同樣的執行計劃，使用的是連接配接池或者持久化連接配接那麼執行緩存計劃可能會有更長的生命周期

4、  存儲程式和複制是一個詭異組合，如果可以不要複制對存儲程式的調用。

   不過，對于一些操作，存儲過程比其他的要快得多——特别是當一個存儲過程調用可以代替很多小查詢的時候，如果查詢很小，相比這個查詢執行成本，解析和網絡開銷就變得非常明顯。

觸發器

     觸發器可以在讓你執行INSERT、UPDATE或者DELETE的時候，執行一些特定的操作。可以在MySQL上指定是在SQL語句執行前觸發還是在執行後觸發，觸發器本身不反悔值，但是可以讀取或者改變觸發SQL語句影響的資料。觸發器使用中需要注意幾點：

1、  對每一個表的每一個事件，最多隻能定義一個觸發器（不能在AFTER INSERT 上定義兩個觸發器）

2、  MySQL隻支援基于行的觸發，也就是說觸發器始終是針對一條記錄，而不是針對整個SQL語句。

3、  觸發器可以掩蓋伺服器背後的工作一個簡單的SQL語句背後，因為觸發器可能包含了很多看不見的工作

4、  觸發器的問題很難排查，如果某個性能問題和觸發器相關，很難分析和定位。

5、  觸發器可能導緻死鎖和鎖等待，如果鎖失敗那麼原來的SQL語句也會失敗。

如果僅考慮性能，那麼MySQL觸發器的實作對伺服器限制最大的就是它的“基于行的觸發”設計，因為性能原因，他很多時候無法使用觸發器來維護彙總和緩存表。

MyISAM觸發器無法保證更新的原子性，也就說沒有辦法復原操作的,當建立一個AFTER UPDATE的觸發器，用來更新另外一個MyISAMbiao ,如果在更新第二張表時候失敗，第一張表是不會復原的。

InnoDB表上的觸發器是在同一個事務中完成的，它們執行操作是原子性的，同時失敗或者同時成功。

事件

MySQL事件類似于Linux的定時任務，不過完全在MySQL内部中實作，可以建立事件讓MySQL在某一個時候執行一段代碼或者每隔一個時間執行一段SQL代碼，通常會把複雜的SQL都封裝成一個存儲過程中，這樣事件在執行的時候隻需要做一個簡單的CALL調用。

一些存儲過程的考慮也可以同樣适用于事件，建立事件意味着給伺服器帶來額外的工作，事件實作本身的開銷不大，但是時間需要執行SQL則可能對性能有很大的影響，事件的一些典型應用包括：定期維護任務、重建緩存、建構彙總表來模拟物化視圖，存儲用于監控和診斷的狀态值。

注：

如果一個定時事件執行需要很長的時間，那麼有可能出現事件沖突情況，MySQL本身不會防止這種并發，是以使用者需要自己編寫這種情況防止并發代碼，可以使用GET_LOCK()來確定目前隻有一個事件在被執行：

CREATE EVENT optimize_somedb ONSCHEDULE EVERY 1 WEEK

DO

BEGIN

   DECLARE CONTINUE HANLDER FOR SQLEXCEPTOIN

     BEGIN END;

IF GET_LOCK(‘somedb’，0) THEN

  DO CALLoptimize_tables(‘somedb’);

END IF;

DO RELEASE_LOCK(‘somedb’);

END

這裡的“CONTINUE HANLDER”用來確定，即使當事件執行出現了異常，仍然會釋放持有的鎖。

綁定變量

MySQL支援服務端的綁定變量，這樣提高了用戶端和服務端資料傳輸的效率，當建立一個綁定變量的SQL語句時，用戶端向伺服器發送了一個SQL語句的原型服務端收到後，解析并存儲這個SQL語句的部分執行計劃，傳回給用戶端一個SQL語句的處理句柄，以後每次執行這類查詢，用戶端都指定使用這個句柄。

INSERT INTO tb1(col1,col2,col3)VALUES(?,?,?)；可以通過向伺服器端發送這個問好的取值和之歌SQL的句柄來執行一個具體的查詢。MySQL在使用綁定變量的時候可以高效的執行大量的重複語句，原因在于：

1、  伺服器端隻需要解析一次SQL語句

2、  伺服器端某些優化器的工作隻需要執行一次，因為他會緩存一部分的執行計劃。

3、  以二進制的方式隻發送參數和句柄，比起每次都發送ASCII碼文本效率更高。二進制協定在用戶端能節省很多記憶體，減少網絡開銷

4、  僅僅是參數而不是整個查詢語句需要發送到伺服器端，網絡開銷更小

5、  在存儲參數時，直接将其放在緩存中，不需要再記憶體中多次複制。

6、  相對安全，無需再應用程式中處理轉義，簡單并且減少SQL注入和攻擊風險。

綁定變量的優化

優化分為三類：

1、  準備階段，伺服器解析SQL語句，移除不可能的條件，并重寫子查詢

2、  第一次執行時候，伺服器先簡化嵌套循環的關聯，并将外關聯轉化成内關聯

3、  每次SQL語句執行時，過濾分區、盡量移除 COUNT().MIN(),MAX()、移除常數表達式、檢測常量表、做必要的等值傳播、分析和優化ref、range和索引優化等通路資料的方法、優化關聯順序。

有些優化隻需要做一次，但是上面的操作還是都會被執行。

綁定變量的限制

關于綁定變量的限制和注意項如下：

1、  綁定變量是會話級别，連接配接之間不能共用綁定變量句柄，一旦連結斷開，原來句柄也不能再使用了。（連接配接池和持久化連接配接在一定程度上緩解這個問題）

2、  Mysql5.1版本前，綁定變量的SQL是不能使用查詢緩存的

3、  并不是所有時候使用綁定變量都能獲得更好的性能，如果隻執行一次SQL那麼使用綁定變量方式無疑比直接執行多了一次額外的準備階段消耗，還需要一次網絡開銷

4、  目前版本下，還不能在存儲函數中使用綁定變量

5、  如果忘記釋放綁定變量，則伺服器端很容易發生資源洩露

6、  有些操作如BEGIN，無法在綁定變量中完成。