mysql 技術内幕：InnoDB存儲引擎

一．mysql體系結構和存儲引擎

資料庫：實體作業系統或其他形式檔案類型的集合。在mysql下資料庫檔案可以是frm，myd，myi，ibd結尾的檔案。

資料庫執行個體：由資料庫背景程序/線程以及一個共享記憶體區組成。資料庫執行個體才是真正用來操作資料庫檔案的。

mysql資料庫是單程序多線程的程式，與sql server比較類似。也就是說，Mysql資料庫執行個體在系統上的表現就是一個程序。

mysql由連接配接池元件、管理服務和工具元件、sql接口組建、查詢分析器元件、優化器元件、緩存元件、插件是存儲引擎、實體檔案。

1.3.1、innodb存儲引擎，特點支援外鍵、行鎖、非鎖定讀(預設情況下讀取不會産生鎖)、mysql-4.1開始支援每個innodb引擎的表單獨放到一個表空間裡。innodb通過使用MVCC來擷取高并發性，并且實作sql标準的4種隔離級别，同時使用一種被稱成next-key locking的政策來避免換讀(phantom)現象。除此之外innodb引擎還提供了插入緩存(insert buffer)、二次寫(double write)、自适應哈西索引(adaptive hash index)、預讀(read ahead)等高性能技術。

1.3.2、myisam存儲引擎，myisam特點是不支援事物，适合olap應用，myisam表由MYD和MYI組成。mysql-5.0版本之前，myisam預設支援的表大小為4G，從mysql-5.0以後，myisam預設支援256T的表單資料。myisam隻緩存索引資料。

1.3.3、NDB存儲引擎，特點是資料放在記憶體中，mysql-5.1版本開始可以将非索引資料放到磁盤上。NDB之前的缺陷是join查詢是mysql資料庫層完成的，而不是存儲引擎完成的，複雜的join查詢需要巨大的網絡開銷，速度很慢。目前mysql cluster7.2版本中已經解決此問題，join查詢效率提高了70倍。

1.3.4、memeory存儲引擎，将資料放到記憶體中，預設使用hash索引，不支援text和blob類型，varchara是按照char的方式來存儲的。mysql資料庫使用memory存儲引擎作為臨時表還存儲中間結果集(intermediate result)，如果中間集結果大于memorg表的容量設定，又或者中間結果集包含text和blog列類型字段，則mysql會把他們轉換到myisam存儲引擎表而放到磁盤上，會對查詢産生性能影響。

1.3.5、archive存儲引擎，壓縮能力較強，主要用于歸檔存儲。

1.3.6、federated存儲引擎，不存儲資料，他指向一台遠端mysql資料庫上的表。

1.3.7、maria存儲引擎，myisam的後續版本,支援緩存資料和索引，行鎖設計，支援mvcc，支援事務和非事務安全的選項，以及更好的BLOG字元類型的處理性能。

1.3.8、其他存儲引擎，sphinx用于全文索引，infobright用于資料倉庫。

1.4.1、TCP/IP：基于網絡的連接配接，連接配接進行權限檢查。

1.4.2、命名管道和共享記憶體：Windows系統上同一伺服器上的兩程序可通過命名管道連接配接，需在配置檔案中啟用--enable-named-pipe選項。

1.4.3、Unix套接字：用戶端與服務端位于同一伺服器時才可使用，可以在my.cnf中指定-socket=/tmp/mysql.sock，連接配接時指定./mysql -S/tmp/mysql.sock。

二．InnoDB存儲引擎

InnoDB的多個記憶體塊組成了記憶體池，負責如下工作：

1).維護所有程序/線程需要通路的多個内部資料結構。

2).緩存磁盤上的資料，友善快速的讀取，并且在對磁盤檔案的資料進行修改之前在這裡緩存。

3).重做日志緩存。

背景線程的主要作用是負責重新整理記憶體池中的資料，保證緩沖池中的記憶體緩存是最近的資料，此外、将已經修改的資料檔案重新整理到磁盤檔案

innodb存儲引擎背景有7個線程，—–4個IO線程(insert buffer thread,log thread,read thread,write thread)，1個master thread，一個lock監控線程，一個錯誤監控線程。

