MySQL如何執行查詢語句

1. 前言

以下對MySQL執行查詢語句的主要步驟進行分析，未涉及連接配接優化、排序優化等内容。

2. MySQL伺服器架構

在了解MySQL如何執行查詢語句前，首先需要了解MySQL伺服器（MySQL Server）架構。

MySQL伺服器架構示意圖如下所示：

圖檔來源： https://www.oracle.com/technetwork/articles/javase/figure2-large-145676.jpg 。

圖檔來源： 《High Performance MySQL, 3rd Edition》 。

MySQL子產品總覽示意圖如下所示：

圖檔來源： 《Understanding MySQL Internals》 。

以上示意圖中均未顯示查詢執行引擎（Query Execution Engine），在後續内容中有對其說明。

2.1. MySQL伺服器層與存儲引擎關系

參考 https://dev.mysql.com/doc/refman/5.6/en/storage-engines.html 。

存儲引擎（Storage engine）是用于處理不同表類型的SQL操作的MySQL元件。InnoDB是預設的也是最通用的存儲引擎。

MySQL伺服器使用可插拔的存儲引擎架構，使存儲引擎可以在運作的MySQL伺服器加載或解除安裝。

參考 https://dev.mysql.com/doc/refman/5.6/en/pluggable-storage-common-layer.html 。

MySQL可插拔存儲引擎是MySQL資料庫伺服器中的元件， 負責執行資料庫的實際資料I/O操作 ，以及啟用和實施針對特定應用程式需求的某些功能集。

參考 https://dev.mysql.com/doc/refman/5.6/en/mysqld.html 。

mysqld，也稱為MySQL伺服器，是在MySQL設施中完成大部分工作的主程式。MySQL伺服器對包含資料庫和表的MySQL資料目錄的通路進行管理。資料目錄也是儲存其他資訊的預設位置（例如日志檔案和狀态檔案）。

當MySQL伺服器啟動時，會監聽來自用戶端程式的網絡連接配接，并代表這些用戶端管理對資料庫的通路。

MySQL伺服器還具有一組系統變量，這些變量會在MySQL伺服器運作時影響其操作。MySQL伺服器還具有一組狀态變量，這些變量提供有關MySQL伺服器操作的資訊。

3. MySQL查詢語句執行過程

以下内容參考 《High Performance MySQL, 3rd Edition》。

向MySQL（伺服器）發送查詢語句時，MySQL按照以下步驟執行：

• （MySQL）用戶端将SQL語句發送到（MySQL）伺服器；
• 伺服器檢查查詢緩存。如果命中，從緩存中傳回存儲的結果；否則，将SQL語句傳遞到下一步；
• 伺服器對SQL進行解析、預處理，并優化SQL為查詢執行計劃；
• 查詢執行引擎通過調用存儲引擎API來執行計劃；
• 伺服器将結果發送給用戶端。

查詢語句的執行步驟如下圖所示：

可以看到查詢語句執行的步驟中，會涉及以下内容：

• MySQL用戶端/伺服器協定
• 查詢緩存
• 解析器與預處理器
• 優化器
• 查詢執行引擎
• 傳回結果給用戶端

3.1. MySQL用戶端/伺服器協定

MySQL用戶端/伺服器協定（The MySQL Client/Server Protocol）是半雙工的，這意味着在任何指定的時間，MySQL伺服器都可以發送或接收消息，但不能同時發送或接收消息。這也意味着沒有辦法縮短消息。

以上協定使MySQL通信變得簡單而快速，但在某些方面也存在限制。這意味着沒有流量控制；一旦一方發送了一條消息，另一方在進行響應之前必須擷取整個消息。

3.2. 查詢緩存

在解析查詢之前，如果已啟用查詢緩存（Query Cache），MySQL會在查詢緩存中檢查查詢。該操作是區分大小寫的哈希查找。如果該查詢與緩存中的類似查詢不同，即使隻相差一個位元組，也不會比對，查詢處理将進入下一階段。

參考 https://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/ 。

從MySQL 8.0開始，不再支援查詢緩存。

盡管MySQL Query Cache旨在提高性能，但它具有嚴重的可擴充性問題，并且很容易成為嚴重的瓶頸。

從MySQL 5.6起，查詢緩存已預設被禁用，因為衆所周知它無法在多核計算機上随着高吞吐量工作負載進行擴充。

假設可擴充性能夠被提高，那麼查詢緩存的限制因素在于，由于隻有命中緩存的查詢才會得到改善；它不太可能提高性能的可預測性。對于面向使用者的系統，降低性能差異通常比提高吞吐量峰值更重要。

當緩存靠近用戶端時，緩存可以帶來最大的好處。

參考 https://dev.mysql.com/doc/refman/5.6/en/query-cache-in-select.html ，在查詢語句中指定SQL_CACHE，當查詢緩存可用時，該查詢結果可以被緩存；在查詢語句中指定SQL_NO_CACHE，MySQL伺服器不會使用查詢緩存。

參考 https://dev.mysql.com/doc/refman/5.6/en/query-cache-configuration.html 、

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html 。

