最近看了《MySQL技術(shù)內(nèi)幕InnoDB存儲(chǔ)引擎》一書臼氨，受益良多电谣，對(duì)Mysql InnoDB有了進(jìn)一步的了解尼酿。于是根據(jù)自己理解和整理的資料，寫了一系列深入InnoDB的文章殉挽，其中不少知識(shí)來著《MySQL技術(shù)內(nèi)幕InnoDB存儲(chǔ)引擎》以及《MYSQL內(nèi)核：INNODB存儲(chǔ)引擎》丰涉，感謝這兩本書的作者拓巧，也向各位推薦這兩本書。

這一系列文章都是從MySql的使用和設(shè)計(jì)的出發(fā)一死，不會(huì)涉及源碼肛度，希望可以幫助大家更深入理解InnoDB的設(shè)計(jì)和實(shí)現(xiàn)。

概念區(qū)分

先明確兩個(gè)概念投慈，雖然平常我們并不需要嚴(yán)格區(qū)分他們
數(shù)據(jù)庫(kù)：存儲(chǔ)數(shù)據(jù)的物理操作系統(tǒng)文件或其他形式文件的集合
數(shù)據(jù)庫(kù)實(shí)例：MySql數(shù)據(jù)庫(kù)實(shí)例由后臺(tái)線程以及一個(gè)共享內(nèi)存區(qū)組成贤斜，負(fù)責(zé)操作數(shù)據(jù)庫(kù)文件。
MySql是一個(gè)單進(jìn)程多線程架構(gòu)的數(shù)據(jù)庫(kù)逛裤，MySql數(shù)據(jù)庫(kù)實(shí)例在系統(tǒng)上表現(xiàn)就是一個(gè)進(jìn)程。

MySql架構(gòu)

MySql架構(gòu)圖如下

MySql中有以下組件

• 連接池組件 -- Connection Pool
• 管理服務(wù)和工具組件 -- Management Services & utilities
• SQL接口組件 -- SQL Interface
• 查詢分析器組件 -- Praser
• 優(yōu)化器組件 -- Optimizer
• 緩沖組件 -- Caches & Buffers
• 插件式表存儲(chǔ)引擎 -- Pluggable Storage Enginess
• 物理文件 -- Files & Logs

MySQL區(qū)別于其他數(shù)據(jù)庫(kù)的一個(gè)最重要的特點(diǎn)是插件式存儲(chǔ)引擎猴抹。它是基于表的带族，而不是數(shù)據(jù)庫(kù)。MySql常用存儲(chǔ)引擎如下：
MyISAM存儲(chǔ)引擎

• 不支持事務(wù)
• 緩沖池只緩存索引文件蟀给，不緩沖數(shù)據(jù)文件
• 由MYD和MYI文件組成蝙砌，MYD用來存放數(shù)據(jù)文件，MYI用來存放索引文件跋理，

InnoDB存儲(chǔ)引擎
獨(dú)立表空間择克，支持MVCC，行鎖設(shè)計(jì)前普，提供一致性非鎖定讀
支持外鍵肚邢，插入緩沖，二次寫拭卿，自適應(yīng)哈希索引骡湖，預(yù)讀
使用聚集的方式存儲(chǔ)數(shù)據(jù)，每張表的存儲(chǔ)都是按主鍵順序存放峻厚。

此外還有NDB响蕴，Memory，Archive惠桃，F(xiàn)ederated浦夷，Marai等存儲(chǔ)引擎。

InnoDB架構(gòu)

InnoDB架構(gòu)圖如下

以下主要從內(nèi)存和線程的角度分析InnoDB的架構(gòu)辜王。

內(nèi)存池

主要工作：

• 維護(hù)所有進(jìn)程/線程需要使用的多個(gè)內(nèi)部數(shù)據(jù)結(jié)構(gòu)
• 緩存磁盤上的數(shù)據(jù)劈狐，方便快速地讀取，同時(shí)對(duì)磁盤文件數(shù)據(jù)修改之前在這里緩存
• 重做日志緩存

InnoDB內(nèi)存池主要有以下部分

