【MySQL深入】一條SQL更新陳述句的執行程序

1.MySQL邏輯架構示意圖

• 對于一條update陳述句來說，也會執行上圖中的流程，如果您對于上圖中的連接器、分析器等名詞不太熟悉，請查看上一篇文章：MySQL邏輯架構中各名詞詳解

2.一條示例update陳述句執行程序簡析

# 建表SQL
create table `article` (
    `id` bigint(20) unsigned NOT NULL DEFAULT 0,
    `commentcnt` int(11) unsigned NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# 插入資料
insert into `article` (`id`, `commentcnt`) values(1, 11);
insert into `article` (`id`, `commentcnt`) values(2, 22);

• 更新資料sql

update `article` set `commentcnt` = `commentcnt` + 1 where `id` = 2;

• 該SQL的執行程序：

1. 連接器會先檢查權限，若沒有權限就直接回傳錯誤資訊，如果有權限就會清除查詢快取（MySQL 8.0版本之前），將article表所有快取結果都清空（一般不建議使用查詢快取），接下來執行下一步，
2. 通過分析器先進行詞法分析，提取sql陳述句里面的關鍵字，示例SQL取的update，然后提取要更新的表名article ，更新條件是id= 2 ，然后進行語法分析，判斷sql陳述句是否正確，如果有錯會回傳報錯資訊，否則執行下一步，
3. 優化器確定執行方案，優化器根據自己的優化演算法選擇一個執行效率最好的一個方案，比如：優化器會去找id欄位有沒有索引，使用id主鍵索引，執行計劃確定后就會執行下一步，
4. 執行器首先會判斷當前用戶對article表是否有更新的權限，如果沒有權限就會回傳權限錯誤，若有權限會打開表執行，根據表的引擎定義呼叫引擎提供的介面，回傳引擎執行結果，

與查詢SQL流程不同的是，更新SQL流程還涉及兩個重要的日志模塊redo log 和 binlog

3. MySQL日志

3.1 redo log

1. 前言：之前MySQL每一條更新記錄，都需要寫入磁盤，然后磁盤也要找到對應的那條記錄，然后再更新，整個程序 IO 成本、查找成本都很高，為了解決這個問題，MySQL 的設計者就用了WAL技術解決該問題，
2. WAL（Write-Ahead Logging）預寫日志：先寫日志，再寫磁盤，將隨機寫轉換成了順序寫，大大提升了資料庫的性能，WAL技術的典型應用就是redo log
3. redo log的定義：一塊固定大小的重做物理日志檔案，可以回圈寫，redo log記錄的是“在某個資料頁上做了什么修改”，是InnoDB引擎特有的日志，
4. redo log的作業流程：當有一條記錄更新的時候，InnoDB 引擎就會先把記錄寫到 redo log 里面，并更新記憶體，這個時候更新就算完成了，同時，InnoDB 會在適當的時候，將這個操作記錄更新到磁盤
5. redo log寫示例：比如MySQL配置redo log一組4個1G的檔案，寫redo log的流程示意圖如下
   

• write pos：當前記錄的位置，邊寫邊往后移動，直到移動到ib_logfile_3的末尾，然后會回到ib_logfile_0檔案的開頭
• checkpoint：當前要擦除的位置，和write pos一樣也是往后移動且會回圈，擦除記錄前要把記錄保存到磁盤中
• write pos和checkpoint之前的空間：記錄新的操作，如果write pos追上checkpoint，就表明redo log已經沒有空間來記錄新的操作了，這時就需要把擦除當前記錄保存到磁盤中，從而保證新的操作可以被記錄，

6. crash-safe：因為有redo log，所以InnoDB可以保證即使資料庫發生例外重啟，之前提交的記錄都不會丟失，這個能力稱為crash-safe

3.2 binlog

1. binlog （歸檔日志）定義：server層日志，是邏輯日志，記錄陳述句的原始邏輯，比如“給ID=2這一行的c欄位加1 ”，可以追加寫入，即 binlog 檔案寫到一定大小后會切換到下一個binlog檔案，并不會覆寫以前的日志，
2. binlog的三種模式

• 注：一般采用row模式，因為遇到時間，從庫可能會出現不一致的情況，但是row更新前后都有

3. binlog保證完整性的方法：statement格式的binlog, 最后會有Commit標識，row格式的binlog, 最后會有一個XID event標識，在MySQL5.6.2版本以后, 還引入了binlog-checksum引數, 用來驗證binlog內容的正確性，

3.3 redo log和binlog的區別

1. redo是物理日志，binlog是邏輯日志
2. redo log是InnoDB引擎特有的，binlog是MySQL的Server層實作的，所有引擎都可以使用，
3. redo log回圈寫，空間固定會用完；binlog可以追加寫入，

3.4 出現兩份日志的原因

1. binlog沒有能力恢復“資料頁”，redo log 來實作 crash-safe 能力，
2. redo log是回圈寫，寫到末尾是要回到開頭繼續寫的，這樣歷史日志沒法保留，redo log也就起不到歸檔的作用，binlog 日志來實作歸檔
3. MySQL系統依賴于binlog，例如：MySQL系統高可用的基礎，就是binlog復制，

4. update陳述句執行程序詳解

update `article` set `commentcnt` = `commentcnt` + 1 where `id` = 2;

• 有了redo log 和 binlog的知識儲備后，再來看看這條update陳述句的執行原理，

4.1 update陳述句的執行流程圖

（圖中淺色框表示是在InnoDB內部執行的，深色框表示是在執行器中執行的）

更新SQL執行流程：

• 連接資料庫，清空查詢快取（MySQL8.0之前），分析詞法和語法后知道這是一條update陳述句，優化器決定使用ID這個索引
• 執行器先找引擎取 ID=2 這一行，ID 是主鍵，引擎直接用樹搜索找到這一行，
• 如果 ID=2 這一行所在的資料頁本來就在記憶體中，就直接回傳給執行器，否則，需要先從磁盤讀入記憶體，然后再回傳，
• 執行器拿到引擎給的行資料，把這個值加上 1，比如原來是 N，現在就是 N+1，得到新的一行資料，再呼叫引擎介面寫入這行新資料，
• 引擎將這行新資料更新到記憶體中，同時將這個更新操作記錄到 redo log 里面，此時 redo log 處于 prepare 狀態，然后告知執行器執行完成了，隨時可以提交事務，
• 執行器生成這個操作的 binlog，并把 binlog 寫入磁盤，
• 執行器呼叫引擎的提交事務介面，引擎把剛剛寫入的 redo log 改成提交（commit）狀態，更新完成

4.2 兩階段提交

1. 定義：更新流程中寫入redo log的程序拆成了兩個步驟prepare和commit，如果不使用兩階段提交，資料庫的狀態就有可能和用它的日志恢復出來的庫的狀態不一致，
2. 作用：讓redo log和binlog邏輯上保持一致. 如果在commit時崩潰了, 雖然沒有commit, 但是prepare和binlog完整, 所以重啟之后會自動commit
3. MySQL例外重啟后的崩潰恢復規則:

• 如果redo log里面的事務是完整的, 也就是有了commit標識, 則直接提交
• 如果redo log里面的事務只有完整的prepare, 則判斷對應的事務是否存在完整的binlog，如果是, 則提交事務，否則,回滾事務

4. redo log和binlog的如何關聯

• 它們有一個共同的資料欄位XID. 崩潰恢復的時候, 會按順序掃描redo log
• 如果碰到既有prepare, 又有commit的redo log, 就直接提交
• 如果碰到只有prepare, 而沒有commit的redo log, 就拿著XID去binlog找對應的事務

5. 雙1配置保證不丟資料：

• innodb_flush_log_at_trx_commit=1 每次事務的redo log都直接持久化到磁盤，可以保證MySQL例外重啟之后資料不丟失，
• sync_binlog=1 每次事務的binlog都持久化到磁盤，可以保證MySQL例外重啟之后binlog不丟失，