MySql架構

MySql並不完美，但是足夠靈活，能夠適應高要求的環境，例如Web類應用。同時，MySql既可以嵌入到應用程式中，也可以支援資料倉儲、內容索引和部署軟體、高可用的冗餘系統、線上事務處理系統（OLTP）等各種應用型別。

MySql最重要、最與眾不同的特性是它的儲存引擎架構，這種架構的設計將查詢處理及其它系統任務和資料的儲存/提取相分離。這種處理和儲存分離的設計可以在使用時根據效能、特性，以及其它需求來選擇資料儲存的方式。

1、 MySql邏輯架構

下圖是MySql的邏輯架構圖：

第一層的服務主要是連線處理、授權認證、安全等。
第二層架構是MySql的核心服務功能所在的層次。包括查詢解析、分析、優化、快取以及所有的內建函式（例如，日期，時間，資料和加密函式），所有跨儲存引擎的功能都在這一層實現：儲存過程、觸發器、檢視等。
第三次包括了儲存引擎，儲存引擎負責MySql中資料的儲存和提取。伺服器通過API與儲存引擎進行通訊，這些介面遮蔽了不同儲存引擎之間的差異，使得這些差異對上層的查詢過程透明。儲存引擎API包含幾十個底層函式，用於執行諸如“開始一個事務”或者“根據主鍵提取一行記錄”等操作。

• 連線管理與安全性

每個客戶端連線都會在伺服器程式中擁有一個執行緒，這個連線的查詢只會在這個單獨的執行緒中執行，該執行緒只能輪流在某個CPU核心或者CPU中執行。伺服器會負責快取執行緒，因此不需要為每一個新建的連線建立或者銷燬執行緒。

當客戶端連線到MySql伺服器時，伺服器需要對其進行認證。認證基於使用者名稱、原始主機資訊和密碼。如果使用了安全套接字(SSL)的方式連線，還可以使用X.509證書認證。一旦客戶端連線成功，伺服器會繼續驗證該客戶端是否具有執行某個特定查詢的許可權（例如，是否允許客戶端對world資料庫的Country表執行select語句）。

• 優化與執行

MySql會解析查詢，並建立內部資料結構（解析樹），然後對其進行各種優化，包括重寫查詢、決定表的讀取順序，以及選擇合適的索引等。使用者可以通過特殊的關鍵字提示優化器，影響它的決策過程。

對於select語句，在解析查詢之前，伺服器會先檢查查詢快取，如果能夠在其中找到對應的查詢，伺服器就不必再執行查詢解析、優化和執行的整個過程，而是直接返回查詢快取中的結果集。

2、併發控制

無論何時，只要有多個查詢需要在同一個時刻修改資料，都會產生併發控制的問題。

• 讀寫鎖

在處理併發讀或者寫的時候，可以通過實現一個由兩種型別的鎖組成的鎖系統來解決問題。這兩種型別的鎖通常被稱為共享鎖（shared lock）和排他鎖（exclusive lock），也叫讀鎖（read lock）和寫鎖（write lock）。

讀鎖是共享的，或者說是相互不阻塞的。多個客戶在同一個時刻可以同時讀取同一個資源，而互不干擾。寫鎖則是排他的，也就是說一個寫鎖會阻塞其它的寫鎖和讀鎖，這是出於安全策略的考慮，只有這樣，才能確保在給定的時間裡，只有一個使用者能執行寫入，並防止其它使用者讀取正在寫入的同一資源。

在實際的資料庫系統中，每時每刻都在發生鎖定，當某個使用者在修改某一部分資料時，MySql會通過鎖定防止其它使用者讀取同一資料。大多數時候，MySql鎖的內部管理都是透明的。

• 鎖粒度

一種提高共享資源併發性的方式就是讓鎖定物件更有選擇性。儘量只鎖定需要修改的部分資料，而不是所有的資料。在給定的資源上，鎖定的資料量越少，則系統的併發程式越高，只要相互之間不發生衝突即可。

