MySQL存儲(chǔ)引擎通常有哪3種？各自分別有什么特點(diǎn)？應(yīng)用場(chǎng)景是哪些？

MySQL存儲(chǔ)引擎通常有哪3種？各自分別有什么特點(diǎn)？應(yīng)用場(chǎng)景是哪些？

MySQL5.5以后默認(rèn)使用InnoDB存儲(chǔ)引擎，其中InnoDB和BDB提供事務(wù)安全表，其它存儲(chǔ)引擎都是非事務(wù)安全表。
若要修改默認(rèn)引擎，可以修改配置文件中的default-storage-engine。可以通過(guò)：show variables like 'default_storage_engine';查看當(dāng)前數(shù)據(jù)庫(kù)到默認(rèn)引擎。命令：show engines和show variables like 'have%'可以列出當(dāng)前數(shù)據(jù)庫(kù)所支持到引擎。其中Value顯示為disabled的記錄表示數(shù)據(jù)庫(kù)支持此引擎，而在數(shù)據(jù)庫(kù)啟動(dòng)時(shí)被禁用。在MySQL5.1以后，INFORMATION_SCHEMA數(shù)據(jù)庫(kù)中存在一個(gè)ENGINES的表，它提供的信息與show engines;語(yǔ)句完全一樣，可以使用下面語(yǔ)句來(lái)查詢哪些存儲(chǔ)引擎支持事物處理：select engine from information_chema.engines where transactions = 'yes';
可以通過(guò)engine關(guān)鍵字在創(chuàng)建或修改數(shù)據(jù)庫(kù)時(shí)指定所使用到引擎。
主要存儲(chǔ)引擎：MyISAM、InnoDB、MEMORY和MERGE介紹：
在創(chuàng)建表到時(shí)候通過(guò)engine=...或type=...來(lái)指定所要使用到引擎。show table status from DBname來(lái)查看指定表到引擎。

(一)MyISAM
　　它不支持事務(wù)，也不支持外鍵，尤其是訪問(wèn)速度快，對(duì)事務(wù)完整性沒(méi)有要求或者以SELECT、INSERT為主的應(yīng)用基本都可以使用這個(gè)引擎來(lái)創(chuàng)建表。
每個(gè)MyISAM在磁盤上存儲(chǔ)成3個(gè)文件，其中文件名和表名都相同，但是擴(kuò)展名分別為：

• .frm(存儲(chǔ)表定義)

• MYD(MYData，存儲(chǔ)數(shù)據(jù))

• MYI(MYIndex，存儲(chǔ)索引)

　　數(shù)據(jù)文件和索引文件可以放置在不同的目錄，平均分配IO，獲取更快的速度。要指定數(shù)據(jù)文件和索引文件的路徑，需要在創(chuàng)建表的時(shí)候通過(guò)DATA DIRECTORY和INDEX DIRECTORY語(yǔ)句指定，文件路徑需要使用絕對(duì)路徑。
　　每個(gè)MyISAM表都有一個(gè)標(biāo)志，服務(wù)器或myisamchk程序在檢查MyISAM數(shù)據(jù)表時(shí)會(huì)對(duì)這個(gè)標(biāo)志進(jìn)行設(shè)置。MyISAM表還有一個(gè)標(biāo)志用來(lái)表明該數(shù)據(jù)表在上次使用后是不是被正常的關(guān)閉了。如果服務(wù)器以為當(dāng)機(jī)或崩潰，這個(gè)標(biāo)志可以用來(lái)判斷數(shù)據(jù)表是否需要檢查和修復(fù)。如果想讓這種檢查自動(dòng)進(jìn)行，可以在啟動(dòng)服務(wù)器時(shí)使用--myisam-recover現(xiàn)象。這會(huì)讓服務(wù)器在每次打開(kāi)一個(gè)MyISAM數(shù)據(jù)表是自動(dòng)檢查數(shù)據(jù)表的標(biāo)志并進(jìn)行必要的修復(fù)處理。MyISAM類型的表可能會(huì)損壞，可以使用CHECK TABLE語(yǔ)句來(lái)檢查MyISAM表的健康，并用REPAIR TABLE語(yǔ)句修復(fù)一個(gè)損壞到MyISAM表。
　　MyISAM的表還支持3種不同的存儲(chǔ)格式：

