MySQL innoDB 儲存引擎學習篇

MySQL innoDB 儲存引擎學習篇

master thread的縣城優先順序別最高.其內部由幾個迴圈(loop)組成:主迴圈(loop)、後台迴圈(background loop)、重新整理迴圈(flush loop)、暫停迴圈(suspend loop)。master thread 會根據數-據庫啟動並執行狀態在loop,background loop、flush loop 和suspend loop 中進行切換.
       
        每秒一次的操作:
        1、日誌緩衝重新整理到磁碟，即使這個事務還沒有提交(總是).
        2、合并插入緩衝(可能)
        3、至多重新整理100個InnoDB的緩衝池中的髒頁到磁碟(可能)。
        4、如果當前沒有使用者活動,切換到background loop(可能).
 
      即使某個事務還沒有提交,innodb儲存引擎仍然會每秒將重做日誌緩衝中的內容重新整理到重做記錄檔.這一點是必須知道的,這可以很好地解釋為什麼再大的事務commit的時間也是很快的。

        合并插入緩衝(insert buffer) 並不是每秒都發生。Innodb儲存引擎會判斷當前一秒內發生的IO次數是否小於5次，如果小於5次,innodb 認為當前的IO壓力很小，可以執行合并插入緩衝的操作.

        同樣，重新整理100個髒頁也不是每秒都在發生。innodb儲存引擎通過判斷當前緩衝池中髒頁的比例(buf_get_modified_ratio_pct)是否超過設定檔中innodb_max_dirty_pages_pct這個參數(預設為90，代表%90)如果超過了這個閥值，Innodb儲存引擎認為需要做磁碟同步操作，將100個髒頁寫入磁碟.

        Innodb儲存引擎每10秒的操作
        1、重新整理100個髒頁到磁碟(可能)
        2、合并至多5個插入緩衝(總是)
        3、將日誌緩衝重新整理到磁碟(總是)
        4、刪除無用的Undo頁(總是)
        5、重新整理100個或者10個髒頁到磁碟(總是)

        在以上的過程中，innodb儲存引擎會先判斷過去10秒之內磁碟的IO操作是否小於200次。如果是，innodb儲存引擎認為當前有足夠的磁碟IO操作能力.因此將100個髒頁重新整理到磁碟。接著，Innodb 儲存引擎會合并插入緩衝。不同於每1秒操作時可能發生的合并插入緩衝操作，這次的合并插入緩衝操作總會在這個階段進行。之後Innodb儲存引擎會執行一次將日誌緩衝重新整理到磁碟的操作，這與每秒發生的操作是一樣的。
        接著InnoDB儲存引擎會執行一步full purge操作，即刪除無用的Undo頁。對錶執行update、delete這類的操作時，原先的行被標記為刪除，但是因為一致性讀(consistent read)的關係，需要保留這些版本的資訊。但是在full perge 過程中，Innodb儲存引擎會判斷當前事務系統中已刪除的行是否可以刪除，比如有時候可能還有查詢操作需要讀取之前版本undo資訊，如果可以，innodb會立即將其刪除.從原始碼中可以發現，innodb儲存引擎在操作full purge的時，每次最多刪除20個undo頁.
        然後，Innodb儲存引擎會判斷緩衝池中髒頁的比例(buf_get_modified_ratio_pct），如果有超過70%的髒頁，則只需重新整理10%的髒頁到磁碟。
        最後，Innodb儲存引擎會產生一個檢查點(checkpoint)，innodb儲存引擎的檢查點也成為模糊檢查點(fuzzy checkpoint)。Innodb儲存引擎在checkpoint時並不會把所有緩衝池中的髒頁都寫入磁碟，因為這樣可能對效能產生影響，而只是將最老記錄序號(oldest LSN)的頁寫入磁碟.
     
        接著來看background loop，若當前沒有使用者活動(資料庫空閑時)或者資料庫關閉時，就會切換到這個迴圈。這個迴圈會執行以下操作:
        1、刪除無用的Undo頁(總是)
        2、合并20個插入緩衝(總是)
        3、跳回到主迴圈(總是)
        4、不斷重新整理100個頁，直到符合條件(可能，跳轉到flush loop中完成)
        如果flush llop中也沒有事情可以做了，innodb儲存引擎會切換到suspend_loop，將master thread掛起，等待事件的發生，若啟用了Innodb儲存引擎.卻沒有使用任何Innodb儲存引擎的表，那麼master thread總是處於掛起狀態.
        從Innodb plugin 開始，用show engine innodb status 可以查看當前master thread的狀態資訊，如下所示：這裡可以看到主迴圈執行了 6300568;

 

mysql> show engine innodb status\G;
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2015-02-13 13:42:44 2ac95fdf3940 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 14 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 6300568 srv_active, 0 srv_shutdown, 5298516 srv_idle
srv_master_thread log flush and writes: 11599055

 

        日均月異隨著市場上磁碟裝置越來越先進;IO讀寫越發的高了起來...innodb儲存引擎如果安裝以前的方式:(1秒內處理100個頁的寫入和20個插入緩衝合并;未免過於遲緩了)所以Innodb儲存引擎開始提供一個參數；用來表示磁碟IO的輸送量，參數為Innodb_io_capacity，預設值為200.對於重新整理到磁碟的數量，會按照Innodb_io_capacity的百分比來重新整理相對數量的頁。規則如下:
        1、在合并插入緩衝時，合并插入緩衝的數量為Innodb_io_capacity數值5%
        2、在從緩衝區重新整理髒頁時,重新整理髒頁的數量為innodb_io_capacity。
 
        另外一個問題是參數innodb_max_dirty_pages_pct的預設值，在mysql5.1版本之前(包括5.1)，該值得預設值為90，意味著髒頁占緩衝池的90%。目前該值預設是75%;
        另外一個參數是innodb_adaptive_flushing(自適應地重新整理)，該值影響每1秒重新整理髒頁的數量。原來的重新整理規則是:如果髒頁在緩衝池所佔的比例小於innodb_max_dirty_pages_pct時，不重新整理髒頁;大於Innodb_max_dirty_pages_pct時，重新整理100個髒頁，而innodb_adaptive_flushting參數的引入，innodb儲存引擎會通過一個名為buf_flush_get_desired_flush_rate的函數來判斷需要重新整理髒頁最合適的數量。而buf_flush_get_desired_flush_rate是通過判斷產生重做日誌的速度來判斷最合適的重新整理髒頁的數量。因此當髒頁的比例小於Innodb_max_dirty_pages_pct時，也會重新整理一定量的髒頁。

 

        關鍵性
        Innodb儲存引擎的關鍵性特性包括插入緩衝、兩次寫(double wirte)、自適應雜湊索引(adaptive hash index)。
        插入緩衝
        插入緩衝是Innodb儲存引擎關鍵特性;Insert Buffer和資料頁一樣，也是物理頁的一個組成部分;
        叢集索引一般是順序的，不需要磁碟的隨機讀取，
        在很多情況下，一張表有多個非聚集的輔助索引(secondary index)
        InnoDB儲存引擎開創性設計了插入緩衝，對於非叢集索引的插入或更新操作，不是每一次直接插入索引頁中。而是先判斷插入的非叢集索引頁是否在緩衝池中。如果在，則直接插入；如果不在，則先放入一個插入緩衝區中，好似欺騙資料庫這個非叢集索引已經查到葉子節點了.然後再以一定的頻率執行插入緩衝和非叢集索引頁子節點的合併作業，這時通常能將多個插入合并到一個操作中(因為在一個索引頁中)，這就大大提高了對非叢集索引執行插入和修改操作的效能。
        插入緩衝的使用需要滿足以下兩個條件:
        1、索引是輔助索引。
        2、索引不是唯一的。
        輔助索引不能是唯一的，因為在把它插入到插入緩衝時，我們並不去尋找索引頁的情況。如果尋找肯定會出現離散讀的情況，插入緩衝就失去了意義.
        Ibuf: size 1, free list len 0, seg size 2, 38 merges
        seg size顯示當前插入緩衝的大小為2*16KB
        free list len 0
        free list len 代表了空閑列表的長度。
        目前插入緩衝存在一個問題是，在寫密集的情況下，插入緩衝會佔用過多的緩衝池記憶體，預設情況下最大可佔用1/2的緩衝池記憶體。
        修改IBUF_POOL_SIZE_PER_MAX_SIZE就可以對插入緩衝的大小進行控制;列如；將IBUF_POOL_SIZE_PER_MAX_SIZE改為3，則最大隻能使用1/3的緩衝池記憶體.

        兩次寫
        如果說插入緩衝帶給Innodb儲存引擎的是效能，那麼兩次寫帶給Innodb儲存引擎的資料的可靠性，當資料庫戎機時，可能發生資料庫正在寫一個頁面，而這個頁只能寫一部分(比如16K的頁，唯寫前4K的頁)的情況，我們稱之為部分寫失效(partial page write);
注意:重做日誌中記錄的是對物理操作，如位移量800，寫'aaa'記錄。如果這個頁本身已經損壞了,再對其進行重做是沒有意義的。這就是說，在應用(apply)重做日誌之前，我們需要一個頁的副本，當寫入失效發生時，先通過頁的副本來還原該頁，再進行重做，這就是doublewrite.innodb儲存引擎doublewerite；
        doublewrite由兩部分組成:一部分是記憶體中的doublewrite buffer，大小為2MB；另一部分是物理磁碟上共用資料表空間中連續的128個頁;即兩個區(extent)，大小同樣為2MB；當緩衝池的髒頁重新整理時，並不直接寫磁碟，而是會通過memcpy函數將髒頁先拷貝到記憶體中的doublewrite buffer，之後通過doublewrite再分兩次，每次寫入1MB到共用資料表空間的物理磁碟上,然後馬上調用fsync函數 ,同步磁碟，避免緩衝寫帶來的問題。在這個過程中，因為doublewrite頁是連續的，因此這個過程是順序寫的.開銷並不是很大。在完成doublewrite頁的寫入後，再將doublewrite buffer中的頁寫入各個資料表空間檔案中，此時的寫入則是離散的。可以通過以下命令觀察double write健全狀態;

 

mysql> show global status like 'innodb_dblwr%'\G;
*************************** 1. row ***************************
Variable_name: Innodb_dblwr_pages_written
        Value: 10005304
*************************** 2. row ***************************
Variable_name: Innodb_dblwr_writes
        Value: 3272391
2 rows in set (0.01 sec)

 

        可以看到，doublewrite 一共寫了10005304個頁，但實際的寫入次數為3272391，如果你發現你的系統在高峰時Innodb_dblwr_pages_written:Innodb_dblwr_writes遠小於64:1，那麼說明你的系統寫入壓力並不是很高.
        如果作業系統在將頁寫入磁碟的過程中崩潰了，在恢複過程中,Innodb儲存引擎可以從共用表中的doublewrite找到改頁的一個副本，將其拷貝到資料表空間檔案，再應用重做日誌。
        參數skip_innodb_doublewrite可以禁止使用兩次寫功能，這時可能會發生前面提及的寫失效問題。
注意：有些檔案系統本身就提供了部分寫失效的防範機制，如ZFS檔案系統。在這種情況下,我們就不要啟用doublewrite了。
        自適應雜湊索引
        雜湊(hash)是一種非常快的尋找方法，一般情況下尋找時間複雜度為o(1)。常用於join操作，如SQL Server 和Oracle中的雜湊串連(hash join)。但是SQL Server和Oracle等常見的資料庫並不支援雜湊索引(hash index)。MySQL的Heap儲存引擎預設的索引類型為雜湊，而Innodb儲存引擎提出了另外一種實現方法，自適應hash index(adaptive hash index)。
        Innodb儲存引擎會監控對錶上索引的尋找，如果觀察到建立雜湊索引可以帶來速度的提升，則建立hash index，所以稱之為自適應(adaptive)的。自適應雜湊索引通過緩衝池的B+tree構造而來,因此建立的速度很快.
Hash table size 9461399, node heap has 2933 buffer(s)
7767.23 hash searches/s, 130.87 non-hash searches/s
        這裡主要輸出的資訊有:包括自適應雜湊索引的大小、使用方式、每秒使用自適應hash index搜尋的情況。值得注意的是，hash索引只能用來搜尋等值的查詢，如select * from table where index_col = 'xxx' 而對其他尋找的類型,如範圍尋找,是不能使用的。因此這裡出現了non-hash searches/s的情況。用hash searches：non-hash searches命令可以大概瞭解使用雜湊索引後的效率.
        我們可以通過參數innodb_adaptive_hash_index來禁用或啟動此特性，預設為開啟。

MySQL InnoDB儲存引擎鎖機制實驗

InnoDB儲存引擎的啟動、關閉與恢複

MySQL InnoDB獨立資料表空間的配置

MySQL Server 層和 InnoDB 引擎層 體繫結構圖

InnoDB 死結案例解析

MySQL Innodb獨立資料表空間的配置

本文永久更新連結地址：