讓人心動的mysql體系架構和InnoDB儲存引擎知識詳解

這篇文章為大家帶來了MYSQL進階之體系架構和InnoDB儲存引擎的相關知識，希望對大家有幫助。

MySQL基本架構圖

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

Server 層包括連接器、查詢快取、分析器、最佳化器、執行器等，涵蓋MySQL 的大多數核心服務功能，以及所有的內建函數（如日期、時間、數學和加密函數等），所有跨儲存引擎的功能都在這一層實現，例如預存程序、觸發器、視圖等。

連接器

連接器就是你連接到資料庫時所使用的，負責跟隨客戶端建立連線、取得權限、維持和管理連線。

指令： mysql -h$ip -P$port -u$user -p，回車後輸密碼，也可以在 -p 後面輸入密碼，但有密碼外洩的風險。

show processlist,可以查看連接的情況，Command 欄位中有一個 Sleep 表示連線空閒。

空閒連線預設8小時會被斷開，可以由wait_timeout參數配置。

在資料庫中，長連線是指連線成功後，如果客戶端持續有請求，則一直使用同一個連線。短連接則是指每次執行完很少的幾次查詢就斷開連接，下次查詢再重新建立一個。

由於建立連接比較耗資源，所以建議盡量使用長連接，但是使用長連接後，MySQL 佔用內存漲得特別快，這是因為MySQL 在執行過程中臨時使用的內存是管理在連接對象裡面的。這些資源會在連線中斷的時候才會被釋放。所以如果長連線累積下來，可能導致記憶體佔用太大，被系統強行殺掉（OOM），從現像看就是 MySQL 異常重啟了。

解決方案：

定期斷開長連線。使用一段時間，或程式裡面判斷執行過一個佔用記憶體的大查詢後，斷開連接，之後要查詢再重連。

如果你用的是 MySQL 5.7 或更新版本，可以在每次執行一個比較大的操作後，執行 mysql_reset_connection 來重新初始化連線資源。這個過程不需要重連和重新做權限驗證，但是會將連線恢復到剛建立完時的狀態。

查詢快取

查詢快取是將先前執行過的語句及其結果以 key-value 對的形式快取在記憶體中。 key 是查詢的語句，value 是查詢的結果。如果你的查詢能夠直接在這個快取中找到 key，那麼這個 value 就會直接回傳給客戶端。

查詢快取在MYSQL8時移除了，由於查詢快取失效頻繁，命中率低。

分析器

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

優化器

執行器

儲存引擎層負責資料的儲存和擷取。其架構模式是插件式的，支援 InnoDB、MyISAM、Memory 等多個儲存引擎。現在最常用的儲存引擎是 InnoDB，它從 MySQL 5.5.5 版本開始成為了預設儲存引擎。

一條Select 語句執行流程

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

• 使用者傳送請求到tomcat ，透過tomcat 連結池和mysql 連接池建立連接，然後透過連接發送SQL 語句到MySQL；

• MySQL 有一個單獨的監聽線程，讀取到請求數據，得到連接中請求的SQL語句；

• 將獲取到的SQL資料傳送給SQL介面執行;

• SQL介面將SQL傳送給SQL解析器解析；

• #將解析好的SQL傳送給查詢最佳化器，找到最佳的查詢路勁，然後發給執行器；

• 執行器根據最佳化後的執行方案呼叫儲存引擎的介面按照一定的順序和步驟進行執行。

• 舉個例子，例如執行器可能會先調用儲存引擎的一個接口，去獲取「users」表中的第一行數據，然後判斷一下這個數據的「id」字段的值是否等於我們期望的一個值，如果不是的話，那就繼續呼叫儲存引擎的接口，去取得「users」表的下一行資料。就是基於上述的思路，執行器就會去根據我們的優化器產生的一套執行計劃，然後不停的調用存儲引擎的各種接口去完成SQL 語句的執行計劃，大致就是不停的更新或者提取一些數據出來。

在這裡牽涉到幾個問題：

MySQL驅動到底是什麼東西？

以java為例，我們如果要在Java系統中去存取一個MySQL資料庫，必須得在系統的依賴中加入一個MySQL驅動，例如在Maven裡面要加上

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

那這個MySQL驅動到底是個什麼東西?其實L驅動就會在底層跟資料庫建立網路連接，有網路連接，接著才能去傳送請求給資料庫伺服器！讓語言編寫的系統透過MySQL驅動程式去存取資料庫，如下圖

資料庫連接池到底是用來做什麼的？

假設用java開發一個web服務部署在tomcat上，tomcat可以多線程並發處理請求，所以首先一點就是不可能只會創建一個資料庫連接（多個請求去搶一個連接，效率得多低下）。

其次，如果每個請求都去創建一個資料庫連接呢? 這也是非常不好的，因為每次建立一個資料庫連接都很耗時，好不容易建立好了連接，執行完了SQL語句，也把資料庫連接給銷毀，頻繁創建和銷毀帶來效能問題。

