天天看點
傳回

來聊一聊MySQL的MyISAM與InnoDB兩個常用存儲引擎

作者：Java靈風

儲存引擎概念介紹

  • MySQL中的資料用各種不同的技術存儲在檔案中，每一種技術都使用不同的存儲機制、索引技巧、鎖定水準并最終提供不同的功能和能力，這些不同的技術以及配套的功能在MySQL中成為存儲引擎
  • 存儲引擎是MySQL将資料存儲在檔案系統中的存儲方式或者存儲格式
  • MySQL資料庫中的元件，負責執行實際的資料I/O操作
  • MySQL系統中，存儲引擎處于檔案之上，在資料儲存到資料檔案之前會傳輸到存儲引擎，之後按照各個存儲引擎的存儲格式進行存儲
  • MySQL常用的存儲引擎（MyISAM、InnoDB）

MyISAM儲存引擎

MyISAM的特點

  1. MyISAM不支援事務，也不支援外鍵限制，隻支援全文索引，資料檔案和索引檔案是分開儲存的
  2. 通路速度快，對事務完整性沒有要求
  3. MyISAM适合查詢、插入為主的應用
  4. MyISAM在磁盤上存儲成三個檔案，檔案名和表名都相同，擴充名分别為：
  • .frm檔案存儲表結構的定義
  • 資料檔案的擴充名為.MYD(MYData)
  • 索引檔案的擴充名是.MYI(MYIndex)
  1. 表級鎖定形式，資料在更新時鎖定整個表
  2. 資料庫在讀寫過程中互相阻塞
  • 會在資料寫入的過程阻塞使用者資料的讀取
  • 也會在資料讀取的過程中阻塞使用者的資料寫入
  1. 資料單獨寫入或讀取，速度過程較快且占用資源相對少
  2. MyISAM支援的存儲格式
  • 靜态表
  • 動态表
  • 壓縮表

MyISAM表支援的儲存格式

  • 靜态（固定長度）表

靜态表是預設的存儲格式，靜态表中的字段都是非可變字段，這樣每個記錄都是固定長度的，這種存儲方式的優點是存儲非常迅速，容易緩存，出現故障容易恢複，缺點是占用的空間通常比動态表多

  • 動态表

動态表包含可變字段，記錄不是固定長度的，這樣存儲的優點是占用空間較小，但是頻繁的更新、删除記錄會産生碎片，需要定期執行OPTIMIZE TABLE語句或myiaamchk -r指令來改善性能，并且出現故障的時候恢複相對比較困難

  • 壓縮表

壓縮表由myiaamchk工具建立，占據非常小的空間，因為每條記錄都是被單獨壓縮的，是以隻有非常小的通路開支

常用的存儲引擎

  • MyISAM:不支援事務和外鍵限制，占用資源較小，通路資源較小，通路速度快，表級鎖定，适用于不需要事務處理，單獨寫入或查詢的應用場景
  • InnoDB：支援事務處理、外鍵限制、占用資源比MyISAM大，支援行級鎖定，讀寫并發能力較好，使用于一緻性要求高、資料更新頻繁的應用場景

MyISAM适用場景

  • 公司業務不需要事務的支援
  • 單方面讀取或寫入資料比較多的業務
  • MyISAM存儲引擎資料讀寫都比較頻繁場景不适合
  • 使用讀寫并發通路相對較低的業務
  • 資料修改相對較少的業務
  • 對資料業務一緻性要求不是非常高的業務
  • 伺服器硬體資源相對比較差

InnoDB存儲引擎

InnoDB介紹

  • 支援事務，支援4個事務隔離級别
  • MySQL從5.5.5版本開始，預設的存儲引擎為InnoDB
  • 讀寫阻塞與事務隔離級别相關
  • 能非常高效地緩存索引和資料
  • 表與主鍵以簇的方式存儲 BTREE
  • 支援分區、表空間，類似oracle資料庫
  • 支援外鍵限制，5.5前不支援全文索引，5.5後支援全文索引
  • 對硬體資源要求還是比較高的場合
  • 行級鎖定，但是全表掃描仍然會是表級鎖定，如
    • update table set a=1 where user like '%zhang%';

