分析mysql中一條SQL查詢語句是如何執(zhí)行的

一、MySQL 邏輯架構(gòu)概覽

MySQL 最重要、最與眾不同的特性就是它的可插拔存儲引擎架構(gòu)（pluggable storage engine architecture），這種架構(gòu)的設(shè)計將查詢處理及其他系統(tǒng)任務(wù)和數(shù)據(jù)的存儲/提取分離開來。來看官網(wǎng)的解釋：

The MySQL pluggable storage engine architecture enables a database professional to select a specialized storage engine for a particular application need while being completely shielded from the need to manage any specific application coding requirements.

大致意思就是，MySQL 可插拔存儲引擎架構(gòu)使開發(fā)者能夠?yàn)樘囟☉?yīng)用程序需求選擇專門的存儲引擎，同時完全無需管理任何特定應(yīng)用程序編碼要求。也就是說，盡管不同存儲引擎具有不同的功能，但應(yīng)用程序不受這些差異的影響。

如果應(yīng)用程序更改帶來了需要更改底層存儲引擎的需求，或者需要添加一個或多個存儲引擎來支持新需求，則無需進(jìn)行重大的編碼或流程更改即可使工作正常進(jìn)行。 MySQL 服務(wù)器架構(gòu)通過提供適用于跨存儲引擎的一致且易于使用的 API，使應(yīng)用程序免受存儲引擎底層復(fù)雜性的影響。

MySQL 的邏輯架構(gòu)圖如下，參考《高性能 MySQL - 第 3 版》：

我們可以大致把 MySQL 的邏輯架構(gòu)分成 Server 層和存儲引擎層：

1）大多數(shù) MySQL 的核心服務(wù)功能都在 Server 層，包括連接，查詢解析、分析、優(yōu)化、緩存以及所有的內(nèi)置函數(shù)（例如，日期、時間、數(shù)學(xué)和加密函數(shù)），所有跨存儲引擎的功能都在這一層實(shí)現(xiàn)：存儲過程、觸發(fā)器、視圖等。

值得一提的是，Server 最上面的服務(wù)也就是連接器，擁有管理 MySQL 連接、權(quán)限驗(yàn)證的功能。顯然這并非 MySQL 所獨(dú)有，大多數(shù)基于網(wǎng)絡(luò)的客戶端/服務(wù)器的工具或者服務(wù)都有類似的架構(gòu)。

2）第二層就是存儲引擎（支持 InnoDB、MyISAM、Memory 等多個存儲引擎）。存儲引擎負(fù)責(zé) MySQL 中數(shù)據(jù)的存儲和提取，響應(yīng)上層服務(wù)器的請求。每個存儲引擎自然是有它的優(yōu)勢和劣勢，不同的存儲引擎之間無法相互通信，所以我們需要根據(jù)不同的場景來選擇合適的存儲引擎。

服務(wù)器通過 API 與存儲引擎進(jìn)行通信。這些接口屏蔽了不同存儲引擎之間的差異，使得這些差異對上層的查詢過程透明。存儲引擎 API 包含幾十個底層函數(shù)，用于執(zhí)行諸如 “開始一個事務(wù)” 或者 “根據(jù)主鍵提取一行記錄” 等操作。

需要注意的是，在 MySQL 5.1 及之前的版本，MyISAM 是默認(rèn)的存儲引擎，而在 MySQL 5.5.5 后，InnoDB 成為了默認(rèn)的存儲引擎。

二、連接器（Connector）

MySQL 5.7 的官方文檔中，是這樣描述連接器的：

MySQL Connectors provide connectivity to the MySQL server for client programs.

MySQL 連接器為客戶端程序提供到 MySQL 服務(wù)器的連接。 說得更細(xì)節(jié)一點(diǎn)的話，連接器其實(shí)會做兩個事情，一個是管理 MySQL 連接，一個是權(quán)限驗(yàn)證。我們依次來解釋下。

