1、存儲引擎概念

• MySQL中的數(shù)據(jù)用各種不同的技術(shù)存儲在文件中，每 一種技術(shù)都使用不同的存儲機制、索引技巧、鎖定水平 并最終提供不同的功能和能力，這些不同的技術(shù)以及配 套的功能在MySQL中稱為存儲引擎
• 存儲引擎是MySQL將數(shù)據(jù)存儲在文件系統(tǒng)中的存儲方 式或者存儲格式
• MySQL常用的存儲引擎：MyISAM、InnoDB
• MySQL數(shù)據(jù)庫中的組件，負責(zé)執(zhí)行實際的數(shù)據(jù)I/O操作
• MySQL系統(tǒng)中，存儲引擎處于文件系統(tǒng)之上，在數(shù)據(jù)保存到數(shù)據(jù)文件之前會傳輸?shù)酱鎯σ?，之后按照各個存儲引擎的存儲格式進行存儲

2、常用存儲引擎 

2.1MyISAM

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

特點：

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

2、訪問速度快，對事務(wù)完整性沒有要求

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

4、MyISAM在磁盤上存儲成三個文件，文件名和表名都相 同，但是擴展名分別為：

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

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

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

• 會在數(shù)據(jù)寫入的過程阻塞用戶數(shù)據(jù)的讀取
• 也會在數(shù)據(jù)讀取的過程中阻塞用戶的數(shù)據(jù)寫入

7、數(shù)據(jù)單獨寫入或讀取，速度過程較快且占用資源相對少

8、MyIAM支持的存儲格式

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

2.1.1MyISAM表支持3種不同的存儲格式： 

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

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

• 動態(tài)表

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

• 壓縮表

壓縮表由myisamchk工具創(chuàng)建，占據(jù)非常小的空間，因為每條記錄都是被單獨壓縮的，所以只有非常小的訪問開支。

2.1.2MyISAM適用的生產(chǎn)場景舉例 

• 公司業(yè)務(wù)不需要事務(wù)的支持
• 單方面讀取或?qū)懭霐?shù)據(jù)比較多的業(yè)務(wù)
• MyISAM存儲引擎數(shù)據(jù)讀寫都比較頻繁場景不適合
• 使用讀寫并發(fā)訪問相對較低的業(yè)務(wù)
• 數(shù)據(jù)修改相對較少的業(yè)務(wù)
• 對數(shù)據(jù)業(yè)務(wù)一致性要求不是非常高的業(yè)務(wù)
• 服務(wù)器硬件資源相對比較差

2.2InnoDB 

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

InnoDB特點介紹

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

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

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

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

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

鎖定類型：不同的存儲引擎支持不同級別的鎖定

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

索引的支持

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

事務(wù)處理的支持

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

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

show engines;

 5、查看表使用的存儲引擎

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

6、修改存儲引擎 

1.通過alter table修改

use庫名;
alter table 表名 engine=存儲引擎;

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

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

編輯配置文件 

 重啟服務(wù)

查看原先表的存儲引擎 

 新建表并查看存儲引擎

3.通過 create table創(chuàng)建表時指定存儲引擎

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

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

InnoDB行鎖是通過索引項加鎖來實現(xiàn)的,如果沒有索引,InnoDB將通過隱藏的聚簇索引（主鍵）來對記錄加鎖.

創(chuàng)建一個表

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對于主鍵使用了聚簇索引（主鍵）,會直接鎖住整行記錄.
 
delete from t1 where name='aaa';
如果name字段是普通索引,會先鎖住索引的兩行因為有兩行含有aaa的記錄,接著會鎖住相應(yīng)主鍵對應(yīng)的記錄.
 
delete from t1 where age=22;
如果age字段沒有索引,會使用全表掃描過濾,這時表上的各個記錄都將加上鎖.

8、死鎖

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

如何盡可能避免死鎖

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

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

最新評論