小貼士：

  • 使用like進行模糊查詢時，會進行全表掃描，鎖定整個表。
  • 對沒有建立索引的字段進行查詢，也會進行全表掃描鎖定整個表。
  • 使用索引進行查詢，則是行級鎖定。

InnoDB的特點

  • InnoDB中不儲存表的行數，如 select count(*) from table; 時，InnoDB需要掃描一遍整個表來計算有多少行，但是MyISAM隻要簡單的讀出儲存好的行數即可。需要注意的是當count(*)語句包含where條件時MyISAM也需要掃描整個表。
  • 對于自增長的字段，InnoDB 中必須包含隻有該字段的索引，但是在MyISAM表中可以和其他字段一起建立組合索引。
  • delete清空整個表時，InnoDB 是一行一 行的删除，效率非常慢。MyISAM則會重建表。

InnoDB适用生産場景分析

  • 業務是需要事務的支援。
  • 行級鎖定對高并發有很強的适應能力，但是需要確定查詢是通過索引來完成。
  • 業務資料更新較頻繁的場景。如：論壇，微網誌等。
  • 業務資料一緻性要求較高。如：銀行業務等。
  • 硬體裝置記憶體較大，利用InnoDB較好的緩存能力來提高記憶體使用率，減少磁盤IO的壓力。

MyISAM和InnoDB的差別：

MyISAM： 不支援事務和外鍵限制，占用空間較小，通路速度快，表級鎖定，适用于不需要事務處理、單獨寫入或查詢的應用場景。（寫入和查詢不一起使用的場景）

InnoDB： 支援事務處理、外鍵限制、占用空間比MyISAM 大，支援行級鎖定，讀寫開發能力較好，适用于需要事務處理、讀寫頻繁的應用場景。

企業選擇存儲引擎依據

1、需要考慮每個存儲引擎提供了哪些不同的核心功能及應用場景。

2、支援的字段和資料類型

  • 所有引擎都支援通用的資料類型
  • 但不是所有的弓|擎都支援其它的字段類型，如二進制對象.

3、鎖定類型：不同的存儲引擎支援不同級别的鎖定

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

檢視存儲引擎

檢視系統支援的存儲引擎 

show engines;
 
 
 
 #Engine：存儲引擎的名稱。
 
 #Support：YES表示引擎受支援且處于活動狀态，NO表示不支援，DEFAULT表示預設存儲引擎。DISABLED表示支援引擎但已将其禁用。
 
 #Comment：存儲引擎的簡要說明。
 
 #Transactions：存儲引擎是否支援事務。
 
 #XA：存儲引擎是否支援XA事務。
 
 #Savepoints：存儲引擎是否支援復原點（标記點）。
複制代碼
來聊一聊MySQL的MyISAM與InnoDB兩個常用存儲引擎
來聊一聊MySQL的MyISAM與InnoDB兩個常用存儲引擎

檢視資料表使用的存儲引擎

方法一： 

show table status from 庫名 where name='表名'\G
複制代碼
來聊一聊MySQL的MyISAM與InnoDB兩個常用存儲引擎

方法二： 

use 庫名;
 show create table 表名;
複制代碼
來聊一聊MySQL的MyISAM與InnoDB兩個常用存儲引擎

修改存儲引擎

方法一：alter table修改

修改目前資料表使用的存儲引擎。

use 庫名;
 alter table 表名 engine=存儲引擎名稱;
#注意：因為MyISAM不支援外鍵限制，如果資料表設定了外鍵，則無法修改為MyISAM。
複制代碼
來聊一聊MySQL的MyISAM與InnoDB兩個常用存儲引擎

方法二：修改配置檔案，指定預設存儲引擎

注意：此方法隻對修改配置檔案并重新開機mysql服務之後新建立的表有效，已經存在的表不會有變更。

建立資料表時如果沒有指定存儲引擎，則會使用預設存儲引擎。

vim /etc/my.cnf
 ......
 [mysqld] 
 default-storage-engine=InnoDB   #修改這一行，指定預設存儲引擎為InnoDB
 
 systemctl restart mysqld   #重新開機服務
