mysql存儲(chǔ)引擎之MyISAM 和 InnoDB的比較

一、什么是存儲(chǔ)引擎

存儲(chǔ)引擎說白了就是如何存儲(chǔ)數(shù)據(jù)、如何為存儲(chǔ)的數(shù)據(jù)建立索引和如何更新、查詢數(shù)據(jù)等技術(shù)的實(shí)現(xiàn)方法。因?yàn)樵陉P(guān)系數(shù)據(jù)庫中數(shù)據(jù)的存儲(chǔ)是以表的形式存儲(chǔ)的，所以存儲(chǔ)引擎也可以稱為表類型（即存儲(chǔ)和操作此表的類型）。
??????

在Oracle 和SQL Server等數(shù)據(jù)庫中只有一種存儲(chǔ)引擎，所有數(shù)據(jù)存儲(chǔ)管理機(jī)制都是一樣的。而MySql數(shù)據(jù)庫提供了多種存儲(chǔ)引擎。用戶可以根據(jù)不同的需求為數(shù)據(jù)表選擇不同的存儲(chǔ)引擎，用戶也可以根據(jù)自己的需要編寫自己的存儲(chǔ)引擎。

二、MyISAM

??????

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

1、tb_Demo.frm（存儲(chǔ)表定義）

2、tb_Demo.MYD（MYData，存儲(chǔ)數(shù)據(jù)）

3、Tb_Demo.MYI（MYIndex，存儲(chǔ)索引）

???????

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

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

MyISAM的表還支持3種不同的存儲(chǔ)格式：

其中靜態(tài)表是默認(rèn)的存儲(chǔ)格式。靜態(tài)表中的字段都是非變長字段，這樣每個(gè)記錄都是固定長度的，這種存儲(chǔ)方式的優(yōu)點(diǎn)是存儲(chǔ)非常迅速，容易緩存，出現(xiàn)故障容易恢復(fù)；缺點(diǎn)是占用的空間通常比動(dòng)態(tài)表多。靜態(tài)表在數(shù)據(jù)存儲(chǔ)時(shí)會(huì)根據(jù)列定義的寬度定義補(bǔ)足空格，但是在訪問的時(shí)候并不會(huì)得到這些空格，這些空格在返回給應(yīng)用之前已經(jīng)去掉。同時(shí)需要注意：在某些情況下可能需要返回字段后的空格，而使用這種格式時(shí)后面到空格會(huì)被自動(dòng)處理掉。

動(dòng)態(tài)表包含變長字段，記錄不是固定長度的，這樣存儲(chǔ)的優(yōu)點(diǎn)是占用空間較少，但是頻繁到更新刪除記錄會(huì)產(chǎn)生碎片，需要定期執(zhí)行OPTIMIZE TABLE語句或myisamchk -r命令來改善性能，并且出現(xiàn)故障的時(shí)候恢復(fù)相對比較困難。

???????

壓縮表由myisamchk工具創(chuàng)建，占據(jù)非常小的空間，因?yàn)槊織l記錄都是被單獨(dú)壓縮的，所以只有非常小的訪問開支。

靜態(tài)MyISAM：如果數(shù)據(jù)表中的各數(shù)據(jù)列的長度都是預(yù)先固定好的，服務(wù)器將自動(dòng)選擇這種表類型。因?yàn)閿?shù)據(jù)表中每一條記錄所占用的空間都是一樣的，所以這種表存取和更新的效率非常高。當(dāng)數(shù)據(jù)受損時(shí)，恢復(fù)工作也比較容易做。

動(dòng)態(tài)MyISAM：如果數(shù)據(jù)表中出現(xiàn)varchar、xxxtext或xxxBLOB字段時(shí)，服務(wù)器將自動(dòng)選擇這種表類型。相對于靜態(tài)MyISAM，這種表存儲(chǔ)空間比較小，但由于每條記錄的長度不一，所以多次修改數(shù)據(jù)后，數(shù)據(jù)表中的數(shù)據(jù)就可能離散的存儲(chǔ)在內(nèi)存中，進(jìn)而導(dǎo)致執(zhí)行效率下降。同時(shí)，內(nèi)存中也可能會(huì)出現(xiàn)很多碎片。因此，這種類型的表要經(jīng)常用optimize table 命令或優(yōu)化工具來進(jìn)行碎片整理。

壓縮MyISAM：以上說到的兩種類型的表都可以用myisamchk工具壓縮。這種類型的表進(jìn)一步減小了占用的存儲(chǔ)，但是這種表壓縮之后不能再被修改。另外，因?yàn)槭菈嚎s數(shù)據(jù)，所以這種表在讀取的時(shí)候要先時(shí)行解壓縮。

