MySQL儲存引擎入門介紹

誰主沉浮oo7發表於2020-09-28

什麼是MySQL?

MySQL 是一種關係型資料庫，在Java企業級開發中非常常用，因為 MySQL 是開源免費的，並且方便擴充套件。阿里巴巴資料庫系統也大量用到了 MySQL，因此它的穩定性是有保障的。MySQL是開放原始碼的，因此任何人都可以在 GPL(General Public License) 的許可下下載並根據個性化的需要對其進行修改。MySQL的預設埠號是3306。

儲存引擎

常用命令

檢視MySQL提供的所有儲存引擎

mysql> show engines;

從上圖我們可以檢視出 MySQL 當前預設的儲存引擎是InnoDB,並且在5.7版本所有的儲存引擎中只有 InnoDB 是事務性儲存引擎，也就是說只有 InnoDB 支援事務。

檢視MySQL當前預設的儲存引擎

我們也可以通過下面的命令檢視預設的儲存引擎。

mysql> show variables like '%storage_engine%';

檢視錶的儲存引擎

show table status like "table_name" ;

MyISAM和InnoDB區別

MyISAM是MySQL的預設資料庫引擎（5.5版之前）。雖然效能極佳，而且提供了大量的特性，包括全文索引、壓縮、空間函式等，但MyISAM不支援事務和行級鎖，而且最大的缺陷就是崩潰後無法安全恢復。不過，5.5版本之後，MySQL引入了InnoDB（事務性資料庫引擎），MySQL 5.5版本後預設的儲存引擎為InnoDB。

兩者的對比：

是否支援行級鎖 : MyISAM 只有表級鎖(table-level locking)，而InnoDB 支援行級鎖(row-level locking)和表級鎖,預設為行級鎖。
是否支援事務和崩潰後的安全恢復： MyISAM 強調的是效能，每次查詢具有原子性,其執行速度比InnoDB型別更快，但是不提供事務支援。但是InnoDB 提供事務支援，外部鍵等高階資料庫功能。具有事務(commit)、回滾(rollback)和崩潰修復能力(crash recovery capabilities)的事務安全(transaction-safe (ACID compliant))型表。
是否支援外來鍵： MyISAM不支援，而InnoDB支援。
是否支援MVCC ：僅 InnoDB 支援。應對高併發事務, MVCC比單純的加鎖更高效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 兩個隔離級別下工作;MVCC可以使用樂觀(optimistic)鎖和悲觀(pessimistic)鎖來實現;各資料庫中MVCC實現並不統一。推薦閱讀：MySQL-InnoDB-MVCC多版本併發控制
......

《MySQL高效能》上面有一句話這樣寫到:

不要輕易相信“MyISAM比InnoDB快”之類的經驗之談，這個結論往往不是絕對的。在很多我們已知場景中，InnoDB的速度都可以讓MyISAM望塵莫及，尤其是用到了聚簇索引，或者需要訪問的資料都可以放入記憶體的應用。

一般情況下我們選擇 InnoDB 都是沒有問題的，但是某些情況下你並不在乎可擴充套件能力和併發能力，也不需要事務支援，也不在乎崩潰後的安全恢復問題的話，選擇MyISAM也是一個不錯的選擇。但是一般情況下，我們都是需要考慮到這些問題的。
大多數時候我們使用的都是 InnoDB 儲存引擎，但是在某些情況下使用 MyISAM 也是合適的比如讀密集的情況下。（如果你不介意 MyISAM 崩潰恢復問題的話）。

MyISAM和InnoDB索引實現對比

MyISAM索引實現

MyISAM引擎使用B+Tree作為索引結構，葉節點的data域存放的是資料記錄的地址。如圖：

這裡設表一共有三列，假設我們以Col1為主鍵，則上圖是一個MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引檔案僅僅儲存資料記錄的地址。在MyISAM中，主索引和輔助索引（Secondary key）在結構上沒有任何區別，只是主索引要求key是唯一的，而輔助索引的key可以重複。如果我們在Col2上建立一個輔助索引，則此索引的結構如下圖所示：

同樣也是一顆B+Tree，data域儲存資料記錄的地址。因此，MyISAM中索引檢索的演算法為首先按照B+Tree搜尋演算法搜尋索引，如果指定的Key存在，則取出其data域的值，然後以data域的值為地址，讀取相應資料記錄。
MyISAM的索引方式也叫做“非聚集”的，之所以這麼稱呼是為了與InnoDB的聚集索引區分。

InnoDB索引實現

雖然InnoDB也使用B+Tree作為索引結構，但具體實現方式卻與MyISAM截然不同。

第一個重大區別是InnoDB的資料檔案本身就是索引檔案。從上文知道，MyISAM索引檔案和資料檔案是分離的，索引檔案僅儲存資料記錄的地址。而在InnoDB中，表資料檔案本身就是按B+Tree組織的一個索引結構，這棵樹的葉節點data域儲存了完整的資料記錄。這個索引的key是資料表的主鍵，因此InnoDB表資料檔案本身就是主索引。

上圖是InnoDB主索引（同時也是資料檔案）的示意圖，可以看到葉節點包含了完整的資料記錄。這種索引叫做聚集索引。因為InnoDB的資料檔案本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則MySQL系統會自動選擇一個可以唯一標識資料記錄的列作為主鍵，如果不存在這種列，則MySQL自動為InnoDB表生成一個隱含欄位作為主鍵，這個欄位長度為6個位元組，型別為長整形。

第二個與MyISAM索引的不同是InnoDB的輔助索引data域儲存相應記錄主鍵的值而不是地址。換句話說，InnoDB的所有輔助索引都引用主鍵作為data域。例如，下圖為定義在Col3上的一個輔助索引：

這裡以英文字元的ASCII碼作為比較準則。聚集索引這種實現方式使得按主鍵的搜尋十分高效，但是輔助索引搜尋需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然後用主鍵到主索引中檢索獲得記錄。

總結

在資料庫開發中，瞭解不同儲存引擎的索引實現方式對於正確使用和優化索引都非常有幫助。例如，知道了InnoDB的索引實現後，就很容易明白為什麼不建議使用過長的欄位作為主鍵，因為所有輔助索引都引用主索引，過長的主索引會令輔助索引變得過大。再例如，用非單調的欄位作為主鍵在InnoDB中不是個好做法，因為InnoDB資料檔案本身是一顆B+Tree，非單調的主鍵會造成在插入新記錄時資料檔案為了維持B+Tree的特性而頻繁的分裂調整，十分低效，而使用自增欄位作為主鍵則是一個很好的選擇。