首先，要連接到 MySQL 服務(wù)器，我們通常需要提供 MySQL 用戶名和密碼，并且如果服務(wù)器運(yùn)行在我們登錄的機(jī)器以外的機(jī)器上，還需要指定一個主機(jī)名比如 host。 所以連接命令一般是這樣的：

shell> mysql -h host -u user -p

Enter password: ********

當(dāng)然了，如果在運(yùn)行 MySQL 的同一臺機(jī)器上登錄，就可以省略主機(jī)名，只需使用以下內(nèi)容：

shell> mysql -u user -p

上面這個命令各位應(yīng)該都很熟悉。

OK，通過上述命令完成經(jīng)典的 TCP 三次握手建立連接后，連接器就會根據(jù)你輸入的用戶名和密碼來認(rèn)證你的身份：

1）如果用戶名或密碼不對，你就會收到一個 "Access denied for user" 的錯誤，然后客戶端程序結(jié)束執(zhí)行。

2）如果用戶名密碼認(rèn)證通過，你會看到下面這一串內(nèi)容：

mysql> 就是在提示你 MySQL 已準(zhǔn)備好了，你可以開始輸入 SQL 語句了！

當(dāng)然，連接器做的事情不僅僅是比對一下用戶名和密碼，它還會驗(yàn)證該用戶是否具有執(zhí)行某個特定查詢的權(quán)限（例如，是否允許該用戶對 world 數(shù)據(jù)庫的 Country 表執(zhí)行 SELECT 語句）。之后，這個連接里面的所有權(quán)限判斷邏輯，都將依賴于此時讀到的權(quán)限。

這意味著，當(dāng)一個用戶成功建立連接后，即使你在另一個終端用管理員賬號對這個用戶的權(quán)限做了修改，對當(dāng)前已經(jīng)存在連接的權(quán)限不會造成任何影響。

也就是說，當(dāng)修改了用戶權(quán)限后，只有再新建的連接才會使用新的權(quán)限設(shè)置。

當(dāng)一個連接建立起來后，如果你沒有后續(xù)的動作，那么這個連接就處于空閑狀態(tài)（Sleep）。

事實(shí)上，對于一個 MySQL 連接來說（或者說一個線程），任何時刻都有一個狀態(tài)，該狀態(tài)表示了 MySQL 當(dāng)前正在做什么。有很多種方式能查看當(dāng)前的狀態(tài)，最簡單的是使用 SHOW FULL PROCESSLIST 命令（該命令返回結(jié)果中的 Command 列就表示當(dāng)前的狀態(tài)）。

在一個查詢的生命周期中，狀態(tài)會變化很多次。這里就不詳細(xì)列出來了，上圖中的 Sleep 狀態(tài)就是說當(dāng)前連接正在等待客戶端發(fā)送新的請求，Query 狀態(tài)表示當(dāng)前連接正在執(zhí)行查詢或者正在將結(jié)果發(fā)送給客戶端。

在 MyQL 的默認(rèn)設(shè)置中，如果一個連接處在 Sleep 狀態(tài) 8 小時（就是超過 8 小時沒有使用），服務(wù)器將斷開這條連接，后續(xù)在該連接上進(jìn)行的所有操作都將失敗。這個時間是由參數(shù) wait_timeout 控制的：

三、查詢緩存（Query Cache）

OK，連接建立完成后，我們就可以輸入 select 語句進(jìn)行查詢了。執(zhí)行邏輯就來到了第二步：查詢緩存。

官方文檔是這樣解釋 Query Cache 的：

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

就是說查詢緩存存儲了 SELECT 語句的文本以及響應(yīng)給客戶端的相應(yīng)結(jié)果。這樣，如果服務(wù)器稍后接收到相同的 SELECT 語句，服務(wù)器會先從查詢緩存中檢索結(jié)果，而不是再次解析和執(zhí)行該語句。查詢緩存在 session 之間共享，因此可以發(fā)送一個客戶端生成的結(jié)果集以響應(yīng)另一個客戶端發(fā)出的相同查詢。

