本文概述了MySQL的服務(wù)器架構(gòu)、各種存儲(chǔ)引擎之間的主要區(qū)別，以及這些區(qū)別的重要性。

[[255510]]

MySQL邏輯架構(gòu)整體分為三層

>最上層并非MySQL所獨(dú)有，主要進(jìn)行如連接處理、授權(quán)認(rèn)證、安全等功能的處理。

>MySQL大多數(shù)核心服務(wù)均在第二層架構(gòu)，包括查詢解析、分析、優(yōu)化、緩存、內(nèi)置函數(shù)(如日期、時(shí)間、數(shù)學(xué)和加密等函數(shù))。所有的跨存儲(chǔ)引擎的功能也在這一層實(shí)現(xiàn)：存儲(chǔ)過(guò)程、觸發(fā)器、視圖等。

>最下層為存儲(chǔ)引擎，負(fù)責(zé)MySQL中的數(shù)據(jù)存儲(chǔ)和提取。每種存儲(chǔ)引擎都有其優(yōu)勢(shì)和劣勢(shì)。服務(wù)器通過(guò)API與存儲(chǔ)引擎進(jìn)行通信，這些API接口屏蔽了不同存儲(chǔ)引擎間的差異。存儲(chǔ)引擎API包含幾十個(gè)底層函數(shù)用于執(zhí)行，但不會(huì)去解析SQL(InnoDB是個(gè)例外，他會(huì)解析外鍵定義，因?yàn)镸ySQL服務(wù)器沒(méi)有實(shí)現(xiàn)該功能);不同引擎只會(huì)簡(jiǎn)單的響應(yīng)上層服務(wù)器的請(qǐng)求，而不會(huì)相互通信。

對(duì)于存儲(chǔ)引擎，本文以及之后的文章只對(duì)MyISAM和InnoDB進(jìn)行探究。

1.連接管理與安全性

對(duì)于每個(gè)客戶端連接，服務(wù)器都會(huì)在進(jìn)程中新建一個(gè)線程處理(如果是線程池的話，則是分配一個(gè)空的線程)，這個(gè)連接的查詢只會(huì)在這個(gè)單獨(dú)的線程中執(zhí)行(每個(gè)線程相互獨(dú)立)，該線程只能輪流在某個(gè)CPU核心(多核CPU)或者CPU中運(yùn)行。服務(wù)器會(huì)負(fù)責(zé)緩存線程，因此不需要為每個(gè)新建的連接創(chuàng)建或者銷毀線程(線程的重用和銷毀都由服務(wù)器控制)。

當(dāng)客戶端連接到MySQL服務(wù)器時(shí)，服務(wù)器需要對(duì)其進(jìn)行認(rèn)證，如基于用戶名、原始主機(jī)信息和密碼;一旦連接成功，服務(wù)器會(huì)繼續(xù)驗(yàn)證客戶端是否具有執(zhí)行某個(gè)特定查詢的權(quán)限。

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

MySQL會(huì)解析查詢，并創(chuàng)建內(nèi)部數(shù)據(jù)結(jié)構(gòu)(解析樹)，然后對(duì)其進(jìn)行各種優(yōu)化，包括重寫查詢、決定表的讀取順序，以及選擇合適的索引等。

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

對(duì)于SELECT語(yǔ)句，服務(wù)器會(huì)優(yōu)先查詢緩存(Query Cache)。如果有緩存就直接返回緩存中的結(jié)果集，否則就執(zhí)行查詢解析、優(yōu)化和執(zhí)行的整個(gè)過(guò)程。

具體優(yōu)化措施我們之后再進(jìn)行探討。

3.并發(fā)控制

無(wú)論何時(shí)，只要有多個(gè)查詢需要在同一時(shí)刻修改數(shù)據(jù)，都會(huì)產(chǎn)生并發(fā)控制的問(wèn)題。在處理并發(fā)讀或者寫的時(shí)候，可以通過(guò)實(shí)現(xiàn)一個(gè)由兩種類型的鎖組成的鎖系統(tǒng)來(lái)解決問(wèn)題。這兩種類型的鎖通常被稱為共享鎖(讀鎖)和排它鎖(寫鎖)。

讀鎖是共享的，多個(gè)客戶在同一時(shí)刻可以同時(shí)讀取一個(gè)資源，互不干擾;而寫鎖是排他的，也就是說(shuō)一個(gè)寫鎖會(huì)阻塞其他的寫鎖和讀鎖，這樣才能保證數(shù)據(jù)安全。

一種提高共享資源并發(fā)性的方式就是讓鎖定對(duì)象更有選擇性，盡量只鎖定需要修改的部分?jǐn)?shù)據(jù)而不是所有的資源。在給定的資源上，鎖定的數(shù)據(jù)量越少，則系統(tǒng)的并發(fā)程度越高，只要相互之間不發(fā)生沖突即可。

