MySQL innodb引擎詳解

MySQL innodb引擎數據存儲與其它的是不一樣了，對於我們做開發的肯定知道MySQL innodb引擎與其它存儲的一些區別，在這裡給各位詳細的介紹一下關於MySQL innodb引擎吧． innodb是事物安全的MySQL存儲引擎 是oltp應用中核心表的首選存儲引擎 MySQL第一個支持事物的存儲引擎是BDB MySQL第一個完整支持ACID事物是innodb innodb的特點 行鎖設計 支持MVCC 支持外鍵 提供一致性非鎖定讀 同時被設計用來最有效的利用以及使用內存和cpu   版本 功能 老版本innodb 支持ACID 行鎖設計 MVCC innodb 1.0.x 增加了compress和dynamic頁格式 innodb 1.1.x 增加了Linux AIO 多回滾段 innodb 1.2.x 增加了全文索引支持 在線索引添加 innodb 體系架構     innodb的存儲引擎 有多個內存塊 可以認為這些內存組成一個大的內存池          1 維護所有進程/線程需要訪問的多個內部數據結構         2 緩存磁盤上的數據 方便快速地讀取 同時在對磁盤文件的數據修改之前在這裡緩存         3 重做日志(redo log)緩沖                  後台線程的主要作用是負責刷新池中的內存池中的數據 保證緩沖池中的內存是最近的數據 將已經修改的數據文件刷新到磁盤文件 同時保證在數據庫發生異常的情況下 innodb能恢復到正常運行狀態   後台線程     innodb存儲引擎是多線程的模型 因此其中有多個不同的後台線程 負責處理不同的任務       1 Master Thread         MT 是一個非常核心的後台線程 住一套負責將緩沖池中的數據一部刷新到磁盤 保證數據的一致性 包括髒頁的刷新 合並插入緩沖(insert buffer) undo頁的回收     2 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_threads來增大io thread          從innodb 1.0.x版本開始 read thread和 write thread分別增大到了4個並且不在使用innodb_file_file_io_threads參數 而是分別使用 innodb_read_io_threads和innodb_write_io_threads         可以通過命令show engine innodb status來觀察innodb中的IO Thread             show engine innodb status\G;     3 Purge Thread         事物被提交後 其所使用的undolog可能不在需要 因此需要PurgeThread來回收已經使用並分配的undo頁          在innodb 1.1版本之前 purge操作僅在innodb存儲引擎的master thread 中完成          而從innodb 1.1版本開始 purge操作可以獨立到單獨的線程中進行 以此來減輕master thread 的工作 從而提高cpu的使用率以及提升存儲引擎的性能          用戶可以再MySQL數據庫的配置文件中添加如下命令來啟用獨立的Purge Thread         [mysql]         innodb_purge_threads=1         在innodb 1.1 版本中 即使將innodb_purge_threads設為大於1 innodb存儲引擎啟動時也將其設為1 並在錯誤文件中出現如下類似的提示                  在innodb 1.2版本開始 innodb支持多個purge thread 這樣做的目的是為了進一步加快undo頁的回收這樣也能更進一步利用磁盤的隨機讀取性能 用戶可以設置4個purge thread       4 page cleaner thread          innodb 1.2.x 引入 page cleaner thread           作用:將之前版本中臧晔的刷新操作都放入到單獨的線程中完成 減輕原master thread的工作對於用戶查詢線程的阻塞   內存      1 緩沖池         innodb存儲引擎給予磁盤存儲的 並將其中的激勵按照頁的方式進行管理 一次可將其視為給予磁盤的數據系統(disk-base database) 由於cpu跟磁盤速度之間的鴻溝 給予磁盤的數據庫系統通常使用緩沖池技術來提高數據庫的整體性能         緩沖池就是一塊內存區域 通過內存的速度來彌補磁盤的速度較慢對數據庫性能的影響 在數據庫中進行讀取頁的操作 首先將從磁盤讀到頁存放在緩沖池中 這個過程稱為將頁FIX 在緩沖池中 下一次在讀相同的頁時 首先判斷頁是否在緩沖池中 若在緩沖池中 該頁在緩沖池中被命中 直接讀取該頁 否則讀取 磁盤上的頁         對於數據庫中頁的修改操作 則首先修改在緩沖池中的頁 然後再以一定的頻率刷新到磁盤上 這裡需要注意的是 頁從緩沖池刷新回磁盤的操作並不是在每次頁發生更新時觸發 而是通過一種稱為checkpoint的機制刷新回磁盤 同樣 這也是為了提高數據庫的整體性能         緩沖池的大小直接影響著數據庫的整體性能          緩沖池緩存的數據頁的類型有:索引頁 數據也 undo頁 插入緩沖(insert buffer) 自適應哈希索引(adaptive hash index) innodb存儲鎖信息(lock info) 數據字典信息(data dictionary)等  緩沖池不只是緩存索引頁和數據頁 他們只是占緩沖池很大的一部分而已         innodb 1.0.x以後 允許多個緩沖池實例 每頁根據哈希值平均分配到不同緩沖池裡 這樣做的好處是減少數據庫內部的資源競爭 增加數據庫的並發處理能力             show variables likes 'innodb_buffer_pool_instances'\G;         將這個參數改為大於1 的值 就可以得到多個緩沖池實例 再通過命令show engine innodb status\G;觀察修改後系統的緩沖池狀態          MySQL 5.6 可以通過information_schema架構下的表 innodb_buffer_pool_status來觀察緩沖的狀態       2 LRU List Free List Flush List         數據庫中的緩沖池是通過LRU(latest recent used 最近最少使用)算法來進行管理的 最頻繁使用的頁是在LRU列表的前端  而最少使用的頁在LRU列表的尾端 即使用頻發的頁放在LRU列表的前端 而最少使用的頁在LRU列表的尾端 當緩沖池不能存放新讀取到的頁時 將首先釋放LRU列表中尾端的頁         在innodb存儲引擎中 緩沖池中頁的大小默認為16KB 同樣使用LRU算法對緩沖池進行管理 稍有不同的是innodb存儲引起對傳統LRU算法做了一些優化在innodb的存儲引擎中 LRU列表中還加入了midpoint位置 即新讀取的頁 並不會直接放到LRU連的首部 而是放到midpoint位置 這個算法在innodb存儲引擎下稱為 midpoint insertion strategy 默認該位置在LRU列表長度的5/8處              參數:innodb_old_blocks_pct控制             show variables like 'innodb_old_blocks_pct'\G;         采用midpoint的原因             某些sql操作可能會使緩沖池的頁被刷新出來 從而影響緩沖池的效率 常見的這類操作為索引或數據的掃描操作 這類操作需要訪問表中的許多頁甚至全部 而這些頁通常來說又僅在這次查詢操作中需要 並不是活躍的熱點數據 如果頁被放到LRU首部 那麼非常可能將所需要的熱點數據頁從LRU列表一處 而在下一次需要讀取該頁時 innodb存儲引擎需要再次訪問磁盤         innodb_old_blocks_time             用於表示頁讀取到mid未支護需要等待多久才會被加入到LRU列表的熱端 因此當需要執行上述所說的sql操作時 可以通過下面方法使LRU列表中熱點數據不被刷出             set global innodb_old_blocks_time=1000;             如果用戶預估自己活躍的熱點數據不止百分之63 那麼執行SQL語句前 可以修改innodb_old_blocks_pct 減少熱點頁被刷出來的概率           LRU列表管理已經讀取的頁 但數據庫剛啟動的時候 LRU列表是空的 即沒有任何的頁  這時頁都存放在Free列表中 當需要從緩沖池中分頁時 首先從Free列表中查找是否有可用的空閒頁 若有則將該頁從Free列表中刪除 放入到LRU列表中 否則 根據LRU算法 淘汰LRU列表末尾的頁將該內存空間分配給新的頁 當頁從LRU列表的old部分 加入到new部分時 稱此時發生的操作為page made young 而因為innodb_old_blocks_time的設置而導致頁沒有從old部分移動到new部分的操作稱為page noe made young可以通過命令 show engine innodb status來觀察LRU列表及Free列表的使用情況和運行狀態 這個狀態顯示的不是當前狀態 而是過去某個時間范圍內 innodb存儲引擎的狀態 per second averages calculated from the last 24 seconds 表示過去的24小時         page made young 顯示了LRU列表中頁移動到前端的次數 因為該服務器在運行階段沒有改變innodb_old_blocks_time的值 因此not young為0 youngs/s non-youngs表示美妙這兩類操作的次數          buffer pool hit rate 表示緩沖池的命中率 值越接近1或等於1 說明緩沖池運行狀態非常好 一般不應該小於百分之九五 若發生buffer pool hit rate的值小於百分之九五的情況 需要檢查是否由於全表掃描引起的LRU列表被污染的問題         innodb_buffer_pool_stats:觀察緩沖池的運行狀態 information_schema中的表         innodb_buffer_page_lru:觀察lru列表中每個頁的具體信息 information_schema中的表         innodb1.0.x 開始支持壓縮頁的功能 可以講16kb的頁壓縮為1kb 2kb 4kb 和8kb 但是由於壓縮頁的大小發生變化 lru列表也有了些許的改變 對於非16kb的頁通過unzip_LRU列表進行管理 通過show engine innodbdb status可以觀察                  這裡 lru len 包含unzip_lru len 列         對於不同壓縮大小的頁管理方式:在unzip_LRU列表中對不同壓縮也大小的頁進行分別管理 其次通過伙伴算法進行內存的分配 加入對需要從緩沖池中申請頁為4kb的大小 過程如下             1 檢查4kb的unzip_LRU列表 檢查是否有可用的空閒頁;             2 若有 則直接使用             3 否則 檢查8kb的unzip_LRU列表;             4 若能的到空閒頁 將頁分成2 個4kb頁 存放到4kb的unzip_LRU列表             5 若不能夠得到空閒頁 從LRU列表中申請一個16kb的頁 將頁分為1個8kb的頁 2個4kb的頁分別存放到對應的unzip_LRU列表中             同樣可以通過information_schema架構下的表innodb_buffer_page_lru來觀察unzip_LRU列表中的頁         LRU列表中的頁被修改後 稱該頁為髒頁(dirty page) 即緩沖池中的頁和磁盤上的頁的數據產生了不一致 這時數據庫會通過checkpoint機制將髒頁刷新回磁盤 而flush列表中的頁即為髒頁列表 髒頁寄存在與LRU列表中 也存在於flush列表中 LRU列表用來管理緩沖池頁的可用性 Flush用來管理將頁刷新回磁盤二者互不影響          3 重做日志緩存         innodb存儲引擎首先將臭作日志信息先放入到這個緩沖區 然後按一定頻率將其刷新到重做日志文件         參數:innodb_log_buffer_size 默認8MB         將重做日志緩沖的內容刷新到外部磁盤的重做日志文件的情況:             1 master thread 每一秒將重做日志緩沖到重做日志文件             2 每個事物提交時會將重做日志緩沖刷新到重做日志文件                 3 當重做日志緩沖池剩余空間小於二分之一時 重做日志緩沖刷新到重做日志文件          4 額外的內存池         在innodb存儲引擎 對內存管理是通過一種稱為內存堆(heap)的方式進行的    4 checkpoint技術     write ahead log 日志先寫策略 避免數據丟失通過重做日志來完成數據的恢復     宕機通過重做日志恢復的條件         1 緩沖池可以緩存數據庫中所有的數據         隨著數據庫的日積月累的增大 導致數據增到 內存不足以緩存所有數據 所以對生產環境應用中的數據庫是很難保證的         2 重做日志可以無線增大             重做日志無線增大 會對成本要求太高 頁不便於運維 而且 DBA 或SA 不知道什麼時候重做日志是否已經接近於磁盤可使用的空間的閥值 而且讓存儲設備支持顆動態擴展頁是需要一定的技巧和設備支持         3 宕機後的恢復時間 時間越久 恢復的代價就會越大       checkpoint的優點         1 縮短數據庫的恢復時間         2 緩沖池不夠用時 將髒頁刷新到磁盤         3 重做日志不可用 刷新髒頁     這樣 數據庫宕機的時候 就不需要所有的重做日志 因為checkpoint之前的頁都已經刷新到磁盤 所以這時候只需要 checkpoint之後的重做日志進行恢復 這樣就大大縮短了恢復時間     當緩沖池不夠用的時候 就會根據 LRU算法會溢出最近最少使用的頁 若此頁為髒頁 那麼需要強制執行checkpoint 江髒頁頁就是頁的新版本刷回磁盤     重做日志的循環使用     對於innodb存儲引擎 是通過LSN（log sequence number）來標記版本 二LSN是8字節的數字 單位是字節 每個頁都有lsn 重做日志頁有LSN checkpoint頁有LSN 可以根據 show engine innodb status來觀察     checkpoint作用：將緩沖池中的髒頁刷會磁盤 但是每次刷多少頁到磁盤 從哪取髒頁 什麼時候觸發checkpoint都不同     checkpoint類型         1 sharp checkpoint             作用：數據庫關閉時將所有髒頁都刷新到磁盤 這是默認工作方式             參數：innodb_fast_shutdown=1         2 fuzzy checkpoint             如果數據庫正常運行的時候使用sharp checkpoint 那麼數據庫的可能性就會收到很大影響 所以在innodb存儲引擎內部使用 fuzzy checkpoint進行頁的刷新 （只刷新一部分髒頁 而不是刷新所有的髒頁回磁盤）         發生 fuzzy checkpoint情況         1 master  threadcheckpoint             差不多以每秒或者每10秒的速度從緩沖池髒頁列表刷新一定比例的頁回磁盤 （異步 查詢線程不會阻塞）         2 flush_lru_list checkpoint             innodb 1.1x版本前 需要檢查LRU列表中是否有足夠的（100）可用空間操作發生在用戶查詢線程中 會阻塞查詢 沒有 innodb存儲引擎就會將lru列表尾端的頁溢出 如果這些頁裡面有髒頁 就需要checkpoint              innodb 1.2.x版本開始 這個檢查被放在了一個單獨的page cleaner 線程中進行 並且用戶可以通過innod_lruscan depth控制列表中可用頁的數量 默認為1024         3 async/sync flush checkpoint             當重做日志不可用的情況下 這時需要強制將一些頁刷新會磁盤 而此時髒頁是從髒頁列表中選取 若將已經寫入到重做日志的LSN 記為 redo_lsn 將已經刷新會磁盤最新頁的lsn結尾checkpoint_lsn                 checkpoint_age = redo_lsn - checkpoint_lsn                 async_water_mark = 75% total_redo_log_file_size             async/sync flush checkpoint 是為了保證重做日志的循環使用的可用性              innodb 1.2x之前 async flush checkpoint會阻塞發現問題的用戶查詢線程 sync flush checkpoint會阻塞用戶查詢線程 並等待髒頁的刷新完成             MySQL官方版本 並不能查看刷新也是從flush 列表還是LRU列表中進行checkpoint的 也不知道因為重做日志而產生的async/sync flush的次數 但是 innoSQL版本可以通過 show engine innodb status來觀察         4 dirty page too much checkpoint             髒頁太多導致innodb存儲引擎強制進行checkpoint 其目的總的來說還是為了保證緩沖池中有足夠多的頁 主要是為了保證緩沖池中有足夠可用的頁             參數innodb_max_dirty_pages_pct   5 master thread 工作方式     innodb 1.0.x 之前的master thread         master thread 具有最高的線程優先級別 其內部由多個循環(loop)組成 主循環 (loop) 後台循環(backgroup loop) 刷新循環(flush loop) 暫停循環(suspend loop) master thread就會在這些循環中切換         loop為主循環         loop循環通過thread sleep來實現 在負載很大的情況下可能會有延遲          每秒一次的操作包括             1 日志緩沖刷新到磁盤 即使這個事務還沒有提交(總是)             2 合並插入緩沖(可能)             3 至多刷新100個innodb的緩沖池中的髒頁到磁盤(可能)             4 如果當前沒有用戶活動 則切換到 background loop(可能)         再大的事務提交的時間很短的原因:innodb存儲引擎仍然每秒會將重做日志緩沖中的內容刷新到重做日志文件         合並插入緩沖 也不是每秒都會發生的 innodb存儲引擎會判斷當前一秒內發生的io次數是否小於5次 如果小於5次 innodb人為當前io壓力很小 可以執行合並插入緩沖的操作         buf_get_modified_ratio_pct跟百分之90的innodb_max_dirty_pages_pct相比較 超過 才會人為需要進行磁盤同步操作 將100個髒頁寫入磁盤     每10秒的操作         1 刷新100個髒頁到磁盤(可能)         2 合並之多5個插入緩沖(總是)         3 將日志緩沖刷新到磁盤(總是)         4 刪除無用的undo頁(總是)         5 刷新100或者10個髒頁到磁盤(總是)     過程         1 判斷10秒之內的磁盤的IO操作是否小於200             2 小於 innodb認為有足夠的IO能力 將100的髒頁刷新到磁盤         3 innodb存儲引擎會合並插入緩沖 這次合並插入緩沖總會在這個剪短進行         4 innodb存儲引擎會再進行一次獎入職緩沖刷新到磁盤的操作 這和沒秒一次發生的操作一樣         5 innodb進一步執行full purge 即刪除無用的undo頁 對表update delete這類操作時操作時 原先的行被標記為刪除 但是因為一致性讀(consistent read)的關系 需要保留這些行版本的信息 但是在full purge過程中 innodb存儲引擎會判斷當前事物系統已被刪除的行是否可以刪除 比如有時候還有查詢操作需要讀取之前版本的undo信息 如果可以刪除 innodb會立即將其刪除          6 buf_get_modified_ratio_pct >70% 則刷新100個髒頁到磁盤 <70%刷新10%的髒頁到磁盤     background loop 當當前沒有用戶活躍(數據庫空閒)或者數據庫關閉(shutdown)         進行的操作             1 刪除無用的undo(總是)             2 合並20個插入緩沖(總是)             3 跳回到主循環(總是)             4 不斷刷新100個頁知道符合條件(可能 跳轉到flush loop中完成)         跳轉到flush loop 後 如果也沒什麼事情做了 innodb存儲引擎會切換到suspend loop 將master thread掛起 等待事件的發生 若用戶啟用了(enable)innodb 卻沒有使用任何innodb的表 那麼master thread 一直掛起狀態   innodb 1.2.x之前的master thread     參數 innodb_io_capacity 用來表示磁盤io的吞吐量 默認是200 對於刷新到磁盤頁的數量 按照innodb_io_capacity的百分比控制         1 在合並插入緩沖時 合並插入緩沖的數量為 innodb_io_capacity         2 在從緩沖區刷新髒頁時 刷新髒頁的數量為innodb_io_capacity     用來解決寫入密集的應用程序中 產生大於20額插入緩沖的情況 master thread忙不來的問題             參數 innodb_max_dirty_pages_pct