MySQL資料庫：SQL陳述句的執行程序

一、客戶端的MySQL驅動：

我們的系統在和 MySQL 資料庫進行通信前，需要先和資料庫建立連接，而這個功能就是由MySQL驅動底層幫我們完成的，建立完連接之后，我們只需要發送 SQL 陳述句就可以執行 CRUD 了，如下圖所示：

一次 SQL 請求就會建立一個連接，多個請求就會建立多個連接，假設我們的系統是部署在 tomcat 容器中的， tomcat 是可以并發處理多個請求的，這就會導致多個請求會去建立多個連接，然后使用完再都去關閉，這樣會有什么問題呢？Java系統在通過 MySQL 驅動 和 MySQL 資料庫連接的時候是基于 TCP/IP 協議的，所以如果每個請求都是新建連接和銷毀連接，這樣頻繁的創建和銷毀連接勢必會大大降低我們系統的性能，

為了解決上面的問題，采用了“池化”的思想，通過連接池維護一定數量的連接執行緒，當需要使用連接時，就直接從執行緒池中獲取，使用完畢之后，再歸還給執行緒池，通過執行緒池大大減少了不斷創建與銷毀執行緒的開銷，也不需要我們去關心連接的創建與銷毀，以及執行緒池是怎么去維護這些連接的，常見的資料庫連接池有 Druid、C3P0、DBCP，

二、MySql架構的Server層：

在介紹MySQL資料庫中SQL陳述句在Server的執行步驟前，我們先了解下MySQL的整體架構：

如果上圖不清楚，可以再看看下面的圖：

通過上面的架構圖可以得知，Server層中主要由 連接器、查詢快取、決議器/分析器、優化器、執行器 幾部分組成的，下面將主要描述下這幾部分，

1、連接器：

客戶端想要對資料庫進行操作時，前提是與資料庫建立好連接；而連接器就是用來負責跟客戶端建立連接、獲取權限、維持和管理連接的，

（1）連接方式：

MySQL既支持短連接，也支持長連接，短連接就是操作完畢后，馬上close關掉，長連接可以保持打開，減少服務端創建和釋放連接的消耗，后續程式訪問的時候還可以使用這個連接，

（2）連接池：

與客戶端的連接池一樣，為了減少頻繁創建和銷毀連接造成的不必要的性能損失，這里也采用了“池化”的思想，通過資料庫連接池去管理連接，一般我們會在連接池中使用長連接，例如：druid、c3p0、dbcp等

2、查詢快取：

MySQL快取是默認關閉的，也就是說不推薦使用快取，并且在 MySQL 8.0 版本直接將查詢快取的整塊功能刪掉了

（1）MySql為什么默認不開啟快取呢？

主要是由于它的使用場景限制的：

① 先說下快取中資料存盤格式：key（sql陳述句）- value（資料值），所以如果SQL陳述句（key）只要存在一點不同之處就會直接進行資料庫查詢了；

② 由于表中的資料不是一成不變的，大多數是經常變化的，而當資料庫中的資料變化了，那么相應的與此表相關的快取資料就需要移除掉；

3、分析/決議器：

分析器的作業主要是對要執行的SQL陳述句進行決議，最終得到抽象語法樹，然后再使用前處理器判斷抽象語法樹中的表是否存在，如果存在的話，在接著判斷select投影列欄位是否在表中存在等，

（1）詞法分析：

詞法分析用于將SQL拆解為不可再分的原子符號，稱為Token，并根據不同資料庫方言所提供的字典，將其歸類為關鍵字，運算式，字面量和運算子，

（2）語法分析：

語法分析就是根據詞法分析拆解出來的Token（原子符號）將SQL陳述句轉換為抽象語法樹，

下面就直接舉例說明，看一個SQL它的抽象語法書到底長什么樣：

SELECT id, name FROM t_user WHERE status = 'ACTIVE' AND age > 18

然后上面的SQL陳述句經過詞法分析、語法分析后得到的抽象語法書如下：

