讓人心動的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體系結構和InnoDB儲存引擎知識詳解的詳細內容，更多請關注TW511.COM其它相關文章！