MySQL學習之聊查詢語句執行流程

如果想深入學習 MySQL ，那麼應該從宏觀的架構上面著手，這篇我們學習 MySQL 查詢語句執行的流程，希望對大家有幫助！

本篇文章MySQL 版本為8.0.18

架構圖

解析器

解析器的作用是針對客戶端傳來的SQL 語句進行以下工作：

• 語法解析：檢查SQL 語句的語法，括號、引號是否閉合等
• 詞法解析：把SQL 語句中的關鍵字、表名、欄位名稱分割成一個個節點，最後得到一顆解析樹

預處理器

#解析器主要是檢查語法詞法方面，但是如果語法詞法都正確，但是表、字段是不存在的，那麼這段SQL 語句也是無法正確執行的。

所以預處理器的作用是：語意解析，判斷解析樹的語意是否正確，表、字段這些是否存在，預處理後會得到一顆新的解析樹。

查詢最佳化器

查詢最佳化器結構

#在MySQL 中一條SQL 語句的執行方式有多種，雖然最終都會得到相同的結果，但是存在開銷上的差異，具體選擇哪一種執行方式是由查詢優化器來決定的。比方說：

• 表中有多個索引可以選擇，具體選擇哪一個索引
• 當我們對多張表進行關聯查詢時，以哪一張表的數據為基準表

查詢最佳化器是基於開銷（cost）的最佳化器，它的工作原理是根據解析樹產生的多種執行計劃，會評估各種執行方式所需的開銷（cost），最終會得到一個開銷最小的執行計劃作為最終方案。

但是這個開銷最小的執行方式不一定是最優的執行方式，例如本該使用索引，卻進行了全表掃描等。雖然查詢優化器中有《優化》兩個字，但這個優化並不是萬能的，很多時候更需要考慮 SQL 語句寫得是否合理。

邏輯查詢最佳化

邏輯查詢最佳化主要負責進行一些關係代數對SQL 語句進行最佳化，使SQL 語句執行效率更高

邏輯查詢優化我們可以使用幾個案例來簡單理解

• 子查詢合併

  #合併前

  SELECT * FROM t1 WHERE a1<10 AND (
    EXISTS(SELECT a2 FROM t2 WHERE t2.a2<5 AND t2.b2=1) OR
    EXISTS(SELECT a2 FROM t2 WHERE t2.a2<5 AND t2.b2=2)
  );

  合併後

  SELECT * FROM t1 WHERE a1<10 AND (
    EXISTS(SELECT a2 FROM t2 WHERE t2.a2<5 AND (t2.b2=1 OR t2.b2=2)
  );

  把多個子查詢透過合併查詢條件合併查詢，把多次連線運算減少為單次表掃描和單次連線

• 等價謂詞重寫

  #像我們熟悉的like 模糊查詢，% 寫在條件後面才會進行索引範圍查詢，其實這是查詢優化器的功勞

  假設使用的條件都是有建立索引的，重寫前

  SELECT * FROM USERINFO WHERE name LIKE 'Abc%';

  重寫後

  SELECT * FROM USERINFO WHERE name >= &#39;Abc&#39; AND name < &#39;Abd&#39;;

  這就是為什麼能進行索引範圍查詢的答案

• 條件簡化

  條件簡化也是利用一些等式、代數關係來實現簡化

  • 去除表達式中的冗餘括號，減少語法分析時產生的AND和OR 樹的層次，例如((a AND b) AND (c AND d)) 簡化為a AND b AND c AND d
  • 常數傳遞，例如col1 = col2 AND col2 = 3 簡化為col1 = 3 AND col2 = 3
  • 表達式計算，對於一些可直接求解的表達式會轉換為最終的計算結果，例如col1 = 1 2 簡化為col1 = 3

物理查詢最佳化

物理查詢最佳化主要做的工作是根據SQL語句分別對多種執行計畫進行開銷的評估

物理查詢最佳化主要解決以下幾個問題：

• 單表掃描中採用哪一種方式是開銷最小的（掃描索引回表or 全表掃描）

• 存在表連接的時候使用哪種連接方式是開銷最小的

##簡單了解代價評估，代價評估是基於CPU 代價和IO 代價兩個維度的

掃描方式代價評估公式順序掃描N_page * a_page_IO_time N_tuple * a_tuple_CPU_time索引掃描C_index N_page_index * a_page_IO_time

上述參數說明如下：

• a_page_IO_time， 一個資料頁載入的IO耗時
• N_page，資料頁數
• N_tuple，元組數（元組理解為一行資料）
• a_tuple_CPU_time，一個元組從資料頁解析的CPU耗時
• C_index，索引的IO耗時
• N_page_index，索引頁數量

關於索引成本計算可以參考這篇文章：MySQL查詢為什麼選擇使用這個索引？ ——基於MySQL 8.0.22索引成本計算

執行計畫

執行計畫是查詢最佳化器的產物，最後交給儲存引擎執行。執行計畫可以幫助我們得知 MySQL 會怎麼執行這個 SQL 語句。

使用explain 關鍵字查看SQL 語句的執行計劃，可以得到以下資訊：

• id：嵌套查詢中查詢的執行順序
• possible_keys：本次查詢可能用到的索引
• Key：實際用到的索引
• rows：得到結果大概要檢索多少行資料
• select_type多表之間的連接類型
• extra：額外的信息，是否有索引覆蓋、索引下推等

儲存引擎

MySQL 服務端規定了資料如何儲存、如何擷取、如何更新的規範，這個規範由儲存引擎來實現，不同的儲存引擎的實現方式不同，所以不同的儲存引擎會呈現其獨特的功能和特點。其中最常用的儲存引擎是InnoDB 和MyISAM

簡單說說這兩款儲存引擎的特點

InnoDB：

• 支援外鍵、事務，保證了資料的完整性和一致性
• 支援更細的鎖定粒度，對鎖定的控制更好，讀寫效率更高

##MyISAM

• #不支援事務，只支援行鎖，適合資料只讀的場景

• 儲存引擎方面暫時先不展開，會在其他文章繼續穿插他們的對比，以及會詳細分析InnoDB 更新資料的流程

• 總結

• 從前，只知道在客戶端軟體上寫下SQL 語句，點選執行，拿到資料
  到現在終於了解到一條查詢語句傳入MySQL 服務端後需要經歷這一系列的操作

• 解析器根據這條SQL 語句的語法、詞法進行檢查，如果沒有錯誤的話會按關鍵字拆分成一個節點，最終形成一棵解析樹

#預處理器會檢查SQL 語句的語義，檢查SQL 語句是否有歧義、字段等是否存在，形成一棵新的解析樹

######查詢優化器拿到這個解析樹產生的各種執行計劃，經過邏輯查詢優化、物理查詢優化後得到一個開銷最小的執行計劃############執行引擎拿到這份執行計劃調用存儲引擎的接口############存儲引擎根據執行計劃進行數據查詢，查詢會查詢呼叫作業系統中檔案系統的一些接口，完成資料查詢，最後傳回給客戶端############【相關推薦：###mysql影片教學###】 ###

以上是MySQL學習之聊查詢語句執行流程的詳細內容。更多資訊請關注PHP中文網其他相關文章！