一 存儲引擎體系

1.1 MySQL體系架構

上圖描述

Connection Pool : 連接池組件 
Management Services & Utilities : 管理服務和工具組件 
SQL Interface : SQL接口組件 
Parser : 查詢分析器組件 
Optimizer : 優化器組件 
Caches & Buffers : 緩沖池組件 
Pluggable Storage Engines : 存儲引擎 
File System : 文件系統 

1. 連接層

• 最上層是一些客戶端和鏈接服務，包含本地sock 通信和大多數基于客戶端/服務端工具實現的類似于TCP/IP的通信。主要完成一些類似于連接處理、授權認證、及相關的安全方案。在該層上引入了線程池的概念，為通過認證安全接入的客戶端提供線程。同樣在該層上可以實現基于SSL的安全鏈接。服務器也會為安全接入的每個客戶端驗證它所具有的操作權限。

2. 服務層

• 第二層架構主要完成大多數的核心服務功能，如SQL接口，并完成緩存的查詢，SQL的分析和優化，部分內置函數的執行。所有跨存儲引擎的功能也在這一層實現，如 過程、函數等。在該層，服務器會解析查詢并創建相應的內部解析樹，并對其完成相應的優化如確定表的查詢的順序，是否利用索引等，最后生成相應的執行操作。如果是select語句，服務器還會查詢內部的緩存，如果緩存空間足夠大，這樣在解決大量讀操作的環境中能夠很好的提升系統的性能。

3. 引擎層

• 存儲引擎層， 存儲引擎真正的負責了MySQL中數據的存儲和提取，服務器通過API和存儲引擎進行通信。不同的存儲引擎具有不同的功能，這樣我們可以根據自己的需要，來選取合適的存儲引擎。

4. 存儲層

數據存儲層， 主要是將數據存儲在文件系統之上，并完成與存儲引擎的交互。

和其他數據庫相比，MySQL存儲引擎是插件式的存儲引擎架構。將 查詢處理和其他的系統任務以及數據的存儲提取分離。這種架構可 以根據業務的需求和實際需要選擇合適的存儲引擎。

1.2 存儲引擎介紹

1. 概述

針對不同的存儲需求可以選擇最優的存儲引擎。存儲引擎就是存 儲數據，建立索引，更新查詢數據等等技術的實現方式 。存儲引 擎是基于表的，而不是基于庫的。所以存儲引擎也可被稱為表類 型。

2. 查看MySQL存儲引擎

3. MySQL常見兩種引擎

MySQL默認支持InnoDB

二 InnoDB深入刨析

2.1 InnoDB體系結構

1. 緩沖池

介紹

InnoDB存儲引擎基于磁盤文件存儲，訪問物理硬盤和在內存中進行訪問，速度相差很大，為了盡可能 彌補這兩者之間的I/O效率的差值，就需要把經常使用的數據加載到緩沖池中，避免每次訪問都進行磁 盤I/O。

在InnoDB的緩沖池中不僅緩存了索引頁和數據頁，還包含了undo頁、插入緩存、自適應哈希索引以及 InnoDB的鎖信息等等。

讀取

在數據庫中進行讀取頁的操作時， 首先將磁盤中讀取到的頁數據存放在緩沖池中， 下一次再讀相同的 頁時， 首先判斷緩沖池中是否存在，如果緩沖池被命中，則直接讀取數據， 如果沒有，則讀取磁盤中 的頁數據。

更新

而對于數據庫中頁的修改操作，則首先修改在緩沖池中的頁，然后再以一定的頻率刷新到磁盤上，從而 保證緩沖池中的數據與磁盤中的數據一致。頁從緩沖池刷新回磁盤的操作并不是在每次頁發生更新時， 都需要觸發，出于整體的性能考慮，而是通過checkpoint機制刷新回磁盤。

參數配置

在專用服務器上，通常將多達80%的物理內存分配給緩沖池。參數設置：

show variables like 'innodb_buffer_pool_size'; 

 

在InnoDB引擎中，允許有多個緩沖池實例，根據頁的哈希值分配到不同的緩沖池實例中，從而減少數 據庫內部的資源競爭， 提升并發處理能力。參數配置：

