第二章 innodb存儲引擎 · MYSQL體系知識點詳記

## innodb * 第一個完整支持ACID事務的MySQL存儲引擎（BDB是第一個支持事務的MySQL存儲引擎,現在已停止開發）。 #### 特性： * 行鎖設計，支持mvcc ，支持外鍵，提供一致性非鎖定讀，同時被設計用來最有效地利用以及試用內存和cpu。 #### 體系架構： innodb 存儲引擎有多個內存塊，可以認為這些內存快組成了一個大的內存池。 * 維護所有進程/線程需要訪問的多個內部數據結構。 * 緩存磁盤上的數據，方便快速地讀取，同時在對磁盤文件的數據修改之前在這里緩存。 * 重做日志（redo log）緩沖 #### innodb 后臺線程： ###### Master Thread : * 是一個非常核心的后臺線程，主要負責將緩沖池中的數據異步刷新到磁盤，保證數據的一致性，包括臟頁的刷新、合并插入緩沖（INSERT BUFFER），undo頁的回收等。 ###### IO Thread : * 在InnoDB存儲引擎中大量使用了AIO（Async IO）來處理寫IO請求，這樣可以極大提高數據庫的性能。 * IO Thread的工作主要是負責這些IO請求的回調（call back）處理。 * InnoDB 1.0版本之前共有4個IO Thread，分別是write、read、insert buffer和log IO thread。 * 在Linux平臺下，IO Thread的數量不能進行調整，但是在Windows平臺下可以通過參數innodb_file_io_thread來增大IO Thread。 ###### PURGE Thread : * 事務被提交后，其所使用的undolog可能不再需要，因此需要PurgeThread來回收已經使用并分配的undo頁。 * 在InnoDB 1.1版本之前，purge操作僅在InnoDB存儲引擎的Master Thread中完成。而從InnoDB 1.1版本開始，purge操作可以獨立到單獨的線程中進行，以此來減輕Master Thread的工作，從而提高CPU的使用率以及提升存儲引擎的性能。 ###### Page Cleaner Thread： * Page Cleaner Thread 是在InnoDB 1.2.x版本中引入的。其作用是將之前版本中臟頁的刷新操作都放入到單獨的線程中來完成。而其目的是為了減輕原Master Thread的工作及對于用戶查詢線程的阻塞，進一步提高InnoDB存儲引擎的性能。 ### 內存: #### 緩沖池： * 緩沖池簡單來說就是一塊內存區域，通過內存的速度來彌補磁盤速度較慢對數據庫性能的影響。 * **InnoDB存儲引擎是基于磁盤存儲的，并將其中的記錄按照頁的方式進行管理。因此可將其視為基于磁盤的數據庫系統（Disk-base Database）** * 在數據庫總進行讀取頁的操作首先將從磁盤獨到的頁存放在緩沖池中，這個過程叫將**頁FIX 在緩沖池中**。下一次再讀相同頁時，首先判斷該頁是否在緩沖池中。若在緩沖池中，稱**該頁在緩沖池中命中，直接讀取該頁。否則，讀取磁盤上的頁。** * 對于數據庫中頁的修改操作，首先修改在緩沖池的頁，頁從緩沖池刷新回磁盤的操作并不是在每次頁發生更新時觸發，而是通過一種稱為**checkpoint的機制刷新回磁盤**。同樣這樣是為了提高數據庫的整體性能。其緩沖池的配置通過參數innodb_buffer_pool_size來設置。 * 緩沖池中緩存的數據頁類型有：索引頁、數據頁、undo頁、插入緩沖（insert buffer）、自適應哈希索引（adaptive hash index）、InnoDB存儲的鎖信息（lock info）、數據字典信息（data dictionary）等 * 一個數據庫允許有多個緩沖池實例（innodb 1.0 x開始）。每個頁根據哈希值平均分配到沖池實例中。這樣做的好處是減少數據庫內部的資源競爭，增加數據庫的并發處理能力。可以通過參數innodb_buffer_pool_instances來進行配置，默認為1。 #### 緩沖池管理：數據庫中的緩沖池是通過LRU（Latest Recent Used，最近最少使用）算法來進行管理的。即最頻繁使用的頁在lru列表的前端，而最少使用的在lru列表的末端。當緩沖池不能存放新讀取的頁時，將首先釋放lru列表中尾端的頁。 * InnoDB存儲引擎中，**緩沖池中頁的大小默認為16KB**，同樣使用LRU算法對緩沖池進行管理 * 在InnoDB存儲引擎中，**LRU列表中加入midpoint位置。新讀取到的頁**，雖然是最新訪問的頁，但并不是直接放入到LRU列表的首部，**而是放到LRUmidpoint的位置**，這個算法在innoDB存儲引擎下稱為Midpoint insertion strategy。默認配置在lru列表長度的5/8處。 * 在InnoDB存儲引擎中，把midpoint之后的列表稱為old列表，之前的列表稱為new列表。可以簡單地理解為new列表中的頁都是最為活躍的熱點數據。 * InnoDB存儲引擎引入了另一個參數來進一步管理LRU列表，這個參數是innodb_old_blocks_time，用于表示頁讀取到mid位置后需要等待多久才會被加入到LRU列表的熱端 * 當頁從LRU列表中的old部分加入到new部分時，成為**page made young** 而因為innodb_old_blocks_time的設置而導致頁沒有從old部分移到new部分的操作為**page not made young** * innodb引擎自1.0之后開始支持壓縮頁的功能，即原本16kb的頁壓縮成1kb，2kb，4kb，8kb。對于非16KB的頁通過unzip_LRU列表來進行管理。 * 在LRU列表中的頁被修改后，稱該頁為臟頁，即緩沖池中的頁和磁盤上的頁數據產生了不一致。這時數據庫通過checkpoint機制講臟頁刷新回磁盤，而flush列表中的頁即為臟頁列表。臟頁既存在于LRU列表中，也存在與FLUSH列表中。LRU列表時用來管理緩沖池中頁的可用性，FLUSH列表用來管理將頁刷新回磁盤，二者互不影響。 #### 緩沖池利用率： * 這里還有一個重要的觀察變量——Buffer pool hit rate，表示緩沖池的命中率，這個例子中為100%，說明緩沖池運行狀態非常良好。通常該值不應該小于95%。若發生Buffer pool hit rate的值小于95%這種情況，用戶需要觀察是否是由于全表掃描引起的LRU列表被污染的問題。 * 還可以通過表INNODB_BUFFER_PAGE_LRU來觀察每個LRU列表中每個頁的具體信息 * InnoDB存儲引擎從1.0.x版本開始支持壓縮頁的功能，即將原本16KB的頁壓縮為1KB、2KB、4KB和8KB ### 重組日志緩沖（redo log buffer） innodb的內存區除了緩沖池外，還有重做日志緩沖（redo log buffer）。 innodb首先將重做日志放入到這個緩沖區，然后按一定過得頻率將其刷新到重做日志文件。重做日志緩沖一般不需要很大，一般每秒鐘會將緩沖刷新到日志文件。緩沖大小通過innodb_log_buffer_size控制，默認為8MB。 * 為了避免數據庫宕機時數據丟失的問題，當前數據庫系統普遍采用 write ahead log 策略，**即當事務提交時，先寫重寫日志，再修改頁**。當由于發生宕機而導致數據丟失師，可以通過重做日志來完成數據的修改。這也是ACID中的D（持久性）的要求。 * 緩沖池刷新到日志文件的三種情況： - master thread 每一秒將重做日志緩沖刷新到重做日志文件 - 每個事務提交時會將日志緩沖刷新到重做日志文件 - 當重做日志緩沖剩余空間小于1/2時 * 在innodb存儲引擎中，對內存的管理是通過一種稱為內存堆（heap）的方式進行的，當該區域的內存不夠時，會從緩沖池中進行個申請。 ### checkpoint技術 * checkpoint（檢查點）技術解決的問題： - 縮短數據庫恢復時間 - 緩沖池不夠用時，將臟頁刷新到新磁盤。 - 重做日志不可用時，刷新臟也。 * 當數據庫發生宕機的時候，數據庫不需要重做所有的日志，因為checkpoint之前的頁都已經刷新回磁盤。故數據庫只需對chckpoint后的重做日志進行恢復。這樣就大大縮短恢復時間。 * 在innodb中通過lsn（log sequence number）來標記版本的。lsn是八字節的數字。 * checkpoint分為兩種： - **sharp checkpoint**：（默認設置）當數據庫關閉時將所有頁刷新磁盤 (參數：innodb_fast_shutdown=1) - **fuzzy checkpoint**：只刷新一部分臟頁回磁盤 innodb在運行時采用fuzzy checkpoin進頁的刷新。 * fuzzy checkpoint應用情況： - **master thread checkpoint ：**差不多已每秒或每十多秒的速度從緩沖池的臟頁列表中刷新一定比例的頁回磁盤。這個過程是異步的，用戶查詢線程不會阻塞。 - **Flush_LRU_List checkpoint ：** innodb存儲引擎需要保證LRU列表中需要保證有差不多100個空閑頁可供使用。 - **Asyn/Sync flush chceckpoint ：** 重做日志不可用的情況，需要強制將一些頁刷新會磁盤。5.6之前同步（async）會阻塞發現問題的用戶查詢線程， sync flush 會阻塞所有的用戶插敘線程。 5.6之后刷新的操作放到單獨的page cleaner thread中故不會阻塞用戶查詢線程 - **dirty page too much checkpoint ：** 臟頁太多 innodb 存儲引擎強制刷新checkpoint。也是為了保證緩沖池有足夠可用的頁。 ##### Master Thread 工作方式 master thread 具有最高的線程優先級別。其內部由多個循環組成：主循環，后臺循環，刷新循環，暫停循環。 master Thread 會根據數據庫運行的狀態在loop，background loop ，flushloop 和suspend loop 中進行切換。 * **loop 主循環：** 包括兩大部分的操作(大概情況負載大的情況下肯個有延遲）: 每秒鐘的操作每十秒鐘的操作。 - 每秒的操作 * 日志緩沖刷新到磁盤，即使這個事務還沒提交 [總是] —————（innodb存儲引擎仍然每秒將重做日志緩沖中的內容刷新到重做日志文件，這也是為什么在大的事務提交時間也是很短的原因） * 合并插入緩沖 [可能] —————（并不是每秒都發生。innodb會判斷前一秒內發生的io次數是否小于五次，如果小于五次，innodb會認為當前的io壓力很小，可以執行合并插入緩沖的操作） * 至多刷新100個innodb的緩池中的臟頁到磁盤（可能） * 如果當前沒有用戶活動，則切換到background loop（可能） - 每十秒的操作： * 刷新100個臟頁到磁盤 [可能] * 合并至多五個插入緩沖 [總是] * 將日志緩沖刷新到磁盤 [總是] * 刪除無用的undo頁 [總是] * 刷新一百個或者10個臟頁到磁盤 [總是] * 以上過程中，innodb會判斷過去10秒之內磁盤的io操作是否小于200次，存儲引擎認為當前有足夠的io操作能力，因此將100個臟頁刷新到磁盤。 * **background loop :** 若當前沒有用戶活動（數據庫空閑時）或者數據庫關閉，就會切到這個循環。 * background會執行以下操作： - 刪除無用的undo頁 [總是] - 合并20個插入緩沖 [總是] - 跳回到主循環 [總是] - 不斷刷新100個頁直到符合條件（可能，跳到flush loop完成） * 若flush loop中也沒有上面事情可以做，innodb會切換到suspend loop,將Master Thread掛起，等待事件的發生。 * innodb1.0版本帶來一個參數是innodb_adaptive_flsuhing (自適應地刷新)，該值影響每秒刷新臟頁的數量。以前版本的刷新規則是臟頁在緩沖池所占的比例小于innodb_max_dirty_pages_pcts時，不刷新臟頁。 * innodb1.2中將刷新臟頁的操作從master thread中分離到一個單獨的page cleaner thread，從而減輕了master thread 的工作，進一步提升系統的并發性。 ### innoDB關鍵特性： * 插入緩沖 * 兩次寫 * 自適應hash索引 * 異步io * 刷新鄰接頁 #### 插入緩沖( insert buffer) 在innoDB存儲引擎中，主鍵是行唯一的標識符。通常應用程序中進行記錄的插入順序是按照主鍵遞增的順序進行插入的。因此，插入聚集索引一般是順序的，不需要磁盤的隨機讀取。對于非聚集索引葉子節點的插入不再是順序的了，這是就需要離散地訪問非聚集索引頁。（B+樹的特性決定了非聚集索引插入的離散性，因此性能會下降） * innoDB開創性地設計了 **insert buffer** ，對于非聚集索引的插入或更新操作，不是每次直接插入到索引頁中，而是先判斷插入的非聚集索引頁是否在緩沖池中，若在則直接插入；不在則先放入到一個insert buffer對象中。然后在以一定的頻率和情況進行insert buffer和輔助索引葉子結點的merge操作。這時通常能將多個插入合并到一個操作中，大大提高了對于非聚集索引插入的性能。 * insert buffer的使用需要滿足以下兩個條件：－索引是輔助索引－索引不是唯一的 * insert buffer的數據結構是一個B＋樹。在全局只有一棵insert buffer B＋樹，負責對所有的表的輔助索引進行insert buffer這棵樹存在共享表空間中，默認也就是IB data1中。 * insert buffer 是一棵B＋樹，因此其也由頁節和非葉節點組成。非葉節點存放的是search key 。 [ space marker offset]space表示待插入記錄所在表的表空間id space占4個字節，marker占用一個字節，用來兼容老版本的insert buffer。offset表示頁所在的偏移量，只用4個字節。 ##### change buffer innodb1.0開始引入change buffer 可以視為insert buffer的升級。innodb 對dml操作－insert delete update都進行緩沖他們分別：insert buffer，delete buffer，purge buffer。對象也是非唯一的輔助索引。 ##### merge insert buffer * 輔助索引頁被讀取到緩沖池時 * insert buffer bitmap頁追蹤到該輔助索引頁已無可用空間時； * master thread #### 兩次寫（doublewrite）兩次寫帶給innodb存儲引擎的是數據也的可靠性。當數據庫發生宕機時，會產生部分寫失效。innodb未使用兩次寫前，因為這個會導致數據丟失現象。在應用重做日志之前，用戶需要一個頁的副本，當寫入失效發生時先通過頁的副本先還原該頁，在進行重做，這就是doublewrite。操作系統在將頁寫入磁盤的過程中發生了崩潰，在恢復過程中，innodb可以從共享表空間中找到該頁的一個副本，將其復制到表空間文件，在應用重做日志進行恢復。（參數skip_innodb_doublewrite可以禁用兩次寫功能） * 兩次寫由兩部分組成，一部分是內存中的doublewrite buffer 大小為2Mb，另一位一部分是由物理磁盤上共享表空間的連續的128個頁，即兩個區，大小同樣為2Mb。 * 在對緩沖池的臟頁進行刷新時，并不直接謝磁盤，而是通過memcpy函數將臟頁先復制到內存中doublewrite buffer，之后通過double write buffer在分兩次每次1Mb順序的寫入共享表空間的物理磁盤上，然后調用fsync函數，同步磁盤，避免緩沖寫帶來的問題。 #### 自適應哈希索引 innodb 會監控對表上各索引頁的查詢您。如果觀察到建立hash索引可以帶來速度提升，則建立hash索引，稱之為自適應hash索引（AHI）。AHI 是通過緩沖池的b+樹頁構造而來的，因此建立的速度很快，而且不需要對整張表構建hash索引。innodb會自動根據訪問的頻率來為某些熱點頁建立hash索引。 * AHI有一個要求，即對這個頁的連續訪問模式必須是一樣的。 * innodb_adaptive_hash_index可以禁用或啟動此特性，默認AHI為開啟狀態。 #### 異步io AIO 可以進行merge IO操作，也就是將多個IO合并成一個IO，提高IOPS的性能。啟用Native AIO，恢復速度可以提高75%。在InnoDB存儲引擎中，read ahead方式的讀取都是通過AIO完成，臟頁的刷新，即磁盤的寫入操作則全部由AIO完成。 #### 刷新鄰接頁 Flush Neighbor Page（刷新鄰接頁）的特性。其工作原理為：當刷新一個臟頁時，InnoDB存儲引擎會檢測該頁所在區（extent）的所有頁，如果是臟頁，那么一起進行刷新。 #### 關閉啟動與恢復 * 在關閉時，參數innodb_fast_shutdown影響著表的存儲引擎為InnoDB的行為。該參數可取值為0、1、2，默認值為1 - 0表示關閉時完成所有full purge和merge insert buffer并且刷新所有的臟頁到磁盤。 - 1表示不完成上述的full purge和merge insert buffer，但是在緩沖池中的一些數據臟頁還是會刷新回磁盤 - 2表示不完成上述的full purge和merge insert buffer操作也不將緩沖池中的緩存頁率先你回磁盤二十將日志寫入日志文件，下次啟動時進行恢復操作。 * DB需要完成所有的full purge和merge insert buffer，并且將所有的臟頁刷新回磁盤。這需要一些時間，有時甚至需要幾個小時來完成。如果在進行InnoDB升級時，必須將這個參數調為0 * innodb_force_recovery影響了整個InnoDB存儲引擎恢復的狀況。默認為0，當不能進行有效恢復時，如數據頁發生了corruption，MySQL數據庫可能發生宕機 ,并把錯誤寫入錯誤日志中去。 * 參數innodb_force_recovery還可以設置為6個非零值：1～6。大的數字表示包含了前面所有小數字表示的影響。具體情況如下 - 1.( SRV FORCE IGNORE CORRUPT):忽略檢查到的 corrupt頁。 - 2 （SRV FORCE NO BACK GROUND)阻止 Master Thread線程的運行，如 Master Thread線程需要進行 full purge操作，而這會致 crash - 3( SRV FORCE NO TRX UNDO):不進行事務的回滾操作。 - 4( SRV FORCE NO IBUF MERGE):不進行插人緩沖的合并操作。 - 5 ( SRV FORCE NO UNDO LOG SCAN:不査看撤銷日志( Undo Log)， INNODB存儲引擎會將未提交的事務視為已提交。 - 6( SRV FORCE NO LOG REDO:不進行前滾的操作。 - 需要注意的是，在設置了參數 innodb force recovery大于0后，用戶可以對表進行select、 create和drop操作，但insert、 update和 delete這類DML操作是不允許的。