日志是mysql數(shù)據(jù)庫(kù)的重要組成部分，記錄著數(shù)據(jù)庫(kù)運(yùn)行期間各種狀態(tài)信息。mysql日志主要包括錯(cuò)誤日志、查詢?nèi)罩尽⒙樵內(nèi)罩?、事?wù)日志、二進(jìn)制日志幾大類。

以下是mysql數(shù)據(jù)庫(kù)中常用的幾種日志類型：

關(guān)于每種日志的類型的功能可參考：

關(guān)于如何查詢?nèi)罩臼欠耖_啟，可使用以下命令：

MySQL 中的binlog和redolog是兩種核心日志機(jī)制，它們?cè)跀?shù)據(jù)庫(kù)的數(shù)據(jù)持久化、主從復(fù)制和崩潰恢復(fù)中扮演關(guān)鍵角色。

關(guān)于兩者如何進(jìn)行相互聯(lián)系的，可參考如下：

1、表空間結(jié)構(gòu)

1.1、分類

MySQL數(shù)據(jù)表以文件方式存放在磁盤中，默認(rèn)使用共享表空間（0）存儲(chǔ)。

1.對(duì)于 InnoDB：

ibdata 文件：

當(dāng)使用共享表空間時(shí)，所有表的數(shù)據(jù)和索引會(huì)存儲(chǔ)在一個(gè)共享的 ibdata 文件中。表結(jié)構(gòu)以.frm文件的形式存儲(chǔ)在與表對(duì)應(yīng)的文件夾中。

.ibd 文件：

如果使用了獨(dú)立表空間，InnoDB 會(huì)將每個(gè)表的結(jié)構(gòu)和數(shù)據(jù)存儲(chǔ)在獨(dú)立的 .ibd 文件中。每當(dāng)表的數(shù)據(jù)或索引被更新時(shí)，文件也會(huì)隨之變化。表的結(jié)構(gòu)仍然以.frm文件存儲(chǔ)。

2.對(duì)于 MyISAM：

每個(gè)表的數(shù)據(jù)和索引不屬于共享空間或獨(dú)立空間的問題，而是直接通過三個(gè)文件來(lái)管理的：

MyISAM 數(shù)據(jù)表通常使用三種文件，分別是：

• .frm：存儲(chǔ)表的結(jié)構(gòu)信息。
• .MYD：存儲(chǔ)表的數(shù)據(jù)。
• .MYI：存儲(chǔ)表的索引。

3. 設(shè)置

使用共享表空間:

• 只需將innodb_file_per_table設(shè)置為0或不設(shè)置（默認(rèn)值）。

使用獨(dú)立表空間:

• 可以在 MySQL 配置文件（my.cnf或my.ini）中啟用獨(dú)立表空間，添加如下配置：

[mysqld]
innodb_file_per_table=1

這將允許每個(gè) InnoDB 表都有其獨(dú)立的.ibd文件。

1.2、物理結(jié)構(gòu)

如下圖所示：

獨(dú)立表空間文件（.ibd）,每個(gè)獨(dú)立表在磁盤上對(duì)應(yīng)一個(gè).ibd文件（test_table.ibd）

主要包括以下部分：

1.表空間頭（Header）

存儲(chǔ)表空間的基本信息，如：

• 表空間 ID。
• 區(qū)（Extent）的分配狀態(tài)。
• 空閑列表（Free List）、碎片列表（Fragment List）等。

用于管理表空間的物理分配和回收。

2.段（Segment）

InnoDB 的存儲(chǔ)結(jié)構(gòu)是分層的：段 → 區(qū) → 頁(yè)。每個(gè)表空間文件包含多個(gè)段，主要分為以下幾類：

數(shù)據(jù)段（Data Segment）：

• 存儲(chǔ) B+ 樹的葉子節(jié)點(diǎn)（數(shù)據(jù)頁(yè)），即實(shí)際的行數(shù)據(jù)。
• 對(duì)于主鍵索引（聚簇索引），數(shù)據(jù)段直接存儲(chǔ)完整的行數(shù)據(jù)。

索引段（Index Segment）：

• 存儲(chǔ) B+ 樹的非葉子節(jié)點(diǎn)（索引頁(yè)），即索引鍵值和子節(jié)點(diǎn)指針。
• 用于加速數(shù)據(jù)檢索。

