什么是存儲引擎？

關系數據庫表是用于存儲和組織信息的數據結構，可以將表理解為由行和列組成的表格，類似于Excel的電子表格的形式。有的表簡單，有的表復雜，有的表根本不用來存儲任何長期的數據，有的表讀取時非常快，但是插入數據時去很差；而我們在實際開發過程中，就可能需要各種各樣的表，不同的表，就意味著存儲不同類型的數據，數據的處理上也會存在著差異，那么。對于MySQL來說，它提供了很多種類型的存儲引擎，我們可以根據對數據處理的需求，選擇不同的存儲引擎，從而最大限度的利用MySQL強大的功能。這篇博文將總結和分析各個引擎的特點，以及適用場合，并不會糾結于更深層次的東西。我的學習方法是先學會用，懂得怎么用，再去知道到底是如何能用的。下面就對MySQL支持的存儲引擎進行簡單的介紹。

Mysql常用存儲引擎命令

查看當前數據庫到默認引擎

show variables like 'default_storage_engine';

執行結果：

查看數據庫所支持的數據庫引擎

show engines\G

執行結果：

show variables like 'have%';

執行結果：

創建表時設置存儲引擎：

CREATE TABLE dept( /*部門表*/deptno MEDIUMINT UNSIGNEDNOT NULL DEFAULT 0,

dnameVARCHAR(20) NOT NULL DEFAULT"",

locVARCHAR(13) NOT NULL DEFAULT""

) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

修改表的存儲引擎：

ALTER TABLE engineTest ENGINE = INNODB;

Linux修改默認存儲引擎

在mysql配置文件(linux下為/etc/my.cnf)，在mysqld后面增加default-storage-engine=INNODB即可。

Mysql幾個主要的存儲引擎的比較介紹

存儲引擎的選擇：

MyISAM存儲引擎: 如果表對事務要求不高，同時是以查詢和添加為主的，我們考慮使用MyISAM存儲引擎. ,比如 bbs 中的 發帖表，回復表.

INNODB 存儲: 對事務要求高，保存的數據都是重要數據，我們建議使用INNODB,比如訂單表，賬號表.

Memory 存儲，比如我們數據變化頻繁，不需要入庫，同時又頻繁的查詢和修改，我們考慮使用memory, 速度極快.

存儲引擎的比較：

(一)MyISAM它不支持事務，也不支持外鍵，尤其是訪問速度快，對事務完整性沒有要求或者以SELECT、INSERT為主的應用基本都可以使用這個引擎來創建表。

每個MyISAM在磁盤上存儲成3個文件，其中文件名和表名都相同，但是擴展名分別為：

.frm(存儲表定義)

MYD(MYData，存儲數據)

MYI(MYIndex，存儲索引)

數據文件和索引文件可以放置在不同的目錄，平均分配IO，獲取更快的速度。要指定數據文件和索引文件的路徑，需要在創建表的時候通過DATA DIRECTORY和INDEX DIRECTORY語句指定，文件路徑需要使用絕對路徑。

每個MyISAM表都有一個標志，服務器或myisamchk程序在檢查MyISAM數據表時會對這個標志進行設置。MyISAM表還有一個標志用來表明該數據表在上次使用后是不是被正常的關閉了。如果服務器以為當機或崩潰，這個標志可以用來判斷數據表是否需要檢查和修復。如果想讓這種檢查自動進行，可以在啟動服務器時使用--myisam-recover現象。這會讓服務器在每次打開一個MyISAM數據表是自動檢查數據表的標志并進行必要的修復處理。MyISAM類型的表可能會損壞，可以使用CHECK TABLE語句來檢查MyISAM表的健康，并用REPAIR TABLE語句修復一個損壞到MyISAM表。

MyISAM的表還支持3種不同的存儲格式：

靜態(固定長度)表

動態表

壓縮表

其中靜態表是默認的存儲格式。靜態表中的字段都是非變長字段，這樣每個記錄都是固定長度的，這種存儲方式的優點是存儲非常迅速，容易緩存，出現故障容易恢復；缺點是占用的空間通常比動態表多。靜態表在數據存儲時會根據列定義的寬度定義補足空格，但是在訪問的時候并不會得到這些空格，這些空格在返回給應用之前已經去掉。同時需要注意：在某些情況下可能需要返回字段后的空格，而使用這種格式時后面到空格會被自動處理掉。

動態表包含變長字段，記錄不是固定長度的，這樣存儲的優點是占用空間較少，但是頻繁到更新刪除記錄會產生碎片，需要定期執行OPTIMIZE TABLE語句或myisamchk -r命令來改善性能，并且出現故障的時候恢復相對比較困難。

壓縮表由myisamchk工具創建，占據非常小的空間，因為每條記錄都是被單獨壓縮的，所以只有非常小的訪問開支。

注：

在使用mysql的時候有時候，可能會發現盡管一張表刪除了許多數據，但是這張表表的數據文件和索引文件卻奇怪的沒有變小。這是因為mysql在刪除數據(特別是有Text和BLOB)的時候，會留下許多的數據空洞，這些空洞會占據原來數據的空間，所以文件的大小沒有改變。這些空洞在以后插入數據的時候可能會被再度利用起來，當然也有可能一直存在。這種空洞不僅額外增加了存儲代價，同時也因為數據碎片化降低了表的掃描效率。