??????

但是，不管是何種MyISAM表，目前它都不支持事務(wù)，行級(jí)鎖和外鍵約束的功能。

三、InnoDB

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

1、自動(dòng)增長列：
　　

InnoDB表的自動(dòng)增長列可以手工插入，但是插入的如果是空或0，則實(shí)際插入到則是自動(dòng)增長后到值?？梢酝ㄟ^"ALTER TABLE...AUTO_INCREMENT=n;"語句強(qiáng)制設(shè)置自動(dòng)增長值的起始值，默認(rèn)為1，但是該強(qiáng)制到默認(rèn)值是保存在內(nèi)存中，數(shù)據(jù)庫重啟后該值將會(huì)丟失?？梢允褂肔AST_INSERT_ID()查詢當(dāng)前線程最后插入記錄使用的值。如果一次插入多條記錄，那么返回的是第一條記錄使用的自動(dòng)增長值。
??????

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

2、外鍵約束：
　　

MySQL支持外鍵的存儲(chǔ)引擎只有InnoDB，在創(chuàng)建外鍵的時(shí)候，父表必須有對應(yīng)的索引，子表在創(chuàng)建外鍵的時(shí)候也會(huì)自動(dòng)創(chuàng)建對應(yīng)的索引。
??????

在創(chuàng)建索引的時(shí)候，可以指定在刪除、更新父表時(shí)，對子表進(jìn)行的相應(yīng)操作，包括restrict、cascade、set null和no action。其中restrict和no action相同，是指限制在子表有關(guān)聯(lián)的情況下，父表不能更新；casecade表示父表在更新或刪除時(shí)，更新或者刪除子表對應(yīng)的記錄；set null 則表示父表在更新或者刪除的時(shí)候，子表對應(yīng)的字段被set null。
　　

當(dāng)某個(gè)表被其它表創(chuàng)建了外鍵參照，那么該表對應(yīng)的索引或主鍵被禁止刪除。
　　

可以使用set foreign_key_checks=0;臨時(shí)關(guān)閉外鍵約束，set foreign_key_checks=1;打開約束。

四、MyISAM 和 InnoDB 的區(qū)別

雖然MySQL里的存儲(chǔ)引擎不只是MyISAM與InnoDB這兩個(gè)，但常用的就是兩個(gè)。

兩種存儲(chǔ)引擎的大致區(qū)別表現(xiàn)在：

1、InnoDB支持事務(wù)，MyISAM不支持，這一點(diǎn)是非常之重要。事務(wù)是一種高級(jí)的處理方式，如在一些列增刪改中只要哪個(gè)出錯(cuò)還可以回滾還原，而MyISAM就不可以了。

2、MyISAM適合查詢以及插入為主的應(yīng)用，InnoDB適合頻繁修改以及涉及到安全性較高的應(yīng)用。

3、InnoDB支持外鍵，MyISAM不支持。

4、從MySQL5.5.5以后，InnoDB是默認(rèn)引擎。

5、InnoDB不支持FULLTEXT類型的索引。

6、InnoDB中不保存表的行數(shù)，如select count(*) from table時(shí)，InnoDB需要掃描一遍整個(gè)表來計(jì)算有多少行，但是MyISAM只要簡單的讀出保存好的行數(shù)即可。注意的是，當(dāng)count(*)語句包含where條件時(shí)MyISAM也需要掃描整個(gè)表。

7、對于自增長的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中可以和其他字段一起建立聯(lián)合索引。

8、清空整個(gè)表時(shí)，InnoDB是一行一行的刪除，效率非常慢。MyISAM則會(huì)重建表。

9、InnoDB支持行鎖（某些情況下還是鎖整表，如 update table set a=1 where user like '%lee%'）。

四、關(guān)于MyISAM與InnoDB選擇使用：

?????

MYISAM和INNODB是Mysql數(shù)據(jù)庫提供的兩種存儲(chǔ)引擎。兩者的優(yōu)劣可謂是各有千秋。INNODB會(huì)支持一些關(guān)系數(shù)據(jù)庫的高級(jí)功能，如事務(wù)功能和行級(jí)鎖，MYISAM不支持。MYISAM的性能更優(yōu)，占用的存儲(chǔ)空間少。所以，選擇何種存儲(chǔ)引擎，視具體應(yīng)用而定：

1、如果你的應(yīng)用程序一定要使用事務(wù)，毫無疑問你要選擇INNODB引擎。但要注意，INNODB的行級(jí)鎖是有條件的。在where條件沒有使用主鍵時(shí)，照樣會(huì)鎖全表。比如DELETE FROM mytable這樣的刪除語句。

