學習 SQL 的時候，大家肯定第一個先學到的就是 select 查詢語句了，比如下面這句查詢語句：

// 在 product 表中，查詢 id = 1 的記錄
select * from product where id = 1;

但是有沒有想過，執行一條 select 查詢語句，在 MySQL 中期間發生了什麼？

帶著這個問題，我們可以很好的瞭解 MySQL 內部的架構。

所以，這次小林就帶大家拆解一下 MySQL 內部的結構，看看內部裡的每一個“零件”具體是負責做什麼的。

MySQL 執行流程是怎樣的？

先來一個上帝視角圖，下面就是 MySQL 執行一條 SQL 查詢語句的流程，也從圖中可以看到 MySQL 內部架構裡的各個功能模組。

可以看到， MySQL 的架構共分為兩層：Server 層和儲存引擎層，

Server 層負責建立連線、分析和執行 SQL。MySQL 大多數的核心功能模組都在這實現，主要包括聯結器，查詢快取、解析器、最佳化器、執行器等。另外，所有的內建函式（如日期、時間、數學和加密函式等）和所有跨儲存引擎的功能（如儲存過程、觸發器、檢視等。）都在 Server 層實現。
儲存引擎層負責資料的儲存和提取。支援 InnoDB、MyISAM、Memory 等多個儲存引擎，不同的儲存引擎共用一個 Server 層。現在最常用的儲存引擎是 InnoDB，從 MySQL 5.5 版本開始， InnoDB 成為了 MySQL 的預設儲存引擎。我們常說的索引資料結構，就是由儲存引擎層實現的，不同的儲存引擎支援的索引型別也不相同，比如 InnoDB 支援索引型別是 B+樹，且是預設使用，也就是說在資料表中建立的主鍵索引和二級索引預設使用的是 B+ 樹索引。

好了，現在我們對 Server 層和儲存引擎層有了一個簡單認識，接下來，就詳細說一條 SQL 查詢語句的執行流程，依次看看每一個功能模組的作用。

第一步：聯結器

如果你在 Linux 作業系統裡要使用 MySQL，那你第一步肯定是要先連線 MySQL 服務，然後才能執行 SQL 語句，普遍我們都是使用下面這條命令進行連線：

# -h 指定 MySQL 服務得 IP 地址，如果是連線本地的 MySQL服務，可以不用這個引數；
# -u 指定使用者名稱，管理員角色名為 root；
# -p 指定密碼，如果命令列中不填寫密碼（為了密碼安全，建議不要在命令列寫密碼），就需要在互動對話裡面輸入密碼
mysql -h$ip -u$user -p

連線的過程需要先經過 TCP 三次握手，因為 MySQL 是基於 TCP 協議進行傳輸的，如果 MySQL 服務並沒有啟動，則會收到如下的報錯：

如果 MySQL 服務正常執行，完成 TCP 連線的建立後，聯結器就要開始驗證你的使用者名稱和密碼，如果使用者名稱或密碼不對，就收到一個"Access denied for user"的錯誤，然後客戶端程式結束執行。

如果使用者密碼都沒有問題，聯結器就會獲取該使用者的許可權，然後儲存起來，後續該使用者在此連線裡的任何操作，都會基於連線開始時讀到的許可權進行許可權邏輯的判斷。

所以，如果一個使用者已經建立了連線，即使管理員中途修改了該使用者的許可權，也不會影響已經存在連線的許可權。修改完成後，只有再新建的連線才會使用新的許可權設定。

如何檢視 MySQL 服務被多少個客戶端連線了？

如果你想知道當前 MySQL 服務被多少個客戶端連線了，你可以執行 show processlist 命令進行檢視。

比如上圖的顯示結果，共有兩個使用者名稱為 root 的使用者連線了 MySQL 服務，其中 id 為 6 的使用者的 Command 列的狀態為 Sleep ，這意味著該使用者連線完 MySQL 服務就沒有再執行過任何命令，也就是說這是一個空閒的連線，並且空閒的時長是 736 秒（ Time 列）。

