MySQL中InnoDB與MyISAM的區(qū)別

這期內(nèi)容當(dāng)中的小編將會(huì)給大家?guī)碛嘘P(guān)MySQL中InnoDB與MyISAM的區(qū)別，以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

InnoDB存儲(chǔ)引擎

InnoDB，是MySQL的數(shù)據(jù)庫引擎之一，現(xiàn)為MySQL的默認(rèn)存儲(chǔ)引擎，為MySQL AB發(fā)布binary的標(biāo)準(zhǔn)之一。InnoDB由Innobase Oy公司所開發(fā)，2006年五月時(shí)由甲骨文公司并購。與傳統(tǒng)的ISAM與MyISAM相比，InnoDB的最大特色就是支持了ACID兼容的事務(wù)（Transaction）功能，類似于PostgreSQL。

InnoDB 給 MySQL 提供了具有事務(wù)(transaction)、回滾(rollback)和崩潰修復(fù)能力(crash recovery capabilities)、多版本并發(fā)控制(multi-versioned concurrency control)的事務(wù)安全(transaction-safe (ACID compliant))型表。

MyISAM存儲(chǔ)引擎

MyISAM是默認(rèn)存儲(chǔ)引擎（Mysql5.1前）。它基于更老的ISAM代碼，但有很多有用的擴(kuò)展。（注意MySQL 5.1不支持ISAM）。 每個(gè)MyISAM在磁盤上存儲(chǔ)成三個(gè)文件，每一個(gè)文件的名字均以表的名字開始，擴(kuò)展名指出文件類型。

InnoDB和MyISAM的區(qū)別

事務(wù)

為了數(shù)據(jù)庫操作的原子性，我們需要事務(wù)。保證一組操作要么都成功，要么都失敗，比如轉(zhuǎn)賬的功能。我們通常將多條SQL語句放在begin和commit之間，組成一個(gè)事務(wù)。

InnoDB支持，MyISAM不支持。

索引

為了優(yōu)化查詢的速度，進(jìn)行排序和匹配查找，我們需要索引。比如所有人的姓名從a-z首字母進(jìn)行順序存儲(chǔ)，當(dāng)我們查找zhangsan或者第44位的時(shí)候就可以很快的定位到我們想要的位置進(jìn)行查找。

InnoDB是聚集索引，數(shù)據(jù)和主鍵的聚集索引綁定在一起，通過主鍵索引效率很高。如果通過其他列的輔助索引來進(jìn)行查找，需要先查找到聚集索引，再查詢到所有數(shù)據(jù)，需要兩次查詢。

MyISAM是非聚集索引，數(shù)據(jù)文件是分離的，索引保存的是數(shù)據(jù)的指針。

從InnoDB 1.2.x版本，MySQL5.6版本后，兩者都支持全文索引。

auto_increment自增

對(duì)于自增數(shù)的字段，InnoDB要求該列必須是索引，同時(shí)必須是索引的第一個(gè)列，否則會(huì)報(bào)錯(cuò)：

mysql> create table test(
    -> a int auto_increment,
    -> b int,
    -> key(b,a)
    -> ) engine=InnoDB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

把(b,a)順序替換為(a,b)即可。

而MyISAM可以將該字段與其他字段隨意順序組成成聯(lián)合索引。

表行數(shù)

很常見的需求是看表中有多少條數(shù)據(jù)，此時(shí)我們需要select count(*) from table_name。

InnoDB不保存表行數(shù)，需要進(jìn)行全表掃描。MyISAM用一個(gè)變量保存，直接讀取該值，更快。當(dāng)時(shí)當(dāng)帶有where查詢的時(shí)候，兩者一樣。

存儲(chǔ)

數(shù)據(jù)庫的文件都是需要在磁盤中進(jìn)行存儲(chǔ)，當(dāng)應(yīng)用需要時(shí)再讀取到內(nèi)存中。一般包含數(shù)據(jù)文件、索引文件。

InnoDB分為：

• .frm表結(jié)構(gòu)文件
• .ibdata1共享表空間
• .ibd表獨(dú)占空間
• .redo日志文件

MyISAM分為三個(gè)文件：

• .frm存儲(chǔ)表定義
• .MYD存儲(chǔ)表數(shù)據(jù)
• .MYI存儲(chǔ)表索引

主鍵

由于InnoDB的聚集索引，其如果沒有指定主鍵，就會(huì)自動(dòng)生成主鍵。
MyISAM支持沒有主鍵的表存在。

外鍵

為了解決復(fù)雜邏輯的依賴，我們需要外鍵。比如高考成績(jī)的錄入，必須歸屬于某位同學(xué)，我們就需要高考成績(jī)數(shù)據(jù)庫里有準(zhǔn)考證號(hào)的外鍵。