回滾段（Rollback Segment）：

• 存儲(chǔ)事務(wù)的Undo Log（回滾數(shù)據(jù)），用于實(shí)現(xiàn)事務(wù)回滾和 MVCC（多版本并發(fā)控制）。
• 每個(gè)事務(wù)可能關(guān)聯(lián)一個(gè)或多個(gè)回滾段。

3.區(qū)（Extent）

每個(gè)段由多個(gè)區(qū)（Extent）組成，一個(gè)區(qū)固定包含64 個(gè)連續(xù)頁(yè)（默認(rèn)頁(yè)大小為 16KB，因此一個(gè)區(qū)大小為 1MB）。

區(qū)是 InnoDB 分配存儲(chǔ)空間的基本單位，用于提高分配效率。

4.頁(yè)（Page）

頁(yè)是 InnoDB 的最小存儲(chǔ)單元，大小默認(rèn)為16KB。

如下圖所示：

頁(yè)的類型取決于其所屬的段：

• 數(shù)據(jù)頁(yè)：存儲(chǔ) B+ 樹葉子節(jié)點(diǎn)的數(shù)據(jù)（如主鍵索引的行數(shù)據(jù)）。
• 索引頁(yè)：存儲(chǔ) B+ 樹非葉子節(jié)點(diǎn)的索引鍵值和子節(jié)點(diǎn)指針。
• 回滾頁(yè)：存儲(chǔ) Undo Log（回滾數(shù)據(jù)）。
• 系統(tǒng)頁(yè)：存儲(chǔ)表空間的元數(shù)據(jù)（如空閑列表、區(qū)分配信息等）。

5.空閑列表（Free List）

• 記錄表空間中未被使用的頁(yè)，供后續(xù)分配。
• 當(dāng)插入新數(shù)據(jù)或更新索引時(shí)，InnoDB 會(huì)從空閑列表中分配頁(yè)。

小結(jié)

一張表的空間由頁(yè)節(jié)點(diǎn)段、非頁(yè)節(jié)點(diǎn)段、回滾段、和表空間組成。頁(yè)節(jié)點(diǎn)段由多個(gè)區(qū)（1MB）組成，每個(gè)區(qū)由64個(gè)頁(yè)（16KB）組成，每個(gè)頁(yè)有多行組成。

如下圖所示：

注意：此處圖中的頁(yè)僅代表的是葉子結(jié)點(diǎn)的page頁(yè)。對(duì)于非葉子節(jié)點(diǎn)，也有相應(yīng)的索引鍵值的頁(yè)。

1.3、邏輯結(jié)構(gòu)

B+ 樹索引與數(shù)據(jù)組織，InnoDB 的表邏輯上通過B+ 樹索引組織數(shù)據(jù)。

具體如下：

1.主鍵索引（聚簇索引）

• 葉子節(jié)點(diǎn)：存儲(chǔ)完整的行數(shù)據(jù)（包括所有列的值）。
• 非葉子節(jié)點(diǎn)：存儲(chǔ)主鍵值和指向子節(jié)點(diǎn)的指針。
• 聚簇索引決定了數(shù)據(jù)在磁盤上的物理存儲(chǔ)順序，因此主鍵選擇直接影響性能。

2.二級(jí)索引（輔助索引）

• 葉子節(jié)點(diǎn)：存儲(chǔ)主鍵值（而非完整行數(shù)據(jù)）。
• 非葉子節(jié)點(diǎn)：存儲(chǔ)索引鍵值和子節(jié)點(diǎn)指針。
• 查詢時(shí)，InnoDB 會(huì)先通過二級(jí)索引定位到主鍵值，再通過聚簇索引查找完整行數(shù)據(jù)。

3.事務(wù)與 MVCC 機(jī)制

Undo Log（回滾段中的頁(yè)）：

• 記錄事務(wù)修改前的數(shù)據(jù)版本。
• 支持事務(wù)回滾（Rollback）和并發(fā)讀取（MVCC）。

版本鏈（Version Chain）：

• 每個(gè)行記錄通過 Undo Log 構(gòu)建版本鏈，供不同事務(wù)讀取一致性視圖。