但加鎖也需要消耗資源，如果花費(fèi)大量時(shí)間和資源來(lái)管理所而不是存儲(chǔ)數(shù)據(jù)，那就得不償失了。所以需要一種鎖策略，在鎖的開銷和數(shù)據(jù)的安全性之間尋求平衡。

MySQL的每種存儲(chǔ)引擎都可以實(shí)現(xiàn)自己的鎖策略，其中有兩種最重要的鎖策略：表鎖和行鎖。

表鎖是MySQL中最基本的鎖策略，并且是開銷最小的策略，他會(huì)鎖定整張表;在特定場(chǎng)景中表鎖可以有良好的性能。另外寫鎖也比讀鎖有更高的優(yōu)先級(jí)，一個(gè)寫鎖請(qǐng)求可能會(huì)被插到讀鎖隊(duì)列的前面。

行鎖可以***程度的支持并發(fā)，但同時(shí)開銷也是***，在InnoDB中實(shí)現(xiàn)的就是行鎖(在存儲(chǔ)引擎層實(shí)現(xiàn))。

4.MySQL的InnoDB引擎支持事務(wù)

有關(guān)事務(wù)的描述可以參考《MyBatis：Spring事務(wù)管理(十一)》

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

InnoDB采用的是兩階段鎖定協(xié)議，即在事務(wù)執(zhí)行過(guò)程中，隨時(shí)都可以執(zhí)行鎖定，鎖只有在執(zhí)行COMMIT或者ROLLBACK的時(shí)候才會(huì)釋放，并且所有的鎖是在同一時(shí)刻被釋放的，InnDB會(huì)根據(jù)隔離級(jí)別在需要的時(shí)候自動(dòng)加鎖。

5.多版本并發(fā)控制

MySQL的大多數(shù)事務(wù)性存儲(chǔ)引擎實(shí)現(xiàn)的都不是簡(jiǎn)單的行級(jí)鎖。基于提升并發(fā)性能的考慮，他們一般都同時(shí)實(shí)現(xiàn)了多版本并發(fā)控制(MVCC)，他可以認(rèn)為是行級(jí)鎖的一種變種，在很多情況下避免了加鎖操作，所以開銷更低。

MVCC的實(shí)現(xiàn)，是通過(guò)保存數(shù)據(jù)在某個(gè)時(shí)間點(diǎn)的快照來(lái)實(shí)現(xiàn)的，不管需要執(zhí)行多長(zhǎng)時(shí)間，每個(gè)事務(wù)看到的數(shù)據(jù)都是一致的。根據(jù)事務(wù)開始的時(shí)間不同，每個(gè)事務(wù)對(duì)同一張表，同一時(shí)刻看到的數(shù)據(jù)可能是不一樣的。

下面我們通過(guò)InnoDB的簡(jiǎn)化版行為來(lái)說(shuō)明MVCC是如何工作的。

InnoDB的MVCC是通過(guò)在每行記錄后面保存兩個(gè)隱藏列來(lái)實(shí)現(xiàn)：一個(gè)保存了行的創(chuàng)建時(shí)間，另一個(gè)保存行的過(guò)期時(shí)間(并不是實(shí)際時(shí)間值，而是系統(tǒng)版本號(hào))。每開始一個(gè)新的事務(wù)，系統(tǒng)版本號(hào)就會(huì)自動(dòng)遞增。事務(wù)開始時(shí)刻的系統(tǒng)版本號(hào)會(huì)作為事務(wù)的版本號(hào)，用來(lái)和查詢到的每行記錄的版本號(hào)進(jìn)行比較。當(dāng)在默認(rèn)可重復(fù)讀隔離級(jí)別下時(shí)：

SELECT：InnoDB會(huì)根據(jù)以下兩個(gè)條件檢查每行記錄：

>InnoDB只查找版本早于當(dāng)前事務(wù)版本的數(shù)據(jù)行，這樣可以確保事務(wù)讀取的行，要么是在事務(wù)開始之前已經(jīng)存在的，要么是事務(wù)自身插入或者修改過(guò)的。

>行的刪除版本要么未定義，要么大于當(dāng)前事務(wù)版本號(hào)。這可以確保事務(wù)讀取到的行，在事務(wù)開始之前未被刪除。

只有符合上述兩個(gè)條件的記錄，才能返回作為查詢結(jié)果。

INSERT：InnoDB為新插入的每一行保存當(dāng)前系統(tǒng)版本號(hào)作為行版本號(hào)。

DELETE：為刪除的每一行保存當(dāng)前系統(tǒng)版本號(hào)作為行刪除標(biāo)識(shí)。

UPDATE：InnoDB為插入一行新紀(jì)錄，保存當(dāng)前版本號(hào)作為行版本號(hào)，同時(shí)保存當(dāng)前系統(tǒng)版本號(hào)到原來(lái)的行作為行刪除標(biāo)識(shí)。