緩沖池
InnoDB是基于磁盤存儲(chǔ)的呐馆，并將其中的記錄按照頁(yè)的方式進(jìn)行管理懈息。
而緩沖池就是一塊內(nèi)存區(qū)域，主要緩沖數(shù)據(jù)頁(yè)和索引頁(yè)摹恰。
InnoDB中對(duì)頁(yè)的讀取操作辫继，首先判斷該頁(yè)是否在緩沖池中怒见，若在，直接讀取該頁(yè)姑宽，若不在則從磁盤讀取頁(yè)數(shù)據(jù)遣耍，并存放在緩沖池中。
對(duì)頁(yè)的修改操作炮车，首先修改在緩沖池中的頁(yè)舵变，再以一定的頻率（Checkpoint機(jī)制）刷新到磁盤。
參數(shù)：innodb_buffer_pool_size設(shè)置緩沖池大小

緩沖池通過LRU（Latest Recent Used瘦穆，最近最少使用）算法進(jìn)行管理纪隙。最頻繁使用的頁(yè)在LRU列表前端，最少使用的頁(yè)在尾端扛或，當(dāng)緩沖池不能存放新讀取的頁(yè)時(shí)绵咱，首先釋放LRU列表尾端的頁(yè)（頁(yè)數(shù)據(jù)刷新到磁盤，并從緩沖次中刪除）熙兔。
InnoDB對(duì)于新讀取的頁(yè)悲伶，不是放到LRU列表最前端，而是放到midpoint位置（默認(rèn)為5/8處）住涉。
這是因?yàn)橐恍㏒QL操作會(huì)訪問大量的頁(yè)（如全表掃描）麸锉，讀取大量非熱點(diǎn)數(shù)據(jù)，如果直接放到首部舆声，可能導(dǎo)致真正的熱點(diǎn)數(shù)據(jù)被移除花沉。

關(guān)于頁(yè)的概念會(huì)在存儲(chǔ)篇解釋，這里就理解為InnoDB將表數(shù)據(jù)拆分為若干固定大小的頁(yè)媳握，每頁(yè)保存若干表記錄主穗。

重做日志緩存
重做日志先放到這個(gè)緩沖區(qū)，然后按一定頻率刷新到重做日志文件毙芜。
參數(shù)：innodb_log_buffer_size

刷新規(guī)則：

1. Master Thread每秒將一部分重做日志緩沖刷新到重做日志文件
2. 每一事務(wù)提交時(shí)會(huì)將重做日志刷新到重做日志文件（如果配置了）
3. 重做日志緩沖區(qū)使用空間大于1/2

額外的內(nèi)存池
內(nèi)存堆忽媒，對(duì)InnoDB內(nèi)部使用的數(shù)據(jù)結(jié)構(gòu)對(duì)象進(jìn)行管理

Checkpoint機(jī)制

InnoDB對(duì)于對(duì)于DML語句操作（如Update或Delete），事務(wù)提交時(shí)只需在緩沖池中中完成操作腋粥，然后再通過Checkpoint將修改后的臟頁(yè)數(shù)據(jù)刷新到磁盤晦雨。

InnoDB有兩種Checkpoint

1. Sharp Checkpoint：數(shù)據(jù)庫(kù)關(guān)閉是將所有臟頁(yè)刷新會(huì)磁盤
2. Fuzzy Checkpoint：

• Master Thread Checkpoint
  Master Thread每個(gè)1秒或10秒按一定比例將緩存池的臟頁(yè)列表刷新會(huì)磁盤
• FLUSH LRU LIST Checkpoint
  Page Cleaner線程發(fā)現(xiàn)LRU列表中可用頁(yè)數(shù)量少于innodb_lru_scan_depth(1024)，就將LRU列表尾端移>除隘冲，如果這些頁(yè)中有臟頁(yè)闹瞧，就需要Checkpoint
• Async/Sync Flush Checkpoint
  重做日志文件空間不可以用時(shí)，將一部分臟頁(yè)刷新到磁盤展辞。
• Dirty Page too much Checkpoint:
  臟頁(yè)數(shù)量太多（超過比例innodb_max_dirty_pages_pct,默認(rèn)75）奥邮，執(zhí)行Checkpoint。

重做日志

重做日志是為了保證事務(wù)的原子性，持久性洽腺。InnoDB采用Write Ahread Log策略脚粟，事務(wù)提交時(shí)，先寫重做日志蘸朋，再修改頁(yè)核无。
數(shù)據(jù)庫(kù)宕機(jī)重啟時(shí)通過執(zhí)行重做日志恢復(fù)數(shù)據(jù)。
但由于Checkpoint機(jī)制藕坯，數(shù)據(jù)庫(kù)宕機(jī)重啟并不需要重做所有的日志团南，因?yàn)镃heckpoint之前的頁(yè)都刷新到磁盤了，只需執(zhí)行最新一次Checkpoint后的重做日志進(jìn)行恢復(fù)炼彪，這樣可以縮短數(shù)據(jù)庫(kù)的恢復(fù)時(shí)間吐根。

