Mysql 執行流程

酷酷的sinan發表於2022-04-21
MySql

1、邏輯剖析

sql 執行流程為：sql語句 -> 查詢快取 -> 解析器 -> 優化器 -> 執行器。

1.1 伺服器處理客戶端請求

​ 客戶端程式 connectors >> 連線池 >> SQL介面 >> 解析器 >> 優化器 >> 查詢快取 >> 外掛式儲存引擎 >> File（檔案系統/日誌檔案）

1. 客戶端程式 : 包括一些mysql工具如：native 或者語言工具如：php 、go 、python
2. 連線池  : 提供多個使用者客戶端和服務端互動的執行緒
3. SQL介面 : 接收sql命令，返回查詢結果
4. 解析器  : 進行sql語法的解析、語意解析、生成語法樹
5. 優化器  : mysql核心元件，對sql命令進行優化
6. 快取    : 以key -> value方式快取查詢結果 (如果查詢sql指令有快取直接在SQL介面部分返回快取結果)
7. 儲存引擎 : 與底層檔案驚醒互動，查詢資料檔案系統、日誌檔案等

1.2 Connertors

connectors 是指在不同語言中與sql的互動。 要使用mysql 可以編寫程式與mysql服務端建立tcp連線 按照定義好的mysql協議進行互動。

接下來的mysql server結構分為如下三層。

1.3 連線層

客戶端訪問mysql服務端前，需要建立tcp連線

經過三次握手連線成功後，mysql服務端對tcp傳輸的賬號密碼進行認證、許可權獲取（通過許可權表獲取許可權寫入記憶體）。

由於多個系統與mysql建立的連線並不止一個，所以為了結局tcp無限建立銷燬TCP連線帶來的資源消耗、效能下降問題。mysql伺服器有專門的tcp連線池限制最大連線數，採用長連線模式複用tcp連線，來解決以上問題

1.4 服務層

服務層主要完成大多數的核心服務功能，如SQL介面，快取查詢、SQL分析以及優化部分內建函式的執行，所有的跨儲存引擎功能也在這一層實現，如：儲存過程、儲存函式。

在該層中，伺服器會解析查詢並建立相應的解析樹、完成對其的優化如：確定表查詢的順序，是否利用索引等，最後生成相應的執行操作。

如果是查詢SELECT語句 ，服務會查詢內部快取，如果快取空間足夠大，可以解決大量讀操作的環境中很好的提升系統的效能。

  • SQL Interface 介面

    • 接收sql命令，返回查詢結果
    • 支援DML、DDl、儲存過程、檢視、觸發器、自定義函式等多種SQL語言介面

  • Parser：解析器

    • 進行sql語法的解析、語意解析、將sql分解成資料結構
    • sql指令傳遞到解析器的時候被解析器驗證和解析，生成一個語法樹，建立好之後還會對sql查詢進行語法上的優化，進行查詢重寫。

  • 查詢優化器

    • sql命令在解析之後、查詢之前會使用查詢優化器確定sql語句的執行路徑，生成一個執行計劃

    • 這個執行計劃表明應該使用哪些索引進行查詢，表之間的順序應該如何，最後按照執行計劃中的步驟呼叫儲存引擎提供的方法來真正的執行查詢，並將結果返回

    • 他使用選取-投影-連線策略查詢，如：

    • select id,name from user where gender='女'

      這個sql查詢先根據where進行選取，而不是將資料全部查詢出來在進行過濾。

      這個sql先根據id和name進行屬性投影，而不是將所有欄位取出來在過濾

      將兩個條件連線起來生成最後的結果

  • 查詢快取元件（ mysql8.0 已經優化掉該過程 ）

    • mysql內部維持著一些cache和buffer，比如query cache用來快取一條select語句的結果。如果能在查詢中找到對應的快取，則不必在執行解析優化的過程直接返回快取結果。
    • 快取機制由一系列小快取組成。如：表快取、記錄快取、key快取、許可權快取,並且可以在不同客戶端之間共享
    • 從5.7。2之後不推薦使用查詢快取。在mysql8.0中刪除該機制。

1.5 引擎層

