1.引擎的介紹

Isam

該引擎在讀取數(shù)據(jù)方面速度很快，而且不占用大量的內(nèi)存和存儲資源；但是 Isam 不支持事務(wù)處理、不支持外鍵、不能夠容錯、也不支持索引。
該引擎在包括MySQL 5.1及其以上版本的數(shù)據(jù)庫中不再支持。

Berkeley：

該存儲引擎支持COMMIT和ROLLBACK等事務(wù)特性。
該引擎在包括MySQL 5.1及其以上版本的數(shù)據(jù)庫中不再支持。

CSV:

使用該引擎的MySQL數(shù)據(jù)庫表會在MySQL安裝目錄data文件夾中的和該表所在數(shù)據(jù)庫名相同的目錄中生成一個.CSV文件（所以，它可以將CSV類型的文件當(dāng)做表進(jìn)行處理），這種文件是一種普通文本文件，每個數(shù)據(jù)行占用一個文本行。該種類型的存儲引擎不支持索引，即使用該種類型的表沒有主鍵列；另外也不允許表中的字段為null。csv的編碼轉(zhuǎn)換需要格外注意。

場景：

這種引擎支持從數(shù)據(jù)庫中拷入/拷出CSV文件。如果從電子表格軟件輸出一個CSV文件，將其存放在MySQL服務(wù)器的數(shù)據(jù)目錄中，服務(wù)器就能夠馬上讀取相關(guān)的CSV文件。同樣，如果寫數(shù)據(jù)庫到一個CSV表，外部程序也可以立刻讀取它。在實現(xiàn)某種類型的日志記錄時，CSV表作為一種數(shù)據(jù)交換格式，特別有用。

HEAP（也稱為MEMORY）:

該存儲引擎通過在內(nèi)存中創(chuàng)建臨時表來存儲數(shù)據(jù)。每個基于該存儲引擎的表實際對應(yīng)一個磁盤文件，該文件的文件名和表名是相同的，類型為.frm。該磁盤文件只存儲表的結(jié)構(gòu)，而其數(shù)據(jù)存儲在內(nèi)存中，所以使用該種引擎的表擁有極高的插入、更新和查詢效率。這種存儲引擎默認(rèn)使用哈希（HASH）索引，其速度比使用B-+Tree型要快，但也可以使用B樹型索引。由于這種存儲引擎所存儲的數(shù)據(jù)保存在內(nèi)存中，所以其保存的數(shù)據(jù)具有不穩(wěn)定性，比如如果mysqld進(jìn)程發(fā)生異常、重啟或計算機關(guān)機等等都會造成這些數(shù)據(jù)的消失，所以這種存儲引擎中的表的生命周期很短，一般只使用一次。

場景：如果需要該數(shù)據(jù)庫中一個用于查詢的臨時表。

BLACKHOLE（黑洞引擎）:

該存儲引擎支持事務(wù)，而且支持mvcc的行級鎖，寫入這種引擎表中的任何數(shù)據(jù)都會消失，主要用于做日志記錄或同步歸檔的中繼存儲，這個存儲引擎除非有特別目的，否則不適合使用。

BLACKHOLE（黑洞引擎）:

場景1：

使用BLACKHOLE存儲引擎的表不存儲任何數(shù)據(jù)，但如果mysql啟用了二進(jìn)制日志，SQL語句被寫入日志（并被復(fù)制到從服務(wù)器）。這樣使用BLACKHOLE存儲引擎的mysqld可以作為主從復(fù)制中的中繼重復(fù)器或在其上面添加過濾器機制。例如,假設(shè)你的應(yīng)用需要從服務(wù)器側(cè)的過濾規(guī)則，但傳輸所有二進(jìn)制日志數(shù)據(jù)到從服務(wù)器會導(dǎo)致較大的網(wǎng)絡(luò)流量。在這種情況下，在主服務(wù)器主機上建立一個偽從服務(wù)器進(jìn)程。

image

場景2：

如果配置一主多從的話，多個從服務(wù)器會在主服務(wù)器上分別開啟自己相對應(yīng)的線程，執(zhí)行binlogdump命令而且多個此類進(jìn)程并不是共享的。為了避免因多個從服務(wù)器同時請求同樣的事件而導(dǎo)致主機資源耗盡，可以單獨建立一個偽的從服務(wù)器或者叫分發(fā)服務(wù)器。

image

ARCHIVE:

區(qū)別于InnoDB和MyISAM這兩種引擎，ARCHIVE提供了壓縮功能，擁有高效的插入速度，但是這種引擎不支持索引，所以查詢性能較差一些。 archive存儲引擎支持insert、replace和select操作，但是不支持update和delete。

