目錄：

1、存儲(chǔ)引擎介紹

2、InnoDB及MyISAM性能對(duì)比

3、MyISAM和InnoDB的鎖對(duì)比

4、兩種存儲(chǔ)引擎索引對(duì)比

1 存儲(chǔ)引擎介紹

說(shuō)明：基于5.7.19的MySQL數(shù)據(jù)庫(kù)。

圖1.1 數(shù)據(jù)庫(kù)版本

在Navicat for MySQL中進(jìn)行測(cè)試：

輸入sql：show engines;

圖1.2 存儲(chǔ)引擎類(lèi)別

參數(shù)說(shuō)明：

Engine：存儲(chǔ)引擎名稱(chēng)

Support：MySQL是否支持該引擎

Comment：對(duì)該引擎的說(shuō)明

Transaction：是否支持事務(wù)處理

XA：是否分布式交易處理XA規(guī)范

Savepoints：是否支持保存點(diǎn)，以便事務(wù)回滾到保存點(diǎn)

l InnoDB

適用于高性能和事務(wù)處理環(huán)境，支持外鍵，默認(rèn)的存儲(chǔ)引擎，“拆包即用”。

l MyISAM

適用于以只讀為主的數(shù)據(jù)倉(cāng)庫(kù)、電子商務(wù)和企業(yè)應(yīng)用中。MyISAM使用高級(jí)緩存和索引機(jī)制提高數(shù)據(jù)檢索和索引速度，但不支持事務(wù)，不支持外鍵。

l Blackhole

適用于測(cè)試應(yīng)用程序確實(shí)在寫(xiě)數(shù)據(jù)而又不希望在磁盤(pán)上存儲(chǔ)任何數(shù)據(jù)的場(chǎng)景。Blackhole存儲(chǔ)引擎滿足一個(gè)特定的需求，如果啟用了二進(jìn)制日志，SQL語(yǔ)句將被寫(xiě)入日志，將Blackhole存儲(chǔ)引擎作為復(fù)制拓?fù)渲械闹欣^或代理。在這種情況下，中繼代理處理來(lái)自master的數(shù)據(jù)，并將這些數(shù)據(jù)發(fā)送到它的slave上去，但是它本身并不存儲(chǔ)任何數(shù)據(jù)。

l CSV

適用于寫(xiě)CSV日志文件，將結(jié)構(gòu)化業(yè)務(wù)數(shù)據(jù)快速導(dǎo)入電子表格。CSV存儲(chǔ)引擎以表格形式創(chuàng)建、讀取和寫(xiě)入逗號(hào)分隔值（CSV）文件。不提供任何索引機(jī)制，在存儲(chǔ)和轉(zhuǎn)換日期時(shí)間值時(shí)也存在某些問(wèn)題，存儲(chǔ)數(shù)據(jù)的效率不高，因此應(yīng)該謹(jǐn)慎使用。

l Memory

適用于頻繁訪問(wèn)而很少更改的靜態(tài)數(shù)據(jù)的情況，例如郵編列表、省市區(qū)列表、分類(lèi)列表等等，以及適用于利用快照技術(shù)訪問(wèn)分布數(shù)據(jù)或歷史數(shù)據(jù)的數(shù)據(jù)庫(kù)。Memory（有時(shí)被稱(chēng)為HEAP）是內(nèi)存中的存儲(chǔ)器，使用哈希機(jī)制檢索頻繁使用的數(shù)據(jù)，從而檢索更快。由于數(shù)據(jù)存儲(chǔ)在內(nèi)存中，只在MySQL會(huì)話中有效，關(guān)機(jī)時(shí)數(shù)據(jù)被刷新并刪除。

l Federated

適用于分布式或數(shù)據(jù)集環(huán)境。Federated存儲(chǔ)引擎允許將多個(gè)數(shù)據(jù)庫(kù)服務(wù)器的表連接起來(lái)。它不移動(dòng)數(shù)據(jù)，也不要求遠(yuǎn)程表適用相同的存儲(chǔ)引擎。目前Federated存儲(chǔ)引擎在MySQL的大部分發(fā)行版中是禁用的。

