前言：

上一篇文章講解了SQL查詢語句執(zhí)行的過程，并介紹了執(zhí)行過程中涉及的處理模塊?；仡櫼幌拢粭l查詢語句的執(zhí)行過程一般是經(jīng)過連接器、分析器、優(yōu)化器、執(zhí)行器等功能模塊，最后到達(dá)存儲(chǔ)引擎。

一、執(zhí)行過程

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

你執(zhí)行語句前要先連接數(shù)據(jù)庫，這是連接器的工作。

使用數(shù)據(jù)庫的第一步就是先連接到這個(gè)數(shù)據(jù)庫上，這時(shí)候作為接待的就是連接器。連接器負(fù)責(zé)跟客戶端建立連接、獲取權(quán)限、維持和管理連接。連接命令：

mysql mysql -h主機(jī)地址 -u用戶名 －p

輸完命令之后，你就需要在交互對話里面輸入密碼。雖然密碼也可以直接跟在 -p 后面寫在命令行中，但這樣可能會(huì)導(dǎo)致你的密碼泄露，不建議直接跟著輸入。

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

如果輸入賬號(hào)或密碼錯(cuò)誤會(huì)提示（1045）：

回歸正題，我們還是從一個(gè)表的一條更新語句說起，下面是這個(gè)表的創(chuàng)建語句，這個(gè)表有一個(gè)主鍵 id 和一個(gè)字符串類型的字段 name 以及一個(gè) 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 '分?jǐn)?shù)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='學(xué)生分?jǐn)?shù)表';

并加入幾條測試數(shù)據(jù)：

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

結(jié)果如下：

如果要將 id=4 這一行的值加 1，SQL 語句就會(huì)這么寫：

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

執(zhí)行結(jié)果如圖：

在一個(gè)表上有更新的時(shí)候，跟這個(gè)表有關(guān)的查詢緩存會(huì)失效，所以這條語句就會(huì)把表上所有緩存結(jié)果都清空。這也就是我們一般不建議使用查詢緩存的原因。

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

優(yōu)化器決定要使用 ID 這個(gè)索引。

執(zhí)行器負(fù)責(zé)具體執(zhí)行，找到這一行，然后更新。

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

二、日志模塊

在說日志模塊前，先說一下什么是物理日志和邏輯日志。

物理日志：通俗的講，就是只有"我"自己可以使用，別人無法共享我的"物理格式，私有化。

邏輯日志：可以給別的引擎使用，是所有引擎共享的。

1、物理日志redo log

redo log是 InnoDB 引擎特有的日志，又被稱為重寫日志， 用來記錄事務(wù)操作的變化，記錄的是數(shù)據(jù)修改之后的值，不管事務(wù)提交是否成功，都會(huì)被記錄下來。它讓MySQL擁有了崩潰恢復(fù)能力。

比如MySQL實(shí)例掛了或宕機(jī)了，重啟時(shí)，InnoDB存儲(chǔ)引擎會(huì)使用redo log恢復(fù)數(shù)據(jù)，保證數(shù)據(jù)的持久性與完整性。

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

酒館掌柜有一個(gè)黑板，賒賬的人少時(shí)就記在黑板上如果賒賬人多的話，由于黑板的空間大小有限，所以他又需要額外準(zhǔn)備一本賬本，專門記錄所有賒賬的賬目。 如果有人要賒賬的話，一般老板有兩種做法：

（1）、打開賬本，找到賒賬人的記錄，進(jìn)行追加賒賬記錄； （2）、先把賒賬人的記錄寫到黑板上，待客流量少的時(shí)刻，再更新到賒賬賬目上。 如果掌柜使用第一種方法的話，每當(dāng)有人要賒賬的話，首先他需要打開厚厚的賬本，一頁一頁查找該顧客的姓名，然后進(jìn)行登記。你想一下，如果賒賬的人不多，掌柜找賒賬人的記錄輕松點(diǎn)，如果賒賬本有好幾本的話，一本一本的找，掌柜看的都頭疼。

在 MySQL里也有這個(gè)問題。如果每一次的更新操作都需要寫進(jìn)磁盤，然后磁盤也要找到對應(yīng)的那條記錄，然后再更新，整個(gè)過程 IO 成本、查找成本都很高。為了解決這個(gè)問題，MySQL 的設(shè)計(jì)者就用了類似酒店掌柜粉板的思路來提升更新效率。

