MySQL 架構與歷史

1.0 前言

本篇是村民新坑的開始，村民最近在看《 高性能 MySQL 》這本書，村民在看的是第三版，僅涵蓋 MySQL 5.5，雖然最新的 MySQL 已經(jīng)是 8.0 版本，但后者肯定是在前者的基礎上，因此學習價值還是很大的。這系列村民會基本以一章節(jié)一篇的形式記錄村民對書中內容的摘抄整理及筆記，沒什么新意，僅僅算是一種自娛自樂的分享，對這本書感興趣的同學當然也可以買來看看。

本章概要地描述了 MySQL 的服務器架構、各種存儲引擎之間的主要區(qū)別，以及這些區(qū)別的重要性。另外也會回顧一下 MySQL 的歷史背景和基準測試，并試圖通過簡化細節(jié)和演示案例來討論 MySQL 的原理。

1.1 MySQL 邏輯架構

MySQL 邏輯架構圖能幫助我們清晰 MySQL 各組件之間如何協(xié)同工作，也會有助于我們深入理解 MySQL 服務器。

最上層的服務并不是 MySQL 所獨有的，大多數(shù)基于網(wǎng)絡的客戶端 / 服務端的工具或者服務都有類似的架構。比如連接處理、授權認證、安全等等。

第二層架構涵蓋了大多數(shù) MySQL 的核心服務功能，包括查詢解析、分析、優(yōu)化、緩存以及所有的內置函數(shù)（日期、時間、數(shù)學和加密函數(shù)等），所有跨存儲引擎的功能都在這一層實現(xiàn)：存儲過程、觸發(fā)器、視圖等。

第三層包含了存儲引擎。存儲引擎負責 MySQL 中數(shù)據(jù)的存儲和提取。和 GNU/Linux 下的各種文件系統(tǒng)一樣，每個存儲引擎都有它的優(yōu)勢和劣勢。服務器通過 API 與存儲引擎進行通信。這些接口屏蔽了不同存儲引擎之間的差異，使得這些差異對上層的查詢過程透明。存儲引擎 API 包含幾十個底層函數(shù)，用于執(zhí)行諸如 “開始一個事務” 或者 “根據(jù)主鍵提取一條記錄” 等操作，但存儲引擎不會去解析 SQL^注1，不同存儲引擎之間也不會進行通信，而只是簡單地響應上層服務器的請求。

★

注1：InnoDB 存儲引擎是一個例外，它會解析外鍵定義，因為 MySQL 服務器本身沒有實現(xiàn)該功能。

”

1.1.1 連接管理與安全性

每個客戶端連接都會在服務器進程中擁有一個線程，這個連接的查詢只會在這個單獨的線程中進行，該線程只能輪流在某個 CPU 核心或者 CPU 中運行。服務器會負責緩存線程，因此不需要為每一個新建的連接創(chuàng)建或者銷毀線程。

當客戶端連接到 MySQL 服務器時，服務器需要對其進行認證。認證基于用戶名、原始主機信息和密碼。一旦客戶端連接成功，服務器會繼續(xù)驗證該客戶端是否具有執(zhí)行某個特定查詢的權限。

1.1.2 優(yōu)化與執(zhí)行

MySQL 會解析查詢，并創(chuàng)建內部數(shù)據(jù)結構（解析樹），然后對其進行各種優(yōu)化，包括重寫查詢、決定表的讀取順序，以及選擇合適的索引等。用戶可以通過特殊的關鍵字提示 ( hint ) 優(yōu)化器，影響它的決策過程。也可以請求優(yōu)化器解釋 ( explain ) 優(yōu)化過程的各個因素，使用戶可以知道服務器時如何進行優(yōu)化決策的，并提供一個參考基準，便于用戶重構查詢和 schema（ 模式 ）、修改相關配置，使用戶盡可能高效運行。

當我們使用 Navicat 工具時，在查詢功能下可以點擊 “ 解釋 ” 按鈕或者在 SQL 語句前加上 EXPLAIN 關鍵字請求優(yōu)化器解釋優(yōu)化過程。

