本文內(nèi)容來自《高性能 MySQL》(第三版)

@MySQL 服務(wù)器邏輯架構(gòu)圖 | center

1. 最上層的服務(wù)并不是 MySQL 所獨有的消约，大多數(shù)基于網(wǎng)絡(luò)的客戶端/服務(wù)器的工具或者服務(wù)都有類似的架構(gòu)或粮。比如連接的處理氯材、授權(quán)認證、安全等等袋毙。
2. 第二層架構(gòu)是 MySQL 比較有意思的部分听盖。大所述 MySQL 的核心服務(wù)都在這一層皆看，包含查詢解析背零、分析徙瓶、優(yōu)化、緩存以及所有的內(nèi)置函數(shù)（例如：日期禾乘、時間始藕、數(shù)學(xué)和加密函數(shù)）氮趋，所有跨存儲引擎的功能都在這一層實現(xiàn)：存儲過程伍派、觸發(fā)器、視圖等剩胁。
3. 第三層包含了存儲引擎诉植。存儲引擎負責(zé) MySQL 中數(shù)據(jù)的存儲和提取。和 Linux 下的文件系統(tǒng)一樣昵观，每個存儲引擎都有它的優(yōu)勢和劣勢晾腔。服務(wù)器通過 API 與存儲引擎進行通信。這些接口屏蔽了不同存儲引擎之間的差異啊犬，使得這些差異對上層的查詢過程透明。存儲引擎 API 包含幾十個底層函數(shù)觉至，用于執(zhí)行不同的操作剔应。但是 存儲引擎不會去解析一個 SQL 查詢^[1]，不同存儲引擎之間也不會相互通信语御，而只是簡單地響應(yīng)上層服務(wù)器峻贮。

連接管理與安全性

每個客戶端連接都會在服務(wù)器進程中擁有一個線程，這個連接的查詢只會在這個單獨的線程中執(zhí)行应闯，該線程只能輪流在某個 CPU 核心或者 CPU 中運行纤控。服務(wù)器會負責(zé)緩存線程，因此 不需要為每個新建的連接創(chuàng)建或者銷毀線程^[2]孽锥。

當(dāng)客戶端（應(yīng)用）連接到 MySQL 服務(wù)器時嚼黔，服務(wù)器需要對其進行認證。認證基于用戶名惜辑，原始主機信息和密碼唬涧。如果使用了安全套接字（SSL）的方式連接竣贪，還可以使用 X.509 證書認證划滋。一旦客戶端連接成功，服務(wù)器會繼續(xù)驗證該客戶端是否具有執(zhí)行某個特定查詢的權(quán)限粉怕。

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

MySQL 會解析查詢抵卫，會創(chuàng)建內(nèi)部數(shù)據(jù)結(jié)構(gòu)（解析樹）狮荔，然后對其進行各種優(yōu)化胎撇，包括重寫查詢，決定表的讀取順序殖氏，以及選擇合適的索引等晚树。用戶可以通過特殊的關(guān)鍵字提示（hint）優(yōu)化器，影響它的決策過程雅采。也可以請求優(yōu)化器解釋（explain）優(yōu)化過程的各個因素爵憎，使用戶服務(wù)器時如何進行優(yōu)化決策，并提供了一個參考基準婚瓜，便于用戶重構(gòu)和查詢和 schema宝鼓、修改相關(guān)配置，使盡可能高效運行巴刻。

優(yōu)化器并不關(guān)心表使用什么存儲引擎愚铡，但是存儲引擎對于優(yōu)化查詢是有影響的。優(yōu)化器會請求存儲引擎提供容量或某個具體操作得開銷信息胡陪，以及表數(shù)據(jù)的統(tǒng)計信息沥寥，以及表數(shù)據(jù)的統(tǒng)計信息等。

對于 SELECT 語句柠座，在解析查詢之前营曼，服務(wù)器會先檢查查詢緩存（Query Cache），如果能夠在其中找到對應(yīng)的查詢愚隧，服務(wù)器就不必再執(zhí)行查詢解析蒂阱，優(yōu)化和執(zhí)行的整個過程，而是直接返回緩存中的結(jié)果集狂塘。

并發(fā)控制

