瞭解MySQL查詢語句執行過程(5大組件)

開篇

相信廣大程序員朋友經常使用MySQL數據庫作為書籍持久化的工具，我們最常使用的就是MySQL中的SQL語句，從客戶端向MySQL發出一條條指令，然後獲取返回的數據結果進行後面的邏輯處理。盡管大傢經常使用SQL語句完成工作，你是否關註過其執行的階段，利用瞭哪些技術完成？今天，就帶大傢一起看看MySQL數據庫處理SQL請求的全過程。下面將會講述如下內容：

查詢請求在MySQL中的處理流程

MySQL 中處理SQL的組件介紹，包括：

• 連接器
• 查詢緩存
• 分析器
• 優化器
• 執行器

查詢請求的執行流程

眾所周知在MySQL數據庫應用中查詢請求是使用最多的，假設我們輸入代碼段1 中的SQL，通過客戶端請求MySQL服務器，會得到一個包含user的結果集。但是，其中MySQL的處理過程我們並不瞭解，那麼下面就讓我們一起看看在查詢請求前後MySQL服務端發生瞭些什麼吧。

Select * from user where userId=1

代碼段1

如圖1 所示，整張圖由三部分組成，從上到下分別是客戶端（紫色）、MySQL Server層（綠色）、MySQL存儲引擎層（黃色）。

• 客戶端不言而喻，主要負責與MySQL Server層建立連接，發送查詢請求以及接受響應的結果集。
•  MySQL Server層，主要包括連接器、查詢緩存、分析器、優化器、執行器等。這些組件包含瞭MySQL的大部分主要功能，例如平時使用最多的存儲過程、觸發器、視圖都在這一層中。 還有一個通用的日志模塊 bin log。
• MySQL 存儲引擎層，主要負責數據的存儲和提取。其支持多個存儲引擎，例如：InnoDB、MyISAM等。常用的有InnoDB，它從MySQL 5.5.5版本開始成為瞭MySQL的默認存儲引擎，重要的是InnoDB 引擎包含瞭自帶的日志模塊 redo log，這個在後面講述更新語句的時候會著重提到。

圖1 MySQL 查詢請求處理流程：

上面介紹瞭MySQL的組件結構，那麼這裡將其處理SQL語句的流程簡單梳理一遍，之後再對每個組件逐一進行介紹。如圖2 所示，在圖1 的基礎上加上瞭流程處理的編號，順著編號來看看MySQL的各各組件是如何處理SQL查詢請求的。

• 連接器： 當客戶端登陸MySQL的時候，對身份認證和權限判斷。
• 查詢緩存: 執行查詢語句的時候，會先查詢緩存（MySQL 8.0 版本後移除）。
• 分析器: 假設在沒有命中查詢緩存的情況下，SQL請求就會來到分析器。分析器負責明確SQL要完成的功能，以及檢查SQL的語法是否正確。
• 優化器： 為SQL提供優化執行的方案。
•  執行器: 將語句分發到對應的存儲引擎執行，並返回數據。

圖2 SQL 請求執行流程

MySQL組件定義

上面通過一張大圖將SQL執行流程進行瞭梳理，這裡將對應的組件進行詳細介紹。

連接器

客戶端需要通過連接器訪問MySQL Server，連接器主要負責身份認證和權限鑒別的工作。也就是負責用戶登錄數據庫的相關認證操作，例如：校驗賬戶密碼，權限等。在用戶名密碼合法的前提下，會在權限表中查詢用戶對應的權限，並且將該權限分配給用戶。在連接完成以後可以通過圖3看到連接狀態，可以通過命令行“show processlist”生成圖3的查詢結果。其中“Command”列返回的內容中，“Sleep”表示MySQL相同中對應一個空閑連接。而“Query”表示正在查詢的連接。

圖3 連接狀態

上面提到瞭連接狀態，這裡將5種連接狀態整理為如下表格，方便大傢參考。

MySQL將連接器中的連接分為長連接和短連接：

• 長連接是指連接成功後，客戶端請求一直使用是同一個連接。
•  短連接是指每次執行完SQL請求的操作之後會斷開連接，如果再有SQL請求會重新建立連接。

由於短連接會反復創建連接消耗相同資源，因此多數情況下會選擇長連接。但是為瞭保持長連接，會占用系統內存，而這些被占用的內存知道連接斷開以後才會釋放。

這裡提出瞭兩個解決方案：

• 定期斷開長連接，每隔一段時間或者執行一個占用內存的大查詢以後斷開連接，從而釋放內存，當查詢的時候再重新創建連接。
• MySQL 5.7 或者更高的版本，通過執行 mysql_reset_connection 來重新初始化連接。此過程不會重新建立連接，但是會釋放占用的內存，將連接恢復到剛剛創立連接的狀態。

查詢緩存

在建立與數據庫的連接以後就可以執行SQL語句來，不過在執行之前會先查詢緩存，其目的是查看是否之前執行過該語句，並且將執行結果按照key-value的形式緩存在內存中瞭。