優(yōu)化器并不關心表使用的是什么存儲引擎，但存儲引擎對于優(yōu)化查詢是有影響的。優(yōu)化器會請求存儲引擎提供容量或某個具體操作的開銷信息，以及表數(shù)據(jù)的統(tǒng)計信息等。例如，某些存儲引擎的某種索引，可能對一些特定的查詢有優(yōu)化。

對于 SELECT 語句，在解析查詢之前，服務器會先檢查查詢緩存 ( Query Cache )，如果能夠在其中找到對應的查詢，服務器就不必再執(zhí)行查詢解析、優(yōu)化和執(zhí)行的整個過程，而是直接返回查詢緩存中的結果集。

1.2 并發(fā)控制

無論何時，只要有多個進程需要在同一時刻修改同一份數(shù)據(jù)，都會產(chǎn)生并發(fā)控制的問題。本節(jié)的目的是討論 MySQL 在兩個層面的并發(fā)控制：服務器層與存儲引擎層。

1.2.1 讀寫鎖

并發(fā)控制在處理并發(fā)讀或者寫時，可以通過實現(xiàn)一個由兩種類型的鎖組成的鎖系統(tǒng)來解決問題。這兩種類型的鎖通常被稱為共享 ( shared lock ) 和排他鎖 ( exclusive lock )，也叫讀鎖 ( read lock ) 和寫鎖 ( write lock )。

鎖的概念：讀鎖是共享的，或者說是相互不阻塞的。多個客戶在同一時刻可以同時讀取同一個資源，而互不干擾。寫鎖是排他的，也就是說一個寫鎖會阻塞其他的寫鎖和讀鎖，這樣才能確保在給定的時間里只有一個用戶能執(zhí)行寫入，并防止其他用戶讀取正在寫入的同一資源。

1.2.2 鎖粒度

一種提高共享資源并發(fā)性的方式就是讓鎖定對象更有選擇性。在給定的資源上，鎖定的數(shù)據(jù)量越少，則系統(tǒng)的并發(fā)程度越高，只要相互之間不發(fā)生沖突即可。隨之而來的問題是加鎖也需要消耗資源，因此需要通過鎖策略在鎖的開銷和數(shù)據(jù)的安全性之間尋求平衡。最重要的兩種鎖策略就是表鎖和行級鎖。

表鎖 ( table lock ) 是 MySQL 中最基本的鎖策略，并且是開銷最小的策略。表鎖會鎖定整張表，當用戶對表進行寫操作前，需要先獲得寫鎖，這會阻塞其他用戶對該表的所有讀寫操作。只有沒有寫鎖時，其他讀取的用戶才能獲得讀鎖，讀鎖之間是不相互阻塞的。

行級鎖 ( row lock ) 可以最大程度地支持并發(fā)處理，同時也帶來了最大的鎖開銷。行級鎖只在存儲引擎層實現(xiàn)，而 MySQL 服務器層沒有實現(xiàn)。

1.3 事務

事務就是一組原子性的 SQL 查詢，或者說一個獨立的工作單元。如果數(shù)據(jù)引擎能夠成功地對數(shù)據(jù)庫應用該組查詢的全部語句，那么就執(zhí)行該組查詢。如果其中有任何一條語句因為崩潰或其他原因無法執(zhí)行，那么所有的語句都不會執(zhí)行。也就是說，事務內的語句，要么全部執(zhí)行成功，要么全部執(zhí)行失敗。

要了解事務，首先要知道事務的 ACID 概念。ACID 表示原子性 ( atomicity )、一致性 ( consistency )、隔離性 ( isolation ) 和持久性 (durability )。

• 原子性：一個事務必須被視為一個不可分割的最小工作單位。
• 一致性：數(shù)據(jù)庫總是從一個一致性的狀態(tài)轉換到另一個一致性的狀態(tài)。如果事務最終沒有提交，那么事務中所做的修改也不會保存到數(shù)據(jù)庫中。
• 隔離性：通常來說，一個事務所做的修改在最終提交之前，對其他事務是不可見的。
• 持久性：一旦事務提交，則其所做的修改就會永久保存到數(shù)據(jù)庫中。此時即使系統(tǒng)崩潰，修改的數(shù)據(jù)也不會丟失。