關(guān)于獨(dú)立表和共享表的區(qū)別：

2、binlog

2.1. 定義

binlog（Binary Log）是 MySQL 的服務(wù)層日志，屬于二進(jìn)制日志，記錄所有對(duì)數(shù)據(jù)庫(kù)的增刪改操作（不包含查詢）。由 MySQL Server 層生成，與存儲(chǔ)引擎無(wú)關(guān)（如 InnoDB、MyISAM）。默認(rèn)情況下二進(jìn)制日志是關(guān)閉的。

結(jié)構(gòu)如下所示：

2.2. 核心作用

1.主從復(fù)制（Replication）

主庫(kù)將 binlog 發(fā)送給從庫(kù)，從庫(kù)重放 binlog 實(shí)現(xiàn)數(shù)據(jù)同步。

2.數(shù)據(jù)恢復(fù)（Point-in-Time Recovery）

通過 binlog 恢復(fù)到某個(gè)時(shí)間點(diǎn)的數(shù)據(jù)狀態(tài)。

2.3. 存儲(chǔ)位置

默認(rèn)存儲(chǔ)在 MySQL 數(shù)據(jù)目錄下的mysql-bin.xxxxxx文件中。

binlog是通過追加的方式進(jìn)行寫入的，可以通過max_binlog_size參數(shù)設(shè)置每個(gè)binlog文件的大小，當(dāng)文件大小達(dá)到給定值之后，會(huì)生成新的文件來(lái)保存日志。

如下圖所示：

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

2.4. 格式

在MySQL 5.7.7之前，默認(rèn)的格式是STATEMENT，MySQL 5.7.7之后，默認(rèn)值是ROW。日志格式通過binlog-format指定。

可使用以下命令，查看到：

• Row-based：記錄每一行的變更（5.7.7后默認(rèn)）。
• Statement-based：記錄 SQL 語(yǔ)句（5.7.7之前默認(rèn)）。
• Mixed：混合模式。

三種不同格式的優(yōu)缺點(diǎn)：

配置如下：

[mysqld]
log-bin=mysql-bin  # 開啟 binlog
server-id=1        # 主庫(kù) ID

2.5、binlog刷盤時(shí)機(jī)

對(duì)于InnoDB存儲(chǔ)引擎而言，只有在事務(wù)提交時(shí)才會(huì)記錄biglog，此時(shí)記錄還在內(nèi)存中，那么biglog是什么時(shí)候刷到磁盤中的呢？

mysql通過sync_binlog參數(shù)控制biglog的刷盤時(shí)機(jī)，取值范圍是0-N：

• 0：不去強(qiáng)制要求，由系統(tǒng)自行判斷何時(shí)寫入磁盤；
• 1：每次commit的時(shí)候都要將binlog寫入磁盤；
• N：每N個(gè)事務(wù)，才會(huì)將binlog寫入磁盤。

從上面可以看出，sync_binlog最安全的是設(shè)置是1，這也是MySQL 5.7.7之后版本的默認(rèn)值。

設(shè)置一個(gè)大一些的值可以提升數(shù)據(jù)庫(kù)性能，因此實(shí)際情況下也可以將值適當(dāng)調(diào)大，犧牲一定的一致性來(lái)獲取更好的性能。

3、redlog

持久性：只要事務(wù)提交成功，那么對(duì)數(shù)據(jù)庫(kù)做的修改就被永久保存下來(lái)了，不可能因?yàn)槿魏卧蛟倩氐皆瓉?lái)的狀態(tài)。

那么mysql是如何保證一致性的呢？

在每次事務(wù)提交的時(shí)候，將該事務(wù)涉及修改的數(shù)據(jù)頁(yè)全部刷新到磁盤中。但是這么做會(huì)有嚴(yán)重的性能問題。

主要體現(xiàn)在兩個(gè)方面：

• 因?yàn)?code>Innodb是以頁(yè)為單位進(jìn)行磁盤交互的，而一個(gè)事務(wù)很可能只修改一個(gè)數(shù)據(jù)頁(yè)里面的幾個(gè)字節(jié)，這個(gè)時(shí)候?qū)⑼暾臄?shù)據(jù)頁(yè)刷到磁盤的話，太浪費(fèi)資源了！
• 一個(gè)事務(wù)可能涉及修改多個(gè)數(shù)據(jù)頁(yè)，并且這些數(shù)據(jù)頁(yè)在物理上并不連續(xù)，使用隨機(jī)IO寫入性能太差！