InnoDB中重做日志文件是循環(huán)使用的。當(dāng)頁(yè)被Checkpoint刷新到磁盤后辐马，對(duì)應(yīng)的重做日志就不需要使用 拷橘，其空間可以被覆蓋重用。
如果待寫入的重做日志文件空間不可用（臟頁(yè)還沒有刷新到磁盤）齐疙，就需要強(qiáng)制產(chǎn)生Checkpoint，將緩沖池中的頁(yè)至少刷新到當(dāng)前重做日志的位置旭咽。

InnoDB 1.2.x(MySql 5.6)后贞奋，F(xiàn)LUSH LRU LIST Checkpoint以及Async/Sync Flush Checkpoint操作放到Page Cleaner線程，以免阻塞用戶線程穷绵。

線程

主要作用：

• 負(fù)責(zé)刷新內(nèi)存池中的數(shù)據(jù)轿塔，保證緩沖池的內(nèi)存緩沖的是最近的數(shù)據(jù)
• 已修改的數(shù)據(jù)文件刷新到磁盤文件
• 保證數(shù)據(jù)庫(kù)發(fā)生異常的情況下InnoDB能恢復(fù)到正常狀態(tài)。

InnoDB運(yùn)行時(shí)主要有以下線程
Master Thread
負(fù)責(zé)將緩沖池中的數(shù)據(jù)異步刷新到磁盤仲墨，保證數(shù)據(jù)的一致性勾缭，包括臟頁(yè)的刷新，合并插入緩沖（INSERT BUFFER）目养，UNDO頁(yè)的回收等俩由。

IO Thread
負(fù)責(zé)AIO請(qǐng)求的回調(diào)處理。
參數(shù)：innodb_read_io_threads癌蚁，innodb_write_io_threads

Purge Thread
事務(wù)提交后幻梯，undo log可能不再需要，由Purge Thread負(fù)責(zé)回收并重新分配的這些已經(jīng)使用的undo頁(yè)努释。
注意：Purge Thread需要離散地讀取undo頁(yè)碘梢。

Page Cleaner Thread
InnoDB 1.2.x引入，將Master Threader中刷新臟頁(yè)的工作移至該線程伐蒂，如上面說的FLUSH LRU LIST Checkpoint以及Async/Sync Flush Checkpoint煞躬。

Master Thread

Master Thread具有最高的線程優(yōu)先級(jí)別，內(nèi)部由多個(gè)循環(huán)組成：主循環(huán)（loop），后臺(tái)循環(huán)(backgroup loop)恩沛，刷新循環(huán)(flush loop)在扰，暫停循環(huán)(suspend loop)，Master Thread根據(jù)數(shù)據(jù)庫(kù)運(yùn)行狀態(tài)在以上循環(huán)切換复唤。

Master Thread主要流程偽代碼如下

void master_thread() {
    goto loop;
// 主循環(huán)
loop:
for(int i = 0; i < 10; i++) {
    // 每秒一次操作
    thread_sleep(1)
    // 日志緩沖刷新到磁盤健田，即使這個(gè)事務(wù)沒提交
    do log buffer flush to disk
    // 合并插入緩沖（如果前一秒IO次數(shù)少于5次，InnoDB認(rèn)為IO壓力很小佛纫，執(zhí)行該操作）
    if(last_one_second_ios < 5)
        do merge at most 5 insert buffer
    // 至多刷新100個(gè)InnoDB的臟頁(yè)到磁盤（臟頁(yè)比例超過innodb_max_dirty_pages_pct）   
    if(buf_get_modified_ratio_pct > innodb_max_dirty_pages_pct)
        do buffer poll flush 100 dirty page
    // 沒有用戶活動(dòng)妓局，跳轉(zhuǎn)到   backgroupo loop
    if(no user activity)
        goto backgroupo loop
}   
// 每10秒操作
// 刷新100個(gè)臟頁(yè)到磁盤（過去10秒內(nèi)IO操作小于200次）
if(last_ten_second_ios < 200)
    do buffer pool flush 100 dirty page
// 合并最多5個(gè)插入緩沖
do merge at most 5 insert buffer
// 合并最多5個(gè)插入緩沖
do log buffer flush to disk
// 刪除無用的Undo頁(yè)（最多20個(gè)undo頁(yè)）
do full purge
//臟頁(yè)比例超過innodb_max_dirty_pages_pct，刷新100個(gè)臟頁(yè)到磁盤呈宇，否則刷新10個(gè)臟頁(yè)
if(buf_get_modified_ratio_pct > 70%)
    do buffer pool flush 100 dirty page
else    
    do buffer pool flush 10 dirty page
goto loop

// 后臺(tái)循環(huán)
backgroup loop:
// 刪除無用的Undo
do full purge
// 合并20個(gè)插入緩沖
do merge 20 insert buffer
if not idel:
    goto loop
else 
    goto flush loop

// 刷新循環(huán)
flush loop:
// 刷新100個(gè)臟頁(yè)到磁盤好爬，直到臟頁(yè)比例小于innodb_max_dirty_pages_pct
do buffer pool flush 100 dirty page
if(buf_get_modified_ratio_pct>innodb_max_dirty_pages_pct)
    goto flush loop
goto suspend loop

// 暫停循環(huán)
suspend loop:
// 暫停線程
suspend_thread()
// 等待事件
waiting event;
goto loop;  
}

