前言

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

SQL 的更新流程

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

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

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

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

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

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

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

Redo log 重做日志

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

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

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

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

寫 redo log 的流程如下：

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

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

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

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

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

crash-safe 還有個(gè)重要的日志——Binlog 日志。

Binlog 歸檔日志

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

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

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

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

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

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

redo log 和 binlog 設(shè)置

查看 redo log 和 binlog 設(shè)置

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

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

設(shè)置 redo log 和 binlog 配置

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

[mysqld]
sync_binlog=1
innodb_flush_log_at_trx_commit = 1

或者，也可以在 MySQL 運(yùn)行時(shí)動(dòng)態(tài)設(shè)置，但是這種變更只對(duì)新的會(huì)話有效，對(duì)于已經(jīng)存在的會(huì)話，該設(shè)置直到會(huì)話結(jié)束才會(huì)生效。

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

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

最新評(píng)論