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

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

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

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

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

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

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

1.1、分類

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

1.對于 InnoDB：

ibdata 文件：

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

.ibd 文件：

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

2.對于 MyISAM：

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

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

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

3. 設置

使用共享表空間:

• 只需將innodb_file_per_table設置為0或不設置（默認值）。

使用獨立表空間:

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

[mysqld]
innodb_file_per_table=1

這將允許每個 InnoDB 表都有其獨立的.ibd文件。

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

如下圖所示：

獨立表空間文件（.ibd）,每個獨立表在磁盤上對應一個.ibd文件（test_table.ibd）

主要包括以下部分：

1.表空間頭（Header）

存儲表空間的基本信息，如：

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

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

2.段（Segment）

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

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

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

索引段（Index Segment）：

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

回滾段（Rollback Segment）：

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

3.區(qū)（Extent）

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

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

4.頁（Page）

頁是 InnoDB 的最小存儲單元，大小默認為16KB。

如下圖所示：

頁的類型取決于其所屬的段：

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

5.空閑列表（Free List）

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

小結(jié)

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

如下圖所示：

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

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

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

具體如下：

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

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

2.二級索引（輔助索引）

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

3.事務與 MVCC 機制

Undo Log（回滾段中的頁）：

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

版本鏈（Version Chain）：

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

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

2、binlog

2.1. 定義

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

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

2.2. 核心作用

1.主從復制（Replication）

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

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

通過 binlog 恢復到某個時間點的數(shù)據(jù)狀態(tài)。

2.3. 存儲位置

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

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

如下圖所示：

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之前，默認的格式是STATEMENT，MySQL 5.7.7之后，默認值是ROW。日志格式通過binlog-format指定。

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

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

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

配置如下：

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

2.5、binlog刷盤時機

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

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

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

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

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

3、redlog

持久性：只要事務提交成功，那么對數(shù)據(jù)庫做的修改就被永久保存下來了，不可能因為任何原因再回到原來的狀態(tài)。

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

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

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

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

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

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

3.1、原理及流程

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

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

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

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

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

過程如下：

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

3.2、核心作用

如下圖所示：

事務持久化

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

崩潰恢復（Crash Recovery）

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

3.3、存儲位置

• 默認存儲在ib_logfile0和ib_logfile1文件中（InnoDB 專屬）。

3.4、特性

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

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

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

配置如下：

[mysqld]
innodb_log_file_size = 1G  # 設置 redolog 大小
innodb_flush_log_at_trx_commit = 1  # 事務提交時立即刷盤

關(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. 事務提交流程中的協(xié)作

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

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

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

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

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

3. 崩潰恢復流程

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

3.6、應用場景

1. 主從復制

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

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

• 基于 binlog：恢復到某個時間點（如誤刪數(shù)據(jù)后回滾）。
• 基于 redolog：恢復未落盤的事務（數(shù)據(jù)庫崩潰后自動恢復）。

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

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

區(qū)別如下所示：

4、undolog

4.1、介紹

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

同時，undo log也是MVCC(多版本并發(fā)控制)實現(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 導致磁盤空間不足

• 問題：binlog 無限增長，占用磁盤空間。
• 解決方案：

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

-- 設置自動清理（單位：天）
SET GLOBAL expire_logs_days = 7;

2. redolog 刷盤性能問題

問題：innodb_flush_log_at_trx_commit=1會降低性能。

優(yōu)化建議：

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

總結(jié)

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

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

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

最新評論