MySQL 常用數(shù)據(jù)存儲引擎區(qū)別 | Laravel China 社區(qū)

caodaoquan 2017-08-02

展開全文

mysql有多種存儲引擎，目前常用的是 MyISAM 和 InnoDB 這兩個引擎，除了這兩個引擎以為還有許多其他引擎，有官方的，也有一些公司自己研發(fā)的。這篇文章主要簡單概述一下常用常見的 MySQL 引擎，一則這是面試中常被問到的問題，二則這也是數(shù)據(jù)庫設(shè)計中不可忽略的問題，用合適的引擎可以更好的適應(yīng)業(yè)務(wù)場景，提高業(yè)務(wù)效率。

MyISAM#

MyISAM 是 mysql 5.5.5 之前的默認引擎，它支持 B-tree/FullText/R-tree 索引類型。

鎖級別為表鎖，表鎖優(yōu)點是開銷小，加鎖快；缺點是鎖粒度大，發(fā)生鎖沖動概率較高，容納并發(fā)能力低，這個引擎適合查詢?yōu)橹鞯臉I(yè)務(wù)。

此引擎不支持事務(wù)，也不支持外鍵。

MyISAM強調(diào)了快速讀取操作。它存儲表的行數(shù)，于是SELECT COUNT(*) FROM TABLE時只需要直接讀取已經(jīng)保存好的值而不需要進行全表掃描。

InnoDB#

InnoDB 存儲引擎最大的亮點就是支持事務(wù)，支持回滾，它支持 Hash/B-tree 索引類型。

鎖級別為行鎖，行鎖優(yōu)點是適用于高并發(fā)的頻繁表修改，高并發(fā)是性能優(yōu)于 MyISAM。缺點是系統(tǒng)消耗較大，索引不僅緩存自身，也緩存數(shù)據(jù)，相比 MyISAM 需要更大的內(nèi)存。

InnoDB 中不保存表的具體行數(shù)，也就是說，執(zhí)行 select count(*) from table時，InnoDB 要掃描一遍整個表來計算有多少行。

支持事務(wù)，支持外鍵。

ACID 事務(wù)#

A 事務(wù)的原子性(Atomicity)：指一個事務(wù)要么全部執(zhí)行，要么不執(zhí)行。也就是說一個事務(wù)不可能只執(zhí)行了一半就停止了。比如你從取款機取錢，這個事務(wù)可以分成兩個步驟：1）劃卡，2）出錢。不可能劃了卡,而錢卻沒出來，這兩步必須同時完成，要么就不完成。
C 事務(wù)的一致性(Consistency)：指事務(wù)的運行并不改變數(shù)據(jù)庫中數(shù)據(jù)的一致性。例如，完整性約束了a+b=10，一個事務(wù)改變了a，那么b也應(yīng)該隨之改變。
I 獨立性(Isolation）：事務(wù)的獨立性也有稱作隔離性，是指兩個以上的事務(wù)不會出現(xiàn)交錯執(zhí)行的狀態(tài)。因為這樣可能會導(dǎo)致數(shù)據(jù)不一致。
D 持久性(Durability）：事務(wù)的持久性是指事務(wù)執(zhí)行成功以后，該事務(wù)所對數(shù)據(jù)庫所作的更改便是持久的保存在數(shù)據(jù)庫之中，不會無緣無故的回滾。

Memory#

Memory 是內(nèi)存級別存儲引擎，數(shù)據(jù)存儲在內(nèi)存中，所以他能夠存儲的數(shù)據(jù)量較小。

因為內(nèi)存的特性，存儲引擎對數(shù)據(jù)的一致性支持較差。鎖級別為表鎖，不支持事務(wù)。但訪問速度非常快，并且默認使用 hash 索引。

Memory存儲引擎使用存在內(nèi)存中的內(nèi)容來創(chuàng)建表，每個Memory表只實際對應(yīng)一個磁盤文件，在磁盤中表現(xiàn)為.frm文件。

總結(jié)#

	MyISAM	InnoDB
存儲結(jié)構(gòu)	每張表被存放在三個文件：frm-格定義MYD(MYData)-數(shù)據(jù)文件MYI(MYIndex)-索引文件	所有的表都保存在同一個數(shù)據(jù)文件中（也可能是多個文件，或者是獨立的表空間文件），InnoDB表的大小只受限于操作系統(tǒng)文件的大小，一般為2GB
存儲空間	MyISAM可被壓縮，存儲空間較小	InnoDB的表需要更多的內(nèi)存和存儲，它會在主內(nèi)存中建立其專用的緩沖池用于高速緩沖數(shù)據(jù)和索引
可移植性、備份及恢復(fù)	由于MyISAM的數(shù)據(jù)是以文件的形式存儲，所以在跨平臺的數(shù)據(jù)轉(zhuǎn)移中會很方便。在備份和恢復(fù)時可單獨針對某個表進行操作	免費的方案可以是拷貝數(shù)據(jù)文件、備份 binlog，或者用 mysqldump，在數(shù)據(jù)量達到幾十G的時候就相對痛苦了
事務(wù)安全	不支持每次查詢具有原子性	支持具有事務(wù)(commit)、回滾(rollback)和崩潰修復(fù)能力(crash recovery capabilities)的事務(wù)安全(transaction-safe (ACID compliant))型表
AUTO_INCREMENT	MyISAM表可以和其他字段一起建立聯(lián)合索引	InnoDB中必須包含只有該字段的索引
SELECT	MyISAM更優(yōu)
INSERT		InnoDB更優(yōu)
UPDATE		InnoDB更優(yōu)
DELETE		InnoDB更優(yōu) 它不會重新建立表，而是一行一行的刪除
COUNT without WHERE	MyISAM更優(yōu)。因為MyISAM保存了表的具體行數(shù)	InnoDB沒有保存表的具體行數(shù)，需要逐行掃描統(tǒng)計，就很慢了
COUNT with WHERE	一樣	一樣，InnoDB也會鎖表
鎖	只支持表鎖	支持表鎖、行鎖行鎖大幅度提高了多用戶并發(fā)操作的新能。但是InnoDB的行鎖，只是在WHERE的主鍵是有效的，非主鍵的WHERE都會鎖全表的
外鍵	不支持	支持
FULLTEXT全文索引	支持	不支持（5.6.4以上支持英文全文索引）可以通過使用Sphinx從InnoDB中獲得全文索引，會慢一點

互聯(lián)網(wǎng)項目中隨著硬件成本的降低及緩存、中間件的應(yīng)用，一般我們選擇都以 InnoDB 存儲引擎為主，很少再去選擇 MyISAM 了。而業(yè)務(wù)真發(fā)展的一定程度時，自帶的存儲引擎無法滿足時，這時公司應(yīng)該是有實力去自主研發(fā)滿足自己需求的存儲引擎或者購買商用的存儲引擎了。