歡迎訪問(wèn)我的個(gè)人博客：MySQL架構(gòu)及MVCC

MySQL結(jié)構(gòu)

MySQL架構(gòu)圖便于理解MySQL。

MySQL架構(gòu)

最上層連接線程處理，是提供給客戶端的，如連接處理，授權(quán)認(rèn)證，安全。

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

第三層包含了存儲(chǔ)引擎。存儲(chǔ)引擎負(fù)責(zé)MySQL中數(shù)據(jù)的存儲(chǔ)和提取。每個(gè)存儲(chǔ)引擎都有它的優(yōu)勢(shì)和劣勢(shì)，服務(wù)器通過(guò)API與存儲(chǔ)引擎通信。這些API屏蔽了不同存儲(chǔ)引擎之間的差異，使得這些差異對(duì)上層的查詢過(guò)程透明。存儲(chǔ)引擎不會(huì)去解析SQL，不同存儲(chǔ)引擎之間也不會(huì)相互通信，只是簡(jiǎn)單的響應(yīng)上層服務(wù)器的請(qǐng)求。

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

MySQL會(huì)解析查詢，并創(chuàng)建解析樹(shù)對(duì)其進(jìn)行優(yōu)化，包括重寫(xiě)查詢，表的讀取順序，選擇合適的索引。我們一般使用explain請(qǐng)求優(yōu)化器解釋SQL執(zhí)行過(guò)程。優(yōu)化器不關(guān)心表使用的是什么存儲(chǔ)引擎，但存儲(chǔ)引擎對(duì)優(yōu)化查詢是由影響的，優(yōu)化器會(huì)請(qǐng)求存儲(chǔ)引擎提供容量或某個(gè)操作的開(kāi)銷(xiāo)信息，以及表數(shù)據(jù)的統(tǒng)計(jì)信息。

對(duì)于SELECT語(yǔ)句，在解析查詢之前，服務(wù)器會(huì)先檢查查詢緩存，如果能找到對(duì)應(yīng)的查詢，服務(wù)器就不必再執(zhí)行查詢解析，優(yōu)化和執(zhí)行的整個(gè)過(guò)程了，直接返回結(jié)果集即可。

并發(fā)控制

只要存在多個(gè)SQL同時(shí)讀寫(xiě)數(shù)據(jù)，就會(huì)存在并發(fā)問(wèn)題，MySQL是通過(guò)鎖機(jī)制解決并發(fā)問(wèn)題的。

讀寫(xiě)鎖

鎖一共有兩種類(lèi)型，讀鎖和寫(xiě)鎖，讀鎖是共享的，互不阻塞，寫(xiě)鎖是排他的，一個(gè)寫(xiě)鎖會(huì)阻塞其他寫(xiě)鎖和讀鎖，這樣設(shè)計(jì)是出于安全的考慮，保證用戶執(zhí)行寫(xiě)入的時(shí)候，其他用戶不能讀取和寫(xiě)入統(tǒng)一資源。

寫(xiě)鎖比讀鎖優(yōu)先級(jí)更高，因此寫(xiě)鎖請(qǐng)求可能插入到讀鎖前面。

使用鎖也需要消耗資源，包括獲得鎖，檢查鎖，釋放鎖等。所以需要在資源消耗（性能）和數(shù)據(jù)安全之間尋求平衡。MySQL提供了多種選擇，每種存儲(chǔ)引擎都可以實(shí)現(xiàn)自己的鎖策略。在所有鎖策略里面，最重要的是下面兩種策略。

表鎖

表鎖是MySQL中最基本的鎖策略，并且是開(kāi)銷(xiāo)最小的鎖。表鎖會(huì)鎖定整張表，一個(gè)用戶在對(duì)表進(jìn)行寫(xiě)操作前，需要先獲取鎖，獲取到鎖后會(huì)阻塞其他用戶對(duì)表的讀寫(xiě)操作。

在特定的場(chǎng)景中，表鎖策略性能很好，例如READ LOCAL表鎖支持某些類(lèi)型的并發(fā)寫(xiě)操作。

服務(wù)器會(huì)管理自己的鎖，MySQL本身還是會(huì)使用表鎖來(lái)實(shí)現(xiàn)不同的目的。比如服務(wù)器會(huì)為ALTER TABLE語(yǔ)句使用表鎖，而忽略存儲(chǔ)引擎本身的鎖機(jī)制。

行級(jí)鎖

行級(jí)鎖最大程度上的支持了并發(fā)處理，但也帶來(lái)了最大的所開(kāi)銷(xiāo)。存儲(chǔ)引擎InnoDB實(shí)現(xiàn)了行級(jí)鎖，行級(jí)鎖只在存儲(chǔ)引擎實(shí)現(xiàn)，MySQL服務(wù)器層沒(méi)有實(shí)現(xiàn)。

事務(wù)