場景1：存儲引擎基本上用于數(shù)據(jù)歸檔；它的壓縮比非常的高，存儲空間大概是innodb的10-15分之一所以它用來存儲歷史數(shù)據(jù)非常的適合，由于它不支持索引同時也不能緩存索引和數(shù)據(jù)，所以它不適合作為并發(fā)訪問表的存儲引擎。

場景2：由于高壓縮和快速插入的特點Archive非常適合作為日志表的存儲引擎，但是前提是不經(jīng)常對該表進(jìn)行查詢操作。

PERFORMANCE_SCHEMA：

該引擎主要用于收集數(shù)據(jù)庫服務(wù)器性能參數(shù)。這種引擎提供以下功能：提供進(jìn)程等待的詳細(xì)信息，包括鎖、互斥變量、文件信息；保存歷史的事件匯總信息，為提供MySQL服務(wù)器性能做出詳細(xì)的判斷；對于新增和刪除監(jiān)控事件點都非常容易，并可以隨意改變mysql服務(wù)器的監(jiān)控周期，例如（CYCLE、MICROSECOND）。 MySQL用戶是不能創(chuàng)建存儲引擎為PERFORMANCE_SCHEMA的表。

場景： DBA能夠較明細(xì)得了解性能降低可能是由于哪些瓶頸。

memory

出發(fā)點是速度 采用的邏輯存儲介質(zhì)是內(nèi)存

Merge

Merge允許將一組使用MyISAM存儲引擎的并且表結(jié)構(gòu)相同（即每張表的字段順序、字段名稱、字段類型、索引定義的順序及其定義的方式必須相同）的數(shù)據(jù)表合并為一個表，方便了數(shù)據(jù)的查詢。

場景：MySQL中沒有物化視圖，視圖的效率極低，故數(shù)據(jù)倉庫中數(shù)據(jù)量較大的每天、每周或者每個月都創(chuàng)建一個單一的表的歷史數(shù)據(jù)的集合可以通過Merge存儲引擎合并為一張表。

Federated

該存儲引擎可以不同的Mysql服務(wù)器聯(lián)合起來，邏輯上組成一個完整的數(shù)據(jù)庫。
這種存儲引擎非常適合數(shù)據(jù)庫分布式應(yīng)用。
Federated存儲引擎可以使你在本地數(shù)據(jù)庫中訪問遠(yuǎn)程數(shù)據(jù)庫中的數(shù)據(jù)，針對federated存儲引擎表的查詢會被發(fā)送到遠(yuǎn)程數(shù)據(jù)庫的表上執(zhí)行，本地是不存儲任何數(shù)據(jù)的。

場景： dblink。

image

缺點：

1.對本地虛擬表的結(jié)構(gòu)修改，并不會修改遠(yuǎn)程表的結(jié)構(gòu)

2.truncate 命令，會清除遠(yuǎn)程表數(shù)據(jù)

3. drop命令只會刪除虛擬表，并不會刪除遠(yuǎn)程表

4.不支持 alter table 命令

5. select count(*), select * from limit M, N 等語句執(zhí)行效率非常低，數(shù)據(jù)量較大時存在很嚴(yán)重的問題，但是按主鍵或索引列查詢，則很快，如以下查詢就非常慢（假設(shè) id 為主索引）

select id from db.tablea where id >100 limit 10 ;

而以下查詢就很快：

select id from db.tablea where id >100 and id<150

6. 如果虛擬虛擬表中字段未建立索引，而實體表中為此字段建立了索引，此種情況下，性能也相當(dāng)差。但是當(dāng)給虛擬表建立索引后，性能恢復(fù)正常。

7. 類似 where name like "str%" limit 1 的查詢，即使在 name 列上創(chuàng)建了索引，也會導(dǎo)致查詢過慢，是因為federated引擎會將所有滿足條件的記錄讀取到本，再進(jìn)行 limit 處理。

Cluster/NDB

該存儲引擎用于多臺數(shù)據(jù)機器聯(lián)合提供服務(wù)以提高整體性能和安全性。適合數(shù)據(jù)量大、安全和性能要求高的場景。

CAP理論。CAP理論(Brewer’s CAP Theorem) ，是說Consistency(一致性), Availability(可用性), Partition tolerance(分布) 三部分在系統(tǒng)實現(xiàn)只可同時滿足二點，沒法三者兼顧。如果對"一致性"要求高，且必需要做到"分區(qū)"，那么就要犧牲可用性;而對大型網(wǎng)站，可用性與分區(qū)容忍性優(yōu)先級要高于數(shù)據(jù)一致性，一般會盡量朝著 A、P 的方向設(shè)計，然后通過其它手段保證對于一致性的商務(wù)需求。