注意，為了便于理解，抽象語法樹中的關鍵字的Token用綠色表示，變數的Token用紅色表示，灰色表示需要進一步拆分，

（3）前處理器：

預處理是用來對生成的 抽象語法樹 進行語意校驗，語意校驗就是對查詢的表、select投影列欄位進行校驗，判斷表、欄位是否存在等；

4、優化器：

優化器的作用主要是將SQL經過詞法決議/語法決議后得到的語法樹，通過MySQL的資料字典和統計資訊的內容，經過一系列運算 ，最終得出一個執行計劃，包括選擇使用哪個索引，

在優化程序中，經過的一系列運算是什么呢？

（1）邏輯變換：例如SQL的where條件中存在 8>9，那邏輯轉換就是將語法樹中存在的這種常量運算式直接進行化簡，化簡為 false；除了化簡還有常量運算式計算等，

（2）代價優化：就是通過付出一些資料統計分析的代價，來得到這個SQL執行是否可以走索引，以及走哪些索引；除此之外，在多表關聯查詢中，確定最終表join的順序等；

在分析是否走索引查詢時，是通過進行動態資料采樣統計分析出來；只要是統計分析出來的，那就可能會存在分析錯誤的情況，所以在SQL執行不走索引時，也要考慮到這方面的因素

MySql執行計劃怎么查看呢？在執行的SQL陳述句前添加上 explain 關鍵字即可；

5、執行器：

MySQL 通過分析器知道了你要做什么，通過優化器知道了該怎么做，于是就進入了執行器階段，開始執行陳述句，執行器最終就是根據一系列的執行計劃去呼叫存盤引擎提供的API介面去呼叫操作資料，完成SQL的執行，

開始執行的時候，要先判斷一下建立連接的物件對這個表有沒有執行操作的權限，如果沒有，就會回傳沒有權限的錯誤；如果有，就按照生成的執行計劃進行執行，

三、InnoDB存盤引擎：

存盤引擎是對底層物理資料執行實際操作的組件，為Server服務器層提供各種操作資料的 API，資料是被存放在記憶體或者是磁盤中的，MySQL 支持插件式的存盤引擎，包括 InnoDB 、MyISAM、Memory 等，一般情況下，MySQL默認使用的存盤引擎是 InnoDB，如下圖所示，InnoDB存盤引擎整體分為記憶體架構（Memory Structures）和磁盤架構（Disk Structures）

1、Buffer Pool：

Buffer Pool （緩沖池）是 InnoDB 存盤引擎中非常重要的記憶體結構，類似 Redis 一樣的作用，起到一個快取的作用，MySQL 的資料最終是存盤在磁盤中的，如果沒有 Buffer Pool，那么每次的資料庫請求都會磁盤中查找，這樣必然會存在 IO 操作，但是有了 Buffer Pool，只有第一次在查詢的時候會將查詢的結果存到 Buffer Pool 中，這樣后面再有請求的時候就會先從緩沖池中去查詢，如果沒有再去磁盤中查找，然后在放到 Buffer Pool 中，如下圖

UPDATE students SET stuName = '小強' WHERE id = 1

比如這條SQL，按照上面的那幅圖，SQL 陳述句的執行步驟大致是這樣子的：

• （1）innodb 存盤引擎先在緩沖池中查找 id=1 的這條資料是否存在
• （2）如果快取不存在，那么就去磁盤中加載，并將其存放在緩沖池中
• （3）該條記錄會被加上一個獨占鎖

備注：

buffer pool 和 查詢快取的區別：

（1）查詢快取：查詢快取位于Server層，MySQL Server首選會從查詢快取中查看是否曾經執行過這個SQL，如果曾經執行過的話，之前執行的查詢結果會以Key-Value的形式保存在查詢快取中，key是SQL陳述句，value是查詢結果，我們將這個程序稱為查詢快取！

（2）Buffer Pool位于存盤引擎層，Buffer Pool就是MySQL存盤引擎為了加速資料的讀取速度而設計的緩沖機制

2、undo日志檔案：記錄資料被修改前的樣子