vi /etc/my.conf 
 
innodb_buffer_pool_size=268435456 

2. 后臺線程

Master Thread

主要負責將緩沖池中的數據異步刷新到磁盤中, 保持數據的一致性， 還包括臟頁的刷新、合并插入緩 存、undo頁的回收 。

IO Thread

在InnoDB存儲引擎中大量使用了AIO來處理IO請求, 這樣可以極大地提高數據庫的性能，而IO Thread主要負責這些IO請求的回調。

Purge Thread

主要用于回收事務已經提交了的undo log，在事務提交之后，undo log可能不用了，就用它來回 收。

Pager Cleaner Thread

新引入的一個用于協助 Master Thread 刷新臟頁到磁盤的線程，它可以減輕 Master Thread 的 工作壓力，減少阻塞。

3. 文件

frm文件

該文件是用來保存每個表的元數據信息的， 主要包含表結構定義 。

系統表空間

系統表空間是InnoDB數據字典，二次寫緩沖區，更改緩沖區和撤消日志的存儲區 。系統表空間可以具 有一個或多個數據文件， 默認情況下會在數據存放目錄中創建一個名為 ibdata1 表空間數據文件。該文件名稱可以通過參數 innodb_data_file_path 指定。

file_name:file_size[:autoextend[:max:max_file_size]] 

獨占表空間

innodb中設置了參數 innodb_file_per_table 為 1/ON，則會將存儲的數據、索引等信息單獨 存儲在一個獨占表空間，因此也會產生一個獨占表空間文件(ibd)

redo log

重做日志， 用于恢復提交事務修改的頁操作 ， 用來保證事務的原子性和持久性。主要是解決 提交 的事務沒有執行完成但是數據庫崩潰了，當數據庫恢復之后，可以完整的恢復數據。在執行操作時， InnoDB存儲引擎會首先將重做日志信息放到這個緩沖區 redo log buffer,然后按照不同的策略和 頻率將buffer中的數據刷新到重做日志中。redo log在磁盤中保存的名稱為 ib_logfile0，ib_logfile1。

bin log

二進制日志，其中記錄表結構中的數據變更，包含DDL與DML。

其他

錯誤日志、查詢日志、慢查詢日志等。

2.2 InnoDB邏輯存儲結構

1. 表空間

表空間是InnoDB存儲引擎邏輯結構的最高層， 大部分數據都存在于共享表空間ibdata1中。如果用 戶啟用了參數 innodb_file_per_table ，則每張表都會有一個表空間(xxx.ibd),里面存放表 中的數據、索引和插入緩存Bitmap頁。其他的數據如undo log、插入緩存索引頁、系統事務信息、 二次寫緩存都是在共享表空間中。

2. 段

表空間是由各個段組成的，常見的段有數據段、索引段、回滾段等。InnoDB存儲引擎是基于索引組織 的，因此數據即是索引，索引即數據。數據段就是B+樹的葉子節點， 索引段即為B+樹的非葉子節點。InnoDB中對于段的管理，都是引擎自身完成，不需要人為對其控制。

3. 區

區是表空間的單元結構，每個區的大小為1M。默認情況下， InnoDB存儲引擎頁大小為16K， 即一 個區中一共有64個連續的頁。

4. 頁

頁是組成區的最小單元，頁也是InnoDB 存儲引擎磁盤管理的最小單元，每個頁的大小默認為 16KB。為了保證頁的連續性，InnoDB 存儲引擎每次從磁盤申請 4-5 個區。

5. 行

InnoDB 存儲引擎是面向行的(row-oriented)，也就是說數據是按行進行存放的，每個頁存放的行 記錄也是有硬性定義的，最多允許存放 16KB/2-200 行，即 7992 行記錄。

• trx_id：每次對某條聚簇索引記錄進行改動時，都會把對應的事務id賦值給trx_id隱藏列。
• roll_pointer：每次對某條聚簇索引記錄進行改動時，都會把舊的版本寫入到undo日志中，然后這個隱藏列就相當于一個指針，可以通過它來找到該記錄修改前的信息。

2.3 checkpoint

1. 介紹