InnoDB

適用于更新密集型，支持事務(wù)，自動災(zāi)難恢復(fù)，行鎖，外鍵
該存儲引擎為MySQL表提供了ACID事務(wù)支持、系統(tǒng)崩潰修復(fù)能力和多版本并發(fā)控制（即MVCC Multi-Version Concurrency Control）的行鎖
支持自增長列（auto_increment）,自增長列的值不能為空，如果在使用的時候為空則自動從現(xiàn)有值開始增值，如果有但是比現(xiàn)在的還大，則直接保存這個值
支持外鍵（foreign key） ,外鍵所在的表稱為子表而所依賴的表稱為父表。
該引擎在5.5后的MySQL數(shù)據(jù)庫中為默認(rèn)存儲引擎。

MyISAM

不支持事務(wù)，適用于選擇密集型，插入密集型， mysql 默認(rèn)的引擎
該引擎基于ISAM，除了提供ISAM所沒有的索引和字段管理等大量功能
MyISAM還使用一種表鎖機制來優(yōu)化多個并發(fā)讀寫操作，但需要經(jīng)常運行OPTIMIZE TABLE命令，來恢復(fù)被更新機制所浪費的空間，否則碎片也會隨之增加，最終影響數(shù)據(jù)訪問性能。
還有一些有用的擴展，例如用來修復(fù)數(shù)據(jù)庫文件的MyISAM Chk工具和用來恢復(fù)浪費空間的 MyISAM Pack工具
MyISAM強調(diào)了快速讀取操作，主要用于高負(fù)載的select，這可能也是MySQL深受Web開發(fā)的主要原因：在Web開發(fā)中進(jìn)行的大量數(shù)據(jù)操作都是讀，所以大多數(shù)虛擬主機提供商和Internet平臺提供商（Internet Presence Provider，IPP）只允許使用MyISAM格式。

MyISAM類型的表支持三種不同的存儲結(jié)構(gòu)：靜態(tài)型、動態(tài)型、壓縮型。

• 靜態(tài)表(默認(rèn)的存儲格式) 表中的字段都是非變長字段，這樣每個記錄都是固定長度的，這樣存儲
  • 優(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ù)非常小空間，因為每條記錄都是被單獨壓縮，所以只有非常小的訪問開支

第三方存儲引擎：

Infobright

mysql的列存儲引擎，適用于數(shù)據(jù)分析和數(shù)據(jù)倉庫設(shè)計。

優(yōu)點:

1.查詢性能高 --比普通Mysql 數(shù)據(jù)庫引擎(MyISAM、InnoDB) 快5-60倍.

2.存儲數(shù)據(jù)量大 --能存儲的數(shù)據(jù)量特別大.

3.高壓縮比 --與普通數(shù)據(jù)庫存放的數(shù)據(jù)文件相比, 可以達(dá)到55:1

4.不需要建立索引 --省去了大量建立索引的時間.(對于我們非常有優(yōu)勢)

缺點:

1.不能高并發(fā).最多10個并發(fā)

2.Infobright分兩個版本:社區(qū)版(ICE,免費)、企業(yè)版(IEE,收費),社區(qū)版在添加數(shù)據(jù)時,只支持loaddata , 而不支持.insert,update ,delete . 企業(yè)版,則全部支持.

TokuDB

支持?jǐn)?shù)據(jù)壓縮，支持高速寫入的一個引擎，但是不適合update多的場景。

XtraDB、PBXT

是Percona公司基于InnoDB的一個改進(jìn)版本

2.常用兩種引擎的選擇

MyISAM與InnoDB

InnoDB和MyISAM是許多人在使用MySQL時最常用的兩個表類型，這兩個表類型各有優(yōu)劣，視具體應(yīng)用而定。

基本的差別為： MyISAM類型不支持事務(wù)處理等高級處理，而InnoDB類型支持。MyISAM類型的表強調(diào)的是性能，其執(zhí)行數(shù)度比InnoDB類型更快，但是不提供事務(wù)支持，而InnoDB提供事務(wù)支持以及外部鍵等高級數(shù)據(jù)庫功能。

所以從宏觀來講，事務(wù)數(shù)據(jù)庫關(guān)注細(xì)節(jié)，而數(shù)據(jù)倉庫關(guān)注高層次的聚集，所以，InnoDB更適合作為線上的事務(wù)處理，而MyISAM更適合作為ROLAP型數(shù)據(jù)倉庫。