空閒連線會一直佔用著嗎？

當然不是了，MySQL 定義了空閒連線的最大空閒時長，由 wait_timeout 引數控制的，預設值是 8 小時（28880秒），如果空閒連線超過了這個時間，聯結器就會自動將它斷開。

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

當然，我們自己也可以手動斷開空閒的連線，使用的是 kill connection + id 的命令。

mysql> kill connection +6;
Query OK, 0 rows affected (0.00 sec)

一個處於空閒狀態的連線被服務端主動斷開後，這個客戶端並不會馬上知道，等到客戶端在發起下一個請求的時候，才會收到這樣的報錯“ERROR 2013 (HY000): Lost connection to MySQL server during query”。

MySQL 的連線數有限制嗎？

MySQL 服務支援的最大連線數由 max_connections 引數控制，比如我的 MySQL 服務預設是 151 個,超過這個值，系統就會拒絕接下來的連線請求，並報錯提示“Too many connections”。

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

MySQL 的連線也跟 HTTP 一樣，有短連線和長連線的概念，它們的區別如下：

// 短連線
連線 mysql 服務（TCP 三次握手）
執行sql
斷開 mysql 服務（TCP 四次揮手）

// 長連線
連線 mysql 服務（TCP 三次握手）
執行sql
執行sql
執行sql
....
斷開 mysql 服務（TCP 四次揮手）

可以看到，使用長連線的好處就是可以減少建立連線和斷開連線的過程，所以一般是推薦使用長連線。

但是，使用長連線後可能會佔用記憶體增多，因為 MySQL 在執行查詢過程中臨時使用記憶體管理連線物件，這些連線物件資源只有在連線斷開時才會釋放。如果長連線累計很多，將導致 MySQL 服務佔用記憶體太大，有可能會被系統強制殺掉，這樣會發生 MySQL 服務異常重啟的現象。

怎麼解決長連線佔用記憶體的問題？

有兩種解決方式。

第一種，定期斷開長連線。既然斷開連線後就會釋放連線佔用的記憶體資源，那麼我們可以定期斷開長連線。

第二種，客戶端主動重置連線。MySQL 5.7 版本實現了 mysql_reset_connection() 函式的介面，注意這是介面函式不是命令，那麼當客戶端執行了一個很大的操作後，在程式碼裡呼叫 mysql_reset_connection 函式來重置連線，達到釋放記憶體的效果。這個過程不需要重連和重新做許可權驗證，但是會將連線恢復到剛剛建立完時的狀態。

至此，聯結器的工作做完了，簡單總結一下：

與客戶端進行 TCP 三次握手建立連線；
校驗客戶端的使用者名稱和密碼，如果使用者名稱或密碼不對，則會報錯；
如果使用者名稱和密碼都對了，會讀取該使用者的許可權，然後後面的許可權邏輯判斷都基於此時讀取到的許可權；

第二步：查詢快取

聯結器得工作完成後，客戶端就可以向 MySQL 服務傳送 SQL 語句了，MySQL 服務收到 SQL 語句後，就會解析出 SQL 語句的第一個欄位，看看是什麼型別的語句。

如果 SQL 是查詢語句（select 語句），MySQL 就會先去查詢快取（ Query Cache ）裡查詢快取資料，看看之前有沒有執行過這一條命令，這個查詢快取是以 key-value 形式儲存在記憶體中的，key 為 SQL 查詢語句，value 為 SQL 語句查詢的結果。

如果查詢的語句命中查詢快取，那麼就會直接返回 value 給客戶端。如果查詢的語句沒有命中查詢快取中，那麼就要往下繼續執行，等執行完後，查詢的結果就會被存入查詢快取中。

這麼看，查詢快取還挺有用，但是其實查詢快取挺雞肋的。

