本篇文章給大家?guī)砹薓YSQL進(jìn)階之體系結(jié)構(gòu)和InnoDB存儲(chǔ)引擎的相關(guān)知識(shí)，希望對(duì)大家有幫助。

MySQL基本架構(gòu)圖

大體來說，MySQL 可以分為 Server 層和存儲(chǔ)引擎層兩部分。

Server 層包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等，涵蓋 MySQL 的大多數(shù)核心服務(wù)功能，以及所有的內(nèi)置函數(shù)（如日期、時(shí)間、數(shù)學(xué)和加密函數(shù)等），所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)，比如存儲(chǔ)過程、觸發(fā)器、視圖等。

連接器

連接器就是你連接到數(shù)據(jù)庫時(shí)使用的，負(fù)責(zé)跟客戶端建立連接、獲取權(quán)限、維持和管理連接。

命令： mysql -h$ip -P$port -u$user -p，回車后輸密碼，也可以在 -p 后面輸入密碼，但是有密碼泄露的風(fēng)險(xiǎn)。

show processlist,可以查看連接的情況，Command 列中有一個(gè) Sleep 表示連接空閑。

空閑連接默認(rèn)8小時(shí)會(huì)被斷開，可以由wait_timeout參數(shù)配置。

在數(shù)據(jù)庫中，長(zhǎng)連接是指連接成功后，如果客戶端持續(xù)有請(qǐng)求，則一直使用同一個(gè)連接。短連接則是指每次執(zhí)行完很少的幾次查詢就斷開連接，下次查詢?cè)僦匦陆⒁粋€(gè)。

由于建立連接比較耗資源，所以建議盡量使用長(zhǎng)連接，但是使用長(zhǎng)連接后，MySQL 占用內(nèi)存漲得特別快，這是因?yàn)?MySQL 在執(zhí)行過程中臨時(shí)使用的內(nèi)存是管理在連接對(duì)象里面的。這些資源會(huì)在連接斷開的時(shí)候才釋放。所以如果長(zhǎng)連接累積下來，可能導(dǎo)致內(nèi)存占用太大，被系統(tǒng)強(qiáng)行殺掉（OOM），從現(xiàn)象看就是 MySQL 異常重啟了。

解決方案：

定期斷開長(zhǎng)連接。使用一段時(shí)間，或者程序里面判斷執(zhí)行過一個(gè)占用內(nèi)存的大查詢后，斷開連接，之后要查詢?cè)僦剡B。

如果你用的是 MySQL 5.7 或更新版本，可以在每次執(zhí)行一個(gè)比較大的操作后，通過執(zhí)行 mysql_reset_connection 來重新初始化連接資源。這個(gè)過程不需要重連和重新做權(quán)限驗(yàn)證，但是會(huì)將連接恢復(fù)到剛剛創(chuàng)建完時(shí)的狀態(tài)。

查詢緩存

查詢緩存是將之前執(zhí)行過的語句及其結(jié)果以 key-value 對(duì)的形式緩存在內(nèi)存中。key 是查詢的語句，value 是查詢的結(jié)果。如果你的查詢能夠直接在這個(gè)緩存中找到 key，那么這個(gè) value 就會(huì)被直接返回給客戶端。

查詢緩存在MYSQL8時(shí)被移除了，由于查詢緩存失效頻繁，命中率低。

分析器

分析器先會(huì)做“詞法分析”，識(shí)別出里面的字符串分別是什么，代表什么。然后需要做“語法分析”，判斷你輸入的這個(gè) SQL 語句是否滿足 MySQL 語法。

優(yōu)化器

執(zhí)行器

存儲(chǔ)引擎層負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取。其架構(gòu)模式是插件式的，支持 InnoDB、MyISAM、Memory 等多個(gè)存儲(chǔ)引擎?，F(xiàn)在最常用的存儲(chǔ)引擎是 InnoDB，它從 MySQL 5.5.5 版本開始成為了默認(rèn)存儲(chǔ)引擎。

一條 Select 語句執(zhí)行流程

上圖以 InnoDB 存儲(chǔ)引擎為例，處理過程如下：

• 用戶發(fā)送請(qǐng)求到 tomcat ，通過 tomcat 鏈接池和 mysql 連接池建立連接，然后通過連接發(fā)送 SQL 語句到 MySQL；

• MySQL 有一個(gè)單獨(dú)的監(jiān)聽線程，讀取到請(qǐng)求數(shù)據(jù)，得到連接中請(qǐng)求的SQL語句；

• 將獲取到的SQL數(shù)據(jù)發(fā)送給SQL接口去執(zhí)行；

• SQL接口將SQL發(fā)送給SQL解析器進(jìn)行解析；

• 將解析好的SQL發(fā)送給查詢優(yōu)化器，找到最優(yōu)的查詢路勁，然后發(fā)給執(zhí)行器；

• 執(zhí)行器根據(jù)優(yōu)化后的執(zhí)行方案調(diào)用存儲(chǔ)引擎的接口按照一定的順序和步驟進(jìn)行執(zhí)行。

• 舉個(gè)例子，比如執(zhí)行器可能會(huì)先調(diào)用存儲(chǔ)引擎的一個(gè)接口，去獲取“users”表中的第一行數(shù)據(jù)，然后判斷一下這個(gè)數(shù)據(jù)的 “id”字段的值是否等于我們期望的一個(gè)值，如果不是的話，那就繼續(xù)調(diào)用存儲(chǔ)引擎的接口，去獲取“users”表的下一行數(shù)據(jù)。 就是基于上述的思路，執(zhí)行器就會(huì)去根據(jù)我們的優(yōu)化器生成的一套執(zhí)行計(jì)劃，然后不停的調(diào)用存儲(chǔ)引擎的各種接口去完成SQL 語句的執(zhí)行計(jì)劃，大致就是不停的更新或者提取一些數(shù)據(jù)出來。

在這里涉及到幾個(gè)問題：

MySQL驅(qū)動(dòng)到底是什么東西？

以java為例，我們們?nèi)绻贘ava系統(tǒng)中去訪問一個(gè)MySQL數(shù)據(jù)庫，必須得在系統(tǒng)的依賴中加入一個(gè)MySQL驅(qū)動(dòng)，比如在Maven里面要加上

<dependency>     <groupId>mysql</groupId>     <artifactId>mysql-connector-java</artifactId>     <version>5.1.46</version> </dependency>

那么這個(gè)MySQL驅(qū)動(dòng)到底是個(gè)什么東西?其實(shí)L驅(qū)動(dòng)就會(huì)在底層跟數(shù)據(jù)庫建立網(wǎng)絡(luò)連接，有網(wǎng)絡(luò)連接，接著才能去發(fā)送請(qǐng)求給數(shù)據(jù)庫服務(wù)器！讓語言編寫的系統(tǒng)通過MySQL驅(qū)動(dòng)去訪問數(shù)據(jù)庫，如下圖

數(shù)據(jù)庫連接池到底是用來干什么的？

假設(shè)用java開發(fā)一個(gè)web服務(wù)部署在tomcat上，tomcat可以多線程并發(fā)處理請(qǐng)求，所以首先一點(diǎn)就是不可能只會(huì)創(chuàng)建一個(gè)數(shù)據(jù)庫連接（多個(gè)請(qǐng)求去搶一個(gè)連接，效率得多低下）。

其次，如果每個(gè)請(qǐng)求都去創(chuàng)建一個(gè)數(shù)據(jù)庫連接呢? 這也是非常不好的，因?yàn)槊看谓⒁粋€(gè)數(shù)據(jù)庫連接都很耗時(shí)，好不容易建立好了連接，執(zhí)行完了SQL語句，還把數(shù)據(jù)庫連接給銷毀，頻繁創(chuàng)建和銷毀帶來性能問題。