所以一般使用資料庫連接池，也就是在一個池子裡維持多個資料庫連接，讓多個執行緒使用裡面的不同的資料庫連接去執行SQL語句，然後執行完SQL語句之後，不要銷毀這個資料庫連接，而是把連接放回池子裡，後續可以繼續使用。基於這樣的一個資料庫連線池的機制，就可以解決多個執行緒並發的使用多個資料庫連線去執行SQL語句的問題，而且也避免了資料庫連線使用完之後就銷毀的問題了。

MySQL資料庫的連線池是用來做什麼的？

MySQL資料庫的連接池的作用和java應用端連接池作用一樣，都是起到了復用連接的作用。

InnoDB 儲存引擎

#InnoDB 架構簡析

從圖中可見，InnoDB 儲存引擎由記憶體池，後台執行緒和磁碟檔案三大部分組成

#再來一張突出重點的圖：

InnoDB 儲存引擎第一部分：記憶體結構

#Buffer Pool緩衝池

InnoDB 儲存引擎基於磁碟存儲的，並將其中的記錄按照頁的方式進行管理，但是由於CPU速度和磁碟速度之間的鴻溝，基於磁碟的資料庫系統通常使用緩衝池記錄來提高資料庫的整體效能。

在資料庫進行讀取操作，將從磁碟中讀到的頁放在緩衝池中，下次再讀取相同的頁中時，首先判斷該頁是否在緩衝池中。若在緩衝池中，稱該頁在緩衝池中被命中，直接讀取該頁，否則讀取磁碟上的頁。

對於資料庫中頁的修改操作，首先修改在緩衝池中的頁，然後再以一定的頻率刷新到磁碟上，頁從緩衝池刷新回磁碟的操作並不是在每次頁發生更新時觸發，而是透過一種稱為CheckPoint 的機制刷新回磁碟。所以，緩衝池的大小直接影響資料庫的整體效能，可以透過配置參數innodb_buffer_pool_size 來設置，緩衝池預設是128MB，還是有點小的，如果你的資料庫是16核心32G的機器，那麼你就可以給Buffer Pool分配個2GB的記憶體。

由於緩衝池不是無限大的，隨著不停的把磁碟上的資料頁載入到緩衝池中，緩衝池總是要用完，這個時候只能淘汰掉一些快取頁，淘汰方式就使用最近最少被使用演算法（LRU），具體來說就是引入一個新的LRU鍊錶，透過這個LRU鍊錶，就可以知道哪些快取頁是最近最少被使用的，那麼當你快取頁需要騰出一個刷入磁碟的時候，可以選擇那個LRU鍊錶中最近最少被使用的快取頁淘汰。

緩衝池中快取的資料頁類型有：索引頁、資料頁、undo頁、插入緩衝、自適應雜湊索引、InnoDB儲存的鎖定資訊和資料字典資訊。

資料頁和索引頁

頁(Page)是Innodb 儲存的最基本結構，也是Innodb 磁碟管理的最小單位，與資料庫相關的所有內容都儲存在Page 結構裡。 Page 分為幾種類型，資料頁和索引頁就是其中最為重要的兩種類型。

插入緩衝（Insert Buffer）

在 InnoDB 引擎上進行插入操作時，一般需要依照主鍵順序插入，這樣才能取得較高的插入效能。當一張表中存在非聚集的不唯一的索引時，在插入時，資料頁的存放還是按照主鍵進行順序存放，但是對於非聚集索引葉子節點的插入不再是順序的了，這時就需要離散的存取非聚集索引頁，由於隨機讀取的存在導致插入操作效能下降。

所以InnoDB 儲存引擎開創性地設計了Insert Buffer ，對於非聚集索引的插入或更新操作，不是每一次直接插入索引頁中，而是先判斷插入的非聚集索引頁是否在緩衝池中，若在，則直接插入；若不在，則先放入到一個Insert Buffer 物件中，好似欺騙。資料庫這個非聚集的索引已經插到葉子節點，而實際上並沒有，只是存放在另一個位置。然後再以一定的頻率和情況進行Insert Buffer 和輔助索引頁子節點的merge（合併）操作，這時通常能將多個插入合併到一個操作中（因為在一個索引頁中），這就大大提高了對於非聚集索引插入的效能。

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

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

索引不是唯一（ unique ）的。

當滿足上述兩個條件時， InnoDB 儲存引擎會使用 Insert Buffer ，這樣就能提高插入操作的效能了。不過考慮這樣一種情況:應用程式進行大量的插入操作，這些都涉及了不唯一的非聚集索引，也就是使用了 Insert Buffer。若此時 MySQL資料庫發生了宕機這時勢必有大量的 Insert Buffer 並沒有合併到實際的非聚集索引中去。

• 因此這時恢復可能需要很長的時間,在極端情況下甚至需要幾個小時。輔助索引不能是唯一的，因為插入緩衝時，資料庫並不會去尋找索引頁來判斷插入的記錄的唯一性。如果去查找肯定又會有離散讀取的情況發生，導致 Insert Buffer 失去了意義。
  可以透過指令SHOW ENGINE INNODB STATUS 來查看插入緩衝的資訊
