MySQL存儲引擎MyISAM和InnoDB區別和作用

本文主要給大家簡單講講MySQL存儲引擎MyISAM和InnoDB區別和作用，相關專業術語大家可以上網查查或者找一些相關書籍補充一下，這里就不涉獵了，我們就直奔主題吧，希望MySQL存儲引擎MyISAM和InnoDB區別和作用可以給大家帶來一些實際幫助。
CREATE TABLE `user` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '唯一碼',
  `age` int(5) NOT NULL COMMENT '年齡',
  `name` varchar(5) NOT NULL COMMENT '名字',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=92 DEFAULT CHARSET=utf8mb4;

B-樹、B樹和B-tree是同一個數據結構，只不過英語翻譯過來之后，有些人誤解了以為是多種樹。所以好多講解樹的數據結構的博客完全是誤導初學者。。。請讀者認真分辨。

MyISAM和InnoDB的索引均采用B+樹數據結構，所以接下來先介紹一下B樹與B+樹。

B樹與B+樹

B樹

B樹是一種多路搜索樹。

1. 定義任意非葉子結點最多只有M個兒子，且M>2。
2. 根結點的兒子數為[2, M]。
3. 除根結點以外的非葉子結點的兒子數為[M/2, M]。
4. 每個結點存放至少M/2-1（取上整）和至多M-1個關鍵字；（至少2個關鍵字）。
5. 非葉子結點的關鍵字個數=指向兒子的指針個數-1。
6. 非葉子結點的關鍵字：K[1], K[2], …, K[M-1]，且K[i] <= K[i+1]。
7. 非葉子結點的指針：P[1], P[2], …,P[M]（其中P[1]指向關鍵字小于K[1]的子樹，P[M]指向關鍵字大于K[M-1]的子樹，其它P[i]指向關鍵字屬于(K[i-1], K[i])的子樹）。
8. 所有葉子結點位于同一層。

下圖是一個M=4階的B樹。

B樹的搜索，從根結點開始，對結點內的關鍵字（有序）序列進行二分查找，如果命中則結束，否則進入查詢關鍵字所屬范圍的兒子結點；重復，直到所對應的是葉子結點。

查找文件29的過程：

1. 根據根結點指針找到文件目錄的根磁盤塊1，將其中的信息導入內存。（磁盤IO操作1次）
2. 此時內存中有兩個文件名17，35和三個存儲其他磁盤頁面地址的數據。根據算法我們發現17<29<35，因此我們找到指針p2。
3. 根據p2指針，我們定位到磁盤塊3，并將其中的信息導入內存。（磁盤IO操作2次）
4. 此時內存中有兩個文件名26，30和三個存儲其他磁盤頁面地址的數據。根據算法我們發現26<29<30，因此我們找到指針p2。
5. 根據p2指針，我們定位到磁盤塊8，并將其中的信息導入內存。（磁盤IO操作3次）
6. 此時內存中有兩個文件名28，29。根據算法我們查找到文件29，并定位了該文件內存的磁盤地址。

下面的動畫是4階B樹插入的過程。

B樹的特性：

1. 關鍵字分布在整顆樹的所有節點。
2. 任何一個關鍵字出現且只出現在一個結點中。
3. 搜索有可能在非葉子結點結束。
4. 其搜索性能等價于在關鍵字全集內做一次二分查找。

B+樹

下圖是一個M=3階的B+樹。

一般在數據庫系統或文件系統中使用的B+Tree結構都在經典B+Tree的基礎上進行了優化，增加了順序訪問指針。

B+樹是B樹的一種變形樹，總結起來，數據庫索引的B+樹與B樹的差異在于：

1. 非葉子結點的子樹指針與關鍵字個數相同。
2. 非葉子結點的子樹指針P[i]，指向關鍵字值屬于[K[i],K[i+1])的子樹（注意，區間是前閉后開）。
3. 為所有葉子結點增加一個鏈指針。
4. 所有關鍵字都在葉子結點出現。