問題是加鎖也需要消耗資源。鎖的各種操作，包括獲得鎖、檢查鎖是否已經解除、釋放鎖等，都會增加系統的開銷。如果系統花費大量的時間來管理鎖，而不是存取資料，那麼系統的效能可能會因此受到影響。

所謂的鎖策略，就是在鎖的開銷和資料的安全性之間尋求平衡，這種平衡當然會影響到效能。大多數資料庫系統一般都是在表上施加行級鎖(row-level lock)，並以各種複雜的方式來實現，以便在鎖比較多的情況下儘可能地提供更好的效能。

MySql則提供了多種選擇。每種MySql儲存引擎都可以實現自己的鎖策略和鎖粒度。下面介紹兩種最重要的鎖策略。

（1）表鎖
表鎖是MySql中最基本的鎖策略，並且是開銷最小的策略。表鎖會鎖定整張表。一個使用者在對錶進行寫操作（插入、刪除、更新等）前，需要先獲得寫鎖，這會阻塞其它使用者對該表的所有讀寫操作。只有沒有寫鎖時，其它讀取的使用者才能獲得讀鎖，讀鎖之間是不相互阻塞的。
寫鎖比讀鎖有更高的優先順序，因此一個寫鎖請求可能會被插入到讀鎖佇列的前面。

（2）行級鎖
行級鎖可以最大程度地支援併發處理，同時也帶來了最大的鎖開銷。在InnoDB和XtraDB，以及其它一些儲存引擎中實現了行級鎖。行級鎖只在儲存引擎層實現，而MySql伺服器層沒有實現。伺服器層不瞭解儲存引擎中的鎖實現。

3、事務

事務就是一組原子性的SQL查詢。該組SQL語句要麼全部執行成功，要麼所有語句都不會執行。

使用start transaction語句開始一個事務，然後要麼使用commit提交事務將修改的資料持久保留，要麼使用rollback撤銷所有的修改。

start transaction;
......sql語句；
commit;

一個執行良好的事務處理系統，必須具備ACID標準。ACID表示原子性（atomicity）、一致性（consistency）、隔離性（isolation）和永續性（durability）。

原子性：事務中的所有操作要麼全部執行成功，要麼都不會執行。
一致性：資料庫總是從一個一致性的狀態轉移到另外一個一致性的狀態。
隔離性：一個事務所做的修改在最終提交之前，對其它事務是不可見的。
永續性：一旦事務提交，則其所做的修改就會永久儲存到資料庫中。

一個實現了ACID的資料庫，相比沒有實現ACID的資料庫，通常會需要更強的CPU處理能力、更大的記憶體和更多的磁碟空間。對於一些不需要事務的查詢類應用，選擇一個非事務型的儲存引擎，可以獲得更高的效能。即使儲存引擎不支援事務，也可以通過 lock tables語句為應用提供一定程度的保護。

- 隔離級別

在SQL標準中定義了四種隔離級別，每一種級別都規定了一個事務中所做的修改，哪些在事務內和事務間是可見的，哪些是不可見的。較低階別的隔離通常可以執行更高的併發，系統的開銷也更低。

下面是四種隔離級別的介紹：

(1)Read Uncommitted(未提交讀)
在Read Uncommitted級別，事務中的修改，即使沒有提交，對其它事務也都是可見的。事務可以讀取未提交的資料，這也被稱為髒讀。這個級別會導致很多的問題，在實際應用中一般很少使用。

(2)Read Committed(提交讀)或不可重複讀
大多數的資料庫系統的預設隔離級別是Read Committed（但MySql不是）。Read Committed滿足前面提高的隔離性的定義：一個事務開始前，只能看見已經提交的事務所做的修改。也就是說，一個事務從開始直到提交之前，所做的任何修改對其它事務都是不可見的。這個級別有時候也叫做不可重複讀，因為兩次執行同樣的查詢，可能會得到不一樣的結果。