因此使用redo log，就是只記錄事務(wù)對(duì)數(shù)據(jù)頁(yè)做了哪些修改，這樣就能完美地解決性能問題了(相對(duì)而言文件更小并且是順序IO)。

關(guān)于流程如下圖所示：

3.1、原理及流程

redo log包括兩部分：一個(gè)是內(nèi)存中的日志緩沖(redo log buffer)，另一個(gè)是磁盤上的日志文件(redo logfile)。

• redolog（Redo Log）是InnoDB 存儲(chǔ)引擎的事務(wù)日志，記錄事務(wù)對(duì)數(shù)據(jù)頁(yè)的物理修改。
• 用于保證事務(wù)的持久性（Durability），防止數(shù)據(jù)丟失。

mysql每執(zhí)行一條DML語(yǔ)句，先將記錄寫入redo log buffer，后續(xù)某個(gè)時(shí)間點(diǎn)再一次性將多個(gè)操作記錄寫到redo log file。這種先寫日志，再寫磁盤的技術(shù)就是MySQL里經(jīng)常說(shuō)到的WAL(Write-Ahead Logging)技術(shù)。

在計(jì)算機(jī)操作系統(tǒng)中，用戶空間(user space)下的緩沖區(qū)數(shù)據(jù)一般情況下是無(wú)法直接寫入磁盤的，中間必須經(jīng)過操作系統(tǒng)內(nèi)核空間(kernel space)緩沖區(qū)(OS Buffer)。

因此，redo log buffer寫入redo logfile實(shí)際上是先寫入OS Buffer，然后再通過系統(tǒng)調(diào)用fsync()將其刷到redo log file中。

過程如下：

可以通過innodb_flush_log_at_trx_commit參數(shù)配置將redo log buffer寫入redo log file的時(shí)機(jī)，

3.2、核心作用

如下圖所示：

事務(wù)持久化

• 事務(wù)提交時(shí)，先將修改寫入 redolog，再異步刷盤到數(shù)據(jù)文件。

崩潰恢復(fù)（Crash Recovery）

• 數(shù)據(jù)庫(kù)重啟時(shí)，通過 redolog 恢復(fù)未落盤的數(shù)據(jù)。

3.3、存儲(chǔ)位置

• 默認(rèn)存儲(chǔ)在ib_logfile0和ib_logfile1文件中（InnoDB 專屬）。

3.4、特性

• 固定大小：默認(rèn) 4GB（可通過innodb_log_file_size配置）。
• 循環(huán)寫入：當(dāng)文件寫滿時(shí)，覆蓋舊日志。
• 物理日志：記錄的是數(shù)據(jù)頁(yè)的物理修改（如某個(gè)頁(yè)的某個(gè)偏移量被修改）。

redo log實(shí)際上記錄數(shù)據(jù)頁(yè)的變更，而這種變更記錄是沒必要全部保存，因此redo log實(shí)現(xiàn)上采用了大小固定，循環(huán)寫入的方式，當(dāng)寫到結(jié)尾時(shí)，會(huì)回到開頭循環(huán)寫日志。

啟動(dòng)innodb的時(shí)候，不管上次是正常關(guān)閉還是異常關(guān)閉，總是會(huì)進(jìn)行恢復(fù)操作。因?yàn)?code>redo log記錄的是數(shù)據(jù)頁(yè)的物理變化，因此恢復(fù)的時(shí)候速度比邏輯日志(如binlog)要快很多。

配置如下：

[mysqld]
innodb_log_file_size = 1G  # 設(shè)置 redolog 大小
innodb_flush_log_at_trx_commit = 1  # 事務(wù)提交時(shí)立即刷盤

關(guān)于redlog buffer的內(nèi)存結(jié)構(gòu)如下：

關(guān)于redlog buffer到redo log file持久化的流程如下圖：

關(guān)于binlog和redlog的區(qū)別聯(lián)系如圖所示：

3.5、聯(lián)系

binlog 與 redolog的聯(lián)系。

