【大廠面試04期】講講一條MySQL更新語句是怎麼執行的？

【大廠面試04期】講講一條MySQL更新語句是怎麼執行的？

流程圖

這是在網上找到的一張流程圖，寫的比較好，大家可以先看圖，然後看詳細閱讀下面的各個步驟。

執行流程：

1.連接配接驗證及解析

用戶端與MySQL Server建立連接配接，發送語句給MySQL Server，接收到後會針對這條語句建立一個解析樹，然後進行優化，（解析器知道語句是要執行什麼，會評估使用各種索引的代價，然後去使用索引，以及調節表的連接配接順序）然後調用innodb引擎的接口來執行語句。

2.寫undo log

innodb 引擎首先開啟事務，對舊資料生成一個UPDATE的語句(如果是INSERT會生成UPDATE語句)，用于送出失敗後復原，寫入undo log，得到復原指針，并且更新這個資料行的復原指針和版本号(會設定為更新的事務id)。

3.從索引中查找資料

根據查詢條件去B+樹中找到這一行資料（如果是唯一性索引，查到第一個資料就可以了（因為有唯一性限制），如果是普通索引，會把所有資料查找出來。）

4.更新資料

首先判斷資料頁是否在記憶體中？

4.1 如果資料頁在記憶體中

先判斷更新的索引是普通索引還是唯一性索引？

4.1.1 普通索引

如果更新的索引是普通索引，直接更新記憶體中的資料頁

4.1.2 唯一性索引

如果更新的索引是唯一性索引，判斷更新後是否會破壞資料的唯一性，不會的話就更新記憶體中的資料頁。

4.2 如果資料頁不在記憶體中

4.2.1 普通索引

如果是更新的索引是普通索引，将對資料頁的更新操作記錄到change buffer，change buffer會在空閑時異步更新到磁盤。

4.2.2 唯一性索引

如果是更新的索引是唯一性索引，因為需要保證更新後的唯一性，是以不能延遲更新，必須把資料頁從磁盤加載到記憶體，然後判斷更新後是否會資料沖突，不會的話就更新資料頁。

5.寫undo log（prepare狀态）

将對資料頁的更改寫入到redo log，将redo log設定為prepare狀态。

6.寫bin log（commit狀态），送出事務

通知MySQL server已經更新操作寫入到redo log 了，随時可以送出，将執行的SQL寫入到bin log日志，将redo log改成commit狀态，事務送出成功。（一個事務是否執行成功的判斷依據是是否在bin log中寫入成功。寫入成功後，即便MySQL Server崩潰，之後恢複時也會根據bin log， redo log進行恢複。具體可以看看下面的崩潰恢複原則）

補充資料：

二段送出制是什麼？

更新時，先改記憶體中的資料頁，将更新操作寫入redo log日志，此時redo log進入prepare狀态，然後通知MySQL Server執行完了，随時可以送出，MySQL Server将更新的SQL寫入bin log，然後調用innodb接口将redo log設定為送出狀态，更新完成。

如果隻是寫了bin log就送出，那麼忽然發生故障，主節點可以根據redo log恢複資料到最新，但是主從同步時會丢掉這部分更新的資料。

如果隻是寫binlog，然後寫redo log，如果忽然發生故障，主節點根據redo log恢複資料時就會丢掉這部分資料。

MySQL崩潰後，事務恢複時的判斷規則是怎麼樣的？（以redolog是否commit或者binlog是否完整來确定）

如果 redo log 裡面的事務是完整的，也就是已經有了 commit 辨別，則直接送出；

如果 redo log 裡面的事務隻有完整的 prepare，則判斷對應的事務 binlog 是否存在并完整：a. 如果是，則送出事務；b. 否則，復原事務。

undo log是什麼？

undo log主要是保證事務的原子性，事務執行失敗就復原，用于在事務執行失敗後，對資料復原。undo log是邏輯日志，記錄的是SQL。（可以認為當delete一條記錄時，undo log中會記錄一條對應的insert記錄，反之亦然，當update一條記錄時，它記錄一條對應相反的update記錄。）

在事務送出後，undo log日志不會立即删除，會放到一個待删除的連結清單中，有purge線程判斷是否有其他事務在使用上一個事務之前的版本資訊，然後決定是否可以清理，簡單的來說就是前面的事務都送出成功了，這些undo才能删除。

change buffer是什麼（就是将更新資料頁的操作緩存下來）

在更新資料時，如果資料行所在的資料頁在記憶體中，直接更新記憶體中的資料頁。

如果不在記憶體中，為了減少磁盤IO的次數，innodb會将這些更新操作緩存在change buffer中，在下一次查詢時需要通路這個資料頁時，在執行change buffer中的操作對資料頁進行更新。

适合寫多讀少的場景，因為這樣即便立即寫了，也不太可能會被通路到，延遲更新可以減少磁盤I/O，隻有普通索引會用到，因為唯一性索引，在更新時就需要判斷唯一性，是以沒有必要。

redo log 是什麼？

redo log就是為了保證事務的持久性。因為change buffer是存在記憶體中的，萬一機器重新開機，change buffer中的更改沒有來得及更新到磁盤，就需要根據redo log來找回這些更新。

優點是減少磁盤I/O次數，即便發生故障也可以根據redo log來将資料恢複到最新狀态。

缺點是會造成記憶體髒頁，背景線程會自動對髒頁刷盤，或者是淘汰資料頁時刷盤，此時收到的查詢請求需要等待，影響查詢。

掃描二維碼進技術群，領取《大廠面試指北》PDF及更多技術資料，讨論技術，一起學習進步

《大廠面試指北》預覽位址：

http://notfound9.github.io/interviewGuide/

《大廠面試指北》Github位址：

https://github.com/NotFound9/interviewGuide

原文位址

https://www.cnblogs.com/notfound9/p/13048033.html