mysql學習–MySQL存儲引擎對比總結
一、存儲引擎是什麼
存儲引擎是數據庫的核心，對於mysql來說，存儲引擎是以插件的形式運行的。MySQL默認配置了許多不同的存儲引擎，可以預先設置或者在MySQL服務器中啟用。你可以選擇適用於服務器、數據庫和表格的存儲引擎，以便在選擇如何存儲你的信息、如何檢索這些信息以及你需要你的數據結合什麼性能和功能的時候為你提供最大的靈活性。
二、存儲引擎有哪些
MySQL版本為5以後的（博客測試版本是MySQL5.5.15）
1、查看MySQL默認支持的存儲引擎
show engines \g;
可以看到，這裡一共有九種默認的存儲引擎，但能夠經常使用到的其實只有那麼幾種，
2、各主要存儲引擎對比
三、常用存儲引擎介紹
1、InnoDB
MySQL5.5後的默認數據庫存儲引擎。
這個可能是平常用的最多的一個數據庫引擎了，因為他支持事務，MySQL中支持事務的存儲引擎並不多，除了InnoDB，還有NDB（而且NDB只支持在NDB集群中使用），所以一般需要用到事務的場景，我們一般選用的就是InnoDB存儲引擎。
（1）優缺點
優點：支持ACID事務；聚集索引；支持外鍵；支持行鎖；存在着緩衝管理，通過緩衝池，將索引和數據全部緩存起來，加快查詢的速度；
缺點：不支持全文索引，不保存錶行數，相比MyISAM，InnoDB寫的處理效率要差一些，並且會佔用更多的磁盤空間以保留數據和索引。
（2）存儲方式
① 使用共享表空間存儲：
這種方式創建的表結構保存在.frm文件中，數據和索引保存在innodb_data_home_dir和innodb_data_file_path定義的表空間中，可以是多個文件。
② 使用多表空間存儲：
這種方式創建的表結構仍然保存在.frm文件中，但是每個表的數據和索引單獨保存在.idb文件中。如果是個分區表，則每個分區對應單獨的.idb文件，文件名是「表名+分區名」，可以在創建分區的時候指定每個分區的數據文件的位置，以此來將表的IO均勻分佈在多個磁盤上。
要使用多表空間的存儲方式，需要設置參數innodb_file_per_table並重啟服務器後才可以生效，而且只對新建的表生效。多表空間的數據文件沒有大小限制，不需要設置初始大小，也不需要設置文件的最大限制、擴展大小等參數。即使在多表空間的存儲方式下，共享表空間仍然是必須的，InnoDB把內部數據詞典和工作日誌放在這個文件中，所以備份使用多表空間特性的表時直接複製.idb文件是不行的，可以通過命令將數據備份恢復到數據庫中：
ALTER TABLE tbl_name DISCARD TABLESPACE; ALTER TABLE tbl_name IMPORT TABLESPACE;
但是這樣只能恢復到表原來所在數據庫中，如果需要恢復到其他數據庫則需要通過mysqldump和mysqlimport來實現。
（3）數據文件
InnoDB的數據文件由表的存儲方式決定。
① 共享表空間文件：由參數innodb_data_home_dir和innodb_data_file_path定義，用於存放數據詞典和日誌等。
② .frm：存放表結構定義，共享表空間存儲與多表空間存儲均有。
③ .idb：使用多表空間存儲方式時，用於存放表數據和索引，若使用共享表空間存儲則無此文件。
（4）外鍵約束
InnoDB是MySQL唯一支持外鍵約束的引擎。外鍵約束可以讓數據庫自己通過外鍵保證數據的完整性和一致性，但是引入外鍵會使速度和性能下降。在創建外鍵的時候，要求父表必須有對應的索引，子表在創建外鍵的時候也會自動創建對應的索引。
（5）適用情況
如果應用對事務的完整性有比較高的要求，在並發條件下要求數據的一致性，數據操作除了插入和查詢以外，還包括很多的更新、刪除操作，那麼InnoDB 存儲引擎應該是比較合適的選擇。InnoDB 存儲引擎除了有效地降低由於刪除和更新導致的鎖定，還可以確保事務的完整提交和回滾，對於類似計費系統或者財務系統等對數據準確性要求比較高的系統，InnoDB 都是合適的選擇。
2、MyISAM
（1）優缺點
優點：全文索引，非聚集索引、查詢速度快。
缺點：不支持事務、不支持外鍵、僅僅支持表鎖；數據庫所在主機如果宕機，MyISAM的數據文件容易損壞，而且難恢復；。
（2）存儲方式
① 靜態表（默認）：字段都是非變長的（每個記錄都是固定長度的）。存儲非常迅速、容易緩存，出現故障容易恢復；佔用空間通常比動態表多。
② 動態表：佔用的空間相對較少，但是頻繁的更新刪除記錄會產生碎片，需要定期執行optimize table或myisamchk -r命令來改善性能，而且出現故障的時候恢複比較困難。
靜態表的數據在存儲的時候會按照列的寬度定義補足空格，在返回數據給應用之前去掉這些空格。如果需要保存的內容後面本來就有空格，在返回結果的時候也會被去掉。（其實是數據類型char的行為，動態表中若有這個數據類型也同樣會有這個問題）
（靜態表和動態表是根據正使用的列的類型自動選擇的。）
（3）數據文件
MyISAM數據表在磁盤存儲成3個文件，其文件名都和表名相同，擴展名分別是：
① .frm：存儲數據表結構定義。
② .MYD：存儲表數據。
③ .MYI：存儲表索引。
其中，數據文件和索引文件可以放置在不同的目錄，平均分佈IO，獲得更快的速度。指定索引文件和數據文件的路徑，需要在創建表的時候通過data directory和index directory語句指定。（文件路徑需要是絕對路徑並且具有訪問的權限）
MyISAM類型的表可能會損壞，原因可能是多種多樣的，損壞後的表可能不能訪問，會提示需要修復或者訪問後返回錯誤的結果。可以使用check table語句來檢查MyISAM表的健康，並用repair table語句修復已經損壞的MyISAM表。
frm和MYI可以存放在不同的目錄下。MYI文件用來存儲索引，但僅保存記錄所在頁的指針，索引的結構是B+樹結構。下面這張圖就是MYI文件保存的機制：
從這張圖可以發現，這個存儲引擎通過MYI的B+樹結構來查找記錄頁，再根據記錄頁查找記錄。並且支持全文索引、B樹索引和數據壓縮。
（4）適用情況
如果應用是以讀操作和插入操作為主，只有很少的更新和刪除操作，並且對事務的完整性、並發性要求不是很高，那麼選擇這個存儲引擎是非常適合的。MyISAM 是在Web、數據倉庫和其他應用環境下最常使用的存儲引擎之一。
3、MEMORY
（1）優缺點
優點：①分離靜態的和動態的數據，②利用結構接近的數據來優化查詢，③查詢時可以訪問更少的數據，④更容易維護大數據集，⑤可以通過修改.mrg文件來修改merge表，當然也可以用alter進行修改，修改後要通過flush tables刷新表緩存，此法可以動態增加減少子表，⑥Memory存儲引擎將表的數據存放在內存中，能像會話或緩存一樣方便操作和管理，充分發揮內存引擎的特點——速度快，延遲低；僅僅讀或寫為主的訪問模式。
缺點：一旦服務關閉，表中的數據就會丟失；對錶的大小有限制。
（2）存儲方式
數據存放在內存中。
（3）數據文件
每個MEMORY表只對應一個.frm磁盤文件，用於存儲表的結構定義，表數據存放在內存中。默認使用HASH索引，而不是BTREE索引。
（4）適用情況
Memory存儲引擎主要用在那些內容變化不頻繁的代碼表，或者作為統計操作的中間結果表，便於高效地對中間結果進行分析並得到最終的統計結果。
4、MRG_MYISAM （MERGE）
（1）引擎原理
Merge存儲引擎是一組MyISAM表的組合，這些MyISAM表必須結構完全相同，merge表本身並沒有數據，對merge類型的表可以進行查詢、更新、刪除的操作，這些操作實際上是對內部的實際的MyISAM表進行的。
通過insert_method子句定義merge表的插入操作：使用first或last可以使插入操作被相應地作用在第一或最後一個表上，不定義或定義為No表示不能對這個merge表進行插入操作。對merge表進行drop操作只是刪除了merge的定義，對內部的表沒有任何影響。
（2）存儲方式
（3）數據文件
① .frm：存儲表定義。
② .MRG：存儲組合表的信息，包括merge表由哪些表組成、插入新數據時的依據。可以通過修改.mrg文件來修改merge表，但是修改後要通過flush tables刷新。
（4）適用情況
用於將一系列等同的MyISAM 表以邏輯方式組合在一起，並作為一個對象引用它們。MERGE 表的優點在於可以突破對單個MyISAM 表大小的限制，並且通過將不同的表分佈在多個磁盤上，可以有效地改善MERGE 表的訪問效率。這對於諸如數據倉儲等VLDB環境十分適合。
5、ARCHIVE
Archive存儲引擎基本上用於數據歸檔；它的壓縮比非常的高，存儲空間大概是InnoDB的10-15分之一所以它用來存儲歷史數據非常的適合，由於它不支持索引同時也不能緩存索引和數據，所以它不適合作為並發訪問表的存儲引擎。archive存儲引擎使用行鎖來實現高並發插入操作，但是它不支持事務，其設計目標只是提供高速的插入和壓縮功能。
6、BLACKHOLE
黑洞引擎，學習過網絡的同學大概聽過黑洞路由器，在Linux系統中接觸過/dev/null，這個引擎和這一樣，表不保存數據庫，主要作用是如果主被模式slave節點過多，通過blachole減輕master的壓力。
7、FEDERATED
主要是用於和遠程數據庫鏈接的引擎
8、CSV
CSV存儲引擎可以將CSV文件作為MySQL的表進行處理。存儲格式就是普通的CSV文件
特點：①以CSV格式進行數據存儲、②所有的列必須都是不能為null的、③不支持索引，不適合大表，不適合在線處理、④可以對數據文件直接編輯
9、PERFORMANCE_SCHEMA
10、NDB
（也稱為NDBCLUSTER）-此集群數據庫引擎特別適合於需要最高正常運行時間和可用性的應用程序。
參考鏈接：
//zhidao.baidu.com/question/942624256925786012.html
//blog.csdn.net/qq_42534026/article/details/106197607
//www.jb51.net/article/139110.htm
//baijiahao.baidu.com/s?id=1655327558614401593&wfr=spider&for=pc