(3)Repeatable Read(可重複讀)
Repeatable Read解決了髒讀的問題。該級別保證了在同一個事務中多次讀取同樣記錄的結果是一致的。但是理論上，Repeatable Read還是無法解決另外一個幻讀（phantom Read）的問題。所謂幻讀，指的是當某個事務在讀取某個範圍內的記錄時，另外一個事務又在該範圍內插入了新的記錄，當之前的事務再次讀取該範圍的記錄時，會產生幻行。InnoDB和XtraDB儲存引擎通過多版本併發控制（MVCC）解決了幻讀的問題。

可重複讀是MySql的預設事務隔離級別。

(4)Serializable(可序列化)
Serializable是最高的隔離級別。它通過強制事務序列執行，避免了前面說的幻讀的問題。簡單來說，Serializable會在讀取的每一行資料上都加鎖，所以可能導致大量的超時和鎖爭用的問題。實際應用中也很少用到這個隔離級別，只有在非常需要確保資料的一致性而且可以接受沒有併發的情況下，才考慮採用該級別。

- 死鎖
死鎖是指兩個或者多個事務在同一資源上相互佔用，並請求鎖定對方佔用的資源，從而導致惡性迴圈的現象。當多個事務試圖以不同的順序鎖定資源時，就可能會產生死鎖。多個事務同時鎖定同一個資源時，也會產生死鎖。

為了解決這種問題，資料庫系統實現了各種死鎖檢測和死鎖超時機制。越複雜的系統，比如InnoDB儲存引擎，越能檢測到死鎖的迴圈依賴，並立即返回一個錯誤。
還有一種解決方式，就是當查詢的時間達到鎖等待超時的設定後放棄鎖請求，這種方式通常來說不太好。
InnoDB目前處理死鎖的方法是，將持有最少行級排他鎖的事務進行回滾。
死鎖發生後，只有部分或者完全回滾其中一個事務，才能打破死鎖。對於事務型的系統，這是無法避免的，所以應用程式在設計時必須考慮如何處理死鎖。大多數情況下只需要重新執行因死鎖回滾的事務即可。

• 事務日誌

事務日誌可以幫助提高事務的效率。使用事務日誌，儲存引擎在修改表的資料時只需要修改其記憶體拷貝，再把該修改行為記錄到持久在硬碟上的事務日誌中，而不用每次都將修改的資料本身持久到磁碟。
事務日誌採用的是追加的方式，因此寫日誌的操作是磁碟上一小塊區域內的順序IO，而不像隨機IO需要在磁碟的多個地方移動磁頭，所以採用事務日誌的方式相對來說要快很多。事務日誌持久化後，記憶體中被修改的資料在後臺可以慢慢地刷回磁碟。目前大多數儲存引擎都是這樣實現的，稱之為預寫式日誌，修改資料需要寫兩次磁碟。

如果資料的修改已經記錄到事務日誌並持久化，但資料本身還沒有寫回磁碟，此時系統崩潰，儲存引擎在重啟時能夠自動恢復這部分修改的資料。

• MySql中的事務
  MySql中提供了兩種事務型的儲存引擎：InnoDB和NDB Cluster。還有一些第三方儲存引擎也支援事務。

自動提交（autocommit）
MySql預設採用自動提交模式。也就是說，如果不是顯式地開始一個事務，則每個查詢都被當作一個事務執行提交操作。在當前連線中，可以通過設定autocmmit變數來啟用或禁用自動提交模式。

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.02 sec)

1或者ON表示啟用，0或者OFF表示禁用。當Autocommit=0時，所有的查詢都是在一個事務中，直到顯式地執行commit提交或者rollback回滾，該事務結束，同時又開始了另一個新事務。

MySql可以通過執行set transaction isolation level命令來設定隔離級別。

在事務中混合使用儲存引擎
MySql伺服器層不管理事務，事務是由下層的儲存引擎實現的。所以在同一個事務中，使用多種儲存引擎是不可靠的。
如果在事務中混合使用了事務型和非事務型的表（比如InnoDB和MyISAM表），在正常提交的情況下不會有什麼問題。

