mysql dba系統學習（21）mysql儲存引擎InnoDB

mysql儲存引擎InnoDB

1，主體繫結構：

預設7個後台線程，4個io thread(insert buffer、log、read、write),1個master thread(優先順序最高),1個鎖(lock)監控線程，1個錯誤監控線程。可以通過show engine innodb status來查看。新版本已對預設的read thread和write thread分別增大到4個，可通過show variables like 'innodb_io_thread%'查看。
儲存引擎組成：

緩衝池(buffer pool)、重做日誌緩衝池(redo log buffer)以及額外的記憶體池(additional memory pool).具體配置可由show variables like 'innodb_buffer_pool_size'、show variables like
'innodb_log_buffer_size'、show variables like 'innodb_additional_mem_pool_size'來查看。

緩衝池：

占最大塊記憶體，用來存放各種資料的緩衝包括有索引頁、資料頁、undo頁、插入緩衝、自適應雜湊索引、innodb儲存的鎖資訊、資料字典資訊等。工作方式總是將資料庫檔案按頁(每頁16k)讀取到緩衝池，然後按最近最少使用(lru)的演算法來保留在緩衝池中的快取資料。如果資料庫檔案需要修改，總是首先修改在緩衝池中的頁(發生修改後即為髒頁)，然後再按照一定的頻率將緩衝池的髒頁重新整理到檔案。通過命令show engine innodb status;來查看。
日誌緩衝：

將重做日誌資訊先放入這個緩衝區，然後按一定頻率將其重新整理到重做記錄檔。650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/2134522W3-0.jpg" title="Z~YEFVN%MGT_2}{6)A_@W6C.jpg" alt="103407256.jpg" />

innodb_buffer_pool_size 一般設定問機器的實體記憶體的80%

innodb_additional_mem_pool_size 一般是快取資料字典和資料結構的資訊，不宜太大

2，兩次寫：

它帶來InnoDB資料的可靠性。如果寫失效，可以通過重做日誌進行恢複，但是重做日誌中記錄的是對頁的物理操作，如果頁本身損壞，再對其進行重做是沒有意義的。所以，在應用重做日誌前，需要一個頁的副本，當寫入失效發生時，先通過頁的副本來還原該頁，再進行重做，這就是doublewire。
恢複資料=頁副本+重做日誌


650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/2134525120-1.jpg" alt="1fe184a3-3358-3c21-a248-844e1c713e50.jpg" />


3，自適應雜湊索引

InnoDB儲存引擎提出一種自適應雜湊索引，儲存引擎會監控對錶上索引的尋找，如果觀察到建立建立雜湊索引會帶來速度的提升，則建立雜湊索引，所以稱之為自適應的。自適應雜湊索引只能用來搜尋等值的查詢，如select * from table where index_col='***', 此外自適應雜湊是由InnoDB儲存引擎控制的，我們只能通過innodb_adaptive_hash_index來禁用或啟用，預設開啟。

4，重做記錄檔

執行個體和介質失敗，重做記錄檔就能派上用場，如資料庫掉電，InnoDB儲存引擎會使用重做日誌恢複到掉電前的時刻，以此來保證資料的完整性。參數innodb_log_file_size指定了重做記錄檔的大小；innodb_log_file_in_group指定了記錄檔組中重做記錄檔的數量，預設為2，innodb_mirrored_log_groups指定了日誌鏡像檔案組的數量，預設為1，代表只有一個記錄檔組，沒有鏡像；innodb_log_group_home_dir指定了記錄檔組所在路徑，預設在資料庫路徑下。
二進位日誌和重做日誌的區別：首先，二進位日誌會記錄所有與Mysql有關的日誌記錄，包括InnoDB、MyISAM、Heap等其他儲存引擎的日誌。而InnoDB儲存引擎重做日誌只儲存有關其本身的交易記錄；其次內容不同，不管將二進位記錄檔記錄的格式設為STATEMENT還是ROW，又或者是MIXED，其記錄的都是關於一個事務的具體操作內容。而InnoDB儲存引擎的重做記錄檔記錄的關於每個頁的更改的物理情況 。此外，寫入時間不同，二進位記錄檔是在事務提交前進行記錄的，而在事務進行的過程中，不斷有重做日誌條目被 寫入重做記錄檔中。

5，資料表空間檔案

InnoDB預設的資料表空間檔案為ibdata1,可通過show variables like 'innodb_file_per_table'查看每個表是否產生單獨的.idb資料表空間檔案。但是，單獨的資料表空間檔案僅儲存該表的資料、索引和插入緩衝等資訊，其餘資訊還是存放在預設的資料表空間中。每個檔案都可以自動成長

mysql> show variables like "%file_path%";
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:10M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)

innodb老提示我060809 16:49:11 [ERROR] InnoDB: syntax error in innodb_data_file_path

innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
一個指定資料檔案的完全尾碼包括檔案名稱，它的尺寸和數個可選屬性：
file_name:file_size[:autoextend[:max:max_file_size]]
autoextend屬性和後面跟著的屬性只可被用來對innodb_data_file_path行裡最後一個資料檔案。
如果你對最後的資料檔案指定autoextend選項。如果資料檔案耗盡了資料表空間中的自由空間，InnoDB就擴充資料檔案。擴充的幅度是每次8MB。
650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/213452D35-2.jpg" alt="b25d9694-0c64-3767-943d-e0fc714d741f.jpg" />

6，mysql innodb中的鎖
InnoDB儲存引擎鎖的實現和Oracle非常類似，提供一致性的非鎖定讀、行級鎖支援、行級鎖沒有相關的開銷，可以同時得到並發性和一致性。
InnoDB儲存引擎實現了如下兩種標準的行級鎖：
共用鎖定(S Lock)：允許事務讀一行資料；
獨佔鎖定(X Lock)：允許事務刪除或者更新一行資料。
當一個事務已經獲得了行r的共用鎖定，那麼另外的事務可以立即獲得行r的共用鎖定，因為讀取沒有改變行r的資料，我們稱這種情況為鎖相容。但如果有事務想獲得行r的獨佔鎖定，則它必須等待事務釋允許存取r上的共用鎖定————這種情況稱為鎖不相容。

650) this.width=650;" class="magplus" title="點擊查看原始大小圖片" src="http://www.bkjia.com/uploads/allimg/131229/21345240G-3.jpg" alt="b4cc4aea-5c1a-3962-8e61-3b594ce2a7d3.jpg" height="83" width="700" />

在InnoDB Plugin之前，只能通過SHOW FULL PROCESSLIST，SHOW ENGINE INOODB STATUS等命令來查看當前的資料庫請求，然後再判斷當前事務中的鎖的情況。新版本的InnoDB Plugin中，在INFORMATION_SCHEMA架構下添加了INNODB_TRX、INNODB_LOCKS、InnoDB_LOCK_WAITS。通過這三張表，可以更簡單地監控當前的事務並分析可能存在的鎖的問題。

本文出自 “好好活著” 部落格，轉載請與作者聯絡！