目錄
一、存儲引擎簡介
二、MyISAM存儲引擎
1、MylSAM介紹
2、MyISAM表支持3種不同的存儲格式
3、MylSAM的特點
4、MyISAM使用的生產(chǎn)場景
三、InnoDB存儲引擎
1、InnoDB介紹
2、InnoDB的特點
3、InnoDB適用生產(chǎn)場景
4、MyISAM和InnoDB的區(qū)別
四、查看和修改存儲引擎
1、查看系統(tǒng)支持的存儲引擎
2、?查看數(shù)據(jù)表使用的存儲引擎
3、修改存儲引擎?
五、InnoDB行鎖與索引的關系
1、?行級鎖定與表級鎖定
2、死鎖
3、如何盡可能避免死鎖
六、總結
1、MyISAM和InnoDB的區(qū)別
2、查看表使用的存儲引擎
3、修改存儲引擎
一、存儲引擎簡介
? ? ? ?MySQL中的數(shù)據(jù)用各種不同的技術存儲在文件(或者內(nèi)存)中。這些技術中的每一種技術都使用不同的存儲機制、索引技巧、鎖定水平并且最終提供廣泛的不同的功能和能力。通過選擇不同的技術，你能夠獲得額外的速度或者功能，從而改善你的應用的整體功能。 存儲引擎說白了就是如何存儲數(shù)據(jù)、如何為存儲的數(shù)據(jù)建立索引和如何更新、查詢數(shù)據(jù)等技術的實現(xiàn)方法。
? ? ? ?例如，如果你在研究大量的臨時數(shù)據(jù)，你也許需要使用內(nèi)存存儲引擎。內(nèi)存存儲引擎能夠在內(nèi)存中存儲所有的表格數(shù)據(jù)。又或者，你也許需要一個支持事務處理的數(shù)據(jù)庫(以確保事務處理不成功時數(shù)據(jù)的回退能力)。
? ? ? ?mysql常用的存儲引擎有：MyISAM、InnoDB（注意：一個表只能使用一個存儲引擎，一個庫中不同的表可以使用不同的存儲引擎。）
二、MyISAM存儲引擎
1、MylSAM介紹
? ? ? ?MyISAM是MySql默認的存儲引擎,不支持外鍵和事務,優(yōu)勢是訪問速度快.以select和insert為主的應用以這個存儲引擎創(chuàng)建表尤佳。
? ? ? ?它在磁盤上包含三個文件,一個是存儲表定義的.frm文件，存儲數(shù)據(jù)的MYData后綴為.MYD文件和存儲索引的MYIndex后綴為.MYI文件。
2、MyISAM表支持3種不同的存儲格式
- 靜態(tài)表:因為其字段的長度都是固定的，所以相對會占空間，會用空格，但是由于長度固定存儲非?？?。
- 動態(tài)表:變長字段的存儲固然較少空間，但頻繁的更新刪除會產(chǎn)生空間碎片。
- 壓縮表:每個記錄都進行了單獨壓縮，占據(jù)非常小的空間。
3、MylSAM的特點
表級鎖定形式，數(shù)據(jù)在更新時鎖定整個表。
數(shù)據(jù)庫在讀寫過程中相互阻塞：
- 會在數(shù)據(jù)寫入的過程阻塞用戶數(shù)據(jù)的讀取
- 也會在數(shù)據(jù)讀取的過程中阻塞用戶的數(shù)據(jù)寫入
數(shù)據(jù)單獨寫入或讀取，速度過程較快且占用資源相對少。
4、MyISAM使用的生產(chǎn)場景
- 公司業(yè)務不需要事務的支持
- 單方面讀取或寫入數(shù)據(jù)比較多的業(yè)務
- MylSAM存儲引擎數(shù)據(jù)讀寫都比較頻繁場景不適合（因為讀寫是互相阻塞的）
- 使用讀寫并發(fā)訪問相對較低的業(yè)務
- 數(shù)據(jù)修改相對較少的業(yè)務
- 對數(shù)據(jù)業(yè)務-致性要求不是非常高的業(yè)務
- 服務器硬件資源相對比較差（MyISAM占用資源相對少）
三、InnoDB存儲引擎
1、InnoDB介紹
? ? ? ?InnoDB 是 MySQL 上第一個提供外鍵約束的引擎，除了提供事務處理外，InnoDB 還支持行鎖，提供和 Oracle 一樣的一致性的不加鎖讀取，能增加并發(fā)讀的用戶數(shù)量并提高性能，不會增加鎖的數(shù)量。
InnoDB 的設計目標是處理大容量數(shù)據(jù)時最大化性能，它的 CPU 利用率是其他所有基于磁盤的關系數(shù)據(jù)庫引擎中最有效率的。
? ? ? ?InnoDB 是一套放在 MySQL 后臺的完整數(shù)據(jù)庫系統(tǒng)，InnoDB 有它自己的緩沖池，能緩沖數(shù)據(jù)和索引，InnoDB 還把數(shù)據(jù)和索引存放在表空間里面，可能包含好幾個文件，這和 MyISAM 表完全不同，在 MyISAM 中，表被存放在單獨的文件中，InnoDB 表的大小只受限于操作系統(tǒng)文件的大小，一般為 2GB。
2、InnoDB的特點
- InnoDB中不保存表的行數(shù)，如select count（*）from table；時，InnoDB需要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出保存好的行數(shù)即可。需要注意的是當count(*)語句包含where條件時MyISAM也需要掃描整個表。
- 對于自增長的字段，InnoDB 中必須包含只有該字段的索引，但是在MyISAM表中可以和其他字段一起建立組合索引。
- delete清空整個表時，InnoDB 是一行一 行的刪除，效率非常慢。MyISAM則會重建表。
3、InnoDB適用生產(chǎn)場景
-
業(yè)務需要事務的支持。
-
行級鎖定對高并發(fā)有很好的適應能力，但需確保查詢是通過索引來完成。
-
業(yè)務數(shù)據(jù)更新較為頻繁的場景。如：論壇，微博等。
-
業(yè)務數(shù)據(jù)一致性要求較高。如：銀行業(yè)務。
-
硬件設備內(nèi)存較大，利用InnoDB較好的緩存能力來提高內(nèi)存利用率，減少磁盤IO的壓力。
4、MyISAM和InnoDB的區(qū)別
MyISAM： 不支持事務和外鍵約束，占用空間較小，訪問速度快，表級鎖定，適用于不需要事務處理、單獨寫入或查詢的應用場景。（寫入和查詢不一起使用的場景）
InnoDB： 支持事務處理、外鍵約束、占用空間比MyISAM 大，支持行級鎖定，讀寫開發(fā)能力較好，適用于需要事務處理、讀寫頻繁的應用場景。
四、查看和修改存儲引擎
1、查看系統(tǒng)支持的存儲引擎
show engines;
#Engine：存儲引擎的名稱。 ?? ?
#Support：YES表示引擎受支持且處于活動狀態(tài)，NO表示不支持，DEFAULT表示默認存儲引擎。DISABLED表示支持引擎但已將其禁用。 ?? ?
#Comment：存儲引擎的簡要說明。 ?? ?
#Transactions：存儲引擎是否支持事務。 ?? ?
#XA：存儲引擎是否支持XA事務。 ?? ?
#Savepoints：存儲引擎是否支持回滾點（標記點）。
2、?查看數(shù)據(jù)表使用的存儲引擎
方法一：
show table status from 庫名 where name='表名'\G
方法二：?
use 庫名; ?
show create table 表名;
3、修改存儲引擎?
方法一：alter table修改
修改當前數(shù)據(jù)表使用的存儲引擎。
use 庫名; ?
alter table 表名 engine=存儲引擎名稱;
示例：
use uma; ?
alter table week?engine=MyISAM; ? #將class表的存儲引擎修改為MyISAM
注：MyISAM不支持外鍵約束，如果數(shù)據(jù)表設置了外鍵，則無法修改為MyISAM
方法二：修改配置文件，指定默認存儲引擎
注：此方法只對修改配置文件并重啟mysql服務之后新創(chuàng)建的表有效，已經(jīng)存在的表不會有變更。
創(chuàng)建數(shù)據(jù)表時如果沒有指定存儲引擎，則會使用默認存儲引擎。
vim /etc/my.cnf ?
...... ?
[mysqld] ?
default-storage-engine=InnoDB ? #修改這一行，指定默認存儲引擎為InnoDB ?? ?
systemctl restart mysqld ? #重啟服務
方法三：?create table 創(chuàng)建表時指定存儲引擎
use 庫名; ?
create table 表名(字段1 數(shù)據(jù)類型,...) ?engine=存儲引擎名稱;
示例：
use uma; ?
create table litang(id int,name varchar(10)) engine=MyISAM;
五、InnoDB行鎖與索引的關系
準備一個數(shù)據(jù)表tt5：
create table tt5(id int primary key,name char(10),age int);
?
insert into tt5 values(1,'aaa',22); ?
insert into tt5 values(2,'bbb',23); ?
insert into tt5 values(3,'aaa',24); ?
insert into tt5 values(4,'bbb',25); ?
insert into tt5 values(5,'ccc',26); ?
insert into tt5 values(6,'zzz',27);
?
alter table tt5 add index name_index(name); ? #對name字段創(chuàng)建普通索引
1、?行級鎖定與表級鎖定
InnoDB行鎖是通過給索引項加鎖來實現(xiàn)的，如果沒有索引，InnoDB將通過隱藏的聚簇索引來對記錄加鎖。
1）delete from t1 where id=1;
因為id字段是主鍵，Innodb對于主鍵使用了聚簇索引，刪除過程中會直接鎖住整行記錄。行級鎖定。
2）delete from t1 where name='aaa';
因為name字段是普通索引，會先鎖住索引的兩行（因為aaa有兩行），接著會鎖住相應主鍵對應的記錄。行級鎖定。
3）delete from t1 where age=23;
因為age字段沒有索引，會使用全表掃描過濾，這時表上的各個記錄都將加上鎖。表級鎖定。
2、死鎖
死鎖一般是事務相互等待對方資源，最后形成環(huán)路造成的。
| session1 | session2 |
| begin; | begin; |
| delete from tt5?where id=5;#事務結束前，id=5的行會被鎖定 |
|
| select * from tt5 where id=1 for update; #加排他鎖，模擬并發(fā)情況，鎖定id=1的行 | |
| delete from tt5 where id=1; #死鎖產(chǎn)生 | |
| update tt5 set name='abc' where id=5; #死鎖產(chǎn)生。因為會話1中id=5的行還在刪除過程中，該行已被鎖定 | |
| rollback; #回滾，結束事務。id=5的行被解鎖 | |
| update tt5 set name='abc' where id=5; #成功更新數(shù)據(jù) |
for update：?可以為數(shù)據(jù)庫中的行上一個排它鎖。當一個事務的操作未完成時，其他事務可以讀取該行數(shù)據(jù)，但是不能寫入、更新或刪除。
演示：
會話1：
會話2：
會話1：
會話2：
會話1：
會話2：
3、如何盡可能避免死鎖
- 使用更合理的業(yè)務邏輯，以固定的順序訪問表和行。
- 大事務拆小。大事務更傾向于死鎖，如果業(yè)務允許，將大事務拆小。
- 在同一個事務中，盡可能做到一次鎖定所需要的所有資源，減少死鎖概率。
- 降低隔離級別。如果業(yè)務允許，將隔離級別調(diào)低也是較好的選擇，比如將隔離級別從RR調(diào)整為RC，可以避免掉很多因為gap鎖造成的死鎖。
- 為表添加合理的索引。如果不使用索引將會為表的每一行記錄添加上鎖，死鎖的概率大大增加。
六、總結
1、MyISAM和InnoDB的區(qū)別
MyISAM： 不支持事務和外鍵約束，占用空間較小，訪問速度快，表級鎖定，適用于不需要事務處理、單獨寫入或查詢的應用場景。（寫入和查詢不一起使用的場景）
InnoDB： 支持事務處理、外鍵約束、占用空間比MyISAM 大，支持行級鎖定，讀寫開發(fā)能力較好，適用于需要事務處理、讀寫頻繁的應用場景。
2、查看表使用的存儲引擎
方法一：show table status from 庫名 where name='表名'\G
方法二：show create table 表名;
3、修改存儲引擎
方法一：修改已存在的表使用的存儲引擎
alter table 表名 engine=存儲引擎名稱;
方法二：修改配置文件，指定默認存儲引擎
vim /etc/my.cnf
[mysqld]
default-storage-engine=InnoDB #修改這一行，指定默認存儲引擎為InnoDB
systemctl restart mysqld #重啟服務
方法三：創(chuàng)建表時指定存儲引擎文章來源：http://www.zghlxwxcb.cn/news/detail-424459.html
create table 表名(字段1 數(shù)據(jù)類型,...) engine=存儲引擎名稱;文章來源地址http://www.zghlxwxcb.cn/news/detail-424459.html
到了這里，關于MySQL 數(shù)據(jù)庫存儲引擎的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網(wǎng)！
