InnoDB體系架構(gòu)

后臺線程

Master Thread

• Master Thread是一個非常核心的后臺線程，主要負責將緩沖池中的數(shù)據(jù)異步刷新到磁盤许师，保證數(shù)據(jù)的一致性，包括臟頁的刷新僚匆、合并插入緩沖（INSERT BUFFER）微渠、UNDO頁的回收等。

IO Thread

• 在InnoDB存儲引擎中大量使用了AIO（Async IO）來處理寫IO請求咧擂，這樣可以極大提高數(shù)據(jù)庫的性能逞盆。
• IO Thread的工作主要是負責這些IO請求的回調(diào)（call back）處理。
• 通過命令SHOW ENGINE INNODB STATUS來觀察InnoDB中的IO Thread

Purge Thread

• 事務(wù)被提交后松申，其所使用的undolog可能不再需要纳击，因此需要PurgeThread來回收已經(jīng)使用并分配的undo頁。
• InnoDB支持多個Purge Thread攻臀，這樣做的目的是為了進一步加快undo頁的回收。同時由于Purge Thread需要離散地讀取undo頁纱昧，這樣也能更進一步利用磁盤的隨機讀取性能刨啸。
• SHOW VARIABLES LIKE 'innodb_purge_threads'\G

Page Cleaner Thread

• 作用是將之前版本中臟頁的刷新操作都放入到單獨的線程中來完成。而其目的是為了減輕原Master Thread的工作及對于用戶查詢線程的阻塞识脆，進一步提高InnoDB存儲引擎的性能设联。

內(nèi)存

緩沖池

• InnoDB存儲引擎是基于磁盤存儲的，并將其中的記錄按照頁的方式灼捂。
• 緩沖池簡單來說就是一塊內(nèi)存區(qū)域离例，通過內(nèi)存的速度來彌補磁盤速度較慢對數(shù)據(jù)庫性能的影響。
• 數(shù)據(jù)庫中進行讀取頁的操作悉稠，首先將從磁盤讀到的頁存放在緩沖池中宫蛆，這個過程稱為將頁“FIX”在緩沖池中。下一次再讀相同的頁時的猛，首先判斷該頁是否在緩沖池中耀盗。若在緩沖池中想虎，稱該頁在緩沖池中被命中，直接讀取該頁叛拷。否則舌厨，讀取磁盤上的頁。
• 對于InnoDB存儲引擎而言忿薇，其緩沖池的配置通過參數(shù)innodb_buffer_pool_size來設(shè)置裙椭。
• SHOW VARIABLES LIKE'innodb_buffer_pool_size'\G;

#

• 允許有多個緩沖池實例。每個頁根據(jù)哈希值平均分配到不同緩沖池實例中署浩。這樣做的好處是減少數(shù)據(jù)庫內(nèi)部的資源競爭揉燃，增加數(shù)據(jù)庫的并發(fā)處理能力。
• 可以通過參數(shù)innodb_buffer_pool_instances來進行配置瑰抵，該值默認為1
• SHOW VARIABLES LIKE'innodb_buffer_pool_instances'\G;
• show engine innodb status\G;

image.png

可以通過information_schema架構(gòu)下的表INNODB_BUFFER_POOL_STATS來觀察緩沖的狀態(tài)你雌，如運行下列命令可以看到各個緩沖池的使用狀態(tài)

LRU List、Free List和Flush List

• 數(shù)據(jù)庫中的緩沖池是通過LRU（Latest Recent Used二汛，最近最少使用）算法來進行管理的婿崭。即最頻繁使用的頁在LRU列表的前端，而最少使用的頁在LRU列表的尾端肴颊。當緩沖池不能存放新讀取到的頁時氓栈，將首先釋放LRU列表中尾端的頁。
• InnoDB存儲引擎對傳統(tǒng)的LRU算法做了一些優(yōu)化婿着。在InnoDB的存儲引擎中授瘦，LRU列表中還加入了midpoint位置。
• 新讀取到的頁竟宋，雖然是最新訪問的頁提完，但并不是直接放入到LRU列表的首部，而是放入到LRU列表的midpoint位置丘侠。
• midpoint位置可由參數(shù)innodb_old_blocks_pct控制
• SHOW VARIABLES LIKE'innodb_old_blocks_pct'\G;
• 在InnoDB存儲引擎中徒欣，把midpoint之后的列表稱為old列表，之前的列表稱為new列表蜗字〈蚋危可以簡單地理解為new列表中的頁都是最為活躍的熱點數(shù)據(jù)。

那為什么不采用樸素的LRU算法挪捕，直接將讀取的頁放入到LRU列表的首部呢粗梭？

innodb_old_blocks_pct

• 這是因為若直接將讀取到的頁放入到LRU的首部，那么某些SQL操作可能會使緩沖池中的頁被刷新出级零，從而影響緩沖池的效率断医。常見的這類操作為索引或數(shù)據(jù)的掃描操作。這類操作需要訪問表中的許多頁，甚至是全部的頁孩锡，而這些頁通常來說又僅在這次查詢操作中需要酷宵，并不是活躍的熱點數(shù)據(jù)。
• 如果頁被放入LRU列表的首部躬窜，那么非辰娇眩可能將所需要的熱點數(shù)據(jù)頁從LRU列表中移除，而在下一次需要讀取該頁時荣挨，InnoDB存儲引擎需要再次訪問磁盤男韧。
• midpoint位置可由參數(shù)innodb_old_blocks_pct控制
• SHOW VARIABLES LIKE'innodb_old_blocks_pct'\G;
• SET GLOBAL innodb_old_blocks_pct=20;

innodb_old_blocks_time

