淺談MYSQL引擎之INNODB引擎

MYSQL 常用的引擎主要有一下幾種，MRG_MYISAM 、CSV 、MyISAM、InnoDB、MEMORY ，NDB，其中MyISAM、InnoDB是mysql最常用的存儲引擎，今天主要討論 InnoDB引擎。

一、什么是InnoDB引擎

InnoDB引擎是MYSQL數(shù)據(jù)庫的另一個重要的額存儲引擎，正成為目前MYSQL AB所有發(fā)行新版的標準，被包含在所有二進制安裝包里。

和其他的存儲引擎相比，InnoDB引擎的優(yōu)點支持兼容ACID的事物，以及參數(shù)完整性(即對外建的支持)。

MYSQL5.5.5以后數(shù)據(jù)庫的默認存儲引擎為InnoDB引擎

二、InnoDB引擎的特點

1、支持事物：支持4個事物隔離級別，支持多版本讀（ACID :原子性、一致性、隔離性、持久性.）

2、行級鎖定(更新時一般是鎖定當前行，InnoDB鎖定在行級并且也在SELECT語句提供一個Oracle風格一致的非 鎖定讀)：通過索引實現(xiàn)，全表掃描仍然會是鎖表。

3、讀寫阻塞與事物隔離級別有關

4、具有非常高效的緩存特性：能緩存索引，也能緩存數(shù)據(jù)（InnoDB存儲引擎為在主內(nèi)存中緩存數(shù)據(jù)和索引而維持它自己的緩沖池。 InnoDB存儲它的表＆索引在一個表空間中，表空間可以包含多個文件（或原始磁盤分區(qū)））

5、整個表和主鍵以Cluster方式存儲，組成一顆平衡樹

6、所有Secondar Index都會保存主鍵信息

7、支持分區(qū)，表空間,類似oracle數(shù)據(jù)庫

8、支持外鍵約束，5.5版本以后支持全文索引

三、事務的四大特性(ACID)

1、原子性

事務是一個不可分割的單位,事務中的所有SQL燈操作要么都發(fā)生，要么都不發(fā)生.

2、一致性

事務發(fā)生之前和發(fā)生之后，數(shù)據(jù)的完整性必須保持一致.

3、隔離性

當并發(fā)訪問數(shù)據(jù)庫時，一個正在執(zhí)行的事務在執(zhí)行完畢前，對于其他的會話是不可見的，多個并發(fā)事務之間的數(shù)據(jù)相互隔離的。

4、持久性

一個事務一旦被提交，它對數(shù)據(jù)庫中的數(shù)據(jù)改變就是永久性的，如果出了錯誤，事務也不允許撤銷，只能通過“補償性事務”.

四、InnoDB引擎架構

    InnoDB的多個內(nèi)存塊組成了內(nèi)存池，負責如下工作：

     1、維護所有進程/線程需要訪問的多個內(nèi)部數(shù)據(jù)結構。

   2、緩存磁盤上的數(shù)據(jù)，方便快速的讀取，并且在對磁盤文件的數(shù)據(jù)進行修改之前在這里緩存。

   3、重做日志緩存。

 后臺線程的主要作用是負責刷新內(nèi)存池中的數(shù)據(jù)，保證緩沖池中的內(nèi)存緩存是最近的數(shù)據(jù)，此外、將已經(jīng)修改的數(shù)據(jù)文件刷 新到磁盤文件

  后臺線程

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

  內(nèi)存

innodb存儲引擎內(nèi)存由以下三個部分組成：緩沖池(buffer pool),重做日志緩存(redo log buffer),額外的內(nèi)存池(additional

 memory pool)。可以使用 show engine innodb status來查看innodb_buffer_pool的使用情況。

innodb_buffer_pool_size：具體看，緩沖池中的數(shù)據(jù)庫類型有：索引頁、數(shù)據(jù)庫頁、undo頁、插入緩存頁(insert buffer)、自適應

hash(adaptive hashindex)、innodb存儲的鎖信息(lock info)、數(shù)據(jù)字典信息(data dictionary)。

InnoDB工作方式：將數(shù)據(jù)文件按頁(每頁16K)讀入InnoDBbuffer pool，然后按最近最少使用算法(LRU)保留緩存數(shù)據(jù)，最后通過一定頻

率將臟頁刷新到文件。

五、InnoDB引擎適用的生產(chǎn)業(yè)務場景

1、需要事務支持的業(yè)務(具有較好的食物特性)

2、行級鎖定對高并發(fā)有很好的適應能力，但需要確保查詢是通過索引完成

3、數(shù)據(jù)更新較為頻繁的場景

4、數(shù)據(jù)一致性要求較高的業(yè)務

5、硬件設備內(nèi)存較大，可以利用InnoDB較好的緩存能力來提高內(nèi)存利用率，盡可能減少磁盤IO

六、INNODB表空間

默認表空間文件為ibdata1文件innodb_data_file_path存儲數(shù)據(jù)，innodb_file_per_table可以按表分別產(chǎn)生一個表空間.db文件，但僅存該表的

數(shù)據(jù)索引和插入緩沖等信息，其他信息如undo信息，系統(tǒng)事務信息，double write buffer等還是存放在默認表空間(ibdata1或表空間組)里。

七、關鍵特性,為innodb提高性能的技術

   1、插入緩存

