mysql存儲引擎之MyISAM 和 InnoDB的比較

一、什么是存儲引擎

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

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

二、MyISAM

??????

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

1、tb_Demo.frm（存儲表定義）

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

3、Tb_Demo.MYI（MYIndex，存儲索引）

???????

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

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

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

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

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

???????

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

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

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

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

??????

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

三、InnoDB

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

1、自動增長列：
　　

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

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

2、外鍵約束：
　　

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

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

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

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

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

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

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

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

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

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

4、從MySQL5.5.5以后，InnoDB是默認引擎。

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

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

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

8、清空整個表時，InnoDB是一行一行的刪除，效率非常慢。MyISAM則會重建表。

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

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

?????

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

1、如果你的應(yīng)用程序一定要使用事務(wù)，毫無疑問你要選擇INNODB引擎。但要注意，INNODB的行級鎖是有條件的。在where條件沒有使用主鍵時，照樣會鎖全表。比如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)用一般是不會去選用的。

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

MyISAM和InnoDB是MySQL的兩種存儲引擎。

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

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

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

Mysql中InnoDB和MyISAM的比較

1、MyISAM：
?????

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

??????

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

??????

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

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

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

2、InnoDB：

????

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

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

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

??????

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

基本的差別為：

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

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

主要區(qū)別：

應(yīng)用場景：

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