• InnoDB存儲引擎引入了另一個參數(shù)來進一步管理LRU列表，這個參數(shù)是innodb_old_blocks_time默垄，用于表示頁讀取到mid位置后需要等待多久才會被加入到LRU列表的熱端此虑。
• 因此當需要執(zhí)行上述所說的SQL操作時，可以通過下面的方法盡可能使LRU列表中熱點數(shù)據(jù)不被刷出口锭。
• SET GLOBAL innodb_old_blocks_time=1000;

Free List LRU List

• LRU列表用來管理已經(jīng)讀取的頁朦前，但當數(shù)據(jù)庫剛啟動時，LRU列表是空的鹃操，即沒有任何的頁韭寸。這時頁都存放在Free列表中。
• 當需要從緩沖池中分頁時荆隘，首先從Free列表中查找是否有可用的空閑頁恩伺，若有則將該頁從Free列表中刪除，放入到LRU列表中椰拒。否則晶渠，根據(jù)LRU算法，淘汰LRU列表末尾的頁燃观，將該內(nèi)存空間分配給新的頁褒脯。
• 當頁從LRU列表的old部分加入到new部分時，稱此時發(fā)生的操作為page madeyoung缆毁，
• 而因為innodb_old_blocks_time的設(shè)置而導致頁沒有從old部分移動到new部分的操作稱為page not made young憨颠。
• 可以通過命令SHOW ENGINE INNODB STATUS來觀察LRU列表及Free列表的使用情況和運行狀態(tài)。

image.png

• 當前Buffer pool size共有8191個頁积锅，8191*16kb 128MB
• Free buffers表示當前Free列表中頁的數(shù)量
• Database pages表示LRU列表中頁的數(shù)量
• 可能的情況是Free buffers與Database pages的數(shù)量之和不等于Buffer pool size。
• 因為緩沖池中的頁還可能會被分配給自適應(yīng)哈希索引养盗、Lock信息缚陷、Insert Buffer等頁，而這部分頁不需要LRU算法進行維護往核，因此不存在于LRU列表中箫爷。
• pages made young顯示了LRU列表中頁移動到前端的次數(shù),
• not young為因為innodb_old_blocks_time的設(shè)置而導致頁沒有從old部分移動到new部分的操作次數(shù)
• youngs/s、non-youngs/s表示每秒這兩類操作的次數(shù)

Buffer pool hit rate，表示緩沖池的命中率虎锚，這個例子中為100%硫痰，說明緩沖池運行狀態(tài)非常良好。
通常該值不應(yīng)該小于95%窜护。若發(fā)生Buffer pool hit rate的值小于95%這種情況效斑，用戶需要觀察是否是由于全表掃描引起的LRU列表被污染的問題。

image.png

LRU中的頁包含了unzip_LRU列表中的頁柱徙。
unzip_LRU是怎樣從緩沖池中分配內(nèi)存的

• 首先缓屠，在unzip_LRU列表中對不同壓縮頁大小的頁進行分別管理。
• 其次护侮，通過伙伴算法進行內(nèi)存的分配敌完。
  例如對需要從緩沖池中申請頁為4KB的大小，其過程如下：
• 1）檢查4KB的unzip_LRU列表羊初，檢查是否有可用的空閑頁滨溉；
• 2）若有，則直接使用长赞；
• 3）否則晦攒，檢查8KB的unzip_LRU列表；
• 4）若能夠得到空閑頁涧卵，將頁分成2個4KB頁勤家，存放到4KB的unzip_LRU列表；
• 5）若不能得到空閑頁柳恐，從LRU列表中申請一個16KB的頁伐脖，將頁分為1個8KB的頁、2個4KB的頁乐设，分別存放到對應(yīng)的unzip_LRU列表中讼庇。

image.png

在LRU列表中的頁被修改后，稱該頁為臟頁（dirty page）近尚，即緩沖池中的頁和磁盤上的頁的數(shù)據(jù)產(chǎn)生了不一致

• 這時數(shù)據(jù)庫會通過CHECKPOINT機制將臟頁刷新回磁盤蠕啄，而Flush列表中的頁即為臟頁列表。
• 臟頁既存在于LRU列表中戈锻，也存在于Flush列表中歼跟。LRU列表用來管理緩沖池中頁的可用性，F(xiàn)lush列表用來管理將頁刷新回磁盤格遭，二者互不影響
• Modified db pages 0就顯示了臟頁的數(shù)量哈街。

重做日志緩沖

InnoDB存儲引擎的內(nèi)存區(qū)域除了有緩沖池外，還有重做日志緩沖（redo log buffer）

• InnoDB存儲引擎首先將重做日志信息先放入到這個緩沖區(qū)拒迅，然后按一定頻率將其刷新到重做日志文件骚秦。
• 一般情況下每一秒鐘會將重做日志緩沖刷新到日志文件她倘，因此用戶只需要保證每秒產(chǎn)生的事務(wù)量在這個緩沖大小之內(nèi)即可。該值可由配置參數(shù)innodb_log_buffer_size控制作箍，默認為8MB
• SHOW VARIABLES LIKE'innodb_log_buffer_size'\G

重做日志在下列三種情況下會將重做日志緩沖中的內(nèi)容刷新到外部磁盤的重做日志文件中

• Master Thread每一秒將重做日志緩沖刷新到重做日志文件硬梁；
• 每個事務(wù)提交時會將重做日志緩沖刷新到重做日志文件；
• 當重做日志緩沖池剩余空間小于1/2時胞得，重做日志緩沖刷新到重做日志文件荧止。

額外的內(nèi)存池

InnoDB存儲引擎中，對內(nèi)存的管理是通過一種稱為內(nèi)存堆（heap）的方式進行的懒震。

