前言：最近在準(zhǔn)備面試，復(fù)習(xí)到MySQL相關(guān)的知識(shí)，于是做了個(gè)總結(jié)，希望能對(duì)小伙伴們有所幫助，同時(shí)也是對(duì)自己學(xué)習(xí)的一個(gè)反思總結(jié)。

1.MySQL的邏輯結(jié)構(gòu)：

image.png

1.第一層：對(duì)客戶端的連接處理、安全認(rèn)證、授權(quán)等，每個(gè)客戶端連接都會(huì)在服務(wù)端擁有一個(gè)線程，每個(gè)連接發(fā)起的查詢都會(huì)在對(duì)應(yīng)的單獨(dú)線程中執(zhí)行。

2.第二層：MySQL的核心服務(wù)功能層，包括查詢解析、分析、查詢緩存、內(nèi)置函數(shù)、存儲(chǔ)過(guò)程、觸發(fā)器、視圖等，select操作會(huì)先檢查是否命中查詢緩存，命中則直接返回緩存數(shù)據(jù)，否則解析查詢并創(chuàng)建對(duì)應(yīng)的解析樹(shù)。

3.第三層：存儲(chǔ)引擎，負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取，MySQL服務(wù)器通過(guò)API與存儲(chǔ)引擎通信，屏蔽了各種引擎之間的差異，常見(jiàn)的存儲(chǔ)引擎有：InnoDB、MyISAM，兩者的詳細(xì)介紹以后會(huì)繼續(xù)深入分析。

2.MySQL中的鎖：

MySQL在處理并發(fā)讀寫(xiě)的時(shí)候，分別使用共享鎖（寫(xiě)鎖）和排它鎖（讀鎖）對(duì)共享資源高并發(fā)操作，在加鎖的時(shí)候，最好能鎖定所需數(shù)據(jù)，控制鎖的粒度、提高并發(fā)能力，MySQL提供了兩種重要的鎖策略：

• 標(biāo)記鎖：鎖定整張表，在對(duì)表進(jìn)行插入更新刪除操作時(shí)，需要先獲得寫(xiě)鎖，鎖定整張表，其他讀寫(xiě)操作都會(huì)被阻塞。讀鎖之間不會(huì)阻塞。

• 行級(jí)鎖：鎖定數(shù)據(jù)所在行，行級(jí)鎖只在存儲(chǔ)引擎層實(shí)現(xiàn)，可以很好支持并發(fā)處理。

3.MySQL的事務(wù)：

• 事務(wù)的實(shí)現(xiàn)原理：MySQL中的事務(wù)是在存儲(chǔ)引擎中實(shí)現(xiàn)的，所以上層的服務(wù)是不會(huì)管理事務(wù)的。默認(rèn)情況下，MySQL自身提供了兩種事務(wù)型的存儲(chǔ)引擎，分別是InnnoDB和NDB Cluster。
  在默認(rèn)情況下，MySQL事務(wù)采用自動(dòng)提交模式，即如果沒(méi)有顯示的開(kāi)啟一個(gè)事務(wù)，那么每一次的查詢都將被當(dāng)做一個(gè)事務(wù)執(zhí)行自動(dòng)提交。當(dāng)然，也可以通過(guò)設(shè)置來(lái)改變這種自動(dòng)提交的模式。如果想開(kāi)啟自動(dòng)提交，可以通過(guò)命令SET AUTOCOMMIT = 1或SET AUTOCOMMIT =ON來(lái)設(shè)置；關(guān)閉自動(dòng)提交模式，可以通過(guò)命令SET AUTOCOMMIT = 0或SET AUTOCOMMIT = OFF來(lái)設(shè)置。
  InnoDB存儲(chǔ)引擎采用的是兩階段鎖定協(xié)議，在事務(wù)執(zhí)行的過(guò)程中，InnoDB會(huì)根據(jù)隔離級(jí)別在需要加鎖的時(shí)候自定加鎖，鎖只有在事務(wù)提交或回滾的時(shí)候才會(huì)釋放。當(dāng)然，也可以顯示的加鎖，如使用SELECT FOR UPDATE，也可以使用服務(wù)層實(shí)現(xiàn)的LOCK TABLES和UNLOCK TABLES。