如果當(dāng)前的查詢恰好命中了查詢緩存，那么在返回查詢結(jié)果之前 MySQL 會檢查一次用戶權(quán)限。這仍然是無須解析查詢SQL語句的，因?yàn)樵诓樵兙彺嬷幸呀?jīng)存放了當(dāng)前查詢需要訪問的表信息。

那么既然涉及到緩存，就必然繞不開緩存一致性問題了。值得慶幸的是，不需要我們進(jìn)行額外操作，查詢緩存并不會返回陳舊數(shù)據(jù)！

The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.

當(dāng)表被修改時，查詢緩存中的任何相關(guān)條目都會被 flushed，注意，這里的 flushed 翻譯為清空而不是刷新。

看起來好像還不錯？不用我們手動操作，失效緩存就能夠被自動清空。

然而，很不幸的是，正是由于這個特性，從 MySQL 5.7.20 開始，官方不再推薦使用查詢緩存，并在 MySQL 8.0 中直接刪除了查詢緩存！

The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.

其實(shí)不難理解，舉個例子，對于一個流量很大的論壇項(xiàng)目來說，查詢帖子表的需求每時每刻都存在，帖子也幾乎每時每刻都在增加，那只要這個表一更新，這個表上所有的查詢緩存都會被清空，這對于 MySQL 數(shù)據(jù)庫的壓力之大，可想而知了吧。費(fèi)個勁把查詢結(jié)果存起來，還沒來得及使用呢，就被一個更新全清空了。

對于 MySQL 8.0 之前的版本來說，你可以將參數(shù) query_cache_type 設(shè)置成 DEMAND，這樣所有的 SQL 語句都不會再使用查詢緩存。而對于你確定要使用查詢緩存的語句，可以用 SQL_CACHE 顯式指定，像下面這個語句一樣：

mysql> select SQL_CACHE * from t1 where id = 1;

四、解析器（Parser）

如果沒有命中或者沒有開啟查詢緩存，MySQL 服務(wù)器接下來要做的就是將一條 SQL 語句轉(zhuǎn)換成一個執(zhí)行計劃，再依照這個執(zhí)行計劃和存儲引擎進(jìn)行交互。這包括多個子階段：解析 SQL、預(yù)處理、優(yōu)化 SQL 執(zhí)行計劃。這個過程中任何錯誤（例如語法錯誤）都可能終止查詢。

其中解析 SQL 和預(yù)處理就是解析器做的事情，優(yōu)化 SQL 執(zhí)行計劃就是優(yōu)化器做的事情。這里我們先說解析器。

這里《高性能 MySQL - 第 3 版》書中分得更細(xì)致點(diǎn)，解析器用來解析 SQL，預(yù)處理器則用來預(yù)處理，我暫且把它們都?xì)w為解析器吧

所謂解析 SQL 就是說，MySQL 通過關(guān)鍵字對 SQL 語句進(jìn)行解析，并生成一棵對應(yīng)的 “解析樹”，用于根據(jù)語法規(guī)則來驗(yàn)證語句是否正確。例如，它將驗(yàn)證是否使用錯誤的關(guān)鍵字，或者使用關(guān)鍵字的順序是否正確等，再或者它還會驗(yàn)證引號是否能前后正確匹配。

而預(yù)處理則會進(jìn)一步檢查解析樹是否合法，例如，檢查數(shù)據(jù)表和數(shù)據(jù)列是否存在，檢查表名和字段名是否正確等。

五、優(yōu)化器（Optimizer）

現(xiàn)在，解析樹是合法的了，MySQL 已經(jīng)知道你要做什么了。不過，一條查詢可以有很多種執(zhí)行計劃，最后都返回相同的結(jié)果，那到底該選擇哪種執(zhí)行計劃呢？

舉個簡單的例子：

