MySQL的存儲引擎是什么

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

MySQL存儲引擎的功能

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

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

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

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

MySQL的存儲引擎的分類

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

查看存儲引擎

show engines\G;
縱向查看

1.命令行修改引擎

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

2.配置文件修改

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

MYISAM和INNODB做個分析對比

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

訪問速度快

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

在磁盤上有三個文件

文件名和表名相同。但是擴展名不同：

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

MYISAM特點

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

MYISAM支持的存儲格式

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

INNODB支持的存儲格式

1.支持事務(wù)，支持4個事務(wù)的隔離級別。5.5之后是mysql的默認存儲引擎。

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

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

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

INNODB的特點

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

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

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

適用場景

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

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

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

三個文件

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

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

行鎖演示

要對一個非索引鍵進行操作，當一個事務(wù)對非索引列進行操作，因為要全表掃描過濾，所有整張表都會鎖定，另一個事務(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;

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

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

commit即可

表鎖演示

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

死鎖演示

行鎖如果使用不當會導(dǎo)致死鎖（死鎖一般是事務(wù)相互等待對方釋放資源，最后形成環(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ù)據(jù)庫會自動選擇一個事務(wù)作為受害者，然后先解除思索，再回滾事務(wù)。 

2、 MySQL的默認的死鎖機制，會選則一個事務(wù)作為思索的犧牲品。會直接終止其中一個事務(wù)，但是不會自動回滾。

如何僅可能避免死鎖

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

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

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

4.隔離級別，read commit 可以避免死鎖

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

排他鎖演示

總結(jié)

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

最新評論