對於更新比較頻繁的表，查詢快取的命中率很低的，因為只要一個表有更新操作，那麼這個表的查詢快取就會被清空。如果剛快取了一個查詢結果很大的資料，還沒被使用的時候，剛好這個表有更新操作，查詢緩衝就被清空了，相當於快取了個寂寞。

所以，MySQL 8.0 版本直接將查詢快取刪掉了，也就是說 MySQL 8.0 開始，執行一條 SQL 查詢語句，不會再走到查詢快取這個階段了。

對於 MySQL 8.0 之前的版本，如果想關閉查詢快取，我們可以透過將引數 query_cache_type 設定成 DEMAND。

第三步：解析器

在正式執行 SQL 查詢語句之前， MySQL 會先對 SQL 語句做解析，這個工作交由由解析器來完成，解析器會做如下兩件事情。

第一件事情，詞法分析。MySQL 會根據你輸入的字串識別出關鍵字出來，構建出 SQL 語法樹，這樣方面後面模組獲取 SQL 型別、表名、欄位名、 where 條件等等。

第二件事情，語法分析。根據詞法分析的結果，語法解析器會根據語法規則，判斷你輸入的這個 SQL 語句是否滿足 MySQL 語法。

如果我們輸入的 SQL 語句語法不對，或者資料表或者欄位不存在，都會在解析器這個階段報錯。

比如，我下面這條查詢語句，把 from 寫成了 form，這時 MySQL 解析器就會給報錯。

比如，我下面這條查詢語句，test 這張表是不存在的，這時 MySQL 解析器就會給報錯。

mysql> select * from test;
ERROR 1146 (42S02): Table 'mysql.test' doesn't exist

第四步：最佳化器

經過解析器後，接著就要執行 SQL 查詢語句了，但是在真正執行之前，會檢查使用者是否有訪問該資料庫表的許可權，如果沒有就直接報錯了。

如果有許可權，就進入 SQL 查詢語句的執行階段，而 SQL 查詢語句真正執行之前需要先制定一個執行計劃，這個工作交由「最佳化器」來完成的。

最佳化器主要負責將 SQL 查詢語句的執行方案確定下來，比如在表裡面有多個索引的時候，最佳化器會基於查詢成本的考慮，來決定選擇使用哪個索引。

當然，我們本次的查詢語句（select * from product where id = 1）很簡單，就是選擇使用主鍵索引。

要想知道最佳化器選擇了哪個索引，我們可以在查詢語句最前面加個 explain 命令，這樣就會輸出這條 SQL 語句的執行計劃，然後執行計劃中的 key 就表示執行過程中使用了哪個索引，比如下圖的 key 為 PRIMARY 就是使用了主鍵索引。

如果查詢語句的執行計劃裡的 key 為 null 說明沒有使用索引，那就會全表掃描（type = ALL），這種查詢掃描的方式是效率最低檔次的，如下圖：

這張 product 表只有一個索引就是主鍵，現在我在表中將 name 設定為普通索引（二級索引）。

這時 product 表就有主鍵索引（id）和普通索引（name）。假設執行了這條查詢語句：

select id from product where id > 1  and name like 'i%';

這條查詢語句的結果既可以使用主鍵索引，也可以使用普通索引，但是執行的效率會不同。這時，就需要最佳化器來決定使用哪個索引了。

很顯然這條查詢語句是覆蓋索引，直接在二級索引就能查詢到結果（因為二級索引的 B+ 樹的葉子節點的資料儲存的是主鍵值），就沒必要在主鍵索引查詢了，因為查詢主鍵索引的 B+ 樹的成本會比查詢二級索引的 B+ 的成本大，最佳化器基於查詢成本的考慮，會選擇查詢代價小的普通索引。

在下圖中執行計劃，我們可以看到，執行過程中使用了普通索引（name），Exta 為 Using index，這就是表明使用了覆蓋索引最佳化。

第五步：執行器