所以一般使用數(shù)據(jù)庫連接池，也就是在一個(gè)池子里維持多個(gè)數(shù)據(jù)庫連接，讓多個(gè)線程使用里面的不同的數(shù)據(jù)庫連接去執(zhí)行SQL語句，然后執(zhí)行完SQL語句之后，不要銷毀這個(gè)數(shù)據(jù)庫連接，而是把連接放回池子里，后續(xù)還可以繼續(xù)使用?；谶@樣的一個(gè)數(shù)據(jù)庫連接池的機(jī)制，就可以解決多個(gè)線程并發(fā)的使用多個(gè)數(shù)據(jù)庫連接去執(zhí)行SQL語句的問題，而且還避免了數(shù)據(jù)庫連接使用完之后就銷毀的問題了。

MySQL數(shù)據(jù)庫的連接池是用來干什么的？

MySQL數(shù)據(jù)庫的連接池的作用和java應(yīng)用端連接池作用一樣，都是起到了復(fù)用連接的作用。

InnoDB 存儲(chǔ)引擎

InnoDB 架構(gòu)簡(jiǎn)析

從圖中可見，InnoDB 存儲(chǔ)引擎由內(nèi)存池，后臺(tái)線程和磁盤文件三大部分組成

再來一張突出重點(diǎn)的圖：

InnoDB 存儲(chǔ)引擎第一部分：內(nèi)存結(jié)構(gòu)

Buffer Pool緩沖池

InnoDB 存儲(chǔ)引擎基于磁盤存儲(chǔ)的，并將其中的記錄按照頁的方式進(jìn)行管理，但是由于CPU速度和磁盤速度之間的鴻溝，基于磁盤的數(shù)據(jù)庫系統(tǒng)通常使用緩沖池記錄來提高數(shù)據(jù)庫的整體性能。

在數(shù)據(jù)庫進(jìn)行讀取操作，將從磁盤中讀到的頁放在緩沖池中，下次再讀取相同的頁中時(shí)，首先判斷該頁是否在緩沖池中。若在緩沖池中，稱該頁在緩沖池中被命中，直接讀取該頁，否則讀取磁盤上的頁。

對(duì)于數(shù)據(jù)庫中頁的修改操作，首先修改在緩沖池中的頁，然后再以一定的頻率刷新到磁盤上，頁從緩沖池刷新回磁盤的操作并不是在每次頁發(fā)生更新時(shí)觸發(fā)，而是通過一種稱為 CheckPoint 的機(jī)制刷新回磁盤。所以，緩沖池的大小直接影響著數(shù)據(jù)庫的整體性能，可以通過配置參數(shù) innodb_buffer_pool_size 來設(shè)置，緩沖池默認(rèn)是128MB，還是有點(diǎn)小的，如果你的數(shù)據(jù)庫是16核32G的機(jī)器，那么你就可以給Buffer Pool分配個(gè)2GB的內(nèi)存。

由于緩沖池不是無限大的，隨著不停的把磁盤上的數(shù)據(jù)頁加載到緩沖池中，緩沖池總要被用完，這個(gè)時(shí)候只能淘汰掉一些緩存頁，淘汰方式就使用最近最少被使用算法（LRU），具體來說就是引入一個(gè)新的LRU鏈表，通過這個(gè)LRU鏈表，就可以知道哪些緩存頁是最近最少被使用的，那么當(dāng)你緩存頁需要騰出來一個(gè)刷入磁盤的時(shí)候，可以選擇那個(gè)LRU鏈表中最近最少被使用的緩存頁淘汰。

緩沖池中緩存的數(shù)據(jù)頁類型有：索引頁、數(shù)據(jù)頁、undo頁、插入緩沖、自適應(yīng)哈希索引、InnoDB存儲(chǔ)的鎖信息和數(shù)據(jù)字典信息。

數(shù)據(jù)頁和索引頁

頁(Page)是 Innodb 存儲(chǔ)的最基本結(jié)構(gòu)，也是 Innodb 磁盤管理的最小單位，與數(shù)據(jù)庫相關(guān)的所有內(nèi)容都存儲(chǔ)在 Page 結(jié)構(gòu)里。Page 分為幾種類型，數(shù)據(jù)頁和索引頁就是其中最為重要的兩種類型。

插入緩沖（Insert Buffer）

在 InnoDB 引擎上進(jìn)行插入操作時(shí)，一般需要按照主鍵順序進(jìn)行插入，這樣才能獲取較高的插入性能。當(dāng)一張表中存在非聚簇的不唯一的索引時(shí)，在插入時(shí)，數(shù)據(jù)頁的存放還是按照主鍵進(jìn)行順序存放，但是對(duì)于非聚簇索引葉子節(jié)點(diǎn)的插入不再是順序的了，這時(shí)就需要離散的訪問非聚簇索引頁，由于隨機(jī)讀取的存在導(dǎo)致插入操作性能下降。

所以 InnoDB 存儲(chǔ)引擎開創(chuàng)性地設(shè)計(jì)了 Insert Buffer ，對(duì)于非聚集索引的插入或更新操作，不是每一次直接插入到索引頁中，而是先判斷插入的非聚集索引頁是否在緩沖池中，若在，則直接插入；若不在，則先放入到一個(gè) Insert Buffer 對(duì)象中，好似欺騙。數(shù)據(jù)庫這個(gè)非聚集的索引已經(jīng)插到葉子節(jié)點(diǎn)，而實(shí)際并沒有，只是存放在另一個(gè)位置。然后再以一定的頻率和情況進(jìn)行 Insert Buffer 和輔助索引頁子節(jié)點(diǎn)的 merge（合并）操作，這時(shí)通常能將多個(gè)插入合并到一個(gè)操作中（因?yàn)樵谝粋€(gè)索引頁中），這就大大提高了對(duì)于非聚集索引插入的性能。

然而 Insert Buffer 的使用需要同時(shí)滿足以下兩個(gè)條件：

• 索引是輔助索引（ secondary index ) ;

• 索引不是唯一（ unique ）的。

當(dāng)滿足以上兩個(gè)條件時(shí)， InnoDB 存儲(chǔ)引擎會(huì)使用 Insert Buffer ，這樣就能提高插入操作的性能了。不過考慮這樣一種情況:應(yīng)用程序進(jìn)行大量的插入操作，這些都涉及了不唯一的非聚集索引，也就是使用了 Insert Buffer。若此時(shí) MySQL數(shù)據(jù)庫發(fā)生了宕機(jī)這時(shí)勢(shì)必有大量的 Insert Buffer 并沒有合并到實(shí)際的非聚集索引中去。

因此這時(shí)恢復(fù)可能需要很長(zhǎng)的時(shí)間,在極端情況下甚至需要幾個(gè)小時(shí)。輔助索引不能是唯一的，因?yàn)樵诓迦刖彌_時(shí)，數(shù)據(jù)庫并不去查找索引頁來判斷插入的記錄的唯一性。如果去查找肯定又會(huì)有離散讀取的情況發(fā)生，從而導(dǎo)致 Insert Buffer 失去了意義。

可以通過命令 SHOW ENGINE INNODB STATUS 來查看插入緩沖的信息

seg size顯示了當(dāng)前 Insert Buffer的大小為11336×16KB,大約為177MB; free list len代表了空閑列表的長(zhǎng)度;size代表了已經(jīng)合并記錄頁的數(shù)量。而黑體部分的第2行可能是用戶真正關(guān)心的,因?yàn)樗@示了插入性能的提高。 Inserts代表了插入的記錄數(shù);merged recs代表了合并的插入記錄數(shù)量; merges代表合并的次數(shù),也就是實(shí)際讀取頁的次數(shù)。 merges: merged recs大約為1:3,代表了插入緩沖將對(duì)于非聚集索引頁的離散IO邏輯請(qǐng)求大約降低了2/3。

