1、存儲(chǔ)引擎概念

• MySQL中的數(shù)據(jù)用各種不同的技術(shù)存儲(chǔ)在文件中，每 一種技術(shù)都使用不同的存儲(chǔ)機(jī)制、索引技巧、鎖定水平 并最終提供不同的功能和能力，這些不同的技術(shù)以及配 套的功能在MySQL中稱(chēng)為存儲(chǔ)引擎
• 存儲(chǔ)引擎是MySQL將數(shù)據(jù)存儲(chǔ)在文件系統(tǒng)中的存儲(chǔ)方 式或者存儲(chǔ)格式
• MySQL常用的存儲(chǔ)引擎：MyISAM、InnoDB
• 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ǔ)

2、常用存儲(chǔ)引擎 

2.1MyISAM

MyISAM：不支持事務(wù)和外鍵約束，占用空間較小，訪問(wèn)速度快，適用于不需要事務(wù)處理，頻繁查詢的應(yīng)用場(chǎng)景。

特點(diǎn)：

1、MyISAM不支持事務(wù)，也不支持外鍵約束，只支持全文索引，數(shù)據(jù)文件和索引文件是分開(kāi)保存的

2、訪問(wèn)速度快，對(duì)事務(wù)完整性沒(méi)有要求

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

4、MyISAM在磁盤(pán)上存儲(chǔ)成三個(gè)文件，文件名和表名都相 同，但是擴(kuò)展名分別為：

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

5、表級(jí)鎖定形式，數(shù)據(jù)在更新時(shí)鎖定整個(gè)表

6、數(shù)據(jù)庫(kù)在讀寫(xiě)過(guò)程中相互阻塞

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

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

8、MyIAM支持的存儲(chǔ)格式

• 靜態(tài)表
• 動(dòng)態(tài)表
• 壓縮表

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

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

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

• 動(dòng)態(tài)表

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

• 壓縮表

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

2.1.2MyISAM適用的生產(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ì)比較差

2.2InnoDB 

InnoDB：支持事務(wù)處理、外鍵約束、占用空間比MyISAM大，適用于需要事務(wù)處理、更新刪除頻繁的應(yīng)用場(chǎng)景。

InnoDB特點(diǎn)介紹

• 支持事務(wù)，支持4個(gè)事務(wù)隔離級(jí)別
• MySQL從5.5.5版本開(kāi)始，默認(rèn)的存儲(chǔ)引擎為InnoDB
• 讀寫(xiě)阻塞與事務(wù)隔離級(jí)別相關(guān)
• 能非常高效的緩存索引和數(shù)據(jù)
• 表與主鍵以簇的方式存儲(chǔ)
• 支持分區(qū)、表空間，類(lèi)似oracle數(shù)據(jù)庫(kù)
• 支持外鍵約束，5.5前不支持全文索引，5.5后支持全文索引
• 對(duì)硬件資源要求還是比較高的場(chǎng)合
• 行級(jí)鎖定，但是全表掃描仍然會(huì)是表級(jí)鎖定，如 update table set a=1 where user like %zhang%';
• InnoDB中不保存表的行數(shù)，如 select count(*) from table; 時(shí)，InnoDB需要掃描一遍整個(gè)表來(lái)計(jì)算有多少行，但是MyISAM只要簡(jiǎn)單的讀出保存好的行數(shù)即可。需要注意的是當(dāng)count(*)語(yǔ)句包含where條件時(shí)MyISAM也需要掃描整個(gè)表。
• 對(duì)于自增長(zhǎng)的字段，InnoDB 中必須包含只有該字段的索引，但是在MyISAM表中可以和其他字段一起建立組合索引。
• delete清空整個(gè)表時(shí)，InnoDB 是一行一 行的刪除，效率非常慢。MyISAM則會(huì)重建表。

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

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

支持的字段和數(shù)據(jù)類(lèi)型

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

鎖定類(lèi)型：不同的存儲(chǔ)引擎支持不同級(jí)別的鎖定

• 表鎖定：MyISAM支持
• 行鎖定：InnoDB支持

索引的支持

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

事務(wù)處理的支持

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

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

show engines;

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

方法一: 
show table status from 庫(kù)名 where name='表名'\G
方法二: 
use 庫(kù)名;
show create table 表名;

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

1.通過(guò)alter table修改

use庫(kù)名;
alter table 表名 engine=存儲(chǔ)引擎;

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

vim /etc/my.cnf
 
[mysqld]
 
default-storage-engine=INNODB
-----------------------------------
systemctl restart mysql.service

編輯配置文件 

 重啟服務(wù)

查看原先表的存儲(chǔ)引擎 

 新建表并查看存儲(chǔ)引擎

3.通過(guò) create table創(chuàng)建表時(shí)指定存儲(chǔ)引擎

use 庫(kù)名;
create table 表名(字段1數(shù)據(jù)類(lèi)型,...) engine=存儲(chǔ)引擎;

7、InnoDB行鎖與索引的關(guān)系 

InnoDB行鎖是通過(guò)索引項(xiàng)加鎖來(lái)實(shí)現(xiàn)的,如果沒(méi)有索引,InnoDB將通過(guò)隱藏的聚簇索引（主鍵）來(lái)對(duì)記錄加鎖.

創(chuàng)建一個(gè)表

create table t1(id int,name char(10),age int,primary key(id)) engine=innodb;
 ?
insert into t1 values(1,'aaa',22);
insert into t1 values(2,'bbb',23);
insert into t1 values(3,'aaa',24);
insert into t1 values(4,'bbb',25);
insert into t1 values(5,'ccc',26);
insert into t1 values(6,'zzz',27);

delete from t1 where id=1;
如果id字段是主鍵,innodb對(duì)于主鍵使用了聚簇索引（主鍵）,會(huì)直接鎖住整行記錄.
 
delete from t1 where name='aaa';
如果name字段是普通索引,會(huì)先鎖住索引的兩行因?yàn)橛袃尚泻衋aa的記錄,接著會(huì)鎖住相應(yīng)主鍵對(duì)應(yīng)的記錄.
 
delete from t1 where age=22;
如果age字段沒(méi)有索引,會(huì)使用全表掃描過(guò)濾,這時(shí)表上的各個(gè)記錄都將加上鎖.

8、死鎖

死鎖一般是事務(wù)相互等待對(duì)方資源,最后形成環(huán)路造成的.

如何盡可能避免死鎖

• 以固定的順序訪問(wèn)表和行.
• 大事務(wù)拆小。大事務(wù)更傾向于死鎖,如果業(yè)務(wù)允許,將大事務(wù)拆小.
• 在同一個(gè)事務(wù)中,盡可能做到一次鎖定所需要的所有資源,減少死鎖概率.
• 降低隔離級(jí)別。如果業(yè)務(wù)允許,將隔離級(jí)別調(diào)低也是較好的選擇,比如將隔離級(jí)別從RR調(diào)整為RC,可以避免掉很多因?yàn)間ap鎖造成的死鎖.
• 為表添加合理的索引.如果不使用索引將會(huì)為表的每一行記錄添加上鎖,死鎖的概率大大增大.

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

最新評(píng)論