B+樹的特性：

1. 所有關鍵字都出現在葉子結點的鏈表中，且鏈表中的關鍵字是有序的。
2. 搜索只在葉子結點命中。
3. 非葉子結點相當于是葉子結點的索引，葉子結點是存儲關鍵字數據的數據層。

B-/+樹做索引的原因

解釋這個問題之前，需要了解一些基礎知識。

局部性原理與磁盤預讀

由于存儲介質的特性，磁盤本身存取就比主存慢很多，再加上機械運動耗費，磁盤的存取速度往往是主存的幾百分之一，因此為了提高效率，要盡量減少磁盤I/O。為了達到這個目的，磁盤往往不是嚴格按需讀取，而是每次都會預讀，即使只需要一個字節，磁盤也會從這個位置開始，順序向后讀取一定長度的數據放入內存。這樣做的理論依據是計算機科學中著名的局部性原理：

當一個數據被用到時，其附近的數據也通常會馬上被使用——程序運行期間所需要的數據通常比較集中。

由于磁盤順序讀取的效率很高（不需要尋道時間，只需很少的旋轉時間），因此對于具有局部性的程序來說，預讀可以提高I/O效率。

預讀的長度一般為頁的整倍數。頁是計算機管理存儲器的邏輯塊，硬件及操作系統往往將主存和磁盤存儲區分割為連續的大小相等的塊，每個存儲塊稱為一頁（在許多操作系統中，頁得大小通常為4k），主存和磁盤以頁為單位交換數據。當程序要讀取的數據不在主存中時，會觸發一個缺頁異常，此時系統會向磁盤發出讀盤信號，磁盤會找到數據的起始位置并向后連續讀取一頁或幾頁載入內存中，然后異常返回，程序繼續運行。

B-/+樹做索引的原因分析

一般來說，磁盤I/O次數可以用于評價索引結構的優劣。在B-Tree中查找，可知檢索一次最多需要訪問h個節點（上文舉例查找文件29的過程）。數據庫系統的設計者巧妙利用了磁盤預讀原理，將一個節點的大小設為等于一個頁，這樣每個節點只需要一次I/O就可以完全載入。

為了達到這個目的，在實際實現中，B樹還使用如下技巧：

1. 每次新建節點時，直接申請一個頁的空間，這樣就保證一個節點物理上也存儲在一個頁里，加之計算機存儲分配都是按頁對齊的，就實現了一個節點只需一次I/O。
2. B樹中一次檢索最多需要h-1次I/O（根節點常駐內存）。一般實際應用中，出度d（樹的分叉數）是非常大的數字，通常超過100；h非常小，通常不超過3。

綜上所述，用B樹作為索引結構效率是非常高的。

紅黑樹或者平衡二叉樹的其他樹結構，

1. h明顯要深的多，執行效率低。
2. 邏輯上很近的節點（父子）物理上可能很遠，無法利用局部性，
3. 每個節點存儲的數據量太小了，對磁盤空間造成浪費，帶來頻繁的IO操作。

所以其他樹結構的效率明顯比B樹差很多。

相對B樹，B+樹做索引的優勢

1. B+樹的磁盤讀寫代價更低：B+樹的內部節點并沒有指向關鍵字具體信息的指針，因此其內部節點相對B樹更小，如果把所有同一內部節點的關鍵字存放在同一盤塊中，那么盤塊所能容納的關鍵字數量也越多，一次性讀入內存的需要查找的關鍵字也就越多，相對IO讀寫次數就降低了。
2. B+樹的查詢效率更加穩定：由于所有數據都存于葉子節點。所有關鍵字查詢的路徑長度相同，每一個數據的查詢效率相當。
3. B樹在提高了IO性能的同時并沒有解決元素遍歷的我效率低下的問題，正是為了解決這個問題，B+樹應用而生。B+樹只需要去遍歷葉子節點就可以實現整棵樹的遍歷。

