Mysql是我們?cè)谌粘ｉ_發(fā)中最常使用的一種數(shù)據(jù)庫，當(dāng)我們利用Mysql實(shí)現(xiàn)各種業(yè)務(wù)增刪改查時(shí)，都是將其當(dāng)做一個(gè)黑盒在使用，我們的系統(tǒng)只需要從數(shù)據(jù)庫的連接池中獲取一個(gè)連接就可以執(zhí)行sql工作。執(zhí)行一條insert語句，表里會(huì)多出來一條數(shù)據(jù)；執(zhí)行一條select語句，就能從表里找出滿足條件的數(shù)據(jù)。至于這個(gè)過程是怎么實(shí)現(xiàn)的，對(duì)我們來說，底層的事情都交給了數(shù)據(jù)庫，內(nèi)部工作和運(yùn)行機(jī)制完全透明，以至于在學(xué)校學(xué)了一個(gè)學(xué)期的數(shù)據(jù)庫課程，只學(xué)到了表層和皮毛，以為會(huì)寫超長復(fù)雜的Sql語句就算掌握了數(shù)據(jù)庫，直到進(jìn)入職場(chǎng)被面試官吊打，在面對(duì)線上超大數(shù)據(jù)體量的Sql優(yōu)化時(shí)兩眼懵逼，我們對(duì)數(shù)據(jù)庫的了解還太少，底層原理和深層優(yōu)化有待提升，因此在這里開一個(gè)Mysql專題進(jìn)行爆肝與死磕，將學(xué)習(xí)心得與總結(jié)和大家共享。

一. Mysql的架構(gòu)設(shè)計(jì)

1.連接器

Mysql作為服務(wù)器，一個(gè)客戶端的Sql連接過來就需要分配一個(gè)線程進(jìn)行處理，這個(gè)線程會(huì)專門負(fù)責(zé)監(jiān)聽請(qǐng)求并讀取數(shù)據(jù)。這部分的線程和連接管理都是有一個(gè)連接器，專門負(fù)責(zé)跟客戶端建立連接、權(quán)限認(rèn)證、維持和管理連接。

2.解析器

SQL解析，就是按照SQL語法，把我們編寫的SQL語句進(jìn)行詞法分析，理解這個(gè)SQL需要做什么事情，比如如下SQL語句，就會(huì)被解析器給拆成三步邏輯：

• 查詢user表；
• 尋找到userId=007的那條數(shù)據(jù)；
• 將數(shù)據(jù)中的name、age、country字段信息提取出來；

select name,age,country from user where userId=007;

3.優(yōu)化器

當(dāng)解析器把SQL語句解析出來步驟以后，緊接著會(huì)通過優(yōu)化器來選擇一個(gè)最優(yōu)的查詢路徑，這個(gè)是對(duì)于我們編寫的超大型復(fù)雜SQL時(shí)十分有用，一條SQL語句可能會(huì)對(duì)應(yīng)多個(gè)不同的查詢路徑樹，優(yōu)化器會(huì)從中選擇一個(gè)最優(yōu)路徑出來，返回一個(gè)這個(gè)語句的執(zhí)行方案交給下一步進(jìn)行繼續(xù)處理。

4.執(zhí)行器

這一步就是根據(jù)上一步驟優(yōu)化器生成的SQL執(zhí)行方案，去調(diào)用存儲(chǔ)引擎的接口（InnoDB、mysam）完成SQL語句的執(zhí)行計(jì)劃，這個(gè)SQL引擎操作的有可能是內(nèi)存數(shù)據(jù)，也有可能是磁盤文件。

于是把上面一條SQL語句的執(zhí)行抽取出來形成下面這個(gè)Mysql的邏輯架構(gòu)圖：

Mysq架構(gòu)設(shè)計(jì)

補(bǔ)充：MySQL 8.0 版本直接將查詢緩存的整塊功能刪掉了，日常也不建議開啟使用。

二. InnoDB存儲(chǔ)引擎設(shè)計(jì)

上面流程介紹了一條SQL語句在數(shù)據(jù)庫中的執(zhí)行流程，當(dāng)SQL執(zhí)行到存儲(chǔ)引擎這里，因?yàn)椴煌囊娴膶?shí)現(xiàn)機(jī)制有所不同，現(xiàn)在就以使用最廣泛的為InnoDB引擎再來細(xì)化說明Innodb在數(shù)據(jù)查詢和更新流程的細(xì)節(jié)。

1. 內(nèi)存緩沖池

InnoDB中有一個(gè)非常重要的內(nèi)存組件——緩沖池（Buffer Pool），這里會(huì)緩存很多數(shù)據(jù)便于查詢時(shí)，直接從讀取緩存數(shù)據(jù)，而不需要訪問磁盤。在執(zhí)行更新操作時(shí)，如對(duì)“id=007”數(shù)據(jù)進(jìn)行更新，會(huì)先查詢BufferPool中是否存在，不存在的話，就從磁盤中加載到緩沖池中來，然后還要對(duì)這行記錄加獨(dú)占鎖。

2.undo日志

