MySQL儲存引擎全解析：從原理到效能最佳化的完整指南

2025-05-08 03:45 馬哥Linux運維

MySQL的儲存引擎
儲存引擎簡介

1.檔案系統
- 作業系統組織和儲存資料的一種機制
- 檔案系統是一種軟體
2.檔案系統的型別：ext2 3 4 ，xfs資料
  • 不管使用什麼檔案系統，資料內容不會變化
  • 不同的是，儲存空間、大小、速度
3.MySQL引擎
  • 可以理解為：MySQL的檔案系統，只不過功能更加強大
4.MySQL引擎功能
  • 除了可以提供基本的存取功能，還有更多的事務功能、鎖定、備份和恢復、最佳化及特殊功能
總之，儲存引擎的各項特性就是為了保障資料庫的安全和效能設計結構。
MySQL自帶的儲存引擎
01）InnoDB
02）MyISAM
03）MEMORY
04）ARCHIVE
05）FEDERATED
06）EXAMPLE
07）BLACKHOLE
08）MERGE

09）NDBCLUSTER
10）CSV
還可以使用第三方儲存引擎:
01）MySQL當中外掛式的儲存引擎型別
02）MySQL的兩個分支
-

perconaDB

-

mariaDB

檢視MySQL的儲存引擎
# 檢視所有的儲存引擎

root@localhost:world>show engines;

# 檢視庫中哪些表是innoDB的儲存引擎

select TABLE_SCHEMA,TABLE_NAME,ENGINE from information_schema.tables

where

engine=

'innodb'

;

# 檢視庫中哪些表是myisam的儲存引擎

select TABLE_SCHEMA,TABLE_NAME,ENGINE from information_schema.tables

where

engine=

'myisam'

;

InnoDB和MyIsam的區別
物理區別
# innodb

-rw-r----- 1 mysql mysql 8841 Aug 12 08:33 student.frm

# 表結構檔案

-rw-r----- 1 mysql mysql 98304 Aug 12 08:42 student.ibd

# 資料檔案

# myisam

-rw-r----- 1 mysql mysql 10816 Aug 3 17:10 user.frm

# 表結構檔案

-rw-r----- 1 mysql mysql 1460 Aug 12 12:39 user.MYD

# 資料檔案

-rw-r----- 1 mysql mysql 4096 Aug 12 12:40 user.MYI

# 資料檔案

邏輯區別

在MySQL5.5版本之後，預設的儲存引擎，提供高可靠性和高效能
優點:

01）事務安全（遵從 ACID）

02）MVCC（Multi-Versioning Concurrency Control，多版本併發控制） 03）InnoDB 行級別鎖定

04）Oracle 樣式一致非鎖定讀取

05）表資料進行整理來最佳化基於主鍵的查詢

06）支援外部索引鍵引 用完整性約束

07）大型資料捲上的最大效能

08）將對錶的查詢與不同儲存引擎混合

09）出現故障後快速自動恢復

10）用於在記憶體中快取資料和索引的緩衝區池

innDB核心特性
重點
MVCC

事務

行級鎖

熱備份

Crash Safe Recovery（自動故障恢復）
檢視儲存引擎
# 檢視當前正在使用的儲存引擎

root@localhost:(none)>SELECT @@default_storage_engine;

# 檢視建表語句

root@localhost:world>show create table city;
root@localhost:world>select TABLE_SCHEMA,TABLE_NAME,ENGINE from information_schema.tables

where

engine=

'innodb'

and table_name=

'city'

;
root@localhost:world>show table status like

'city'

\G

設定儲存引擎
# 編輯mysql配置檔案

[mysqld]

default-storage-engine=<儲存引擎名>

default-storage-engine=innodb

default-storage-engine=myisam

# 庫內臨時設定

mysql[(none)]>

set

global default_storage_engine=myisam;

# 建表是指定儲存引擎

create table 表名(

id

int)engine=

'儲存引擎名'

;

企業案例

專案背景：

公司原有的架構：一個展示型的網站，LAMT，MySQL5.1.77版本（MYISAM），50M資料量。

小問題不斷：

1、表級鎖：對錶中任意一行資料修改類操作時，整個表都會鎖定，對其他行的操作都不能同時進行。

2、不支援故障自動恢復（CSR）：當斷電時有可能會出現資料損壞或丟失的問題。