正如前面所說的,目前 Insert Buffer存在一個(gè)問題是:在寫密集的情況下,插入緩沖會(huì)占用過多的緩沖池內(nèi)存( innodb buffer pool),默認(rèn)最大可以占用到1/2的緩沖池內(nèi)存。以下是 InnoDB存儲(chǔ)引擎源代碼中對(duì)于 insert buffer的初始化操作:

Change Buffer

InnoDB 從1.0.x版本開始引入了 Change Buffer,可將其視為 Insert Buffer的升級(jí)版本, InnodB 存儲(chǔ)引擎可以對(duì)DML操作— INSERT、 DELETE、 UPDATE 都進(jìn)行緩沖,他們分別是: Insert Buffer、 Delete Buffer、 Purge buffer當(dāng)然和之前 Insert Buffer一樣, Change Buffer適用的對(duì)象依然是非唯一的輔助索引。

對(duì)一條記錄進(jìn)行 UPDATE 操作可能分為兩個(gè)過程:

• 將記錄標(biāo)記為已刪除;

• 真正將記錄刪除

因此 Delete Buffer對(duì)應(yīng) UPDATE操作的第一個(gè)過程,即將記錄標(biāo)記為刪除。 PurgeBuffer對(duì)應(yīng) UPDATE 操作的第二個(gè)過程,即將記錄真正的刪除。同時(shí), InnoDB 存儲(chǔ)引擎提供了參數(shù) innodb_change_buffering,用來開啟各種Buffer的選項(xiàng)。該參數(shù)可選的值為: Inserts、 deletes、 purges、 changes、all、none。 Inserts、 deletes、 purges 就是前面討論過的三種情況。 changes 表示啟用 Inserts 和 deletes,all表示啟用所有,none表示都不啟用。該參數(shù)默認(rèn)值為all。

從 InnoDB1.2.x版本開始,可以通過參數(shù) innodb_change_buffer_max_size 來控制Change Buffer最大使用內(nèi)存的數(shù)量:

mysql> show variables like 'innodb_change_buffer_max_size'; +-------------------------------+-------+ | Variable_name                 | Value | +-------------------------------+-------+ | innodb_change_buffer_max_size | 25    | +-------------------------------+-------+ 1 row in set (0.05 sec)

innodb_change_buffer_max_size 值默認(rèn)為25,表示最多使用1/4的緩沖池內(nèi)存空間。

而需要注意的是,該參數(shù)的最大有效值為50在 MySQL5.5版本中通過命令 SHOW ENGINE INNODB STATUS,可以觀察到類似如下的內(nèi)容:

可以看到這里顯示了 merged operations和 discarded operation,并且下面具體顯示 Change Buffer中每個(gè)操作的次數(shù)。 Insert 表示 Insert Buffer; delete mark表示 Delete Buffer; delete表示 Purge Buffer; discarded operations表示當(dāng) Change Buffer發(fā)生 merge時(shí),表已經(jīng)被刪除,此時(shí)就無需再將記錄合并(merge)到輔助索引中了。

自適應(yīng)哈希索引

InnoDB 會(huì)根據(jù)訪問的頻率和模式，為熱點(diǎn)頁建立哈希索引，來提高查詢效率。InnoDB 存儲(chǔ)引擎會(huì)監(jiān)控對(duì)表上各個(gè)索引頁的查詢，如果觀察到建立哈希索引可以帶來速度上的提升，則建立哈希索引，所以叫做自適應(yīng)哈希索引。

自適應(yīng)哈希索引通過緩沖池的B+樹頁構(gòu)建而來，因此建立速度很快，而且不需要對(duì)整張數(shù)據(jù)表建立哈希索引。其有一個(gè)要求，即對(duì)這個(gè)頁的連續(xù)訪問模式必須一樣的，也就是說其查詢的條件必須完全一樣，而且必須是連續(xù)的。

鎖信息（lock info）

我們都知道，InnoDB 存儲(chǔ)引擎會(huì)在行級(jí)別上對(duì)表數(shù)據(jù)進(jìn)行上鎖，不過 InnoDB 打開一張表，就增加一個(gè)對(duì)應(yīng)的對(duì)象到數(shù)據(jù)字典。

數(shù)據(jù)字典

對(duì)數(shù)據(jù)庫中的數(shù)據(jù)、庫對(duì)象、表對(duì)象等的元信息的集合。在 MySQL 中，數(shù)據(jù)字典信息內(nèi)容就包括表結(jié)構(gòu)、數(shù)據(jù)庫名或表名、字段的數(shù)據(jù)類型、視圖、索引、表字段信息、存儲(chǔ)過程、觸發(fā)器等內(nèi)容，MySQL INFORMATION_SCHEMA 庫提供了對(duì)數(shù)據(jù)局元數(shù)據(jù)、統(tǒng)計(jì)信息、以及有關(guān)MySQL Server的訪問信息（例如：數(shù)據(jù)庫名或表名，字段的數(shù)據(jù)類型和訪問權(quán)限等）。該庫中保存的信息也可以稱為MySQL的數(shù)據(jù)字典。

預(yù)讀機(jī)制

MySQL的預(yù)讀機(jī)制，就是當(dāng)你從磁盤上加載一個(gè)數(shù)據(jù)頁的時(shí)候，他可能會(huì)連帶著把這個(gè)數(shù)據(jù)頁相鄰的其他數(shù)據(jù)頁，也加載到緩存里去！

舉個(gè)例子，假設(shè)現(xiàn)在有兩個(gè)空閑緩存頁，然后在加載一個(gè)數(shù)據(jù)頁的時(shí)候，連帶著把他的一個(gè)相鄰的數(shù)據(jù)頁也加載到緩存里去了，正好每個(gè)數(shù)據(jù)頁放入一個(gè)空閑緩存頁！

哪些情況下會(huì)觸發(fā)MySQL的預(yù)讀機(jī)制？

• 有一個(gè)參數(shù)是innodb_read_ahead_threshold，他的默認(rèn)值是56，意思就是如果順序的訪問了一個(gè)區(qū)里的多個(gè)數(shù)據(jù)頁，訪問的數(shù)據(jù)頁的數(shù)量超過了這個(gè)閾值，此時(shí)就會(huì)觸發(fā)預(yù)讀機(jī)制，把下一個(gè)相鄰區(qū)中的所有數(shù)據(jù)頁都加載到緩存里去。

• 如果Buffer Pool里緩存了一個(gè)區(qū)里的13個(gè)連續(xù)的數(shù)據(jù)頁，而且這些數(shù)據(jù)頁都是比較頻繁會(huì)被訪問的，此時(shí)就會(huì)直接觸發(fā)預(yù)讀機(jī)制，把這個(gè)區(qū)里的其他的數(shù)據(jù)頁都加載到緩存里去這個(gè)機(jī)制是通過參數(shù)innodb_random_read_ahead來控制的，他默認(rèn)是OFF，也就是這個(gè)規(guī)則是關(guān)閉的。

所以默認(rèn)情況下，主要是第一個(gè)規(guī)則可能會(huì)觸發(fā)預(yù)讀機(jī)制，一下子把很多相鄰區(qū)里的數(shù)據(jù)頁加載到緩存里去。

預(yù)讀機(jī)制的好處為了提升性能。假設(shè)你讀取了數(shù)據(jù)頁01到緩存頁里去，那么接下來有可能會(huì)接著順序讀取數(shù)據(jù)頁01相鄰的數(shù)據(jù)頁02到緩存頁里去，這個(gè)時(shí)候，是不是可能在讀取數(shù)據(jù)頁02的時(shí)候要再次發(fā)起一次磁盤IO？

所以為了優(yōu)化性能，MySQL才設(shè)計(jì)了預(yù)讀機(jī)制，也就是說如果在一個(gè)區(qū)內(nèi)，你順序讀取了好多數(shù)據(jù)頁了，比如數(shù)據(jù)頁01到數(shù)據(jù)頁56都被你依次順序讀取了，MySQL會(huì)判斷，你可能接著會(huì)繼續(xù)順序讀取后面的數(shù)據(jù)頁。那么此時(shí)就提前把后續(xù)的一大堆數(shù)據(jù)頁（比如數(shù)據(jù)頁57到數(shù)據(jù)頁72）都讀取到Buffer Pool里去。