• 事務(wù)日志：即一種特殊的操作記錄日志，存儲(chǔ)引擎在修改表數(shù)據(jù)的時(shí)候，只修改內(nèi)存中的拷貝，然后將修改行為記錄保存到硬盤(pán)上的事務(wù)日志中，對(duì)事務(wù)日志的保存操作采用追加的方式，保存操作是順序IO，相對(duì)于存儲(chǔ)引擎直接將數(shù)據(jù)持久化到硬盤(pán)的隨機(jī)IO高效的多。事務(wù)日志保存后，內(nèi)存中被修改的數(shù)據(jù)在后臺(tái)可以慢慢的持久化到硬盤(pán)。如果事務(wù)日志保存成功了，而內(nèi)存中被修改的數(shù)據(jù)沒(méi)有成功的寫(xiě)入硬盤(pán)，發(fā)生了系統(tǒng)崩潰，存儲(chǔ)引擎會(huì)在重啟時(shí)自動(dòng)恢復(fù)這部分?jǐn)?shù)據(jù)。

• 事務(wù)的特性：ACID

• 原子性：一個(gè)事務(wù)的所有操作要么全部執(zhí)行要么全部失敗回滾

• 一致性：事務(wù)執(zhí)行結(jié)果使數(shù)據(jù)庫(kù)從一個(gè)一致性狀態(tài)變成另一個(gè)一致性狀態(tài)

• 隔離性：事務(wù)操作提交之前，操作結(jié)果對(duì)其他事務(wù)不可見(jiàn)

• 持久性：事務(wù)提交后，所有的修改操作會(huì)永久保存到數(shù)據(jù)庫(kù)中

• 事務(wù)的隔離級(jí)別：SQL標(biāo)準(zhǔn)定義了四種隔離級(jí)別，分別是read uncommitter，read committed，repeatable read，serializable，可以通過(guò)set transaction isolation level命令來(lái)設(shè)置隔離級(jí)別

  
  
  image.png

臟讀：事務(wù)未提交的數(shù)據(jù)能被其他事務(wù)讀到
不可重復(fù)讀：一個(gè)事務(wù)前后兩次讀取某數(shù)據(jù)中間時(shí)刻，有事務(wù)修改了數(shù)據(jù)，導(dǎo)致兩次讀取的數(shù)據(jù)不一致
幻讀：事務(wù)在讀取某范圍內(nèi)數(shù)據(jù)，其他事務(wù)在范圍內(nèi)插入了新記錄，導(dǎo)致事務(wù)內(nèi)兩次讀取的數(shù)據(jù)會(huì)不一致

• 隔離級(jí)別的實(shí)現(xiàn)原理：

• 未提交讀：讀數(shù)據(jù)的時(shí)候不加鎖，寫(xiě)數(shù)據(jù)的時(shí)候加行級(jí)別的共享鎖，提交時(shí)釋放鎖。行級(jí)別的共享鎖不會(huì)對(duì)讀產(chǎn)生影響，但可以防止連個(gè)同時(shí)的寫(xiě)操作

• 已提交讀：事務(wù)讀取數(shù)據(jù)時(shí)（讀到數(shù)據(jù)的時(shí)候）加行級(jí)共享鎖鎖，讀完釋放鎖；事務(wù)寫(xiě)數(shù)據(jù)的時(shí)候（寫(xiě)操作發(fā)生瞬間），加行級(jí)獨(dú)占鎖，讀完后立即釋放鎖。由于事務(wù)寫(xiě)操作加獨(dú)占鎖，因此事務(wù)寫(xiě)操作時(shí)，讀操作也不能進(jìn)行，因此不能讀到事務(wù)的未提交數(shù)據(jù)，避免了臟讀的問(wèn)題，但由于讀操作的鎖加在讀上面（鎖的粒度較?。?，而不是事務(wù)之上，所以在同一事務(wù)的兩次讀操作之間可以插入其他事務(wù)的寫(xiě)操作，所以可能發(fā)生不可重復(fù)讀的問(wèn)題