如何解決：

1、提建議將現有的MYISAM引擎替換為Innodb，將版本替換為5.6.38

1）如果使用MYISAM會產生”小問題”，效能安全不能得到保證，使用innodb可以解決這個問題。

2）5.1.77版本對於innodb引擎支援不夠完善，5.6.38版本對innodb支援非常完善了。

2、實施過程和注意要素
解決思路和過程

1.開會討論，停機維護

2.將操作步驟，提前寫在文件中
2.1準備新環境

新的CentOS系統

新的MySQL版本

配置檔案最佳化好

服務啟動好
3.關閉所有連線資料庫的服務

systemctl stop php-fpm

systemctl stop tomcat
4.停資料庫

systemctl stop mysqld
5.備份資料庫中的全部資料

mysqldump -A -R --trigger --single-transaction --master-data=1
6.將備份的資料傳送到新環境中

scp rsync
將表的myisam儲存引擎改成innodb

mysql[zls]> alter table student engine=

'myisam'

;

mysql[zls]> alter table student charset=

'latin1'

;

# 方案一：
#!/bin/bash
for

table

in

` mysql -e

'show tables from zls'

|awk

'NR>1'

`;

do

mysql -e

"alter table zls.$table engine='myisam'"

;

done

# 方案二

[root@db04 zls]

# mysqldump -B zls > /tmp/zls.sql

:%s@ENGINE=MyISAM@ENGINE=InnoDB@g
7.將資料匯入到新的資料庫中
8.將幾臺web伺服器，連線到新庫，做測試
9.應用割接

表空間
  • 共享表空間
  • 獨立表空間
5.5版本以後出現共享表空間概念
表空間的管理模式的出現是為了資料庫的儲存跟容易擴充套件
5.6版本中預設是獨立表空間
共享表空間

[root@db04 zls]

# cd /application/mysql/data

[root@db04 data]

# ll

-rw-r----- 1 mysql mysql 79691776 Aug 15 12:02 ibdata1
mysql[(none)]> show variables like

'%path%'

;
共享表空間，初始大小預設值：12m：自動擴容
5.6版本中預設儲存：

1.系統資料

2.undo

3.臨時表
-rw-rw---- 1 mysql mysql 12582912 Aug 9 10:24 ibdata1

# 共享表空間

-rw-rw---- 1 mysql mysql 50331648 Aug 9 10:24 ib_logfile0

# redo log

-rw-rw---- 1 mysql mysql 50331648 Aug 2 14:45 ib_logfile1

# redo log

5.7版本中的預設儲存：

1.系統資料

-rw-r----- 1 mysql mysql 79691776 Aug 15 12:02 ibdata1

# 共享表空間

-rw-r----- 1 mysql mysql 50331648 Aug 15 12:02 ib_logfile0

# redo log

-rw-r----- 1 mysql mysql 50331648 Aug 3 17:10 ib_logfile1

# redo log

-rw-r----- 1 mysql mysql 12582912 Aug 15 12:01 ibtmp1

# 臨時表

5.7版本中預設會將undo和臨時表獨立出來，5.6版本也可以獨立，只不過需要在初始化的時候進行配置

# 修改共享表空間
# 修改共享表空間

[root@db04 data]

# vim /etc/my.cnf

[mysqld]

innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend

需要看錯誤日誌設定檔案大小
[root@db04 data]

# systemctl restart mysqld

[root@db04 data]

# du -sh ibdata2

-rw-r----- 1 mysql mysql 79691776 Aug 16 09:48 ibdata1

-rw-r----- 1 mysql mysql 52428800 Aug 16 09:48 ibdata2

獨立表空間
對於使用者自主建立的表，會採用此種模式，每個表由一個獨立的表空間進行管理

[root@db01 ~]

# ll /var/lib/mysql/world/

total 1236

-rw-r----- 1 mysql mysql 8710 Aug 15 18:06 city.frm

# 表結構

-rw-r----- 1 mysql mysql 704512 Aug 15 18:06 city.ibd

# 獨立表空間

root@localhost:(none)>show variables like

'%per_table%'

;

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_file_per_table | ON |

+-----------------------+-------+

1 row

inset

(0.01 sec)

ON：開啟

OFF：關閉
[root@db04 data]

# strings world/city.ibd

事務

1.什麼是事務