• 在對一些數(shù)據(jù)結(jié)構(gòu)本身的內(nèi)存進行分配時罩息，需要從額外的內(nèi)存池中進行申請，當該區(qū)域的內(nèi)存不夠時个扰，會從緩沖池中進行申請
• 例如瓷炮，分配了緩沖池（innodb_buffer_pool），但是每個緩沖池中的幀緩沖（frame buffer）還有對應(yīng)的緩沖控制對象（buffer control block）递宅，這些對象記錄了一些諸如LRU娘香、鎖、等待等信息办龄，而這個對象的內(nèi)存需要從額外內(nèi)存池中申請烘绽。

Checkpoint技術(shù)

為了避免發(fā)生數(shù)據(jù)丟失的問題，當前事務(wù)數(shù)據(jù)庫系統(tǒng)普遍都采用了Write Ahead Log策略俐填，即當事務(wù)提交時安接，先寫重做日志，再修改頁英融。當由于發(fā)生宕機而導致數(shù)據(jù)丟失時盏檐，通過重做日志來完成數(shù)據(jù)的恢復。這也是事務(wù)ACID中D（Durability持久性）的要求驶悟。
Checkpoint（檢查點）技術(shù)的目的是解決以下幾個問題：

• 縮短數(shù)據(jù)庫的恢復時間胡野；
• 緩沖池不夠用時，將臟頁刷新到磁盤痕鳍；
• 重做日志不可用時硫豆，刷新臟頁。

當數(shù)據(jù)庫發(fā)生宕機時笼呆，數(shù)據(jù)庫不需要重做所有的日志熊响，因為Checkpoint之前的頁都已經(jīng)刷新回磁盤。故數(shù)據(jù)庫只需對Checkpoint后的重做日志進行恢復诗赌。這樣就大大縮短了恢復的時間汗茄。

當緩沖池不夠用時，根據(jù)LRU算法會溢出最近最少使用的頁境肾，若此頁為臟頁剔难，那么需要強制執(zhí)行Checkpoint，將臟頁也就是頁的新版本刷回磁盤奥喻。

前事務(wù)數(shù)據(jù)庫系統(tǒng)對重做日志的設(shè)計都是循環(huán)使用的

LSN

• 對于InnoDB存儲引擎而言偶宫，其是通過LSN（Log Sequence Number）來標記版本的。而LSN是8字節(jié)的數(shù)字环鲤，其單位是字節(jié)纯趋。
• 每個頁有LSN，重做日志中也有LSN冷离，Checkpoint也有LSN吵冒。
• SHOW ENGINE INNODB STATUS 來觀察 LSN

image.png

在InnoDB存儲引擎內(nèi)部，有兩種Checkpoint西剥，分別為：

• Sharp Checkpoint
• Fuzzy Checkpoint

Sharp Checkpoint發(fā)生在數(shù)據(jù)庫關(guān)閉時將所有的臟頁都刷新回磁盤痹栖，這是默認的工作方式，即參數(shù)innodb_fast_shutdown=1瞭空。

但是若數(shù)據(jù)庫在運行時也使用Sharp Checkpoint揪阿，那么數(shù)據(jù)庫的可用性就會受到很大的影響。故在InnoDB存儲引擎內(nèi)部使用Fuzzy Checkpoint進行頁的刷新咆畏，即只刷新一部分臟頁南捂，而不是刷新所有的臟頁回磁盤

在InnoDB存儲引擎中可能發(fā)生如下幾種情況的Fuzzy Checkpoint：

• Master Thread Checkpoint
• FLUSH_LRU_LIST Checkpoint
• Async/Sync Flush Checkpoint
• Dirty Page too much Checkpoint

Master Thread Checkpoint

對于Master Thread中發(fā)生的Checkpoint，差不多以每秒或每十秒的速度從緩沖池的臟頁列表中刷新一定比例的頁回磁盤旧找。這個過程是異步的溺健，即此時InnoDB存儲引擎可以進行其他的操作，用戶查詢線程不會阻塞钮蛛。

FLUSH_LRU_LIST Checkpoint

• FLUSH_LRU_LIST Checkpoint是因為InnoDB存儲引擎需要保證LRU列表中需要有差不多100個空閑頁可供使用
• 在InnoDB1.1.x版本之前鞭缭，需要檢查LRU列表中是否有足夠的可用空間操作發(fā)生在用戶查詢線程中，顯然這會阻塞用戶的查詢操作愿卒。倘若沒有100個可用空閑頁缚去，那么InnoDB存儲引擎會將LRU列表尾端的頁移除。如果這些頁中有臟頁琼开，那么需要進行Checkpoint易结，而這些頁是來自LRU列表的，因此稱為FLUSH_LRU_LIST Checkpoint柜候。
• 是InnoDB1.2.x版本開始搞动，這個檢查被放在了一個單獨的Page Cleaner線程中進行，并且用戶可以通過參數(shù)innodb_lru_scan_depth控制LRU列表中可用頁的數(shù)量渣刷，該值默認為1024鹦肿，

Async/Sync Flush Checkpoint