l Archive

適用于存儲(chǔ)和檢索大量的很少訪問(wèn)的存檔或歷史數(shù)據(jù)。Archive存儲(chǔ)引擎以壓縮格式存儲(chǔ)大量數(shù)據(jù)，不支持索引，只能通過(guò)表掃描訪問(wèn)。

l MRG_MYISAM

適用于非常大的數(shù)據(jù)庫(kù)應(yīng)用，如數(shù)據(jù)倉(cāng)庫(kù)，其中數(shù)據(jù)存儲(chǔ)在一個(gè)或多個(gè)數(shù)據(jù)庫(kù)的多個(gè)表中。MRG_MYISAM存儲(chǔ)引擎的最優(yōu)的特點(diǎn)就是快速，它將一個(gè)大表分割成許多不同的小表，存儲(chǔ)在不同的磁盤(pán)上，把這些小表合并，然后同時(shí)訪問(wèn)它們，搜索和排序執(zhí)行得更快，因?yàn)槊總�(gè)小表需要管理的數(shù)據(jù)變少了。

缺點(diǎn)：

l 必須使用相同的MyISAM表組成一個(gè)合成表；

l 替換操作不可用；

l 索引比單表的索引效率低。

2 InnoDB及MyISAM性能對(duì)比

說(shuō)明：測(cè)試表中含有36個(gè)字段，并含有988218條記錄。

InnoDB存儲(chǔ)引擎的測(cè)試數(shù)據(jù)庫(kù)名為Innodbtest，其中含有該表，表名為Innodbtable；MyISAM存儲(chǔ)引擎的測(cè)試數(shù)據(jù)庫(kù)名為Myisamtest，其中含有該表，表名為Myisamtable。

在MySQL中分別使用InnoDB及MyISAM存儲(chǔ)引擎對(duì)該表進(jìn)行測(cè)試，首先進(jìn)行前期工作：

（1）將測(cè)試MyISAM存儲(chǔ)引擎的表的存儲(chǔ)引擎從默認(rèn)的InnoDB修改為MyISAM：

alter table myisamtable engine=myisam;

圖2.1 修改存儲(chǔ)引擎

（2）修改數(shù)據(jù)庫(kù)的字符編碼，將其設(shè)置為utf-8

alter database myisamtest character set utf8;
alter database innodbtest character set utf8;

圖2.2 修改InnoDB存儲(chǔ)引擎測(cè)試庫(kù)字符編碼

圖2.3 修改MyISAM存儲(chǔ)引擎測(cè)試庫(kù)字符編碼

對(duì)兩種存儲(chǔ)引擎的一些特性進(jìn)行測(cè)試：

l 存儲(chǔ)結(jié)構(gòu)

（1）InnoDB：

表數(shù)據(jù)都保存在一個(gè)大小為1.21GB的數(shù)據(jù)文件中——Innodbtable.ibd，與表相關(guān)的元數(shù)據(jù)信息存放在innodbtable.frm文件中，包括表結(jié)構(gòu)的定義信息。db.opt中定義了數(shù)據(jù)庫(kù)的一些定義信息。

圖2.4 InnoDB磁盤(pán)存儲(chǔ)目錄

圖2.5 db.opt文件內(nèi)容

（2）MyISAM：

.frm文件：存儲(chǔ)與表相關(guān)的元數(shù)據(jù)信息，包括表結(jié)構(gòu)的定義信息等；

.MYD文件：大小為853.34MB，存放MyISAM表的數(shù)據(jù)。

.MYI文件：大小為34.11MB，存放MyISAM表的索引相關(guān)信息。

db.opt：定義了數(shù)據(jù)庫(kù)的一些定義信息。

圖2.6 MyISAM磁盤(pán)存儲(chǔ)目錄

圖2.7 db.opt文件內(nèi)容

l select

（1）InnoDB：

圖2.8 InnoDB的select測(cè)試