1.3.1 隔離級別

每種存儲引擎實現(xiàn)的隔離級別不盡相同。在 SQL 標準中定義了四種隔離級別：

• READ UNCOMMITTED（未提交讀）：在 READ UNCOMMITTED 級別，事務中的修改，即使沒有提交，對其他事務也都是可見的。事務可以讀取未提交的數(shù)據(jù)，這也被稱為臟讀 ( Dirty Read )。這個級別在實際應用中一般很少使用。
• READ COMMITTED（提交讀）：在 READ COMMITTED 級別中，一個事務從開始直到提交之前，所做的任何修改都對其他事務不可見的。這個級別有時候也叫做不可重復讀 ( nonrepeatable read )，因為兩次執(zhí)行同樣的查詢，可能會得到不一樣的結果。大多數(shù)數(shù)據(jù)庫系統(tǒng)的默認隔離級別都是 READ COMMITTED，但 MySQL 不是。
• REAPEATABLE READ（可重復讀）：REAPEATABLE READ 解決了臟讀的問題，該級別保證了在同一個事務中多次讀取同樣記錄的結果是一致的。但是理論上，可重復讀級別還是無法解決另一個問題——幻讀 ( Phantom Read )。所謂幻讀，指的是當某個事務在讀取某個范圍內的記錄時，另外一個事務又在該范圍內插入了新的記錄，當之前的事務再次讀取該范圍的記錄時，會產(chǎn)生幻行 ( Phantom Row )?？芍貜妥x是 MySQL 的默認事務隔離級別。
• SERIALIZABLE（可串行化）：SERIALIZABLE 是最高的隔離級別。它通過強制事務串行執(zhí)行，避免了幻讀的問題。簡單來說，SERIALIZABLE 會在讀取的每一行數(shù)據(jù)上都加鎖，所以可能導致大量的超時和鎖爭用的問題。實際應用中也很少用到這個隔離級別。

1.3.2 死鎖

死鎖是指兩個或者多個事務在同一資源上相互占用，并請求鎖定對方占用的資源，從而導致惡性循環(huán)的現(xiàn)象。當多個事務試圖以不同的順序鎖定資源時，就可能會產(chǎn)生死鎖。多個事務同時鎖定同一資源時，也會產(chǎn)生死鎖。

為了解決這種問題，數(shù)據(jù)庫系統(tǒng)實現(xiàn)了各種死鎖檢測和死鎖超時機制。InnoDB 目前處理死鎖的方法是將持有最少行級排它鎖的事務進行回滾。

鎖的行為和順序是和存儲引擎相關的。以同樣的順序執(zhí)行語句時，有些存儲引擎會產(chǎn)生死鎖，有些則不會。死鎖的產(chǎn)生有雙重原因：有些是因為真正的數(shù)據(jù)沖突，這種情況通常很難避免，但有些則完全是由于存儲引擎的實現(xiàn)方式導致的。

1.3.3 事務日志

事務日志可以幫助提高事務的效率。事務日志采用的是追加的方式。

1.3.4 MySQL 中的事務

自動提交 ( AUTOCOMMIT )

MySQL 默認采用自動提交 ( AUTOCOMMIT ) 模式。也就是說，如果不是顯式地開始一個事務，則每個查詢都被當做一個事務執(zhí)行提交操作?？梢酝ㄟ^下述命令設置 AUTOCOMMIT 變量來啟用或者禁用自動提交模式：

mysql> SET AUTOCOMMIT = 1;
mysql> SET AUTOCOMMIT = 0;

1 或者 ON 表示啟用，0 或者 OFF 表示禁用。MySQL 可以通過執(zhí)行 SET TRANSACTION ISOLATION LEVEL 命令來設置隔離級別。新的隔離級別會在下一個事務開始的時候生效。

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

