MySQL數(shù)據(jù)庫(kù)存儲(chǔ)引擎的應(yīng)用
1、存儲(chǔ)引擎概念
- MySQL中的數(shù)據(jù)用各種不同的技術(shù)存儲(chǔ)在文件中,每 一種技術(shù)都使用不同的存儲(chǔ)機(jī)制、索引技巧、鎖定水平 并最終提供不同的功能和能力,這些不同的技術(shù)以及配 套的功能在MySQL中稱為存儲(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ù)處理,頻繁查詢的應(yīng)用場(chǎng)景。
特點(diǎn):
1、MyISAM不支持事務(wù),也不支持外鍵約束,只支持全文索引,數(shù)據(jù)文件和索引文件是分開保存的
2、訪問速度快,對(duì)事務(wù)完整性沒有要求
3、MyISAM 適合查詢、插入為主的應(yīng)用
4、MyISAM在磁盤上存儲(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ù)在讀寫過程中相互阻塞
- 會(huì)在數(shù)據(jù)寫入的過程阻塞用戶數(shù)據(jù)的讀取
- 也會(huì)在數(shù)據(jù)讀取的過程中阻塞用戶的數(shù)據(jù)寫入
7、數(shù)據(jù)單獨(dú)寫入或讀取,速度過程較快且占用資源相對(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 語句或myisamchk-r命令來改善性能,并且出現(xiàn)故障的時(shí)候恢復(fù)相對(duì)比較困難。
- 壓縮表
壓縮表由myisamchk工具創(chuàng)建,占據(jù)非常小的空間,因?yàn)槊織l記錄都是被單獨(dú)壓縮的,所以只有非常小的訪問開支。
2.1.2MyISAM適用的生產(chǎn)場(chǎng)景舉例
- 公司業(yè)務(wù)不需要事務(wù)的支持
- 單方面讀取或?qū)懭霐?shù)據(jù)比較多的業(yè)務(wù)
- MyISAM存儲(chǔ)引擎數(shù)據(jù)讀寫都比較頻繁場(chǎng)景不適合
- 使用讀寫并發(fā)訪問相對(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版本開始,默認(rèn)的存儲(chǔ)引擎為InnoDB
- 讀寫阻塞與事務(wù)隔離級(jí)別相關(guān)
- 能非常高效的緩存索引和數(shù)據(jù)
- 表與主鍵以簇的方式存儲(chǔ)
- 支持分區(qū)、表空間,類似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è)表來計(jì)算有多少行,但是MyISAM只要簡(jiǎn)單的讀出保存好的行數(shù)即可。需要注意的是當(dāng)count(*)語句包含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ù)類型
- 所有引擎都支持通用的數(shù)據(jù)類型
- 但不是所有的引擎都支持其它的字段類型,如二進(jìn)制對(duì)象
鎖定類型:不同的存儲(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.通過alter table修改
use庫(kù)名; alter table 表名 engine=存儲(chǔ)引擎;
2.通過修改/etc/my.cnf配置文件,指定默認(rèn)存儲(chǔ)引擎并重啟服務(wù)
vim /etc/my.cnf [mysqld] default-storage-engine=INNODB ----------------------------------- systemctl restart mysql.service
編輯配置文件
重啟服務(wù)
查看原先表的存儲(chǔ)引擎
新建表并查看存儲(chǔ)引擎
3.通過 create table創(chuàng)建表時(shí)指定存儲(chǔ)引擎
use 庫(kù)名; create table 表名(字段1數(shù)據(jù)類型,...) engine=存儲(chǔ)引擎;
7、InnoDB行鎖與索引的關(guān)系
InnoDB行鎖是通過索引項(xiàng)加鎖來實(shí)現(xiàn)的,如果沒有索引,InnoDB將通過隱藏的聚簇索引(主鍵)來對(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字段沒有索引,會(huì)使用全表掃描過濾,這時(shí)表上的各個(gè)記錄都將加上鎖.
8、死鎖
死鎖一般是事務(wù)相互等待對(duì)方資源,最后形成環(huá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)文章希望大家以后多多支持腳本之家!
相關(guān)文章
原來MySQL?數(shù)據(jù)類型也可以優(yōu)化
這篇文章主要介紹了原來MySQL?數(shù)據(jù)類型也可以優(yōu)化,文章圍繞主題展開詳細(xì)的內(nèi)容介紹,具有一定的參考價(jià)值,需要的小伙伴可以參考一下,希望對(duì)你的學(xué)習(xí)有所幫助2022-08-08MySQL高可用解決方案MMM(mysql多主復(fù)制管理器)
MySQL本身沒有提供replication failover的解決方案,通過MMM方案能實(shí)現(xiàn)服務(wù)器的故障轉(zhuǎn)移,從而實(shí)現(xiàn)mysql的高可用。MMM不僅能提供浮動(dòng)IP的功能,如果當(dāng)前的主服務(wù)器掛掉后,會(huì)將你后端的從服務(wù)器自動(dòng)轉(zhuǎn)向新的主服務(wù)器進(jìn)行同步復(fù)制,不用手工更改同步配置2017-09-09Mysql技術(shù)內(nèi)幕之InnoDB鎖的深入講解
這篇文章主要給大家介紹了關(guān)于Mysql技術(shù)內(nèi)幕之InnoDB鎖的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-12-12MySQL UDF調(diào)試方式debugview的相關(guān)方法
MySQL UDF調(diào)試方式debugview的相關(guān)方法...2007-07-07mysql 5.7 的 /etc/my.cnf 參數(shù)介紹
這篇文章主要介紹了mysql 5.7 的 /etc/my.cnf 參數(shù)介紹,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2020-12-12MySQL分表和分區(qū)分表的區(qū)別小結(jié)
MySQL分表和分區(qū)分表是兩種常見的數(shù)據(jù)分割方案,本文主要介紹了MySQL分表和分區(qū)分表的區(qū)別小結(jié),具有一定的參考價(jià)值,感興趣的可以了解一下2024-07-07