前言

在這篇文章中，小魚將介紹更新語句的執(zhí)行流程，從中我們又能學到什么呢？

SQL 的更新流程

我們先創(chuàng)建一張表作為演示表，作為演示表只需要一個主鍵、一個額外字段就可以了。下面是演示表的創(chuàng)建語句：

CREATE TABLE test(ID int primary key, age int);

如果我們需要將 ID=2 目標值自增 1，更新的 SQL 語句如下。

UPDATE test SET age=age+1 WHERE ID=2;

更新目標值時，得先查找的該行數(shù)據(jù)，所以也會執(zhí)行SQL查詢語句的流程。

• 在執(zhí)行語句前，連接上數(shù)據(jù)庫（連接器）。
• 因為是更新語句，涉及到更新目標表的查詢緩存會失效，所以該語句會把 test 表所有緩存結果都清空。
• 分析器通過詞法分析和語法分析知道該語句為更新語句。
• 優(yōu)化器決定要使用 ID 索引。
• 最后，執(zhí)行器負責更新的具體執(zhí)行，根據(jù)索引找到目標行，再執(zhí)行更新。

更新語句流程與查詢語句流程不一樣的地方在于日志模塊，更新語句涉及到兩個十分重要的日志模塊——redo log（重做日志）和 binlog（歸檔日志）。

Redo log 重做日志

??Redo Log??稱為重做日志，提供再寫入操作，恢復提交事務修改的頁操作，用來保證事務的持久性。

mysql 數(shù)據(jù)是被持久化寫進磁盤的，每次更新也需要找到目標數(shù)據(jù)，在進行修改，每次更新都執(zhí)行一遍該操作，這個過程的 IO 成本是比較高的。

為了解決這個問題，MySQL 采用了先寫日志，空余時間再寫磁盤的思路來提升更新效率。即是 WAL 技術（預寫式日志，WAL 的全稱是 Write-Ahead Logging）。

具體來說，當有更新語句執(zhí)行的時候，InnoDB 引擎會先把更新記錄寫到 redo log 日志里，并更新內存，這個時候已經(jīng)完成更新（內存上），實際磁盤上的數(shù)據(jù)尚未更新。等適當?shù)臅r候（通常是系統(tǒng)空閑的時候），InnoDB 引擎會將這個操作記錄（redo log 中記錄的更新語句）更新到磁盤。

寫 redo log 的流程如下：

• 先將目標原始數(shù)據(jù)從磁盤中讀入內存中來，修改數(shù)據(jù)的內存拷貝。
• 生成一條重做日志并寫入 Redo Log Buffer，記錄的是數(shù)據(jù)被修改后的值。
• 當事務 commit 時，將 Redo Log Buffer 中的內容采用追加寫的方式刷新到 Redo Log File。
• 定期將內存中修改的數(shù)據(jù)刷新到數(shù)據(jù)文件（磁盤）中。

這樣做還有一個問題，InnoDB 的 redo log 日志的大小是固定的，它設計的是循環(huán)的，即日志文件寫滿后會覆蓋掉最先的記錄（從頭開始寫，寫到末尾就又回到開頭循環(huán)寫）。

• write pos：當前記錄的位置，一邊寫一邊后移，當寫到第 3 號文件末（末尾）時會回到 0 號文件（開頭）開頭。
• checkpoint：當前要擦除的位置，同樣是往后推移并且循環(huán)的，擦除記錄前要把記錄更新到數(shù)據(jù)文件（更新到磁盤里）。
• write pos 和 checkpoint 之間：redo log 日志文件還空著的部分，可以用來記錄新的操作。
  • 如果 write pos 追上 checkpoint，表示 redo log 日志文件寫滿了，此時不能再執(zhí)行新的更新操作，會將記錄寫入數(shù)據(jù)文件，并執(zhí)行擦除記錄，推進 checkpoint 位置。

試想：對于已經(jīng)寫入 redo log 的記錄，在數(shù)據(jù)庫異常重啟后，能否恢復？

mysql 重啟后，已經(jīng)寫入 redo log 的記錄不會丟失，這個能力也稱為 crash-safe。

crash-safe 還有個重要的日志——Binlog 日志。

Binlog 歸檔日志

MySQL 架構分為 Server 層和存儲引擎層，redo log 是存儲引擎層產(chǎn)生的日志，而 server 層也有日志——Binlog 歸檔日志。

兩者的區(qū)別在于以下幾點：

• Redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 層產(chǎn)生的，任何引擎都存在該日志。
• Redo log 是循環(huán)寫的，空間固定會用完，用完即從頭開始寫。binlog 是追加寫，即 binlog 文件寫到一定大小后會新建日志文件，不會覆蓋掉以前的日志。
• redo log 會不斷記錄，而 binlog 只有在事務提交的時候才記錄。Redo log 是物理日志，詳細記錄了“在某個數(shù)據(jù)頁上做了什么修改”（包含事務的過程操作）；binlog 是邏輯日志，記錄的是語句的原始邏輯（對數(shù)據(jù)最終的影響）。

譬如：一個事務對表做10萬行的記錄插入，在事務執(zhí)行過程中，會一直不斷的往 Redo Log 順序寫，而這個過程 Binlog 不會記錄，直至這個事務提交的時候，才會寫入到  Binlog 文件中。

這兩份日志存在的意義就是實現(xiàn) crash-safe 能力。

這兩個日志文件結合起來，才真正實現(xiàn)了 crash-safe 能力，讓 MySQL 既能保證事務的 ACID 屬性，又能支持高效的數(shù)據(jù)復制和恢復能力。

redo log 和 binlog 設置

查看 redo log 和 binlog 設置

show variables like 'innodb_flush_log_at_trx_commit';
show variables like 'sync_binlog'

• innodb_flush_log_at_trx_commit：
  • 設置成 0，在提交事務時，InnoDB 不會立即觸發(fā)將緩存日志寫到磁盤文件的操作，而是每秒觸發(fā)一次緩存日志回寫磁盤操作，并調用操作系統(tǒng) fsync 刷新 IO 緩存。
  • 設置成 1 ，每次事務的 redo log 都直接持久化到磁盤，如此可以保證 MySQL 在異常重啟后數(shù)據(jù)不會丟失。
  • 設置成 2，在每個事務提交時，InnoDB 立即將緩存中的 redo 日志回寫到日志文件，但并不馬上調用 fsync 來刷新 IO 緩存，而是每秒只做一次磁盤 IO 緩存刷新操作。
• sync_binlog 這個參數(shù)設置成 1 的時候，表示每次事務的 binlog 都持久化到磁盤。對于需要高度數(shù)據(jù)持久性和不能承受數(shù)據(jù)丟失的系統(tǒng)，建議將 sync_binlog設置為 1。

設置 redo log 和 binlog 配置

可以在 MySQL 配置文件（通常是 my. Cnf 或 my. Ini）中設置這個變量。設置好后需要重啟 mysl，使得配置生效。

[mysqld]
sync_binlog=1
innodb_flush_log_at_trx_commit = 1

或者，也可以在 MySQL 運行時動態(tài)設置，但是這種變更只對新的會話有效，對于已經(jīng)存在的會話，該設置直到會話結束才會生效。

SET GLOBAL sync_binlog=1;
SET GLOBAL innodb_flush_log_at_trx_commit = 1;

以上就是詳解MySQL更新語句的執(zhí)行流程的詳細內容，更多關于MySQL更新語句的資料請關注腳本之家其它相關文章！

最新評論