如上所示，主循環(huán)有兩大操作甥啄，每秒操作和十秒操作存炮。

InnoDB1.0.x優(yōu)化：
在每秒操作中，Master Thread每次最多刷新100個(gè)臟頁(yè)（臟頁(yè)比例超過innodb_max_dirty_pages_pct）蜈漓，合并20個(gè)插入緩沖穆桂，如果在寫入密集的應(yīng)用，處理速度可能太慢了融虽。
從InnoDB 1.0.x開始享完，提供了通過innodb_io_capacity參數(shù)

每秒操作中合并插入緩沖數(shù)量為innodb_io_capacity * 5%
刷新臟頁(yè)數(shù)量為innodb_io_capacity
而默認(rèn)innodb_max_dirty_pages_pct參數(shù)值從90調(diào)整為75

引入以下參數(shù)
innodb_adaptive_flushing：自適應(yīng)刷新
臟頁(yè)比例小于innodb_max_dirty_pages_pct，也會(huì)刷新一定量的臟頁(yè)（由InnoDB控制刷新策略和數(shù)量）
innodb_purge_batch_size：控制每次full purge回收Undo頁(yè)有额，默認(rèn)還是20

InnoDB1.2.x優(yōu)化：

1. InnoDB空閑時(shí)般又，執(zhí)行原來的10秒一次操作，繁忙時(shí)巍佑，執(zhí)行原來的每秒一次操作
2. 刷新臟頁(yè)操作茴迁，分離到單獨(dú)Page Cleaner Thread

InnoDB關(guān)鍵特性

插入緩沖

插入聚集索引一般是順序的，不需要磁盤的隨機(jī)讀取
但插入非聚集索引葉子節(jié)點(diǎn)不是順序的萤衰，需要離散訪問非聚集索引頁(yè)堕义，速度較慢。
對(duì)于非聚集索引的插入或更新脆栋，先判斷插入的非聚集索引頁(yè)是否在緩存池中胳螟，若在，直接插入筹吐，或不在糖耸，先放到一個(gè)Inser Buffer對(duì)象中，
然后根據(jù)一些算法將Insert Buffer緩存的記錄通過后臺(tái)線程慢慢合并刷新回輔助索引丘薛。
插入緩沖將多次插入合并為一次操作嘉竟，減少磁盤的離散操作。

使用Insert Buffer需滿足兩個(gè)條件：
索引是輔助索引
索引不是唯一的（不需要查找索引頁(yè)判斷唯一性）

InnoDB從1.0.x引入Change Buffer，對(duì)INSERT舍扰，DELETE倦蚪，UPDATE都進(jìn)行緩沖。
參數(shù)：innodb_change_buffer_max_size边苹，Change Buffer最多使用緩沖池內(nèi)存空間陵且。

兩次寫 doublewrite

部分寫失效：頁(yè)數(shù)據(jù)寫入到磁盤時(shí)只寫了一部分（如16K數(shù)據(jù)只寫了2K），數(shù)據(jù)庫(kù)就宕機(jī)了个束，導(dǎo)致頁(yè)數(shù)據(jù)損壞慕购，這時(shí)無法使用重做日志恢復(fù)。（執(zhí)行重做日志時(shí)需要利用頁(yè)的一些變量茬底，如checksum）