Innodb 存盤引擎的最大特點就是支持事務，如果事務提交失敗，那么該事務中所有的操作都必須回滾到執行前的樣子，而這個回滾的操作，就是利用undo log檔案完成的，

undo 顧名思義，就是沒有做，沒發生的意思，undo log 就是沒有發生事情（原本事情是什么）的一些日志

剛才我們介紹過了，在準備更新一條SQL陳述句的時候，該條陳述句對應的資料已經被加載到 Buffer pool 中了，實際上這里還有這樣的操作，就是在將該條陳述句加載到 Buffer Pool 中的時候同時會往 undo 日志檔案中插入一條日志，也就是將 id=1 的這條記錄的原來的值記錄下來，便于事務失敗后進行回滾

到這一步，我們執行的 SQL 陳述句對應的資料已經被加載到 Buffer Pool 中了，然后開始更新這條陳述句，更新的操作實際是在Buffer Pool中執行的，那問題來了，更新完資料之后，Buffer Pool緩沖池中的中的資料就會和資料庫中的資料庫不一致，那就是說Buffer Pool 中的資料成了臟資料？沒錯，目前這條資料就是臟資料，Buffer Pool 中的記錄是“小強”資料庫中的記錄是“旺財” ，這種情況 MySQL是怎么處理的呢？我們接著往下看

3、redo日志檔案：記錄資料被修改后的樣子

前言：redo 日志檔案是 InnoDB 特有的，他是存盤引擎級別的，不是 MySQL 級別的

除了從磁盤中加載檔案和將操作前的記錄保存到 undo 日志檔案中之外，其他的操作是在記憶體中完成的，記憶體中的資料的特點就是：斷電丟失，如果此時 MySQL 所在的服務器宕機了，那么 Buffer Pool 中的資料會全部丟失的，這個時候 redo 日志檔案就需要來大顯神通了

redo 就是準備去做、將要去做的意思，redo log 記錄的是將要做的一些操作，例如，此時將要做的是update students set stuName='小強' where id=1; 那么這條操作就會被記錄到 redo log buffer 中，redo log buffer是MySQL 為了提高效率，所以將這些操作都先放在記憶體中去完成

這時候假設服務器宕機了，那么快取中的資料還是丟失了，那能不能不要放在記憶體中，直接保存到磁盤呢？很顯然不行，因為在上面也已經介紹了，在記憶體中的操作目的是為了提高效率，此時，如果 MySQL 真的宕機了，那么沒關系的，因為 MySQL 會認為本次事務是失敗的，所以資料依舊是更新前的樣子，并不會有任何的影響，

到了這里，SQL陳述句也更新好了，那么需要將更新的值提交了，也就是需要提交本次的事務，只要事務成功提交了，才會將最后的變更保存到資料庫，在提交事務前會將 redo Log Buffer 中的資料持久化到磁盤中，就是將 redo log buffer 中的資料寫入到 redo log 磁盤檔案中，

如果 redo log Buffer 刷入磁盤后，資料庫服務器宕機了，那我們更新的資料怎么辦？此時資料是在記憶體中，資料豈不是丟失了？不，這次資料就不會丟失了，因為 redo log buffer 中的資料已經被寫入到磁盤了，已經被持久化了，就算資料庫宕機了，在下次重啟的時候 MySQL 也會將 redo 日志檔案內容恢復到 Buffer Pool 中

• （1）準備更新一條 SQL 陳述句
• （2）MySQL（innodb）會先去緩沖池（Buffer Pool）中去查找這條資料，沒找到就會去磁盤中查找，如果查找到就會將這條資料加載 到緩沖池（Buffer Pool）中
• （3）在加載到 Buffer Pool 的同時，會將這條資料的原始記錄保存到 undo 日志檔案中
• （4）innodb 會在 Buffer Pool 中執行更新操作
• （5）更新后的資料會記錄在 redo log buffer 中
• （6）MySQL 提交事務的時候，會將 redo log buffer 中的資料寫入到 redo 日志檔案中，刷磁盤可以通過 innodb_flush_log_at_trx_commit 引數來設定，值為 0 表示不刷入磁盤，值為 1 表示立即刷入磁盤，值為 2 表示先刷到 os cache，一般情況下都是立即刷入磁盤
• （7）myslq 重啟的時候會將 redo 日志恢復到緩沖池中