筆者認為第三條原因才是MySQL使用B+樹而不是B樹做索引的主要原因，畢竟MongoDB的索引是B樹，所以兩種數據結構并沒有絕對的好壞，要看實際的業務需求。

MyISAM

磁盤存儲

MyISAM在磁盤存儲上有三個文件，每個文件名以表名開頭，擴展名指出文件類型。

1. .frm：用于存儲表的定義。
2. .MYD：用于存放數據。
3. .MYI：用于存放表索引。

索引

主鍵索引

MyISAM引擎使用B+樹作為索引結果，葉節點的data域存放的是數據記錄的地址。

MyISAM索引文件和數據文件是分離的，索引文件僅保存記錄所在頁的指針（物理位置），通過這些地址來讀取頁，進而讀取被索引的行。

樹中葉子保存的是對應行的物理位置。通過該值，存儲引擎能順利地進行回表查詢，得到一行完整記錄。同時，每個葉子頁也保存了指向下一個葉子頁的指針。從而方便葉子節點的范圍遍歷。

輔助索引

在MyISAM中，主鍵索引和輔助索引在結構上沒有任何區別，只是主鍵索引要求key是唯一的，而輔助索引的key可以重復。

Innodb

MySQL5.5開始支持InnoDB引擎，并將其作為默認數據庫引擎。

磁盤存儲

Innodb有兩種存儲方式，共享表空間存儲和多表空間存儲。

Innodb只有表結構文件和數據文件。

表結構文件和MyISAM一樣，以表名開頭，擴展名是.frm。

數據文件與存儲方式有關：

• 如果使用共享表空間，那么所有表的數據文件和索引文件都保存在一個表空間里，一個表空間可以有多個文件，通過innodb_data_file_path和innodb_data_home_dir參數設置共享表空間的位置和名字，一般共享表空間的名字叫ibdata1-n。
• 如果使用多表空間，那么每個表都有一個表空間文件用于存儲每個表的數據和索引，文件名以表名開頭，以.ibd為擴展名。

索引

主鍵索引

Innodb主鍵索引中，既存儲了主鍵值，又存儲了行數據。

輔助索引

對于輔助索引，InnoDB采用的方式是在葉子頁中保存主鍵值，通過這個主鍵值來回表（上圖）查詢到一條完整記錄，因此按輔助索引檢索實際上進行了二次查詢，效率肯定是沒有按照主鍵檢索高的。

Innodb與MyISAM的區別

1. 存儲結構

MyISAM存儲表分為三個文件frm（表結構）、MYD（表數據）、MYI（表索引），而Innodb如上文所說，根據存儲方式不同，存儲結構不同。

2. 事務支持

MyISAM不支持事務，而Innodb支持事務，具有事務、回滾和恢復的事務安全。

3. 外鍵和主鍵

MyISAM不支持外鍵，而Innodb支持外鍵。MyISAM允許沒有主鍵，但是Innodb必須有主鍵，若未指定主鍵，會自動生成長度為6字節的主鍵。

4. 鎖

MyISAM只支持表級鎖，而Innodb支持行級鎖，具有比較好的并發性能，但是行級鎖只有在where子句是對主鍵篩選才生效，非主鍵where會鎖全表

5. 索引

MyISAM使用B+樹作為索引結構，葉節點保存的是存儲數據的地址，主鍵索引key值唯一，輔助索引key可以重復，二者在結構上相同。Innodb也是用B+樹作為索引結構，數據表本身就是按照b+樹組織，葉節點key值為數據記錄的主鍵，data域為完整的數據記錄，輔助索引data域保存的是數據記錄的主鍵。

FAQ

MongoDB的索引為什么選擇B樹，而Mysql的索引是B+樹

MongoDB不是傳統的關系性數據庫，而是以Json格式作為存儲的nosql，目的就是高性能，高可用，易擴展。首先它擺脫了關系模型，所以范圍查詢和遍歷查詢的需求就沒那么強烈了，其次Mysql由于使用B+樹，數據都在葉節點上，每次查詢都需要訪問到葉節點，而MongoDB使用B-樹，所有節點都有Data域，只要找到指定索引就可以進行訪問。