經歷完最佳化器後，就確定了執行方案，接下來 MySQL 就真正開始執行語句了，這個工作是由「執行器」完成的。在執行的過程中，執行器就會和儲存引擎互動了，互動是以記錄為單位的。

接下來，用三種方式執行過程，跟大家說一下執行器和儲存引擎的互動過程（PS ：為了寫好這一部分，特地去看 MySQL 原始碼，也是第一次看哈哈）。

主鍵索引查詢
全表掃描
索引下推

主鍵索引查詢

以本文開頭查詢語句為例，看看執行器是怎麼工作的。

select * from product where id = 1;

這條查詢語句的查詢條件用到了主鍵索引，而且是等值查詢，同時主鍵 id 是唯一，不會有 id 相同的記錄，所以最佳化器決定選用訪問型別為 const 進行查詢，也就是使用主鍵索引查詢一條記錄，那麼執行器與儲存引擎的執行流程是這樣的：

執行器第一次查詢，會呼叫 read_first_record 函式指標指向的函式，因為最佳化器選擇的訪問型別為 const，這個函式指標被指向為 InnoDB 引擎索引查詢的介面，把條件 id = 1 交給儲存引擎，讓儲存引擎定位符合條件的第一條記錄。
儲存引擎透過主鍵索引的 B+ 樹結構定位到 id = 1的第一條記錄，如果記錄是不存在的，就會向執行器上報記錄找不到的錯誤，然後查詢結束。如果記錄是存在的，就會將記錄返回給執行器；
執行器從儲存引擎讀到記錄後，接著判斷記錄是否符合查詢條件，如果符合則傳送給客戶端，如果不符合則跳過該記錄。
執行器查詢的過程是一個 while 迴圈，所以還會再查一次，但是這次因為不是第一次查詢了，所以會呼叫 read_record 函式指標指向的函式，因為最佳化器選擇的訪問型別為 const，這個函式指標被指向為一個永遠返回 - 1 的函式，所以當呼叫該函式的時候，執行器就退出迴圈，也就是結束查詢了。

至此，這個語句就執行完成了。

全表掃描

舉個全表掃描的例子：

select * from product where name = 'iphone';

這條查詢語句的查詢條件沒有用到索引，所以最佳化器決定選用訪問型別為 ALL 進行查詢，也就是全表掃描的方式查詢，那麼這時執行器與儲存引擎的執行流程是這樣的：

執行器第一次查詢，會呼叫 read_first_record 函式指標指向的函式，因為最佳化器選擇的訪問型別為 all，這個函式指標被指向為 InnoDB 引擎全掃描的介面，讓儲存引擎讀取表中的第一條記錄；
執行器會判斷讀到的這條記錄的 name 是不是 iphone，如果不是則跳過；如果是則將記錄發給客戶的（是的沒錯，Server 層每從儲存引擎讀到一條記錄就會傳送給客戶端，之所以客戶端顯示的時候是直接顯示所有記錄的，是因為客戶端是等查詢語句查詢完成後，才會顯示出所有的記錄）。
執行器查詢的過程是一個 while 迴圈，所以還會再查一次，會呼叫 read_record 函式指標指向的函式，因為最佳化器選擇的訪問型別為 all，read_record 函式指標指向的還是 InnoDB 引擎全掃描的介面，所以接著向儲存引擎層要求繼續讀剛才那條記錄的下一條記錄，儲存引擎把下一條記錄取出後就將其返回給執行器（Server層），執行器繼續判斷條件，不符合查詢條件即跳過該記錄，否則傳送到客戶端；
一直重複上述過程，直到儲存引擎把表中的所有記錄讀完，然後向執行器（Server層）返回了讀取完畢的資訊；
執行器收到儲存引擎報告的查詢完畢的資訊，退出迴圈，停止查詢。

至此，這個語句就執行完成了。

索引下推

在這部分非常適合講索引下推（MySQL 5.7 推出的查詢最佳化策略），這樣大家能清楚的知道，「下推」這個動作，下推到了哪裡。