4、bin log日志檔案：記錄整個操作程序

前言：bin log和 redo log有些相似，兩者的主要區別有：

（1）redo log是 InnoDB 存盤引擎特有的日志檔案，而bin log屬于是 MySQL 級別的日志

（2）redo log適用于崩潰恢復，bin log適用于主從復制和資料恢復

redo log記錄的東西是偏向于物理性質的，如：“對什么資料，做了什么修改”，bin log是偏向于邏輯性質的，類似于：“對 students 表中的 id 為 1 的記錄租了更新操作” ，

bin log檔案是如何刷入磁盤的? bin log的刷盤策略可以通過sync_bin log來修改，默認為0，表示先寫入 os cache，也就是說在提交事務的時候，資料不會直接到磁盤中，這樣如果宕機bin log資料仍然會丟失，所以建議將sync_bin log設定為 1 表示直接將資料寫入到磁盤檔案中，

既然bin log也是日志檔案，那它是在什么記錄資料的呢？其實 MySQL 在提交事務的時候，不僅僅會將 redo log buffer 中的資料寫入到redo log 檔案中，同時也會將本次修改的資料記錄到 bin log檔案中，同時會將本次修改的bin log檔案名和修改的內容在bin log中的位置記錄到redo log中，最后還會在redo log最后寫入 commit 標記，這樣就表示本次事務被成功的提交了，

如果在資料被寫入到bin log檔案的時候，剛寫完，資料庫宕機了，資料會丟失嗎？

首先可以確定的是，只要redo log最后沒有 commit 標記，說明本次的事務一定是失敗的，但是資料是沒有丟失了，因為已經被記錄到redo log的磁盤檔案中了，在 MySQL 重啟的時候，就會將 redo log 中的資料恢復（加載）到Buffer Pool中，

好了，到目前為止，一個更新操作我們基本介紹得差不多，但是你有沒有感覺少了哪件事情還沒有做？是不是你也發現這個時候被更新記錄僅僅是在記憶體中執行的，哪怕是宕機又恢復了也僅僅是將更新后的記錄加載到Buffer Pool中，這個時候 MySQL 資料庫中的這條記錄依舊是舊值，也就是說記憶體中的資料在我們看來依舊是臟資料，那這個時候怎么辦呢？

其實 MySQL 會有一個后臺執行緒，它會在某個時機將我們Buffer Pool中的臟資料刷到 MySQL 資料庫中，這樣就將記憶體和資料庫的資料保持統一了，

5、小結：

• （1）首先MySQL執行器根據 執行計劃 呼叫存盤引擎的API查詢資料
• （2）存盤引擎先從快取池buffer pool中查詢資料，如果沒有就會去磁盤中查詢，如果查詢到了就將其放到快取池中
• （3）在資料加載到 Buffer Pool 的同時，會將這條資料的原始記錄保存到 undo 日志檔案中
• （4）innodb 會在 Buffer Pool 中執行更新操作
• （5）更新后的資料會記錄在 redo log buffer 中
• （6）提交事務在提交的同時會做以下三件事
• （7）（第一件事）將redo log buffer中的資料刷入到redo log檔案中
• （8）（第二件事）將本次操作記錄寫入到 bin log檔案中
• （9）（第三件事）將bin log檔案名字和更新內容在 bin log 中的位置記錄到redo log中，同時在 redo log 最后添加 commit 標記
• （10）使用一個后臺執行緒，它會在某個時機將我們Buffer Pool中的更新后的資料刷到 MySQL 資料庫中，這樣就將記憶體和資料庫的資料保持統一了

參考文章：

https://juejin.cn/post/6897388295060684807

https://juejin.cn/post/6920076107609800711