一條更新的SQL語句是如何執行的？

techcoder發表於2019-03-16
SQL

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

提出問題

UPDATE student SET score = score + 1 WHERE uid = 666; 以上就是一條最簡單的SQL更新語句，想要知道上面這句SQL語句是怎麼執行的先要了解MySQL資料庫的邏輯架構。

一條更新的SQL語句是如何執行的？
UPDATE語句也不例外的執行這個流程，先連線資料庫（聯結器），然後將SQL語句進行詞法分析，並檢測SQL語法（分析器），然後優化對應的查詢操作（優化器），最後真正的去執行這個語句（執行器）。

具體到上面的UPDATE語句，先取出uid=666的所有行，然後將這些行的score欄位的值加1，並寫入記憶體中。接下來的過程與查詢語句的查詢流程就不一樣了，查詢語句只需要返回查詢結果即可，但是更新語句需要去真的修改資料庫中的資料，所以更新語句相對來講要複雜一些。

說到SQL的更新語句就不得不提到重做日誌(redo log) 和歸檔日誌(binlog)，這兩個日誌在MySQL中起到了巨大的作用，這兩個日誌的相互配合也是很有意思的設計，接下來就要詳細給大家講下這兩種日誌的作用、它們是如何工作的、以及它們之間的相互配合。

redo log

redo log是為了解決crash-safe問題而產生的，是一種物理日誌，我們知道資料庫是用來儲存資料的，crash-safe問題對於資料庫來說是非常重要的，在開啟redo log之後MySQL的異常重啟之前提交的資料都不會丟失，這樣就能保證異常crash後資料不會丟失。

redo log是InnoDB引擎層的一種日誌，是用來記錄這個頁"做了什麼改動"。在MySQL中經常會說道WAL技術，WAL的全稱是Write Ahead Logging，WAL的核心思想就是日誌先行，舉個例子，執行一條更新語句，InnoDB就會先把記錄寫到redo log裡面，然後更新到記憶體，等到系統比較空閒的時候再寫入磁碟。redo log的檔案大小是固定的，是通過迴圈寫的 實現的。

有了redo log就能保證InnoDB即使發生異常重啟也不會丟失資料，這種能力也叫做crash-safe的能力

binlog

binlog是一種邏輯日誌，是Server層的一種日誌，記錄了所有的sql語句，主要是用來配合備份來恢復資料庫的，只要我們有最近一次的備份和這期間完整的binlog就能夠恢復資料庫了。 下面我們來簡單看下binlog檔案，我是ubuntu系統，這個檔案是放在/var/log/mysql/資料夾下面的，

一條更新的SQL語句是如何執行的？

從上面的圖片我們能看到檔名字是依次增加的，與redo log的迴圈寫不同，binlog是追加寫的。 我們執行下面的命令列就能看到binlog記錄的sql語句是什麼樣的，還有一些binlog檔案內容的參照 官方文件操作。

sudo mysqlbinlog /var/log/mysql/mysql-bin.000002 --base64-output=DECODE-ROWS --verbose —verbose
複製程式碼

執行的結果如下圖所示：

一條更新的SQL語句是如何執行的？

從上圖來看很清晰的能看懂這個update語句執行的含義。

上面講了這麼多這兩種日誌的含義，下面簡單總結下這兩種日誌的一些區別：

  • redo log是一種物理日誌，記錄是這個頁做了什麼改動，而binlog是邏輯日誌，記錄是sql語句的原始邏輯。
  • redo log的檔案大小是固定的，會迴圈寫入檔案，所以會覆蓋之前的日誌。而binlog是追加寫，不會覆蓋之前的日誌。
  • redo log是InnoDB引擎層的日誌，而binlog是server層的日誌。

有同學會問，為什麼要搞兩個日誌呀？

我們知道MySQL最開始預設的引擎是MyASIM引擎，根本就不存在crash-safe的問題，binlog只是用來做歸檔的。在MySQL5.5.5之後將InnoDB作為預設的儲存引擎，這樣InnoDB就擁有了crash-safe的能力，在MySQL的架構中，引擎是以外掛的形式存在的，InnoDB引擎不是MySQL資料庫必須的，所以也就好理解redo log也不是MySQL資料庫必須的日誌。

這也就好理解為什麼要搞兩個日誌，一個是server層，一個是引擎層，他們負責不同的功能，相互合作。

那具體這兩個日誌是怎麼合作的呢？他們怎麼保證資料的一致性呢？

兩階段提交

先說下兩階段提交的具體過程：

  • UPDATE語句的結果寫入記憶體，同時將這個操作寫入redo log，此時redo log處於prepare狀態，並告知執行器隨時可以提交事物。
  • 執行器生成這個操作的binlog，並寫入binlog日誌。
  • 執行器通知將之前處於prepare狀態改為commit狀態，更新完成。

兩個階段提交保證了redo log和binlog的一致性。 下面來分析下如果不是兩個階段提交會發生什麼？

先寫redo log後寫binlog

如果先寫redo log再寫binlog的話，當redo log寫完的時候發生了crash，此時binlog裡面是沒有記錄的。這時候發生重啟，redo log會恢復crash的語句，但是如果用這產生時的binlog去恢復資料庫就會丟失這條記錄，此時兩個日誌恢復的資料庫資料就產生了差異。

先寫binlog後寫redo log

如果是先寫binlog後寫redo log，當寫完binlog的時候發生了crash。這時候發生重啟，redo log中還沒寫，此時異常重啟後這個事務是無效的，所以無法恢復，但是binlog中有這條資料，當用此時的binlog檔案去恢復資料庫的時候，就會比當前的資料庫資料多一條記錄。

從上面就可以明白，如果不用兩階段提交就有可能出現兩個日誌狀態不一致。

相關文章