1.引言
大家好，我是小?，一個漂泊江湖多年的 985 非科班程序員，曾混跡于國企、互聯網大廠和創業公司的后臺開發攻城獅。
最近發現，不管是初入職場，還是已經工作很多年的開發同學。雖然接觸數據庫（尤其是 MySQL）很長時間了，但對于 SQL 語句的執行過程都知之甚少，或者一知半解。
而 MySQL 的執行流程也確實是一個復雜的過程，它涉及多個組件的協同工作，故而在面試或者工作的過程中很容易陷入迷惑和誤區。
SQL 執行過程
所以，這篇文章我將以 MySQL 常見的 InnoDB 存儲引擎為例，為大家詳細介紹 SQL 語句的執行流程。從連接器開始，一直到事務的提交和數據持久化。
我們先來看一張圖：
首先，客戶端會與 MySQL Server 連接，并發送增刪改查語句，Server 收到語句后會創建一個解析樹，進行優化。
優化器優化語句時，會評估各種索引的代價，選擇合適的索引，然后通過執行器調用 InnoDB 引擎的接口來執行語句。
2. 具體執行流程
1. 連接器（Connection Manager）
MySQL 的執行流程始于連接器。當客戶端請求與 MySQL 建立連接時，連接器負責處理這些連接請求。
它驗證客戶端的身份和權限，然后分配一個線程來處理該連接。MySQL 每個連接線程會創建一個會話（session），在這個會話中，客戶端可以發送 SQL 語句進行增刪改查等操作。
2. 分析器（Parser）
一旦連接建立，客戶端可以發送待執行的 SQL 語句。
這些 SQL 語句首先被送到分析器，分析器的任務是解析 SQL 語句，確定其語法是否正確，并將其轉化為一個內部數據結構，以供 MySQL 后續使用。
如果 SQL 語句有語法錯誤，分析器將返回錯誤信息給客戶端。
3. 優化器（Optimizer）
一旦 SQL 語句被成功解析，接下來進入優化器的領域。
優化器的任務是評估該 SQL 語句不同的執行計劃，并選擇最優的執行計劃。它會考慮哪些索引可用，哪種連接方法效率最高，以及如何最小化查詢的成本。
4. 執行器（Executor）
執行器接收到優化器生成的執行計劃后，它開始執行實際的查詢操作。
執行器會按照執行計劃中的步驟，調用 InnoDB 引擎層的邏輯并從數據表中獲取數據，然后進行排序、聚合、過濾等操作。
最終，執行器將結果返回給客戶端。
5. 寫 undo log
當執行器執行修改數據的操作時，MySQL 的 InnoDB 引擎首先會開啟事務，為這些修改生成 undo log（也叫回滾日志）。
回滾日志用于記錄修改前的數據，以便在事務回滾時恢復原始數據。如果事務執行失敗，MySQL 可以使用undo log 來撤銷已經進行的修改。
6. 記錄緩存（Record Cache），查找索引
MySQL 使用記錄緩存來存儲從數據表中讀取的數據行，這個緩存可以加速對頻繁讀取的數據的訪問，避免了每次都要從磁盤讀取的開銷。
當數據存在于內存中時，只需要更新內存即可；反之則可能需要從磁盤中讀取數據，再更新磁盤數據。
這取決于 MySQL 的索引類型，可分為兩種：
-
唯一索引：索引列的值唯一，非主鍵的唯一索引允許有空值，主鍵索引不允許空值；
-
普通索引：沒有特殊限制，允許重復值和空值；
當 SQL 操作數據到達這一步時，InnoDB 首先會判斷數據頁是否在內存中：
-
在內存中，判斷更新的索引是否是唯一索引。如果是唯一索引，則判斷更新后是否破壞數據的一致性，不會的話就直接更新內存中的數據頁；如果是非唯一索引，直接更新內存中的數據頁。
-
不在內存中：判斷更新的索引是否是唯一索引。如果是唯一索引，由于需要保證更新后的唯一性，所以需要立即把數據頁從磁盤加載到內存，然后更新數據頁；如果是非唯一索引，則將數據更新的操作記錄到 change buffer，它將在在空閑時異步更新到磁盤。
change buffer
change buffer 是 InnoDB 引擎的特性之一，在 MySQL 5.5 之前，change buffer 的主要作用是提高數據插入的性能，又被稱作 insert buffer。
我們知道，當非聚集索引插入時，數據會按主鍵的順序存放，所以葉子節點可能需要離散地訪問數據索引頁，每次索引頁更新時，都需要刷新磁盤。而每次讀寫磁盤的時間都會很久，故而導致插入性能較低。
而 insert buffer 開啟后，會先判斷聚集索引頁是否存在于緩沖池中，如果有，直接插入；如果不在，先放入一個插入緩沖區進行排序，再以一定的頻率合并（merge）更新索引頁。
如圖所示，insert buffer 將多次操作合并起來，以減少隨機 I/O，減少和磁盤交互的操作，從而提升整體的性能。
MySQL5.5 之后，逐漸加入了數據刪除和修改的緩沖類型，統一叫 change buffer。
一言概之，change buffer 主要作用是將二級索引的增刪改（IDU）操作緩存下來，以減少隨機 I/O，達到操作合并的效果。
由于唯一索引需要立即 IO 到磁盤，以保證數據不沖突，因此唯一索引沒有 change buffer 機制。
8. 寫 redo log
在 SQL 執行的過程中，InnoDB 還會記錄所有的數據修改操作到 redo log（重做日志）中。
重做日志是一個循環寫入的日志文件，它記錄了事務的每個步驟，以確保數據的持久性。如果系統崩潰， InnoDB 可以根據 redo log 來恢復未提交的事務，以保持數據的一致性。
注意，redo log 分為 prepare 和 commit 兩個狀態。在事務執行的過程中，InnoDB 把數據頁的更改寫入到 redo log 時，其狀態為 prepare 狀態。
9. 寫 binlog，提交事務
除了 redo log，MySQL 還會記錄 binlog（二進制日志）。
二進制日志記錄了所有執行的 SQL 語句，而不僅僅是數據修改，這對于數據復制和恢復非常重要，因為它可以確保不僅數據的狀態被恢復，連同執行的 SQL 操作也能被還原。
當 InnoDB 引擎層寫好 redo log 后，會通知 MySQL Server 層已將更新操作已經執行完成。這時，MySQL Server 將執行的 SQL 寫入到 binlog，然后通知 InnoDB 將 redo log 置為 commit 狀態，事務提交成功。
注意，一個事務提交成功的判斷依據在于是否寫入到 binlog 日志中。若已寫入，即便 MySQL Server 崩潰，之后也可以根據 redo log 和 binlog 進行恢復。
3. redo log 和 binlog
上面說到了，當事務提交時，分為兩個階段，我們總結一下：
-
數據更新時，先更新內存中的數據頁，將更新操作寫入到 redo log 中，此時 redo log 進入 prepare 狀態。并通知 MySQL Server 更新執行完了，隨時可以提交；
-
MySQL Server 根據持久化的模式是 STATEMENT 還是 ROW，決定將更新的 SQL 還是數據行寫入到 binlog，然后調用 InnoDB 的接口將 redo log 設置為 commit 狀態，更新完成。
細心的同學可能會問了，為什么 binlog 只需要提交一次，而 redo 要提交兩次？而已經有 redo log了，還需要 binlog 干啥？
要解答這個問題，得從兩種日志的本質區別說起。
redo log
用于記錄 InnoDB 引擎下事務的日志，支持崩潰數據自修復。
如果只寫 binlog，而不寫 redo log，當 MySQL 發生故障宕機時，就可能會丟失最近執行的事務數據。
binlog
binlog 記錄了 MySQL Server 層對數據庫執行的所有更改操作，用于數據歸檔、數據備份及主從復制等。
如果寫了 redo log 直接提交，不經過 prepare 階段，那么這個過程在發生故障時，如果 MySQL 部署了主從節點，主節點可以根據 redo log 恢復數據，但從節點就無法同步這部分數據。
從上圖可以看出，MySQL 主從復制時，主要依賴 Master 節點的 binlog，Slave 節點的 relay-log 和 3 個重要線程。
log dump線程
當從節點連接主節點時，主節點會為其創建一個 log dump 線程，用于讀取和發送 binlog 內容。在讀取 binlog 中時，log dump 線程會對主節點上的 bin-log 加鎖，直到讀取完成，鎖釋放。
主節點會為自己的每一個從節點創建一個 log dump 線程。
I/O線程
當從節點綁定主節點時，會創建一個 I/O 線程用來連接主節點，請求主庫中的 binlog。
當主庫的 log dump 線程發送的日志被監聽到以后，I/O 線程會把日志保存到 relay-log（中繼日志）中。
SQL線程
SQL 線程負責監聽并讀取 relay-log 中的內容，解析成具體的操作并進行重放，使其和主數據庫保持一致。每次執行完畢后相關線程會進行休眠，等待下一次喚醒。
從庫會在一定時間間隔內探測主庫的 bin-log 日志是否發生變化，如有變化，則開啟 IO 線程，繼續執行上述步驟。