查詢緩存是否開啟由系統變量query_cache_size與query_cache_type決定。當query_cache_size為0，或query_cache_type為關閉時，查詢緩存會關閉。

query_cache_size預設值為1M，query_cache_type預設為關閉。查詢緩存預設關閉。

參考 https://mariadb.com/kb/en/server-system-variables 。

在MariaDB中，大于等于10.1.7版本的query_cache_size預設值為1M，query_cache_type預設為關閉；小于等于10.1.6版本的query_cache_size預設值為0，query_cache_type預設為啟用。即查詢緩存預設關閉。

3.3. 查詢優化過程

在查詢步驟的下一步，會将SQL查詢轉換為查詢執行引擎（query execution engine）處理的執行計劃（execution plan）。包含幾個子步驟：解析，預處理和優化。

3.3.1. 解析器與預處理器

首先，MySQL的解析器（parser ）将查詢分解，并建構“解析樹”（parse tree）。解析器使用MySQL的SQL文法來解釋和驗證查詢。

然後，預處理器（preprocessor ）會在結果解析樹中檢查解析器無法解析的其他語義。

接下來，預處理器會檢查權限。這通常非常快，除非伺服器存在大量的權限。

3.3.2. 優化器

解析樹目前是有效的，可供優化器（optimizer ）将其轉換為查詢執行計劃。查詢通常可以通過多種不同的方式執行，并産生相同的結果。優化器的工作是找到最佳的選項。

MySQL使用基于成本的優化器，這意味着它會嘗試預測各種執行計劃的成本，并選擇成本最低的執行計劃。

MySQL查詢優化器是一個非常複雜的軟體，它使用許多優化将查詢轉換為執行計劃。

參考 https://dev.mysql.com/doc/internals/en/optimizer-definitions.html ，優化器的狹義定義是：優化器是一組例行程式，決定DMBS執行查詢時使用什麼執行路徑。

參考《Understanding MySQL Internals》，MySQL的優化器有以下幾個重要的任務：

• 确定哪些索引鍵可用于從表中檢索記錄，并為每個表選擇最佳索引鍵；
• 對于每一個表，确定全表掃描是否比通過索引鍵讀取更好。 如果有很多與鍵值比對的記錄，通過索引鍵讀取的優點會降低，并且全表掃描會變得更快；
• 當查詢中存在多個表時，确定對表進行連接配接時的順序；
• 改寫（rewrite）WHERE子句以消除無效代碼，減少不必要的計算，并在可能的情況下更改限制，以使得索引鍵可被使用；
• 從連接配接中删除未使用的表；
• 确定索引鍵是否可用于ORDER BY和GROUP BY；
• 嘗試用内連接配接替換外連接配接；
• 嘗試簡化子查詢，并确定可以将其結果緩存到什麼程度；
• 合并視圖（将視圖引用擴充為宏）。

3.3.2.1. 表與索引資料

包含查詢優化器的MySQL伺服器層，不存儲有關資料和索引的統計資訊。這是存儲引擎的工作，因為每個存儲引擎可能會保留不同種類的統計資訊（或以不同的方式進行儲存）。

由于MySQL伺服器不存儲統計資訊，是以MySQL查詢優化器必須從存儲引擎擷取查詢中的表的統計資訊。存儲引擎為優化器提供統計資訊，例如每個表或每個索引的頁數，表和索引的基數，行和索引鍵的長度，以及索引鍵分布資訊。優化器可以使用以上資訊來幫助确定最佳執行計劃。

3.3.2.2. 執行計劃

很多其他資料庫産品會生成位元組碼來執行查詢，MySQL與它們不同，MySQL不會生成位元組碼來執行查詢。相反，查詢執行計劃實際上是指令樹，查詢執行引擎會根據查詢執行計劃指令樹産生查詢結果。最終的計劃包含足夠的資訊來重建原始查詢。

3.4. 查詢執行引擎

解析和優化階段輸出了查詢執行計劃，MySQL的查詢執行引擎會使用查詢執行計劃來處理查詢。查詢執行計劃是一個資料結構，不是可執行的位元組碼。

與優化階段相比，執行階段通常并不那麼複雜：MySQL僅遵循查詢執行計劃中給出的指令。查詢執行計劃中的許多操作調用存儲引擎接口實作的方法，也稱為handler API。

在執行查詢時，MySQL伺服器隻需重複執行指令，直到沒有更多行用于檢查。

參考MySQL文檔中關于MySQL伺服器通路存儲引擎的示意圖，可知中間會通過handler API，圖檔位址為 https://dev.mysql.com/doc/refman/5.6/en/images/innodb_memcached2.jpg ，如下所示：

3.5. 傳回結果給用戶端

執行查詢的最後步驟是回複用戶端。即使查詢沒有傳回結果集，仍會使用有關查詢的資訊（例如受影響的行數），對用戶端連接配接進行回複。

如果查詢是可緩存的，MySQL在此階段還會将結果放入查詢緩存。

MySQL伺服器逐漸生成并發送結果。當MySQL處理完最後一個表并成功生成一行時，它就可以并且應當将該行發送給用戶端。

以上有兩個好處：使伺服器避免将行保留在記憶體中；這意味着用戶端将盡快開始擷取結果。

