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%';
   

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

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

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

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

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

4. 執行器：
   通過以上階段所產生的執行計划進行操作。在執行之前需要判斷用戶是否具備許可權。如果沒有，返回許可權錯誤，如果具備許可權就會執行並返回結果。
   執行器會根據表的引擎定義，調用存儲引擎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值
   

本文來自博客園，作者：酷酷的sinan，轉載請註明原文鏈接：https://www.cnblogs.com/Kuju/p/16173927.html