由于日常的DML語句操作時，首先操作的是緩沖池，并沒有直接寫入到磁盤，這有可能會導致內存中的 數據與磁盤中的數據產生不一致的情況，而與磁盤中數據不一致的頁我們成為"臟頁"。而 checkpoint的工作，就是將內存中的臟頁，在一定條件下刷新到磁盤。

如果在從緩沖池將頁數據刷新到磁盤的過程中發生宕機，那么數據就無法恢復了;為了避免這種情況的 發生，采用了Write Ahead Log(WAL)策略，即當事務提交時，先寫重做日志(redo log)，再修改 緩沖池數據頁，最后通過Checkpoint刷新到磁盤(事務提交會觸發checkpoint)。這樣正在執行的 事務，因為存在日志都可以被恢復，沒有日志的事務還沒有執行也不會丟失數據。

2. 作用

A. 縮短數據恢復時間

當數據庫發生宕機時，數據庫不用重做所有的日志，因為Checkpoint之前的頁都已經刷新會磁盤了， 故數據庫只需要重做Checkpoint之后的日志就好，這樣就大大縮短了恢復時間。

B. 緩沖池不夠用時，需要先將臟頁數據刷新到磁盤中;

當緩沖池不夠用時, 根據LRU算法溢出最近最少使用的頁, 如果此頁是臟頁,則強制執行 Checkpoint, 刷新臟頁到磁盤。

C. 重做日志不可用時，刷新臟頁到磁盤;

redo log大小是固定的, 當前的InnoDB引擎中， 重做日志的設計都是循環使用的，并不是無限增 大的。重做日志可以被重用的部分是已經不再需要的， 數據庫發生宕機也不需要這部分的重做日志， 因此可以被覆蓋使用， 如果此時重做日志還需要使用，那么必須強制執行Checkpoint，將緩沖池中 的頁至少刷新磁盤, checkpoint移動到當前重做日志的位置。

write pos表示日志當前記錄的位置，當ib_logfile_1寫滿后，會從ib_logfile_0從頭開始記 錄;check point表示將日志記錄的修改寫進磁盤，完成數據落盤，數據落盤后checkpoint會將日 志上的相關記錄擦除掉，即write position ->checkpoint 之間的部分是redo log空著的部 分，用于記錄新的記錄，checkpoint->write position 之間是redo log待落盤的數據修改記 錄。當write postion追上checkpoint時，得先停下記錄，先推動checkpoint向前移動，空出位 置記錄新的日志。

3. 分類

A. Sharp Checkpoint

Sharp Checkpoint 發生在數據庫關閉時，將所有的臟頁都刷新回磁盤，這是默認的工作方式，參 數：innodb_fast_shutdown=1。

B. Fuzzy Checkpoint

在InnoDB存儲引擎運行時，使用Fuzzy Checkpoint進行頁刷新，只刷新一部分臟頁。

2.4 InnoDB關鍵特性

1. 插入緩存

主鍵是行唯一的標識符，在應用程序中行記錄的插入順序一般是按照主鍵遞增的順序進行插入的。因 此，插入聚集索引一般是順序的，不需要磁盤的隨機讀取。因此，在這樣的情況下，插入操作一般很快 就能完成。

但是，不可能每張表上只有一個聚集索引，在更多的情況下，一張表上有多個非聚集的輔助索引 (secondary index)。比如，我們還需要按照name這個字段進行查找，并且name這個字段不是唯 一的, 這樣的情況下產生了一個非聚集的并且不是唯一的索引。在進行插入操作時，數據頁的存放還是 按主鍵id的執行順序存放，但是對于非聚集索引，葉子節點的插入不再是順序的了。這時就需要離散地 訪問非聚集索引頁，插入性能在這里變低了。然而這并不是這個name字段上索引的錯誤，因為B+樹的 特性決定了非聚集索引插入的離散性。

InnoDB存儲引擎開創性地設計了插入緩沖，對于非聚集索引的插入或更新操作，不是每一次直接插入 索引頁中，而是先判斷插入的非聚集索引頁是否在緩沖池中。如果在，則直接插入;如果不在，則先放 入一個插入緩沖區中，好似欺騙數據庫這個非聚集的索引已經插到葉子節點了，然后再以一定的頻率執 行插入緩沖和非聚集索引葉子節點的合并操作，這時通常能將多個插入合并到一個操作中(因為在一個 索引頁中)，這就大大提高了對非聚集索引執行插入和修改操作的性能。

