MySQL的存儲(chǔ)引擎是什么

MySQL當(dāng)中數(shù)據(jù)用各種不同的技術(shù)存儲(chǔ)在文件中，每一種技術(shù)都使用不同的存儲(chǔ)機(jī)制，索引技巧 鎖定水平，以及最終提供的不同的功能和能力，這些就是我們說的存儲(chǔ)引擎。

MySQL存儲(chǔ)引擎的功能

1.MySQL將數(shù)據(jù)存儲(chǔ)在文件系統(tǒng)中的一種格式和方式

2.存儲(chǔ)引擎負(fù)責(zé)執(zhí)行實(shí)際的數(shù)據(jù)I/O操作。

3.存儲(chǔ)數(shù)據(jù)引擎介于數(shù)據(jù)和文件系統(tǒng)之間，數(shù)據(jù)會(huì)先保存到存儲(chǔ)引擎，再按照存儲(chǔ)引擎的格式保存到文件系統(tǒng)中

即：如何把數(shù)據(jù)保存到文件系統(tǒng)中

MySQL的存儲(chǔ)引擎的分類

• 1.INNODB：5.5之后 MySQL的默認(rèn)存儲(chǔ)引擎。事務(wù)性速記引擎。支持ACID事務(wù)。支持行鎖，鎖表。寫入和查詢性能比較好。
• 2.MYISAM：5.5之前的默認(rèn)存儲(chǔ)引擎。插入數(shù)據(jù)性能較高，查詢速度也很優(yōu)秀。但不支持事務(wù)
• 3.memory引擎：所有數(shù)據(jù)都保存在內(nèi)存的存儲(chǔ)引擎，但是服務(wù)一旦重啟，全部丟失。插入數(shù)據(jù)，更新，查詢數(shù)據(jù)，速度比較快。但是占用內(nèi)存空間比較大。會(huì)占用和數(shù)據(jù)量正比的內(nèi)存空間
• 4.csv：由逗號(hào)分隔數(shù)據(jù)的存儲(chǔ)引擎。他會(huì)在數(shù)據(jù)庫(kù)子目錄里為每一個(gè)數(shù)據(jù)表創(chuàng)建一個(gè) .csv的文件。就是一個(gè)普通的文本文件。每個(gè)數(shù)據(jù)行占用一個(gè)文本行。但是 csv不支持索引。
• 5.Archive：非常適合存儲(chǔ)大量的獨(dú)立的，歷史數(shù)據(jù)的引擎。不需要被經(jīng)常讀取。插入的速度很快。查詢的效率比較低。
• 6.blackhole：黑洞引擎，寫入的任何數(shù)據(jù)都會(huì)消失。

查看存儲(chǔ)引擎

show engines\G;
縱向查看

1.命令行修改引擎

 alter table hj engine=myisam;
 命令行修改

2.配置文件修改

vim /etc/my.cnf
配置文件修改

MYISAM和INNODB做個(gè)分析對(duì)比

MYISAM：不支持事務(wù)，也不支持外鍵，只支持全文索引，數(shù)據(jù)文件和索引文件是分開的。

訪問速度快

使用場(chǎng)景：查詢和插入數(shù)據(jù)為主的應(yīng)用。

在磁盤上有三個(gè)文件

文件名和表名相同。但是擴(kuò)展名不同：

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

MYISAM特點(diǎn)

• 1.更新數(shù)據(jù)時(shí)，整個(gè)表都會(huì)鎖定。
• 2.數(shù)據(jù)庫(kù)在讀寫過程中相互阻塞。

MYISAM支持的存儲(chǔ)格式

• 1.靜態(tài)表，固定長(zhǎng)度表，靜態(tài)表myisam的默認(rèn)存儲(chǔ)格式。靜態(tài)表中字段都是非可變字段。每個(gè)記錄都是固定長(zhǎng)度的。存儲(chǔ)快，方便緩存，有了故障容易恢復(fù)。缺點(diǎn)是占用空間比較多。
• 2.動(dòng)態(tài)表，可以包含可變字段，記錄的長(zhǎng)度是不固定的。優(yōu)點(diǎn)是占用空間比較少。頻繁更新數(shù)據(jù)，刪除記錄，會(huì)產(chǎn)生碎片。需要定期清理。myisamchk -r。出現(xiàn)故障恢復(fù)比較困難。
• 3.壓縮表，myisamchk工具創(chuàng)建，占據(jù)空間非常小。每條記錄都是單獨(dú)壓縮。