無論何時录煤，只要有多個查詢需要在同一個時刻修改數(shù)據(jù)，都會產(chǎn)生并發(fā)控制的問題荞胡。MySQL 提供了兩個層面的并發(fā)的控制：服務(wù)器層和存儲引擎層層妈踊。

以 Unix 系統(tǒng)的 email box 為例，典型的 mbox 文件格式是非常簡單的泪漂。一個 mbox 郵箱中所有的郵件都 串行^[3] 在一起廊营，彼此首尾相連。這種格式對于讀取和分析郵件信息非常友好萝勤，同時投遞郵件也很容易露筒，只要在文件尾部附加新的郵件內(nèi)容即可。

但是如果兩個進程在同一時刻對同一個郵箱投遞郵件敌卓，會發(fā)生什么情況慎式？顯然，郵箱的數(shù)據(jù)會被破壞，兩封郵件的內(nèi)容會交叉地附加在文件的尾部瘪吏。設(shè)計良好的郵箱投遞系統(tǒng)會通過鎖（lock）來防止數(shù)據(jù)損壞癣防。如果客戶試圖投遞郵件，而郵件已經(jīng)被其他客戶鎖住掌眠，那就必須等待蕾盯，直到鎖釋放才能進行投遞。這種鎖的方案雖然在實際應(yīng)用中能良好的工作蓝丙，但是并不支持并發(fā)處理刑枝，因為在任意一個時刻只有一個進程可以修改郵箱的數(shù)據(jù)，這在大容量的郵箱系統(tǒng)中是個問題迅腔。

1. 讀寫鎖

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

• 讀鎖是共享的，他們之間相互不阻塞像云。也就是說多個客戶端連接可以在同一時間訪問同一個資源锌雀，他們之間相互不影響。
• 寫鎖則是排他的迅诬，基于安全策略腋逆，一個寫鎖會阻塞其他的讀鎖或?qū)戞i。只有這樣才能確保在給定的時間內(nèi)只有一個用戶執(zhí)行寫入侈贷，并防止其他用戶在同一時間訪問正在寫入的資源惩歉。

2. 鎖粒度

一種提高共享資源并發(fā)性的策略師讓鎖定的對象更加具有選擇性。盡量只鎖定需要修改的部分俏蛮。更理想的方式是：只對修改的數(shù)據(jù)片進行精確的鎖定撑蚌。在給定資源的條件下，鎖定的數(shù)量越少搏屑，則系統(tǒng)的并發(fā)程度就越高争涌。只要相互之間不發(fā)生沖突就可以。

問題是：加鎖也需要消耗資源辣恋。所得各種操作亮垫，包括獲取鎖，檢查鎖是否已經(jīng)解除伟骨，釋放鎖等都會增加系統(tǒng)開銷饮潦。如果系統(tǒng)在管理鎖上花費大量的資源，則在存取數(shù)據(jù)上的性能就會受到影響

鎖策略就是在所鎖的開銷和數(shù)據(jù)的安全性之間尋求平衡携狭，這種平衡也會影響性能害晦。大多數(shù)數(shù)據(jù)庫都會在表上使用一些復(fù)雜的方式來實現(xiàn)級鎖（row-level lock）,以便在鎖比較多的情況下盡量提供更好的服務(wù)。

MySQL 提供了多種選擇，其在存儲引擎中可以實現(xiàn)自己的鎖策略和鎖粒度壹瘟。

• 表鎖（table lock）：表鎖是 MySQL 中最基本的鎖策略鲫剿，并且是開銷最小的策略。它會鎖定整張表稻轨，一個用戶在對表進行操作得時候會先獲得鎖灵莲，這個鎖會阻塞其他用戶對該表的操作。只有在沒有寫鎖的條件下殴俱，其他用戶才能獲得讀鎖政冻。讀鎖之間是不相互阻塞的。另外：寫鎖比讀鎖擁有更高等級的優(yōu)先級线欲，因此一個寫鎖請求可能會被插入到讀鎖隊列的前面明场，反之則不行。
• 行級鎖（row lock）：行級鎖在最大程度的支持并發(fā)請求的同時帶來了最大的鎖開銷李丰。行級索只有的存儲引擎層面實現(xiàn)苦锨，而 MySQL 服務(wù)器層沒有實現(xiàn)。
• 盡管 MySQL 的存儲引擎自己實現(xiàn)了鎖策略趴泌，但是在進行一些操作得時候舟舒，MySQL 服務(wù)器會忽略存儲引擎的鎖策略，而使用自身各種有效的表鎖來實現(xiàn)目的嗜憔。例如 ALTER TABLE 之類的語句秃励。