如果執(zhí)行一個(gè)更新語句，且這個(gè)語句還在事務(wù)里的話，在事務(wù)提交以前，我們都可以選擇回滾，而這部分回滾的數(shù)據(jù)，就是未更新以前的數(shù)據(jù)，它是保存在undo日志里的。

3.redo日志

在更新操作時(shí)，會(huì)先更新Buffer Pool中的數(shù)據(jù)然后再去操作磁盤，但是在極端情況下會(huì)出現(xiàn)系統(tǒng)宕機(jī)或者斷電導(dǎo)致磁盤還未更新就丟失了數(shù)據(jù)，此時(shí)需要把對(duì)內(nèi)存所做的修改寫入到一個(gè)redo log buffer里去，這里也是一個(gè)內(nèi)存緩沖區(qū)，用于存放redo日志的。在事務(wù)提交策略上，有一個(gè)關(guān)鍵配置：

innodb_flush_log_at_trx_commit=1

commit=0時(shí)，事務(wù)提交成功，redo buffer不會(huì)寫入redo log
commit=1時(shí)，只要事務(wù)提交成功，redo buffer一定會(huì)寫入redo log（推薦）
commit=2時(shí)，事務(wù)提交成功，redo buffer先寫入os cache，然后過段時(shí)間才刷入redo log

采用了commit=1的配置，就能保證提交事務(wù)的時(shí)候，redo日志會(huì)刷入磁盤，數(shù)據(jù)不丟失。

4.binlog日志

前面說的redo日志是偏向物理性質(zhì)的日志，記錄的是對(duì)數(shù)據(jù)頁中某一個(gè)數(shù)據(jù)進(jìn)行了什么修改，和引擎有關(guān)。而Binlog日志則偏向于邏輯層面的一個(gè)歸檔日志，記錄的是對(duì)表中某行數(shù)據(jù)做了什么操作，且修改后的值為多少，是Mysql Server自己的日志文件。

binlog日志的落盤是在上面redo日志落盤以后才會(huì)去執(zhí)行的，而且落盤策略也是可以選擇直接刷盤還是先刷到OS cache中，這個(gè)配置項(xiàng)取決于sync_binlog。sync_binlog 這個(gè)參數(shù)設(shè)置成 1 的時(shí)候，表示每次事務(wù)的 binlog 都持久化到磁盤，這樣可以保證 MySQL 異常重啟之后 binlog 不丟失（一般都推薦這個(gè)值）。當(dāng)Mysql把binlog寫入了磁盤文件以后，就完成了最終的事務(wù)提交，這次提交就會(huì)把本次更新對(duì)應(yīng)的binlog文件名+binlog的位置都寫入redo log的日志文件中，并同時(shí)寫入一個(gè)commit標(biāo)記。到這里，才最終完成了一次事務(wù)的提交，總的一個(gè)流程圖如下：

5.IO線程隨機(jī)刷盤

當(dāng)事務(wù)提交完畢以后，所有的日志文件都已經(jīng)更新到最新了，此時(shí)系統(tǒng)不懼任何宕機(jī)斷電行為了，后臺(tái)的IO線程會(huì)隨機(jī)把內(nèi)存中的那個(gè)更新后的臟數(shù)據(jù)刷入到磁盤文件中，此時(shí)內(nèi)存和磁盤中的數(shù)據(jù)已經(jīng)保持一致。

更新流程

核心總結(jié)

基于InnoDB的數(shù)據(jù)更新做一個(gè)流程總結(jié)如下：

• 1、加載磁盤文件到buffer Pool中；
• 2、更新數(shù)據(jù)之前，寫入舊數(shù)據(jù)到undo日志，便于回退；
• 3、更新內(nèi)存中的buffer pool數(shù)據(jù)；
• 4、將更新部分的redo log寫入到redo log buffer中；
• 5、redo日志刷入磁盤
• 6、binlog日志刷入磁盤
• 7、將binlog文件和位置寫入到redo日志文件中，并寫入commit。
• 8、后臺(tái)的IO線程某個(gè)時(shí)間隨機(jī)將buffer pool中的臟數(shù)據(jù)同步到磁盤文件。

InnoDB引擎中主要就是包含了buffer pool、redo log buffer等內(nèi)存數(shù)據(jù)，同時(shí)也包含了undo日志、redo日志等磁盤文件數(shù)據(jù)，另外Mysql也會(huì)有自己的binlog日志。buffer pool是Mysql里面的一個(gè)核心內(nèi)存組件，所有的增刪改查操作都是針對(duì)buffer pool來進(jìn)行的，然后才是去配合寫undo、redo、binlog等操作，這些都是組件的設(shè)計(jì)既可以用于提高數(shù)據(jù)庫的并發(fā)性能，同時(shí)更重要的可以設(shè)計(jì)保證事務(wù)的四個(gè)特性（ACID），譬如redo日志可以保證持久性，undo可以用于回滾保證原子性等等。這一節(jié)就到這里，后面一節(jié)的會(huì)重點(diǎn)安排一下InnoDB索引及數(shù)據(jù)結(jié)構(gòu)的內(nèi)容。