• 靜態(tài)(固定長(zhǎng)度)表

• 動(dòng)態(tài)表

• 壓縮表

　　其中靜態(tài)表是默認(rèn)的存儲(chǔ)格式。靜態(tài)表中的字段都是非變長(zhǎng)字段，這樣每個(gè)記錄都是固定長(zhǎng)度的，這種存儲(chǔ)方式的優(yōu)點(diǎn)是存儲(chǔ)非常迅速，容易緩存，出現(xiàn)故障容易恢復(fù)；缺點(diǎn)是占用的空間通常比動(dòng)態(tài)表多。靜態(tài)表在數(shù)據(jù)存儲(chǔ)時(shí)會(huì)根據(jù)列定義的寬度定義補(bǔ)足空格，但是在訪問(wèn)的時(shí)候并不會(huì)得到這些空格，這些空格在返回給應(yīng)用之前已經(jīng)去掉。同時(shí)需要注意：在某些情況下可能需要返回字段后的空格，而使用這種格式時(shí)后面到空格會(huì)被自動(dòng)處理掉。
　　動(dòng)態(tài)表包含變長(zhǎng)字段，記錄不是固定長(zhǎng)度的，這樣存儲(chǔ)的優(yōu)點(diǎn)是占用空間較少，但是頻繁到更新刪除記錄會(huì)產(chǎn)生碎片，需要定期執(zhí)行OPTIMIZE TABLE語(yǔ)句或myisamchk -r命令來(lái)改善性能，并且出現(xiàn)故障的時(shí)候恢復(fù)相對(duì)比較困難。
　　壓縮表由myisamchk工具創(chuàng)建，占據(jù)非常小的空間，因?yàn)槊織l記錄都是被單獨(dú)壓縮的，所以只有非常小的訪問(wèn)開(kāi)支。 
(二)InnoDB
　　InnoDB存儲(chǔ)引擎提供了具有提交、回滾和崩潰恢復(fù)能力的事務(wù)安全。但是對(duì)比MyISAM的存儲(chǔ)引擎，InnoDB寫的處理效率差一些并且會(huì)占用更多的磁盤空間以保留數(shù)據(jù)和索引。
1)自動(dòng)增長(zhǎng)列：
　　InnoDB表的自動(dòng)增長(zhǎng)列可以手工插入，但是插入的如果是空或0，則實(shí)際插入到則是自動(dòng)增長(zhǎng)后到值。可以通過(guò)"ALTER TABLE...AUTO_INCREMENT=n;"語(yǔ)句強(qiáng)制設(shè)置自動(dòng)增長(zhǎng)值的起始值，默認(rèn)為1，但是該強(qiáng)制到默認(rèn)值是保存在內(nèi)存中，數(shù)據(jù)庫(kù)重啟后該值將會(huì)丟失?？梢允褂肔AST_INSERT_ID()查詢當(dāng)前線程最后插入記錄使用的值。如果一次插入多條記錄，那么返回的是第一條記錄使用的自動(dòng)增長(zhǎng)值。
對(duì)于InnoDB表，自動(dòng)增長(zhǎng)列必須是索引。如果是組合索引，也必須是組合索引的第一列，但是對(duì)于MyISAM表，自動(dòng)增長(zhǎng)列可以是組合索引的其他列，這樣插入記錄后，自動(dòng)增長(zhǎng)列是按照組合索引到前面幾列排序后遞增的。
2)外鍵約束：
　　MySQL支持外鍵的存儲(chǔ)引擎只有InnoDB，在創(chuàng)建外鍵的時(shí)候，父表必須有對(duì)應(yīng)的索引，子表在創(chuàng)建外鍵的時(shí)候也會(huì)自動(dòng)創(chuàng)建對(duì)應(yīng)的索引。
      在創(chuàng)建索引的時(shí)候，可以指定在刪除、更新父表時(shí)，對(duì)子表進(jìn)行的相應(yīng)操作，包括restrict、cascade、set null和no action。其中restrict和no action相同，是指限制在子表有關(guān)聯(lián)的情況下，父表不能更新；casecade表示父表在更新或刪除時(shí)，更新或者刪除子表對(duì)應(yīng)的記錄；set null 則表示父表在更新或者刪除的時(shí)候，子表對(duì)應(yīng)的字段被set null。