索引下推能夠減少二級索引在查詢時的回表操作，提高查詢的效率，因為它將 Server 層部分負責的事情，交給儲存引擎層去處理了。

舉一個具體的例子，方便大家理解，這裡一張使用者表如下，我對 age 和 reword 欄位建立了聯合索引（age，reword）：

現在有下面這條查詢語句：

select * from t_user  where age > 20 and reward = 100000;

聯合索引當遇到範圍查詢 (>、<、between、like) 就會停止匹配，也就是 a 欄位能用到聯合索引，但是 reward 欄位則無法利用到索引。具體原因這裡可以看這篇：索引常見面試題

那麼，不使用索引下推（MySQL 5.7 之前的版本）時，執行器與儲存引擎的執行流程是這樣的：

Server 層首先呼叫儲存引擎的介面定位到滿足查詢條件的第一條二級索引記錄，也就是定位到 age > 20 的第一條記錄；
儲存引起根據二級索引的 B+ 樹快速定位到這條記錄後，獲取主鍵值，然後進行回表操作，將完整的記錄返回給 Server 層；
Server 層在判斷該記錄的 reward 是否等於 100000，如果成立則將其傳送給客戶端；否則跳過該記錄；
接著，繼續向儲存引擎索要下一條記錄，儲存引擎在二級索引定位到記錄後，獲取主鍵值，然後回表操作，將完整的記錄返回給 Server 層；
如此往復，直到儲存引擎把表中的所有記錄讀完。

可以看到，沒有索引下推的時候，每查詢到一條二級索引記錄，都要進行回表操作，然後將記錄返回給 Server，接著 Server 再判斷該記錄的 reward 是否等於 100000。

而使用索引下推後，判斷記錄的 reward 是否等於 100000 的工作交給了儲存引擎層，過程如下：

Server 層首先呼叫儲存引擎的介面定位到滿足查詢條件的第一條二級索引記錄，也就是定位到 age > 20 的第一條記錄；
儲存引擎定位到二級索引後，先不執行回表操作，而是先判斷一下該索引中包含的列（reward列）的條件（reward 是否等於 100000）是否成立。如果條件不成立，則直接跳過該二級索引。如果成立，則執行回表操作，將完成記錄返回給 Server 層。
Server 層在判斷其他的查詢條件（本次查詢沒有其他條件）是否成立，如果成立則將其傳送給客戶端；否則跳過該記錄，然後向儲存引擎索要下一條記錄。
如此往復，直到儲存引擎把表中的所有記錄讀完。

可以看到，使用了索引下推後，雖然 reward 列無法使用到聯合索引，但是因為它包含在聯合索引（age，reward）裡，所以直接在儲存引擎過濾出滿足 reward = 100000 的記錄後，才去執行回表操作獲取整個記錄。相比於沒有使用索引下推，節省了很多回表操作。

當你發現執行計劃裡的 Extr 部分顯示了 “Using index condition”，說明使用了索引下推。

參考資料：

《MySQL 45 講》
《MySQL是怎樣執行的：從根兒上理解MySQL》
https://gohalo.me/post/mysql-executor.html
http://www.iskm.org/mysql56/sql__executor_8cc_source.html

總結

執行一條 SQL 查詢語句，期間發生了什麼？

聯結器：建立連線，管理連線、校驗使用者身份；
查詢快取：查詢語句如果命中查詢快取則直接返回，否則繼續往下執行。MySQL 8.0 已刪除該模組；
解析器，對 SQL 查詢語句進行詞法分析、語法分析，然後構建語法樹，方便後續模組讀取表名、欄位、語句型別；
最佳化器：基於查詢成本的考慮，選擇查詢成本最小的執行計劃；
執行器：根據執行計劃執行 SQL 查詢語句，從儲存引擎讀取記錄，返回給客戶端；

執行一條 SQL 語句，期間發生了什麼？