一 MySQL 基礎架構分析
1.1 MySQL 基本架構概覽
下圖是 MySQL 的一個簡要架構圖，從下圖你可以很清晰的看到用戶的 SQL 陳述句在 MySQL 內部是如何執行的，
先簡單介紹一下下圖涉及的一些組件的基本作用幫助大家理解這幅圖，在 1.2 節中會詳細介紹到這些組件的作用，
- 連接器： 身份認證和權限相關(登錄 MySQL 的時候)，
- 查詢快取: 執行查詢陳述句的時候，會先查詢快取（MySQL 8.0 版本后移除，因為這個功能不太實用），
- 分析器: 沒有命中快取的話，SQL 陳述句就會經過分析器，分析器說白了就是要先看你的 SQL 陳述句要干嘛，再檢查你的 SQL 陳述句語法是否正確，
- 優化器： 按照 MySQL 認為最優的方案去執行，
- 執行器: 執行陳述句，然后從存盤引擎回傳資料，
簡單來說 MySQL 主要分為 Server 層和存盤引擎層：
- Server 層：主要包括連接器、查詢快取、分析器、優化器、執行器等，所有跨存盤引擎的功能都在這一層實作，比如存盤程序、觸發器、視圖，函式等，還有一個通用的日志模塊 binglog 日志模塊，
- 存盤引擎： 主要負責資料的存盤和讀取，采用可以替換的插件式架構，支持 InnoDB、MyISAM、Memory 等多個存盤引擎，其中 InnoDB 引擎有自有的日志模塊 redolog 模塊，現在最常用的存盤引擎是 InnoDB，它從 MySQL 5.5.5 版本開始就被當做默認存盤引擎了，
1.2 Server 層基本組件介紹
1) 連接器
連接器主要和身份認證和權限相關的功能相關，就好比一個級別很高的門衛一樣，
主要負責用戶登錄資料庫，進行用戶的身份認證，包括校驗賬戶密碼，權限等操作，如果用戶賬戶密碼已通過，連接器會到權限表中查詢該用戶的所有權限，之后在這個連接里的權限邏輯判斷都是會依賴此時讀取到的權限資料，也就是說，后續只要這個連接不斷開，即時管理員修改了該用戶的權限，該用戶也是不受影響的，
2) 查詢快取(MySQL 8.0 版本后移除)
查詢快取主要用來快取我們所執行的 SELECT 陳述句以及該陳述句的結果集，
連接建立后，執行查詢陳述句的時候，會先查詢快取，MySQL 會先校驗這個 sql 是否執行過，以 Key-Value 的形式快取在記憶體中，Key 是查詢預計，Value 是結果集，如果快取 key 被命中，就會直接回傳給客戶端，如果沒有命中，就會執行后續的操作，完成后也會把結果快取起來，方便下一次呼叫，當然在真正執行快取查詢的時候還是會校驗用戶的權限，是否有該表的查詢條件，
MySQL 查詢不建議使用快取，因為查詢快取失效在實際業務場景中可能會非常頻繁，假如你對一個表更新的話，這個表上的所有的查詢快取都會被清空，對于不經常更新的資料來說，使用快取還是可以的，
所以，一般在大多數情況下我們都是不推薦去使用查詢快取的，
MySQL 8.0 版本后洗掉了快取的功能，官方也是認為該功能在實際的應用場景比較少，所以干脆直接刪掉了，
3) 分析器
MySQL 沒有命中快取，那么就會進入分析器，分析器主要是用來分析 SQL 陳述句是來干嘛的，分析器也會分為幾步：
第一步，詞法分析，一條 SQL 陳述句有多個字串組成，首先要提取關鍵字，比如 select，提出查詢的表，提出欄位名，提出查詢條件等等，做完這些操作后，就會進入第二步，
第二步，語法分析，主要就是判斷你輸入的 sql 是否正確，是否符合 MySQL 的語法，
完成這 2 步之后，MySQL 就準備開始執行了，但是如何執行，怎么執行是最好的結果呢？這個時候就需要優化器上場了，
4) 優化器
優化器的作用就是它認為的最優的執行方案去執行（有時候可能也不是最優，這篇文章涉及對這部分知識的深入講解），比如多個索引的時候該如何選擇索引，多表查詢的時候如何選擇關聯順序等，
可以說，經過了優化器之后可以說這個陳述句具體該如何執行就已經定下來，
5) 執行器
當選擇了執行方案后，MySQL 就準備開始執行了，首先執行前會校驗該用戶有沒有權限，如果沒有權限，就會回傳錯誤資訊，如果有權限，就會去呼叫引擎的介面，回傳介面執行的結果，
二 陳述句分析
2.1 查詢陳述句
說了以上這么多，那么究竟一條 sql 陳述句是如何執行的呢？其實我們的 sql 可以分為兩種，一種是查詢，一種是更新（增加，更新，洗掉），我們先分析下查詢陳述句，陳述句如下：
select * from tb_student A where A.age='18' and A.name=' 張三 ';
結合上面的說明，我們分析下這個陳述句的執行流程：
-
先檢查該陳述句是否有權限，如果沒有權限，直接回傳錯誤資訊，如果有權限，在 MySQL8.0 版本以前，會先查詢快取，以這條 sql 陳述句為 key 在記憶體中查詢是否有結果，如果有直接快取，如果沒有，執行下一步，
-
通過分析器進行詞法分析，提取 sql 陳述句的關鍵元素，比如提取上面這個陳述句是查詢 select，提取需要查詢的表名為 tb_student,需要查詢所有的列，查詢條件是這個表的 id='1'，然后判斷這個 sql 陳述句是否有語法錯誤，比如關鍵詞是否正確等等，如果檢查沒問題就執行下一步，
-
接下來就是優化器進行確定執行方案，上面的 sql 陳述句，可以有兩種執行方案：
a.先查詢學生表中姓名為“張三”的學生，然后判斷是否年齡是 18， b.先找出學生中年齡 18 歲的學生，然后再查詢姓名為“張三”的學生，那么優化器根據自己的優化演算法進行選擇執行效率最好的一個方案（優化器認為，有時候不一定最好），那么確認了執行計劃后就準備開始執行了，
-
進行權限校驗，如果沒有權限就會回傳錯誤資訊，如果有權限就會呼叫資料庫引擎介面，回傳引擎的執行結果，
2.2 更新陳述句
以上就是一條查詢 sql 的執行流程，那么接下來我們看看一條更新陳述句如何執行的呢？sql 陳述句如下：
update tb_student A set A.age='19' where A.name=' 張三 ';
我們來給張三修改下年齡，在實際資料庫肯定不會設定年齡這個欄位的，不然要被技術負責人打的，其實條陳述句也基本上會沿著上一個查詢的流程走，只不過執行更新的時候肯定要記錄日志啦，這就會引入日志模塊了，MySQL 自帶的日志模塊式 binlog（歸檔日志） ，所有的存盤引擎都可以使用，我們常用的 InnoDB 引擎還自帶了一個日志模塊 redo log（重做日志），我們就以 InnoDB 模式下來探討這個陳述句的執行流程，流程如下：
- 先查詢到張三這一條資料，如果有快取，也是會用到快取，
- 然后拿到查詢的陳述句，把 age 改為 19，然后呼叫引擎 API 介面，寫入這一行資料，InnoDB 引擎把資料保存在記憶體中，同時記錄 redo log，此時 redo log 進入 prepare 狀態，然后告訴執行器，執行完成了，隨時可以提交，
- 執行器收到通知后記錄 binlog，然后呼叫引擎介面，提交 redo log 為提交狀態，
- 更新完成，
這里肯定有同學會問，為什么要用兩個日志模塊，用一個日志模塊不行嗎?
這是因為最開始 MySQL 并沒與 InnoDB 引擎( InnoDB 引擎是其他公司以插件形式插入 MySQL 的) ，MySQL 自帶的引擎是 MyISAM，但是我們知道 redo log 是 InnoDB 引擎特有的，其他存盤引擎都沒有，這就導致會沒有 crash-safe 的能力(crash-safe 的能力即使資料庫發生例外重啟，之前提交的記錄都不會丟失)，binlog 日志只能用來歸檔，
并不是說只用一個日志模塊不可以，只是 InnoDB 引擎就是通過 redo log 來支持事務的，那么，又會有同學問，我用兩個日志模塊，但是不要這么復雜行不行，為什么 redo log 要引入 prepare 預提交狀態？這里我們用反證法來說明下為什么要這么做？
- 先寫 redo log 直接提交，然后寫 binlog，假設寫完 redo log 后，機器掛了，binlog 日志沒有被寫入，那么機器重啟后，這臺機器會通過 redo log 恢復資料，但是這個時候 bingog 并沒有記錄該資料，后續進行機器備份的時候，就會丟失這一條資料，同時主從同步也會丟失這一條資料，
- 先寫 binlog，然后寫 redo log，假設寫完了 binlog，機器例外重啟了，由于沒有 redo log，本機是無法恢復這一條記錄的，但是 binlog 又有記錄，那么和上面同樣的道理，就會產生資料不一致的情況，
如果采用 redo log 兩階段提交的方式就不一樣了，寫完 binglog 后，然后再提交 redo log 就會防止出現上述的問題，從而保證了資料的一致性，那么問題來了，有沒有一個極端的情況呢？假設 redo log 處于預提交狀態，binglog 也已經寫完了，這個時候發生了例外重啟會怎么樣呢？ 這個就要依賴于 MySQL 的處理機制了，MySQL 的處理程序如下：
- 判斷 redo log 是否完整，如果判斷是完整的，就立即提交，
- 如果 redo log 只是預提交但不是 commit 狀態，這個時候就會去判斷 binlog 是否完整，如果完整就提交 redo log, 不完整就回滾事務，
這樣就解決了資料一致性的問題，
三 總結
- MySQL 主要分為 Server 層和引擎層，Server 層主要包括連接器、查詢快取、分析器、優化器、執行器，同時還有一個日志模塊（binlog），這個日志模塊所有執行引擎都可以共用,redolog 只有 InnoDB 有，
- 引擎層是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等，
- 查詢陳述句的執行流程如下：權限校驗（如果命中快取）---》查詢快取---》分析器---》優化器---》權限校驗---》執行器---》引擎
- 更新陳述句執行流程如下：分析器----》權限校驗----》執行器---》引擎---redo log(prepare 狀態---》binlog---》redo log(commit狀態)
作者：Snailclimb
鏈接：一條 SQL 陳述句在 MySQL 中如何執行的
來源：github
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/225499.html
標籤：MySQL
上一篇：使用JdbcTemplate查詢PostgreSQL的所有表
下一篇：[MySQL] mysql 5.5和 5.6 timestamp default 默認值CURRENT_TIMESTAMP問題