MySQL的存儲引擎整理，MySQL存儲引擎整理
01.MyISAM
MySQL 5.0 以前的默認存儲引擎。MyISAM 不支持事務、也不支持外鍵，其優勢是訪問的速度快，對事務完整性沒有要求或者以SELECT、INSERT 為主的應用基本上都可以使用這個引擎來創建表。
每個MyISAM 在磁盤上存儲成3 個文件，其文件名都和表名相同，但擴展名分別是：
.frm（存儲表定義）；
.MYD（MYData，存儲數據）；
.MYI （MYIndex，存儲索引）。
數據文件和索引文件可以放置在不同的目錄，平均分布IO，獲得更快的速度。 要指定索引文件和數據文件的路徑，需要在創建表的時候通過DATA DIRECTORY 和INDEX DIRECTORY 語句指定，也就是說不同MyISAM 表的索引文件和數據文件可以放置到不同的路徑下。文件路徑需要是絕對路徑，並且具有訪問權限。
MyISAM 類型的表可能會損壞，原因可能是多種多樣的，損壞後的表可能不能訪問，會提示需要修復或者訪問後返回錯誤的結果。MyISAM 類型的表提供修復的工具，可以用CHECKTABLE 語句來檢查MyISAM 表的健康，並用REPAIR TABLE 語句修復一個損壞的MyISAM 表。表損壞可能導致數據庫異常重新啟動，需要盡快修復並盡可能地確認損壞的原因。
MyISAM 的表又支持3 種不同的存儲格式，分別是：
靜態（固定長度）表；
動態表；
壓縮表。
其中，靜態表是默認的存儲格式。靜態表中的字段都是非變長字段，這樣每個記錄都是固定長度的，這種存儲方式的優點是存儲非常迅速，容易緩存，出現故障容易恢復；缺點是占用的空間通常比動態表多。靜態表的數據在存儲的時候會按照列的寬度定義補足空格，但是在應用訪問的時候並不會得到這些空格，這些空格在返回給應用之前已經去掉。
動態表中包含變長字段，記錄不是固定長度的，這樣存儲的優點是占用的空間相對較少，但是頻繁地更新刪除記錄會產生碎片，需要定期執行OPTIMIZE TABLE 語句或myisamchk -r 命令來改善性能，並且出現故障的時候恢復相對比較困難。
壓縮表由myisampack 工具創建，占據非常小的磁盤空間。因為每個記錄是被單獨壓縮的，所以只有非常小的訪問開支。
02.InnoDB
MySQL 5.0開始的默認引擎，InnoDB 存儲引擎提供了具有提交、回滾和崩潰恢復能力的事務安全。但是對比MyISAM的存儲引擎，InnoDB 寫的處理效率差一些並且會占用更多的磁盤空間以保留數據和索引。
AUTO_INCREMENT
InnoDB 表的自動增長列可以手工插入，但是插入的值如果是空或者0，則實際插入的將是自動增長後的值。
可以通過“ALTER TABLE *** AUTO_INCREMENT = n;”語句強制設置自動增長列的初識值，默認從1 開始，但是該強制的默認值是保留在內存中的，如果該值在使用之前數據庫重新啟動，那麼這個強制的默認值就會丟失，就需要在數據庫啟動以後重新設置。
可以使用LAST_INSERT_ID()查詢當前線程最後插入記錄使用的值。如果一次插入了多條記錄，那麼返回的是第一條記錄使用的自動增長值。
對於InnoDB 表，自動增長列必須是索引。如果是組合索引，也必須是組合索引的第一列，但是對於MyISAM 表，自動增長列可以是組合索引的其他列，這樣插入記錄後，自動增長列是按照組合索引的前面幾列進行排序後遞增的。
例如，創建一個新的MyISAM 類型的表autoincre_demo，自動增長列d1 作為組合索引的第二列，對該表插入一些記錄後，可以發現自動增長列是按照組合索引的第一列d2 進行排序後遞增的：
外鍵約束
MySQL 支持外鍵的存儲引擎只有InnoDB，在創建外鍵的時候，要求父表必須有對應的索引，子表在創建外鍵的時候也會自動創建對應的索引。
創建索引的時候，可以指定在刪除、更新父表時，對子表進行的相應操作，包括RESTRICT、CASCADE、SET NULL 和NO ACTION。其中RESTRICT 和NO ACTION 相同，是指限制在子表有關聯記錄的情況下父表不能更新；CASCADE 表示父表在更新或者刪除時，更新或者刪除子表對應記錄；SET NULL 則表示父表在更新或者刪除的時候，子表的對應字段被SET NULL。選擇後兩種方式的時候要謹慎，可能會因為錯誤的操作導致數據的丟失。子表的外鍵指定是ON DELETE RESTRICT ON UPDATE CASCADE方式的，那麼在主表刪除記錄的時候，如果子表有對應記錄，則不允許刪除，主表在更新記錄的時候，如果子表有對應記錄，則子表對應更新。
當某個表被其他表創建了外鍵參照，那麼該表的對應索引或者主鍵禁止被刪除。
在導入多個表的數據時，如果需要忽略表之前的導入順序，可以暫時關閉外鍵的檢查；同樣，在執行LOAD DATA 和ALTER TABLE 操作的時候，可以通過暫時關閉外鍵約束來加快處理的速度，關閉的命令是“SET FOREIGN_KEY_CHECKS = 0;”，執行完成之後，通過執行“SET FOREIGN_KEY_CHECKS = 1;”語句改回原狀態。
存儲方式
InnoDB 存儲表和索引有以下兩種方式。
使用共享表空間存儲，這種方式創建的表的表結構保存在.frm 文件中，數據和索引保存在innodb_data_home_dir 和innodb_data_file_path 定義的表空間中，可以是多個文件。
使用多表空間存儲，這種方式創建的表的表結構仍然保存在.frm 文件中，但是每個表的數據和索引單獨保存在.ibd 中。如果是個分區表，則每個分區對應單獨的.ibd文件，文件名是“表名+分區名”，可以在創建分區的時候指定每個分區的數據文件的位置，以此來將表的IO 均勻分布在多個磁盤上。
要使用多表空間的存儲方式，需要設置參數innodb_file_per_table，並重新啟動服務後才可以生效，對於新建的表按照多表空間的方式創建，已有的表仍然使用共享表空間存儲。如果將已有的多表空間方式修改回共享表空間的方式，則新建表會在共享表空間中創建，但已有的多表空間的表仍然保存原來的訪問方式。所以多表空間的參數生效後，只對新建的表生效。
多表空間的數據文件沒有大小限制，不需要設置初始大小，也不需要設置文件的最大限制、擴展大小等參數。對於使用多表空間特性的表，可以比較方便地進行單表備份和恢復操作，但是直接復制.ibd 文件是不行的，因為沒有共享表空間的數據字典信息，直接復制的.ibd 文件和.frm 文件恢復時是不能被正確識別的，但可以通過以下命令：
-
1 ALTER TABLE tbl_name DISCARD TABLESPACE;
2 ALTER TABLE tbl_name IMPORT TABLESPACE;
將備份恢復到數據庫中，但是這樣的單表備份，只能恢復到表原來在的數據庫中，而不能恢復到其他的數據庫中。如果要將單表恢復到目標數據庫，則需要通過mysqldump 和mysqlimport 來實現。
注意：即便在多表空間的存儲方式下，共享表空間仍然是必須的，InnoDB 把內部數據詞典和未作日志放在這個文件中。
03.MEMORY
MEMORY 存儲引擎使用存在內存中的內容來創建表。每個MEMORY 表只實際對應一個磁盤文件，格式是.frm。MEMORY 類型的表訪問非常得快，因為它的數據是放在內存中的，並且默認使用HASH 索引，但是一旦服務關閉，表中的數據就會丟失掉。
給MEMORY 表創建索引的時候，可以指定使用HASH 索引還是BTREE 索引：
![]()
04.MERGE
MERGE 存儲引擎是一組MyISAM 表的組合，這些MyISAM 表必須結構完全相同，MERGE表本身並沒有數據，對MERGE 類型的表可以進行查詢、更新、刪除的操作，這些操作實際上是對內部的實際的MyISAM 表進行的。對於MERGE 類型表的插入操作，是通過INSERT_METHOD 子句定義插入的表，可以有3 個不同的值，使用FIRST 或LAST 值使得插入操作被相應地作用在第一或最後一個表上，不定義這個子句或者定義為NO，表示不能對這
個MERGE 表執行插入操作。
可以對MERGE 表進行DROP 操作，這個操作只是刪除MERGE 的定義，對內部的表沒有任何的影響。
MERGE 表在磁盤上保留兩個文件，文件名以表的名字開始，一個.frm 文件存儲表定義，另一個.MRG 文件包含組合表的信息，包括MERGE 表由哪些表組成、插入新的數據時的依據。可以通過修改.MRG 文件來修改MERGE 表，但是修改後要通過FLUSH TABLES 刷新。
可以發現，payment_all 表中的數據是payment_2006 和payment_2007 表的記錄合並後的結果集。
下面向MERGE 表插入一條記錄，由於MERGE 表的定義是INSERT_METHOD=LAST，就會向最後一個表中插入記錄，所以雖然這裡插入的記錄是2006 年的，但仍然會寫到payment_2007表中。
這也是MERGE 表和分區表的區別，MERGE 表並不能智能地將記錄寫到對應的表中，而分區表是可以的。通常我們使用MERGE 表來透明地對多個表進行查詢和更新操作，而對這種按照時間記錄的操作日志表則可以透明地進行插入操作。
05.如何選擇存儲引擎
MyISAM：默認的MySQL 插件式存儲引擎。如果應用是以讀操作和插入操作為主，只有很少的更新和刪除操作，並且對事務的完整性、並發性要求不是很高，那麼選擇這個存儲引擎是非常適合的。MyISAM 是在Web、數據倉儲和其他應用環境下最常使用的存儲引擎之一。
InnoDB：用於事務處理應用程序，支持外鍵。如果應用對事務的完整性有比較高的要求，在並發條件下要求數據的一致性，數據操作除了插入和查詢以外，還包括很多的更新、刪除操作，那麼InnoDB 存儲引擎應該是比較合適的選擇。InnoDB 存儲引擎除了有效地降低由於刪除和更新導致的鎖定，還可以確保事務的完整提交（Commit）和回滾（Rollback），對於類似計費系統或者財務系統等對數據准確性要求比較高的系統，InnoDB 都是合適的選擇。
MEMORY：將所有數據保存在RAM 中，在需要快速定位記錄和其他類似數據的環境下，可提供極快的訪問。MEMORY 的缺陷是對表的大小有限制，太大的表無法CACHE 在內存中，其次是要確保表的數據可以恢復，數據庫異常終止後表中的數據是可以恢復的。MEMORY 表通常用於更新不太頻繁的小表，用以快速得到訪問結果。
MERGE：用於將一系列等同的MyISAM 表以邏輯方式組合在一起，並作為一個對象引用它們。MERGE 表的優點在於可以突破對單個MyISAM 表大小的限制，並且通過將不同的表分布在多個磁盤上，可以有效地改善MERGE表的訪問效率。這對於諸如數據倉儲等VLDB環境十分適合。
注意：以上只是我們按照實施經驗提出的關於存儲引擎選擇的一些建議，但是不同應用的特點是千差萬別的，選擇使用哪種存儲引擎才是最佳方案也不是絕對的，這需要根據用戶各自的應用進行測試，從而得到最適合自己的結果。
