題外話：中華文化博大進(jìn)深，從學(xué)Java到數(shù)據(jù)庫(kù)，無(wú)一不體現(xiàn)出同一組件魚(yú)和熊掌不可兼得的要義。自然，編程中安全和效率也很難同時(shí)做到完美，這一次InnoDB和MyISAM又讓我大開(kāi)眼界。

好了，Talk is cheap,show you the code：

測(cè)試環(huán)境：Mysql 5.7.20-log,IDEA 2018

首先創(chuàng)建兩張表: testinnodb,testmyisam，sql如下：

1.同時(shí)大批量插入數(shù)據(jù)（百萬(wàn)級(jí)，million），小編采用了存儲(chǔ)過(guò)程，代碼及測(cè)試結(jié)果如下：

下面代碼在IDEA上運(yùn)行即可：

同時(shí)插入100W條數(shù)據(jù)，MyISAM耗時(shí)38s左右，而InnoDB卻耗時(shí)76分鐘4s左右，很明顯可以看出MyISAM在處理速度上完勝I(mǎi)nnoDB,但是如果實(shí)際項(xiàng)目中使用，由于涉及到數(shù)據(jù)安全（或者事物安全）問(wèn)題，大多數(shù)公司還是選擇了InnoDB, 較少公司使用MyISAM(得力于其在業(yè)務(wù)層的嚴(yán)格控制)。但MyISAM依然可以被我們使用在日志數(shù)據(jù)分析，實(shí)驗(yàn)等環(huán)境中。

2.再看其在刪改查方面的對(duì)比

改查耗時(shí)

刪除耗時(shí)

其實(shí)對(duì)比下來(lái)，差距并沒(méi)有插入數(shù)據(jù)那樣夸張，對(duì)于大多數(shù)要求事物安全的公司來(lái)說(shuō)還是可以接受的。

PS: 你可以使用mysql插件profile來(lái)顯示最近執(zhí)行命令的持續(xù)時(shí)長(zhǎng)，用法如下：

mysql默認(rèn)是關(guān)閉profiles的，你需要開(kāi)啟它，

查看是否開(kāi)啟命令： show variables like '%pro%';

小編已經(jīng)把他開(kāi)啟了，所以顯示為ON，默認(rèn)為OFF。

開(kāi)啟命令：set profiling=1;

關(guān)閉命令：set profiling=0;

查詢(xún)最近使用命令時(shí)長(zhǎng)：show profiles;

根據(jù)Query ID查詢(xún)單個(gè)命令詳情: show profile for query 1;

根據(jù)Query ID查詢(xún)單個(gè)命令memory，source,cpu等詳情: show profile cpu for query 1;或者 show profile all for query 1;

測(cè)試Over，接下來(lái)總結(jié)一下:

1.InnoDB支持事物，外鍵等高級(jí)的數(shù)據(jù)庫(kù)功能，MyISAM不支持。需要注意的是，InnDB行級(jí)鎖也不是絕對(duì)的，例如mysql執(zhí)行一個(gè)未定范圍的sql時(shí)，也還是會(huì)鎖表，例如sql中l(wèi)ike的使用

2.效率，明顯MyISAM在插入數(shù)據(jù)的表現(xiàn)是InnoDB所遠(yuǎn)遠(yuǎn)不及的，在刪改查，隨著InnoDB的優(yōu)化，差距漸漸變小

3.行數(shù)查詢(xún)，InnoDB不保存行數(shù)，也就是select的時(shí)候，要掃描全表，MyISAM只需讀取保存的行數(shù)即可，這也是MyISAM查詢(xún)速度快的一個(gè)因素。

4.索引，InnoDB會(huì)自動(dòng)創(chuàng)建Auto_Increment類(lèi)型字段的索引，一般習(xí)慣應(yīng)用于主鍵，即主鍵索引（只包含該字段），而MyISAM可以和其他字段創(chuàng)建聯(lián)合索引。

除此之外，MyISAM還支持全文索引（FULLTEXT_INDEX），壓縮索引,InnoDB不支持。

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

InnoDB存儲(chǔ)引擎被完全與MySQL服務(wù)器整合，InnoDB存儲(chǔ)引擎為在主內(nèi)存中緩存數(shù)據(jù)和索引而維持它自己的緩沖池。InnoDB存儲(chǔ)它的表＆索引在一個(gè)表空間中，表空間可以包含數(shù)個(gè)文件（或原始磁盤(pán)分區(qū)）。這與MyISAM表不同，比如在MyISAM表中每個(gè)表被存在分離的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制為2GB的操作系統(tǒng)上。

5.服務(wù)器數(shù)據(jù)備份。InnoDB必須導(dǎo)出SQL來(lái)備份，LOAD TABLE FROM MASTER操作對(duì)InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導(dǎo)入數(shù)據(jù)后再改成InnoDB表，但是對(duì)于使用的額外的InnoDB特性(例如外鍵)的表不適用。

備注：而且MyISAM應(yīng)對(duì)錯(cuò)誤編碼導(dǎo)致的數(shù)據(jù)恢復(fù)速度快。MyISAM的數(shù)據(jù)是以文件的形式存儲(chǔ)，所以在跨平臺(tái)的數(shù)據(jù)轉(zhuǎn)移中會(huì)很方便。在備份和恢復(fù)時(shí)可單獨(dú)針對(duì)某個(gè)表進(jìn)行操作。

