一、存儲引擎的概念
1.1 什么是存儲引擎
- MySQL中的數(shù)據(jù)用各種不下同的技術(shù)存儲在文件中，每一種技術(shù)都使用不同的存儲機(jī)制、索引技巧、鎖定水平并最終提供不同的功能和能力，這些不同的技術(shù)以及配套的功能在MySQL中稱為存儲引擎。
- 存儲引擎是MySQL將數(shù)據(jù)存儲在文件系統(tǒng)中的存儲方式或者存儲格式。
- 存儲引擎是MySQL數(shù)據(jù)庫中的組件，負(fù)責(zé)執(zhí)行實際的數(shù)據(jù)I/O操作。
- MySQL系統(tǒng)中，存儲引擎處于文件系統(tǒng)之上，在數(shù)據(jù)保存到數(shù)據(jù)文件之前會傳輸?shù)酱鎯σ?，之后按照各個存儲引擎的存儲格式進(jìn)行存儲。
1.2 MySQL常用的存儲引擎：
- MylSAM
- InnoDB
注意：一個表只能使用一個存儲引擎，一個庫中不同的表可以使用不同的存儲引擎。
二、MyISAM存儲引擎
2.1 MylSAM介紹
-
MylSAM不支持事務(wù)，也不支持外鍵約束，只支持全文索引，數(shù)據(jù)文件和索引文件是分開保存的。
-
訪問速度快，對事務(wù)完整性沒有要求。
-
MylSAM適合查詢、插入為主的應(yīng)用。
-
MylSAM在磁盤.上存儲成三個文件，文件名和表名都相同，但是擴(kuò)展名分別為：
- .frm文件存儲表結(jié)構(gòu)的定義
- 數(shù)據(jù)文件的擴(kuò)展名為.MYD (MYData)
- 索引文件的擴(kuò)展名是.MYI (MYIndex)
2.2 MylSAM的特點
-
表級鎖定形式，數(shù)據(jù)在更新時鎖定整個表。
-
數(shù)據(jù)庫在讀寫過程中相互阻塞：
- 會在數(shù)據(jù)寫入的過程阻塞用戶數(shù)據(jù)的讀取
- 也會在數(shù)據(jù)讀取的過程中阻塞用戶的數(shù)據(jù)寫入
-
數(shù)據(jù)單獨寫入或讀取，速度過程較快且占用資源相對少。
2.3 MyISAM表支持3種不同的存儲格式
（1）靜態(tài)（固定長度）表
靜態(tài)表是默認(rèn)的存儲格式。靜態(tài)表中的字段都是非可變字段，這樣每個記錄都是固定長度的，這種存儲方式的優(yōu)點是存儲非常迅速，容易緩存，出現(xiàn)故障容易恢復(fù);缺點是占用的空間通常比動態(tài)表多。
（2）動態(tài)表
動態(tài)表包含可變字段，記錄不是固定長度的，這樣存儲的優(yōu)點是占用空間較少，但是頻繁的更新、刪除記錄會產(chǎn)生碎片，需要定期執(zhí)行OPTIMIZE TABLE語句或myisamchk-r命令來改善性能，并且出現(xiàn)故障的時候恢復(fù)相對比較困難（因為會產(chǎn)生磁盤碎片，而且存儲空間不是連續(xù)的）。
（3）壓縮表
壓縮表由 myisamchk 工具創(chuàng)建，占據(jù)非常小的空間，因為每條記錄都是被單獨壓縮的，所以只有非常小的訪問開支。（壓縮的過程中會占用CPU性能）
2.4 MyISAM使用的生產(chǎn)場景舉例
- 公司業(yè)務(wù)不需要事務(wù)的支持
- 單方面讀取或?qū)懭霐?shù)據(jù)比較多的業(yè)務(wù)
- MylSAM存儲引擎數(shù)據(jù)讀寫都比較頻繁場景不適合（因為讀寫是互相阻塞的）
- 使用讀寫并發(fā)訪問相對較低的業(yè)務(wù)
- 數(shù)據(jù)修改相對較少的業(yè)務(wù)
- 對數(shù)據(jù)業(yè)務(wù)-致性要求不是非常高的業(yè)務(wù)
- 服務(wù)器硬件資源相對比較差（MyISAM占用資源相對少）
三、InnoDB存儲引擎
3.1 InnoDB介紹
-
支持事務(wù)，支持4個事務(wù)隔離級別
-
MySQL從5.5.5版本開始，默認(rèn)的存儲引擎為InnoDB
-
讀寫阻塞與事務(wù)隔離級別相關(guān)
-
能非常高效的緩存索引和數(shù)據(jù)
-
表與主鍵以簇的方式存儲 BTREE
-
支持分區(qū)、表空間，類似oracle數(shù)據(jù)庫
-
支持外鍵約束，5.5前不支持全文索引，5.5后支持全文索引
-
對硬件資源要求還是比較高的場合
-
行級鎖定，但是全表掃描仍然會是表級鎖定，如
-
update table set a=1 where user like '%zhang%';
-
注意：
- 使用like進(jìn)行模糊查詢時，會進(jìn)行全表掃描，鎖定整個表。
- 對沒有創(chuàng)建索引的字段進(jìn)行查詢，也會進(jìn)行全表掃描鎖定整個表。
- 使用索引進(jìn)行查詢，則是行級鎖定。
3.2 InnoDB的特點
-
InnoDB中不保存表的行數(shù)，如
select count(*) from table;時，InnoDB需要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出保存好的行數(shù)即可。需要注意的是當(dāng)count(*)語句包含where條件時MyISAM也需要掃描整個表。 -
對于自增長的字段，InnoDB 中必須包含只有該字段的索引，但是在MyISAM表中可以和其他字段一起建立組合索引。
-
delete清空整個表時，InnoDB 是一行一 行的刪除，效率非常慢。MyISAM則會重建表。
3.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)存利用率，減少磁盤IO的壓力。
3.3 MyISAM和InnoDB的區(qū)別：
MyISAM： 不支持事務(wù)和外鍵約束，占用空間較小，訪問速度快，表級鎖定，適用于不需要事務(wù)處理、單獨寫入或查詢的應(yīng)用場景。（寫入和查詢不一起使用的場景）
InnoDB： 支持事務(wù)處理、外鍵約束、占用空間比MyISAM 大，支持行級鎖定，讀寫開發(fā)能力較好，適用于需要事務(wù)處理、讀寫頻繁的應(yīng)用場景。
四、企業(yè)選擇存儲引擎依據(jù)
1、需要考慮每個存儲引擎提供了哪些不同的核心功能及應(yīng)用場景。
2、支持的字段和數(shù)據(jù)類型
- 所有引擎都支持通用的數(shù)據(jù)類型
- 但不是所有的弓|擎都支持其它的字段類型，如二進(jìn)制對象.
3、鎖定類型：不同的存儲引擎支持不同級別的鎖定
- 表鎖定：MyISAM 支持
- 行鎖定：InnoDB 支持
五、查看和修改存儲引擎
5.1 查看存儲引擎
1）查看系統(tǒng)支持的存儲引擎
?show engines;
??
?------------------輸出包含以下列：----------------------
??
?#Engine：存儲引擎的名稱。
??
?#Support：YES表示引擎受支持且處于活動狀態(tài)，NO表示不支持，DEFAULT表示默認(rèn)存儲引擎。DISABLED表示支持引擎但已將其禁用。
??
?#Comment：存儲引擎的簡要說明。
??
?#Transactions：存儲引擎是否支持事務(wù)。
??
?#XA：存儲引擎是否支持XA事務(wù)。
??
?#Savepoints：存儲引擎是否支持回滾點（標(biāo)記點）。
2）查看數(shù)據(jù)表使用的存儲引擎
方法一：
show table status from 庫名 where name='表名'\G
方法二：
use 庫名;
show create table 表名;
5.2 修改存儲引擎
方法一：alter table修改
修改當(dāng)前數(shù)據(jù)表使用的存儲引擎。
use 庫名;
alter table 表名 engine=存儲引擎名稱;
例：
use school;
alter table class engine=MyISAM; #將class表的存儲引擎修改為MyISAM
?
#注意：因為MyISAM不支持外鍵約束，如果數(shù)據(jù)表設(shè)置了外鍵，則無法修改為MyISAM。注： MyISAM不支持外鍵約束，如果數(shù)據(jù)表設(shè)置了外鍵，則無法修改為MyISAM
方法二：修改配置文件，指定默認(rèn)存儲引擎
注意：此方法只對修改配置文件并重啟mysql服務(wù)之后新創(chuàng)建的表有效，已經(jīng)存在的表不會有變更。
創(chuàng)建數(shù)據(jù)表時如果沒有指定存儲引擎，則會使用默認(rèn)存儲引擎。
vim /etc/my.cnf
......
[mysqld]
default-storage-engine=InnoDB #修改這一行，指定默認(rèn)存儲引擎為InnoDB
?
systemctl restart mysqld #重啟服務(wù)
?
方法三：create table 創(chuàng)建表時指定存儲引擎
use 庫名;
create table 表名(字段1 數(shù)據(jù)類型,...) engine=存儲引擎名稱;
use school;
create table teacher(id int,name varchar(10)) engine=MyISAM;
#指定存儲引擎為MyISAM
六、InnoDB行鎖與索引的關(guān)系
準(zhǔn)備一個數(shù)據(jù)表t1：
use ky;
create table t1(id int primary key,name char(10),age int);
?
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);
alter table t1 add index name_index(name); #對name字段創(chuàng)建普通索引
6.1 行級鎖定與表級鎖定
InnoDB行鎖是通過給索引項加鎖來實現(xiàn)的，如果沒有索引，InnoDB將通過隱藏的聚簇索引來對記錄加鎖。
1）delete from t1 where id=1;
因為id字段是主鍵，Innodb對于主鍵使用了聚簇索引，刪除過程中會直接鎖住整行記錄。行級鎖定。
2）delete from t1 where name='aaa';
因為name字段是普通索引，會先鎖住索引的兩行（因為aaa有兩行），接著會鎖住相應(yīng)主鍵對應(yīng)的記錄。行級鎖定。
3）delete from t1 where age=23;
因為age字段沒有索引，會使用全表掃描過濾，這時表上的各個記錄都將加上鎖。表級鎖定
6.2 死鎖：
?
for update：?可以為數(shù)據(jù)庫中的行上一個排它鎖。當(dāng)一個事務(wù)的操作未完成時，其他事務(wù)可以讀取該行數(shù)據(jù)，但是不能寫入、更新或刪除。
例：
?
?
?
?
什么是死鎖? 怎么解決?
死鎖是指兩個或多個事務(wù)在同一資源上相互占用，并請求鎖定對方的資源，從而導(dǎo)致惡性循環(huán)的現(xiàn)象。常見的解決死鎖的方法
如果不同程序會并發(fā)存取多個表，盡量約定以相同的順序訪問表，可以大大降低死鎖機(jī)會。2、在同一個事務(wù)中，盡可能做到一次鎖定所需要的所有資源，減少死鎖產(chǎn)生概率:
3、對于非常容易產(chǎn)生死鎖的業(yè)務(wù)部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死鎖產(chǎn)生的概率:如果業(yè)務(wù)處理不好可以用分布式事務(wù)鎖或者使用樂觀鎖
如何盡可能避免死鎖 ?
1) 使用更合理的業(yè)務(wù)邏輯，以固定的順序訪問表和行
2) 大事務(wù)拆小。大事務(wù)更傾向于死鎖，如果業(yè)務(wù)允許，將大事務(wù)拆小。
3)在同一個事務(wù)中，盡可能做到一次鎖定所需要的所有資源，減少死鎖概率。
4)降低隔離級別。如果業(yè)務(wù)允許，將隔離級別調(diào)低也是較好的選擇，比如將隔離級別從RR調(diào)整為RC，可以避免掉很多因為gap鎖造成的死鎖
5)為表添加合理的索引。如果不使用索引將會為表的每一行記錄添加上鎖，死鎖的概率大大增大。
總結(jié)：
1.MyISAM和InnoDB的區(qū)別：
MyISAM： 不支持事務(wù)和外鍵約束，占用空間較小，訪問速度快，表級鎖定，適用于不需要事務(wù)處理、單獨寫入或查詢的應(yīng)用場景。（寫入和查詢不一起使用的場景）
InnoDB： 支持事務(wù)處理、外鍵約束、占用空間比MyISAM 大，支持行級鎖定，讀寫開發(fā)能力較好，適用于需要事務(wù)處理、讀寫頻繁的應(yīng)用場景
2.查看系統(tǒng)支持的存儲引擎：
show engines;
3.查看表使用的存儲引擎：
方法一：show table status from 庫名 where name='表名'\G
方法二：show create table 表名;
4.修改存儲引擎：
方法一：修改已存在的表使用的存儲引擎
alter table 表名 engine=存儲引擎名稱;
方法二：修改配置文件，指定默認(rèn)存儲引擎
vim /etc/my.cnf
[mysqld]
default-storage-engine=InnoDB #修改這一行，指定默認(rèn)存儲引擎為InnoDB
systemctl restart mysqld #重啟服務(wù)
方法三：創(chuàng)建表時指定存儲引擎文章來源：http://www.zghlxwxcb.cn/news/detail-437873.html
create table 表名(字段1 數(shù)據(jù)類型,...) engine=存儲引擎名稱;文章來源地址http://www.zghlxwxcb.cn/news/detail-437873.html
到了這里，關(guān)于MySQL數(shù)據(jù)庫之存儲引擎的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