最基礎(chǔ)也是最重要的：事務(wù)和隔離級(jí)別。學(xué)習(xí)Hibernate會(huì)說(shuō)，學(xué)習(xí)MyBatis會(huì)說(shuō)，學(xué)習(xí)Spring事務(wù)也會(huì)說(shuō)，感覺(jué)耳朵都起繭子了。附一個(gè)自己寫(xiě)過(guò)的文章：SPRING事務(wù)

死鎖

如果多個(gè)事務(wù)嘗試以不同的順序鎖定資源時(shí)，就會(huì)產(chǎn)生死鎖，多個(gè)事務(wù)同時(shí)鎖定同一資源時(shí)，也會(huì)產(chǎn)生死鎖。為了解決這個(gè)問(wèn)題，數(shù)據(jù)庫(kù)實(shí)現(xiàn)了各種死鎖檢測(cè)和死鎖超時(shí)機(jī)制。越復(fù)雜的系統(tǒng)，越能檢測(cè)到死鎖，比如InnoDB存儲(chǔ)引擎，會(huì)立即返回一個(gè)錯(cuò)誤。死鎖發(fā)生以后，只有部分或完全回滾一個(gè)事務(wù)，才能打破死鎖。對(duì)于事務(wù)型系統(tǒng)，死鎖是無(wú)法避免的，所以必須考慮如何處理死鎖。大多數(shù)情況下再次執(zhí)行因死鎖回滾的事務(wù)即可。

事務(wù)日志

事務(wù)日志可以幫助提高事務(wù)的效率。使用事務(wù)日志，存儲(chǔ)引擎在修改表數(shù)據(jù)時(shí)，只需要修改其內(nèi)存拷貝，再將該修改行為記錄到持久在硬盤(pán)上的事務(wù)日志中，而不用每次都將修改的數(shù)據(jù)本身持久到磁盤(pán)。

事務(wù)日志采用的是追加的方式，因此只在磁盤(pán)一小塊區(qū)域順序I/O，所以速度很快。事務(wù)日志持久之后，內(nèi)存中的被修改的數(shù)據(jù)可以慢慢的刷新到磁盤(pán)。

如果數(shù)據(jù)的修改已經(jīng)記錄到日志并持久化，但數(shù)據(jù)本身還沒(méi)有寫(xiě)回到磁盤(pán)，此時(shí)系統(tǒng)崩潰，存儲(chǔ)引擎在重啟時(shí)能夠自動(dòng)恢復(fù)這部分修改的數(shù)據(jù)。具體的恢復(fù)方式視存儲(chǔ)引擎而定。

MySQL中的事務(wù)

MySQL提供了兩種事務(wù)型的存儲(chǔ)引擎：InnoDB 和 NDB Cluster。

自動(dòng)提交

MySQL默認(rèn)采用自動(dòng)提交模式。如果不顯示的開(kāi)始一個(gè)事務(wù)，則每個(gè)查詢都被當(dāng)做一個(gè)事務(wù)執(zhí)行提交操作。

show variables like 'autocommit';   -- 查詢當(dāng)前模式
set autocommit = 0  ;  -- 關(guān)閉自動(dòng)提交，1或者ON為打開(kāi)，0或者OFF為關(guān)閉

在自動(dòng)提交關(guān)閉時(shí)，所有的查詢都是在一個(gè)事務(wù)中，直到顯式的執(zhí)行COMMIT或者ROLLBACK，該事務(wù)結(jié)束，同時(shí)又開(kāi)始了一個(gè)新事務(wù)。

如果修改非事務(wù)型的表，如MyISAM，不會(huì)又任何影響，這類(lèi)表沒(méi)有事務(wù)概念，相當(dāng)于一直啟用autocommit。

設(shè)置隔離級(jí)別

MySQL默認(rèn)的隔離級(jí)別為REPEATABLE-READ，可以通過(guò)以下命令設(shè)置隔離級(jí)別，新的隔離級(jí)別在下一個(gè)事務(wù)開(kāi)始時(shí)生效。

select @@global.tx_isolation;    -- 查詢當(dāng)前隔離級(jí)別
set session transacton isolation level read committed;  -- 只改變當(dāng)前會(huì)話隔離級(jí)別
set global transacton isolation level read uncommitted; -- 設(shè)置當(dāng)前系統(tǒng)的隔離級(jí)別

也可以在配置文件中設(shè)置整個(gè)數(shù)據(jù)庫(kù)的隔離級(jí)別，MySQL識(shí)別4個(gè)ANSI隔離級(jí)別，InnoDB支持所有隔離級(jí)別。

MVCC

MySQL的大多數(shù)事務(wù)存儲(chǔ)引擎實(shí)現(xiàn)都不是簡(jiǎn)單的行級(jí)鎖，一般是實(shí)現(xiàn)了多版本并發(fā)控制(MVCC)?？梢哉J(rèn)為MVCC是行級(jí)鎖的一個(gè)變種，它在很多情況下避免了加鎖操作，因此開(kāi)銷(xiāo)更低，在進(jìn)行讀操作時(shí)不需要阻塞，寫(xiě)操作只鎖定必要的行。

MVCC的實(shí)現(xiàn)