• 可重復(fù)讀：與已提交讀不同的是，事務(wù)讀取數(shù)據(jù)在讀操作開(kāi)始瞬間就加上了行級(jí)鎖共享鎖，而且在事務(wù)結(jié)束的時(shí)候才釋放（鎖的粒度與已提交鎖不一樣），鎖加在事務(wù)上，解決了不可重讀問(wèn)題，事務(wù)寫(xiě)操作加獨(dú)占鎖，同上，但是由于加的是行級(jí)鎖，所以會(huì)出現(xiàn)幻讀問(wèn)題

• 可串行化：在讀操作時(shí)，加表級(jí)共享鎖，事務(wù)結(jié)束時(shí)釋放，寫(xiě)操作時(shí)，加表級(jí)獨(dú)占鎖，事務(wù)結(jié)束時(shí)釋放。該隔離級(jí)別下可能會(huì)導(dǎo)致大量的超時(shí)和鎖競(jìng)爭(zhēng)問(wèn)題。

4.MySQL中的死鎖：

指多個(gè)事務(wù)在同一資源上相互占用，并請(qǐng)求鎖定對(duì)方所占用的資源，從而導(dǎo)致的惡性循環(huán)條件，數(shù)據(jù)庫(kù)系統(tǒng)為解決這個(gè)問(wèn)題實(shí)現(xiàn)了死鎖檢測(cè)和死鎖超時(shí)機(jī)制，在MySQL的InnoDB存儲(chǔ)引擎中，解決死鎖的方法是將持有最少行級(jí)排它鎖的事務(wù)進(jìn)行回滾。

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

基于對(duì)并發(fā)性能的考慮，MySQL的大多數(shù)事務(wù)型存儲(chǔ)引擎都實(shí)現(xiàn)了多版本并發(fā)控制，可以簡(jiǎn)單的認(rèn)為MVCC是行級(jí)鎖的一個(gè)變種，但是它在很多情況下避免了加鎖操作，降低了開(kāi)銷。

InnoDB的MVCC是通過(guò)在每行記錄后添加兩個(gè)隱藏列來(lái)實(shí)現(xiàn)的，一個(gè)列用于保存行的創(chuàng)建時(shí)間，一個(gè)列用于保存行的過(guò)期時(shí)間，這兩個(gè)時(shí)間在實(shí)際存儲(chǔ)的時(shí)候，存儲(chǔ)的是系統(tǒng)版本號(hào)。每開(kāi)始一個(gè)新事務(wù)，系統(tǒng)版本號(hào)都將遞增。需要注意的是MVCC只能在Read Committed和Repeatable Read隔離級(jí)別下正常工作。

6.MySQL存儲(chǔ)引擎：

MySQL自身和第三方提供了多種存儲(chǔ)引擎，每種存儲(chǔ)引擎優(yōu)勢(shì)各不相同，可以根據(jù)實(shí)際業(yè)務(wù)需要來(lái)選擇對(duì)應(yīng)的存儲(chǔ)引擎。

• InnoDB是MySQL的默認(rèn)事務(wù)型存儲(chǔ)引擎，主要用來(lái)處理大量的短期型事務(wù)。它采用MVCC來(lái)支持高并發(fā)，默認(rèn)的事務(wù)隔離級(jí)別是Repeatable Read，并通過(guò)間隙鎖策略防止幻讀的出現(xiàn)。InnoDB表是基于聚簇索引建立的，而聚簇索引可以提高對(duì)主鍵查詢的性能，但是它的二級(jí)索引(非主鍵索引)中必須包含主鍵，如果主鍵列很大，并且有很多個(gè)二級(jí)索引，那么這些索引將會(huì)占用很大的空間和資源，所以在創(chuàng)建主鍵的時(shí)候盡量的小。

• 在MySQL5.1及之前的版本中，MyISAM是默認(rèn)的存儲(chǔ)引擎，它提供了全文索引、壓縮、空間函數(shù)等功能，但是它不支持事務(wù)和行級(jí)鎖，并且崩潰后無(wú)法安全恢復(fù)，而InnoDB引擎是可以自動(dòng)崩潰恢復(fù)的。MyISAM在并發(fā)的情況下，對(duì)整張表加鎖，讀操作會(huì)對(duì)需要讀的所有表加共享鎖，寫(xiě)入時(shí)對(duì)表加排它鎖。

感謝閱讀~