seg size顯示了目前Insert Buffer的大小為11336×16KB,大約為177MB; free list len代表了空閒清單的長度;size代表了已經合併記錄頁的數量。而黑體部分的第2行可能是使用者真正關心的,因為它顯示了插入性能的提高。 Inserts代表了插入的記錄數;merged recs代表了合併的插入記錄數量; merges代表合併的次數,也就是實際讀取頁的次數。 merges: merged recs大約為1:3,代表了插入緩衝將對於非聚集索引頁的離散IO邏輯請求大約降低了2/3。

正如前面所說的,目前Insert Buffer存在一個問題是:在寫密集的情況下,插入緩衝會佔用過多的緩衝池記憶體( innodb buffer pool),預設最大可以佔用到1/ 2的緩衝池記憶體。以下是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對應UPDATE操作的第一個過程,即將記錄標記為刪除。 PurgeBuffer對應 UPDATE 操作的第二個流程,即將記錄真正的刪除。同時, InnoDB 儲存引擎提供了參數 innodb_change_buffering,用來開啟各種Buffer的選項。此參數可選的值為: Inserts、 deletes、 purges、 changes、all、none。 Inserts、 deletes、 purges 就是前面討論過的三種情況。 changes 表示啟用 Inserts 和 deletes,all表示啟用所有,none表示都不啟用。此參數預設值為all。 ###

从 InnoDB1.2.x版本开始,可以通过参数 innodb_change_buffer_max_size 来控制Change Buffer最大使用内存的数量:

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的缓冲池内存空间。

而需要注意的是,该参数的最大有效值为50在 MySQL5.5版本中通过命令 SHOW ENGINE INNODB STATUS,可以观察到类似如下的内容:

可以看到这里显示了 merged operations和 discarded operation,并且下面具体显示 Change Buffer中每个操作的次数。 Insert 表示 Insert Buffer; delete mark表示 Delete Buffer; delete表示 Purge Buffer; discarded operations表示当 Change Buffer发生 merge时,表已经被删除,此时就无需再将记录合并(merge)到辅助索引中了。

自适应哈希索引

InnoDB 会根据访问的频率和模式，为热点页建立哈希索引，来提高查询效率。InnoDB 存储引擎会监控对表上各个索引页的查询，如果观察到建立哈希索引可以带来速度上的提升，则建立哈希索引，所以叫做自适应哈希索引。

自适应哈希索引通过缓冲池的B+树页构建而来，因此建立速度很快，而且不需要对整张数据表建立哈希索引。其有一个要求，即对这个页的连续访问模式必须一样的，也就是说其查询的条件必须完全一样，而且必须是连续的。

锁信息（lock info）

我们都知道，InnoDB 存储引擎会在行级别上对表数据进行上锁，不过 InnoDB 打开一张表，就增加一个对应的对象到数据字典。

数据字典

对数据库中的数据、库对象、表对象等的元信息的集合。在 MySQL 中，数据字典信息内容就包括表结构、数据库名或表名、字段的数据类型、视图、索引、表字段信息、存储过程、触发器等内容，MySQL INFORMATION_SCHEMA 库提供了对数据局元数据、统计信息、以及有关MySQL Server的访问信息（例如：数据库名或表名，字段的数据类型和访问权限等）。该库中保存的信息也可以称为MySQL的数据字典。

预读机制

MySQL的预读机制，就是当你从磁盘上加载一个数据页的时候，他可能会连带着把这个数据页相邻的其他数据页，也加载到缓存里去！

举个例子，假设现在有两个空闲缓存页，然后在加载一个数据页的时候，连带着把他的一个相邻的数据页也加载到缓存里去了，正好每个数据页放入一个空闲缓存页！

哪些情况下会触发MySQL的预读机制？

• 有一个参数是innodb_read_ahead_threshold，他的默认值是56，意思就是如果顺序的访问了一个区里的多个数据页，访问的数据页的数量超过了这个阈值，此时就会触发预读机制，把下一个相邻区中的所有数据页都加载到缓存里去。

• 如果Buffer Pool里缓存了一个区里的13个连续的数据页，而且这些数据页都是比较频繁会被访问的，此时就会直接触发预读机制，把这个区里的其他的数据页都加载到缓存里去这个机制是通过参数innodb_random_read_ahead来控制的，他默认是OFF，也就是这个规则是关闭的。

所以默认情况下，主要是第一个规则可能会触发预读机制，一下子把很多相邻区里的数据页加载到缓存里去。

预读机制的好处为了提升性能。假设你读取了数据页01到缓存页里去，那么接下来有可能会接着顺序读取数据页01相邻的数据页02到缓存页里去，这个时候，是不是可能在读取数据页02的时候要再次发起一次磁盘IO？

所以为了优化性能，MySQL才设计了预读机制，也就是说如果在一个区内，你顺序读取了好多数据页了，比如数据页01到数据页56都被你依次顺序读取了，MySQL会判断，你可能接着会继续顺序读取后面的数据页。那么此时就提前把后续的一大堆数据页（比如数据页57到数据页72）都读取到Buffer Pool里去。

缓冲池内存管理