緩沖池內(nèi)存管理

這里需要了解三個(gè)鏈表（Free List、Flush List、LRU List），

• Free List磁盤上的數(shù)據(jù)頁和緩存頁是一 一對(duì)應(yīng)起來的，都是16KB，一個(gè)數(shù)據(jù)頁對(duì)應(yīng)一個(gè)緩存頁。數(shù)據(jù)庫會(huì)為Buffer Pool設(shè)計(jì)一個(gè)free鏈表，他是一個(gè)雙向鏈表數(shù)據(jù)結(jié)構(gòu)，這個(gè)free鏈表里，每個(gè)節(jié)點(diǎn)就是一個(gè)空閑的緩存頁的描述數(shù)據(jù)塊的地址，也就是說，只要你一個(gè)緩存頁是空閑的，那么他的描述數(shù)據(jù)塊就會(huì)被放入這個(gè)free鏈表中。剛開始數(shù)據(jù)庫啟動(dòng)的時(shí)候，可能所有的緩存頁都是空閑的，因?yàn)榇藭r(shí)可能是一個(gè)空的數(shù)據(jù)庫，一條數(shù)據(jù)都沒有，所以此時(shí)所有緩存頁的描述數(shù)據(jù)塊，都會(huì)被放入這個(gè)free鏈表中，除此之外，這個(gè)free鏈表有一個(gè)基礎(chǔ)節(jié)點(diǎn)，他會(huì)引用鏈表的頭節(jié)點(diǎn)和尾節(jié)點(diǎn)，里面還存儲(chǔ)了鏈表中有多少個(gè)描述數(shù)據(jù)塊的節(jié)點(diǎn)，也就是有多少個(gè)空閑的緩存頁。

• Flush List和 Free List 鏈表類似，flush鏈表本質(zhì)也是通過緩存頁的描述數(shù)據(jù)塊中的兩個(gè)指針，讓被修改過的緩存頁的描述數(shù)據(jù)塊，組成一個(gè)雙向鏈表。凡是被修改過的緩存頁，都會(huì)把他的描述數(shù)據(jù)塊加入到flush鏈表中去，flush的意思就是這些都是臟頁，后續(xù)都是要flush刷新到磁盤上去。

• LRU List由于緩沖池大小是一定的，換句話說 free 鏈表中的空閑緩存頁數(shù)據(jù)是一定的，當(dāng)你不停的把磁盤上的數(shù)據(jù)頁加載到空閑緩存頁里去，free 鏈表中不停的移除空閑緩存頁，遲早有那么一瞬間，free 鏈表中已經(jīng)沒有空閑緩存頁，這時(shí)候就需要淘汰掉一些緩存頁，那淘汰誰呢？這就需要利用緩存命中率了，緩存命中多的就是常用的，那不常用的就可以淘汰了。所以引入 LRU 鏈表來判斷哪些緩存頁是不常用的。

那LRU鏈表的淘汰策略是什么樣的呢？

假設(shè)我們從磁盤加載一個(gè)數(shù)據(jù)頁到緩存頁的時(shí)候，就把這個(gè)緩存頁的描述數(shù)據(jù)塊放到 LRU 鏈表頭部去，那么只要有數(shù)據(jù)的緩存頁，他都會(huì)在 LRU 里了，而且最近被加載數(shù)據(jù)的緩存頁，都會(huì)放到LRU鏈表的頭部去，然后加入某個(gè)緩存頁在尾部，只要發(fā)生查詢，就把它移到頭部，那么最后尾部就是需要淘汰了。

但是這樣真的就可以嗎？

第一種情況預(yù)讀機(jī)制破壞

由于預(yù)讀機(jī)制會(huì)把相鄰的沒有被訪問到的數(shù)據(jù)頁加載到緩存里，實(shí)際上只有一個(gè)緩存頁是被訪問了，另外一個(gè)通過預(yù)讀機(jī)制加載的緩存頁，其實(shí)并沒有人訪問，此時(shí)這兩個(gè)緩存頁可都在LRU鏈表的前面，如下圖

這個(gè)時(shí)候，假如沒有空閑緩存頁了，那么此時(shí)要加載新的數(shù)據(jù)頁了，是不是就要從LRU鏈表的尾部把所謂的“最近最少使用的一個(gè)緩存頁”給拿出來，刷入磁盤，然后騰出來一個(gè)空閑緩存頁了。這樣顯然是很不合理的。

第二種情況可能導(dǎo)致頻繁被訪問的緩存頁被淘汰的場(chǎng)景

全表掃描導(dǎo)致他直接一下子把這個(gè)表里所有的數(shù)據(jù)頁，都從磁盤加載到Buffer Pool里去。這個(gè)時(shí)候可能會(huì)一下子就把這個(gè)表的所有數(shù)據(jù)頁都一一裝入各個(gè)緩存頁里去！此時(shí)可能LRU鏈表中排在前面的一大串緩存頁，都是全表掃描加載進(jìn)來的緩存頁！那么如果這次全表掃描過后，后續(xù)幾乎沒用到這個(gè)表里的數(shù)據(jù)呢？此時(shí)LRU鏈表的尾部，可能全部都是之前一直被頻繁訪問的那些緩存頁！然后當(dāng)你要淘汰掉一些緩存頁騰出空間的時(shí)候，就會(huì)把LRU鏈表尾部一直被頻繁訪問的緩存頁給淘汰掉了，而留下了之前全表掃描加載進(jìn)來的大量的不經(jīng)常訪問的緩存頁！

優(yōu)化LRU算法：基于冷熱數(shù)據(jù)分離的思想設(shè)計(jì)LRU鏈表

MySQL在設(shè)計(jì)LRU鏈表的時(shí)候，采取的實(shí)際上是冷熱數(shù)據(jù)分離的思想。LRU鏈表，會(huì)被拆分為兩個(gè)部分，一部分是熱數(shù)據(jù)，一部分是冷數(shù)據(jù)，這個(gè)冷熱數(shù)據(jù)的比例是由 innodb_old_blocks_pct 參數(shù)控制的，他默認(rèn)是37，也就是說冷數(shù)據(jù)占比37%。數(shù)據(jù)頁第一次被加載到緩存的時(shí)候，實(shí)際上緩存頁會(huì)被放在冷數(shù)據(jù)區(qū)域的鏈表頭部。

然后MySQL設(shè)定了一個(gè)規(guī)則，他設(shè)計(jì)了一個(gè) innodb_old_blocks_time 參數(shù)，默認(rèn)值1000，也就是1000毫秒也就是說，必須是一個(gè)數(shù)據(jù)頁被加載到緩存頁之后，在1s之后，你訪問這個(gè)緩存頁，它會(huì)被挪動(dòng)到熱數(shù)據(jù)區(qū)域的鏈表頭部去。因?yàn)榧僭O(shè)你加載了一個(gè)數(shù)據(jù)頁到緩存去，然后過了1s之后你還訪問了這個(gè)緩存頁，說明你后續(xù)很可能會(huì)經(jīng)常要訪問它，這個(gè)時(shí)間限制就是1s，因此只有1s后你訪問了這個(gè)緩存頁，他才會(huì)給你把緩存頁放到熱數(shù)據(jù)區(qū)域的鏈表頭部去。

這樣的話預(yù)讀和全表掃描的數(shù)據(jù)都只會(huì)在冷數(shù)據(jù)頭部，不會(huì)一開始就進(jìn)去熱數(shù)據(jù)區(qū)。

LRU算法極致優(yōu)化