複制代碼
來聊一聊MySQL的MyISAM與InnoDB兩個常用存儲引擎

方法三：create table 建立表時指定存儲引擎 

use 庫名;
 create table 表名(字段1 資料類型,...)  engine=存儲引擎名稱;
複制代碼
來聊一聊MySQL的MyISAM與InnoDB兩個常用存儲引擎

InnoDB行鎖與索引的關系

準備一個資料表t1：

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字段建立普通索引
複制代碼
來聊一聊MySQL的MyISAM與InnoDB兩個常用存儲引擎

行級鎖定與表級鎖定

InnoDB行鎖是通過給索引項加鎖來實作的，如果沒有索引，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字段沒有索引，會使用全表掃描過濾，這時表上的各個記錄都将加上鎖。表級鎖定。

死鎖

死鎖一般是事務互相等待對方資源，最後形成環路造成的。

session1 session2
begin; begin;
delete from t1 where id=5;#事務結束前，id=5的行會被鎖定
select * from t1 where id=1 for update; #加排他鎖，模拟并發情況，鎖定id=1的行
delete from t1 where id=1; #死鎖産生
update t1 set name='abc' where id=5; #死鎖産生。因為會話1中id=5的行還在删除過程中，該行已被鎖定
rollback; #復原，結束事務。id=5的行被解鎖
update t1 set name='abc' where id=5; #成功更新資料

for update： 可以為資料庫中的行上一個排它鎖。當一個事務的操作未完成時，其他事務可以讀取該行資料，但是不能寫入、更新或删除。

死鎖示範：

注釋紅色字為會話1，黃色字為會話2

來聊一聊MySQL的MyISAM與InnoDB兩個常用存儲引擎
來聊一聊MySQL的MyISAM與InnoDB兩個常用存儲引擎
來聊一聊MySQL的MyISAM與InnoDB兩個常用存儲引擎
來聊一聊MySQL的MyISAM與InnoDB兩個常用存儲引擎
來聊一聊MySQL的MyISAM與InnoDB兩個常用存儲引擎
來聊一聊MySQL的MyISAM與InnoDB兩個常用存儲引擎

如何盡可能避免死鎖

1、使用更合理的業務邏輯，以固定的順序通路表和行。

2、大事務拆小。大事務更傾向于死鎖，如果業務允許，将大事務拆小。

3、在同一個事務中，盡可能做到一次鎖定所需要的所有資源，減少死鎖機率。

4、降低隔離級别。如果業務允許，将隔離級别調低也是較好的選擇，比如将隔離級别從RR調整為RC，可以避免掉很多因為gap鎖造成的死鎖。

5、為表添加合理的索引。如果不使用索引将會為表的每一行記錄添加上鎖，死鎖的機率大大增加。

總結：

MyISAM和InnoDB的差別：

MyISAM： 不支援事務和外鍵限制，占用空間較小，通路速度快，表級鎖定，适用于不需要事務處理、單獨寫入或查詢的應用場景。（寫入和查詢不一起使用的場景）

InnoDB： 支援事務處理、外鍵限制、占用空間比MyISAM 大，支援行級鎖定，讀寫開發能力較好，适用于需要事務處理、讀寫頻繁的應用場景。

檢視系統支援的存儲引擎：

show engines;

檢視表使用的存儲引擎：

方法一：show table status from 庫名 where name='表名'\G

方法二：show create table 表名;

修改存儲引擎：

方法一：修改已存在的表使用的存儲引擎

alter table 表名 engine=存儲引擎名稱;

方法二：修改配置檔案，指定預設存儲引擎

vim /etc/my.cnf

[mysqld]

default-storage-engine=InnoDB #修改這一行，指定預設存儲引擎為InnoDB

systemctl restart mysqld #重新開機服務

方法三：建立表時指定存儲引擎

create table 表名(字段1 資料類型,...) engine=存儲引擎名稱;

原文：https://juejin.cn/post/7162825723118354439
存儲引擎 mysql Inno
上一篇: 【Shiro】Shiro從小白到大神(五)-自定義Realm
下一篇: linux下 /etc/profile、~/.bash_profile ~/.profile的執行過程

繼續閱讀