MySQL儲存引擎:MyISAM和InnoDB的區別

大雜草發表於2020-12-09

MyISAM和InnoDB的區別

定義

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

MyISAM:在MySQL 5.1 及之前的版本,MyISAM是預設引擎。MyISAM提供的大量的特性,包括全文索引、壓縮、空間函式(GIS)等,但MyISAM並不支援事務以及行級鎖,而且一個毫無疑問的缺陷是崩潰後無法安全恢復。

事務

InnoDB:支援

MyISAM:不支援

InnoDB:支援行鎖、表鎖。行鎖是實現在索引上的,如果沒有索引,就沒法使用行鎖,將退化為表鎖。

MyISAM:支援表鎖。

主鍵

InnoDB:必須有,沒有指定會預設生成一個隱藏列作為主鍵

MyISAM:可以沒有

索引

InnoDB:聚集索引,使用 B+ 樹作為索引結構,資料檔案和索引綁在一起,必須要有主鍵。主鍵索引一次查詢;輔助索引兩次查詢,先查詢主鍵,再查詢資料;

MyISAM:非聚集索引,使用 B+ 樹作為索引結構,索引和資料檔案是分離的。主鍵索引和輔助索引是獨立的。

外來鍵

InnoDB:支援

MyISAM:不支援

AUTO_INCREMENT

InnoDB:必須包含只有該欄位的索引。引擎的自動增長列必須是索引,如果是組合索引也必須是組合索引的第一列。

MyISAM:可以和其他欄位一起建立聯合索引。引擎的自動增長列必須是索引,如果是組合索引,自動增長可以不是第一列,他可以根據前面幾列進行排序後遞增。

資料庫檔案

InnoDB:frm是表定義檔案,ibd是資料檔案。支援兩種儲存方式:

  • 共享表空間儲存:所有表的資料檔案和索引都儲存在一個表空間裡,一個表空間可以有多個檔案,通過 innodb_data_file_pathinnodb_data_home_dir 引數設定共享表空間的位置和名字,一般共享表空間的名字叫 ibdata1-n
  • 多表空間儲存:每個表都有一個表空間檔案用於儲存每個表的資料和索引,檔名以表名開關,以.ibd為副檔名

MyISAM:frm是表定義檔案,myd是資料檔案,myi是索引檔案。支援三種儲存格式:靜態表(預設,注意資料末尾不能有空格,會被去掉。)、動態表、壓縮表。

表的行數

InnoDB:沒有儲存。select count(*) from table;會掃描全表。

MyISAM:儲存。select count(*) from table;會直接取出該值。

注:但加了 where 條件後,兩者處理方式一樣,都是掃描全表。

全文索引

InnoDB:5.7及以後版本支援。

MyISAM:支援。

總結

InnoDB

  • 優點:支援事務,支援外來鍵,併發量較大,適合大量 update。
  • 缺點:查詢資料相對較快,不適合大量的 select。

MyISAM

  • 優點:查詢資料相對較快,適合大量的 select,可以全文索引。
  • 缺點:不支援事務,不支援外來鍵,併發量較小,不適合大量 update。

如何選擇?

  • 你的資料庫有外來鍵嗎?如有,選擇 InnoDB
  • 你需要事務支援嗎?如需要,選擇 InnoDB
  • 你需要全文索引嗎?在5.7及以後版本,都可選,優先考慮 InnoDB + Sphinx
  • 你經常使用什麼樣的查詢模式?如果表中絕大多數都只是讀查詢,可以考慮 MyISAM,如果既有讀寫也挺頻繁,請使用 InnoDB
  • 你的資料有多大?資料量大,選擇 InnoDB,因為其支援事務處理和故障恢復。MyISAM 可能需要幾小時或幾天來恢復,InnoDB 只需要幾分鐘。
  • 你需要線上熱備份嗎?如需要,選擇 InnoDB

參考資料

相關文章