前言

在 MySQL 中，有三大關鍵的 Log 機制：Undo Log、Redo Log、Bin Log。這三個 Log 分別負責「交易回滾」、「當機恢復」、「主從複製」等核心功能。搞懂它們，就能理解 MySQL 如何保證資料的一致性與持久性！

歌曲

🎵 點擊展開觀看歌曲

三大 Log 一圖流快速理解

不同情境下的 Query 中，Log 扮演的角色

情境思考：哪些操作會用到 Log？

思考一下，以下兩個情境分別會用到哪些 Log？

1
2
3
4
5
-- 情境 1：SELECT 查詢
SELECT * FROM students WHERE id = 3;

-- 情境 2：資料異動
UPDATE students SET score = 90 WHERE id = 3;

答案揭曉

SELECT 查詢與 Log 的關係

SELECT 查詢不會寫入任何 Log，但根據隔離級別，可能會讀取 Undo Log 來實現 MVCC（多版本併發控制）。

Mysql 隔離級別是否讀取 Undo Log說明
Read Uncommitted❌ 不使用直接讀取最新資料，不在乎是否已 Commit
Read Committed✅ 使用每次查詢都讀取「已 Commit」的最新版本
Repeatable Read✅ 使用整個 Transaction 期間讀取「開始時」的快照版本
Serializable❌ 不使用透過鎖機制排隊執行，不需要讀取歷史版本

重點：SELECT 不會「寫入」Log，但可能「讀取」Undo Log

資料異動（INSERT / UPDATE / DELETE）

當「資料異動時」（Insert、Update、Delete）都會使用到這三個 Log！

三個 Log 的分工如下：

Log 類型核心目的一句話說明
Undo Log回滾 + MVCCCommit 失敗時復原資料，同時支援其他 Transaction 讀取舊版本
Redo Log當機恢復Server 當機後，透過 Redo Log 恢復已 Commit 但尚未寫入磁碟的資料
Bin Log複製 + 備份主從複製的基礎，也用於時間點資料恢復

三大 Log 詳解

Undo Log（回滾日誌）

核心目的： Transaction 失敗時的「後悔藥」，同時支援 MVCC 讓其他 Transaction 讀取舊版本資料。

項目說明
目的1. Commit 失敗後復原資料
2. 支援 MVCC，讓其他 Transaction 讀取歷史版本
記錄內容舊值，例如 score 原本是 60，就記錄 score = 60
清空時機Insert：Commit 後可立即清空
Update/Delete：由 Purge Thread 判斷無 Transaction 需要後才清理
儲存位置Undo Tablespaces（undo_001、undo_002 等檔案）

為什麼 Insert 和 Update/Delete 的清空時機不同？

1
2
3
4
5
Insert：新資料不存在「舊版本」，其他 Transaction 不需要讀取
        → Commit 後可以立即清空

Update/Delete：其他 Transaction 可能正在讀取舊版本（MVCC）
              → 必須等到沒有任何 Transaction 需要該版本後才能清理

Insert 的 Undo Log：Commit 後立即清空

Update/Delete 的 Undo Log：由 Purge Thread 異步清理，可能保留數小時

Redo Log（重做日誌）

核心目的： 實現 WAL（Write-Ahead Logging）= 先寫日誌再寫資料，確保 Server 當機後資料不丟失。

項目說明
目的避免 Commit 後、資料還沒寫入硬碟時 Server 當機導致資料遺失
記錄內容記錄「對哪個位置做了什麼修改」（例如：把第 5 頁的第 100 個位置改成 90）
清空時機循環寫入，滿了會從頭覆蓋舊的 Log
儲存位置ib_logfile0、ib_logfile1 等檔案

WAL 是什麼？為什麼需要 Redo Log？

WAL = Write-Ahead Logging = 先寫日誌再寫資料

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
【沒有 Redo Log 的問題】

1. Client 執行 UPDATE
2. 修改 Buffer Pool 中的資料（在記憶體裡）
3. 回傳 Commit 成功 ✅
4. 背景程式「之後」才把資料寫入硬碟
5. 如果在步驟 4 之前當機... 💥
   → 記憶體資料消失，但 Client 已收到成功
   → 資料不見了！❌

【有 Redo Log 的保護】