InnoDB支持，MyISAM不支持。

執(zhí)行速度

如果你的操作是大量的查詢操作，如SELECT，使用MyISAM性能會(huì)更好。
如果大部分是刪除和更改的操作，使用InnoDB。

InnoDB和MyISAM的索引都是B+樹索引，通過索引可以查詢到數(shù)據(jù)的主鍵，不熟悉B+樹的可以查看MySQL InnoDB索引原理和算法。兩者的性能區(qū)別主要在于查詢到數(shù)據(jù)主鍵后兩者的處理方式卻不同。

InnoDB會(huì)緩存索引和數(shù)據(jù)文件，一般以16KB為一個(gè)最小單元（數(shù)據(jù)頁大小）和磁盤進(jìn)行交互，InnoDB在查詢到索引數(shù)據(jù)后實(shí)際得到的是主鍵的ID，它需要在內(nèi)存中的數(shù)據(jù)頁中查找該行的全部數(shù)據(jù)，但如果該數(shù)據(jù)不是加載過的熱數(shù)據(jù)，還需要進(jìn)行數(shù)據(jù)頁的查找和替換，這其中可能牽涉到多次I/O操作和內(nèi)存中數(shù)據(jù)查找，導(dǎo)致耗時(shí)較高。

而MyISAM存儲(chǔ)引擎只緩存索引文件，不緩存數(shù)據(jù)文件，其數(shù)據(jù)文件的緩存直接使用操作系統(tǒng)的緩存，這點(diǎn)非常獨(dú)特。此時(shí)相同的空間能夠加載更多的索引，因此當(dāng)緩存空間有限時(shí)，MyISAM的索引數(shù)據(jù)頁替換次數(shù)會(huì)更少。根據(jù)前面我們知道MyISAM的文件分為MYI和MYD，當(dāng)我們通過MYI查找到主鍵ID時(shí)，其實(shí)得到是MYD數(shù)據(jù)文件的offset偏移量，查找數(shù)據(jù)比InnoDB尋址映射要快的多。

但由于MyISAM是表鎖，而InnoDB支持行鎖，因此在牽涉到大量寫操作時(shí)，InnoDB的并發(fā)性能比MyISAM好很多。同時(shí)InnoDB還通過MVVC多版本控制來提高并發(fā)讀寫性能。

delete刪除數(shù)據(jù)

調(diào)用delete from table時(shí)，MyISAM會(huì)直接重建表，InnoDB會(huì)一行一行的刪除，但是可以用truncate table代替。參考: mysql清空表數(shù)據(jù)的兩種方式和區(qū)別。

鎖

MyISAM僅支持表鎖，每次操作鎖定整張表。
InnoDB支持行鎖，每次操作鎖住最小數(shù)量的行數(shù)據(jù)。

表鎖相比于行鎖消耗的資源更少，且不會(huì)出現(xiàn)死鎖，但同時(shí)并發(fā)性能差。行鎖消耗更多的資源，速度較慢，且可能發(fā)生死鎖，但是因?yàn)殒i定的粒度小、數(shù)據(jù)少，并發(fā)性能好。如果InnoDB的一條語句無法確定要掃描的范圍，也會(huì)鎖定整張表。

當(dāng)行鎖發(fā)生死鎖的時(shí)候，會(huì)計(jì)算每個(gè)事務(wù)影響的行數(shù)，然后回滾行數(shù)較少的事務(wù)。

數(shù)據(jù)恢復(fù)

MyISAM崩潰后無法快速的安全恢復(fù)。InnoDB有一套完善的恢復(fù)機(jī)制。

數(shù)據(jù)緩存

MyISAM僅緩存索引數(shù)據(jù)，通過索引查詢數(shù)據(jù)。InnoDB不僅緩存索引數(shù)據(jù)，同時(shí)緩存數(shù)據(jù)信息，將數(shù)據(jù)按頁讀取到緩存池，按LRU（Latest Rare Use 最近最少使用）算法來進(jìn)行更新。

如何選擇存儲(chǔ)引擎

創(chuàng)建表的語句都是相同的，只有最后的type來指定存儲(chǔ)引擎。

MyISAM

1、大量查詢總count

2、查詢頻繁，插入不頻繁

3、沒有事務(wù)操作

InnoDB

1、需要高可用性，或者需要事務(wù)

2、表更新頻繁

上述就是小編為大家分享的MySQL中InnoDB與MyISAM的區(qū)別了，如果您也有類似的疑惑，不妨礙參照上述分析進(jìn)行理解。如果想了解更多相關(guān)內(nèi)容，請(qǐng)關(guān)注億速云行業(yè)資訊。