而黑板和賬本配合的整個(gè)過程，其實(shí)就是 MySQL 里經(jīng)常說到的 WAL （Write Ahead Logging）技術(shù),它的關(guān)鍵點(diǎn)就是先寫日志，再寫磁盤，

當(dāng)有一條記錄需要更新的時(shí)候，InnoDB 引擎就會(huì)先把記錄寫到 redo log（黑板）里面，并更新內(nèi)存，這個(gè)時(shí)候更新就算完成了。同時(shí)，InnoDB 引擎會(huì)在適當(dāng)?shù)臅r(shí)候，將這個(gè)操作記錄更新到磁盤里面，而這個(gè)更新往往是在系統(tǒng)比較空閑的時(shí)候做，這就像打烊以后掌柜做的事。

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

與此類似，InnoDB 的 redo log 是固定大小的，比如可以配置為一組 4 個(gè)文件，每個(gè)文件的大小是 1GB，那么這塊“黑板”總共就可以記錄 4GB 的操作。從頭開始寫，寫到末尾就又回到開頭循環(huán)寫，

如下圖所示：

write pos是當(dāng)前記錄的位置，一邊寫一邊后移，寫到4號(hào)文件末尾就回到1號(hào)文件開頭。check point是當(dāng)前要把記錄寫入到數(shù)據(jù)文件的位置，也是后移并且循環(huán)的。

如果和上面老板黑板場景結(jié)合起來描述的話，write pos就是老板在黑板上順序?qū)懭胭d賬人記錄位置，對于mysql來說，write pos后移；而check point就是老板把黑板上記錄寫入到賒賬本上的位置，當(dāng)老板寫入到賒賬本上后，就會(huì)把粉板上該記錄擦除掉，對于mysql來說，check point后移。

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

有了 redo log，InnoDB 就可以保證即使數(shù)據(jù)庫發(fā)生異常重啟，之前提交的記錄都不會(huì)丟失，這個(gè)能力稱為crash-safe。

redo log的使用場景

用于系統(tǒng)奔潰恢復(fù)。

redolog配置

（1）、緩存大小

innodb_log_buffer_size 默認(rèn)大小 16MB。 查看相關(guān)配置sql：

SHOW GLOBAL VARIABLES LIKE '%innodb_log%';

結(jié)果如圖所示：

（2）、刷盤策略

提交事物寫入磁盤中，會(huì)根據(jù)這個(gè)配置的策略進(jìn)行同步。

• 0 提交事物的時(shí)候不會(huì)把redo log buffer 里的數(shù)據(jù)刷入磁盤。
• 1 提交事物的時(shí)候，必須把日志刷入磁盤中，可以嚴(yán)格保證數(shù)據(jù)不丟失 （默認(rèn)且推薦策略）。
• 2 提交事物的時(shí)候，先把日志刷入磁盤文件對應(yīng)的 os cache 緩存里，隔一段時(shí)間再把數(shù)據(jù)刷入磁盤。

查看相關(guān)參數(shù)SQL：

SHOW GLOBAL VARIABLES LIKE '%sync_binlog%';

2、邏輯日志binlog

MySQL 整體來看，其實(shí)就有兩塊：一塊是 Server層，它主要做的是 MySQL 功能層面的事情；還有一塊是引擎層，負(fù)責(zé)存儲(chǔ)相關(guān)的具體事宜。 redo log 是 InnoDB 引擎特有的日志，而 Server 層也有自己的日志，稱為 binlog（歸檔日志）。

為什么會(huì)有兩份日志呢？

最開始 MySQL 里并沒有 InnoDB 引擎。MySQL 自帶的引擎是 MyISAM，但是 MyISAM 沒有 crash-safe 的能力，binlog 日志只能用于歸檔。而 InnoDB 是另一個(gè)公司以插件形式引入 MySQL 的，既然只依靠 binlog 是沒有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系統(tǒng)——也就是 redo log 來實(shí)現(xiàn) crash-safe 能力。

bin log是mysql數(shù)據(jù)庫service層的，是所有存儲(chǔ)引擎共享的日志模塊，它用于記錄數(shù)據(jù)庫執(zhí)行的寫入性操作，也就是在事務(wù)commit階段進(jìn)行記錄，以二進(jìn)制的形式保存于磁盤中。