1. Client 執行 UPDATE
2. 修改 Buffer Pool 中的資料（在記憶體裡）
3. 同時寫入 Redo Log 到硬碟（速度很快）
4. 回傳 Commit 成功 ✅
5. 如果在資料寫入硬碟之前當機... 💥
   → 重啟後讀取 Redo Log，把資料補回來
   → 資料恢復！✅

Redo Log 將「隨機寫入」轉換為「順序寫入」，提升效能

當機重啟後 MySQL 會自動透過 Redo Log 恢復資料

Bin Log（二進位日誌）

核心目的： 主從複製的基礎，也用於時間點資料恢復（Point-in-Time Recovery）。

項目說明
目的1. 主從複製（讀寫分離）
2. 時間點資料恢復
記錄內容記錄 SQL 語句或每一列的變化（取決於設定）
清空時機不清空！追加寫入，檔案滿了換新檔案
儲存位置mysql-bin.000001、mysql-bin.000002…

Redo Log vs Bin Log 差異

項目Redo LogBin Log
層級InnoDB 引擎層MySQL Server 層
記錄類型記錄「頁面哪裡改了什麼」記錄「SQL 語句或資料變化」
用途當機恢復（自動）主從複製 + 時間點恢復（手動）
寫入時機Transaction 執行中持續寫入Commit 時一次寫入
生命週期循環覆蓋持續追加

Redo Log：InnoDB 的「保險箱」，保護單機資料

Bin Log：MySQL 的「日記本」，支援複製與備份

三大 Log 能否被關閉？

重要觀念： 並非所有 Log 都可以被設定開關！

Log 類型能否關閉說明
Undo Log❌ 無法關閉InnoDB 核心機制，Transaction 回滾與 MVCC 必須依賴它
Redo Log❌ 無法關閉InnoDB 核心機制，WAL 策略與當機恢復必須依賴它
Bin Log✅ 可以關閉透過 log_bin 參數設定開關（預設在 MySQL 8.0+ 為開啟）
1
2
3
4
5
6
-- 查看 Bin Log 是否開啟
SHOW VARIABLES LIKE 'log_bin';

-- 關閉 Bin Log（需在 my.cnf 設定後重啟）
-- [mysqld]
-- skip-log-bin

為什麼只有 Bin Log 可以關閉？

  • Undo Log / Redo Log：屬於 InnoDB 儲存引擎層，是保證 ACID 特性的核心機制，關閉會破壞資料一致性
  • Bin Log：屬於 MySQL Server 層，主要用於主從複製與備份，單機環境下不需要複製功能時可以關閉

MySQL 完整架構圖

MySQL 更詳細的運作原理圖

架構圖中的其他知識點補充

Change Buffer

目的： 減少 Disk I/O，針對非唯一索引的寫入進行優化。

項目說明
對象Non-Unique 的 Secondary Index
原理將變更先暫存在 Buffer，之後再批次合併寫入
預設狀態MySQL 5.5~8.3：開啟
MySQL 8.4：關閉
MySQL 9.0+：開啟
適用時機HDD 環境下效益較大，SSD 環境可考慮關閉

什麼是非唯一索引（Non-Unique Index）？

索引類型分類：

  • 唯一索引（Unique Index）：包含 Primary Key 與 Unique Key，確保欄位值不重複
  • 非唯一索引（Non-Unique Index）：一般的 Secondary Index，允許欄位值重複

延伸思考：為什麼只有「非唯一索引」才能使用 Change Buffer？

核心原因： 唯一性檢查需要立即讀取資料頁，無法延遲寫入。

1
2
3
4
5
6
7
8
9
10
【唯一索引的寫入流程】
1. INSERT INTO students (id, email, score) VALUES (1, '[email protected]', 90);
2. 必須先檢查 email 是否已存在 → 需要讀取索引頁
3. 既然已經讀取了，就直接寫入，不需要 Change Buffer

【非唯一索引的寫入流程】
1. INSERT INTO students (id, email, score) VALUES (1, '[email protected]', 90);
2. score 欄位允許重複，不需要檢查 → 不用讀取索引頁
3. 將變更暫存在 Change Buffer，之後批次寫入
4. 減少隨機 I/O，提升效能

非唯一索引：可延遲寫入，減少 Disk I/O

