Mysql中MyISAM和InnoDB的區別及說明

2022-12-27 14:02:08

MyISAM和InnoDB的區別

1. 定義

InnoDB：

InnoDB：MySQL預設的事務型引擎，也是最重要和使用最廣泛的儲存引擎。

它被設計成為大量的短期事務，短期事務大部分情況下是正常提交的，很少被回滾。InnoDB的效能與自動崩潰恢復的特性，使得它在非事務儲存需求中也很流行。除非有非常特別的原因需要使用其他的儲存引擎，否則應該優先考慮InnoDB引擎。

MyISAM：

MyISAM：在MySQL 5.5 及之前的版本，MyISAM是預設引擎。

MyISAM提供的大量的特性，包括全文索引、壓縮、空間函數（GIS）等，但MyISAM並不支援事務以及行級鎖，而且一個毫無疑問的缺陷是崩潰後無法安全恢復。正是由於MyISAM引擎的緣故，即使MySQL支援事務已經很長時間了，在很多人的概念中MySQL還是非事務型資料庫。儘管這樣，它並不是一無是處的。對於唯讀的資料，或者表比較小，可以忍受修復操作，則依然可以使用MyISAM（但請不要預設使用MyISAM，而是應該預設使用InnoDB）

2. 區別

InnoDB 支援事務，MyISAM 不支援事務。這是 MySQL 將預設儲存引擎從 MyISAM 變成 InnoDB 的重要原因之一；

InnoDB 支援外來鍵，而 MyISAM 不支援。對一個包含外來鍵的 InnoDB 錶轉為 MYISAM 會失敗；

InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的檔案存放在主鍵索引的葉子節點上，因此 InnoDB 必須要有主鍵，通過主鍵索引效率很高。但是輔助索引需要兩次查詢，先查詢到主鍵，然後再通過主鍵查詢到資料。因此，主鍵不應該過大，因為主鍵太大，其他索引也都會很大。而 MyISAM 是非聚集索引，資料檔案是分離的，索引儲存的是資料檔案的指標。主鍵索引和輔助索引是獨立的。

InnoDB 不儲存表的具體行數，執行 select count(*) from table 時需要全表掃描。而MyISAM 用一個變數儲存了整個表的行數，執行上述語句時只需要讀出該變數即可，速度很快；

InnoDB 最小的鎖粒度是行鎖，MyISAM 最小的鎖粒度是表鎖。一個更新語句會鎖住整張表，導致其他查詢和更新都會被阻塞，因此並行存取受限。

這也是 MySQL 將預設儲存引擎從 MyISAM 變成 InnoDB 的重要原因之一；

3. 使用

是否要支援事務，如果要請選擇 InnoDB，如果不需要可以考慮 MyISAM；

如果表中絕大多數都只是讀查詢，可以考慮 MyISAM，如果既有讀寫也挺頻繁，請使用InnoDB。

系統奔潰後，MyISAM恢復起來更困難，能否接受，不能接受就選 InnoDB；

MySQL5.5版本開始Innodb已經成為Mysql的預設引擎(之前是MyISAM)，說明其優勢是有目共睹的。如果你不知道用什麼儲存引擎，那就用InnoDB，至少不會差。

MyISAM和InnoDB索引結構分析

儲存引擎作用於什麼物件

儲存引擎是作用在表上的，而不是資料庫。

MyISAM和InnoDB對索引和資料的儲存在磁碟上是如何體現的

先來看下面建立的兩張表資訊，role表使用的儲存引擎是MyISAM，而user使用的是InnoDB：

再來看下兩張表在磁碟中的索引檔案和資料檔案：

1. role表有三個檔案，對應如下：

role.frm：表結構檔案
role.MYD：資料檔案（MyISAM Data）
role.MYI：索引檔案（MyISAM Index）

2. user表有兩個檔案，對應如下：

user.frm：表結構檔案
user.ibd：索引和資料檔案（InnoDB Data）

也由於兩種引擎對索引和資料的儲存方式的不同，我們也稱MyISAM的索引為非聚集索引，InnoDB的索引為聚集索引。

MyISAM主鍵索引與輔助索引的結構

我們先列舉一部分資料出來分析，如下：

上面已經說明了MyISAM引擎的索引檔案和資料檔案是分離的，我們接著看一下下面兩種索引結構異同。

1. 主鍵索引：

上一篇文章已經介紹過資料庫索引是採用B+Tree儲存，並且只在葉子節點儲存資料，在MyISAM引擎中葉子結點儲存的資料其實是索引和資料的檔案指標兩類。

如下圖中我們以Col1列作為主鍵建立索引，對應的葉子結點儲存形式可以看一下表格。

通過索引查詢資料的流程：先從索引檔案中查詢到索引節點，從中拿到資料的檔案指標，再到資料檔案中通過檔案指標定位了具體的資料。

2. 輔助（非主鍵）索引：

以Col2列建立索引，得到的輔助索引結構跟上面的主鍵索引的結構是相同的。

InnoDB主鍵索引與輔助索引的結構

1. 主鍵索引：

我們已經知道InnoDB索引是聚集索引，它的索引和資料是存入同一個.idb檔案中的，因此它的索引結構是在同一個樹節點中同時存放索引和資料，如下圖中最底層的葉子節點有三行資料，對應於資料表中的Col1、Col2、Col3資料項。

2. 輔助（非主鍵）索引：

這次我們以資料表中的Col3列的字串資料建立輔助索引，它的索引結構跟主鍵索引的結構有很大差別，我們來看下面的圖：

在最底層的葉子結點有兩行資料，第一行的字串是輔助索引，按照ASCII碼進行排序，第二行的整數是主鍵的值。

InnoDB索引結構需要注意的點

1. 資料檔案本身就是索引檔案
2. 表資料檔案本身就是按B+Tree組織的一個索引結構檔案
3. 聚集索引中葉節點包含了完整的資料記錄
4. InnoDB表必須要有主鍵，並且推薦使用整型自增主鍵

正如我們上面介紹InnoDB儲存結構，索引與資料是共同儲存的，不管是主鍵索引還是輔助索引，在查詢時都是通過先查詢到索引節點才能拿到相對應的資料，如果我們在設計表結構時沒有顯式指定索引列的話，MySQL會從表中選擇資料不重複的列建立索引，如果沒有符合的列，則MySQL自動為InnoDB表生成一個隱含欄位作為主鍵，並且這個欄位長度為6個位元組，型別為整型。

那為什麼推薦使用整型自增主鍵而不是選擇UUID？

UUID是字串，比整型消耗更多的儲存空間；

在B+樹中進行查詢時需要跟經過的節點值比較大小，整型資料的比較運算比字串更快速；

自增的整型索引在磁碟中會連續儲存，在讀取一頁資料時也是連續；UUID是隨機產生的，讀取的上下兩行資料儲存是分散的，不適合執行where id > 5 && id < 20的條件查詢語句。

在插入或刪除資料時，整型自增主鍵會在葉子結點的末尾建立新的葉子節點，不會破壞左側子樹的結構；UUID主鍵很容易出現這樣的情況，B+樹為了維持自身的特性，有可能會進行結構的重構，消耗更多的時間。

為什麼非主鍵索引結構葉子節點儲存的是主鍵值？

保證資料一致性和節省儲存空間，可以這麼理解：商城系統訂單表會儲存一個使用者ID作為關聯外來鍵，而不推薦儲存完整的使用者資訊，因為當我們使用者表中的資訊（真實名稱、手機號、收貨地址···）修改後，不需要再次維護訂單表的使用者資料，同時也節省了儲存空間。