这里需要了解三个链表（Free List、Flush List、LRU List），

• Free List磁碟上的資料頁和快取頁是一 一對應起來的，都是16KB，一個資料頁對應一個快取頁。資料庫會為Buffer Pool設計一個free鍊錶，他是一個雙向鍊錶資料結構，這個free鍊錶裡，每個節點就是一個空閒的快取頁的描述資料塊的位址，也就是說，只要你一個快取頁是空閒的，那麼他的描述資料塊就會被放入這個free鍊錶中。剛開始資料庫啟動的時候，可能所有的快取頁都是空閒的，因為此時可能是一個空的資料庫，一條資料都沒有，所以此時所有快取頁的描述資料塊，都會被放入這個free鍊錶中，除此之外，這個free鍊錶有一個基礎節點，他會引用鍊錶的頭節點和尾節點，裡面還儲存了鍊錶中有多少個描述資料塊的節點，也就是有多少個空閒的快取頁。

• Flush List和Free List 鍊錶類似，flush鍊錶本質也是透過快取頁的描述資料區塊中的兩個指針，讓被修改過的快取頁的描述資料區塊，組成一個雙向鍊錶。凡是被修改過的快取頁，都會把他的描述資料區塊加入到flush鍊錶中去，flush的意思就是這些都是髒頁，後續都是要flush刷新到磁碟上去。
  

• LRU List由於緩衝池大小是一定的，換句話說free 鍊錶中的空閒快取頁資料是一定的，當你不停的把磁碟上的數據頁面載入到空閒快取頁裡去，free 鍊錶中不停的移除空閒快取頁，遲早有那麼一瞬間，free 鍊錶中已經沒有空閒快取頁，這時候就需要淘汰掉一些快取頁，那淘汰誰呢？這就需要利用快取命中率了，快取命中多的就是常用的，那不常用的就可以淘汰了。所以引入 LRU 鍊錶來判斷哪些快取頁是不常用的。
  

那LRU鍊錶的淘汰策略是什麼樣的呢？

假設我們從磁碟載入一個資料頁到快取頁的時候，就把這個快取頁的描述資料區塊放到LRU 鍊錶頭部去，那麼只要有資料的快取頁，他就會在LRU 裡了，而且最近被載入資料的快取頁，都會放到LRU鍊錶的頭部去，然後加入某個快取頁在尾部，只要發生查詢，就把它移到頭部，那麼最後尾部就是需要淘汰了。

但這樣真的就可以嗎？

第一種情況預讀機制破壞

由於預讀機制會把相鄰的沒有被訪問到的資料頁載入到快取裡，實際上只有一個快取頁被訪問了，另外一個透過預讀機制載入的快取頁，其實並沒有人訪問，此時這兩個快取頁可都在LRU鍊錶的前面，如下圖

這時候，假如沒有空閒快取頁了，那麼此時要載入新的資料頁了，是不是就要從LRU鍊錶的尾部把所謂的「最近最少使用的一個快取頁」給拿出來，刷入磁碟，然後騰出來一個空閒快取頁了。這樣顯然是很不合理的。

第二種情況可能導致頻繁被存取的快取頁被淘汰的場景

全表掃描導致他直接一下子把這個表裡所有的資料頁，都從磁碟載入到Buffer Pool裡去。這時候可能會一下子就把這個表的所有資料頁都一一裝入各個快取頁裡去！此時LRU鍊錶可能排在前面的一大串快取頁，都是全表掃描載入進來的快取頁！那如果這次全表掃描過後，後續幾乎沒用到這個表裡的資料呢？此時LRU鍊錶的尾部，可能全部都是之前一直被頻繁存取的那些快取頁！然後當你要淘汰掉一些快取頁騰出空間的時候，就會把LRU鍊錶尾部一直被頻繁訪問的緩存頁給淘汰掉了，而留下了之前全表掃描加載進來的大量的不經常訪問的緩存頁！

優化LRU演算法：基於冷熱資料分離的想法設計LRU鍊錶

MySQL在設計LRU鍊錶的時候，採取的實際上是冷熱資料分離的想法。 LRU鍊錶，會被分割為兩個部分，一部分是熱數據，一部分是冷數據，這個冷熱數據的比例是由 innodb_old_blocks_pct 參數控制的，他預設是37，也就是說冷數據佔37%。資料頁第一次載入到快取的時候，實際上快取頁會被放在冷資料區域的鍊錶頭部。

然後MySQL設定了一個規則，他設計了一個innodb_old_blocks_time 參數，預設值1000，也就是1000毫秒也就是說，必須是一個資料頁被載入到快取頁之後，在1s之後，你存取這個緩存頁，它會被挪動到熱資料區域的鍊錶頭部去。因為假設你載入了一個資料頁到快取去，然後過了1s之後你還訪問了這個快取頁，說明你後續很可能會經常要存取它，這個時間限制就是1s，因此只有1s後你造訪了這個快取頁，他才會給你把快取頁放到熱資料區域的鍊錶頭去。

這樣的話預讀和全表掃描的資料都只會在冷資料頭部，不會一開始就進去熱資料區。