mysql> select * from t1 where id = 10 and name = "good";

對于上面這個語句，可以先查找 name = good 再查找 id = 10，也可以先查找 id = 10 再查找 name = good，這兩種不同的執(zhí)行計劃可能耗費(fèi)的時間成本是不一樣的。

那么優(yōu)化器的作用就是找到這其中最好的執(zhí)行計劃。需要注意的是，這里的執(zhí)行計劃是一個數(shù)據(jù)結(jié)構(gòu)，而不是和很多其他的關(guān)系型數(shù)據(jù)庫那樣會生成對應(yīng)的字節(jié)碼。

另外，優(yōu)化器并不關(guān)心表使用的是什么存儲引擎，但存儲引擎對于優(yōu)化查詢是有影響的。優(yōu)化器會請求存儲引擎提供容量或某個具體操作的開銷信息，以及表數(shù)據(jù)的統(tǒng)計信息等。

當(dāng)優(yōu)化器階段完成后，這個語句的執(zhí)行計劃就確定下來了，就可以進(jìn)入執(zhí)行器階段了。

六、執(zhí)行器

和命中查詢緩存一樣，在開始執(zhí)行 SQL 語句之前，執(zhí)行器會先判斷一下當(dāng)前用戶對這個表有沒有執(zhí)行查詢的權(quán)限，如果沒有，就會返回沒有權(quán)限的錯誤。

權(quán)限認(rèn)證完成后，MySQL 就會根據(jù)執(zhí)行計劃給出的指令逐步執(zhí)行。在根據(jù)執(zhí)行計劃逐步執(zhí)行的過程中，有大量的操作需要通過調(diào)用存儲引擎實(shí)現(xiàn)的接口來完成，這些接口也就是我們稱為 “handler API” 的接口。

查詢中的每一個表由一個 handler 的實(shí)例表示。實(shí)際上，MySQL 在優(yōu)化階段就為每個表創(chuàng)建了一個 handler 實(shí)例，優(yōu)化器根據(jù)這些實(shí)例的接口可以獲取表的相關(guān)信息，包括表的所有列名、索引統(tǒng)計信息，等等。

舉個例子：

mysql> select * from t1 where id = 10;

假設(shè)我們使用默認(rèn)的 InnoDB 引擎，則執(zhí)行器的執(zhí)行流程大概是這樣的（注意，如果 id 不是索引則會進(jìn)行全表掃描，一行一行的查找，如果是索引則會在索引組織表中查詢，比較負(fù)責(zé)。這里以非索引舉例）：

1）調(diào)用 InnoDB 引擎接口獲取這個表的第一行記錄，判斷 id 值是不是 10，如果是則將這行記錄存在一個集合中；如果不是則進(jìn)入下一行的判斷，直到取到這個表的最后一行

2）執(zhí)行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結(jié)果返回給客戶端

七、小結(jié)

總結(jié)下一條查詢語句的執(zhí)行過程：

1.MySQL 客戶端與服務(wù)器間建立連接，客戶端發(fā)送一條查詢給服務(wù)器；

2.服務(wù)器先檢查查詢緩存，如果命中了緩存，則立刻返回存儲在緩存中的結(jié)果；否則進(jìn)入下一階段；

3.服務(wù)器端進(jìn)行 SQL 解析、預(yù)處理，生成合法的解析樹；

4.再由優(yōu)化器生成對應(yīng)的執(zhí)行計劃；

5.MySQL 根據(jù)優(yōu)化器生成的執(zhí)行計劃，調(diào)用相應(yīng)的存儲引擎的 API 來執(zhí)行，并將執(zhí)行結(jié)果返回給客戶端。

以上就是分析mysql中一條SQL查詢語句是如何執(zhí)行的的詳細(xì)內(nèi)容，更多關(guān)于mysql查詢語句是如何執(zhí)行的的資料請關(guān)注腳本之家其它相關(guān)文章！