唯一索引：必須立即檢查，無法使用 Change Buffer

Background Threads

目的： 職責拆分，適應高併發場景。

Thread 類型職責
IO Threads處理讀寫 I/O 請求
Purge Thread清理已不需要的 Undo Log
Page Cleaner將修改過的資料寫入硬碟
Log Writer將 Redo Log 從記憶體寫入硬碟
1
2
-- 查看當前 Thread 設定
SHOW VARIABLES LIKE 'innodb_%_threads';

範例輸出：

VariableValue
innodb_ddl_threads4
innodb_log_writer_threadsON
innodb_parallel_read_threads4
innodb_purge_threads1
innodb_read_io_threads5
innodb_write_io_threads4

Log Buffer 監控指令

1
2
3
4
5
6
7
8
9
10
11
-- ========== Binlog Buffer ==========
SHOW STATUS LIKE 'Binlog_cache_use';       -- 使用緩存的次數
SHOW STATUS LIKE 'Binlog_cache_disk_use';  -- 緩存不夠用而寫入臨時檔的次數
SHOW VARIABLES LIKE 'binlog_cache_size';   -- 緩存大小設定

-- ========== Redo Log Buffer ==========
SHOW VARIABLES LIKE 'innodb_log_buffer_size';  -- Buffer 大小
SHOW STATUS LIKE 'Innodb_log_waits';           -- 因 Buffer 不夠而等待的次數

-- ========== Undo Log / Buffer Pool ==========
SHOW ENGINE INNODB STATUS;  -- 查看整體狀況

調優建議：

  • 如果 Binlog_cache_disk_use 很高 → 增加 binlog_cache_size
  • 如果 Innodb_log_waits 很高 → 增加 innodb_log_buffer_size

Adaptive Hash Index（AHI）

目的： 透過 Hash Map 加速熱點資料的查詢。

項目說明
原理MySQL 自動為頻繁查詢的資料建立 Hash 索引
預設狀態MySQL 8.0：開啟
MySQL 8.4+：關閉
適用場景有明顯熱點資料的 OLTP 場景
1
2
3
4
-- 查看 AHI 狀態
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';

-- 補充說明：主要針對熱點資料使用，只是現在高併發場景下的熱點資料都會搭配 NoSQL DB 來處理，AHI 再使用則佔用 Buffer，也需要考慮到 Lock 等各類問題，所以官方後續預設就設為關閉。

延伸比較：其他資料庫的 Log 機制

了解 MySQL 的三大 Log 後，我們來看看其他資料庫是如何設計的。

PostgreSQL：單一 WAL 取代三大 Log

PostgreSQL 架構流程圖

PostgreSQL 沒有 Undo Log、Redo Log、Bin Log！ 它使用單一的 WAL（Write-Ahead Log） 來處理所有功能。

項目MySQLPostgreSQL
回滾機制Undo LogWAL + MVCC（舊版本保留在原表）
當機恢復Redo LogWAL
複製機制Bin LogWAL
連線模型多線程（Thread）多進程（Process）
緩存管理Buffer Pool（InnoDB 獨立管理）Shared Buffer（與 OS 共同管理）

MySQL vs PostgreSQL 的 Thread/Process 差異

MySQL 的風險： 如果某個 Thread 因為爛 SQL 導致記憶體飆升，觸發 OS OOM Killer（Linux），整個 MySQL Process 會被終止，影響所有連線。

PostgreSQL 的優勢： 每個連線是獨立 Process，單一連線出問題不會影響其他連線，但代價是資源消耗較高。

ClickHouse：OLAP 資料庫的不同設計

ClickHouse 架構流程圖

ClickHouse 是 OLAP 資料庫，設計理念與 OLTP（MySQL、PostgreSQL）完全不同。

項目MySQL（OLTP）ClickHouse（OLAP）
儲存方式Row-basedColumn-based
適合場景高併發小查詢海量資料分析
Query Cache8.0 後移除有（v23.1+，預設關閉）
執行方式一個連線 = 一個 Thread⚠️（需要特別注意）一個 SQL = 所有 CPU 一起處理
寫入方式直接寫入指定位置先寫臨時區，背景 Merge

ClickHouse 的核心特點

Column-based 儲存：分析查詢只需讀取相關欄位，速度極快