這兩種日志有以下不同：

對這兩個(gè)日志的有了概念性理解，我們再來看執(zhí)行器和 InnoDB 引擎在執(zhí)行這個(gè)簡單的 update 語句時(shí)的內(nèi)部流程。

• （1）、執(zhí)行器先找引擎取 ID=4 這一行。ID 是主鍵，引擎直接用樹搜索找到這一行。如果 ID=4 這一行所在的數(shù)據(jù)頁本來就在內(nèi)存中，就直接返回給執(zhí)行器；否則，需要先從磁盤讀入內(nèi)存，然后再返回，并且對這行記錄加獨(dú)占鎖，把更新行記錄的舊值寫入 undo log（以便回滾）。
• （2）、執(zhí)行器拿到引擎給的行數(shù)據(jù)，把這個(gè)值加上 1，比如原來是 N，現(xiàn)在就是 N+1，得到新的一行數(shù)據(jù)，再調(diào)用引擎接口寫入這行新數(shù)據(jù)。
• （3）、引擎將這行新數(shù)據(jù)更新到內(nèi)存中，同時(shí)將這個(gè)更新操作記錄到 redo log 里面，此時(shí) redo log 處于 prepare狀態(tài)。然后告知執(zhí)行器執(zhí)行完成了，隨時(shí)可以提交事務(wù)。
• （4）、執(zhí)行器生成這個(gè)操作的 binlog，再按策略刷到 binlog文件（磁盤中）。
• （5）、執(zhí)行器調(diào)用引擎的提交事務(wù)接口，引擎把剛剛寫入的 redo log 改成提交（commit）狀態(tài)，更新完成。

update 語句的執(zhí)行流程圖如下，其中圖中黃色框表示是在 InnoDB 內(nèi)部執(zhí)行的，綠色框表示是在執(zhí)行器中執(zhí)行的。

重點(diǎn)看下最后三步，將 redo log 的寫入拆成了兩個(gè)步驟：prepare 和 commit，這就是"兩階段提交"。

兩階段提交

為什么必須有“兩階段提交”呢？這是為了讓兩份日志之間的邏輯一致。

binlog 會(huì)記錄所有的邏輯操作，并且是采用“追加寫”的形式。如果你的 DBA 承諾說半個(gè)月內(nèi)可以恢復(fù)，那么備份系統(tǒng)中一定會(huì)保存最近半個(gè)月的所有 binlog，同時(shí)系統(tǒng)會(huì)定期做整庫備份。

當(dāng)需要恢復(fù)到指定的某一秒時(shí)，比如某天下午點(diǎn)發(fā)現(xiàn)上午11點(diǎn)有一次誤刪表，需要找回?cái)?shù)據(jù)，那我們可以這么做：

首先，找到最近的一次全量備份，如果你運(yùn)氣好，可能就是昨天晚上的一個(gè)備份，從這個(gè)備份恢復(fù)到臨時(shí)庫； 然后，從備份的時(shí)間點(diǎn)開始，將備份的 binlog 依次取出來，重放到中午誤刪表之前的那個(gè)時(shí)刻。 這樣你的臨時(shí)庫就跟誤刪之前的線上庫一樣了，然后你可以把表數(shù)據(jù)從臨時(shí)庫取出來，按需要恢復(fù)到線上庫去。

為什么日志需要“兩階段提交”？

由于 redo log 和 binlog 是兩個(gè)獨(dú)立的邏輯，如果不用兩階段提交，要么就是先寫完 redo log 再寫 binlog，或者采用反過來的順序。我們看看這兩種方式會(huì)有什么問題。

仍然用前面的 update 語句來做例子。假設(shè)當(dāng)前 ID=4 的行，字段 score 的值是 98.00 ，再假設(shè)執(zhí)行 update 語句過程中在寫完第一個(gè)日志后，第二個(gè)日志還沒有寫完期間發(fā)生了 crash，會(huì)出現(xiàn)什么情況呢？

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