2. 兩次寫

當數據庫寫物理頁時，如果宕機了，那么可能會導致物理頁的一致性被破壞。

可能有人會說，重做日志不是可以恢復物理頁嗎?實際上是的，但是要求是在物理頁一致的情況下。也就是說，如果物理頁完全是未寫之前的狀態，則可以用重做日志恢復。如果物理頁已經完全寫完了， 那么也可以用重做日志恢復。但是如果物理頁前面2K寫了新的數據，但是后面2K還是舊的數據，則種 情況下就無法使用重做日志恢復了。

這里的兩次寫就是保證了物理頁的一致性，使得即使宕機，也可以用重做日志恢復。在寫物理頁時，并不是直接寫到真正的物理頁上去，而是先寫到一個臨時頁上去，臨時頁寫完后，再寫 物理頁。這樣一來：

• A. 如果寫臨時頁時宕機了，物理頁還是完全未寫之前的狀態，可以用重做日志恢復
• B. 如果寫物理頁時宕機了，則可以使用臨時頁來恢復物理頁

每次寫物理頁時，先寫到double write buffer中，然后從double write buffer寫到double write上去。最后再從double write buffer寫到物理頁上去。

3. 自適應hash索引

在InnoDB中默認支持的索引結構為 B+ 樹，B+ 樹索引可以使用到范圍查找，同時是按照順序的方式 對數據進行存儲，因此很容易對數據進行排序操作，在聯合索引中也可以利用部分索引鍵進行查詢 。而對于Hash索引則只能滿足 =，<>,in查詢，不能使用范圍查詢， 而且數據的存儲是沒有順序的。

MySQL 默認使用 B+ 樹作為索引，因為 B+ 樹有著 Hash 索引沒有的優點，那么為什么還需要自 適應 Hash 索引呢?

這是因為B+樹的查找次數，取決于B+樹的高度，在生產環境中，B+樹的高度一般為3-4層，故需要3-4 次查詢。而 Hash 索引在進行數據檢索的時候效率非常高，通常只需要 O(1) 的復雜度，也就是一 次就可以完成數據的檢索。雖然 Hash 索引的使用場景有很多限制，但是優點也很明顯。InnoDB存儲 引擎會監控對表上各索引頁的查詢，如果觀察到hash索引可以提升速度，則建立hash索引，稱之為自 適應hash索引(Adaptive Hash Index，AHI)。

注意，這里的自適應指的是不需要人工來指定，系統會根據情況自動完成。

什么情況下才會使用自適應 Hash 索引呢?如果某個數據經常被訪問，當滿足一定條件的時候，就會 將這個數據頁的地址存放到 Hash 表中。這樣下次查詢的時候，就可以直接找到這個頁面的所在位 置。值得注意的是，hash索引只能用于= ，in的查詢，對于其他的查詢類型，如范圍匹配等是不能使 用hash索引的。而且自適應 Hash 索引只保存熱數據(經常被使用到的數據)，并非全表數據。因此 數據量并不會很大，因此自適應 Hash 也是存放到緩沖池中，這樣也進一步提升了查找效率。

 

 

4. 異步IO

為了提高磁盤的操作性能，在InnoDB存儲引擎中使用異步非阻塞AIO的方式來操作磁盤。

與AIO對應的是Sync IO，如果是同步IO操作，則每進行一次IO操作，需要等待此次操作結束后才可 以進行接下來的操作。但是如果用戶發出的是一條索引掃描的查詢，那么這條SQL查詢語句可能需要掃 描多個索引頁，也就是需要進行多次的IO操作。每掃描一個頁并等待其完成之后，再進行下一次掃描， 這是沒有必要的。

用戶可以在發出一個IO請求后立即再發出另一個IO請求，當全部的IO請求發送完畢后，等待所有的IO 操作完成，這就是AIO。

5. 刷新臨接頁