innodb存儲引擎記憶體由以下三個部分組成：緩沖池(buffer pool),重做日志緩存(redo log buffer),額外的記憶體池(additional memory pool)。可以使用 show engine innodb status來檢視innodb_buffer_pool的使用情況。

innodb_buffer_pool_size：具體看，緩沖池中的資料庫類型有：索引頁、資料庫頁、undo頁、插入緩存頁(insert buffer)、自适應hash(adaptive hashindex)、innodb存儲的鎖資訊(lock info)、資料字典資訊(data dictionary)。

InnoDB工作方式：将資料檔案按頁(每頁16K)讀入InnoDBbuffer pool，然後按最近最少使用算法(LRU)保留緩存資料，最後通過一定頻率将髒頁重新整理到檔案。

1、由于硬體的發展，現在的硬體性能已經提高了很多，如果innodb每秒最大重新整理100個髒頁，那麼效率會很低，為了解決這個問題，innodb plugin提供了一個參數innodb_io_capacity，用來表示磁盤IO的吞吐量，預設值是200，規則如下：在合并插入緩存時，合并插入緩存的數量為innodb_io_capacity的5%；在從緩沖區重新整理髒頁時，啥新髒頁的數量為innodb_io_capacity。

2、關于innodb_max_dirty_pages_pct值的争議，如果值過大，記憶體也很大或者伺服器壓力很大，那麼效率很降低，如果設定的值過小，那麼硬碟的壓力會增加，建議是在75-80.并且innodb plugin引進了innodb_adaptive_flushng(自适應的重新整理)，該值影響每秒重新整理髒頁的數量。

當一個表有非聚集索引時，對于非聚集索引的葉子節點的插入不是順序的，這時候需要離散的通路非聚集索引頁，性能就在這裡降低了，這是由于b+樹的原理導緻的。插入緩存就是用來解決這個問題的。

對于非聚集索引的插入和更新操作，不是每一次都直接插入索引頁，而是先判斷插入的非聚集索引頁是否在緩存中，如果在就直接插入，如果不在就放入到一個插入緩沖區中，好似欺騙資料庫這個非聚集索引已經插入到葉子節點了。然後再以一定的頻率插入緩存和非聚集索引頁位元組點的合并操作。

插入緩存的使用需要滿足以下兩個條件(也就是非唯一的輔助索引)：索引是輔助索引；索引不是唯一的。

兩次寫給innodb帶來的是可靠性，主要用來解決部分寫失敗(partial page write)。在應用重做日之前，我們需要一個頁的副本，當寫入失效發生時，先通過頁的副本來還原該頁，再進行重做，這就是doublewrite。

doublewrite有兩部分組成，一部分是記憶體中的doublewrite buffer，大小為2M，另外一部分就是實體磁盤上的共享表空間中聯系的128個頁，即兩個區，大小同樣為2M。當緩沖池的張也重新整理時，并不直接寫硬碟，而是回通過memcpy函數将髒頁先拷貝到記憶體中的doublewrite buffer，之後通過doublewrite buffer再分兩次寫，每次寫入1M到共享表空間的實體磁盤上，然後馬上調用fsync函數，同步磁盤。

由于innodb不支援hash索引，但是在某些情況下hash索引的效率很高，于是出現了 adaptive hash index功能，innodb存儲引擎會監控對表上索引的查找，如果觀察到建立hash索引可以提高性能的時候，則自動建立hash索引。

innodb_fast_shutdown影響InnoDB表關閉。該參數有0、1、2三個參數。

0 MySQL關閉時 完成所有的full purge和merge insertbuffer操作

1預設值 隻将緩沖池内的一些髒頁重新整理至磁盤

2将日志都寫入日志檔案不會有任何事務丢失但下次啟動時會進行recovery

innodb_force_recovery影響整個innodb存儲引擎的恢複狀況，該值預設為0，表示當需要恢複時，需要執行所有的恢複操作，當不能進行有效恢複時，如資料頁發生了corruption，mysql資料庫可能當機，并把錯誤寫入錯誤日志中。

三．檔案

Mysql執行個體可以不需要參數檔案，這是所有的參數值取決于編譯Mysql時指定的預設值和源代碼中指定參數的預設值。其參數檔案是Mysql.cnf。