但如果該事務需要回滾，非事務型的表上的變更就無法撤銷，這會導致資料庫處於不一致的狀態。所以為每張表選擇合適的儲存引擎很重要。

在非事務型的表執行事務相關的操作的時候，MySql通常不會發出提醒，也不會報錯。有時候只有回滾的時候才會發出一個警告“某些非事務型的表上的變更不能被回滾”。但大多數情況下，對非事務型表的操作都不會有提示。

隱式和顯式鎖定

InnoDB採用的是兩階段鎖定協議。在事務執行過程中，隨時都可以執行鎖定，鎖只有在執行Commit或者Rollback的時候才會釋放，並且所有的鎖是在同一時刻被釋放。前面描述的鎖定都是隱式鎖定，InnoDB會根據隔離級別在需要的時候自動加鎖。
另外，InnoDB也支援通過特定的語句進行顯式鎖定，這些語句不屬於SQL規範：
select .. lock in share mode
select ..for update

MySql也支援lock tables 和 unlock tables語句，這是在伺服器層實現的，和儲存引擎無關。它們有自己的用途，但並不能替代事務處理。如果應用需要用到事務，還是應該選擇事務型儲存引擎。

4、多版本併發控制

MySql的大多數事務型儲存引擎實現的都不是簡單的行級鎖。基於提升併發效能的考慮，它們一般都同時實現了多版本併發控制（MVCC）。
可以認為MVCC是行級鎖的一個變種，但是它在很多情況下避免了加鎖操作，因此開銷更低。雖然實現機制有所不同，但大都實現了非阻塞的讀操作，寫操作也只鎖定必要的行。

MVCC的實現，是通過儲存資料在某個時間點的快照來實現的。也就是說，不管需要執行多長時間，每個事務看到的資料都是一致的。根據事務開始的時間不同，每個事務對同一張表，同一時刻看到的資料可能是不一樣的。

下面通過InnoDB的簡化版行為來說明MVCC是如何工作的。
InnoDB的MVCC是通過在每行記錄後儲存兩個隱藏的列來實現的。這兩個列，一個儲存了行的建立時間，一個儲存行的過期時間（或刪除時間）。當然儲存的並不是實際的時間值，而是系統版本號。每開啟一個新的事務，系統版本號都會自動遞增。事務開始時刻的系統版本號會作為事務的版本號，用來和查詢到的每行記錄的版本號進行比較。

下面看在reapeatable read隔離級別下，MVCC具體是如何操作的。
select:InnoDB會根據以下兩個條件檢查每行記錄:
1.InnoDB只查詢版本早於當前事務版本的資料行，這樣可以確保事務讀取的行，要麼是在事務開始前已經存在的，要麼是事務自身插入或者修改過的。
2.行的函式版本要麼未定義，要麼大於當前事務版本號。這可以確保事務讀取到的行，在事務開始之前未被刪除。
只有符合上述兩個條件的記錄，才能返回作為查詢結果。

Insert：InnoDB為新插入的每一行記錄儲存當前系統版本號作為行版本號。
Delete： InnoDB為刪除的每一行儲存當前系統版本號作為行刪除標識。
Update： InnoDB為插入一行新記錄，儲存當前系統版本號作為行版本號，同時儲存當前系統版本號到原來的行作為行刪除標識。

儲存這兩個額外系統版本號，使大多數讀操作都可以不用加鎖。這樣設計使得讀資料操作很簡單，效能很好，並且也能保證只會讀取到符合標準的行。不足之處是每行記錄都需要額外的儲存空間，需要做更多的行檢查工作，以及一些額外的維護工作。

MVCC只在Repeatable read和Read committed兩個隔離級別下工作。其它兩個隔離級別和MVCC不相容。因為Read uncommitted總是讀取最新的資料行，而不是符合當前事務版本的資料行。而Serializable則會對所有讀取的行都加鎖。