保存了這兩個(gè)額外系統(tǒng)版本號(hào)，可以使大多數(shù)讀操作都可以不用加鎖，使得讀數(shù)據(jù)操作很簡(jiǎn)單，性能很好，并且也能保證只會(huì)讀取到符合標(biāo)準(zhǔn)的行。不足之處是每行記錄都需要額外的存儲(chǔ)空間，需要做更多的行檢查工作以及一些額外的維護(hù)工作。

MVCC只在可重復(fù)讀和讀寫提交兩個(gè)隔離級(jí)別下工作。讀未提交下總是讀取***的數(shù)據(jù)行，而不是符合當(dāng)前事務(wù)版本的數(shù)據(jù)行;而序列化則會(huì)對(duì)所有讀取的行都是加鎖，所以這兩個(gè)隔離級(jí)別與MVCC不兼容。

6.InnoDB存儲(chǔ)引擎

InnoDB的數(shù)據(jù)存儲(chǔ)在表空間(tablespace)中，表空間是由InnoDB管理的一個(gè)黑盒子，由一系列的數(shù)據(jù)文件組成。在MySQL4.1后，InnoDB可以將每個(gè)表的數(shù)據(jù)和索引存放在單獨(dú)的文件中。

InnoDB采用MVCC來(lái)支持高并發(fā)，并且實(shí)現(xiàn)了四個(gè)標(biāo)準(zhǔn)的隔離界別，默認(rèn)是可重復(fù)讀，并且通過(guò)間隙鎖策略防止幻讀的出現(xiàn)。間隙鎖使得InnoDB不僅僅鎖定查詢涉及的行，還會(huì)對(duì)索引中的間隙進(jìn)行鎖定，防止幻影行的插入。

InnoDB表是基于聚簇索引建立的(后面再詳細(xì)介紹)，對(duì)主鍵查詢有很高的性能。不過(guò)他的二級(jí)索引(非主鍵索引)中必須包含主鍵列，所以如果主鍵列很大的話，其他的所有索引都會(huì)很大。

InnoDB內(nèi)部做了很多優(yōu)化，包括從磁盤讀取時(shí)間時(shí)采用的可預(yù)測(cè)性預(yù)讀，能夠自動(dòng)在內(nèi)存中創(chuàng)建hash索引以加速讀操作的自適應(yīng)哈希索引，以及能夠加速插入操作的插入緩沖區(qū)等，這些之后再具體分析其實(shí)現(xiàn)。同時(shí)作為事務(wù)型的存儲(chǔ)引擎，InnoDB通過(guò)一些機(jī)制和工具支持真正的熱備份。

7.MyISAM存儲(chǔ)引擎

在MySQL5.1及之前的版本MyISAM是默認(rèn)的存儲(chǔ)引擎，他提供了大量的特性如全文索引、壓縮、空間函數(shù)等，但他不支持事務(wù)和行級(jí)鎖，而且崩潰后無(wú)法安全恢復(fù)。對(duì)于只讀的數(shù)據(jù)，或者表比較小、可以忍受修復(fù)操作的，依然可以繼續(xù)使用。

>加鎖與并發(fā)：MyISAM對(duì)整張表加鎖，讀取時(shí)會(huì)對(duì)需要讀到的所有表加共享鎖，寫入時(shí)加排它鎖。但在表有讀取查詢的同時(shí)，也可以往表中插入新的記錄(并發(fā)插入)。

>修復(fù)：對(duì)于MyISAM表，可以手動(dòng)或者自動(dòng)執(zhí)行檢查和修復(fù)工作，但會(huì)造成一些數(shù)據(jù)丟失，而且修復(fù)操作很慢。

>索引特性：對(duì)于MyISAM即使是BLOB和TEXT字段也可以基于前500個(gè)字符創(chuàng)建索引。他也支持全文索引(基于分詞創(chuàng)建的索引)，可以支持復(fù)雜的查詢。

>延遲更新索引鍵：在創(chuàng)建表時(shí)，如果指定了DELAY_KEY_WRITE，在每次修改執(zhí)行完成時(shí)，不會(huì)立刻將修改的數(shù)據(jù)寫入磁盤，而是會(huì)寫到內(nèi)存中的鍵緩沖區(qū)，只有在清理緩沖區(qū)或者關(guān)閉表的時(shí)候才會(huì)將對(duì)應(yīng)的索引塊寫入磁盤。這樣可以極大提升寫入性能，但遇到數(shù)據(jù)庫(kù)或服務(wù)器崩潰時(shí)會(huì)造成索引損壞。

如果表創(chuàng)建并導(dǎo)入數(shù)據(jù)行不會(huì)再進(jìn)行修改操作，這時(shí)可以采用MyISAM壓縮表(myisampack)。這樣可以極大減少磁盤空間占用、減少磁盤I/O，從而提升查詢性能。壓縮表支持索引，但索引也都是只讀。