Vectorized 執行：批次處理資料，大幅提升運算效率

MergeTree 引擎：寫入先暫存，背景合併，避免隨機 I/O

不適合高併發：一個查詢會佔用所有 CPU

不適合頻繁更新：設計上偏向「追加寫入」

Milvus：向量資料庫的架構

Milvus 架構流程圖

Milvus 是向量資料庫，專門處理 AI Embedding 的相似度搜尋，與傳統資料庫的設計完全不同。

項目MySQL（OLTP）Milvus（Vector DB）
查詢方式精確查詢（WHERE id = 3）相似度查詢（找最像的 Top K）
索引類型B+ Tree、HashIVF_FLAT、HNSW 等向量索引
比對方式值是否相等向量距離（計算相似程度）
適合場景電商訂單、用戶資料推薦系統、語意搜尋、RAG

常見向量距離算法：

  • 歐式距離（L2）：計算兩點間的直線距離，適合絕對位置相似度比較
  • 內積（IP）：計算向量夾角的餘弦值乘以長度，適合推薦系統
  • 餘弦相似度（Cosine）：只看方向不看長度，適合文字語意比較

思考：假設使用 MySQL 儲存向量，程式端需自行實作相似度計算，可行嗎？

1
2
3
4
5
6
7
8
9
10
// PHP + Laravel 為例
$this->jobInfoRepository->chunk(1000, function ($jobInfos) use ($embedding, &$results) {
    foreach ($jobInfos as $jobInfo) {
        // 每一筆都要計算餘弦相似度（cosineSimilarity 為自定義方法）
        $jobInfo->similarity = $this->cosineSimilarity(
            json_decode($jobInfo->embedding, true),
            $embedding
        );
    }
});

可行，但是這種做法會產生以下問題：

  1. CPU 負擔大：每次查詢都要對所有資料計算相似度
  2. 網路頻寬大：需將所有向量資料從 DB 傳到應用層
  3. 效能低落：無法利用索引，時間複雜度為 O(n)（為什麼失效，請具體查看下面的「為什麼向量資料庫不能用 B+ Tree？」

延伸思考：為什麼向量資料庫不能用 B+ Tree？

維度災難（Curse of Dimensionality）： 向量通常是 768 維甚至更高，傳統索引在高維空間下完全失效。

舉例來說，一個句子經過 Embedding 模型轉換後，會變成這樣的向量：

1
"今天天氣很好" → [0.123, -0.456, 0.789, 0.234, ..., -0.567]  // 共 768 個維度

B+ Tree 的排序邏輯是「單一維度比大小」，但向量有 768 個維度，無法決定誰大誰小：

向量 A向量 B誰比較大？
[0.1, 0.9, ...][0.8, 0.2, ...]第一維 B 大，第二維 A 大，無法排序

因此向量資料庫必須使用專門的索引結構（如 HNSW、IVF），透過「近似最近鄰搜尋（ANN）」來快速找到相似向量。

應用場景對照表

1
2
3
4
5
6
7
8
9
┌──────────────────┬──────────────────────────────────────────────────┐
│ 資料庫類型        │ 適合場景                                          │
├──────────────────┼──────────────────────────────────────────────────┤
│ OLTP（MySQL）    │ 電商訂單、用戶資料、交易紀錄                        │
│ OLAP（ClickHouse）│ 日誌分析、報表統計、用戶行為分析                    │
│ Vector（Milvus） │ 語意搜尋、推薦系統、AI 聊天機器人知識庫檢索          │
└──────────────────┴──────────────────────────────────────────────────┘

備註：如果向量資料量不大，PostgreSQL 的 pgvector 擴充也足夠應付。

總結

三大 Log 的核心價值：

  • Undo Log：交易的「後悔藥」+ MVCC 的基礎
  • Redo Log：當機恢復的「保險箱」（WAL 策略）
  • Bin Log：主從複製的「傳輸帶」+ 時間點恢復的「時光機」
Log 類型一句話總結
Undo LogCommit 失敗能回滾，其他 Transaction 能讀舊版本
Redo Log先寫 Log 再寫資料，當機後能恢復
Bin Log主從複製靠它傳資料，備份恢復靠它回到過去

延伸閱讀

MySQL 官方文件：

相關文章：