SQL語句在MySQL中如何執行？

這篇文章帶給大家的內容是關於SQL語句在MySQL中如何執行？有一定的參考價值，有需要的朋友可以參考一下，希望對你有幫助。

這篇文章會分析下一個 sql 語句在 MySQL 中的執行流程，包括 sql 的查詢在 MySQL 內部會怎麼流轉，sql 語句的更新是怎麼完成的。

在分析之前我會先帶你去看 MySQL 的基礎架構，知道了 MySQL 由那些元件組成已經這些元件的作用是什麼，可以幫助我們理解和解決這些問題。

一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狀態)【相關推薦：MySQL教學】

以上是SQL語句在MySQL中如何執行？的詳細內容。更多資訊請關注PHP中文網其他相關文章！