LRU鏈表的熱數(shù)據(jù)區(qū)域的訪問規(guī)則優(yōu)化一下，即只有在熱數(shù)據(jù)區(qū)域的后3/4部分的緩存頁被訪問了，才會(huì)給你移動(dòng)到鏈表頭部去。如果你是熱數(shù)據(jù)區(qū)域的前面1/4的緩存頁被訪問，他是不會(huì)移動(dòng)到鏈表頭部去的。

舉個(gè)例子，假設(shè)熱數(shù)據(jù)區(qū)域的鏈表里有100個(gè)緩存頁，那么排在前面的25個(gè)緩存頁，他即使被訪問了，也不會(huì)移動(dòng)到鏈表頭部去的。但是對(duì)于排在后面的75個(gè)緩存頁，他只要被訪問，就會(huì)移動(dòng)到鏈表頭部去。這樣的話，他就可以盡可能的減少鏈表中的節(jié)點(diǎn)移動(dòng)了。

LRU鏈表淘汰緩存頁時(shí)機(jī)

MySQL在執(zhí)行CRUD的時(shí)候，首先就是大量的操作緩存頁以及對(duì)應(yīng)的幾個(gè)鏈表。然后在緩存頁都滿的時(shí)候，必然要想辦法把一些緩存頁給刷入磁盤，然后清空這幾個(gè)緩存頁，接著把需要的數(shù)據(jù)頁加載到緩存頁里去！

我們已經(jīng)知道，他是根據(jù)LRU鏈表去淘汰緩存頁的，那么他到底是什么時(shí)候把LRU鏈表的冷數(shù)據(jù)區(qū)域中的緩存頁刷入磁盤的呢？實(shí)際上他有以下三個(gè)時(shí)機(jī)：

定時(shí)把LRU尾部的部分緩存頁刷入磁盤

• 后臺(tái)線程，運(yùn)行一個(gè)定時(shí)任務(wù)，這個(gè)定時(shí)任務(wù)每隔一段時(shí)間就會(huì)把LRU鏈表的冷數(shù)據(jù)區(qū)域的尾部的一些緩存頁，刷入磁盤里去，清空這幾個(gè)緩存頁，把他們加入回free鏈表去。

把flush鏈表中的一些緩存頁定時(shí)刷入磁盤

如果只是把 LRU 鏈表的冷數(shù)據(jù)區(qū)域的緩存頁刷入磁盤是不夠，因?yàn)殒湵淼臒釘?shù)據(jù)區(qū)域里的很多緩存頁可能也會(huì)被頻繁的修改，難道他們永遠(yuǎn)都不刷入磁盤中了嗎？

所以這個(gè)后臺(tái)線程同時(shí)也會(huì)在MySQL不怎么繁忙的時(shí)候，把flush鏈表中的緩存頁都刷入磁盤中，這樣被你修改過的數(shù)據(jù)，遲早都會(huì)刷入磁盤的！

只要flush鏈表中的一波緩存頁被刷入了磁盤，那么這些緩存頁也會(huì)從flush鏈表和lru鏈表中移除，然后加入到free鏈表中去！

所以整體效果就是不停的加載數(shù)據(jù)到緩存頁里去，不停的查詢和修改緩存數(shù)據(jù)，然后free鏈表中的緩存頁不停的在減少，flush鏈表中的緩存頁不停的在增加，lru鏈表中的緩存頁不停的在增加和移動(dòng)。

另外一邊，你的后臺(tái)線程不停的在把lru鏈表的冷數(shù)據(jù)區(qū)域的緩存頁以及flush鏈表的緩存頁，刷入磁盤中來清空緩存頁，然后flush鏈表和lru鏈表中的緩存頁在減少，free鏈表中的緩存頁在增加。

free鏈表沒有空閑緩存頁

如果所有的free鏈表都被使用了，這個(gè)時(shí)候如果要從磁盤加載數(shù)據(jù)頁到一個(gè)空閑緩存頁中，此時(shí)就會(huì)從LRU鏈表的冷數(shù)據(jù)區(qū)域的尾部找到一個(gè)緩存頁，他一定是最不經(jīng)常使用的緩存頁！然后把他刷入磁盤和清空，然后把數(shù)據(jù)頁加載到這個(gè)騰出來的空閑緩存頁里去！

總結(jié)一下，三個(gè)鏈表的使用情況，Buffer Pool被使用的時(shí)候，實(shí)際上會(huì)頻繁的從磁盤上加載數(shù)據(jù)頁到他的緩存頁里去，然后free鏈表、flush鏈表、lru鏈表都會(huì)同時(shí)被使用，比如數(shù)據(jù)加載到一個(gè)緩存頁，free鏈表里會(huì)移除這個(gè)緩存頁，然后lru鏈表的冷數(shù)據(jù)區(qū)域的頭部會(huì)放入這個(gè)緩存頁。

然后如果你要是修改了一個(gè)緩存頁，那么flush鏈表中會(huì)記錄這個(gè)臟頁，lru鏈表中還可能會(huì)把你從冷數(shù)據(jù)區(qū)域移動(dòng)到熱數(shù)據(jù)區(qū)域的頭部去。

如果你是查詢了一個(gè)緩存頁，那么此時(shí)就會(huì)把這個(gè)緩存頁在lru鏈表中移動(dòng)到熱數(shù)據(jù)區(qū)域去，或者在熱數(shù)據(jù)區(qū)域中也有可能會(huì)移動(dòng)到頭部去。

Redo log Buffer 重做日志緩沖

InnoDB 有 buffer pool（簡(jiǎn)稱bp）。bp 是數(shù)據(jù)庫頁面的緩存，對(duì) InnoDB 的任何修改操作都會(huì)首先在bp的page上進(jìn)行，然后這樣的頁面將被標(biāo)記為 dirty(臟頁) 并被放到專門的 flush list 上，后續(xù)將由 master thread 或?qū)ｉT的刷臟線程階段性的將這些頁面寫入磁盤（disk or ssd）。

這樣的好處是避免每次寫操作都操作磁盤導(dǎo)致大量的隨機(jī)IO，階段性的刷臟可以將多次對(duì)頁面的修改 merge 成一次IO操作，同時(shí)異步寫入也降低了訪問的時(shí)延。然而，如果在 dirty page 還未刷入磁盤時(shí)，server非正常關(guān)閉，這些修改操作將會(huì)丟失，如果寫入操作正在進(jìn)行，甚至?xí)捎趽p壞數(shù)據(jù)文件導(dǎo)致數(shù)據(jù)庫不可用。

為了避免上述問題的發(fā)生，Innodb將所有對(duì)頁面的修改操作寫入一個(gè)專門的文件，并在數(shù)據(jù)庫啟動(dòng)時(shí)從此文件進(jìn)行恢復(fù)操作，這個(gè)文件就是redo log file。這樣的技術(shù)推遲了bp頁面的刷新，從而提升了數(shù)據(jù)庫的吞吐，有效的降低了訪問時(shí)延。

帶來的問題是額外的寫redo log操作的開銷（順序IO，當(dāng)然很快），以及數(shù)據(jù)庫啟動(dòng)時(shí)恢復(fù)操作所需的時(shí)間。

redo日志由兩部分構(gòu)成：redo log buffer、redo log file（在磁盤文件那部分介紹）。innodb 是支持事務(wù)的存儲(chǔ)引擎，在事務(wù)提交時(shí)，必須先將該事務(wù)的所有日志寫入到 redo 日志文件中，待事務(wù)的 commit 操作完成才算整個(gè)事務(wù)操作完成。在每次將redo log buffer寫入redo log file后，都需要調(diào)用一次fsync操作，因?yàn)橹刈鋈罩揪彌_只是把內(nèi)容先寫入操作系統(tǒng)的緩沖系統(tǒng)中，并沒有確保直接寫入到磁盤上，所以必須進(jìn)行一次fsync操作。因此，磁盤的性能在一定程度上也決定了事務(wù)提交的性能（具體后面 redo log 落盤機(jī)制介紹）。