因此在使用重做日志恢復(fù)數(shù)據(jù)庫(kù)沪悲，需要有一個(gè)頁(yè)的副本，當(dāng)發(fā)生寫失效時(shí)阱表，先通過頁(yè)的副本還原該頁(yè)殿如，再進(jìn)行重做。于是InnoDB實(shí)現(xiàn)了doublewrite技術(shù)最爬。

doublewrite有兩部分涉馁，一部分是內(nèi)存中的doublewrite buffer，大小為2MB爱致，另一部分是磁盤共享表空間連續(xù)的128個(gè)頁(yè)烤送，也是2MB。
doublewrite要求刷新緩沖池的臟頁(yè)時(shí)執(zhí)行以下步驟

1. 通過memcpy函數(shù)將臟頁(yè)復(fù)制到內(nèi)存的doublewrite buffer
2. doublewrite buffer分兩次蒜鸡，每次1MB順序?qū)懭牍蚕肀砜臻g
3. 調(diào)用fsync函數(shù)同步磁盤胯努，避免緩沖寫帶來問題牢裳，確保數(shù)據(jù)刷新到共享表空間（順序?qū)懛攴溃_銷小）
4. 將上述的臟頁(yè)數(shù)據(jù)寫入各個(gè)表空間文件（離散寫）

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

InnoDB會(huì)監(jiān)控對(duì)表上各索引頁(yè)的查詢執(zhí)行情況蒲讯，如發(fā)現(xiàn)建立哈希索引可以提升速度忘朝，則建立哈希索引，這是過程不需要用戶干預(yù)判帮。
參數(shù)：innodb_adaptive_hash_index局嘁，默認(rèn)AHI為開啟狀態(tài)

異步IO

InnoDB使用異步IO操作磁盤，避免同步IO導(dǎo)致阻塞晦墙，也可以進(jìn)行IO Merge操作悦昵，將多個(gè)IO操作合并為一個(gè)IO操作。

刷新鄰接頁(yè)

當(dāng)刷新一個(gè)臟頁(yè)時(shí)晌畅，InnoDB會(huì)檢測(cè)該頁(yè)所在區(qū)的所有頁(yè)但指，如果是臟頁(yè)，一起刷新，這是可以通過AIO將多個(gè)IO寫入操作合并為一個(gè)IO操作棋凳。
參數(shù)：innodb_flush_neighbors拦坠，控制開關(guān)

文件

參數(shù)文件

在MySql實(shí)例啟動(dòng)時(shí)指定某些初始化參數(shù)，如數(shù)據(jù)庫(kù)文件目錄剩岳，內(nèi)存池大小等贞滨。
參看參數(shù)文件所在目錄：mysql --help|grep my.cnf
linux下默認(rèn)目錄為/etc/mysql/my.cnf
/etc/mysql/conf.d/mysql.cnf為客戶端參數(shù)文件
/etc/mysql/mysql.conf.d/mysql.cnf為服務(wù)端參數(shù)文件

參數(shù)類型：

• 動(dòng)態(tài)參數(shù)，可以在MySql實(shí)例運(yùn)行中進(jìn)行更改
  SET [global | session] system_var_name = expr
  SET [@@golbal. | @@session. | @@]system_var_name = expr
• 靜態(tài)參數(shù)：只能在實(shí)例停止時(shí)修改

注意：datadir參數(shù)指定MySql數(shù)據(jù)目錄拍棕，它是數(shù)據(jù)文件晓铆，日志文件默認(rèn)存放目錄。

日志文件

錯(cuò)誤日志
遇到問題應(yīng)該查看該文件以便定位問題莫湘。
參數(shù)：log-error

慢查詢?nèi)罩?/strong>
記錄執(zhí)行時(shí)間超過某一閥值的所有SQL
參數(shù)：
log_slow_queries：記錄慢SQL的開關(guān)
long_query_time：慢SQl的閥值尤蒿，默認(rèn)為10，單位秒
log_queries_not_using_indexes:是否記錄沒有使用索引的查詢
log_throttle_queries_not_using_indexs:每分鐘最多記錄沒使用索引的SQL的數(shù)量
slow-query-log-file:指定目錄幅垮，默認(rèn)在data目錄
log_output：輸出格式腰池，默認(rèn)為FILE，可以配置為TABLE（記錄到slow_log表）
可以使用mysqldumpslow分析慢日志