1. 事務(wù)提交流程中的協(xié)作

• 事務(wù)執(zhí)行：修改數(shù)據(jù)頁(yè)（內(nèi)存中）。
• 寫入 redolog：事務(wù)提交時(shí)，將修改寫入 redolog（確保持久性）。
• 寫入 binlog：事務(wù)提交后，將修改寫入 binlog（用于主從復(fù)制和恢復(fù)）。

關(guān)鍵區(qū)別：

• redolog 是 InnoDB 的事務(wù)日志，確保事務(wù)的原子性和持久性。
• binlog 是 MySQL 的邏輯日志，用于主從復(fù)制和數(shù)據(jù)恢復(fù)。

2. 數(shù)據(jù)一致性保障

• redolog：確保事務(wù)的修改不會(huì)因宕機(jī)丟失。
• binlog：確保主從節(jié)點(diǎn)的數(shù)據(jù)一致性。

3. 崩潰恢復(fù)流程

• 恢復(fù) redolog：數(shù)據(jù)庫(kù)重啟時(shí)，通過 redolog 恢復(fù)未落盤的事務(wù)。
• 恢復(fù) binlog：通過 binlog 恢復(fù)到某個(gè)時(shí)間點(diǎn)（需結(jié)合 redo log）。

3.6、應(yīng)用場(chǎng)景

1. 主從復(fù)制

• 主庫(kù)：將 binlog 發(fā)送給從庫(kù)。
• 從庫(kù)：重放 binlog 實(shí)現(xiàn)數(shù)據(jù)同步。

2. 數(shù)據(jù)恢復(fù)

• 基于 binlog：恢復(fù)到某個(gè)時(shí)間點(diǎn)（如誤刪數(shù)據(jù)后回滾）。
• 基于 redolog：恢復(fù)未落盤的事務(wù)（數(shù)據(jù)庫(kù)崩潰后自動(dòng)恢復(fù)）。

3. 數(shù)據(jù)一致性

• binlog + redolog：共同保證 ACID 中的D（Durability）和主從一致性。

區(qū)別如下所示：

4、undolog

4.1、介紹

原子性底層就是通過undo log實(shí)現(xiàn)的。undo log主要記錄了數(shù)據(jù)的邏輯變化，比如一條INSERT語(yǔ)句，對(duì)應(yīng)一條DELETE的undo log，對(duì)于每個(gè)UPDATE語(yǔ)句，對(duì)應(yīng)一條相反的UPDATE的undo log，這樣在發(fā)生錯(cuò)誤時(shí)，就能回滾到事務(wù)之前的數(shù)據(jù)狀態(tài)。

同時(shí)，undo log也是MVCC(多版本并發(fā)控制)實(shí)現(xiàn)的關(guān)鍵。

4.2、流程

關(guān)于undolog的流程如下圖所示：

4.3、日志結(jié)構(gòu)

關(guān)于undolog的日志結(jié)構(gòu)如下圖所示：

5、常見問題與優(yōu)化

1. binlog 導(dǎo)致磁盤空間不足

• 問題：binlog 無(wú)限增長(zhǎng)，占用磁盤空間。
• 解決方案：

-- 查看 binlog 狀態(tài)
SHOW VARIABLES LIKE 'expire_logs_days';

-- 設(shè)置自動(dòng)清理（單位：天）
SET GLOBAL expire_logs_days = 7;

2. redolog 刷盤性能問題

問題：innodb_flush_log_at_trx_commit=1會(huì)降低性能。

優(yōu)化建議：

• 生產(chǎn)環(huán)境：保持默認(rèn)值1（保證數(shù)據(jù)安全）。
• 性能優(yōu)先場(chǎng)景：可設(shè)置為2（每秒刷盤一次，風(fēng)險(xiǎn)略高）。

總結(jié)

MySQL中的binlog、redolog和undolog日志類型，包括它們的作用、格式和刷盤機(jī)制。binlog主要用于主從復(fù)制和數(shù)據(jù)恢復(fù)，redolog確保事務(wù)的持久性，而undolog則實(shí)現(xiàn)了事務(wù)的原子性。

通過理解這些日志機(jī)制，可以更好地保障數(shù)據(jù)庫(kù)的安全性和性能。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論