mysql的架構可以在不同場景中應用併發揮良好的作用，主要體現在儲存引擎哈桑，外掛式的引擎架構將查詢處理和其他系統任務以及資料的儲存提取分離。這種架構可以根據業務去求和實際需要選擇合適的儲存引擎，同時開源的mysql還允許開發人員設定自己的開發引擎。

外掛式的儲存引擎層，真正的負責了mysql中資料的儲存和提取，對物理伺服器級別維護的底層資料執行操作，伺服器通過api與儲存引擎通訊。不同的儲存引擎具有的功能不同，這樣我們可以根據自己的實際需要進行選取。

1.6 儲存層

所有的資料、資料庫、表的定義，表的資料、索引等都是儲存在檔案系統上以檔案的形式存在，並且完雨儲存引擎的互動，在檔案系統下，可以使用本地磁碟，也可以使用DAS、NAS、SAN等各種儲存系統。

2、SQL執行流程

2.1 mysql中的sql執行流程圖

Mysql中的查詢流程：

  1. 查詢快取： Server如果在快取中發現了sql語句，則直接返回 如果沒有就進入解析器階段。需要說明的是因為查詢快取往往效率不高，所以8.0就拋棄了這個功能。

    ​ 一般建議在靜態表裡使用查詢快取，靜態表就是極少更新的表，比如系統的配置表、字典表。好在mysql 提供了按需使用的方式，可以將my.cnf引數query_cache_type設定成DEMAND，代表sql語句中有sql_cache關鍵詞時才快取如：

    #quert_cache_type 0表示關閉查詢快取OFF。1表示開啟查詢快取ON.  2 表示 DEMAND
query_cache_type=2 
#對於你確定要使用查詢快取的語句時可以用SQL_CACHE顯式指定：
SELECT SQL_CACHE * FROM test where id = 1

    ​ 在 5.7中檢視是否開啟查詢快取：

    show variables like '%query_cache_type%'

    ​ 監控查詢快取的命中率：

    show status like '%Qcache%';

  1. 解析器：介於sql語句的語法分享、語義分析

  1. 優化器： 在優化器中會確定sql語句的執行路徑，比如式根據全表檢索 還是根據索引檢索等

    ​ 在查詢優化器中，分為邏輯查詢優化和物理查詢優化兩個大塊

    • 物理查詢優化是通過索引和表連線方式等技術進行優化
    • 邏輯查詢優化是通過sql等價變換提升查詢效率，直白點說就是換一種執行效率高的寫法。

    截止到現在，還沒有真正的讀表，而是產出了一個執行計劃。於是進入到執行器階段

  2. 執行器：
    通過以上階段所產生的執行計劃進行操作。在執行之前需要判斷使用者是否具備許可權。如果沒有，返回許可權錯誤，如果具備許可權就會執行並返回結果。
    執行器會根據表的引擎定義，呼叫儲存引擎API對錶進行讀寫。儲存引擎Api只是抽象介面，下面還有一層儲存引擎層，具體實現還要看錶選擇的儲存引擎。

總結

至此sql語句在mysql中就執行完了。對於有索引的表，執行的邏輯也差不多。

2.2 mysql中的sql執行原理

​ 前面的機構很複雜，我們值需要抓去最核心的部分：sql執行原理。 不同的dbms的sql執行原理相同，在不同的軟體中，各有各的實現路徑

​ 在不同的模組中，sql執行所使用的資源是怎麼樣的，如何對一條sql語句的執行時間進行分析：

  1. 確認profiling值

    ​ 瞭解查詢語句底層執行的過程：SELECT @@profiling; 或者 SHOW variables like '%profiling%';

    ​ 檢視是否開啟了計劃，開啟這個值可以讓mysql收集在sql語句執行時所使用的資源情況，命令如下：

    SELECT @@profiling; 
#或
SHOW variables like '%profiling%';


#profiling  0代表關閉 1代表開啟
set profiling = 1;



#檢視執行語句的資源情況
SHOW PROFILES 
# 或
SHOW PROFILE FOR QUERY 157  #SHOW PROFILES 中的id值

相關文章