事務(wù)

事務(wù)就是一組原子性的 SQL 查詢或者說是一個獨立的工作單元。如果存儲引擎能夠成功地對數(shù)據(jù)庫應(yīng)用所有的全部語句吉捶，那么就執(zhí)行該組查詢夺鲜。如果其中有一條語句因為崩潰或者其他原因無法執(zhí)行，那么所有的語句都不會執(zhí)行呐舔。也就是說在一個事務(wù)內(nèi)部的所有操作語句谣旁，要么全部執(zhí)行成功要么全部執(zhí)行失敗。

ACID 表示原子性（atomicity）滋早、一致性（consistency）榄审、隔離性（isolation）和持久性（durability）。一個運行良好的事務(wù)處理系統(tǒng)必須具有這些標準特征杆麸。

• 原子性（atomicity）：一個事務(wù)必須被視為一個不可分割的最小工作單元搁进，整個事務(wù)中的所有操作要么全部提交成功，要么全部失敗回滾昔头。對于一個事務(wù)來講饼问，不可能只執(zhí)行其中的一部分操作；
• 一致性（consistency）：數(shù)據(jù)庫總是從一個一致性狀態(tài)轉(zhuǎn)到另一個一致性狀態(tài)揭斧。
• 隔離性（isolation）：通常來說莱革，一個事務(wù)修改在最終提交修改之前峻堰，對其他事務(wù)是不可見的。
• 持久性（durability）：一旦事務(wù)提交盅视，則其所做的修改就會永久保存在數(shù)據(jù)庫中捐名。此時即使系統(tǒng)崩潰，修改的數(shù)據(jù)也不會丟失

隔離級別

在 SQL 標準中定義了四種隔離級別闹击，一種級別都規(guī)定了一個事務(wù)所能做的修改镶蹋，哪些在事務(wù)之間是可見的，哪些在事務(wù)之間是不可見的赏半。較低級別的隔離通產(chǎn)更可以執(zhí)行更高的并發(fā)贺归，系統(tǒng)的開銷也更低。

• READ UNCOMMITTED（未提交讀）：在 READ UNCOMMITTED 中断箫，對于事務(wù)中的修改拂酣，即使沒有提交，對于其他事務(wù)也是可見的仲义。事務(wù)可以讀取無提交的數(shù)據(jù)婶熬，這也被稱為是臟讀（Dirty Read）。這個級別會導(dǎo)致很多其他的問題光坝，從性能上來說，READ UNCOMMITTED 不會比其他的級別好太多甥材，但是卻缺乏其他級別的很多好處盯另，除非真的有非常必要的理由，在實際應(yīng)用中一般很少使用洲赵。

• READ COMMITTED（提交讀）：大多數(shù)數(shù)據(jù)庫默認的隔離級別是 READ COMMITTED鸳惯，但是 MySQL 并不是。其滿足了一個事務(wù)開始時叠萍，只能看到已經(jīng)提交的事務(wù)所做的修改芝发。換句話說：一個事務(wù)從開始直到提交之前，所做的任何修改對于其他事務(wù)都是不可見的苛谷。這個級別有時也被稱為不可重復(fù)讀（nonrepeatable read）辅鲸，因為兩次執(zhí)行同樣的查詢，可能會得到不一樣的結(jié)果腹殿。

• REPEATABLE READ（可重復(fù)度）：其解決了臟讀的問題独悴，該級別保證了在同一個事務(wù)中多次讀取同樣記錄的結(jié)果是一致的。但是理論上來講锣尉，可重復(fù)讀隔離級別還是無法解決另外一個 幻讀^[4] 的問題刻炒。其是 MySQL 默認的事務(wù)隔離級別。