總體來說，Mysql選用B+樹和MongoDB選用B-樹還是以自己的需求來選擇的。

索引有關的名詞解釋

普通索引

用表中的普通列構建的索引，沒有任何限制

唯一索引

唯一索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。

主鍵索引

根據主鍵建立索引，不允許重復，不允許空值；

全文索引

僅可用于MyISAM表，針對較大的數據，生成全文索引非常的消耗時間和空間（在生成FULLTEXT索引時，會為文本生成一份單詞的清單，在索引時及根據這個單詞的清單來索引）。

組合索引

又叫聯合索引。用多個列組合構建的索引，這多個列中的值不允許有空值。可以在創建表的時候指定，也可以修改表結構。

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

為了更多的提高mysql效率可建立組合索引，遵循”最左前綴“原則。創建復合索引時應該將最常用（頻率）作限制條件的列放在最左邊，依次遞減。示例的組合索引相當于建立了col1,col1col2,col1col2col3三個索引，而col2或者col3是不能使用索引的。

最左前綴規則

假設聯合索引由列(a,b,c)組成，則一下順序滿足最左前綴規則：a、ab、abc；selece、where、order by 、group by都可以匹配最左前綴。其它情況都不滿足最左前綴規則就不會用到聯合索引。

聚集索引

定義：數據行的物理順序與列值（一般是主鍵的那一列）的邏輯順序相同，一個表中只能擁有一個聚集索引。

如果定義了主鍵，Innodb會選擇主鍵作為聚集索引；如果沒有定義主鍵，Innodb會選擇不包含NULL值的唯一索引作為聚集索引；如果也沒有這樣的唯一索引列，Innodb會選擇內置6字節長的rowID作為隱含的聚集索引，這里的RowId會隨著記錄的寫入而主鍵自增，但是它是不可引用和查看的，是數據庫引擎內部的使用。

如果我們使用自增主鍵，那么每次插入的新紀錄都在原先記錄的尾部按照順序，添加到當前節點的索引后面，當一頁快寫滿的時候，就會開辟一個新的頁。數據記錄本身就存與主索引的葉子節點上，B+tree的樹。這就要求每一個葉子節點內的各條數據記錄按主鍵順序存放，因此每當有一條新的記錄插入的時候，MYSQL會根據其主鍵將其插入到合適的節點和位置上，如果頁面達到裝載因子（INNODB默認為15/16），則開辟新的頁面（節點）

如果使用非自增主鍵（如果身份證號或學號等），由于每次插入主鍵的值近似于隨機，因此每次新紀錄都要被插到現有索引頁得中間某個位置，此時MySQL不得不為了將新記錄插到合適位置而移動數據，甚至目標頁面可能已經被回寫到磁盤上而從緩存中清掉，此時又要從磁盤上讀回來，這增加了很多開銷，同時頻繁的移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結構，后續不得不通過OPTIMIZE TABLE來重建表并優化填充頁面。

非聚集索引

定義：該索引中索引的邏輯順序與磁盤上行的物理存儲順序不同，一個表中可以擁有多個非聚集索引。

除了InnoDB的主鍵索引，在mysql中的其他索引形式都是非聚集索引。

覆蓋索引

指從輔助索引中就能獲取到需要的記錄，而不需要查找主鍵索引中的記錄。使用覆蓋索引的一個好處是因為輔助索引不包括一條記錄的整行信息，所以數據量較聚集索引要少，可以減少大量io操作。

覆蓋查詢失效

1. select選擇的字段中含有不在索引中的字段 ，即索引沒有覆蓋全部的列。
2. where條件中不能含有對索引進行like的操作。

MySQL存儲引擎MyISAM和InnoDB區別和作用就先給大家講到這里，對于其它相關問題大家想要了解的可以持續關注我們的行業資訊。我們的板塊內容每天都會捕捉一些行業新聞及專業知識分享給大家的。