（2）MyISAM：

圖2.9 MyISAM的select測(cè)試

l insert

（1）InnoDB：

圖2.10 InnoDB的insert測(cè)試

（2）MyISAM：

圖2.11 MyISAM的insert測(cè)試

l update

（1）InnoDB：

圖2.12 InnoDB的update測(cè)試

（2）MyISAM：

圖2.13 MyISAM的update測(cè)試

l delete

（1）InnoDB：

圖2.14 InnoDB的delete測(cè)試

（2）MyISAM：

圖2.15 MyISAM的delete測(cè)試

l delete where

（1）InnoDB：

圖2.16 InnoDB的delete where測(cè)試

（2）MyISAM：

圖2.17 MyISAM的delete where測(cè)試

l count without where

（1）InnoDB：

圖2.18 InnoDB的count without where測(cè)試

（2）MyISAM：

圖2.19 MyISAM的count without where測(cè)試

l group by

（1）InnoDB：

圖2.20 InnoDB的group by測(cè)試

（2）MyISAM：

圖2.21 MyISAM的group by測(cè)試

l 外鍵

創(chuàng)建一個(gè)新表，將測(cè)試表的主鍵作為新表的外鍵進(jìn)行測(cè)試：

create table `foreigntest`(
`id` int primary key not null,
`taskid` varchar(64) not null,
`host` varchar(128) not null default '',
`month` char(8) not null,
constraint `fk_task_h_m` foreign key (`taskid`,`host`,`month`)
references `innodbtable`(`taskid`,`host`,`month`)
) charset=utf8mb4

（1）InnoDB：

圖2.22 InnoDB的外鍵測(cè)試

（2）MyISAM：

圖2.23 MyISAM的外鍵測(cè)試

總結(jié)如下表：

附注：

[1]表空間：InnoDB用來(lái)組織與機(jī)器無(wú)關(guān)的文件的工具，包括數(shù)據(jù)、索引及回滾機(jī)制。默認(rèn)情況下，所有表共享一個(gè)表空間（稱(chēng)為共享表空間）。共享表空間不會(huì)自動(dòng)擴(kuò)展成多個(gè)文件。默認(rèn)情況下，一個(gè)表空間只占據(jù)單個(gè)文件，該文件隨數(shù)據(jù)增加而增長(zhǎng)。使用autoextend選項(xiàng)可以允許表空間創(chuàng)建新的文件。

[2]崩潰修復(fù)能力：InnoDB存儲(chǔ)引擎使用兩種基于磁盤(pán)的機(jī)制存儲(chǔ)數(shù)據(jù)，即日志文件和表空間。在關(guān)機(jī)或死機(jī)之前，InnoDB會(huì)使用這些日志來(lái)重建數(shù)據(jù)恢復(fù)。在程序啟動(dòng)時(shí)，InnoDB讀取日志并自動(dòng)將臟頁(yè)寫(xiě)入磁盤(pán)，從而在系統(tǒng)崩潰前恢復(fù)緩沖中的更新。

3 MyISAM和InnoDB的鎖對(duì)比

（1）表級(jí)鎖：開(kāi)銷(xiāo)小，加鎖快；不會(huì)出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。

（2）行級(jí)鎖：開(kāi)銷(xiāo)大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。

（3）對(duì)MyISAM表的讀操作，不會(huì)阻塞其他用戶對(duì)同一表的讀請(qǐng)求，但會(huì)阻塞對(duì)同一表的寫(xiě)請(qǐng)求；對(duì)MyISAM表的寫(xiě)操作，則會(huì)阻塞其他用戶對(duì)同一表的讀和寫(xiě)請(qǐng)求；MyISAM表的讀和寫(xiě)操作之間，以及寫(xiě)和寫(xiě)操作之間是串行的（當(dāng)一線程獲得對(duì)一個(gè)表的寫(xiě)鎖后，只有持有鎖的線程可以對(duì)表進(jìn)行更新操作。其他線程的讀、寫(xiě)操作都會(huì)等待，直到鎖被釋放為止。）