LRU演算法極致最佳化

LRU鍊錶的熱資料區域的存取規則最佳化一下，即只有在熱資料區域的後3/4部分的快取頁被訪問了，才會給你移動到鍊錶頭部去。如果你是熱資料區域的前面1/4的快取頁被訪問，他是不會移動到鍊錶頭部去的。

舉個例子，假設熱資料區域的鍊錶裡有100個快取頁，那麼排在前面的25個快取頁，他即使被存取了，也不會移動到鍊錶頭部去的。但是對於排在後面的75個快取頁，他只要被訪問，就會移動到鍊錶頭部去。這樣的話，他就可以盡可能的減少鍊錶中的節點移動了。

LRU鍊錶淘汰快取頁時機

MySQL在執行CRUD的時候，首先就是大量的操作快取頁以及對應的幾個鍊錶。然後在快取頁都滿的時候，必然要想辦法把一些快取頁給刷入磁碟，然後清空這幾個快取頁，接著把需要的資料頁載入到快取頁裡去！

我們已經知道，他是根據LRU鍊錶去淘汰快取頁的，那麼他到底是什麼時候把LRU鍊錶的冷資料區域中的快取頁刷入磁碟的呢？實際上他有以下三個時機：

定時把LRU尾部的部分快取頁刷入磁碟

• 後台線程，運行一個定時任務，這個定時任務每隔一段時間就會把LRU鍊錶的冷資料區域的尾部的一些快取頁，刷入磁碟裡去，清空這幾個快取頁，把他們加入回free鍊錶去。

把flush鍊錶中的一些快取頁定時刷入磁碟

如果只是把LRU 鍊錶的冷資料區域的緩存頁刷入磁碟是不夠，因為鍊錶的熱資料區域裡的許多快取頁可能也會被頻繁的修改，難道他們永遠都不刷入磁碟中了嗎？

所以這個後台執行緒同時也會在MySQL不太繁忙的時候，把flush鍊錶中的快取頁都刷入磁碟中，這樣被你修改過的數據，遲早都會刷入磁碟的！

只要flush鍊錶中的一波快取頁被刷入了磁碟，那麼這些快取頁也會從flush鍊錶和lru鍊錶中移除，然後加入到free鍊錶中去！

所以整體效果就是不停的載入資料到快取頁裡去，不停的查詢和修改快取數據，然後free鍊錶中的快取頁不停的在減少，flush鍊錶中的快取頁不停的在增加，lru鍊錶中的快取頁不停的在增加和移動。

另外一邊，你的後台執行緒不停的在把lru鍊錶的冷資料區域的快取頁以及flush鍊錶的快取頁，刷入磁碟中來清空快取頁，然後flush鍊錶和lru鍊錶中的快取頁面正在減少，free鍊錶中的快取頁面正在增加。

free鍊錶沒有空閒快取頁面

如果所有的free鍊錶都被使用了，這個時候如果要從磁碟載入資料頁到一個空閒快取頁中，此時就會從LRU鍊錶的冷資料區域的尾部找到一個快取頁，他一定是最不常使用的快取頁！然後把他刷入磁碟和清空，然後把資料頁載入到這個騰出的空閒快取頁裡去！

總結一下，三個鍊錶的使用情況，Buffer Pool被使用的時候，實際上會頻繁的從磁碟上加載資料頁到他的快取頁裡去，然後free鍊錶、flush鍊錶、lru鍊錶都會同時被使用，例如資料載入到一個快取頁，free鍊錶裡會移除這個快取頁，然後lru鍊錶的冷資料區域的頭部會放入這個快取頁。

然後如果你要是修改了一個快取頁，那麼flush鍊錶中會記錄這個髒頁，lru鍊錶中還可能會把你從冷數據區域移動到熱數據區域的頭部去。

如果你是查詢了一個快取頁，那麼此時就會把這個快取頁在lru鍊錶中移動到熱資料區域去，或者在熱資料區域中也有可能會移到頭部去。

Redo log Buffer 重做日誌緩衝

InnoDB 有 buffer pool（簡稱bp）。 bp 是資料庫頁面的緩存，對InnoDB 的任何修改操作都會首先在bp的page上進行，然後這樣的頁面將被標記為dirty(臟頁) 並被放到專門的flush list 上，後續將由master thread 或專門的刷髒線程階段性的將這些頁面寫入磁碟（disk or ssd）。

這樣的好處是避免每次寫入操作都操作磁碟導致大量的隨機IO，階段性的刷臟可以將多次對頁面的修改merge 成一次IO操作，同時異步寫入也降低了訪問的時延。然而，如果在 dirty page 尚未刷入磁碟時，server非正常關閉，這些修改操作將會遺失，如果寫入作業正在進行，甚至會因為損壞資料檔案而導致資料庫無法使用。

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

帶來的問題是額外的寫入redo log操作的開銷（順序IO，當然很快），以及資料庫啟動時恢復操作所需的時間。