• Async/Sync Flush Checkpoint指的是重做日志文件不可用的情況，這時需要強制將一些頁刷新回磁盤辅柴，而此時臟頁是從臟頁列表中選取的箩溃。
• 若將已經(jīng)寫入到重做日志的LSN記為redo_lsn瞭吃，將已經(jīng)刷新回磁盤最新頁的LSN記為checkpoint_lsn，
• checkpoint_age=redo_lsn-checkpoint_lsn
  • async_water_mark=75%*total_redo_log_file_size
  • sync_water_mark=90%*total_redo_log_file_size
  • 若每個重做日志文件的大小為1GB涣旨，并且定義了兩個重做日志文件歪架，則重做日志文件的總大小為2GB。那么async_water_mark=1.5GB霹陡，sync_water_mark=1.8GB和蚪。
  • 當checkpoint_age＜async_water_mark時，不需要刷新任何臟頁到磁盤
  • 當async_water_mark＜checkpoint_age＜sync_water_mark時觸發(fā)Async Flush烹棉，從Flush列表中刷新足夠的臟頁回磁盤攒霹，使得刷新后滿足checkpoint_age＜async_water_mark；
  • checkpoint_age＞sync_water_mark這種情況一般很少發(fā)生浆洗，除非設(shè)置的重做日志文件太小催束，并且在進行類似LOADDATA的BULK INSERT操作。此時觸發(fā)Sync Flush操作辅髓，從Flush列表中刷新足夠的臟頁回磁盤泣崩，使得刷新后滿足checkpoint_age＜async_water_mark。
• 在InnoDB 1.2.x版本之前洛口，Async FlushCheckpoint會阻塞發(fā)現(xiàn)問題的用戶查詢線程矫付，而Sync Flush Checkpoint會阻塞所有的用戶查詢線程，并且等待臟頁刷新完成。
• 從InnoDB 1.2.x版本開始——也就是MySQL 5.6版本，這部分的刷新操作同樣放入到了單獨的Page Cleaner Thread中野蝇，故不會阻塞用戶查詢線程喘批。

image.png

Master Thread工作方式

InnoDB 1.0.x版本之前的Master Thread

Master Thread具有最高的線程優(yōu)先級別。其內(nèi)部由多個循環(huán)（loop）組成：主循環(huán)（loop）、后臺循環(huán)（backgrouploop）、刷新循環(huán)（flush loop）、暫停循環(huán)（suspend loop）脂崔。Master Thread會根據(jù)數(shù)據(jù)庫運行的狀態(tài)在loop、background loop梧喷、flush loop和suspendloop中進行切換

Loop

Loop被稱為主循環(huán)砌左，因為大多數(shù)的操作是在這個循環(huán)中，其中有兩大部分的操作——每秒鐘的操作和每10秒的操作
每秒一次的操作包括：

• 日志緩沖刷新到磁盤铺敌，即使這個事務(wù)還沒有提交（總是）
• 合并插入緩沖（可能）
• 至多刷新100個InnoDB的緩沖池中的臟頁到磁盤（可能）
• 如果當前沒有用戶活動汇歹，則切換到background loop（可能）

即使某個事務(wù)還沒有提交，InnoDB存儲引擎仍然每秒會將重做日志緩沖中的內(nèi)容刷新到重做日志文件偿凭。這一點是必須要知道的产弹，因為這可以很好地解釋為什么再大的事務(wù)提交（commit）的時間也是很短的。

合并插入緩沖（Insert Buffer）并不是每秒都會發(fā)生的弯囊。InnoDB存儲引擎會判斷當前一秒內(nèi)發(fā)生的IO次數(shù)是否小于5次痰哨，如果小于5次胶果，InnoDB認為當前的IO壓力很小，可以執(zhí)行合并插入緩沖的操作斤斧。

刷新100個臟頁也不是每秒都會發(fā)生的稽物。InnoDB存儲引擎通過判斷當前緩沖池中臟頁的比例（buf_get_modified_ratio_pct）是否超過了配置文件中innodb_max_dirty_pages_pct這個參數(shù)（默認為90，代表90%）折欠，如果超過了這個閾值，InnoDB存儲引擎認為需要做磁盤同步的操作吼过，將100個臟頁寫入磁盤中锐秦。

接著來看每10秒的操作，包括如下內(nèi)容：

• 刷新100個臟頁到磁盤（可能的情況下）
• 合并至多5個插入緩沖（總是）
• 將日志緩沖刷新到磁盤（總是）
• 刪除無用的Undo頁（總是）
• 刷新100個或者10個臟頁到磁盤（總是）

InnoDB存儲引擎會先判斷過去10秒之內(nèi)磁盤的IO操作是否小于200次盗忱，如果是酱床，InnoDB存儲引擎認為當前有足夠的磁盤IO操作能力，因此將100個臟頁刷新到磁盤

InnoDB存儲引擎會合并插入緩沖趟佃。不同于每秒一次操作時可能發(fā)生的合并插入緩沖操作扇谣，這次的合并插入緩沖操作總會在這個階段進行。

InnoDB存儲引擎會再進行一次將日志緩沖刷新到磁盤的操作闲昭。這和每秒一次時發(fā)生的操作是一樣的罐寨。

full purge操作，即刪除無用的Undo頁序矩。

• 對表進行update鸯绿、delete這類操作時，原先的行被標記為刪除簸淀，但是因為一致性讀（consistent read）的關(guān)系瓶蝴，需要保留這些行版本的信息。
• 但是在full purge過程中租幕，InnoDB存儲引擎會判斷當前事務(wù)系統(tǒng)中已被刪除的行是否可以刪除舷手，比如有時候可能還有查詢操作需要讀取之前版本的undo信息，如果可以刪除劲绪，InnoDB會立即將其刪除男窟。
• 從源代碼中可以發(fā)現(xiàn)，InnoDB存儲引擎在執(zhí)行full purge操作時珠叔，每次最多嘗試回收20個undo頁蝎宇。

InnoDB存儲引擎會判斷緩沖池中臟頁的比例（buf_get_modified_ratio_pct），如果有超過70%的臟頁祷安，則刷新100個臟頁到磁盤姥芥，如果臟頁的比例小于70%，則只需刷新10%的臟頁到磁盤