InnoDB 存儲(chǔ)引擎會(huì)首先將重做日志信息先放入重做日志緩沖中，然后在按照一定頻率將其刷新到重做日志文件，重做日志緩沖一般不需要設(shè)置的很大，因?yàn)橐话闱闆r每一秒鐘都會(huì)將重做日志緩沖刷新到日志文件中，可通過配置參數(shù) Innodb_log_buffer_size 控制，默認(rèn)為8MB。

Double Write 雙寫

如果說 Insert Buffer 給 InnoDB 存儲(chǔ)引擎帶來了性能上的提升，那么 Double wtite 帶給 InnoDB 存儲(chǔ)引擎的是數(shù)據(jù)頁的可靠性。

InnoDB 的 Page Size 一般是16KB，其數(shù)據(jù)校驗(yàn)也是針對(duì)這16KB來計(jì)算的，將數(shù)據(jù)寫入到磁盤是以 Page 為單位進(jìn)行操作的。我們知道，由于文件系統(tǒng)對(duì)一次大數(shù)據(jù)頁（例如InnoDB的16KB）大多數(shù)情況下不是原子操作，這意味著如果服務(wù)器宕機(jī)了，可能只做了部分寫入。16K的數(shù)據(jù)，寫入4K時(shí)，發(fā)生了系統(tǒng)斷電 os crash ，只有一部分寫是成功的，這種情況下就是 partial page write 問題。

有經(jīng)驗(yàn)的DBA可能會(huì)想到，如果發(fā)生寫失效，MySQL可以根據(jù)redo log進(jìn)行恢復(fù)。這是一個(gè)辦法，但是必須清楚地認(rèn)識(shí)到，redo log中記錄的是對(duì)頁的物理修改，如偏移量800，寫’aaaa’記錄。如果這個(gè)頁本身已經(jīng)發(fā)生了損壞，再對(duì)其進(jìn)行重做是沒有意義的。MySQL在恢復(fù)的過程中檢查page的checksum，checksum就是檢查page的最后事務(wù)號(hào)，發(fā)生partial page write問題時(shí)，page已經(jīng)損壞，找不到該page中的事務(wù)號(hào)。在InnoDB看來，這樣的數(shù)據(jù)頁是無法通過 checksum 驗(yàn)證的，就無法恢復(fù)。即時(shí)我們強(qiáng)制讓其通過驗(yàn)證，也無法從崩潰中恢復(fù)，因?yàn)楫?dāng)前InnoDB存在的一些日志類型，有些是邏輯操作，并不能做到冪等。

為了解決這個(gè)問題，InnoDB實(shí)現(xiàn)了double write buffer，簡(jiǎn)單來說，就是在寫數(shù)據(jù)頁之前，先把這個(gè)數(shù)據(jù)頁寫到一塊獨(dú)立的物理文件位置（ibdata），然后再寫到數(shù)據(jù)頁。這樣在宕機(jī)重啟時(shí)，如果出現(xiàn)數(shù)據(jù)頁損壞，那么在應(yīng)用redo log之前，需要通過該頁的副本來還原該頁，然后再進(jìn)行redo log重做，這就是double write。double write技術(shù)帶給innodb存儲(chǔ)引擎的是數(shù)據(jù)頁的可靠性，下面對(duì)doublewrite技術(shù)進(jìn)行解析

如上圖所示，Double Write 由兩部分組成，一部分是內(nèi)存中的 double write buffer，大小為2MB，另一部分是物理磁盤上共享表空間連續(xù)的128個(gè)頁，大小也為2MB。在對(duì)緩沖池的臟頁進(jìn)行刷新時(shí)，并不直接寫磁盤，而是通過 memcpy 函數(shù)將臟頁先復(fù)制到內(nèi)存中的該區(qū)域，之后通過 double write buffer 再分兩次，每次1MB順序地寫入共享表空間的物理磁盤上，然后馬上調(diào)用 fsync 函數(shù)，同步磁盤，避免操作系統(tǒng)緩沖寫帶來的問題。在完成double write 頁的寫入后，再將 double wirite buffer 中的頁寫入各個(gè)表空間文件中。

在這個(gè)過程中，doublewrite 是順序?qū)?，開銷并不大，在完成 doublewrite 寫入后，在將 double write buffer寫入各表空間文件，這時(shí)是離散寫入。

如果操作系統(tǒng)在將頁寫入磁盤的過程中發(fā)生了崩潰，在恢復(fù)過程中，InnoDB 存儲(chǔ)引擎可以從共享表空間中的double write 中找到該頁的一個(gè)副本，將其復(fù)制到表空間文件中，再應(yīng)用重做日志。

InnoDB 存儲(chǔ)引擎第二部分：后臺(tái)線程

IO 線程

在 InnoDB 中使用了大量的 AIO（Async IO） 來做讀寫處理，這樣可以極大提高數(shù)據(jù)庫的性能。在 InnoDB 1.0 版本之前共有4個(gè) IO Thread，分別是 write，read，insert buffer和log thread，后來版本將 read thread和 write thread 分別增大到了4個(gè)，一共有10個(gè)了。

• – read thread ： 負(fù)責(zé)讀取操作，將數(shù)據(jù)從磁盤加載到緩存page頁。4個(gè)

• – write thread：負(fù)責(zé)寫操作，將緩存臟頁刷新到磁盤。4個(gè)

• – log thread：負(fù)責(zé)將日志緩沖區(qū)內(nèi)容刷新到磁盤。1個(gè)

• – insert buffer thread ：負(fù)責(zé)將寫緩沖內(nèi)容刷新到磁盤。1個(gè)

Purge 線程

事務(wù)提交之后，其使用的 undo 日志將不再需要，因此需要 Purge Thread 回收已經(jīng)分配的 undo 頁。show variables like '%innodb*purge*threads%';

Page Cleaner 線程

作用是將臟數(shù)據(jù)刷新到磁盤，臟數(shù)據(jù)刷盤后相應(yīng)的 redo log 也就可以覆蓋，即可以同步數(shù)據(jù)，又能達(dá)到 redo log 循環(huán)使用的目的。會(huì)調(diào)用write thread線程處理。show variables like '%innodb*page*cleaners%';

InnoDB 存儲(chǔ)引擎第三部分：磁盤文件

InnoDB 的主要的磁盤文件主要分為三大塊：一是系統(tǒng)表空間，二是用戶表空間，三是 redo 日志文件和歸檔文件。

二進(jìn)制文件（binlong）等文件是 MySQL Server 層維護(hù)的文件，所以未列入 InnoDB 的磁盤文件中。

系統(tǒng)表空間和用戶表空間

系統(tǒng)表空間包含 InnoDB 數(shù)據(jù)字典（元數(shù)據(jù)以及相關(guān)對(duì)象）并且 double write buffer , change buffer , undo logs 的存儲(chǔ)區(qū)域。

系統(tǒng)表空間也默認(rèn)包含任何用戶在系統(tǒng)表空間創(chuàng)建的表數(shù)據(jù)和索引數(shù)據(jù)。

系統(tǒng)表空間是一個(gè)共享的表空間，因?yàn)樗潜欢鄠€(gè)表共享的。

系統(tǒng)表空間是由一個(gè)或者多個(gè)數(shù)據(jù)文件組成。默認(rèn)情況下,1個(gè)初始大小為10MB，名為 ibdata1 的系統(tǒng)數(shù)據(jù)文件在MySQL的data目錄下被創(chuàng)建。用戶可以使用 innodb_data_file_path 對(duì)數(shù)據(jù)文件的大小和數(shù)量進(jìn)行配置。

innodb_data_file_path 的格式如下：

innodb_data_file_path=datafile1[,datafile2]...

用戶可以通過多個(gè)文件組成一個(gè)表空間，同時(shí)制定文件的屬性:

innodb_data_file_path = /db/ibdata1:1000M;/dr2/db/ibdata2:1000M:autoextend

這里將 /db/ibdata1 和 /dr2/db/ibdata2 兩個(gè)文件組成系統(tǒng)表空間。如果這兩個(gè)文件位于不同的磁盤上，磁盤的負(fù)載可能被平均，因此可以提高數(shù)據(jù)庫的整體性能。兩個(gè)文件的文件名之后都跟了屬性，表示文件 ibdata1 的大小為1000MB，文件 ibdata2 的大小為1000MB，而且用完空間之后可以自動(dòng)增長(zhǎng)。

設(shè)置 innodb_data_file_path 參數(shù)之后，所有基于 InnoDB 存儲(chǔ)引擎的表的數(shù)據(jù)都會(huì)記錄到該系統(tǒng)表空間中，如果設(shè)置了參數(shù) innodb_file_per_table ，則用戶可以將每個(gè)基于 InnoDB 存儲(chǔ)引擎的表產(chǎn)生一個(gè)獨(dú)立的用戶空間。

用戶表空間的命名規(guī)則為：表名.ibd。通過這種方式，用戶不用將所有數(shù)據(jù)都存放于默認(rèn)的系統(tǒng)表空間中，但是用戶表空間只存儲(chǔ)該表的數(shù)據(jù)、索引和插入緩沖BITMAP等信息，其余信息還是存放在默認(rèn)的系統(tǒng)表空間中。

下圖顯示 InnoDB 存儲(chǔ)引擎對(duì)于文件的存儲(chǔ)方式，其中frm文件是表結(jié)構(gòu)定義文件，記錄每個(gè)表的表結(jié)構(gòu)定義。

重做日志文件（redo log file）和歸檔文件

默認(rèn)情況下，在 InnoDB 存儲(chǔ)引擎的數(shù)據(jù)目錄下會(huì)有兩個(gè)名為 ib_logfile0 和 ib_logfile1 的文件，這就是 InnoDB 的重做文件（redo log file），它記錄了對(duì)于 InnoDB 存儲(chǔ)引擎的事務(wù)日志。

當(dāng) InnoDB 的數(shù)據(jù)存儲(chǔ)文件發(fā)生錯(cuò)誤時(shí)，重做日志文件就能派上用場(chǎng)。InnoDB 存儲(chǔ)引擎可以使用重做日志文件將數(shù)據(jù)恢復(fù)為正確狀態(tài)，以此來保證數(shù)據(jù)的正確性和完整性。

每個(gè) InnoDB 存儲(chǔ)引擎至少有1個(gè)重做日志文件，每個(gè)文件組下至少有2個(gè)重做日志文件，加默認(rèn)的 ib_logfile0 和 ib_logfile1。

為了得到更高的可靠性，用戶可以設(shè)置多個(gè)鏡像日志組，將不同的文件組放在不同的磁盤上，以此來提高重做日志的高可用性。

在日志組中每個(gè)重做日志文件的大小一致，并以【循環(huán)寫入】的方式運(yùn)行。InnoDB 存儲(chǔ)引擎先寫入重做日志文件1，當(dāng)文件被寫滿時(shí)，會(huì)切換到重做日志文件2，再當(dāng)重做日志文件2也被寫滿時(shí)，再切換到重做日志1。

用戶可以使用 Innodb_log_file_size 來設(shè)置重做日志文件的大小 ，這對(duì) InnoDB 存儲(chǔ)引擎的性能有著非常大的影響。

如果重做日志文件設(shè)置的太大，數(shù)據(jù)丟失時(shí)，恢復(fù)時(shí)可能需要很長(zhǎng)的時(shí)間；另一個(gè)方面，如果設(shè)置的太小，重做日志文件太小會(huì)導(dǎo)致依據(jù) checkpoint 的檢查需要頻繁刷新臟頁到磁盤中，導(dǎo)致性能的抖動(dòng)。

重做日志的落盤機(jī)制

InnoDB 對(duì)于數(shù)據(jù)文件和日志文件的刷盤遵守WAL（write ahead redo log）和 Force-log-at-commit 兩種規(guī)則，二者保證了事務(wù)的持久性。WAL 要求數(shù)據(jù)的變更寫入到磁盤前，首先必須將內(nèi)存中的日志寫入到磁盤；Force-log-at-commit 要求當(dāng)一個(gè)事務(wù)提交時(shí)，所有產(chǎn)生的日志都必須刷新到磁盤上，如果日志刷新成功后，緩沖池中的數(shù)據(jù)刷新到磁盤前數(shù)據(jù)庫發(fā)生了宕機(jī)，那么重啟時(shí)，數(shù)據(jù)庫可以從日志中恢復(fù)數(shù)據(jù)。

如上圖所示，InnoDB 在緩沖池中變更數(shù)據(jù)時(shí)，會(huì)首先將相關(guān)變更寫入重做日志緩沖中，然后再按時(shí)（比如每秒刷新機(jī)制）或者當(dāng)事務(wù)提交時(shí)寫入磁盤，這符合 Force-log-at-commit 原則；當(dāng)重做日志寫入磁盤后，緩沖池中的變更數(shù)據(jù)才會(huì)依據(jù) checkpoint 機(jī)制寫入到磁盤中，這符合 WAL 原則。

在 checkpoint 擇時(shí)機(jī)制中，就有重做日志文件寫滿的判斷，所以，如前文所述，如果重做日志文件太小，經(jīng)常被寫滿，就會(huì)頻繁導(dǎo)致 checkpoint 將更改的數(shù)據(jù)寫入磁盤，導(dǎo)致性能抖動(dòng)。

操作系統(tǒng)的文件系統(tǒng)是帶有緩存的，當(dāng) InnoDB 向磁盤寫入數(shù)據(jù)時(shí)，有可能只是寫入到了文件系統(tǒng)的緩存中，沒有真正的“落袋為安”。

InnoDB 的 innodb_flush_log_at_trx_commit 屬性可以控制每次事務(wù)提交時(shí) InnoDB 的行為。當(dāng)屬性值為0時(shí)，事務(wù)提交時(shí)，不會(huì)對(duì)重做日志進(jìn)行寫入操作，而是等待主線程按時(shí)寫入；當(dāng)屬性值為1時(shí)，事務(wù)提交時(shí)，會(huì)將重做日志寫入文件系統(tǒng)緩存，并且調(diào)用文件系統(tǒng)的 fsync ，將文件系統(tǒng)緩沖中的數(shù)據(jù)真正寫入磁盤存儲(chǔ)，確保不會(huì)出現(xiàn)數(shù)據(jù)丟失；當(dāng)屬性值為2時(shí)，事務(wù)提交時(shí)，也會(huì)將日志文件寫入文件系統(tǒng)緩存，但是不會(huì)調(diào)用fsync，而是讓文件系統(tǒng)自己去判斷何時(shí)將緩存寫入磁盤。

日志的刷盤機(jī)制如下圖所示：

Innodb_flush_log_at_commit 是 InnoDB 性能調(diào)優(yōu)的一個(gè)基礎(chǔ)參數(shù)，涉及 InnoDB 的寫入效率和數(shù)據(jù)安全。當(dāng)參數(shù)數(shù)值為0時(shí)，寫入效率最高，但是數(shù)據(jù)安全最低；參數(shù)值為1時(shí)，寫入效率最低，但是數(shù)據(jù)安全最高；參數(shù)值為2時(shí)，二者都是中等水平，一般建議將屬性值設(shè)置為1，以獲得較高的安全性，而且也只有設(shè)置為1，才能保證事務(wù)的持久性。

用一條 UPDATE 語句再來了解 InnoDB 存儲(chǔ)引擎

有了上面 InnoDB 存儲(chǔ)引擎的架構(gòu)基礎(chǔ)介紹，我們?cè)賮矸治鲆幌乱淮?UPDATE 數(shù)據(jù)更新具體流程。

