歡迎光臨
每天分享高質量文章

解析MySQL基礎架構及一條SQL陳述句的執行流程和流轉

前言


 

本篇文章分析SQL陳述句在MySQL中的執行流程,包括SQL的查詢在MySQL內部會怎麼流轉,SQL陳述句的更新是怎麼完成的。在分析之前我們一起看看MySQL的基礎架構,知道了 MySQL由那些組件組成以及這些組件的作用是什麼,可以幫助我們理解和解決這些問題。

 

MySQL架構分析


 

下麵是MySQL的一個簡要架構圖:

 

 

MySQL主要分為Server層和儲存引擎層


 

Server層

主要包括連接器、查詢快取、分析器、優化器、執行器等,所有跨儲存引擎的功能都在這一層實現,比如儲存過程、觸發器、視圖,函式等,還有一個通用的日誌模塊binglog日誌模塊。

 

儲存引擎

主要負責資料的儲存和讀取,採用可以替換的插件式架構,支持InnoDB、MyISAM、Memory等多個儲存引擎,其中InnoDB引擎有自有的日誌模塊redolog 模塊,InnoDB 5.5.5版本作為預設引擎。

 

連接器

主要負責用戶登錄資料庫,進行用戶的身份認證,包括校驗賬戶密碼,權限等操作,如果用戶賬戶密碼已通過,連接器會到權限表中查詢該用戶的所有權限,之後在這個連接里的權限邏輯判斷都是會依賴此時讀取到的權限資料,也就是說,後續只要這個連接不斷開,即時管理員修改了該用戶的權限,該用戶也是不受影響的。

 

查詢快取

連接建立後,執行查詢陳述句的時候,會先查詢快取,MySQL會先校驗這個SQL是否執行過,以Key-Value的形式快取在記憶體中,Key是查詢預計,Value是結果集。如果快取key被命中,就會直接傳回給客戶端,如果沒有命中,就會執行後續的操作,完成後也會把結果快取起來,方便下一次呼叫。當然在真正執行快取查詢的時候還是會校驗用戶的權限,是否有該表的查詢條件。

 

MySQL查詢不建議使用快取,因為對於經常更新的資料來說,快取的有效時間太短了,往往帶來的效果並不好,對於不經常更新的資料來說,使用快取還是可以的,MySQL 8.0版本後刪除了快取的功能,官方也是認為該功能在實際的應用場景比較少,所以乾脆直接刪掉了。

 

分析器

MySQL沒有命中快取,那麼就會進入分析器,分析器主要是用來分析SQL陳述句是來幹嘛的,分析器也會分為幾步:

 

第一步,詞法分析,一條SQL陳述句有多個字串組成,首先要提取關鍵字,比如select,提出查詢的表,提出欄位名,提出查詢條件等。

第二步,語法分析,主要是判斷你輸入的SQL是否正確,是否符合MySQL的語法。

 

完成這2步之後,MySQL就準備開始執行,但是如何執行,怎麼執行是最好的結果呢?這個時候就需要優化器上場了。

 

優化器

優化器的作用就是它認為的最優的執行方案去執行(雖然有時候也不是最優),比如多個索引的時候該如何選擇索引,多表查詢的時候如何選擇關聯順序等。

 

執行器

當選擇了執行方案後,MySQL就準備開始執行了,首先執行前會校驗該用戶有沒有權限,如果沒有權限,就會傳回錯誤信息,如果有權限,就會去呼叫引擎的接口,傳回接口執行的結果。

 

陳述句分析


查詢陳述句

說了以上這麼多,那麼究竟一條SQL陳述句是如何執行的呢?其實我們的sql可以分為兩種,一種是查詢,一種是更新(增加,更新,刪除)。我們先分析下查詢陳述句,陳述句如下:

 

select * from tb_student  A where A.age='18' and A.name='張三';

結合上面的說明,我們分析下這個陳述句的執行流程:

  • 先檢查該陳述句是否有權限,如果沒有權限,直接傳回錯誤信息,如果有權限,在MySQL 8.0版本以前,會先查詢快取,以這條SQL陳述句為key在記憶體中查詢是否有結果,如果有直接快取,如果沒有,執行下一步。

  • 通過分析器進行詞法分析,提取SQL陳述句的關鍵元素,比如提取上面這個陳述句是查詢select,提取需要查詢的表名為tb_student,需要查詢所有的列,查詢條件是這個表的id=’1’。然後判斷這個SQL陳述句是否有語法錯誤,比如關鍵詞是否正確等,如果檢查沒問題就執行下一步。
  • 接下來就是優化器進行確定執行方案,上面的SQL陳述句,可以有兩種執行方案:

     

     a.先查詢學生表中姓名為“張三”的學生,然後判斷是否年齡是18。
     b.先找出學生中年齡18歲的學生,然後再查詢姓名為“張三”的學生。

    
    

複製代碼

  • 那麼優化器根據自己的優化演算法進行選擇執行效率最好的一個方案(優化器認為,有時候不一定最好)。那麼確認了執行計劃後就準備開始執行了。
  • 進行權限校驗,如果沒有權限就會傳回錯誤信息,如果有權限就會呼叫資料庫引擎接口,傳回引擎的執行結果。

更新陳述句

以上就是一條查詢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的樣式了,MyISAM引擎是沒有redo log的,那麼我們知道它是不支持事務的,所以並不是說只用一個日誌模塊不可以,只是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),這個日誌模塊所有執行引擎都可以共用。
    • 引擎層是插件式的,目前主要包括,MyISAM、InnoDB、Memory等。
    • 查詢陳述句的執行流程如下:權限校驗(如果命中快取)→查詢快取分析器優化器權限校驗執行器引擎
    • 更新陳述句執行流程如下:分析器權限校驗執行器引擎redo log(prepare 狀態)→binlogredo log(commit狀態)

 

參考


  • 《mysql專欄45講》
  • MySQL 5.6參考手冊

原文:https://juejin.im/post/5c91b5d7e51d456f957ebd10

    赞(0)

    分享創造快樂