當一個表有非聚集索引時，對于非聚集索引的葉子節(jié)點的插入不是順序的，這時候需要離散的訪問非聚集索引頁，性能就在這里降 

 低了，這是由于b+樹的原理導致的。插入緩存就是用來解決這個問題的。對于非聚集索引的插入和更新操作，不是每一次都直接插入

索引頁，而是先判斷插入的非聚集索引頁是否在緩存中，如果在就直接插入，如果不在就放入到一個插入緩沖區(qū)中，好似欺騙數(shù)據(jù)庫

這個非聚集索引已經(jīng)插入到葉子節(jié)點了。然后再以一定的頻率插入緩存和非聚集索引頁字節(jié)點的合并操作。插入緩存的使用需要滿足

以下兩個條件(也就是非唯一的輔助索引)：索引是輔助索引；索引不是唯一的。

  2、兩次寫

      兩次寫給innodb帶來的是可靠性，主要用來解決部分寫失敗(partial page write)。在應用重做日之前，我們需要一個頁的副本，當寫入失效

     發(fā)生時，先通過頁的副本來還原該頁，再進行重做，這就是doublewrite。

      doublewrite有兩部分組成，一部分是內(nèi)存中的doublewrite buffer，大小為2M，另外一部分就是物理磁盤上的共享表空間中聯(lián)系的128個頁，

    即兩個區(qū)，大小同樣為2M。當緩沖池的張也刷新時，并不直接寫硬盤，而是回通過memcpy函數(shù)將臟頁先拷貝到內(nèi)存中的doublewrite

     buffer，之后通過doublewrite buffer再分兩次寫，每次寫入1M到共享表空間的物理磁盤上，然后馬上調(diào)用fsync函數(shù)，同步磁盤。

  3、自適應哈西索引

由于innodb不支持hash索引，但是在某些情況下hash索引的效率很高，于是出現(xiàn)了 adaptive hash index功能，innodb存儲引擎會監(jiān)控對表上

索引的查找，如果觀察到建立hash索引可以提高性能的時候，則自動建立hash索引

 4、啟動、關閉、恢復

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

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

      1 默認值 只將緩沖池內(nèi)的一些臟頁刷新至磁盤

      2 將日志都寫入日志文件不會有任何事務丟失但下次啟動時會進行recovery

     innodb_force_recovery影響整個innodb存儲引擎的恢復狀況，該值默認為0，表示當需要恢復時，需要執(zhí)行所有的恢復操作，當不能進行有      效恢復時，如數(shù)據(jù)頁發(fā)生了corruption，mysql數(shù)據(jù)庫可能宕機，并把錯誤寫入錯誤日志中。

八、InnoDB引擎調(diào)優(yōu)精要

1、主鍵盡可能小，避免給Secondar Index帶來過大的空間負擔

2、避免全表掃描，因為會使用表級鎖

3、盡可能緩存所有的索引和數(shù)據(jù)。，提高響應速度，減少磁盤IO消耗

4、在大批量插入的時候，盡量自己控制事物而不要使用autocommit自動提交有開關可以控制提交方式

【  取消自動提交事物

  mysql> set global init_connect="set autocommit=0";

  init_connect='SET autocommit=0' // 在mysqld里面加上這些內(nèi)容  

】

5、合理設置innodb_flush_log_at_trx_commit參數(shù)值，不要過度追求安全性、

  (如果innodb_flush_log_at_trx_commit=0 log buffer 每秒就會被刷寫日志文件到磁盤，提交事物的時候不做任何操作)

6避免主鍵更新，因為這會帶來大量的數(shù)據(jù)移動

九、生產(chǎn)環(huán)境中如何批量更改mysql引擎

alter table table_name ENGINE = INNODB

十、InnoDB與MYISAM區(qū)別總結

1.InnoDB不支持FULLTEXT類型的索引。 

2.InnoDB 中不保存表的具體行數(shù)，也就是說，執(zhí)行select count(*) from table時，InnoDB要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出保存好的行數(shù)即可。注意的是，當count(*)語句包含 where條件時，兩種表的操作是一樣的。 

在執(zhí)行數(shù)據(jù)庫寫入的操作（insert,update,delete）的時候，mysiam表會鎖表，而innodb表會鎖行。通俗點說，就是你執(zhí)行了一個update語句，那么mysiam表會將整個表都鎖住，其他的insert和delete、update都會被拒之門外，等到這個update語句執(zhí)行完成后才會被依次執(zhí)行

另外，InnoDB表的行鎖也不是絕對的，如果在執(zhí)行一個SQL語句時MySQL不能確定要掃描的范圍，InnoDB表同樣會鎖全表，例如update table set num=1 where name like “%aaa%” 

3.對于AUTO_INCREMENT類型的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中，可以和其他字段一起建立聯(lián)合索引。 

4.DELETE FROM table時，InnoDB不會重新建立表，而是一行一行的刪除。 

5.LOAD TABLE FROM MASTER操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導入數(shù)據(jù)后再改成InnoDB表，但是對于使用的額外的InnoDB特性（例如外鍵）的表不適用。 

十一、InnoDB與MYISAM如何選擇

    當你的數(shù)據(jù)庫有大量的寫入、更新操作而查詢比較少或者數(shù)據(jù)完整性要求比較高的時候就選擇innodb表。當你的數(shù)據(jù)庫主要以查詢?yōu)橹?，相比較而言更新和寫入比較少，并且業(yè)務方面數(shù)據(jù)完整性要求不那么嚴格，就選擇mysiam表。因為mysiam表的查詢操作效率和速度都比innodb要快。