我們把這張圖分為上下兩部分來看，上面那部分是 MySQL Server 層處理流程，下面那部分是 MySQL InnoDB存儲(chǔ)引擎處理流程。

MySQL Server 層處理流程

這部分處理流程無關(guān)于哪個(gè)存儲(chǔ)引擎，它是 Server 層處理的，具體步驟如下：

• 用戶各種操作觸發(fā)后臺(tái)sql執(zhí)行，通過web項(xiàng)目中自帶的數(shù)據(jù)庫連接池：如 dbcp、c3p0、druid 等，與數(shù)據(jù)庫服務(wù)器的數(shù)據(jù)庫連接池建立網(wǎng)絡(luò)連接；

• 數(shù)據(jù)庫連接池中的線程監(jiān)聽到請(qǐng)求后，將接收到的sql語句通過SQL接口響應(yīng)給查詢解析器，查詢解析器將sql按照sql的語法解析出查詢哪個(gè)表的哪些字段，查詢條件是啥；

• 再通過查詢優(yōu)化器處理，選擇該sq最優(yōu)的一套執(zhí)行計(jì)劃；

• 然后執(zhí)行器負(fù)責(zé)調(diào)用存儲(chǔ)引擎的一系列接口，執(zhí)行該計(jì)劃而完成整個(gè)sql語句的執(zhí)行

這部分流程和上面分析的 一次 Select 請(qǐng)求處理流程分析的基本一致。

InnoDB 存儲(chǔ)引擎處理流程

具體執(zhí)?語句得要存儲(chǔ)引擎來完成，如上圖所示：

• 更新users表中id=10的這條數(shù)據(jù)，如果緩沖池中沒有該條數(shù)據(jù)的，得要先從磁盤中將被更新數(shù)據(jù)的原始數(shù)據(jù)加載到緩沖池中。

• 同時(shí)為了保證并發(fā)更新數(shù)據(jù)安全問題，會(huì)對(duì)這條數(shù)據(jù)先加鎖，防?其他事務(wù)進(jìn)?更新。

• 接著將更新前的值先備份寫?到undo log中（便于事務(wù)回滾時(shí)取舊數(shù)據(jù)），?如 update 語句即存儲(chǔ)被更新字段之前的值。

• 更新 buffer pool 中的緩存數(shù)據(jù)為最新的數(shù)據(jù)，那么此時(shí)內(nèi)存中的數(shù)據(jù)為臟數(shù)據(jù)（內(nèi)存中數(shù)據(jù)和磁盤中數(shù)據(jù)不一致）

?此就完成了在緩沖池中的執(zhí)?流程（如上圖）。

緩沖池中更新完數(shù)據(jù)后，需要將本次的更新信息順序?qū)懙?Redo Log ?志，因?yàn)楝F(xiàn)在已經(jīng)把內(nèi)存里的數(shù)據(jù)進(jìn)行了修改，但是磁盤上的數(shù)據(jù)還沒修改，此時(shí)萬一 MySQL所在的機(jī)器宕機(jī)了，必然會(huì)導(dǎo)致內(nèi)存里修改過的數(shù)據(jù)丟失，redo 日志就是記錄下來你對(duì)數(shù)據(jù)做了什么修改，比如對(duì)“id=10這行記錄修改了name字段的值為xxx”，這就是一個(gè)日志，用來在MySQL突然宕機(jī)的時(shí)候，用來恢復(fù)你更新過的數(shù)據(jù)的。不過注意的是此時(shí) Redo Log 還沒有落盤到日志文件。

這個(gè)時(shí)候思考一個(gè)問題：如果還沒提交事務(wù)，MySQL宕機(jī)了怎么辦？

上面我們知道到目前我們修改了內(nèi)存數(shù)據(jù)，然后記錄了 Redo Log Buffer 日志緩沖，如果這個(gè)時(shí)候 MySQL 奔潰，內(nèi)存數(shù)據(jù)和 Redo Log Buffer 數(shù)據(jù)都會(huì)丟失，但是此時(shí)數(shù)據(jù)丟失并不要緊，因?yàn)橐粭l更新語句，沒提交事務(wù)，就代表他沒執(zhí)行成功，此時(shí)MySQL宕機(jī)雖然導(dǎo)致內(nèi)存里的數(shù)據(jù)都丟失了，但是你會(huì)發(fā)現(xiàn)，磁盤上的數(shù)據(jù)依然還停留在原樣子。

接下來要提交事物了，此時(shí)就會(huì)根據(jù)一定的策略把redo日志從redo log buffer里刷入到磁盤文件里去，此時(shí)這個(gè)策略是通過 innodb_flush_log_at_trx_commit 來配置的。

innodb_flush_log_at_trx_commit=0，表示提交事物不會(huì)把redo log buffer里的數(shù)據(jù)刷入磁盤文件的，此時(shí)可能你都提交事務(wù)了，結(jié)果mysql宕機(jī)了，然后此時(shí)內(nèi)存里的數(shù)據(jù)全部丟失，所以這種方式不可取。

innodb_flush_log_at_trx_commit=1，redo log從內(nèi)存刷入到磁盤文件里去，只要事務(wù)提交成功，那么redo log就必然在磁盤里了，所以如果這個(gè)時(shí)候MySQL奔潰，可以根據(jù)Redo Log日志恢復(fù)數(shù)據(jù)。

innodb_flush_log_at_trx_commit=2，提交事務(wù)的時(shí)候，把redo日志寫入磁盤文件對(duì)應(yīng)的os cache緩存里去，而不是直接進(jìn)入磁盤文件，可能1秒后才會(huì)把os cache里的數(shù)據(jù)寫入到磁盤文件里去。

提交事務(wù)的時(shí)候，同時(shí)會(huì)寫入binlog，binlog也有不同的刷盤策略，有一個(gè)sync_binlog參數(shù)可以控制binlog的刷盤策略，他的默認(rèn)值是0，此時(shí)你把binlog寫入磁盤的時(shí)候，其實(shí)不是直接進(jìn)入磁盤文件，而是進(jìn)入os cache內(nèi)存緩存。?般我們?yōu)榱吮ＷC數(shù)據(jù)不丟失會(huì)配置雙1策略，Redo Log 和 Binlog落盤策略都選擇1。

Binlog 落盤后，再將Binlog的?件名、?件所在路徑信息以及commit標(biāo)記給同步順序?qū)懙絉edo log中，這一步的意義是用來保持 redo log 日志與 binlog 日志一致的。commit標(biāo)記是判定事務(wù)是否成功提交的?個(gè)?較重要的標(biāo)準(zhǔn)，舉個(gè)例子，如果如果第5步或者第6步執(zhí)行成功后MySQL就奔潰了，這個(gè)時(shí)候因?yàn)闆]有最終的事務(wù)commit標(biāo)記在redo日志里，所以此次事務(wù)可以判定為不成功。不會(huì)說redo日志文件里有這次更新的日志，但是binlog日志文件里沒有這次更新的日志，不會(huì)出現(xiàn)數(shù)據(jù)不一致的問題。

做完前面后，內(nèi)存數(shù)據(jù)已經(jīng)修改，事物已經(jīng)提交，日志已經(jīng)落盤，但是磁盤數(shù)據(jù)還沒有同步修改。InnoDB存儲(chǔ)引擎后臺(tái)有?個(gè)IO線程，會(huì)在數(shù)據(jù)庫壓?的低峰期間，將緩沖池中被事務(wù)更新、但還沒來得及寫到磁盤中的數(shù)據(jù)（臟數(shù)據(jù)，因?yàn)榇疟P數(shù)據(jù)和內(nèi)存數(shù)據(jù)已經(jīng)不?致了）給刷到磁盤中，完成事務(wù)的持久化。

所以 InnoDB 處理寫入過程可以用下面這幅圖表示

推薦學(xué)習(xí)：mysql視頻教程