MySQL基礎架構（三）一條查詢語句的一生

目錄

1 執(zhí)行流程概述

2 連接器

3 分析器

4 優(yōu)化器

5 執(zhí)行器

6 存儲引擎

1 執(zhí)行流程概述

前面我們提到過MySQL的結構可以大致分為MySQL server層和存儲引擎層。一條SQL查詢語句會的請求當然也會經(jīng)過server層和存儲引擎層。

MySQL數(shù)據(jù)庫執(zhí)行一條查詢語句的過程涉及多個階段。以下是一條查詢語句在MySQL中的大致執(zhí)行過程：

• 連接器:
• 當客戶端嘗試與MySQL服務器建立連接時，服務器首先驗證連接請求是否來自允許的主機，并檢查提供的用戶名和密碼是否有效。
• 連接成功建立后由連接器管理這些連接。
• 分析器:
• MySQL使用SQL解析器進行詞法分析，將查詢語句分解為多個令牌或詞素。
• 語法分析器然后根據(jù)這些詞素生成一個所稱的“解析樹”。
• 優(yōu)化器:
• 這是查詢處理中最重要的部分。查詢優(yōu)化器的任務是找到執(zhí)行查詢的最有效方式，尤其是對于涉及復雜連接、子查詢和其他高級功能的查詢。
• 優(yōu)化器會考慮各種因素，如可用的索引、表的統(tǒng)計信息（例如行數(shù)、數(shù)據(jù)分布等）和查詢的結構。
• 它可能會重寫查詢或更改查詢的執(zhí)行順序來找到最佳的執(zhí)行路徑。
• 執(zhí)行器:
• 根據(jù)查詢優(yōu)化器確定的計劃，MySQL現(xiàn)在開始實際執(zhí)行查詢。
• 執(zhí)行器調(diào)用存儲引擎提供的接口讀寫數(shù)據(jù)。

2 連接器

客戶端工具通過連接器與服務端建立連接。常用客戶端如下：

//JDBC 創(chuàng)建連接
Connection conn = DriverManager.getConnection(url, user, password);


#MySQL Client 創(chuàng)建連接
mysql -h$ip -P$port -u$user -p$pwd
mysql -u$user -p$pwd -S $socket

如果密碼不對，會收到錯誤如下所示：

ERROR 1045 (28000): Access denied for user 'xxx'@'xxx' (using password: YES)

連接成功后，可以在MySQL服務端看到當前連接：

mysql> show processlist;
+----+------+-----------------+------+---------+---------+----------+------------------+
| Id | User | Host ? ? ? ? ? ?| db ? | Command | Time ? ?| State ? ?| Info ? ? ? ? ? ? |
+----+------+-----------------+------+---------+---------+----------+------------------+
| ?6 | root | localhost:52466 | test | Sleep ? | 1196053 | ? ? ? ? ?| NULL ? ? ? ? ? ? |
| ?7 | root | localhost:41184 | NULL | Sleep ? | ? ? ?25 | ? ? ? ? ?| NULL ? ? ? ? ? ? |
| ?8 | root | localhost:52564 | NULL | Query ? | ? ? ? 0 | starting | show processlist |
+----+------+-----------------+------+---------+---------+----------+------------------+

這里需要注意的是，連接如果在長時間不使用后會自動斷開，這時候再發(fā)送sql請求就會報錯。所以在使用長會話生產(chǎn)環(huán)境，我們通常建議設置“連接?；顧C制”，如果一個連接暫時不用，那么也每5分鐘發(fā)送一個簡單sql如select 1;保持連接的活動狀態(tài)。當然也可以每次不用的時候手工關閉connection，每次使用的時候再新建connection，但是這樣會對性能由損耗。

3 分析器

對于一條SQL，MySQL 需要判斷SQL語法是否正確，以及SQL要做什么，因此需要對 SQL 語句做解析。分析器會對SQL做如下分析：

1. 詞法分析，生成語法分析需要的token。
2. 語法分析，生成語法樹。

如果語法分析失敗，會報錯如下：

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xxx' at line 1

如果sql出現(xiàn)語法錯誤，重點關注'xxx'內(nèi)容。

4 優(yōu)化器

數(shù)據(jù)庫查詢優(yōu)化器的主要目的是為給定的查詢找到最有效的執(zhí)行計劃。

比如下面的SQL就有不止一種執(zhí)行方式:

select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;

• 執(zhí)行方式1：先從表 t1 里面取出 c=10 的記錄的 ID 值，再根據(jù) ID 值關聯(lián)到表 t2，再判斷 t2 里面 d 的值是否等于 20。
• 執(zhí)行方式2：先從表 t2 里面取出 d=20 的記錄的 ID 值，再根據(jù) ID 值關聯(lián)到 t1，再判斷 t1 里面 c 的值是否等于 10。

兩種執(zhí)行方式耗時差別可能非常大，以需要優(yōu)化器來幫助我們選擇合適的執(zhí)行方式。

大體上，優(yōu)化器可以分為兩大類：基于規(guī)則的優(yōu)化器（Rule-Based Optimizer, RBO）和基于成本的優(yōu)化器（Cost-Based Optimizer, CBO）。

• 基于規(guī)則的優(yōu)化器 (RBO)：它根據(jù)一組預定義的規(guī)則來選擇執(zhí)行計劃。這些規(guī)則是基于通用的數(shù)據(jù)庫設計和查詢原則。
• 基于成本的優(yōu)化器 (CBO)：CBO使用統(tǒng)計信息（例如表的大小、數(shù)據(jù)分布、索引的選擇性等）來估計執(zhí)行不同查詢計劃的“成本”。CBO的目標是找到具有最低成本的執(zhí)行計劃。

MySQL使用的優(yōu)化器是CBO，在生成執(zhí)行計劃時各種操作的成本，取成本最低的操作作為最終的執(zhí)行計劃。

MySQL關于成本的計算是一個單獨的話題，后面有機會再講解。

為了生成成本最低的執(zhí)行計劃，優(yōu)化器需要做到如下兩點：

• 若查詢只涉及到單表，計算走不同索引和全表掃描的代價，找出成本最低的方案。
• 若查詢是多表聯(lián)查，選擇最優(yōu)的表連接順序，同時為驅動表和被驅動表選擇成本最低的訪問方法。

使用explain查看優(yōu)化器生成的執(zhí)行計劃：

對于執(zhí)行計劃的詳細介紹以及優(yōu)化在后面性能優(yōu)化課程中會講解。

5 執(zhí)行器

MySQL根據(jù)執(zhí)行計劃給出的指令逐步執(zhí)行。在根據(jù)執(zhí)行計劃逐步執(zhí)行的過程中，有大量的操作需要通過調(diào)用存儲引擎實現(xiàn)的接口來完成。

一個執(zhí)行器執(zhí)行全表掃描流程示例：

1. 調(diào)用存儲引擎接口取這個表的第一行，判斷是否滿足條件，如果不是則跳過，如果是則將這行存在結果集中。
2. 調(diào)用引擎接口取“下一行”，重復相同的判斷邏輯，直到取到這個表的最后一行。
3. 執(zhí)行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結果集返回給客戶端。

對于有索引的表，調(diào)用接口會稍有變化，不過大致類似。

6 存儲引擎

當執(zhí)行器與InnoDB存儲引擎交互時，InnoDB有其特定的內(nèi)部機制來處理請求，如下：

1. 請求解釋: 當InnoDB收到來自執(zhí)行器的請求時，首先會解釋該請求的性質(zhì)。這可能是一個全表掃描、索引查找或基于主鍵的查找。
2. Buffer Pool查找: 在檢索數(shù)據(jù)之前，InnoDB首先在其Buffer Pool中查找該數(shù)據(jù)。Buffer Pool是InnoDB的中心緩存，用于存儲表數(shù)據(jù)和索引數(shù)據(jù)。它幫助減少對物理磁盤的訪問，從而加速查詢性能。

• 如果所需的數(shù)據(jù)頁已在Buffer Pool中，則直接從那里讀取數(shù)據(jù)。
• 如果數(shù)據(jù)頁不在Buffer Pool中，則需要從磁盤中讀取。

1. 磁盤讀取: 如果必要，InnoDB會從磁盤中讀取所需的數(shù)據(jù)頁。然后，這些數(shù)據(jù)頁通常會被加載到Buffer Pool中，以便將來的請求可以更快地訪問它們。
2. 數(shù)據(jù)返回: 一旦得到了所需的數(shù)據(jù)，InnoDB將其返回給執(zhí)行器。

最后我們來看一下InnoDB的架構：