• SERIALIZABLE（可串行化）：SERIALIZABLE 是最高級別的事務(wù)隔離等級自沧，它通過強制事務(wù)串行執(zhí)行坟奥，避免了前面的幻讀問題。簡單來說：SERIALIZABLE 會在讀取的每一行數(shù)據(jù)上面加上鎖，所以可能導(dǎo)致大量的超時和鎖爭用的問題爱谁。實際引用中很少用到這個隔離級別晒喷，只有在非常需要確保數(shù)據(jù)的一致性而且可以接受沒有并發(fā)的情況下，才會考慮該級別管行。

  隔離級別	臟讀可能性	不可重復(fù)讀可能性	幻讀可能性	加鎖讀
  READ UNCOMMMITTED	yes	yes	yes	no
  READ COMMITTED	no	yes	yes	no
  REPEATABLE COMMITTED	no	no	yes	no
  SERIALIZABLE	no	no	no	yes

死鎖

死鎖是指兩個事務(wù)或者多個事務(wù)在同一個資源上相互占用厨埋，并請求鎖定對方占用的資源，從而導(dǎo)致惡性循環(huán)的現(xiàn)象捐顷。當(dāng)多個事務(wù)企圖用不同的順序鎖定資源時就會造成死鎖荡陷。多個事務(wù)同時鎖定同一個資源時也會造成死鎖。

假設(shè)現(xiàn)在有兩個事務(wù)如下：

• 事務(wù) 1:

  START TRANSACTION;
  update tablename set name = 'heroic' where id = 5;
  update tablename set name = 'sophia' where id = 4;
  COMMIT;
  

• 事務(wù) 2:

  START TRANSACATION;
  update tablename set name = 'root' where id = 4;
  update tablename set name = 'staff' where id = 5;
  COMMIT;
  

如果湊巧兩個事務(wù)都執(zhí)行了第一條 update 語句迅涮，更新了一行數(shù)據(jù)废赞，同時也鎖定了該行數(shù)據(jù)。接著每個事務(wù)又都去執(zhí)行第二條 update 語句叮姑，卻發(fā)現(xiàn)資源已經(jīng)被對方鎖定唉地，然后兩個事務(wù)都等待對方釋放鎖，同時又持有對方需啊鎖传透，這樣就會容易陷入死循環(huán)耘沼，造成死鎖。
InnoDB 目前處理死鎖的辦法是將持有最少行級排他鎖的事務(wù)進行回滾朱盐。

事務(wù)日志

事務(wù)日志可以幫助提高事務(wù)的執(zhí)行效率群嗤。使用事務(wù)日志，存儲引擎在修改表的數(shù)據(jù)的時候只需要修改其內(nèi)存拷貝兵琳。再把該修改行為記錄到事務(wù)日志中狂秘，而不用每次都將修改數(shù)據(jù)的操作本身持久化到磁盤。事務(wù)日志采用追加的方式躯肌，因此事務(wù)日志的操作是磁盤上一小塊位置的順序 I/O者春，而不像隨機 I/O 那樣在在磁盤的多個位置移動磁頭。所以使用事務(wù)日志的方式相對而言要快得多清女。事務(wù)日志持久以后钱烟，內(nèi)存中被修改的數(shù)據(jù)可以在后臺慢慢地刷回到磁盤。這種方式通常被稱作預(yù)寫式日志（Write-Ahead Logging）嫡丙。修改數(shù)據(jù)需要寫兩次磁盤忠售。

如果數(shù)據(jù)的修改已經(jīng)寫入到事務(wù)日志并持久化，但數(shù)據(jù)本身還沒有會寫磁盤迄沫，這個時候系統(tǒng)崩潰稻扬，這種情況下存儲引擎能夠在重啟時恢復(fù)這部分數(shù)據(jù)。

MySQL 中的事務(wù)

Mysql 提供兩種事務(wù)型操作引擎羊瘩，INnoDB 和 NDB Cluster泰佳，另外還有一些第三方的存儲引擎支持事務(wù)盼砍，比如 XtraDB 等。

• 自動提交：MySQL 默認采用自動提交模式（AUTOCOMMIT）逝她。也就是說如果不顯式的開始一個事務(wù)浇坐，則每個查詢都會被當(dāng)做是一個事務(wù)并自動提交∏穑可以通過 SET AUTOCOMMIT 來修改設(shè)置啟用或者禁用自動提交模式（0 或 OFF 表示禁用近刘，1 或 ON 表示啟用）。還有一些操作會強制提交當(dāng)前事務(wù)臀晃，比較典型的有在 DDL 中如果導(dǎo)致大量的數(shù)據(jù)改變的操作觉渴，比如 ALTER TABLE 等。
• MySQL 可以通過 SET TRANSACTION LEVEL 的命令來設(shè)置事務(wù)隔離等級徽惋，新的事務(wù)隔離等級會在下次事務(wù)操作的時候生效案淋。 如：mysql> SET SESSION TRANSACTION LEVEL READ COMMITTED。
• 由于 MySQL 服務(wù)器層不管理事務(wù)险绘，事務(wù)是由下層得存儲引擎實現(xiàn)的踢京。所以在同一個事務(wù)中，使用多種存儲引擎是不可靠的宦棺。