InnoDB提供刷新臨近頁功能：當刷新一臟頁時，同時檢測所在區(extent)的所有頁，如果有臟頁則 一并刷新，好處則是通過AIO特性合并寫IO請求，缺點則是有些頁不怎么臟也好被刷新，而且頻繁的更 改那些不怎么臟的頁又很快變成臟頁，造成頻繁刷新。對于固態磁盤則考慮關閉此功能(將 innodb_flush_neighbors設置為0)。

2.5 InnoDB事務

事務可由一條簡單的SQL語句組成，也可以由一組復雜的SQL語句組成。事務是訪問并更新數據庫中各 個數據項的一個程序執行單元。在事務操作時，這組執行單元中的SQL，要么全部成功， 要么全部失 敗。

1. 事務具有以下四個特性(ACID)

2. 隔離級別

并發事務帶來的問題：

為了解決上述提到的事務并發問題，數據庫提供一定的事務隔離機制來解決這個問題。數據庫的事務隔 離越嚴格，并發副作用越小，但付出的代價也就越大，因為事務隔離實質上就是使用事務在一定程度上 “串行化” 進行，這顯然與“并發” 是矛盾的。

數據庫的隔離級別有4個，由低到高依次為Read uncommitted、Read committed、Repeatable read、Serializable，這四個級別可以逐個解決臟寫、臟讀、不可重復讀、幻讀這幾類問題。

3. 實現

1). redo log

redo log叫做重做日志，是用來實現事務的持久性。該日志文件由兩部分組成：重做日志緩沖(redo log buffer)以及重做日志文件(redo log),前者是在內存中，后者在磁盤中。當事務提交之后 會把所有修改信息都會存到該日志中, 用于在刷新臟頁到磁盤時,發生錯誤時, 進行數據恢復使用。例：

 

 

執行事務操作

start transaction; 
select balance from bank where name="Tom"; 
-- 生成 重做日志 balance=8000 
update bank set balance = balance - 2000; 
-- 生成 重做日志 account=2000 
update finance set account = account + 2000; 
commit; 

流程

mysql 為了提升性能不會把每次的修改都實時同步到磁盤，而是會先存到Buffer Pool(緩沖池)里 頭，把這個當作緩存來用。然后使用后臺線程將緩存池刷新到磁盤。

當在執行刷新時，宕機或者斷電，可能會丟失部分數據。所以引入了redo log來記錄已成功提交事務 的修改信息，并且在事務提交時會把redo log持久化到磁盤，系統重啟之后在讀取redo log恢復最 新數據。

簡單來說 ， redo log是用來恢復數據的 用于保障，已提交事務的持久化特性 ;

2). undo log

undo log 叫做回滾日志，用于記錄數據被修改前的信息。他正好跟前面所說的重做日志所記錄的相 反，重做日志記錄數據被修改后的信息。undo log主要記錄的是數據的邏輯變化，為了在發生錯誤時 回滾之前的操作，需要將之前的操作都記錄下來，然后在發生錯誤時才可以回滾。

undo log 記錄事務修改之前版本的數據信息，因此假如由于系統錯誤或者rollback操作而回滾的話 可以根據undo log的信息來進行回滾到沒被修改前的狀態。

三 存儲引擎應用場景

在選擇存儲引擎時，應該根據應用系統的特點選擇合適的存儲引擎。對于復雜的應用系統，還可以根據 實際情況選擇多種存儲引擎進行組合。以下是幾種常用的存儲引擎的使用環境 。

• InnoDB : 是Mysql的默認存儲引擎，用于事務處理應用程序，支持外鍵, 行鎖。如果應用對事務的完整性有比較高的要求，在并發條件下要求數據的一致性，數據操作除了插入和查詢以外，還包含很多的更新、刪除操作，那么InnoDB存儲引擎是比較合適的選擇。InnoDB存儲引擎除了有效的降低由于刪除和更新導致的鎖定， 還可以確保事務的完整提交和回滾，對于電商系統中的商品(SPU、SKU、分類、品牌)、訂單、用戶等信息的存儲，InnoDB是最合適的選擇。
• MyISAM ： 如果應用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對事務的完整性、并發性要求不是很高，那么選擇這個存儲引擎是非常合適的。對于電商系統中，系統的操作日志、用戶評價、足跡等信息的存儲，MyISAM是合適的選擇。