MySQL數(shù)據(jù)庫區(qū)別于其他數(shù)據(jù)庫的很重要的一個(gè)特點(diǎn)就是其插件式的表存儲(chǔ)引擎，其基于表，而不是數(shù)據(jù)庫。由于每個(gè)存儲(chǔ)引擎都有其特點(diǎn)，因此我們可以針對(duì)每一張表來挑選最合適的存儲(chǔ)引擎。

作為DBA，我們應(yīng)該深刻的認(rèn)識(shí)存儲(chǔ)引擎。今天介紹兩種最常見的存儲(chǔ)引擎和它們的區(qū)別：InnoDB和MyISAM。

InnoDB存儲(chǔ)引擎

InnoDB存儲(chǔ)引擎支持事務(wù)，其設(shè)計(jì)目標(biāo)主要就是面向OLTP（On Line Transaction Processing 在線事務(wù)處理）的應(yīng)用。特點(diǎn)為行鎖設(shè)計(jì)、支持外鍵，并支持非鎖定讀。從5.5.8版本開始，InnoDB成為了MySQL的默認(rèn)存儲(chǔ)引擎。

InnoDB存儲(chǔ)引擎采用聚集索引（clustered）的方式來存儲(chǔ)數(shù)據(jù)，因此每個(gè)表都是按照主鍵的順序進(jìn)行存放，如果沒有指定主鍵，InnoDB會(huì)為每行自動(dòng)生成一個(gè)6字節(jié)的ROWID作為主鍵。

MyISAM存儲(chǔ)引擎

MyISAM存儲(chǔ)引擎不支持事務(wù)、表鎖設(shè)計(jì)，支持全文索引，主要面向OLAP（On Line Analytical Processing 聯(lián)機(jī)分析處理）應(yīng)用，適用于數(shù)據(jù)倉庫等查詢頻繁的場(chǎng)景。在5.5.8版本之前，MyISAM是MySQL的默認(rèn)存儲(chǔ)引擎。該引擎代表著對(duì)海量數(shù)據(jù)進(jìn)行查詢和分析的需求。它強(qiáng)調(diào)性能，因此在查詢的執(zhí)行速度比InnoDB更快。

InnoDB和MyISAM的區(qū)別

事務(wù)

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

InnoDB支持，MyISAM不支持。

主鍵

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

外鍵

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

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ǔ)表索引

執(zhí)行速度

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

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

InnoDB會(huì)緩存索引和數(shù)據(jù)文件，一般以16KB為一個(gè)最小單元（數(shù)據(jù)頁大?。┖痛疟P進(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í)相同的空間能夠加載