5、儲存引擎

在檔案系統中，MySql將每個資料庫儲存為資料目錄下的一個子目錄。建立表時，MySql會在資料庫子目錄下建立一個和表同名的.frm檔案儲存表的定義。例如建立一個名為MyTable的表，MySql會在MyTable.frm檔案中儲存該表的定義。因為MySql使用檔案系統的目錄和檔案來儲存資料庫和表的定義，大小寫敏感性和具體的平臺密切相關。在Windows中，大小寫是不敏感的；而在類Unix中則是敏感的。不同的儲存引擎儲存資料和索引的方式是不同的，但表的定義則是在MySql服務層統一處理的。

- InnoDB儲存引擎

InnoDB儲存引擎是MySql預設的事務型引擎，也是最重要、使用最廣泛的儲存引擎。
InnoDB儲存引擎的資料儲存在表空間中，表空間是由InnoDB管理的一個黑盒子，由一系列的資料檔案組成。在MySql4.1以後的版本中，InnoDB儲存引擎可以為每個表的資料和索引存放在單獨的檔案中。
InnoDB儲存引擎採用MVCC來支援高併發，並且實現了四個標準的隔離級別，其預設級別是Repeatable Read(可重複讀)，並且通過間隙鎖策略防止幻讀的出現。間隙鎖使得InnoDB不僅僅鎖定查詢涉及的行，還會對索引中的間隙進行鎖定，以防止幻影行的插入。

InnoDB表是基於聚簇索引建立的。聚簇索引對主鍵查詢有很高的效能，不過它的二級索引中（非主鍵索引）中必須包含主鍵列，所以如果主鍵列很大的話，其它的所有索引都會很大。因此，若表上的索引較多的話，主鍵應當儘可能的小。

InnoDB儲存引擎內部做了很多優化，包括從磁碟讀取資料時採用的可預測性讀，能夠自動在記憶體中建立hash索引以加速讀操作的自適應雜湊索引，以及能夠加速插入操作的插入緩衝區等。

• MyISAM儲存引擎

MySql5.1 版本之前，MyISAM是預設的儲存引擎。MyISAM提供了大量的特性，包括全文索引、壓縮、空間函式等，但MyISAM不支援事務和行級鎖，還有就是崩潰後無法安全恢復。

MyISAM會將表儲存在兩個檔案中：資料檔案和索引檔案，分別以.MYD和.MYI為副檔名。MyISAM表可以包含動態或者靜態行。MyISAM會根據表的定義來決定採用何種行格式。MyISAM表可以儲存的行記錄數，一般受限於可用的磁碟空間或者作業系統中單個檔案的最大尺寸。

加鎖與併發：MyISAM對整張表加鎖，而不是針對行。
修復：通過check table mytable檢查表的錯誤，如果有錯誤可以通過執行repair table mytable進行修復。另外，如果MySql伺服器已經關閉，也可以通過myisamchk命令列工具進行檢查和修復操作。
索引特性：對於MyISAM表，即使是Blob和Text等長欄位，也可以基於其前500個字元建立索引。MyISAM也支援全文索引，這是一種基於分詞建立的索引，可以支援複雜的查詢。
延時更新索引鍵:建立MyISAM表的時候，如果指定了Delay_key_write選項，在每次修改執行完成時，不會立刻將修改的索引資料寫入磁碟，而是會寫到記憶體中的鍵緩衝區，只有在清理鍵緩衝區或者關閉表的時候才會將對應的索引塊寫入到磁碟。
MyISAM壓縮表：如果表在建立並匯入資料以後，不會再進行修改操作，那麼這樣的表適合採用MyISAM壓縮表。可以使用myisampack對MyISAM表進行壓縮。壓縮表是不能進行修改的。壓縮包可以極大地減少磁碟空間佔用，因此也可以減少磁碟IO，從而提升查詢效能。壓縮表也支援索引，但索引也是隻讀的。