一、存儲引擎概念介紹

• MySQL中的數(shù)據(jù)用各種不同的技術(shù)存儲在文件中，每一種技術(shù)都使用不同的存儲機(jī)制、索引技巧、鎖定水平并最終提供不同的功能和能力，這些不同的技術(shù)以及配套的功能在MySQL中稱為存儲引擎
• 存儲引擎是MySQL將數(shù)據(jù)存儲在文件系統(tǒng)中的存儲方式或者存儲格式
• MySQL數(shù)據(jù)庫中的組件，負(fù)責(zé)執(zhí)行實(shí)際的數(shù)據(jù)I/O操作
• MySQL系統(tǒng)中，存儲引擎處于文件系統(tǒng)之上，在數(shù)據(jù)保存到數(shù)據(jù)文件之前會傳輸?shù)酱鎯σ?，之后按照各個存儲引擎的存儲格式進(jìn)行存儲

二、MySQL常用的存儲引擎

• 常用兩種存儲引擎：MyISAM和InnoDB

1、 MyISAM

1.1 MylSAM的特點(diǎn)

MyISAM 適合查詢、插入為主的應(yīng)用場景

• MyISAM不支持事務(wù)，也不支持外鍵約束，只支持全文索引，數(shù)據(jù)文件和索引文件是分開保存的
• 訪問速度快，對事務(wù)完整性沒有要求
• MyISAM在磁盤上存儲成三個文件，文件名和表名都相同，但是擴(kuò)展名分別為：

• .frm 文件存儲表結(jié)構(gòu)的定義
• 數(shù)據(jù)文件的擴(kuò)展名為 .MYD (MYData)
• 索引文件的擴(kuò)展名是 .MYI (MYIndex)

• 表級鎖定形式，數(shù)據(jù)在更新時鎖定整個表
• 數(shù)據(jù)庫在讀寫過程中相互阻塞:

串行操作，按照順序操作，每次在讀或?qū)懙臅r候會把全表鎖起來

• 會在數(shù)據(jù)寫入的過程阻塞用戶數(shù)據(jù)的讀取
• 也會在數(shù)據(jù)讀取的過程中阻塞用戶的數(shù)據(jù)寫入

特性：數(shù)據(jù)單獨(dú)寫入或讀取，速度過程較快且占用資源相對少

MyIsam 是表級鎖定，讀或?qū)憻o法同時進(jìn)行,但好處是：分開執(zhí)行時，速度快、資源占用相對較少（相對） 

1.2 MyISAM 表支持 3 種不同的存儲格式：

（1）靜態(tài)(固定長度)表

• 靜態(tài)表是默認(rèn)的存儲格式。靜態(tài)表中的字段都是非可變字段，這樣每個記錄都是固定長度的，
• 優(yōu)點(diǎn)是存儲非常迅速，容易緩存，出現(xiàn)故障容易恢復(fù)；
• 缺點(diǎn)是占用的空間通常比動態(tài)表多。

（2）動態(tài)表

• 動態(tài)表包含可變字段（varchar），記錄不是固定長度的
• 優(yōu)點(diǎn)是占用空間較少
• 缺點(diǎn)是頻繁的更新、刪除記錄會產(chǎn)生碎片，需要定期執(zhí)行 OPTIMIZE TABLE 語句或 myisamchk -r 命令來改善性能，并且出現(xiàn)故障的時候恢復(fù)相對比較困難。

（3）壓縮表 

• 壓縮表由 myisamchk 工具創(chuàng)建，占據(jù)非常小的空間，因?yàn)槊織l記錄都是被單獨(dú)壓縮的，所以只有非常小的訪問開支。

1.3 MyISAM適用的生產(chǎn)場景

• 公司業(yè)務(wù)不需要事務(wù)的支持 
• 單方面讀取或?qū)懭霐?shù)據(jù)比較多的業(yè)務(wù)
• MyISAM存儲引擎數(shù)據(jù)讀寫都比較頻繁場景不適合
• 使用讀寫并發(fā)訪問相對較低的業(yè)務(wù)
• 數(shù)據(jù)修改相對較少的業(yè)務(wù)
• 對數(shù)據(jù)業(yè)務(wù)一致性要求不是非常高的業(yè)務(wù)
• 服務(wù)器硬件資源相對比較差

MyIsam：適合于單方向的任務(wù)場景、同時并發(fā)量不高、對于事務(wù)要求不高的場景

2、 InnoDB

2.1 InnoDB特點(diǎn)