結果集的每一行均以MySQL用戶端/伺服器協定單獨的包發送，協定包在TCP協定層可進行緩沖和合并發送。

4. WHERE子句的執行方式

通常，MySQL可以通過以下三種方式處理WHERE子句，以下分别為從最佳到最差的處理方式：

• 将條件應用于索引查找操作，以（從查詢結果）消除不比對的行。以上發生在存儲引擎層；
• 使用覆寫索引（covering index）避免對行進行通路，并在從索引中擷取每個結果後濾除不比對的行。以上發生在伺服器層，但不需要從表中讀取行；
• 從表中檢索行，然後過濾不比對的行。這發生在伺服器層，需要伺服器從表中讀取行才能對其進行過濾。

5. MySQL語句執行過程分析方法

5.1. 擷取MySQL最近執行的語句

5.1.1. 使用SHOW PROFILES擷取執行的語句

參考 https://dev.mysql.com/doc/refman/5.6/en/show-profiles.html 、 https://dev.mysql.com/doc/refman/5.6/en/show-profile.html 。

SHOW PROFILES、SHOW PROFILE語句顯示分析（profiling）資訊，表明目前會話過程中執行語句的資源使用情況。

SHOW PROFILES顯示最近發送到伺服器的語句清單。清單的大小由會話變量profiling_history_size控制，其預設值為15。最大值為100。将其值設定為0會禁用分析。

所有的語句都會被分析，除了SHOW PROFILE與SHOW PROFILES，是以在分析結果清單中不會出現上述語句。格式錯誤的語句會出現在分析結果中

會話變量profiling可用于對分析進行控制，其預設值為0（OFF）， 将profiling變量值設定為1或ON可以開啟分析 ：

分析針對每個會話啟用。當會話結束時，其分析資訊會丢失。

SHOW PROFILES輸出示例如下：

Query_ID | Duration   | Query
---------+------------+------------------------------------------------------
       1 | 0.00026232 | select now()
       2 | 0.00249026 | select * from test_table
       4 | 3.32405767 | select * from test_table_log where other2=2 limit 1
           

5.2. 擷取MySQL最近執行語句的資源使用情況

5.2.1. 使用SHOW PROFILE擷取語句資源使用情況

參考 https://dev.mysql.com/doc/refman/5.6/en/show-profile.html 。

5.2.1.1. SHOW PROFILE作用與啟用

SHOW PROFILE顯示關于單個語句的詳細資訊。

啟用SHOW PROFILE時，需要将會話變量profiling設定為1或ON。

5.2.1.2. SHOW PROFILE文法

SHOW PROFILE的文法如下：

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]
           

type值是可選的，可用于指定需要顯示的額外類型的資訊，可選值包括ALL、BLOCK IO、CONTEXT SWITCHES、CPU、IPC、MEMORY、PAGE FAULTS、SOURCE、SWAPS。

如果沒有FOR QUERY n子句，則輸出最近執行的語句。如果包括FOR QUERY n子句，則顯示第n條語句的資訊。n的值對應于SHOW PROFILES顯示的Query_ID值。

預設情況下，SHOW PROFILE顯示Status與Duration列。Status列的值與SHOW PROCESSLIST展示的State值類似。

5.2.1.3. SHOW PROFILE輸出格式

SHOW PROFILE輸出中的Status字段值，可參考 https://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html 。

對于查詢語句執行SHOW PROFILE，輸出結果中的Status列常見值如下所示：

starting
checking permissions
Opening tables
After opening tables
System lock
Table lock
After opening tables
init
optimizing
statistics
preparing
executing
Sending data
end
query end
closing tables
freeing items
updating status
logging slow query
cleaning up
           

Sending data表示線程正在為查詢語句讀取并處理行，并将資料發送給用戶端。因為在此狀态下的操作通常需要執行大量的磁盤通路（讀取），是以該狀态通常是給定的查詢生命周期中運作時間最長的狀态。

其他狀态的含義與顯示的名稱接近，與MySQL執行語句時的執行步驟對應。

SHOW PROFILE輸出中的Duration列，代表語句在指定的狀态執行的時間，機關為秒。

SHOW PROFILE輸出中的剩餘列的含義可參考INFORMATION_SCHEMA.PROFILING表的說明。

5.2.2. 從資料庫表擷取語句資源使用情況

參考 https://dev.mysql.com/doc/refman/5.6/en/profiling-table.html 。

INFORMATION_SCHEMA.PROFILING表提供語句的分析資訊，其内容與SHOW PROFILE和SHOW PROFILES語句産生的資訊對應。

會話變量profiling需要設定為1，否則上述表是空的。

INFORMATION_SCHEMA.PROFILING表包含以下關注的列：

• QUERY_ID

語句的數字形式辨別符；

• SEQ

序列号，表明具有相同QUERY_ID值的行的顯示順序；

• STATE

同SHOW PROFILE輸出結果中的Status列；

• DURATION

同SHOW PROFILE輸出結果中的Duration列；

• SOURCE_FUNCTION, SOURCE_FILE, SOURCE_LINE

表明被分析的狀态執行的源代碼位置的資訊。