MySQL 能夠識別所有的 4 個 ANSI 隔離級別，InnoDB 引擎也支持所有的隔離級別。

在事務中混合使用存儲引擎

MySQL 服務器層不管理事務，事務是由下層的存儲引擎實現(xiàn)的。所以在同一事務中，使用多種存儲引擎是不可靠的。

隱式和顯式鎖定

InnoDB 采用的是兩階段鎖定協(xié)議 ( two-phase locking protocol ) 。在事務執(zhí)行過程中，隨時都可以執(zhí)行鎖定，鎖只有在執(zhí)行 COMMIT 或者 ROLLBACK 的時候才會釋放，并且所有的鎖都在同一時刻被釋放。前面描述的鎖定都是隱式鎖定，InnoDB 會根據(jù)隔離級別在需要的時候自動加鎖。另外，InnoDB 也支持通過特定的語句進行顯示鎖定。

1.4 多版本并發(fā)控制

MySQL 的大多數(shù)事務型存儲引擎實現(xiàn)的都不是簡單的行級鎖，基于提升并發(fā)性能的考慮，它們一般都實現(xiàn)了多版本并發(fā)控制 ( MVCC )?？梢哉J為 MVCC 是行級鎖的一個變種，但是它在很多情況下避免了加鎖操作，因此開銷更低。MVCC 是通過保存數(shù)據(jù)在某個時間點的快照來實現(xiàn)的，也就是說，不管需要執(zhí)行多長時間，每個事務看到的數(shù)據(jù)都是一致的。

InnoDB 的 MVCC 是通過在每行記錄后面保存兩個隱藏的列來實現(xiàn)的，一個列保存了行的創(chuàng)建時間，另一個列保存行的過期時間或刪除時間。當然存儲的并不是實際的時間值，而是系統(tǒng)版本號 ( system version number )。每開始一個新的事務，系統(tǒng)版本號都會自動遞增。事務開始時刻的系統(tǒng)版本號會作為事務的版本號，用來和查詢到的每行記錄的版本號進行比較。保存這兩個額外系統(tǒng)版本號，使大多數(shù)讀操作都可以不用加鎖。這樣設計使得讀數(shù)據(jù)操作很簡單，性能很好，并且也能保證只會讀取到符合標準的行。不足之處是每行記錄都需要額外的存儲空間，需要做更多的行檢查工作，以及一些額外的維護工作。

MVCC 只在 REPEATABLE 和 READ READ COMMITTED 兩個隔離級別下工作。

1.5 MySQL 的存儲引擎

1.5.1 InnoDB 存儲引擎

InnoDB 是 MySQL 的默認事務型引擎，也是最重要、使用最廣泛的存儲引擎。它被設計用來處理大量的短期 ( short-lived ) 事務，短期事務大部分情況是正常提交的，很少會被回滾。InnoDB 的性能和自動崩潰恢復特性，使得它在非事務型存儲的需求中也很流行。

InnoDB 概覽

InnoDB 采用 MVCC 來支持高并發(fā)，并且實現(xiàn)了四個標準的隔離級別。其默認隔離級別是 REPEATABLE READ（可重復讀），并且通過間隙鎖 ( next-key locking ) 策略防止幻讀的出現(xiàn)。間隙鎖使得 InnoDB 不僅僅鎖定查詢涉及的行，還會對索引中的間隙進行鎖定，以防止幻影行的插入。

InnoDB 的表是基于聚簇索引建立的。聚簇索引對主鍵查詢有很高的性能，不過它的二級索引 ( secondary index，非主鍵索引 ) 中必須包含主鍵列，所以如果主鍵列很大的話，其他的索引都會很大。因此，若表上的索引較多的話，主鍵應當盡可能的小。InnoDB 的存儲格式是平臺獨立的，也就是說可以將數(shù)據(jù)和索引文件復制出來轉移到另一個平臺。

1.5.2 MyISAM 存儲引擎

MyISAM 提供了大量的特性，包括全文索引、壓縮、空間函數(shù) ( GIS ) 等，但 MyISAM 不支持事務和行級鎖，而且有一個毫無疑問的缺陷就是崩潰后無法安全恢復。

