簡介

Mysql創(chuàng)建、查詢、更新和刪除數(shù)據(jù)操作都依賴于存儲引擎，不同的存儲引擎提供不同的存儲機制、索引技巧、鎖定水平等功能，使用不同的存儲引擎獲得額外的速度或者功能。

主要存儲引擎

查看mysql（5.7）支持的存儲引擎

通過show engines;命令可以看到：

image

修改存儲引擎

• 全局修改配置文件my.cnf（永久）：default-storage-engine=CSV
• 全局命令修改（臨時，重啟后失效）：SET default_storage_engine=CSV;
• 修改某個表：ALTER TABLE 表名 ENGINE = CSV;

主要存儲引擎對比

MYISAM

MyISAM是MySQL5.5.8之前默認儲存引擎。

• 文件格式：
  • 表定義：*.frm；
  • 表數(shù)據(jù)文件：*.MYD；
  • 表索引文件：*.MYI；
• 優(yōu)點：
  • 只緩存索引，占用內存少；
  • 精確記錄表行數(shù)，count查詢速度極快；
  • 鎖級別為表鎖，表鎖優(yōu)點是開銷小，加鎖快；
  • 可被壓縮，存儲空間較小
• 缺點：
  • 不支持事務；
  • 不支持外鍵；
  • 表鎖粒度大，發(fā)生鎖沖動概率較高，并發(fā)能力低；
  • 宕機后，MyISAM表易損壞，災難恢復性差

適用場景：

• 做很多count的計算；
• 并發(fā)不高；
• 極少修改，例如存儲用戶登錄日志；
• 不需要事務的；
• 數(shù)據(jù)安全要求低。

INNODB（推薦）

INNODB是MySQL5.5.8版本開始就是默認儲存引擎，適用絕大多數(shù)場景。

• 文件格式：
  • 表定義：*.frm；
  • 表數(shù)據(jù)、索引文件：*.ibd；
• 優(yōu)點：
  • 支持事務；
  • 支持外鍵；
  • 可靠性高，災難恢復性好；
  • 使用行級鎖，支持mvcc,并發(fā)能力強；
  • 默認RR隔離級別，提供next-key loking避免幻讀；
  • 緩存數(shù)據(jù)和索引，還提供變更緩沖，二次寫，自適應哈希索引等高效的緩存特性；
• 缺點：
  • 服務器資源開銷大；
  • 不記錄表行數(shù)，count查詢會做全表掃描；

適用場景：

• 頻繁修改，并發(fā)要求高；
• 支持事務，外鍵；
• 數(shù)據(jù)安全要求高。

MEMORY

也叫HEAP存儲引擎，數(shù)據(jù)存儲在內存，如果MySQL服務重啟數(shù)據(jù)會丟失，但是表結構會保存下來。

• 優(yōu)點：
  • 由于數(shù)據(jù)存儲在內存，顯著提高訪問性能；
• 缺點：
  • 不支持事務；
  • 不支持外鍵；
  • 表鎖粒度大，發(fā)生鎖沖動概率較高，并發(fā)能力低；
  • 所有字段都為固定長度，不支持 BLOB 和 TEXT 等大字段
  • 服務重啟數(shù)據(jù)會丟失，不支持災難恢復

適用場景：

• 小表，查詢性能要求高；
• 臨時表；

ARCHIVE

Archiv存儲引擎，只支持insert和select操作，不支持索引，主要用于數(shù)據(jù)歸檔。

• 文件格式：
  • 表定義：*.frm；
  • 表數(shù)據(jù)文件：*.arz；
• 優(yōu)點：
  • 只允許插入和查詢，不允許修改和刪除，可以實現(xiàn)高并發(fā)的插入；
  • 用zlib對表的數(shù)據(jù)進行壓縮，相比MyISAM會更加節(jié)約磁盤IO，數(shù)據(jù)量大時，依然較好的插入性能。
• 缺點：
  • 功能單一，只是提供高速的插入和壓縮功能；

適用場景：日志和數(shù)據(jù)采集類應用

詳細的存儲引擎對比表

總結

絕大多數(shù)場景下，使用默認INNODB都是正確的選擇。除非需要用到某些INNODB不具備的特性，并且沒有其他辦法可以代替，那可以考慮使用其他存儲引擎。

除非萬不得已，否則不建議混合使用多種存儲引擎，不然可能會帶來一系列復雜的問題，以及一些潛在的bug和邊界問題，例如在一個事務里面同時，插入兩張表A(MYISAM)、B(INNODB)數(shù)據(jù)，表A數(shù)據(jù)先插入成功，接著表B插入失敗回滾，就無法保證數(shù)據(jù)一致性了。混合存儲對一致性備份和服務器參數(shù)配置也帶來了一些麻煩。