background loop

前沒有用戶活動（數(shù)據(jù)庫空閑時）或者數(shù)據(jù)庫關(guān)閉（shutdown）汇鞭，就會切換到這個循環(huán)凉唐。background loop會執(zhí)行以下操作：

• 刪除無用的Undo頁（總是)
• 合并20個插入緩沖（總是）
• 跳回到主循環(huán)（總是）
• 不斷刷新100個頁直到符合條件（可能庸追，跳轉(zhuǎn)到flush loop中完成）

若flush loop中也沒有什么事情可以做了，InnoDB存儲引擎會切換到suspend__loop台囱，將Master Thread掛起淡溯，等待事件的發(fā)生。若用戶啟用（enable）了InnoDB存儲引擎簿训，卻沒有使用任何InnoDB存儲引擎的表咱娶，那么Master Thread總是處于掛起的狀態(tài).

InnoDB1.2.x版本之前的Master Thread

問題：InnoDB存儲引擎最大只會刷新100個臟頁到磁盤，合并20個插入緩沖强品。如果是在寫入密集的應(yīng)用程序中膘侮，每秒可能會產(chǎn)生大于100個的臟頁，如果是產(chǎn)生大于20個插入緩沖的情況的榛，Master Thread似乎會“忙不過來”琼了，或者說它總是做得很慢
解決辦法：提供參數(shù)innodb_io_capacity，用來表示磁盤IO的吞吐量夫晌，默認值為200雕薪。對于刷新到磁盤頁的數(shù)量，會按照innodb_io_capacity的百分比來進行控制.規(guī)則如下：

• 在合并插入緩沖時晓淀，合并插入緩沖的數(shù)量為innodb_io_capacity值的5%所袁；
• 在從緩沖區(qū)刷新臟頁時，刷新臟頁的數(shù)量為innodb_io_capacity凶掰。

問題：參數(shù)innodb_max_dirty_pages_pct默認值的問題纲熏，在InnoDB 1.0.x版本之前，該值的默認為90锄俄，意味著臟頁占緩沖池的90%局劲。但是該值“太大”了，因為InnoDB存儲引擎在每秒刷新緩沖池和flush loop時會判斷這個值奶赠，如果該值大于innodb_max_dirty_pages_pct鱼填，才刷新100個臟頁，如果有很大的內(nèi)存毅戈，或者數(shù)據(jù)庫服務(wù)器的壓力很大苹丸，這時刷新臟頁的速度反而會降低。同樣苇经，在數(shù)據(jù)庫的恢復階段可能需要更多的時間赘理。
解決辦法：從InnoDB 1.0.x版本開始，innodb_max_dirty_pages_pct默認值變?yōu)榱?5扇单，和Google測試的80比較接近商模。這樣既可以加快刷新臟頁的頻率，又能保證了磁盤IO的負載。

InnoDB 1.0.x版本帶來的另一個參數(shù)是innodb_adaptive_flushing（自適應(yīng)地刷新）施流，該值影響每秒刷新臟頁的數(shù)量

• 刷新規(guī)則是：臟頁在緩沖池所占的比例小于innodb_max_dirty_pages_pct時响疚，不刷新臟頁；大于innodb_max_dirty_pages_pct時瞪醋，刷新100個臟頁忿晕。
• 隨著innodb_adaptive_flushing參數(shù)的引入，InnoDB存儲引擎會通過一個名為buf_flush_get_desired_flush_rate的函數(shù)來判斷需要刷新臟頁最合適的數(shù)量银受。粗略地翻閱源代碼后發(fā)現(xiàn)buf_flush_get_desired_flush_rate通過判斷產(chǎn)生重做日志（redo log）的速度來決定最合適的刷新臟頁數(shù)量践盼。因此，當臟頁的比例小于innodb_max_dirty_pages_pct時宾巍，也會刷新一定量的臟頁宏侍。

之前每次進行full purge操作時，最多回收20個Undo頁蜀漆，從InnoDB 1.0.x版本開始引入了參數(shù)innodb_purge_batch_size，該參數(shù)可以控制每次full purge回收的Undo頁的數(shù)量咱旱。該參數(shù)的默認值為20确丢，并可以動態(tài)地對其進行修改

從InnoDB 1.0.x開始，命令SHOW ENGINE INNODB STATUS可以查看當前Master Thread的狀態(tài)信息

InnoDB 1.2.x版本的Master Thread

對于刷新臟頁的操作吐限，從Master Thread線程分離到一個單獨的Page Cleaner Thread鲜侥，從而減輕了Master Thread的工作，同時進一步提高了系統(tǒng)的并發(fā)性诸典。
InnoDB存儲引擎的關(guān)鍵特性包括：

• 插入緩沖（Insert Buffer）
• 兩次寫（Double Write）
• 自適應(yīng)哈希索引（Adaptive Hash Index）
• 異步IO（Async IO）
• 刷新鄰接頁（Flush Neighbor Page）

插入緩沖

Insert Buffer

• InnoDB存儲引擎開創(chuàng)性地設(shè)計了Insert Buffer描函，對于非聚集索引的插入或更新操作，不是每一次直接插入到索引頁中狐粱，而是先判斷插入的非聚集索引頁是否在緩沖池中舀寓，若在，則直接插入肌蜻；若不在互墓，則先放入到一個Insert Buffer對象中，好似欺騙蒋搜。
• 數(shù)據(jù)庫這個非聚集的索引已經(jīng)插到葉子節(jié)點篡撵，而實際并沒有，只是存放在另一個位置豆挽。
• 然后再以一定的頻率和情況進行Insert Buffer和輔助索引頁子節(jié)點的merge（合并）操作育谬，這時通常能將多個插入合并到一個操作中（因為在一個索引頁中），這就大大提高了對于非聚集索引插入的性能帮哈。

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