存儲

MyISAM 會將表存儲在兩個文件中：數(shù)據(jù)文件和索引文件，分別以 .MYD 和 .MYI 為擴展名。MyISAM 表可以包含動態(tài)或者靜態(tài)（長度固定）行。MySQL 會根據(jù)表的定義來決定采用何種格式。MyISAM 表可以存儲的行記錄數(shù)，一般受限于可用的磁盤空間，或者操作系統(tǒng)中單個文件的最大尺寸。

MyISAM 特性

• 加鎖與并發(fā)：MyISAM 對整張表加鎖，而不是針對行。讀取時會對需要讀到的所有表加共享鎖，寫入時則對表加排他鎖，但是在表有讀取查詢的同時，也可以往表中插入新的記錄（這也被稱為并發(fā)插入，CONCURRENT INSERT ）。
• 修復：對于MyISAM 表，MySQL 可以手工或者自動執(zhí)行檢查和修復操作，但這里說的修復和事務恢復以及崩潰恢復時不同的概念。執(zhí)行表的修復可能導致一些數(shù)據(jù)丟失，而且修復操作是非常慢的?？梢酝ㄟ^ CHECK TABLE mytable 檢查表的錯誤，如果有錯誤可以通過執(zhí)行 REPAIR TABLE mytable 進行修復。另外，如果 MySQL 服務器已經(jīng)關閉，也可以通過 myisamchk 命令行工具進行檢查和修復操作。
• 索引特性：對于 MyISAM 表，即使是 BLOB 和 TEXT 等長字段，也可以基于其前 500 個字符創(chuàng)建索引。MyISAM 也支持全文索引，這是一種基于分詞創(chuàng)建的索引，可以支持復雜的查詢。
• 延遲更新索引鍵 ( Delayed Key Write )：創(chuàng)建 MyISAM 表的時候，如果指定了 DELAY_kEY_wRITE 選項，在每次修改執(zhí)行完成時，不會立刻將修改的索引數(shù)據(jù)寫入磁盤，而是會寫到內存中的鍵緩沖區(qū) ( in-memory key buffer )，只有在清理鍵緩沖區(qū)或者關閉表的時候才會將對應的索引塊寫入到磁盤。這種方式可以極大地提升寫入性能，但是在數(shù)據(jù)庫或者主機崩潰時會造成索引損壞，需要執(zhí)行修復操作。延遲更新索引鍵的特性，可以在全局設置，也可以為單個表設置。

MyISAM 壓縮表

如果表在創(chuàng)建并導入數(shù)據(jù)以后，不會在進行修改操作，那么這樣的表或許適合采用 MyISAM 壓縮表?？梢允褂?myisampack 對 MyISAM 表進行壓縮（也叫打包 pack ）。壓縮表是不能修改的（除非先將表解除壓縮，修改數(shù)據(jù)，然后再次壓縮）。壓縮表可以極大地減少磁盤空間占用，因此也可以減少磁盤 I/O，從而提升查詢性能。壓縮表也支持索引，但索引也是只讀的。

1.5.5 選擇合適的引擎

大部分情況下，InnoDB 都是正確的選擇，這也是 InnoDB 被選擇作為 MySQL 默認存儲引擎的原因。簡單來說，除非需要用到某些 InnoDB 不具備的特性，并且沒有其他辦法可以替代，否則都應該優(yōu)先選擇 InnoDB 引擎。當然，如果不需要用到 InnoDB 的特性，同時其他引擎的特性能夠更好地滿足需求，也可以考慮一下其他存儲引擎。

★

除非萬不得已，否則不建議混合使用多種存儲引擎，否則可能會帶來一系列復雜的問題，以及一些潛在的 bug 和邊界問題?；旌洗鎯σ恢滦詡浞莺头掌鲄?shù)配置都帶來了一些困難。

”

如果應用需要不同的存儲引擎，請先考慮一下幾個因素：