• 支持事務(wù)，支持4個事務(wù)隔離級別（數(shù)據(jù)不一致問題）
• 讀寫阻塞與事務(wù)隔離級別相關(guān)
• 能非常高效的緩存索引和數(shù)據(jù)
• 表與主鍵以簇的方式存儲
• 支持分區(qū)、表空間，類似oracle數(shù)據(jù)庫
• 支持外鍵約束，5.5前不支持全文索引，5.5后支持全文索引
• 對硬件資源要求還是比較高的場合
• 行級鎖定，但是全表掃描仍然會是表級鎖定（select ）

MySQL從5.5.5版本開始，默認(rèn)的存儲引擎為 InnoDB，5.5 之前是默認(rèn)myisam （isam）

2.2 InnoDB適用生產(chǎn)場景分析

• 業(yè)務(wù)需要事務(wù)的支持

• 行級鎖定對高并發(fā)有很好的適應(yīng)能力，但需確保查詢是通過索引來完成

• 業(yè)務(wù)數(shù)據(jù)更新較為頻繁的場景（如：論壇，微博等）

• 業(yè)務(wù)數(shù)據(jù)一致性要求較高（如：銀行業(yè)務(wù)）

• 硬件設(shè)備內(nèi)存較大，利用Innodb較好的緩存能力來提高內(nèi)存利用率，減少磁盤I0的壓力

三、MyISAM與InnoDB區(qū)別

1、 統(tǒng)計表行數(shù)時

• InnoDB 中不保存表的行數(shù)，如 select count( ) from table; 時，InnoDB 需要掃描一遍整個表來計算有多少行
• MyISAM 只要簡單的讀出保存好的行數(shù)即可。需要注意的是，當(dāng) count( )語句包含 where 條件時 MyISAM 也需要掃描整個表

2、 建立索引時

• 對于自增長的字段，InnoDB 中必須包含只有該字段的索引
• 但是在 MyISAM 表中可以和其他字段一起建立組合索引

3 、刪除表時

• 清空整個表時，InnoDB 是一行一行的刪除，效率非常慢。
• MyISAM 則會重建表（truncate）

四、企業(yè)選擇存儲引擎依據(jù)

1、 需要考慮每個存儲引擎提供了哪些不同的核心功能及應(yīng)用場景

2、支持的字段和數(shù)據(jù)類型

• 所有引擎都支持通用的數(shù)據(jù)類型
• 但不是所有的引擎都支持其它的字段類型，如二進(jìn)制對象

3、鎖定類型:不同的存儲引擎支持不同級別的鎖定

3.1 表鎖定: MyISAM 支持

• 開銷小，加鎖快
• 不會出現(xiàn)死鎖
• 鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低

3.2 行鎖定: InnoDB 支持

• 開銷大，加鎖慢
• 會出現(xiàn)死鎖
• 鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高

4、索引的支持

• 建立索引在搜索和恢復(fù)數(shù)據(jù)庫中的數(shù)據(jù)時能顯著提高性能
• 不同的存儲引擎提供不同的制作索引的技術(shù)
• 有些存儲引擎根本不支持索引

5、 事務(wù)處理的支持

• 提高在向表中更新和插入信息期間的可靠性
• 可根據(jù)企業(yè)業(yè)務(wù)是否要支持事務(wù)選擇存儲引擎

五、存儲引擎管理

1、查看系統(tǒng)支持的存儲引擎

show engines;
#查看存儲引擎

show variables like '%storage_engine%';
#查看當(dāng)前默認(rèn)的存儲引擎

2、查看表使用的存儲引擎

2.1 方法一

show table status from 庫名 where name='表名'\G；

2.2 方法二

use 庫名;
show create table 表名;

3、修改存儲引擎

3.1 方法一：通過 alter table 修改

use 庫名;
#切換數(shù)據(jù)庫

alter table 表名 engine = 存儲引擎類型;
#修改存儲引擎

3.2 方法二：通過修改 /etc/my.cnf 配置文件，指定默認(rèn)存儲引擎并重啟服務(wù)

配置文件 /etc/my.cnf 修改完后，需要重啟服務(wù)

#修改配置文件
vim /etc/my.cnf
[mysqld]
default-storage-engine=INNODB
#添加此行配置內(nèi)容

systemctl restart mysqld.service
#修改完重啟mysql服務(wù)


#注意：此方法只對修改了配置文件并重啟mysql服務(wù)后新創(chuàng)建的表有效，已經(jīng)存在的表不會有變更。

3.3 方法三：通過 create table 創(chuàng)建表時指定存儲引擎

use 庫名;
create table 表名(字段1 數(shù)據(jù)類型,...) engine=MyISAM;

到此這篇關(guān)于一文詳解MySQL 存儲引擎的文章就介紹到這了,更多相關(guān)MySQL 存儲引擎內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！ 

最新評論