• 索引是輔助索引（secondary index）膛檀；
• 索引不是唯一（unique）的

image.png

• seg size顯示了當前Insert Buffer的大小
• free list len代表了空閑列表的長度
• size代表了已經(jīng)合并記錄頁的數(shù)量。
• Inserts代表了插入的記錄數(shù)
• merges代表合并的次數(shù)，也就是實際讀取頁的次數(shù)

目前Insert Buffer存在一個問題是：在寫密集的情況下宿刮，插入緩沖會占用過多的緩沖池內(nèi)存（innodb_buffer_pool）互站，默認最大可以占用到1/2的緩沖池內(nèi)存。

Change Buffer

InnoDB存儲引擎可以對DML操作——INSERT僵缺、DELETE胡桃、UPDATE都進行緩沖，他們分別是：Insert Buffer磕潮、Delete Buffer翠胰、Purge buffer。
當然和之前Insert Buffer一樣自脯，Change Buffer適用的對象依然是非唯一的輔助索引之景。

對一條記錄進行UPDATE操作可能分為兩個過程：

• 將記錄標記為已刪除；
• 真正將記錄刪除膏潮。

因此Delete Buffer對應(yīng)UPDATE操作的第一個過程锻狗，即將記錄標記為刪除。Purge Buffer對應(yīng)UPDATE操作的第二個過程焕参，即將記錄真正的刪除

• InnoDB存儲引擎提供了參數(shù)innodb_change_buffering轻纪，用來開啟各種Buffer的選項。
• 該參數(shù)可選的值為：inserts叠纷、deletes刻帚、purges、changes涩嚣、all崇众、none。inserts航厚、deletes顷歌、purges就是前面討論過的三種情況
• changes表示啟用inserts和deletes，all表示啟用所有幔睬，none表示都不啟用衙吩。該參數(shù)默認值為all。

可以通過參數(shù)innodb_change_buffer_max_size來控制Change Buffer最大使用內(nèi)存的數(shù)量

image.png

顯示了merged operations和discarded operation溪窒，并且下面具體顯示Change Buffer中每個操作的次數(shù)坤塞。

• insert表示Insert Buffer；
• delete mark表示Delete Buffer澈蚌；
• delete表示Purge Buffer摹芙；
• discarded operations表示當Change Buffer發(fā)生merge時，表已經(jīng)被刪除宛瞄，此時就無需再將記錄合并（merge）到輔助索引中了浮禾。

Insert Buffer的內(nèi)部實現(xiàn)

• Insert Buffer的數(shù)據(jù)結(jié)構(gòu)是一棵B+樹
• 現(xiàn)在版本本中交胚，全局只有一棵Insert BufferB+樹，負責對所有的表的輔助索引進行Insert Buffer盈电。而這棵B+樹存放在共享表空間中蝴簇，默認也就是ibdata1中
• 試圖通過獨立表空間ibd文件恢復表中數(shù)據(jù)時，往往會導致CHECK TABLE失敗匆帚。這是因為表的輔助索引中的數(shù)據(jù)可能還在Insert Buffer中熬词，也就是共享表空間中，所以通過ibd文件進行恢復后吸重，還需要進行REPAIR TABLE操作來重建表上所有的輔助索引互拾。
• 非葉節(jié)點存放的是查詢的search key（鍵值）
• image.png
  • search key一共占用9個字節(jié)，
  • 其中space表示待插入記錄所在表的表空間id嚎幸，在InnoDB存儲引擎中颜矿，每個表有一個唯一的space id，可以通過space id查詢得知是哪張表嫉晶，space占用4字節(jié)
  • marker占用1字節(jié)骑疆，它是用來兼容老版本的Insert Buffer
  • offset表示頁所在的偏移量，占用4字節(jié)
• 當一個輔助索引要插入到頁（space替废，offset）時箍铭，如果這個頁不在緩沖池中，那么InnoDB存儲引擎首先根據(jù)上述規(guī)則構(gòu)造一個search key舶担，接下來查詢Insert Buffer這棵B+樹，然后再將這條記錄插入到Insert Buffer B+樹的葉子節(jié)點中
• 對于插入到Insert Buffer B+樹葉子節(jié)點的記錄（如圖2-4所示）彬呻，并不是直接將待插入的記錄插入衣陶，而是需要根據(jù)如下的規(guī)則進行構(gòu)造：
• image.png
  • space、marker闸氮、page_no字段和之前非葉節(jié)點中的含義相同剪况，一共占用9字節(jié)。第4個字段metadata占用4字節(jié)蒲跨，其存儲的內(nèi)容如表
  • image.png
  • IBUF_REC_OFFSET_COUNT是保存兩個字節(jié)的整數(shù)译断，用來排序每個記錄進入Insert Buffer的順序
  • 從Insert Buffer葉子節(jié)點的第5列開始，就是實際插入記錄的各個字段了或悲。因此較之原插入記錄孙咪，Insert Buffer B+樹的葉子節(jié)點記錄需要額外13字節(jié)的開銷。
  • 因為啟用Insert Buffer索引后巡语，輔助索引頁（space翎蹈，page_no）中的記錄可能被插入到Insert Buffer B+樹中，所以為了保證每次Merge Insert Buffer頁必須成功男公，還需要有一個特殊的頁用來標記每個輔助索引頁（space荤堪，page_no）的可用空間。這個頁的類型為Insert Buffer Bitmap。
  • 每個Insert Buffer Bitmap頁用來追蹤16384個輔助索引頁澄阳，也就是256個區(qū)（Extent）拥知。每個Insert Buffer Bitmap頁都在16384個頁的第二個頁中。