MVCC的實(shí)現(xiàn)，是通過(guò)保存數(shù)據(jù)在某個(gè)時(shí)間點(diǎn)的快照實(shí)現(xiàn)的。這樣就保證了不管事務(wù)不管運(yùn)行多長(zhǎng)時(shí)間，在同一個(gè)事務(wù)中看到的數(shù)據(jù)是一致的。

不同存儲(chǔ)引擎的MVCC實(shí)現(xiàn)是不同的，典型的有樂(lè)觀鎖并發(fā)控制和悲觀并發(fā)控制。在InnoDB的MVCC，是樂(lè)觀鎖并發(fā)控制，通過(guò)在每行記錄后面保存兩個(gè)隱藏的列來(lái)實(shí)現(xiàn)的。這兩個(gè)列保存了行創(chuàng)建時(shí)的系統(tǒng)版本號(hào)和刪除時(shí)的刪除版本號(hào)，每開(kāi)始一個(gè)新的事務(wù)，系統(tǒng)版本號(hào)都會(huì)自動(dòng)遞增。系統(tǒng)版本號(hào)會(huì)作為事務(wù)的版本號(hào)，用來(lái)和查詢每行記錄的版本號(hào)進(jìn)行比較。

InnoDB在REPEATABLE READ隔離級(jí)別下，不同具體操作如下：

• SELECT

  查找的數(shù)據(jù)必要符合下面兩個(gè)條件：

  1. 查找系統(tǒng)版本號(hào)小于等于當(dāng)前事務(wù)版本號(hào)的數(shù)據(jù)行，這樣可以確保事務(wù)讀取的行要么是事務(wù)開(kāi)始前已經(jīng)存在的行，要么是事務(wù)自身插入或修改的

  2. 刪除版本號(hào)未指定或大于當(dāng)前事務(wù)版本號(hào)的數(shù)據(jù)行，確保數(shù)據(jù)讀取到的行在事務(wù)開(kāi)始前未被刪除。

• INSERT

  新插入的每一行保存當(dāng)前事務(wù)版本號(hào)

• DELETE

  將當(dāng)前事務(wù)版本號(hào)保存為刪除版本號(hào)

• UPDATE

  原有的數(shù)據(jù)行的刪除版本號(hào)記錄為當(dāng)前事務(wù)版本號(hào)，然后插入一條新的記錄

MVCC的總結(jié)

保存了這兩個(gè)版本號(hào)，使大多數(shù)讀操作都不用加鎖，這樣設(shè)計(jì)操作簡(jiǎn)單，提升了性能，保證了只會(huì)讀取到符合標(biāo)準(zhǔn)的行。不足的地方是每行記錄都需要額外的存儲(chǔ)空間，需要做更多的檢查工作，以及額外的維護(hù)工作。

MVCC只在REPEATABLE READ和READ COMMIT兩個(gè)隔離級(jí)別下工作，對(duì)于READ UNCOMMITTED和SERIALIZABLE不兼容，前者只讀取最新的數(shù)據(jù)行，后者對(duì)讀取的所有數(shù)據(jù)行都加鎖。

存儲(chǔ)引擎

InnoDB

InnoDB是MySQL默認(rèn)的存儲(chǔ)引擎，也是最重要，使用最廣泛的存儲(chǔ)引擎，它被設(shè)計(jì)用來(lái)處理短期事務(wù)，另外InnoDB還有性能優(yōu)勢(shì)和自動(dòng)崩潰恢復(fù)特性。在日常開(kāi)發(fā)中，除非有特別的原因，否則優(yōu)先考慮InnoDB。

特性

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

2.InnoDB表是基于聚簇索引建立的，聚簇索引對(duì)主鍵查詢有很高的性能。不過(guò)它的二級(jí)索引中必須包含主鍵列，所以如果主鍵列很大的話，其他所有索引都會(huì)很大。因此索引較多的話，主鍵應(yīng)當(dāng)盡可能的小。

3.InnoDB支持在線熱備份。

4.InnoDB在崩潰的情況下發(fā)生損壞的概率低，恢復(fù)速度快。

MyISAM

在MySQL5.1版本之前，MyISAM是默認(rèn)的存儲(chǔ)引擎，它提供了大量的特性，包括全文索引，壓縮，空間函數(shù)等，但它不支持事務(wù)和行級(jí)鎖，最重要的是崩潰后無(wú)法安全恢復(fù)。

特性

MyISAM對(duì)整張表加鎖，而不是針對(duì)行。讀取時(shí)會(huì)對(duì)需要讀到的所有表加讀鎖，寫(xiě)入時(shí)對(duì)表加寫(xiě)鎖。

MyISAM引擎設(shè)計(jì)簡(jiǎn)單，數(shù)據(jù)以緊密格式存儲(chǔ)，在某些場(chǎng)景下性能很好。

其他內(nèi)建引擎

Archive、CVS、Memory、Merge等等。

除了內(nèi)建引擎外，還有一些第三方的存儲(chǔ)引擎，以插件的形式供MySQL使用。