Key 是查詢的SQL語句，Value 是查詢的結果。如果緩存 Key 被命中，就會直接返回給客戶端，如果沒有命中，就會執行後續的操作，執行完SQL仍舊會把結果緩存起來，方便下一次調用。

MySQL 查詢不建議使用緩存，因為會出現這樣的場景：如果針對某張表進行更新，針對這張表的查詢緩存就會被清空。如果張表不斷地被使用（更新、查詢），那麼查詢緩存會頻繁地失效，獲取查詢緩存也失去瞭意義。不過可以運用在一些修改不頻繁的數據表，例如：系統配置、或者修改不頻繁的表。緩存的淘汰策略是先進先出，適用於查詢遠大於修改的情況下， 否則建議使用Redis或者其他做緩存工具。因此大多數情況下不推薦使用查詢緩存。MySQL 8.0 版本後刪除瞭查詢緩存的功能，官方認為該功能應用場景較少，所以將其刪除。

如果你不需要在MySQL中使用查詢緩存，也可以將參數query_cache_type設置成 DEMAND，那麼默認情況下的執行SQL語句時就不會使用查詢緩存瞭。如果打開瞭緩存可以通過“show status like 'Qcache%'”命令查看緩存的情況。

如圖4 所示，其中幾個使用較多的狀態值如下：

Qcache_inserts 是否有新的數據添加，每有一條數據添加Value會加一。

Qcache_hits 查詢語句是否命中緩存，每有一條語句命中Value會加一。

Qcache_free_memory 緩存空閑大小。

如圖4 緩存狀態

分析器

如果查詢緩存沒有命中，那麼SQL請求會進入分析器，分析器是用來分辨SQL語句的執行目的，其執行過程大致分為兩步：

第一步，詞法分析(Lexical scanner)

，主要負責從SQL 語句中提取關鍵字，比如：查詢的表，字段名，查詢條件等等。

第二步，語法規則(Grammar rule module)

，主要判斷SQL語句是否合乎MySQL的語法。

其實說白瞭詞法分析(Lexical scanner) 就是將整個SQL語句拆分成一個個單詞，而語法規則(Grammar rule module)則根據MySQL定義的語法規則生成對應的數據結構，並存儲在對象結構當中。其結果供優化器生成執行計劃，再調用存儲引擎接口執行。來看下面這個例子，假設有這樣一個SQL語句“select username from userinfo”。

先通過詞法分析，從左到右逐個字符進行解析，獲得如表1的四個單詞。

表1 語法分析關鍵字

然後再通過語法規則解析，判斷輸入的SQL 語句是否滿足MySQL語法，並且生成圖5的語法樹。由SQL語句生成的四個單詞中，識別出兩個關鍵字，分別是select 和from。根據MySQL的語法Select 和 from之間對應的是fields 字段，下面應該掛接username；在from後面跟隨的是Tables字段，其下掛接的是userinfo。

圖5 語法規則生成語法樹

優化器

優化器的作用是對SQL進行優化，生成最有的執行方案。如圖6所示，前面提到的SQL解析器通過語法分析和語法規則生成瞭SQL語法樹。這個語法樹作為優化器的輸入，而優化器（黃色的部分）包含瞭邏輯變換和代價優化兩部分的內容。在優化完成以後會生成SQL執行計劃作為整個優化過程的輸出，交給執行器在存儲引擎上執行。

圖6 優化器所處的位置

如上圖所示，這節的重點在優化器中的邏輯變換和代價優化上。

邏輯變換

邏輯變換也就是在關系代數基礎上進行變換，其目的是為瞭化簡，同時保證SQL變化前後的結果一致，也就是邏輯變化並不會帶來結果集的變化。

其主要包括以下幾個方面：

• 否定消除：針對表達式“和取”或“析取”前面出現“否定”的情況，應將關系條件進行拆分，從而將外層的“NOT”消除。
• 等值常量傳遞：利用瞭等值關系的傳遞特性，為瞭能夠盡早執行“下推”運算。“下推”的基本策略是，始終將過濾表達式盡可能移至靠近數據源的位置。
• 常量表達式計算：對於能立刻計算出結果的表達式，直接計算結果，同時將結果與其他條件盡量提前進行化簡。

這樣講概念或許有些抽象，通過圖7 來看看邏輯變化如何在SQL中執行的吧。

如圖7所示，從上往下共有4個步驟：

• 針對存在的SQL語句，首先通過“否定消除”，去掉條件判斷中的“NOT”。語句由原來的“or”轉換成“and”，並且大於小於符號進行變號。藍色部分為修改前的SQL，紅色是修改以後的SQL。
• 等值傳遞，這一步很好理解分別降”t2.a=9” 和”t2.b=5”分別替換掉SQL中對應的值。
• 接下來就是常量表達式計算，將“5+7”計算得到“12”。
• 最後是常量表達式計算後的化簡，將”9<=10”化簡為”true”帶入到最終的SQL表達式中完成優化。