　　當(dāng)某個(gè)表被其它表創(chuàng)建了外鍵參照，那么該表對(duì)應(yīng)的索引或主鍵被禁止刪除。
　　可以使用set foreign_key_checks=0;臨時(shí)關(guān)閉外鍵約束，set foreign_key_checks=1;打開(kāi)約束。
(三)MEMORY
　　memory使用存在內(nèi)存中的內(nèi)容來(lái)創(chuàng)建表。每個(gè)MEMORY表實(shí)際對(duì)應(yīng)一個(gè)磁盤文件，格式是.frm。MEMORY類型的表訪問(wèn)非?？?，因?yàn)樗綌?shù)據(jù)是放在內(nèi)存中的，并且默認(rèn)使用HASH索引，但是一旦服務(wù)器關(guān)閉，表中的數(shù)據(jù)就會(huì)丟失，但表還會(huì)繼續(xù)存在。
      默認(rèn)情況下，memory數(shù)據(jù)表使用散列索引，利用這種索引進(jìn)行“相等比較”非?？?，但是對(duì)“范圍比較”的速度就慢多了。因此，散列索引值適合使用在"="和"<=>"的操作符中，不適合使用在"<"或">"操作符中，也同樣不適合用在order by字句里。如果確實(shí)要使用"<"或">"或betwen操作符，可以使用btree索引來(lái)加快速度。
　　存儲(chǔ)在MEMORY數(shù)據(jù)表里的數(shù)據(jù)行使用的是長(zhǎng)度不變的格式，因此加快處理速度，這意味著不能使用BLOB和TEXT這樣的長(zhǎng)度可變的數(shù)據(jù)類型。VARCHAR是一種長(zhǎng)度可變的類型，但因?yàn)樗贛ySQL內(nèi)部當(dāng)作長(zhǎng)度固定不變的CHAR類型，所以可以使用。

　　使用USING HASH/BTREE來(lái)指定特定到索引。

　　在啟動(dòng)MySQL服務(wù)的時(shí)候使用--init-file選項(xiàng)，把insert into...select或load data infile 這樣的語(yǔ)句放入到這個(gè)文件中，就可以在服務(wù)啟動(dòng)時(shí)從持久穩(wěn)固的數(shù)據(jù)源中裝載表。
　　服務(wù)器需要足夠的內(nèi)存來(lái)維持所在的在同一時(shí)間使用的MEMORY表，當(dāng)不再使用MEMORY表時(shí)，要釋放MEMORY表所占用的內(nèi)存，應(yīng)該執(zhí)行DELETE FROM或truncate table或者刪除整個(gè)表。
　　每個(gè)MEMORY表中放置到數(shù)據(jù)量的大小，受到max_heap_table_size系統(tǒng)變量的約束，這個(gè)系統(tǒng)變量的初始值是16M，同時(shí)在創(chuàng)建MEMORY表時(shí)可以使用MAX_ROWS子句來(lái)指定表中的最大行數(shù)。
(四)MERGE
　　merge存儲(chǔ)引擎是一組MyISAM表的組合，這些MyISAM表結(jié)構(gòu)必須完全相同，MERGE表中并沒(méi)有數(shù)據(jù)，對(duì)MERGE類型的表可以進(jìn)行查詢、更新、刪除的操作，這些操作實(shí)際上是對(duì)內(nèi)部的MyISAM表進(jìn)行操作。對(duì)于對(duì)MERGE表進(jìn)行的插入操作，是根據(jù)INSERT_METHOD子句定義的插入的表，可以有3個(gè)不同的值，first和last值使得插入操作被相應(yīng)的作用在第一個(gè)或最后一個(gè)表上，不定義這個(gè)子句或者為NO，表示不能對(duì)這個(gè)MERGE表進(jìn)行插入操作?？梢詫?duì)MERGE表進(jìn)行drop操作，這個(gè)操作只是刪除MERGE表的定義，對(duì)內(nèi)部的表沒(méi)有任何影響。MERGE在磁盤上保留2個(gè)以MERGE表名開(kāi)頭文件：.frm文件存儲(chǔ)表的定義；.MRG文件包含組合表的信息，包括MERGE表由哪些表組成，插入數(shù)據(jù)時(shí)的依據(jù)?？梢酝ㄟ^(guò)修改.MRG文件來(lái)修改MERGE表，但是修改后要通過(guò)flush table刷新。