主要是針對DML語句（update，delete，insert）
一組資料操作執行步驟，這些步驟被視為一個工作單元: 1）用於對多個語句進行分組 2）可以在多個客戶機併發訪問同一個表中的資料時使用

2.事務的通俗理解

伴隨著交易出現的資料庫概念
我們理解的“交易”是什麼？
1）物與物的交換（古代）
2）貨幣現金與實物的交換（現代1）
3）虛擬貨幣與實物的交換（現代2）
4）虛擬貨幣與虛擬實物交換（現代3）

3.如何保證在資料庫中，事務的和諧

靠事務性；來維持
A：原子性：所有語句作為一個單元全部成功執行或全部取消
C：一致性：如果資料庫在事務開始時處於一致轉態，則在執行該事務旗艦保留一致轉態
I：隔離性：事務之間不相互影響
D: 永續性：事務成功完成後，所做的所有更改都會準確地記錄在資料庫中，所做的更改不會丟失。

4.事務流程

5.事務的流程控制語句

情況一：只要執行了DML語句，就會開啟一個事務

insert update delete
情況二：begin執行後，會開啟一個事務

# 開啟事務語句

begin

start transaction
mysql> create table payment(

id

int,name varchar(10),money bigint);

mysql> insert into payment value(1,

'cjk'

,1000),(2,

'dsb'

,1000);

mysql> select * from payment;
start transaction（或 begin）：顯式開始一個新事務

savepoint：分配事務過程中的一個位置，以供將來引用

savepoint abc;
commit：永久記錄當前事務所做的更改

rollback：取消當前事務所做的更改

rollback to savepoint：取消在 savepoint 之後執行的更改

rollback to savepoint abc;
release savepoint：刪除 savepoint 識別符號

release savepoint sb_hl;

# 臨時關閉
set

autocommit：為當前連線停用或啟用預設 autocommit 模式

mysql> autocommit=1; 開啟自動提交（臨時）

mysql> autocommit=0; 關閉自動提交（臨時）

# 永久關閉

vim /etc/my.cnf

[mysqld]

autocommit=0

# 事務的生命週期
# 一個失敗的事務生命週期

begin 開啟一個事務

DML

...

rollback;

# 一個成功的事務生命週期

begin 開啟一個事務

DML

...

commit;

事務的隱式提交

1.在上一個事務沒有執行完，就執行begin或者start transaction，就會將一個事務提交

2.在上一個事務沒有執行完，就執行DDL和DCL就會隱式提交一個事務

3.在事務執行期間，執行鎖定語句（lock tables 、unlock tables）

4.load data infile

5.autocommit=1（開啟自動提交）

事務日誌（CSR自動故障恢復）
  • redo log (重做日誌)

  • 作用是什麼
    在事務的ACID過程中，實現的是D持久化的作用
    D：永續性：事務成功完成後，所做的所有更改都會準確地記錄在資料庫中。所做的更改不會丟失。
    MySQL中：WAL Write Ahead Log 日誌優先寫
    redo log 原理圖

    redo log 故障恢復過程

    undo log

    1.提交了，commit，資料儲存到磁碟上redo log中了，斷電了資料還沒有寫入獨立表空間

    2.沒提交，資料也儲存到磁碟上redo log中了，斷電了，資料還沒有寫入獨立表空間

    3.沒有提交，資料也沒有儲存到 redo log中，斷電了，資料還沒寫入獨立表空間

事務中的鎖
1.什麼是鎖？
鎖顧名思義就是鎖定的意思。
2.鎖的作用是什麼？
在事務ACID特性過程中，鎖 和 隔離級別一起來實現 I 隔離性的作用

MySQL中的鎖：
排它鎖：在事務操作期間，實現行級鎖，保證資料的一致性
共享鎖：在事務操作期間，其他事務不可以修改資料，但是可以查詢資料
樂觀鎖：誰先提交誰
悲觀鎖：只要事務執行期間，其他事務均無法查詢
MVCC多版本併發控制
  • 只阻塞修改類操作，不阻塞查詢類操作
  • 樂觀鎖的機制（誰先提交誰為準）
事務的隔離級別
# 檢視當前使用的事務隔離級別

root@localhost:

wc

>show variables like

'%iso%'

;

read

uncommitted (RU級別：未提交讀)

允許事務檢視其他事務所進行的為提交更改