圖7 邏輯變換

代價優化

代價優化是用來確定每個表，根據條件是否應用索引，應用哪個索引和確定多表連接的順序等問題。為瞭完成代價優化，需要找到一個代價最小的方案。

因此，優化器是通過基於代價的計算方法來決定如何執行查詢的（Cost-based Optimization）。

簡化的過程如下：

• 賦值操作代價：針對每個數據庫操作（創建表、返回數據集）設置對應的代價，這個代價值一般設置為1、0.2之類的值，沒有具體的含義就是對操作的代價定義。
• 計算操作數量：將SQL語句中涉及到的操作進行邏輯，並且做計算。說白瞭就是看這次SQL請求需要做哪些具體的數據庫操作。
• 求和操作代價：既然知道SQL由哪些數據庫操作組成，同時知道每個操作對應的代價，求和以後就是知道整體SQL執行的代價。
• 選擇代價計劃：如果說沒給SQL執行的操作都是一個計劃，那麼這些操作的不同組合就會對應不同的計劃，這裡需要選擇整體執行代價最低的操作計劃，作為這次執行SQL語句的代價計劃，從而達到總代價最低。

這裡將配置操作的代價分為MySQL 服務層和MySQL 引擎層，MySQL 服務層主要是定義CPU的代價，而MySQL 引擎層主要定義IO代價。 MySQL 5.7 引入瞭兩個系統表mysql.server_cost和mysql.engine_cost來分別配置這兩個層的代價。如下：

MySQL 服務層代價保存在表server_cost中，其具體內容如下：

•   row_evaluate_cost (default 0.2) 計算符合條件的行的代價，行數越多，此項代價越大
• memory_temptable_create_cost (default 2.0) 內存臨時表的創建代價
•  memory_temptable_row_cost (default 0.2) 內存臨時表的行代價
•  key_compare_cost (default 0.1) 鍵比較的代價，例如排序
• disk_temptable_create_cost (default 40.0) 內部myisam或innodb臨時表的創建代價
• disk_temptable_row_cost (default 1.0) 內部myisam或innodb臨時表的行代價

由上可以看出創建臨時表的代價是很高的，尤其是內部的myisam或innodb臨時表。

MySQL 引擎層代價保存在表engine_cost中，其具體內容如下：

•  io_block_read_cost (default 1.0) 從磁盤讀數據的代價，對innodb來說，表示從磁盤讀一個page的代價
•  memory_block_read_cost (default 1.0) 從內存讀數據的代價，對innodb來說，表示從buffer pool讀一個page的代價

目前io_block_read_cost和memory_block_read_cost默認值均為1，實際生產中建議酌情調大memory_block_read_cost，特別是對普通硬盤的場景。

MySQL會根據SQL查詢生成的查詢計劃中對應的操作從上面兩張代價表中查找對應的代價值，並且進行累加形成最終執行SQL計劃的代價。再將多種可能的執行計劃進行比較，選取最小代價的計劃執行。

執行器

當分析器生成查詢計劃，並且經過優化器以後，就到瞭執行器。執行器會選擇執行計劃開始執行，但在執行之前會校驗請求用戶是否擁有查詢的權限，如果沒有權限，就會返回錯誤信息，否則將會去調用MySQL引擎層的接口，執行對應的SQL語句並且返回結果。

例如SQL：

​“SELECT * FROM userinfo WHERE username = 'Tom';“

假設 “username“ 字段沒有設置索引，就會調用存儲引擎從第一條開始查，如果碰到瞭用戶名字是” Tom“， 就將結果集返回，沒有查找到就查看下一行，重復上一步的操作，直到讀完整個表或者找到對應的記錄。

需要註意SQL語句的執行順序並不是按照書寫順序來的，順序的定義會在分析器中做好，一般是按照如下順序：

圖8 SQL的執行順序

總結

本文從MySQL中SQL語句的執行過程作為切入點，首先介紹瞭查詢請求的執行流程，其中將MySQL的處理分為MySQL Server層和MySQL存儲引擎層。通過介紹SQL語句的流轉，引出瞭後面要介紹的5大組件，他們分別是：連接器、查詢緩存、分析器、優化器、執行器。後面的內容中對每個組件進行瞭詳細的介紹。連接器，負責身份認證和權限鑒別；查詢緩存，將查詢的結果集進行緩存，提高查詢效率；分析器，對SQL語句執行語法分析和語法規則，生成語法樹和執行計劃；優化器，包括邏輯變換和代價優化；執行器，在檢查用戶權限以後對數據進行逐條查詢，整個過程遵守SQL語句的執行順序。

到此這篇關於瞭解MySQL查詢語句執行過程(5大組件)的文章就介紹到這瞭,更多相關MySQL查詢語句執行內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：

• mysql優化之query_cache_limit參數說明
• 幾個MySQL高頻面試題的解答
• MySQL的Query Cache圖文詳解
• MySQL query_cache_type 參數與使用詳解
• SQL查詢語句執行的過程