mysql體系結(jié)構(gòu)和InnoDB存儲引擎知識有哪些

這篇文章主要介紹“mysql體系結(jié)構(gòu)和InnoDB存儲引擎知識有哪些”，在日常操作中，相信很多人在mysql體系結(jié)構(gòu)和InnoDB存儲引擎知識有哪些問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”mysql體系結(jié)構(gòu)和InnoDB存儲引擎知識有哪些”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！

MySQL基本架構(gòu)圖

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

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

連接器

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

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

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

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

在數(shù)據(jù)庫中，長連接是指連接成功后，如果客戶端持續(xù)有請求，則一直使用同一個連接。短連接則是指每次執(zhí)行完很少的幾次查詢就斷開連接，下次查詢再重新建立一個。

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

解決方案：

定期斷開長連接。使用一段時間，或者程序里面判斷執(zhí)行過一個占用內(nèi)存的大查詢后，斷開連接，之后要查詢再重連。

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

查詢緩存

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

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

分析器

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

優(yōu)化器

執(zhí)行器

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

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

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

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

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

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

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

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

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

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

在這里涉及到幾個問題：

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

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

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

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

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

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

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

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

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

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

InnoDB 存儲引擎

InnoDB 架構(gòu)簡析

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

再來一張突出重點的圖：

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

Buffer Pool緩沖池

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

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

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

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

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

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

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

插入緩沖（Insert Buffer）

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

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

然而 Insert Buffer 的使用需要同時滿足以下兩個條件：

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

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

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

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

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

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

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

Change Buffer

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

對一條記錄進行 UPDATE 操作可能分為兩個過程:

• 將記錄標記為已刪除;

• 真正將記錄刪除

因此 Delete Buffer對應(yīng) UPDATE操作的第一個過程,即將記錄標記為刪除。 PurgeBuffer對應(yīng) UPDATE 操作的第二個過程,即將記錄真正的刪除。同時, InnoDB 存儲引擎提供了參數(shù) innodb_change_buffering,用來開啟各種Buffer的選項。該參數(shù)可選的值為: Inserts、 deletes、 purges、 changes、all、none。 Inserts、 deletes、 purges 就是前面討論過的三種情況。 changes 表示啟用 Inserts 和 deletes,all表示啟用所有,none表示都不啟用。該參數(shù)默認值為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 值默認為25,表示最多使用1/4的緩沖池內(nèi)存空間。

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

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

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

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

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

鎖信息（lock info）

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

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

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

預(yù)讀機制

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

但是這樣真的就可以嗎？

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

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

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

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

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

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

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

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

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

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

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

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

LRU鏈表淘汰緩存頁時機

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

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

定時把LRU尾部的部分緩存頁刷入磁盤

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

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

如果只是把 LRU 鏈表的冷數(shù)據(jù)區(qū)域的緩存頁刷入磁盤是不夠，因為鏈表的熱數(shù)據(jù)區(qū)域里的很多緩存頁可能也會被頻繁的修改，難道他們永遠都不刷入磁盤中了嗎？

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

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

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

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

free鏈表沒有空閑緩存頁

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

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

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

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

Redo log Buffer 重做日志緩沖

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

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

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

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

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

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

Double Write 雙寫

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

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

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

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

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

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

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

InnoDB 存儲引擎第二部分：后臺線程

IO 線程

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

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

• - write thread：負責寫操作，將緩存臟頁刷新到磁盤。4個

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

• - insert buffer thread ：負責將寫緩沖內(nèi)容刷新到磁盤。1個

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ù)，又能達到 redo log 循環(huán)使用的目的。會調(diào)用write thread線程處理。show variables like '%innodb*page*cleaners%';

InnoDB 存儲引擎第三部分：磁盤文件

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

二進制文件（binlong）等文件是 MySQL Server 層維護的文件，所以未列入 InnoDB 的磁盤文件中。

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

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

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

系統(tǒng)表空間是一個共享的表空間，因為它是被多個表共享的。

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

innodb_data_file_path 的格式如下：

innodb_data_file_path=datafile1[,datafile2]...

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

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

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

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

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

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

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

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

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

每個 InnoDB 存儲引擎至少有1個重做日志文件，每個文件組下至少有2個重做日志文件，加默認的 ib_logfile0 和 ib_logfile1。

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

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

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

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

重做日志的落盤機制

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

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

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

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

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

日志的刷盤機制如下圖所示：

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

用一條 UPDATE 語句再來了解 InnoDB 存儲引擎

有了上面 InnoDB 存儲引擎的架構(gòu)基礎(chǔ)介紹，我們再來分析一下一次 UPDATE 數(shù)據(jù)更新具體流程。

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

MySQL Server 層處理流程

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

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

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

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

• 然后執(zhí)行器負責調(diào)用存儲引擎的一系列接口，執(zhí)行該計劃而完成整個sql語句的執(zhí)行

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

InnoDB 存儲引擎處理流程

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

到此，關(guān)于“mysql體系結(jié)構(gòu)和InnoDB存儲引擎知識有哪些”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>