InnoDB 存儲引擎概覽

• InnoDB 的數(shù)據(jù)在存儲在表空間（tablespace）中瓣距，表的空間是由 InnoDB 管理的一個黑盒子，有一系列數(shù)據(jù)文件組成代咸。
• InnoDB 采用 MVCC 來支持高并發(fā)蹈丸。并實現(xiàn)了四個標準的隔離等級。其默認級別是 REPEATABLE READ（可重復(fù)讀）侣背，并通過間隙鎖（next-key locking）策略來實現(xiàn)防止幻讀白华。間隙鎖使得 InnoDB 不僅僅鎖定查詢涉及的行慨默，還會對索引中的間隙進行鎖定贩耐。以防幻行的插入。
• InnoDB 是基于聚簇索引的厦取，聚簇索引對于查詢有很高的性能潮太。不過它的二級索引（secondary index，非主鍵索引）中必須包含主鍵列虾攻，所以如果主鍵列很大的話铡买，其他的所有引擎都很大。因此霎箍，若表上的索引比較多的話奇钞，主鍵應(yīng)該盡可能的小。

臟讀：是指一個事務(wù)讀到了另一個事務(wù)中修改過但沒有提交的數(shù)據(jù)漂坏。假如一個事務(wù)失敗回滾景埃，那么它做的修改統(tǒng)統(tǒng)被撤銷媒至，這就使得前面的一個事務(wù)讀到了后面事務(wù)撤銷的垃圾數(shù)據(jù)。
不可重復(fù)度：在一個事務(wù)中谷徙，再次讀取數(shù)據(jù)的結(jié)果和前面一次讀取數(shù)據(jù)的結(jié)果不一樣拒啰。
幻讀：事務(wù)1讀取指定的where子句所返回的一些行。然后完慧，事務(wù)2插入一個新行谋旦，這個新行也滿足事務(wù)1使用的查詢 where 子句。然后事務(wù)1再次使用相同的查詢讀取行屈尼，但是現(xiàn)在它看到了事務(wù)2剛插入的行册着。這個行被稱為幻象，因為對事務(wù)1來說鸿染，這一行的出現(xiàn)是不可思議的指蚜。
幻讀和不可重復(fù)讀的區(qū)別：幻讀的重點在于新增或者刪除，導(dǎo)致兩次讀取的數(shù)據(jù)行數(shù)不一樣涨椒，而不可重復(fù)度的重點在于修改摊鸡，導(dǎo)致兩次查詢得到的結(jié)果不一樣。

1. InnoDB是一個例外蚕冬，他會解析外鍵定義免猾，因為MySQL服務(wù)器本身沒有實現(xiàn)該功能。 ?

2. MySQL5.5或者更新的版本提供了一個API囤热，支持線程池（Thread-Pooling）插件猎提，可以使用少量的線程來服務(wù)大量的連接。 ?

3. 串行通信是指使用一條數(shù)據(jù)線旁蔼，將數(shù)據(jù)按位一位一位地依次傳輸锨苏，每一個數(shù)據(jù)占據(jù)一個固定的時間長度。其只需要少數(shù)的幾根線就可以在系統(tǒng)間交換信息棺聊，特別適合計算機之間或者計算機和外設(shè)之間的遠距離通信伞租。【百度百科】 ?

4. 所謂幻讀，指的是當(dāng)某個事務(wù)在讀取某個范圍內(nèi)記錄的時候限佩，另外一個事務(wù)又在該范圍內(nèi)插入了新的記錄葵诈，當(dāng)之前的事務(wù)再次讀取該范圍的記錄時，會產(chǎn)生幻行祟同。InnoDB和XtraDB通過多版本并發(fā)控制機制解決了幻讀問題作喘。 ?