參數是一個鍵/值對。可以使用show variables like指令檢視，也可以通過information_schema的GLOBAL_VARIABLES視圖來查找。

參數檔案分為兩類：動态參數和靜态參數。動态參數意味着你可以在Mysql執行個體運作中進行更改；靜态參數說明在整個執行個體生命周期内都不得進行更改，好像是隻讀的。對于動态參數，又可以分為global和session關鍵字，表明該參數的修改是基于目前會話還是真格執行個體的生命周期。有些動态參數隻能在會話中進行修改，如autocommit；有些參數修改完後，在整個執行個體生命周期中都會生效，如binlog_cache_size；而有些參數既可以在會話又可以在整個執行個體的生命周期内生效，如read_buffer_size。

錯誤日志對Mysql的啟動、運作、關閉過程進行了記錄。出現Mysql不能正常啟動時，第一個必須查找的檔案應該就是錯誤日志檔案。使用show variables like ‘log_error’來定位檔案。

慢查詢能為SQL語句的優化帶來很好的幫助。設定一個閥值，将運作時間超過該值的所有SQL語句都記錄到慢查詢日志檔案中。用參數long_query_time來設定。另一個參數log_queries_not_using_indexes，若運作的SQL語句沒有使用索引，則這條SQL語句會被記錄下來。

查詢日志記錄了所有對Mysql請求的資訊，不論這些請求是否得到正确的執行。預設檔案名為：主機名.log。

二進制記錄了對資料庫執行更改的所有操作，但是不包括SELECT和SHOW操作，還包括了執行時間和更改操作時間。可用來恢複某些資料，同時也可以用來複制同步遠端資料庫。将binlog_format設定成row，可以支援事務隔離級别為READ COMMITTED，以獲得更好的并發性。在使用MIXED格式下，mysql采用STATEMENT格式進行二進制日志檔案的記錄，但是有一些情況下會使用ROW格式，可能的情況如下：

1、表的存儲引擎為NDB，這個時候DML操作都會以ROW格式記錄。

2、使用了uuid()、user(),current_user(),found_rows(),row_count(),等不确定函數。

3、使用了insert delay語句

4、使用了使用者定于的函數(UDF)

5、使用了臨時表(temporary table)

注意：針對系統庫mysql裡面的表發生變化的處理規則如下：

1、 如果采用insert，update，delete直接操作表，則日志根據binlog_format設定的格式記錄。

2、 如果使用grant，revoke，set password等DCL語句，那麼無論如何都會使用SBR模式記錄。

3、 blockhole引擎不支援row格式，ndb引擎不支援statement格式。

Unix系統下本地連接配接Mysql可以采用Unix套接字方法，需要一個套接字檔案，可以使用show variableslike ‘socket’查詢。

pid檔案是執行個體啟動是記錄自己程序ID号的檔案，表結構定義檔案是以frm為字尾名的檔案，還可以用來存放視圖的定義。

預設表空間檔案為ibdata1檔案innodb_data_file_path存儲資料，innodb_file_per_table可以按表分别産生一個表空間.db檔案，但僅存該表的資料索引和插入緩沖等資訊，其他資訊如undo資訊，系統事務資訊，double write buffer等還是存放在預設表空間(ibdata1或表空間組)裡。

redo log是在執行個體或者媒體失敗的時候，用來保證資料完整性。每個innodb存儲引擎至少有一個重做日志組,每個重做日志檔案組下至少又2個重做日志檔案，如預設的ib_logfile0、ib_logfile1.為了得到更高的可靠性，你可以設定多個重做鏡像日志組。

因為重做日志條目先被寫到日志緩沖中，然後根據一定條件重新整理到磁盤重做日志檔案中。與redo log相關的就是innodb_flush_log_at_trx_commit的值，對innodb的性能影響很大。他有0，1，2三個值，0代表送出事務時，并不同步寫redo log，而是等master threas每秒寫。1代表commit的時候就将redo log緩存寫入磁盤，2代表commit的時候将redo log緩存異步的寫入磁盤。

原文：http://blog.csdn.net/yingminxing/article/details/8268484

感謝原部落客：瑛民星