一條SQL更新語句的執行過程解析

2022-05-20 19:00:39

一、執行過程

在SQL查詢語句執行的過程中，我們學習過 SQL 語句基本的執行鏈路，這裡我再把那張圖拿過來，你也可以先簡單看看這個圖回顧下。首先，可以確定的說，查詢語句的那一套流程，更新語句也是同樣會走一遍。

你執行語句前要先連線資料庫，這是聯結器的工作。

使用資料庫的第一步就是先連線到這個資料庫上，這時候作為接待的就是聯結器。聯結器負責跟使用者端建立連線、獲取許可權、維持和管理連線。連線命令：

mysql mysql -h主機地址 -u使用者名稱 －p

輸完命令之後，你就需要在互動對話裡面輸入密碼。雖然密碼也可以直接跟在 -p 後面寫在命令列中，但這樣可能會導致你的密碼洩露，不建議直接跟著輸入。

下圖都是我的電腦操作（mac+ mamp）。

如果輸入賬號或密碼錯誤會提示（1045）：

迴歸正題，我們還是從一個表的一條更新語句說起，下面是這個表的建立語句，這個表有一個主鍵 id 和一個字串型別的欄位 name 以及一個 decimal型別的欄位score ：

CREATE TABLE `s_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `name` varchar(255) NOT NULL DEFAULT '' COMMENT '名字',
  `score` decimal(5,2) NOT NULL DEFAULT '0.00' COMMENT '分數',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='學生分數表';

並加入幾條測試資料：

INSERT INTO `test`.`s_info` ( `name`, `score`) VALUES ( '張一', 80.00), ( '趙二', 90.00),( '王三', 100.00), ( '李四', 98.00),( '馬五', 87.00);

結果如下：

如果要將 id=4 這一行的值加 1，SQL 語句就會這麼寫：

update s_info set score=score+1 where id = 4;

執行結果如圖：

在一個表上有更新的時候，跟這個表有關的查詢快取會失效，所以這條語句就會把表上所有快取結果都清空。這也就是我們一般不建議使用查詢快取的原因。

分析器會通過詞法和語法解析知道這是一條更新語句。

優化器決定要使用 ID 這個索引。

執行器負責具體執行，找到這一行，然後更新。

與查詢流程不一樣的是，更新流程還涉及兩個重要的紀錄檔模組，redo log（重做紀錄檔，物理紀錄檔）和 binlog（歸檔紀錄檔，邏輯紀錄檔）。如果接觸過 MySQL，這兩個詞肯定是繞不過的。

二、紀錄檔模組

在說紀錄檔模組前，先說一下什麼是物理紀錄檔和邏輯紀錄檔。

物理紀錄檔：通俗的講，就是隻有"我"自己可以使用，別人無法共用我的"物理格式，私有化。

邏輯紀錄檔：可以給別的引擎使用，是所有引擎共用的。

1、物理紀錄檔redo log

redo log是 InnoDB 引擎特有的紀錄檔，又被稱為重寫紀錄檔，用來記錄事務操作的變化，記錄的是資料修改之後的值，不管事務提交是否成功，都會被記錄下來。它讓MySQL擁有了崩潰恢復能力。

比如MySQL範例掛了或宕機了，重啟時，InnoDB儲存引擎會使用redo log恢復資料，保證資料的永續性與完整性。

以常見的古代酒館掌櫃記賬舉例：

酒館掌櫃有一個黑板，賒賬的人少時就記在黑板上如果賒賬人多的話，由於黑板的空間大小有限，所以他又需要額外準備一本賬本，專門記錄所有賒賬的賬目。如果有人要賒賬的話，一般老闆有兩種做法：

（1）、開啟賬本，找到賒賬人的記錄，進行追加賒賬記錄；（2）、先把賒賬人的記錄寫到黑板上，待客流量少的時刻，再更新到賒賬賬目上。如果掌櫃使用第一種方法的話，每當有人要賒賬的話，首先他需要開啟厚厚的賬本，一頁一頁查詢該顧客的姓名，然後進行登記。你想一下，如果賒賬的人不多，掌櫃找賒賬人的記錄輕鬆點，如果賒賬本有好幾本的話，一本一本的找，掌櫃看的都頭疼。

在 MySQL裡也有這個問題。如果每一次的更新操作都需要寫進磁碟，然後磁碟也要找到對應的那條記錄，然後再更新，整個過程 IO 成本、查詢成本都很高。為了解決這個問題，MySQL 的設計者就用了類似酒店掌櫃粉板的思路來提升更新效率。

而黑板和賬本配合的整個過程，其實就是 MySQL 裡經常說到的 WAL （Write Ahead Logging）技術,它的關鍵點就是先寫紀錄檔，再寫磁碟，

當有一條記錄需要更新的時候，InnoDB 引擎就會先把記錄寫到 redo log（黑板）裡面，並更新記憶體，這個時候更新就算完成了。同時，InnoDB 引擎會在適當的時候，將這個操作記錄更新到磁碟裡面，而這個更新往往是在系統比較空閒的時候做，這就像打烊以後掌櫃做的事。

如果某天賒賬的特別多，粉板寫滿了，又怎麼辦呢？這個時候掌櫃只好放下手中的活兒，把黑板中的一部分賒賬記錄更新到賬本中，然後把這些記錄從黑板上擦掉，為記新賬騰出空間。

與此類似，InnoDB 的 redo log 是固定大小的，比如可以設定為一組 4 個檔案，每個檔案的大小是 1GB，那麼這塊“黑板”總共就可以記錄 4GB 的操作。從頭開始寫，寫到末尾就又回到開頭回圈寫，

如下圖所示：

write pos是當前記錄的位置，一邊寫一邊後移，寫到4號檔案末尾就回到1號檔案開頭。check point是當前要把記錄寫入到資料檔案的位置，也是後移並且迴圈的。

如果和上面老闆黑板場景結合起來描述的話，write pos就是老闆在黑板上順序寫入賒賬人記錄位置，對於mysql來說，write pos後移；而check point就是老闆把黑板上記錄寫入到賒賬本上的位置，當老闆寫入到賒賬本上後，就會把粉板上該記錄擦除掉，對於mysql來說，check point後移。

write pos 和 checkpoint 之間的是“黑板”上還空著的部分，可以用來記錄新的操作。如果 write pos 追上 check point，表示“黑板”滿了，這時候不能再執行新的更新，得停下來先擦掉一些記錄，把 check kpoint 推進一下。

有了 redo log，InnoDB 就可以保證即使資料庫發生異常重啟，之前提交的記錄都不會丟失，這個能力稱為crash-safe。

redo log的使用場景

用於系統奔潰恢復。

redolog設定

（1）、快取大小

innodb_log_buffer_size 預設大小 16MB。檢視相關設定sql：

SHOW GLOBAL VARIABLES LIKE '%innodb_log%';

結果如圖所示：

（2）、刷盤策略

提交事物寫入磁碟中，會根據這個設定的策略進行同步。

0 提交事物的時候不會把redo log buffer 裡的資料刷入磁碟。
1 提交事物的時候，必須把紀錄檔刷入磁碟中，可以嚴格保證資料不丟失（預設且推薦策略）。
2 提交事物的時候，先把紀錄檔刷入磁碟檔案對應的 os cache 快取裡，隔一段時間再把資料刷入磁碟。

檢視相關引數SQL：

SHOW GLOBAL VARIABLES LIKE '%sync_binlog%';

2、邏輯紀錄檔binlog

MySQL 整體來看，其實就有兩塊：一塊是 Server層，它主要做的是 MySQL 功能層面的事情；還有一塊是引擎層，負責儲存相關的具體事宜。 redo log 是 InnoDB 引擎特有的紀錄檔，而 Server 層也有自己的紀錄檔，稱為 binlog（歸檔紀錄檔）。

為什麼會有兩份紀錄檔呢？

最開始 MySQL 裡並沒有 InnoDB 引擎。MySQL 自帶的引擎是 MyISAM，但是 MyISAM 沒有 crash-safe 的能力，binlog 紀錄檔只能用於歸檔。而 InnoDB 是另一個公司以外掛形式引入 MySQL 的，既然只依靠 binlog 是沒有 crash-safe 能力的，所以 InnoDB 使用另外一套紀錄檔系統——也就是 redo log 來實現 crash-safe 能力。

bin log是mysql資料庫service層的，是所有儲存引擎共用的紀錄檔模組，它用於記錄資料庫執行的寫入性操作，也就是在事務commit階段進行記錄，以二進位制的形式儲存於磁碟中。

這兩種紀錄檔有以下不同：

	redo log	binlog
1	InnoDB 引擎特有的。	binlog 是 MySQL 的 Server 層實現的，所有引擎都可以使用。
2	redo log 是物理紀錄檔，記錄的是“在某個資料頁上做了什麼修改。”	binlog 是邏輯紀錄檔，並且由mysql資料庫的service層執行。記錄的是這個語句的原始邏輯，比如 “給 ID=4 這一行的 score 欄位加 1 ”。
3	redo log 是迴圈寫的，空間固定會用完。	binlog 是可以追加寫入的。可以通過 max_binlog_size 引數設定bin log檔案大小，當檔案大小達到某個值時，會生成新的檔案來儲存紀錄檔。

對這兩個紀錄檔的有了概念性理解，我們再來看執行器和 InnoDB 引擎在執行這個簡單的 update 語句時的內部流程。

（1）、執行器先找引擎取 ID=4 這一行。ID 是主鍵，引擎直接用樹搜尋找到這一行。如果 ID=4 這一行所在的資料頁本來就在記憶體中，就直接返回給執行器；否則，需要先從磁碟讀入記憶體，然後再返回，並且對這行記錄加獨佔鎖，把更新行記錄的舊值寫入 undo log（以便回滾）。
（2）、執行器拿到引擎給的行資料，把這個值加上 1，比如原來是 N，現在就是 N+1，得到新的一行資料，再呼叫引擎介面寫入這行新資料。
（3）、引擎將這行新資料更新到記憶體中，同時將這個更新操作記錄到 redo log 裡面，此時 redo log 處於 prepare狀態。然後告知執行器執行完成了，隨時可以提交事務。
（4）、執行器生成這個操作的 binlog，再按策略刷到 binlog檔案（磁碟中）。
（5）、執行器呼叫引擎的提交事務介面，引擎把剛剛寫入的 redo log 改成提交（commit）狀態，更新完成。

update 語句的執行流程圖如下，其中圖中黃色框表示是在 InnoDB 內部執行的，綠色框表示是在執行器中執行的。

重點看下最後三步，將 redo log 的寫入拆成了兩個步驟：prepare 和 commit，這就是"兩階段提交"。

兩階段提交

為什麼必須有“兩階段提交”呢？這是為了讓兩份紀錄檔之間的邏輯一致。

binlog 會記錄所有的邏輯操作，並且是採用“追加寫”的形式。如果你的 DBA 承諾說半個月內可以恢復，那麼備份系統中一定會儲存最近半個月的所有 binlog，同時系統會定期做整庫備份。

當需要恢復到指定的某一秒時，比如某天下午點發現上午11點有一次誤刪表，需要找回資料，那我們可以這麼做：

首先，找到最近的一次全量備份，如果你運氣好，可能就是昨天晚上的一個備份，從這個備份恢復到臨時庫；然後，從備份的時間點開始，將備份的 binlog 依次取出來，重放到中午誤刪表之前的那個時刻。這樣你的臨時庫就跟誤刪之前的線上庫一樣了，然後你可以把表資料從臨時庫取出來，按需要恢復到線上庫去。

為什麼紀錄檔需要“兩階段提交”？

由於 redo log 和 binlog 是兩個獨立的邏輯，如果不用兩階段提交，要麼就是先寫完 redo log 再寫 binlog，或者採用反過來的順序。我們看看這兩種方式會有什麼問題。

仍然用前面的 update 語句來做例子。假設當前 ID=4 的行，欄位 score 的值是 98.00 ，再假設執行 update 語句過程中在寫完第一個紀錄檔後，第二個紀錄檔還沒有寫完期間發生了 crash，會出現什麼情況呢？

(1) 、先寫 redo log 後寫 binlog。

假設在 redo log 寫完，binlog 還沒有寫完的時候，MySQL 程序異常重啟。由於我們前面說過的，redo log 寫完之後，系統即使崩潰，仍然能夠把資料恢復回來，所以恢復後這一行 score 的值是 99.00。但是由於 binlog 沒寫完就 crash 了，這時候 binlog 裡面就沒有記錄這個語句。因此，之後備份紀錄檔的時候，存起來的 binlog 裡面就沒有這條語句。如果需要用這個 binlog 來恢復臨時庫的話，由於這個語句的 binlog 丟失，這個臨時庫就會少了這一次更新，恢復出來的這一行的值就是 98.00，與原庫的值不同。

（2）、先寫 binlog 後寫 redo log

如果在 binlog 寫完之後 crash，由於 redo log 還沒寫，崩潰恢復以後這個事務無效，所以這一行 score 的值是 98.00。但是 binlog 裡面已經記錄了“把 score 從 98.00 改成 99.00”這個紀錄檔。所以，在之後用 binlog 來恢復的時候就多了一個事務出來，恢復出來的這一行 score 的值就是 99.00，與原庫的值不同。

可以看到，如果不使用“兩階段提交”，那麼資料庫的狀態就有可能和用它的紀錄檔恢復出來的庫的狀態不一致。

疑問：這樣的操作概率是不是很低，平時也沒有什麼動不動就需要恢復臨時庫的場景呀？

不只是誤操作後需要用這個過程來恢復資料。當你需要擴容的時候，也就是需要再多搭建一些備庫來增加系統的讀能力的時候，現在常見的做法也是用全量備份加上應用 binlog 來實現的，這個“不一致”就會導致你的線上出現主從資料庫不一致的情況。

簡單來說，redo log 和 binlog 都可以用於表示事務的提交狀態，而兩階段提交就是讓這兩個狀態保持邏輯上的一致。

對於InnoDB引擎而言，在每次事務commit提交時才會記錄binlog紀錄檔，此時記錄仍然在記憶體中，那麼什麼時候儲存到磁碟中呢？mysql通過 sync_binlog 引數控制binlog刷盤時機，取值範圍：0～N：

0：不去強求，由系統自行判斷何時寫入磁碟；
1：每次事務commit的時候都要將bin log寫入磁碟；
N：每N個事務commit，才會將bin log寫入磁碟；

備註：該值預設為0，採用作業系統機制進行緩衝資料同步。 sync_binlog 引數建議設定為1，這樣每次事務commit時就會把bin log寫入磁碟中，這樣也可以保證mysql異常重啟之後bin log紀錄檔不會丟失。

 SHOW GLOBAL VARIABLES LIKE '%innodb_flush%';

如圖所示：

binlog使用場景

在實際場景中， bin log 的主要場景有兩點，一點是主從複製，另一點是資料恢復。

（1）、主從複製：在master端開啟 bin log ，然後將 binlog 傳送給各個slaver端，slaver端讀取 binlog 紀錄檔，從而使得主從資料庫中資料一致。
（2）、資料恢復：通過 binlog 獲取想要恢復的時間段資料

到此這篇關於一條SQL更新語句的執行過程解析的文章就介紹到這了,更多相關SQL更新語句內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！