• 事務：如果應用需要事務支持，那么 InnoDB 是目前最穩(wěn)定并且經(jīng)過驗證的選擇。如果不需要事務，并且主要是 SELECT 和 INSERT 操作，那么 MyISAM 是不錯的選擇，一般日志型的應用比較符合這一特性。
• 備份：備份的需求也會影響存儲引擎的選擇。如果可以定期地關閉服務器來執(zhí)行備份的，那么備份的因素可以忽略。反之，如果需要在線熱備份，那么選擇 InnoDB 就是基本的要求。
• 崩潰恢復：數(shù)據(jù)量比較大的時候，系統(tǒng)崩潰后如何快速地恢復是一個需要考慮的問題。相對而言，MyISAM 崩潰后發(fā)生損壞的概率比 InnoDB 要高很多，而且恢復速度也要慢。因此，即使不需要事務支持，很多人也選擇 InnoDB 引擎，這是一個非常重要的因素。
• 特有的特性：有些應用可能依賴一些存儲引擎所獨有的特性或者優(yōu)化。比如很多應用依賴聚簇索引的優(yōu)化，另外，MySQL 中也只有 MyISAM 支持地理空間搜索。如果一個存儲引擎擁有一些關鍵的特性，同時又缺乏一些必要的特性，那么有時候不得不做折中的考慮，或者在架構設計上做一些取舍。某些存儲引擎無法直接支持的特性，有時候通過變通也可以滿足需求。

1.5.6 轉換表的引擎

如果轉換表的存儲引擎，將會失去和原引擎相關的所有特性。有很多種方法可以將表的存儲引擎轉換成另外一種引擎。每種方法都有其優(yōu)點和缺點，這里介紹三種方法。

ALTER TABLE

將表從一個引擎修改為另一個引擎最簡單的方法是使用 ALTER TABLE 語句。下面的語句將 mytable 的引擎修改為 InnoDB：

mysql> ALTER TABLE mytable ENGINE = InnoDB;

上述語法可以適用任何存儲引擎，但存在需要執(zhí)行很長時間的問題。MySQL 會按行將數(shù)據(jù)從原表復制到一張新的表中，在復制期間可能會消耗系統(tǒng)所有的 I/O 能力，同事原表上會加上讀鎖。

導出與導入

為了更好地控制轉換的過程，可以使用 mysqldump 工具將數(shù)據(jù)導出到文件，然后修改文件中 CREATE TABLE 語句的存儲引擎選項，注意同時修改表名，因為同一數(shù)據(jù)庫中不能存在相同的表名，即使它們使用的是不同的存儲引擎。同時要注意 mysqldump 默認會自動在 CREAT TABLE 語句前加上 DROP TABLE 語句，不注意這一點可能會導致數(shù)據(jù)丟失。

創(chuàng)建與查詢

第三種轉換的技術綜合了第一種方法的高效和第二種方法的安全，不需要導出整個表的數(shù)據(jù)，而是先創(chuàng)建一個新的存儲引擎的表，然后利用 INSERT ··· SELECT 語法來導數(shù)據(jù)：

mysql> CREATE TABLE innodb_table LIKE myisam_table;
mysql> ALTER TABLE innodb_table ENGINE = InnoDB;
mysql> INSERT INTO innodb_table SELECT * FROM myisam_table;

數(shù)據(jù)量不大的話，這樣做工作得很好。如果數(shù)據(jù)量很大，則可以考慮做分批處理，針對每一段數(shù)據(jù)執(zhí)行事務提交操作，以避免大事務產(chǎn)生過多的 undo。假設有主鍵字段 id，重復運行一下語句（最小值 x 和最大值 y 進行相應的替換）將數(shù)據(jù)導入到新表：

mysql> START TRANSACTION;
mysql> INSERT INTO innodb_tabel SELECT * FROM myisam_table WHERE id BETWEEN x AND y;
mysql> COMMIT;

這樣操作完成以后，新表是原表的一個全量復制，原表還在，如果需要可以刪除原表。如果有必要，可以在執(zhí)行的過程中對原表加鎖，以確保新表和原表的數(shù)據(jù)一致。