2、如果你的應(yīng)用程序?qū)Σ樵冃阅芤筝^高，就要使用MYISAM了。MYISAM索引和數(shù)據(jù)是分開的，而且其索引是壓縮的，可以更好地利用內(nèi)存。所以它的查詢性能明顯優(yōu)于INNODB。壓縮后的索引也能節(jié)約一些磁盤空間。MYISAM擁有全文索引的功能，這可以極大地優(yōu)化LIKE查詢的效率。

現(xiàn)在一般都是選用innodb了，主要是myisam的全表鎖，讀寫串行問題，并發(fā)效率鎖表，效率低myisam對于讀寫密集型應(yīng)用一般是不會(huì)去選用的。

關(guān)于Mysql數(shù)據(jù)庫默認(rèn)的存儲(chǔ)引擎：

MyISAM和InnoDB是MySQL的兩種存儲(chǔ)引擎。

如果是默認(rèn)安裝，那就應(yīng)該是InnoDB，你可以在my.cnf文件中找到default-storage-engine=INNODB；

當(dāng)然你可以在建表時(shí)指定相應(yīng)的存儲(chǔ)引擎。

通過show create table xx 可以看見相應(yīng)信息。

Mysql中InnoDB和MyISAM的比較

1、MyISAM：
?????

每個(gè)MyISAM在磁盤上存儲(chǔ)成三個(gè)文件。第一個(gè)文件的名字以表的名字開始，擴(kuò)展名指出文件類型。.frm文件存儲(chǔ)表定義。數(shù)據(jù)文件的擴(kuò)展名為.MYD (MYData)。
MyISAM表格可以被壓縮，而且它們支持全文搜索。不支持事務(wù)，而且也不支持外鍵。如果事物回滾將造成不完全回滾，不具有原子性。在進(jìn)行update時(shí)進(jìn)行表鎖，并發(fā)量相對較小。如果執(zhí)行大量的SELECT，MyISAM是更好的選擇。

??????

MyISAM的索引和數(shù)據(jù)是分開的，并且索引是有壓縮的，內(nèi)存使用率就對應(yīng)提高了不少。能加載更多索引，而Innodb是索引和數(shù)據(jù)是緊密捆綁的，沒有使用壓縮從而會(huì)造成Innodb比MyISAM體積龐大不少。

??????

MyISAM緩存在內(nèi)存的是索引，不是數(shù)據(jù)。而InnoDB緩存在內(nèi)存的是數(shù)據(jù)，相對來說，服務(wù)器內(nèi)存越大，InnoDB發(fā)揮的優(yōu)勢越大。

優(yōu)點(diǎn)：查詢數(shù)據(jù)相對較快，適合大量的select，可以全文索引。

缺點(diǎn)：不支持事務(wù)，不支持外鍵，并發(fā)量較小，不適合大量update。

2、InnoDB：

????

這種類型是事務(wù)安全的。.它與BDB類型具有相同的特性，它們還支持外鍵。InnoDB表格速度很快。具有比BDB還豐富的特性，因此如果需要一個(gè)事務(wù)安全的存儲(chǔ)引擎，建議使用它。在update時(shí)表進(jìn)行行鎖，并發(fā)量相對較大。如果你的數(shù)據(jù)執(zhí)行大量的INSERT或UPDATE，出于性能方面的考慮，應(yīng)該使用InnoDB表。

優(yōu)點(diǎn)：支持事務(wù)，支持外鍵，并發(fā)量較大，適合大量update。

缺點(diǎn)：查詢數(shù)據(jù)相對較快，不適合大量的select。

??????

對于支持事物的InnoDB類型的表，影響速度的主要原因是AUTOCOMMIT默認(rèn)設(shè)置是打開的，而且程序沒有顯式調(diào)用BEGIN 開始事務(wù)，導(dǎo)致每插入一條都自動(dòng)Commit，嚴(yán)重影響了速度。可以在執(zhí)行sql前調(diào)用begin，多條sql形成一個(gè)事物（即使autocommit打開也可以），將大大提高性能。

基本的差別為：

MyISAM類型不支持事務(wù)處理等高級(jí)處理，而InnoDB類型支持。

MyISAM類型的表強(qiáng)調(diào)的是性能，其執(zhí)行數(shù)度比InnoDB類型更快，但是不提供事務(wù)支持，而InnoDB提供事務(wù)支持已經(jīng)外部鍵等高級(jí)數(shù)據(jù)庫功能。

主要區(qū)別：

應(yīng)用場景：

原文鏈接：cnblogs.com/xiaoxi/p/7404870.html