INNODB支持的存儲(chǔ)格式

1.支持事務(wù)，支持4個(gè)事務(wù)的隔離級(jí)別。5.5之后是mysql的默認(rèn)存儲(chǔ)引擎。

• 讀寫阻塞和隔離級(jí)別相關(guān)
• 支持高效的緩存索引以及緩存數(shù)據(jù)。
• 表于主鍵以簇方式存儲(chǔ)BTREE。
• 支持外鍵約束，5.5之后INNODB也可以支持全文索引。
• 硬件資源的要求比較高。

支持行鎖定，也可以支持表鎖定（全表掃描）

• 1.使用like模糊查詢，會(huì)進(jìn)行全表掃描，鎖定整個(gè)表
• 2.對(duì)沒有創(chuàng)建索引的字段進(jìn)行增，刪，改，也會(huì)進(jìn)行全表掃描，鎖定整個(gè)表。
• 3.使用索引，進(jìn)行增 刪 改 ，則是行級(jí)鎖定。

INNODB的特點(diǎn)

1.不保存表的行數(shù)，統(tǒng)計(jì)表的行數(shù)會(huì)掃描一遍整個(gè)表來計(jì)算有多少行。

2.自增長(zhǎng)字段必須有索引，INNODB中必須包含只有該字段的索引

3.delete清空表，一行一行刪，速度比較慢，推薦 truncate

適用場(chǎng)景

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

2.論壇，微博，對(duì)數(shù)據(jù)一致性比較高的場(chǎng)景

3.訪問量和并發(fā)比較高的場(chǎng)景，innodb支持緩存，減少后臺(tái)服務(wù)器的壓力。

三個(gè)文件

• 表名 .frm （表結(jié)構(gòu)文件）
• 表名 .idb（既是數(shù)據(jù)文件，又是索引名）
• dp.opt：表的屬性文件

INNODB行鎖和索引的關(guān)系 以及表鎖 排他鎖 死鎖

行鎖演示

要對(duì)一個(gè)非索引鍵進(jìn)行操作，當(dāng)一個(gè)事務(wù)對(duì)非索引列進(jìn)行操作，因?yàn)橐頀呙柽^濾，所有整張表都會(huì)鎖定，另一個(gè)事務(wù)只能查。

create table test (
id int(4) PRIMARY KEY,
name varchar(10),
age varchar(3)
);
alter table test add index name_index (name);
show index from test;

此時(shí)  右側(cè)的命令無法執(zhí)行，只到命令行執(zhí)行commit才可運(yùn)行（時(shí)間15.9秒可以看出來commit執(zhí)行之后才將行鎖取消）

此時(shí)使用name，普通索引，會(huì)鎖住索引行，緊接著對(duì)應(yīng)的主鍵，一并鎖定，就是把那一行鎖住

commit即可

表鎖演示

刪除age=2（沒有設(shè)置索引），事務(wù)B（右側(cè)）所有為age的條件都無法更新數(shù)據(jù)，該表現(xiàn)為表鎖

死鎖演示

行鎖如果使用不當(dāng)會(huì)導(dǎo)致死鎖（死鎖一般是事務(wù)相互等待對(duì)方釋放資源，最后形成環(huán)路造成的）

在終端執(zhí)行
begin;
delete from test1 where id =4;
在外部
begin;
select * from test1 where id =5 for update;
再回到終端執(zhí)行
delete from test1 where id =5;

1、 發(fā)生死鎖的時(shí)候，數(shù)據(jù)庫(kù)會(huì)自動(dòng)選擇一個(gè)事務(wù)作為受害者，然后先解除思索，再回滾事務(wù)。 

2、 MySQL的默認(rèn)的死鎖機(jī)制，會(huì)選則一個(gè)事務(wù)作為思索的犧牲品。會(huì)直接終止其中一個(gè)事務(wù)，但是不會(huì)自動(dòng)回滾。

如何僅可能避免死鎖

1.業(yè)務(wù)邏輯要合理，以固定的順序訪問表和行

2.如果事務(wù)的類型比較復(fù)雜，要進(jìn)行拆分，在業(yè)務(wù)允許的情況下，把大事務(wù)拆小

3.在同一事務(wù)中，盡可能一次性鎖定所有需要的資源?？梢詼p少需要的資源，可以減少死鎖的概率

4.隔離級(jí)別，read commit 可以避免死鎖

5.添加合理的索引，可以減少死鎖的概率。

排他鎖演示

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。