• image.png

Merge Insert Buffer

Merge Insert Buffer的操作可能發(fā)生在以下幾種情況下：

• 輔助索引頁被讀取到緩沖池時碎赢；
• Insert Buffer Bitmap頁追蹤到該輔助索引頁已無可用空間時低剔；
• Master Thread

第一種情況為當輔助索引頁被讀取到緩沖池中時，例如這在執(zhí)行正常的SELECT查詢操作揩抡，

• 這時需要檢查Insert Buffer Bitmap頁户侥，然后確認該輔助索引頁是否有記錄存放于Insert Buffer B+樹中。
• 若有峦嗤，則將Insert Buffer B+樹中該頁的記錄插入到該輔助索引頁中蕊唐。
• 可以看到對該頁多次的記錄操作通過一次操作合并到了原有的輔助索引頁中，因此性能會有大幅提高

第二種情況Insert Buffer Bitmap頁用來追蹤每個輔助索引頁的可用空間烁设，并至少有1/32頁的空間替梨。

• 若插入輔助索引記錄時檢測到插入記錄后可用空間會小于1/32頁，則會強制進行一個合并操作装黑，即強制讀取輔助索引頁副瀑，將Insert Buffer B+樹中該頁的記錄及待插入的記錄插入到輔助索引頁中。

在Master Thread線程中每秒或每10秒會進行一次Merge Insert Buffer的操作恋谭，不同之處在于每次進行merge操作的頁的數(shù)量不同糠睡。

對于Insert Buffer頁的選擇，InnoDB存儲引擎并非采用這個方式疚颊，它隨機地選擇Insert Buffer B+樹的一個頁狈孔，讀取該頁中的space及之后所需要數(shù)量的頁。該算法在復雜情況下應(yīng)有更好的公平性均抽。

若進行merge時其掂，要進行merge的表已經(jīng)被刪除油挥，此時可以直接丟棄已經(jīng)被Insert/Change Buffer的數(shù)據(jù)記錄。

兩次寫

doublewrite（兩次寫）帶給InnoDB存儲引擎的是數(shù)據(jù)頁的可靠性款熬。

image.png

doublewrite由兩部分組成深寥，

• 一部分是內(nèi)存中的doublewrite buffer，大小為2MB贤牛，
• 另一部分是物理磁盤上共享表空間中連續(xù)的128個頁翩迈，即2個區(qū)（extent），大小同樣為2MB

doublewrite 寫入過程

• 在對緩沖池的臟頁進行刷新時盔夜，并不直接寫磁盤负饲，而是會通過memcpy函數(shù)將臟頁先復制到內(nèi)存中的doublewrite buffer堤魁，之后通過doublewrite buffer再分兩次，每次1MB順序地寫入共享表空間的物理磁盤上返十，然后馬上調(diào)用fsync函數(shù)妥泉，同步磁盤，避免緩沖寫帶來的問題洞坑。
• 在這個過程中盲链，因為doublewrite頁是連續(xù)的，因此這個過程是順序?qū)懙某僭樱_銷并不是很大刽沾。
• 在完成doublewrite頁的寫入后，再將doublewrite buffer中的頁寫入各個表空間文件中排拷，此時的寫入則是離散的

查看doublewrite 運行情況

image.png

如果發(fā)現(xiàn)系統(tǒng)在高峰時的Innodb_dblwr_pages_written:Innodb_dblwr_writes遠小于64∶1监氢，那么可以說明系統(tǒng)寫入壓力并不是很高。

如果操作系統(tǒng)在將頁寫入磁盤的過程中發(fā)生了崩潰纵揍，在恢復過程中泽谨，InnoDB存儲引擎可以從共享表空間中的doublewrite中找到該頁的一個副本吧雹，將其復制到表空間文件吮炕，再應(yīng)用重做日志

參數(shù)skip_innodb_doublewrite可以禁止使用doublewrite功能，這時可能會發(fā)生前面提及的寫失效問題悍抑。不過如果用戶有多個從服務(wù)器（slave server）搜骡，需要提供較快的性能（如在slaves erver上做的是RAID0）记靡，也許啟用這個參數(shù)是一個辦法摸吠。不過對于需要提供數(shù)據(jù)高可靠性的主服務(wù)器（master server）呀洲，任何時候用戶都應(yīng)確保開啟doublewrite

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

InnoDB存儲引擎會監(jiān)控對表上各索引頁的查詢道逗。如果觀察到建立哈希索引可以帶來速度提升滓窍，則建立哈希索引贰您，稱之為自適應(yīng)哈希索引（Adaptive Hash Index，AHI）
AHI是通過緩沖池的B+樹頁構(gòu)造而來杠园，因此建立的速度很快抛蚁，而且不需要對整張表構(gòu)建哈希索引瞧甩。

AHI構(gòu)建要求

• 對這個頁的連續(xù)訪問模式必須是一樣的。
• 以該模式訪問了100次
• 頁通過該模式訪問了N次彬坏，其中N=頁中記錄*1/1

根據(jù)InnoDB存儲引擎官方的文檔顯示务冕，啟用AHI后臊旭，讀取和寫入速度可以提高2倍巍扛，輔助索引的連接操作性能可以提高5倍。毫無疑問喊括，AHI是非常好的優(yōu)化模式府喳，其設(shè)計思想是數(shù)據(jù)庫自優(yōu)化的（self-tuning）钝满，即無需DBA對數(shù)據(jù)庫進行人為調(diào)整弯蚜。

image.png

可以看到AHI的使用信息了，包括AHI的大小收厨、使用情況诵叁、每秒使用AHI搜索的情況。