（4）共享鎖（s）：允許一個(gè)事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。

（5）排他鎖（X）：允許獲取排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)取得相同的數(shù)據(jù)集共享讀鎖和排他寫(xiě)鎖。

（6）對(duì)于UPDATE、DELETE和INSERT語(yǔ)句，InnoDB會(huì)自動(dòng)給涉及數(shù)據(jù)集加排他鎖（X）；對(duì)于普通SELECT語(yǔ)句，InnoDB不會(huì)加任何鎖。

4 兩種存儲(chǔ)引擎索引對(duì)比

l InnoDB：

l 在InnoDB中，表數(shù)據(jù)文件本身就是按B+Tree組織的一個(gè)索引結(jié)構(gòu)，這棵樹(shù)的葉節(jié)點(diǎn)data域保存了完整的數(shù)據(jù)記錄。這個(gè)索引的key是數(shù)據(jù)表的主鍵，因此InnoDB表數(shù)據(jù)文件本身就是主索引。

l InnoDB表使用的索引是聚集索引。聚集索引是一種數(shù)據(jù)結(jié)構(gòu)，不僅存儲(chǔ)索引，也存儲(chǔ)數(shù)據(jù)本身。因此，一旦定位到索引中的某個(gè)值，就可以直接檢索數(shù)據(jù)而無(wú)需額外的磁盤(pán)尋道。

l 主鍵索引或者表的第一個(gè)索引都采用聚集索引創(chuàng)建。

l InnoDB的所有輔助索引都引用主鍵作為data域。如果如果創(chuàng)建了輔助索引，聚集索引的關(guān)鍵字（主鍵、唯一鍵或行ID）也會(huì)存在輔助索引中，這樣可以快速按照關(guān)鍵字查找和快速獲取聚集索引中的原始數(shù)據(jù)。也就是如果使用主鍵列掃描輔助索引，則查詢只需要用輔助索引就可以獲取數(shù)據(jù)。

l MyISAM：

l 索引文件和數(shù)據(jù)文件是分離的，索引文件僅保存數(shù)據(jù)記錄的地址。使用B+樹(shù)作為索引結(jié)構(gòu)，葉節(jié)點(diǎn)的data域存放的是數(shù)據(jù)記錄的地址。

l 在MyISAM中，主索引和輔助索引（Secondary key）在結(jié)構(gòu)上沒(méi)有任何區(qū)別，只是主索引要求key是唯一的，而輔助索引的key可以重復(fù)。

l 主要區(qū)別：

l 主索引的區(qū)別：InnoDB的數(shù)據(jù)文件本身就是索引文件。而MyISAM的索引和數(shù)據(jù)是分開(kāi)的。

l 輔助索引的區(qū)別：InnoDB的輔助索引data域存儲(chǔ)相應(yīng)記錄主鍵的值而不是地址。而MyISAM的輔助索引和主索引沒(méi)有多大區(qū)別。

附注：

B+樹(shù)：對(duì)于一棵m階B+樹(shù)，具有如下特點(diǎn)：

l 有n棵子樹(shù)的節(jié)點(diǎn)中含有n個(gè)關(guān)鍵字。

l 所有的葉子結(jié)點(diǎn)中包含了全部關(guān)鍵字的信息，及指向含這些關(guān)鍵字記錄的指針。且葉子結(jié)點(diǎn)本身依照關(guān)鍵字的大小自小而大順序鏈接。

l 所有的非終端結(jié)點(diǎn)可以看成索引部分，結(jié)點(diǎn)中僅含其子樹(shù)（根結(jié)點(diǎn)）中的最大（或最�。╆P(guān)鍵字。

l 在B+樹(shù)，不管查找成功與否，每次查找都是走了一條從根到葉子結(jié)點(diǎn)的路徑。

l 樹(shù)中每個(gè)結(jié)點(diǎn)最多含有m棵子樹(shù)。

以上就是MySQL存儲(chǔ)引擎初探的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！