InnoDB是拷貝數(shù)據(jù)文件、備份 binlog，或者用 mysqldump，支持災(zāi)難恢復(fù)（僅需幾分鐘），MyISAM不支持，遇到數(shù)據(jù)崩潰，基本上很難恢復(fù)，所以要經(jīng)常進(jìn)行數(shù)據(jù)備份。

6.鎖的支持。**MyISAM只支持表鎖。InnoDB支持表鎖、行鎖 行鎖大幅度提高了多用戶(hù)并發(fā)操作的新能。但是InnoDB的行鎖，只是在WHERE的主鍵是有效的，非主鍵的WHERE都會(huì)鎖全表的

使用場(chǎng)景建議：

1）可靠性高或者要求事務(wù)處理，則使用InnoDB。這個(gè)是必須的。

2）表更新和查詢(xún)都相當(dāng)?shù)念l繁，并且表鎖定的機(jī)會(huì)比較大的情況指定InnoDB數(shù)據(jù)引擎的創(chuàng)建。

對(duì)比之下，MyISAM的使用場(chǎng)景：

1）做很多count的計(jì)算的。如一些日志，調(diào)查的業(yè)務(wù)表。

2）插入修改不頻繁，查詢(xún)非常頻繁的。

MySQL能夠允許你在表這一層應(yīng)用數(shù)據(jù)庫(kù)引擎，所以你可以只對(duì)需要事務(wù)處理的表格來(lái)進(jìn)行性能優(yōu)化，而把不需要事務(wù)處理的表格交給更加輕便的MyISAM引擎。對(duì)于 MySQL而言，靈活性才是關(guān)鍵。

引擎原理分析

MyISAM索引結(jié)構(gòu): MyISAM索引用的B+ tree來(lái)儲(chǔ)存數(shù)據(jù)，MyISAM索引的指針指向的是鍵值的地址，地址存儲(chǔ)的是數(shù)據(jù)。B+Tree的數(shù)據(jù)域存儲(chǔ)的內(nèi)容為實(shí)際數(shù)據(jù)的地址，也就是說(shuō)它的索引和實(shí)際的數(shù)據(jù)是分開(kāi)的，只不過(guò)是用索引指向了實(shí)際的數(shù)據(jù)，這種索引就是所謂的非聚集索引

主索引如下：

輔助索引如下：

因此，過(guò)程為： MyISAM中索引檢索的算法為首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，則取出其data域的值，然后以data域的值為地址，根據(jù)data域的值去讀取相應(yīng)數(shù)據(jù)記錄。

InnoDB引擎的索引結(jié)構(gòu)：

也是B+Treee索引結(jié)構(gòu)。Innodb的索引文件本身就是數(shù)據(jù)文件，即B+Tree的數(shù)據(jù)域存儲(chǔ)的就是實(shí)際的數(shù)據(jù)，這種索引就是聚集索引。這個(gè)索引的key就是數(shù)據(jù)表的主鍵，因此InnoDB表數(shù)據(jù)文件本身就是主索引。

InnoDB的輔助索引數(shù)據(jù)域存儲(chǔ)的也是相應(yīng)記錄主鍵的值而不是地址，所以當(dāng)以輔助索引查找時(shí)，會(huì)先根據(jù)輔助索引找到主鍵，再根據(jù)主鍵索引找到實(shí)際的數(shù)據(jù)。所以Innodb不建議使用過(guò)長(zhǎng)的主鍵，否則會(huì)使輔助索引變得過(guò)大。

建議使用自增的字段作為主鍵，這樣B+Tree的每一個(gè)結(jié)點(diǎn)都會(huì)被順序的填滿，而不會(huì)頻繁的分裂調(diào)整，會(huì)有效的提升插入數(shù)據(jù)的效率。

主索引如下：

輔助索引如下：

上圖，可以看到葉節(jié)點(diǎn)包含了完整的數(shù)據(jù)記錄。這種索引叫做聚集索引。因?yàn)镮nnoDB的數(shù)據(jù)文件本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒(méi)有），如果沒(méi)有顯式指定，則MySQL系統(tǒng)會(huì)自動(dòng)選擇一個(gè)可以唯一標(biāo)識(shí)數(shù)據(jù)記錄的列作為主鍵，如果不存在這種列，則MySQL自動(dòng)為InnoDB表生成一個(gè)隱含字段作為主鍵，這個(gè)字段長(zhǎng)度為6個(gè)字節(jié)，類(lèi)型為長(zhǎng)整形。

而且，與MyISAM索引的不同是InnoDB的輔助索引data域存儲(chǔ)相應(yīng)記錄主鍵的值而不是地址。換句話說(shuō)，InnoDB的所有輔助索引都引用主鍵作為data域。

因此，過(guò)程為：將主鍵組織到一棵B+樹(shù)中，而行數(shù)據(jù)就儲(chǔ)存在葉子節(jié)點(diǎn)上，若使用”where id = 13”這樣的條件查找主鍵，則按照B+樹(shù)的檢索算法即可查找到對(duì)應(yīng)的葉節(jié)點(diǎn)，之后獲得行數(shù)據(jù)。若對(duì)Name列進(jìn)行條件搜索，則需要兩個(gè)步驟：第一步在輔助索引B+樹(shù)中檢索Name，到達(dá)其葉子節(jié)點(diǎn)獲取對(duì)應(yīng)的主鍵。第二步使用主鍵在主索引B+樹(shù)種再執(zhí)行一次B+樹(shù)檢索操作，最終到達(dá)葉子節(jié)點(diǎn)即可獲取整行數(shù)據(jù)。

兩種索引數(shù)據(jù)查找過(guò)程如下：