redo日誌由兩個部分組成：redo log buffer、redo log file（在磁碟檔案那部分介紹）。 innodb 是支援事務的儲存引擎，在交易提交時，必須先將該交易的所有日誌寫入到 redo 日誌檔案中，待事務的 commit 操作完成才算整個事務操作完成。每次將redo log buffer寫入redo log file後，都需要呼叫一次fsync操作，因為重做日誌緩衝只是先把內容寫入作業系統的緩衝系統中，並沒有確保直接寫入到磁碟上，所以必須進行一次fsync操作。因此，磁碟的效能在某種程度上也決定了事務提交的效能（具體後面 redo log 落盤機制介紹）。

InnoDB 儲存引擎會先將重做日誌資訊先放入重做日誌緩衝中，然後在按照一定頻率將其刷新到重做日誌文件，重做日誌緩衝一般不需要設定的很大，因為一般情況每一秒鐘都會將重做日誌緩衝刷新到日誌檔案中，可透過設定參數Innodb_log_buffer_size 控制，預設為8MB。

Double Write 雙寫

如果說Insert Buffer 給InnoDB 儲存引擎帶來了效能上的提升，那麼Double wtite 帶給InnoDB 儲存引擎的是資料頁的可靠性。

InnoDB 的 Page Size 一般是16KB，其資料校驗也是針對這16KB來計算的，將資料寫入到磁碟是以 Page 為單位進行操作的。我們知道，由於檔案系統對一次大數據頁（例如InnoDB的16KB）大多數情況下不是原子操作，這意味著如果伺服器宕機了，可能只做了部分寫入。 16K的數據，寫入4K時，發生了系統斷電 os crash ，只有一部分寫是成功的，這種情況下就是 partial page write 問題。

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

為了解決這個問題，InnoDB實作了double write buffer，簡單來說，就是在寫資料頁之前，先把這個資料頁寫到一塊獨立的實體檔案位置（ibdata），然後再寫到數據頁。這樣在宕機重啟時，如果出現資料頁損壞，那麼在應用redo log之前，需要透過該頁的副本來還原該頁，然後再進行redo log重做，這就是double write。 double write技術帶給innodb儲存引擎的是資料頁的可靠性，以下將doublewrite技術解析

如上图所示，Double Write 由两部分组成，一部分是内存中的 double write buffer，大小为2MB，另一部分是物理磁盘上共享表空间连续的128个页，大小也为2MB。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是通过 memcpy 函数将脏页先复制到内存中的该区域，之后通过 double write buffer 再分两次，每次1MB顺序地写入共享表空间的物理磁盘上，然后马上调用 fsync 函数，同步磁盘，避免操作系统缓冲写带来的问题。在完成double write 页的写入后，再将 double wirite buffer 中的页写入各个表空间文件中。

在这个过程中，doublewrite 是顺序写，开销并不大，在完成 doublewrite 写入后，在将 double write buffer写入各表空间文件，这时是离散写入。

如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB 存储引擎可以从共享表空间中的double write 中找到该页的一个副本，将其复制到表空间文件中，再应用重做日志。

InnoDB 存储引擎第二部分：后台线程

IO 线程

在 InnoDB 中使用了大量的 AIO（Async IO） 来做读写处理，这样可以极大提高数据库的性能。在 InnoDB 1.0 版本之前共有4个 IO Thread，分别是 write，read，insert buffer和log thread，后来版本将 read thread和 write thread 分别增大到了4个，一共有10个了。

• - read thread ： 负责读取操作，将数据从磁盘加载到缓存page页。4个

• - write thread：负责写操作，将缓存脏页刷新到磁盘。4个

• - log thread：负责将日志缓冲区内容刷新到磁盘。1个

• - insert buffer thread ：负责将写缓冲内容刷新到磁盘。1个

Purge 线程

事务提交之后，其使用的 undo 日志将不再需要，因此需要 Purge Thread 回收已经分配的 undo 页。show variables like '%innodb*purge*threads%';

Page Cleaner 线程

作用是将脏数据刷新到磁盘，脏数据刷盘后相应的 redo log 也就可以覆盖，即可以同步数据，又能达到 redo log 循环使用的目的。会调用write thread线程处理。show variables like '%innodb*page*cleaners%';

InnoDB 存储引擎第三部分：磁盘文件

InnoDB 的主要的磁盘文件主要分为三大块：一是系统表空间，二是用户表空间，三是 redo 日志文件和归档文件。

二进制文件（binlong）等文件是 MySQL Server 层维护的文件，所以未列入 InnoDB 的磁盘文件中。

系统表空间和用户表空间

系统表空间包含 InnoDB 数据字典（元数据以及相关对象）并且 double write buffer , change buffer , undo logs 的存储区域。

系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据。

系统表空间是一个共享的表空间，因为它是被多个表共享的。

系统表空间是由一个或者多个数据文件组成。默认情况下,1个初始大小为10MB，名为 ibdata1 的系统数据文件在MySQL的data目录下被创建。用户可以使用 innodb_data_file_path 对数据文件的大小和数量进行配置。

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 两个文件组成系统表空间。如果这两个文件位于不同的磁盘上，磁盘的负载可能被平均，因此可以提高数据库的整体性能。两个文件的文件名之后都跟了属性，表示文件 ibdata1 的大小为1000MB，文件 ibdata2 的大小为1000MB，而且用完空间之后可以自动增长。