假設(shè)在 redo log 寫完，binlog 還沒有寫完的時(shí)候，MySQL 進(jìn)程異常重啟。由于我們前面說過的，redo log 寫完之后，系統(tǒng)即使崩潰，仍然能夠把數(shù)據(jù)恢復(fù)回來，所以恢復(fù)后這一行 score 的值是 99.00。但是由于 binlog 沒寫完就 crash 了，這時(shí)候 binlog 里面就沒有記錄這個(gè)語句。因此，之后備份日志的時(shí)候，存起來的 binlog 里面就沒有這條語句。如果需要用這個(gè) binlog 來恢復(fù)臨時(shí)庫的話，由于這個(gè)語句的 binlog 丟失，這個(gè)臨時(shí)庫就會(huì)少了這一次更新，恢復(fù)出來的這一行 的值就是 98.00，與原庫的值不同。

（2）、先寫 binlog 后寫 redo log

如果在 binlog 寫完之后 crash，由于 redo log 還沒寫，崩潰恢復(fù)以后這個(gè)事務(wù)無效，所以這一行 score 的值是 98.00。但是 binlog 里面已經(jīng)記錄了“把 score 從 98.00 改成 99.00”這個(gè)日志。所以，在之后用 binlog 來恢復(fù)的時(shí)候就多了一個(gè)事務(wù)出來，恢復(fù)出來的這一行 score 的值就是 99.00，與原庫的值不同。

可以看到，如果不使用“兩階段提交”，那么數(shù)據(jù)庫的狀態(tài)就有可能和用它的日志恢復(fù)出來的庫的狀態(tài)不一致。

疑問：這樣的操作概率是不是很低，平時(shí)也沒有什么動(dòng)不動(dòng)就需要恢復(fù)臨時(shí)庫的場景呀？

不只是誤操作后需要用這個(gè)過程來恢復(fù)數(shù)據(jù)。當(dāng)你需要擴(kuò)容的時(shí)候，也就是需要再多搭建一些備庫來增加系統(tǒng)的讀能力的時(shí)候，現(xiàn)在常見的做法也是用全量備份加上應(yīng)用 binlog 來實(shí)現(xiàn)的，這個(gè)“不一致”就會(huì)導(dǎo)致你的線上出現(xiàn)主從數(shù)據(jù)庫不一致的情況。

簡單來說，redo log 和 binlog 都可以用于表示事務(wù)的提交狀態(tài)，而兩階段提交就是讓這兩個(gè)狀態(tài)保持邏輯上的一致。

對于InnoDB引擎而言，在每次事務(wù)commit提交時(shí)才會(huì)記錄binlog日志，此時(shí)記錄仍然在內(nèi)存中，那么什么時(shí)候存儲(chǔ)到磁盤中呢？mysql通過 sync_binlog 參數(shù)控制binlog刷盤時(shí)機(jī)，取值范圍：0～N：

• 0：不去強(qiáng)求，由系統(tǒng)自行判斷何時(shí)寫入磁盤；
• 1：每次事務(wù)commit的時(shí)候都要將bin log寫入磁盤；
• N：每N個(gè)事務(wù)commit，才會(huì)將bin log寫入磁盤；

備注：該值默認(rèn)為0，采用操作系統(tǒng)機(jī)制進(jìn)行緩沖數(shù)據(jù)同步。 sync_binlog 參數(shù)建議設(shè)置為1，這樣每次事務(wù)commit時(shí)就會(huì)把bin log寫入磁盤中，這樣也可以保證mysql異常重啟之后bin log日志不會(huì)丟失。

 SHOW GLOBAL VARIABLES LIKE '%innodb_flush%';

如圖所示：

binlog使用場景

在實(shí)際場景中， bin log 的主要場景有兩點(diǎn)，一點(diǎn)是主從復(fù)制，另一點(diǎn)是數(shù)據(jù)恢復(fù)。

• （1）、主從復(fù)制：在master端開啟 bin log ，然后將 binlog 發(fā)送給各個(gè)slaver端，slaver端讀取 binlog 日志，從而使得主從數(shù)據(jù)庫中數(shù)據(jù)一致。
• （2）、數(shù)據(jù)恢復(fù)：通過 binlog 獲取想要恢復(fù)的時(shí)間段數(shù)據(jù)

到此這篇關(guān)于一條SQL更新語句的執(zhí)行過程解析的文章就介紹到這了,更多相關(guān)SQL更新語句內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論