mysql提供了解決這一個問題的方法：optimize table table_name 命令。該命令會對表進行碎片整理,去除空洞。查看前后效果可以使用show table status命令,例如show table status from [database] like '[table_name]';返回結果中的data_free即為空洞所占據的存儲空間。當執行完optimize table命令后，該字段的值將為0。

InnoDB類型的表是無法使用optimize table命令的。

(二)InnoDBInnoDB存儲引擎提供了具有提交、回滾和崩潰恢復能力的事務安全。但是對比MyISAM的存儲引擎，InnoDB寫的處理效率差一些并且會占用更多的磁盤空間以保留數據和索引。

1)自動增長列：InnoDB表的自動增長列可以手工插入，但是插入的如果是空或0，則實際插入到則是自動增長后到值。可以通過"ALTER TABLE...AUTO_INCREMENT=n;"語句強制設置自動增長值的起始值，默認為1，但是該強制到默認值是保存在內存中，數據庫重啟后該值將會丟失。可以使用LAST_INSERT_ID()查詢當前線程最后插入記錄使用的值。如果一次插入多條記錄，那么返回的是第一條記錄使用的自動增長值。

對于InnoDB表，自動增長列必須是索引。如果是組合索引，也必須是組合索引的第一列，但是對于MyISAM表，自動增長列可以是組合索引的其他列，這樣插入記錄后，自動增長列是按照組合索引到前面幾列排序后遞增的。

2)外鍵約束：MySQL支持外鍵的存儲引擎只有InnoDB，在創建外鍵的時候，父表必須有對應的索引，子表在創建外鍵的時候也會自動創建對應的索引。

在創建索引的時候，可以指定在刪除、更新父表時，對子表進行的相應操作，包括restrict、cascade、set null和no action。其中restrict和no action相同，是指限制在子表有關聯的情況下，父表不能更新；casecade表示父表在更新或刪除時，更新或者刪除子表對應的記錄；set null?則表示父表在更新或者刪除的時候，子表對應的字段被set null。

當某個表被其它表創建了外鍵參照，那么該表對應的索引或主鍵被禁止刪除。

可以使用set foreign_key_checks=0;臨時關閉外鍵約束，set foreign_key_checks=1;打開約束。

(三)MEMORYmemory使用存在內存中的內容來創建表。每個MEMORY表實際對應一個磁盤文件，格式是.frm。MEMORY類型的表訪問非常快，因為它到數據是放在內存中的，并且默認使用HASH索引，但是一旦服務器關閉，表中的數據就會丟失，但表還會繼續存在。

默認情況下，memory數據表使用散列索引，利用這種索引進行“相等比較”非常快，但是對“范圍比較”的速度就慢多了。因此，散列索引值適合使用在"="和"<=>"的操作符中，不適合使用在""操作符中，也同樣不適合用在order by字句里。如果確實要使用""或betwen操作符，可以使用btree索引來加快速度。

存儲在MEMORY數據表里的數據行使用的是長度不變的格式，因此加快處理速度，這意味著不能使用BLOB和TEXT這樣的長度可變的數據類型。VARCHAR是一種長度可變的類型，但因為它在MySQL內部當作長度固定不變的CHAR類型，所以可以使用。

使用USING HASH/BTREE來指定特定到索引。

在啟動MySQL服務的時候使用--init-file選項，把insert into...select或load data infile 這樣的語句放入到這個文件中，就可以在服務啟動時從持久穩固的數據源中裝載表。

服務器需要足夠的內存來維持所在的在同一時間使用的MEMORY表，當不再使用MEMORY表時，要釋放MEMORY表所占用的內存，應該執行DELETE FROM或truncate table或者刪除整個表。

每個MEMORY表中放置到數據量的大小，受到max_heap_table_size系統變量的約束，這個系統變量的初始值是16M，同時在創建MEMORY表時可以使用MAX_ROWS子句來指定表中的最大行數。

(四)MERGEmerge存儲引擎是一組MyISAM表的組合，這些MyISAM表結構必須完全相同，MERGE表中并沒有數據，對MERGE類型的表可以進行查詢、更新、刪除的操作，這些操作實際上是對內部的MyISAM表進行操作。對于對MERGE表進行的插入操作，是根據INSERT_METHOD子句定義的插入的表，可以有3個不同的值，first和last值使得插入操作被相應的作用在第一個或最后一個表上，不定義這個子句或者為NO，表示不能對這個MERGE表進行插入操作。可以對MERGE表進行drop操作，這個操作只是刪除MERGE表的定義，對內部的表沒有任何影響。MERGE在磁盤上保留2個以MERGE表名開頭文件：.frm文件存儲表的定義；.MRG文件包含組合表的信息，包括MERGE表由哪些表組成，插入數據時的依據。可以通過修改.MRG文件來修改MERGE表，但是修改后要通過flush table刷新。

create?table?man_all(idint,name?varchar(20))engine=mergeunion=(man1,man2) insert_methos=last;

reference：

http://blog.chinaunix.net/uid-21411227-id-1826943.html