设置 innodb_data_file_path 参数之后，所有基于 InnoDB 存储引擎的表的数据都会记录到该系统表空间中，如果设置了参数 innodb_file_per_table ，则用户可以将每个基于 InnoDB 存储引擎的表产生一个独立的用户空间。

用户表空间的命名规则为：表名.ibd。通过这种方式，用户不用将所有数据都存放于默认的系统表空间中，但是用户表空间只存储该表的数据、索引和插入缓冲BITMAP等信息，其余信息还是存放在默认的系统表空间中。

下图显示 InnoDB 存储引擎对于文件的存储方式，其中frm文件是表结构定义文件，记录每个表的表结构定义。

重做日誌檔案（redo log file）和歸檔檔案

預設情況下，在InnoDB 儲存引擎的資料目錄下會有兩個名為ib_logfile0 和ib_logfile1 的文件，這就是InnoDB 的重做檔案（redo log file），它記錄了對於InnoDB 儲存引擎的交易日誌。

當 InnoDB 的資料儲存檔案發生錯誤時，重做日誌檔案就能派上用場。 InnoDB 儲存引擎可以使用重做日誌檔案將資料還原為正確狀態，以此來確保資料的正確性和完整性。

每個 InnoDB 儲存引擎至少有1個重做日誌文件，每個文件組下方至少有2個重做日誌文件，加預設的 ib_logfile0 和 ib_logfile1。

為了得到更高的可靠性，使用者可以設定多個鏡像日誌群組，將不同的檔案群組放在不同的磁碟上，以此來提高重做日誌的高可用性。

在日誌組中每個重做日誌檔案的大小一致，並以【循環寫入】的方式運行。 InnoDB 儲存引擎先寫入重做日誌檔案1，當檔案被寫滿時，會切換到重做日誌檔案2，再當重做日誌檔案2也被寫滿時，再切換到重做日誌1。

使用者可以使用 Innodb_log_file_size 來設定重做日誌檔案的大小 ，這對 InnoDB 儲存引擎的效能有著非常大的影響。

如果重做日誌檔案設定的太大，資料遺失時，復原時可能需要很長的時間；另一個方面，如果設定的太小，重做日誌檔案太小會導致依據checkpoint 的檢查需要頻繁刷新髒頁到磁碟中，導致效能的抖動。

重做日誌的落盤機制

InnoDB 對於資料檔案和日誌檔案的刷盤遵守WAL（write ahead redo log）和Force-log-at- commit 兩種規則，二者保證了事務的持久性。 WAL 要求資料的變更寫入磁碟前，首先必須將記憶體中的日誌寫入磁碟；Force-log-at-commit 要求當一個交易提交時，所有產生的日誌都必須刷新到磁碟上，如果日誌刷新成功後，緩衝池中的資料刷新到磁碟前資料庫發生了宕機，那麼重啟時，資料庫可以從日誌中恢復資料。

如上圖所示，InnoDB 在緩衝池中變更資料時，會先將相關變更寫入重做日誌緩衝中，然後再按時（例如每秒刷新機制）或當交易提交時寫入磁碟，這符合Force-log-at-commit 原則；當重做日誌寫入磁碟後，緩衝池中的變更資料才會依據checkpoint 機制寫入磁碟中，這符合WAL 原則。

在checkpoint 擇時機制中，就有重做日誌檔案寫滿的判斷，所以，如前文所述，如果重做日誌檔案太小，經常被寫滿，就會頻繁導致checkpoint 將更改的資料寫入磁碟，導致效能抖動。

作業系統的檔案系統是帶有快取的，當 InnoDB 寫入資料到磁碟時，有可能只是寫入到了檔案系統的快取中，沒有真正的「落袋為安」。

InnoDB 的 innodb_flush_log_at_trx_commit 屬性可以控制每次交易提交時 InnoDB 的行為。當屬性值為0時，交易提交時，不會對重做日誌進行寫入操作，而是等待主執行緒按時寫入；當屬性值為1時，交易提交時，會將重做日誌寫入文件系統緩存，並且調用檔案系統的fsync ，將檔案系統緩衝中的資料真正寫入磁碟存儲，確保不會出現資料遺失；當屬性值為2時，事務提交時，也會將日誌檔案寫入檔案系統緩存，但不會呼叫fsync，而是讓檔案系統自己去判斷何時將快取寫入磁碟。

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

Innodb_flush_log_at_commit 是InnoDB 效能調優的一個基礎參數，涉及InnoDB 的寫入效率和數據安全。當參數數值為0時，寫入效率最高，但是資料安全最低；參數值為1時，寫入效率最低，但是資料安全性最高；參數值為2時，二者都是中等水平，一般建議將屬性值設定為1，以獲得較高的安全性，而且也只有設定為1，才能確保事務的持久性。

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

有了上面InnoDB 儲存引擎的架構基礎介紹，我們再來分析一次UPDATE數據更新具體流程。

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

