一、存儲(chǔ)引擎概念介紹

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

二、MySQL常用的存儲(chǔ)引擎

• 常用兩種存儲(chǔ)引擎：MyISAM和InnoDB

1、 MyISAM

1.1 MylSAM的特點(diǎn)

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

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

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

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

串行操作，按照順序操作，每次在讀或?qū)懙臅r(shí)候會(huì)把全表鎖起來(lái)

• 會(huì)在數(shù)據(jù)寫(xiě)入的過(guò)程阻塞用戶數(shù)據(jù)的讀取
• 也會(huì)在數(shù)據(jù)讀取的過(guò)程中阻塞用戶的數(shù)據(jù)寫(xiě)入

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

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

1.2 MyISAM 表支持 3 種不同的存儲(chǔ)格式：

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

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

（2）動(dòng)態(tài)表

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

（3）壓縮表 

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

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

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

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

2、 InnoDB

2.1 InnoDB特點(diǎn)

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

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

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

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

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

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

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

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

三、MyISAM與InnoDB區(qū)別

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

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

2、 建立索引時(shí)

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

3 、刪除表時(shí)

• 清空整個(gè)表時(shí)，InnoDB 是一行一行的刪除，效率非常慢。
• MyISAM 則會(huì)重建表（truncate）

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

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

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

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

3、鎖定類型:不同的存儲(chǔ)引擎支持不同級(jí)別的鎖定

3.1 表鎖定: MyISAM 支持

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

3.2 行鎖定: InnoDB 支持

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

4、索引的支持

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

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

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

五、存儲(chǔ)引擎管理

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

show engines;
#查看存儲(chǔ)引擎

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

2、查看表使用的存儲(chǔ)引擎

2.1 方法一

show table status from 庫(kù)名 where name='表名'\G；

2.2 方法二

use 庫(kù)名;
show create table 表名;

3、修改存儲(chǔ)引擎

3.1 方法一：通過(guò) alter table 修改

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

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

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

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

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

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


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

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

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

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

最新評(píng)論