• 值得注意的是势腮，哈希索引只能用來搜索等值的查詢，如SELECT*FROM table WHERE index_col='xxx’盲厌。
• 而對于其他查找類型建芙，如范圍查找，是不能使用哈希索引的，因此這里出現(xiàn)了non-hash searches/s的情況映砖。
• 通過hash searches:non-hash searches可以大概了解使用哈希索引后的效率邑退。

由于AHI是由InnoDB存儲引擎控制的，因此這里的信息只供用戶參考乓土。不過用戶可以通過觀察SHOW ENGINE INNODB STATUS的結(jié)果及參數(shù)innodb_adaptive_hash_index來考慮是禁用或啟動此特性趣苏，默認AHI為開啟狀態(tài)

異步IO

為了提高磁盤操作性能，當前的數(shù)據(jù)庫系統(tǒng)都采用異步IO（Asynchronous IO彬伦，AIO）的方式來處理磁盤操作

• 用戶可以在發(fā)出一個IO請求后立即再發(fā)出另一個IO請求曹宴，當全部IO請求發(fā)送完畢后区转，等待所有IO操作的完成侄泽，這就是AIO悼尾。
• AIO的另一個優(yōu)勢是可以進行IO Merge操作，也就是將多個IO合并為1個IO，這樣可以提高IOPS的性能活翩。

InnoDB 1.1.x開始（InnoDB Plugin不支持）沮焕，提供了內(nèi)核級別AIO的支持，稱為Native AIO旦事。

參數(shù)innodb_use_native_aio用來控制是否啟用Native AIO魁巩，在Linux操作系統(tǒng)下，默認值為ON：

用戶可以通過開啟和關(guān)閉Native AIO功能來比較InnoDB性能的提升姐浮。官方的測試顯示谷遂，啟用Native AIO，恢復速度可以提高75%卖鲤。

刷新鄰接頁

刷新一個臟頁時肾扰，InnoDB存儲引擎會檢測該頁所在區(qū)（extent）的所有頁，如果是臟頁蛋逾，那么一起進行刷新条摸。這樣做的好處顯而易見，通過AIO可以將多個IO寫入操作合并為一個IO操作间驮，故該工作機制在傳統(tǒng)機械磁盤下有著顯著的優(yōu)勢

InnoDB存儲引擎從1.2.x版本開始提供了參數(shù)innodb_flush_neighbors，用來控制是否啟用該特性谍肤。對于傳統(tǒng)機械硬盤建議啟用該特性内地，而對于固態(tài)硬盤有著超高IOPS性能的磁盤，則建議將該參數(shù)設(shè)置為0箕肃，即關(guān)閉此特性吟宦。

啟動宛篇、關(guān)閉與恢復

在關(guān)閉時，參數(shù)innodb_fast_shutdown影響著表的存儲引擎為InnoDB的行為须妻。該參數(shù)可取值為0儡湾、1摊崭、2蛤迎，默認值為1。

• 0表示在MySQL數(shù)據(jù)庫關(guān)閉時逗威，InnoDB需要完成所有的full purge和merge insert buffer岔冀，并且將所有的臟頁刷新回磁盤罐呼。這需要一些時間登馒，有時甚至需要幾個小時來完成。如果在進行InnoDB升級時，必須將這個參數(shù)調(diào)為0谜悟，然后再關(guān)閉數(shù)據(jù)庫话肖。
• 1是參數(shù)innodb_fast_shutdown的默認值，表示不需要完成上述的full purge和merge insert buffer操作葡幸，但是在緩沖池中的一些數(shù)據(jù)臟頁還是會刷新回磁盤最筒。
• 2表示不完成full purge和merge insert buffer操作，也不將緩沖池中的數(shù)據(jù)臟頁寫回磁盤蔚叨，而是將日志都寫入日志文件床蜘。這樣不會有任何事務(wù)的丟失辙培，但是下次MySQL數(shù)據(jù)庫啟動時，會進行恢復操作（recovery）邢锯。

參數(shù)innodb_force_recovery影響了整個InnoDB存儲引擎恢復的狀況扬蕊。該參數(shù)值默認為0，代表當發(fā)生需要恢復時丹擎，進行所有的恢復操作尾抑，當不能進行有效恢復時，如數(shù)據(jù)頁發(fā)生了corruption蒂培，MySQL數(shù)據(jù)庫可能發(fā)生宕機（crash）再愈，并把錯誤寫入錯誤日志中去

參數(shù)innodb_force_recovery還可以設(shè)置為6個非零值：1～6。大的數(shù)字表示包含了前面所有小數(shù)字表示的影響护戳。具體情況如下：

• 1(SRV_FORCE_IGNORE_CORRUPT)：忽略檢查到的corrupt頁翎冲。
• 2(SRV_FORCE_NO_BACKGROUND)：阻止Master Thread線程的運行，如Master Thread線程需要進行full purge操作灸异，而這會導致crash府适。
• 3(SRV_FORCE_NO_TRX_UNDO)：不進行事務(wù)的回滾操作羔飞。
• 4(SRV_FORCE_NO_IBUF_MERGE)：不進行插入緩沖的合并操作肺樟。
• 5(SRV_FORCE_NO_UNDO_LOG_SCAN)：不查看撤銷日志（Undo Log），InnoDB存儲引擎會將未提交的事務(wù)視為已提交逻淌。
• 6(SRV_FORCE_NO_LOG_REDO)：不進行前滾的操作

需要注意的是么伯，在設(shè)置了參數(shù)innodb_force_recovery大于0后，用戶可以對表進行select卡儒、create和drop操作田柔，但insert、update和delete這類DML操作是不允許的骨望。