MySQL Server 層處理流程

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

• 使用者各種操作觸發後台sql執行，透過web專案中自帶的資料庫連線池：如dbcp、c3p0、druid 等，與資料庫伺服器的資料庫連線池建立網路連線；

• 資料庫連線池中的線程監聽到請求後，將接收到的sql語句透過SQL介面回應給查詢解析器，查詢解析器將sql依照sql的語法解析出查詢哪個表的哪些字段，查詢條件是啥；

• 再透過查詢優化器處理，選擇該sq最優的一套執行計劃；

• 然後執行器負責呼叫儲存引擎的一系列接口，執行這個計畫而完成整個sql語句的執行

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

InnoDB 儲存引擎處理流程

#具體執⾏語句得要儲存引擎來完成，如上圖中所示：

• 更新users表中id=10的這條數據，如果緩衝池中沒有該條數據的，得要先從磁碟中將被更新數據的原始資料載入到緩衝池中。

• 同時為了確保並發更新資料安全問題，會對這條資料先加鎖，防⽌其他事務進⾏更新。

• 接著將更新前的值先備份寫入⼊到undo log中（方便交易回溯時取舊資料），⽐如 update 語句即儲存更新欄位之前的值。

• 更新buffer pool 中的快取資料為最新的數據，那麼此時記憶體中的資料為髒資料（記憶體中資料和磁碟中資料不一致）

⾄此就完成了在緩衝池中的執⾏流程（如上圖）。

緩衝池中更新完資料後，需要將本次的更新資訊順序​​寫到Redo Log ⽇志，因為現在已經把記憶體裡的資料進行了修改，但是磁碟上的資料還沒修改，此時萬一MySQL所在的機器宕機了，必然會導致記憶體裡修改過的資料遺失，redo 日誌就是記錄下來你對資料做了什麼修改，例如對「id=10這行記錄修改了name欄位的值為xxx”，這就是一個日誌，用來在MySQL突然宕機的時候，用來恢復你更新過的資料的。不過注意的是此時 Redo Log 還沒有落盤到日誌檔。

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

上面我們知道到目前我們修改了內存數據，然後記錄了Redo Log Buffer 日誌緩衝，如果這個時候MySQL 奔潰，內存數據和  Redo Log Buffer 數據都會丟失，但是此時數據丟失並不要緊，因為一則更新語句，沒提交事務，就代表他沒執行成功，此時MySQL宕機雖然導致記憶體裡的資料都遺失了，但你會發現，磁碟上的資料還是還停留在原樣子。

接下來要提交事物了，此時就會根據一定的策略把redo日誌從redo log buffer裡刷入到磁碟檔案裡去，此時這個策略是透過 innodb_flush_log_at_trx_commit 來設定的。

innodb_flush_log_at_trx_commit=0，表示提交事物不會把redo log buffer裡的資料刷入磁碟檔案的，此時可能你都提交事務了，結果mysql宕機了，然後此時記憶體裡的數據全部遺失，所以這種方式不可取。

innodb_flush_log_at_trx_commit=1，redo log從記憶體刷到磁碟檔案去，只要交易提交成功，那麼redo log就必然在磁碟裡了，所以如果這個時候MySQL奔潰，可以根據Redo Log日誌恢復資料。

innodb_flush_log_at_trx_commit=2，提交事務的時候，把redo日誌寫入磁碟文件對應的os cache快取裡去，而不是直接進入磁碟文件，可能1秒後才會把os cache裡的資料寫入到磁碟檔案裡去。

提交交易的時候，同時會寫入binlog，binlog也有不同的刷盤策略，有一個sync_binlog參數可以控制binlog的刷盤策略，他的預設值是0，此時你把binlog寫入磁碟的時候，其實不是直接進入磁碟文件，而是進入os cache記憶體快取。 ⼀般我們為了確保資料不遺失會配置雙1策略，Redo Log 和 Binlog落盤策略都選擇1。

Binlog 落盤後，再將Binlog的⽂件名、⽂件所在路徑資訊以及commit標記給同步順序寫到Redo log中，這一步的意義是用來保持 redo log 日誌與 binlog 日誌一致的。 commit標記是判定事務是否成功提交的⼀個⽐較重要的標準，舉個例子，如果如果第5步或第6步執行成功後MySQL就奔潰了，這個時候因為沒有最終的事務commit標記在redo日誌裡，所以此次事務可以判定不成功。不會說redo日誌檔裡有這次更新的日誌，但binlog日誌檔裡沒有這次更新的日誌，不會有資料不一致的問題。

做完前面後，記憶體資料已經修改，事物已經提交，日誌已經落盤，但是磁碟資料還沒有同步修改。 InnoDB儲存引擎後台有⼀個IO線程，會在資料庫壓⼒的低峰期間，將緩衝池中被事務更新、但還沒來得及寫到磁碟中的資料（髒數據，因為磁碟資料和記憶體資料已經不⼀致了）給刷到磁碟中，完成事務的持久化。

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

#推薦學習：mysql影片教學

以上是讓人心動的mysql體系架構和InnoDB儲存引擎知識詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章！