上篇文章介紹了一條SQL語句在數(shù)據(jù)庫中的執(zhí)行流程，當(dāng)SQL執(zhí)行到存儲(chǔ)引擎這里，因?yàn)椴煌囊娴膶?shí)現(xiàn)機(jī)制有所不同桐经，現(xiàn)在就以使用最廣泛的為InnoDB引擎再來細(xì)化說明Innodb在數(shù)據(jù)查詢和更新流程的細(xì)節(jié)。
上篇: Mysql快速學(xué)習(xí)——《一》: Mysql的基礎(chǔ)架構(gòu)
參考: The InnoDB Storage Engine
思維導(dǎo)圖: mysql
存儲(chǔ)引擎層
存儲(chǔ)引擎層（Storage Engines），它決定了 MySQL 會(huì)怎樣存儲(chǔ)數(shù)據(jù)派桩，怎樣讀取和寫入數(shù)據(jù)，也在很大程度上決定了 MySQL 的讀寫性能和數(shù)據(jù)可靠性蚌斩。
對(duì)于這么重要的一層能力铆惑，MySQL 提供了極強(qiáng)的擴(kuò)展性，你可以定義自己要使用什么樣的存儲(chǔ)引擎：InnoDB送膳、MyISAM鸭津、MEMORY、CSV肠缨，甚至可以自己開發(fā)一個(gè)存儲(chǔ)引擎然后使用它逆趋。
InnoDB存儲(chǔ)引擎
下面是InnoDB 引擎的邏輯架構(gòu)圖, 弄懂它那你對(duì)MYSQL的理解將更進(jìn)一步：
innoDB架構(gòu)圖從上圖可以看到InnoDB分為2大塊:
- In-Memory Structures (內(nèi)存部分)
- On-Disk Structures (磁盤部分)
其中In-Memory Structures (內(nèi)存部分)包括:
- Buffer Pool - 內(nèi)存緩沖池
- Change Buffer - 寫交換緩沖池
- Adaptive Hash Index - 自適應(yīng)哈希索引
- Log Buffer
而On-Disk Structures (磁盤部分), 從架構(gòu)圖可以看到, Tablespaces 分為五種(我們平時(shí)創(chuàng)建的表的數(shù)據(jù)，可以存放到 The System Tablespace 晒奕、File-Per-Table Tablespaces闻书、General Tablespace 三者中的任意一個(gè)地方，具體取決于你的配置和創(chuàng)建表時(shí)的 sql 語句)
- The System Tablespace - 系統(tǒng)表空間
---- 在數(shù)據(jù)庫建立的時(shí)候自動(dòng)創(chuàng)建的脑慧，它包含了整個(gè)數(shù)據(jù)庫的數(shù)據(jù)字典魄眉。- File-Per-Table Tablespaces - 獨(dú)立表空間
---- 是對(duì)The System Tablespace(系統(tǒng)表空間)的一個(gè)更靈活的選擇, 在MySQL 5.6.6和更高版本默認(rèn)啟用的。- General Tablespace - 通用表空間
--- 類似于系統(tǒng)表空間闷袒，常規(guī)表空間是共享表空間坑律，可以存儲(chǔ)多個(gè)表的數(shù)據(jù)。- Undo Tablespaces - 回退表空間
- Temporary Tablespaces
On-Disk Structures (磁盤部分)除了表結(jié)構(gòu)定義和索引囊骤，還有一些為了高性能和高可靠而設(shè)計(jì)的角色晃择，比如
- redo log
- undo log
- Change Buffer
- Doublewrite Buffer
內(nèi)存部分組件詳解
1. Buffer Pool
緩存表數(shù)據(jù)與索引數(shù)據(jù)，把磁盤上的數(shù)據(jù)加載到緩沖池也物，避免每次訪問都進(jìn)行磁盤IO宫屠，起到加速訪問的作用。
InnoDB緩沖池策略
- 按頁(4K)讀取
磁盤讀寫滑蚯，并不是按需讀取浪蹂，而是按頁讀取抵栈，一次至少讀一頁數(shù)據(jù)（一般是4K），如果未來要讀取的數(shù)據(jù)就在頁中坤次，就能夠省去后續(xù)的磁盤IO古劲，提高效率。- “集中讀寫”的原則(預(yù)讀)
數(shù)據(jù)訪問缰猴，通常都遵循“集中讀寫”的原則绢慢，使用一些數(shù)據(jù)，大概率會(huì)使用附近的數(shù)據(jù)洛波，這就是所謂的“局部性原理”胰舆。InnoDB會(huì)把一些“可能要訪問”的頁提前加入緩沖池，避免未來的磁盤IO操作蹬挤。
傳統(tǒng)LRU緩沖池算法
為了減少數(shù)據(jù)移動(dòng)缚窿，LRU一般用鏈表實(shí)現(xiàn)。最常見的玩法是焰扳，把入緩沖池的頁放到LRU的頭部倦零，作為最近訪問的元素，從而最晚被淘汰吨悍。這里又分兩種情況：
（1）頁已經(jīng)在緩沖池里扫茅，那就只做“移至”LRU頭部的動(dòng)作，而沒有頁被淘汰育瓜；
（2）頁不在緩沖池里葫隙，除了做“放入”LRU頭部的動(dòng)作，還要做“淘汰”LRU尾部頁的動(dòng)作躏仇；InnoDB并不直接使用傳統(tǒng)的LRU緩沖池算法, 因?yàn)閭鹘y(tǒng)的LRU緩沖池算法會(huì)出現(xiàn)以下問題:
（1）預(yù)讀失效: 由于預(yù)讀(Read-Ahead)恋脚，提前把頁放入了緩沖池，但最終MySQL并沒有從頁中讀取數(shù)據(jù)焰手，稱為預(yù)讀失效糟描。
（2）緩沖池污染: 當(dāng)某一個(gè)SQL語句，要批量掃描大量數(shù)據(jù)時(shí)书妻，可能導(dǎo)致把緩沖池的所有頁都替換出去船响，導(dǎo)致大量熱數(shù)據(jù)被換出，MySQL性能急劇下降躲履，這種情況叫緩沖池污染见间。
InnoDB對(duì)傳統(tǒng)LRU旳優(yōu)化
預(yù)讀失敗優(yōu)化 - 新老生代機(jī)制
（1）將LRU分為兩個(gè)部分：新生代(new sublist) + 老生代(old sublist)
（2）新老生代收尾相連，即：新生代的尾(tail)連接著老生代的頭(head)崇呵；
（3）新頁（例如被預(yù)讀的頁）加入緩沖池時(shí)缤剧，只加入到老生代頭部
（4）如果數(shù)據(jù)真正被讀认谠（預(yù)讀成功）域慷，才會(huì)加入到新生代的頭部
（5）如果數(shù)據(jù)沒有被讀取，則會(huì)比新生代里的“熱數(shù)據(jù)頁”更早被淘汰出緩沖池
新老生代機(jī)制
緩沖池污染優(yōu)化 - 老生代停留時(shí)間窗口機(jī)制
（1）假設(shè)T=老生代停留時(shí)間窗口；
（2）插入老生代頭部的頁犹褒，即使立刻被訪問抵窒，并不會(huì)立刻放入新生代頭部；
（3）只有滿足“被訪問”并且“在老生代停留時(shí)間”大于T叠骑，才會(huì)被放入新生代頭部李皇；
老生代停留時(shí)間窗口機(jī)制
buffer_pool相關(guān)重要參數(shù)
- innodb_buffer_pool_size
配置緩沖池的大小，在內(nèi)存允許的情況下宙枷，DBA往往會(huì)建議調(diào)大這個(gè)參數(shù)掉房，越多數(shù)據(jù)和索引放到內(nèi)存里，數(shù)據(jù)庫的性能會(huì)越好慰丛。- innodb_old_blocks_pct
老生代占整個(gè)LRU鏈長(zhǎng)度的比例卓囚，默認(rèn)是37，即整個(gè)LRU中新生代與老生代長(zhǎng)度比例是63:37诅病。- innodb_old_blocks_time
老生代停留時(shí)間窗口哪亿，單位是毫秒，默認(rèn)是1000贤笆，即同時(shí)滿足“被訪問”與“在老生代停留時(shí)間超過1秒”兩個(gè)條件蝇棉，才會(huì)被插入到新生代頭部。
2. Change Buffer
寫請(qǐng)求的處理流程
（1）如果索引頁不在buffer pool中, 則先把索引頁芥永，從磁盤加載到緩沖池篡殷，一次磁盤隨機(jī)讀操作；
（2）修改緩沖池中的頁埋涧，一次內(nèi)存操作贴唇；
（3）寫入redo log，一次磁盤順序?qū)懖僮鳎?/p>
寫請(qǐng)求處理
是否會(huì)出現(xiàn)一致性問題呢飞袋？
不會(huì), 因?yàn)?
（1）讀取戳气，會(huì)命中緩沖池的頁；
（2）緩沖池LRU數(shù)據(jù)淘汰巧鸭，會(huì)將“臟頁”刷回磁盤瓶您；
（3）數(shù)據(jù)庫異常奔潰，能夠從redo log中恢復(fù)數(shù)據(jù)纲仍；
利用Change Buffer進(jìn)行優(yōu)化
上述場(chǎng)景中, 被讀取的數(shù)據(jù)沒有命中緩沖池的時(shí)候呀袱，會(huì)先從磁盤索引頁到緩沖池中, 這樣至少產(chǎn)生一次磁盤IO，對(duì)于寫多讀少的業(yè)務(wù)場(chǎng)景郑叠，性能壓力會(huì)劇增, 于是InnoDB引入了Change Buffer:
- 當(dāng)對(duì)頁進(jìn)行了寫操作夜赵，并不會(huì)立刻將磁盤頁加載到緩沖池
- 先把頁的寫操作記錄到緩沖變更池(buffer changes)
- 等未來數(shù)據(jù)被讀取時(shí)，再將數(shù)據(jù)合并(merge)恢復(fù)到緩沖池中
寫緩沖的目的是降低寫操作的磁盤IO乡革，提升數(shù)據(jù)庫性能寇僧。
Change Buffer在內(nèi)存中摊腋，Change Buffer占用Buffer Pool的一部分。在磁盤上嘁傀，Change Buffer是系統(tǒng)表空間的一部分兴蒸，其中的索引會(huì)在關(guān)閉數(shù)據(jù)庫服務(wù)器時(shí)更改。
Change Buffer相關(guān)參數(shù)配置
- 配置Change Pool最大大小
---innodb_change_buffer_max_size: 允許將Change Buffer的最大大小配置為緩沖池總大小的百分比细办。默認(rèn)情況下, innodb_change_buffer_max_size設(shè)置為25.最大設(shè)置為50橙凳。- 配置Change Buffer的適用范圍
---innodb_change_buffering: all | none | inserts | deletes | changes | purges
3. Adaptive Hash Index
AHI是InnoDB索引的索引, 為了在索引很大時(shí)快速得到數(shù)據(jù)。
AHI 在實(shí)現(xiàn)上就是一個(gè)哈希表：從某個(gè)檢索條件到某個(gè)數(shù)據(jù)頁的哈希表笑撞，仿佛并不復(fù)雜岛啸，但其中的關(guān)竅在于哈希表不能太大（哈希表維護(hù)本身就有成本，哈希表太大則成本會(huì)高于收益）茴肥，又不能太小（太小則緩存命中率太低值戳，沒有任何收益）。
AHI建立需要遵循以下約束:
（1）某個(gè)索引樹要被使用足夠多次
（2）該索引樹上的某個(gè)檢索條件要被經(jīng)常使用
（3）該索引樹上的某個(gè)數(shù)據(jù)頁要被經(jīng)常使用
4. Log Buffer
當(dāng)在MySQL中對(duì)InnoDB表進(jìn)行更改時(shí)炉爆，這些更改首先存儲(chǔ)在InnoDB日志緩沖區(qū)的內(nèi)存中堕虹，然后寫入通常稱為重做日志（redo logs）的InnoDB日志文件中。
日志緩沖區(qū)是內(nèi)存存儲(chǔ)區(qū)域芬首，用于保存要寫入磁盤上的日志文件的數(shù)據(jù)赴捞。日志緩沖區(qū)大小由innodb_log_buffer_size 變量定義，默認(rèn)大小為16MB郁稍。
日志緩沖區(qū)的內(nèi)容定期刷新到磁盤赦政。較大的日志緩沖區(qū)可以運(yùn)行大型事務(wù)，而無需在事務(wù)提交之前將重做日志數(shù)據(jù)寫入磁盤耀怜。因此恢着，如果有更新，插入或刪除許多行的事務(wù)财破，則增加日志緩沖區(qū)的大小可以節(jié)省磁盤I/O掰派。
磁盤部分組件詳解
1. redo log
在更新操作時(shí)，會(huì)先更新Buffer Pool中的數(shù)據(jù)然后再去操作磁盤左痢，但是在極端情況下會(huì)出現(xiàn)系統(tǒng)宕機(jī)或者斷電導(dǎo)致磁盤還未更新就丟失了數(shù)據(jù)靡羡，此時(shí)需要把對(duì)內(nèi)存所做的修改寫入到一個(gè)redo log buffer里去，這里也是一個(gè)內(nèi)存緩沖區(qū)俊性，用于存放redo日志的略步。
2. undo log
如果執(zhí)行一個(gè)更新語句，且這個(gè)語句還在事務(wù)里的話定页，在事務(wù)提交以前趟薄，我們都可以選擇回滾，而這部分回滾的數(shù)據(jù)典徊，就是未更新以前的數(shù)據(jù)杭煎，它是保存在undo日志里的恩够。
3. Change Buffer
--
4. Doublewrite Buffer
如果說 Change Buffer 是提升性能，那么 Doublewrite Buffer 就是保證數(shù)據(jù)頁的可靠性岔帽。
怎么理解呢玫鸟？
前面提到過导绷，MySQL 以「頁」為讀取和寫入單位犀勒，一個(gè)「頁」里面有多行數(shù)據(jù)，寫入數(shù)據(jù)時(shí)妥曲，MySQL 會(huì)先寫內(nèi)存中的頁贾费，然后再刷新到磁盤中的頁。
這時(shí)問題來了檐盟，假設(shè)在某一次從內(nèi)存刷新到磁盤的過程中褂萧，一個(gè)「頁」刷了一半，突然操作系統(tǒng)或者 MySQL 進(jìn)程奔潰了葵萎，這時(shí)候导犹，內(nèi)存里的頁數(shù)據(jù)被清除了，而磁盤里的頁數(shù)據(jù)羡忘，刷了一半谎痢，處于一個(gè)中間狀態(tài)，不尷不尬卷雕，可以說是一個(gè)「不完整」节猿，甚至是「壞掉的」的頁。有同學(xué)說漫雕，不是有 Redo Log 么滨嘱？其實(shí)這個(gè)時(shí)候 Redo Log 也已經(jīng)無力回天，Redo Log 是要在磁盤中的頁數(shù)據(jù)是正常的浸间、沒有損壞的情況下太雨，才能把磁盤里頁數(shù)據(jù) load 到內(nèi)存，然后應(yīng)用 Redo Log魁蒜。而如果磁盤中的頁數(shù)據(jù)已經(jīng)損壞躺彬，是無法應(yīng)用 Redo Log 的。
所以梅惯，MySQL 在刷數(shù)據(jù)到磁盤之前宪拥，要先把數(shù)據(jù)寫到另外一個(gè)地方，也就是 Doublewrite Buffer铣减，寫完后她君，再開始寫磁盤。Doublewrite Buffer 可以理解為是一個(gè)備份（recovery）葫哗，萬一真的發(fā)生 crash缔刹，就可以利用 Doublewrite Buffer 來修復(fù)磁盤里的數(shù)據(jù)球涛。
代碼塊