InnoDB引擎適合線上事物型數(shù)據(jù)庫：

1.InnoDB引擎表是基于B+樹的索引組織表(IOT)；

2.每個表都需要有一個聚集索引(clustered index)；

3.所有的行記錄都存儲在B+樹的葉子節(jié)點(leaf pages of the tree)；

4.基于聚集索引的增、刪、改、查的效率相對是最高的；

5.如果我們定義了主鍵(PRIMARY KEY)，那么InnoDB會選擇器作為聚集索引；

6.如果沒有顯式定義主鍵，則InnoDB會選擇第一個不包含有NULL值的唯一索引作為主鍵索引；

7.如果也沒有這樣的唯一索引，則InnoDB會選擇內(nèi)置6字節(jié)長的ROWID作為隱含的聚集索引(ROWID隨著行記錄的寫入而主鍵遞增，這個ROWID不像ORACLE的ROWID那樣可引用，是隱含的)。

MYISAM引擎適用于ROLAP數(shù)據(jù)倉庫：

1.讀取效率：數(shù)據(jù)倉庫的高并發(fā)上承載的大部分是讀， MYISAM強調(diào)的是性能，每次查詢具有原子性,其執(zhí)行數(shù)度比InnoDB類型更快。

2. 存儲空間：MyISAM： MyISAM的索引和數(shù)據(jù)是分開的，并且索引是有壓縮的，內(nèi)存使用率就對應(yīng)提高了不少。InnoDB：需要更多的內(nèi)存和存儲，它會在主內(nèi)存中建立其專用的緩沖池用于高速緩沖數(shù)據(jù)和索引。

3. MyISAM可移植性備份及恢復(fù)：MyISAM：數(shù)據(jù)是以文件的形式存儲，所以在跨平臺的數(shù)據(jù)轉(zhuǎn)移中會很方便。在備份和恢復(fù)時可單獨針對某個表進(jìn)行操作。InnoDB：免費的方案可以是拷貝數(shù)據(jù)文件、備份 binlog，或者用 mysqldump，在數(shù)據(jù)量達(dá)到幾十G的時候就相對痛苦了。移植過程中MyISAM不受字典數(shù)據(jù)的影響。

4.從接觸的應(yīng)用邏輯來說，select count(*) 和order by 是最頻繁的，大概能占了整個sql總語句的60%以上的操作，而這種操作Innodb其實也是會鎖表的，很多人以為Innodb是行級鎖，那個只是where對它主鍵是有效，非主鍵的都會鎖全表的。但MYISAM對于count操作只需要在元數(shù)據(jù)中讀取，不用掃表。

5.如果和MyISAM比insert寫操作的話，Innodb還達(dá)不到MyISAM的寫性能，如果是針對基于索引的update操作，雖然MyISAM可能會遜色I(xiàn)nnodb,但是那么高并發(fā)的寫，從庫能否追的上也是一個問題，且不建議數(shù)據(jù)倉庫中頻繁update數(shù)據(jù)。

6.如果是用MyISAM的話，merge引擎可以大大加快數(shù)據(jù)倉庫開發(fā)速度，非常適合大項目總量約幾億的rows某一類型(如日志，調(diào)查統(tǒng)計)的業(yè)務(wù)表。

7.全文索引：MyISAM：支持 FULLTEXT類型的全文索引。InnoDB：不支持FULLTEXT類型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

8.表主鍵：MyISAM：允許沒有任何索引和主鍵的表存在，索引都是保存行的地址。InnoDB：如果沒有設(shè)定主鍵或者非空唯一索引，就會自動生成一個6字節(jié)的主鍵(用戶不可見)，數(shù)據(jù)是主索引的一部分，附加索引保存的是主索引的值。

9.對于AUTO_INCREMENT類型的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中，可以和其他字段一起建立聯(lián)合索引。

10. MyISAM不支持外鍵，需通過其他方式彌補。

根據(jù)引擎特性的優(yōu)化

如何對InnoDB引擎的表做最優(yōu)的優(yōu)化：

1.使用自增列(INT/BIGINT類型)做主鍵，這時候?qū)懭腠樞蚴亲栽龅?，和B+數(shù)葉子節(jié)點分裂順序一致，這時候存取效率是最高的

2.該表不指定自增列做主鍵，同時也沒有可以被選為主鍵的唯一索引(上面的條件)，這時候InnoDB會選擇內(nèi)置的ROWID作為主鍵，寫入順序和ROWID增長順序一致

ps:多出引用，不一一標(biāo)注。

本文由博客一文多發(fā)平臺 OpenWrite 發(fā)布！