read

committed (RC級別，已提交讀)

允許事務檢視其他事務所進行的已提交更改
repeatable

read

（RR級別）

確保每個事務的SELECT 輸出一致

InnoDB 的預設級別
serializable （序列化級別）

將一個事務的結果與其他事物完全隔離

# 修改隔離級別

[root@db02 world]

# vim /etc/my.cnf

[mysqld]

autocommit=0

## 未提交讀

transaction_isolation=read-uncommit

企業案例
在沒有備份資料的情況下，突然斷電導致表損壞，打不開資料庫
解決思路
# 1.準備新環境，安裝完資料庫啟動

# 2.將data目錄備份出來匯入新環境

# 3.要知道建表語句，管開發要

CREATE TABLE `city1` (

`ID` int(11) NOT NULL AUTO_INCREMENT,

`Name` char(35) NOT NULL DEFAULT

''

,

`CountryCode` char(3) NOT NULL DEFAULT

''

,

`District` char(20) NOT NULL DEFAULT

''

,

`Population` int(11) NOT NULL DEFAULT

'0'

,

PRIMARY KEY (`ID`),

KEY `CountryCode` (`CountryCode`),

KEY `inx_` (`Population`)

) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

# 4.刪除新表的表空間

root@localhost:world>alter table world.city1 discard tablespace;

# 物理層

[root@db01 world]

# cp -a city.ibd city1.ibd

# 光物理複製表空間，會報錯，country_new表的表空間已經被刪除

mysql> select * from country_new;

ERROR 1814 (HY000): Tablespace has been discarded

for

table

'country_new'

# 6.將新表的表空間，匯入進去

mysql> alter table world.country_new import tablespace;

# 7.兩種解決方案：

- 跟開發說，去改程式碼，把所有要增刪改成country表的程式碼改成country_new

- 刪除已損壞的表，將新表改名為舊錶名

[root@db02 world]

# rm -f country.ibd

[root@db02 world]

# rm -f country.frm

mysql> alter table country_new rename country;

# 8.舊業務先停機

# 9.使用binlog擷取新資料，恢復到新環境中

# 10.業務應用割接到新環境

# 髒讀：瞭解這個之前，首先要了解什麼是髒資料。

# 髒資料是指源系統中的資料不在給定的範圍內或對於實際業務毫無意義，或是資料格式非法，以及在源系統中存在不規範的編碼和含糊的業務邏輯。

髒讀：在資料庫技術中,髒資料在臨時更新（ 髒讀）中產生。事務A更新了某個資料項X，但是由於某種原因，事務A出現了問題，於是要把A回滾。但是在回滾之前，另一個事務B讀取了資料項X的值(A更新後)，A回滾了事務，資料項恢復了原值。事務B讀取的就是資料項X的就是一個“臨時”的值，就是髒資料。
　　通俗的講，當一個事務正在訪問資料，並且對資料進行了修改，而這種修改還沒有提交到資料庫中，這時，另外一個事務也訪問這個資料，然後使用了這個資料。因為這個資料是還沒有提交的資料，那麼另外一個事務讀到的這個資料是髒資料，依據髒資料所做的操作可能是不正確的。

# 不可重複讀：是指在資料庫訪問中，一個事務範圍內兩個相同的查詢卻返回了不同資料。

這是由於查詢時系統中其他事務修改的提交而引起的。比如事務T1讀取某一資料，事務T2讀取並修改了該資料，T1為了對讀取值進行檢驗而再次讀取該資料，便得到了不同的結果。

一種更易理解的說法是：在一個事務內，多次讀同一個資料。在這個事務還沒有結束時，另一個事務也訪問該同一資料。那麼，在第一個事務的兩次讀資料之間。由於第二個事務的修改，那麼第一個事務讀到的資料可能不一樣，這樣就發生了在一個事務內兩次讀到的資料是不一樣的，因此稱為不可重複讀，即原始讀取不可重複。

# 幻覺讀：指當事務不是獨立執行時發生的一種現象，例如 第一個事務對一個表中的資料進行了修改，這種修改涉及到表中的全部資料行。同時，第二個事務也修改這個表中的資料，這種修改是向表中插入一行新資料。那麼，以後就會發生操作第一個事務的使用者發現表中還有沒有修改的資料行，就好像發生了幻覺一樣。


相關文章