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

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

但是有沒有想過，執(zhí)行一條 select 查詢語句，在 MySQL 中期間發(fā)生了什么？

帶著這個問題，我們可以很好的了解 MySQL 內(nèi)部的架構(gòu)。

所以，這次小林就帶大家拆解一下 MySQL 內(nèi)部的結(jié)構(gòu)，看看內(nèi)部里的每一個“零件”具體是負(fù)責(zé)做什么的。

MySQL 執(zhí)行流程是怎樣的？

先來一個上帝視角圖，下面就是 MySQL 執(zhí)行一條 SQL 查詢語句的流程，也從圖中可以看到 MySQL 內(nèi)部架構(gòu)里的各個功能模塊。

查詢語句執(zhí)行流程

可以看到， MySQL 的架構(gòu)共分為兩層：Server 層和存儲引擎層。

• Server 層負(fù)責(zé)建立連接、分析和執(zhí)行 SQL。MySQL 大多數(shù)的核心功能模塊都在這實現(xiàn)，主要包括連接器，查詢緩存、解析器、優(yōu)化器、執(zhí)行器等。另外，所有的內(nèi)置函數(shù)（如日期、時間、數(shù)學(xué)和加密函數(shù)等）和所有跨存儲引擎的功能（如存儲過程、觸發(fā)器、視圖等。）都在 Server 層實現(xiàn)。
• 存儲引擎層負(fù)責(zé)數(shù)據(jù)的存儲和提取。支持 InnoDB、MyISAM、Memory 等多個存儲引擎，不同的存儲引擎共用一個 Server 層?，F(xiàn)在最常用的存儲引擎是 InnoDB，從 MySQL 5.5 版本開始， InnoDB 成為了 MySQL 的默認(rèn)存儲引擎。我們常說的索引數(shù)據(jù)結(jié)構(gòu)，就是由存儲引擎層實現(xiàn)的，不同的存儲引擎支持的索引類型也不相同，比如 InnoDB 支持索引類型是 B+樹 ，且是默認(rèn)使用，也就是說在數(shù)據(jù)表中創(chuàng)建的主鍵索引和二級索引默認(rèn)使用的是 B+ 樹索引。

好了，現(xiàn)在我們對 Server 層和存儲引擎層有了一個簡單認(rèn)識，接下來，就詳細(xì)說一條 SQL 查詢語句的執(zhí)行流程，依次看看每一個功能模塊的作用。

第一步：連接器

如果你在 Linux 操作系統(tǒng)里要使用 MySQL，那你第一步肯定是要先連接 MySQL 服務(wù)，然后才能執(zhí)行 SQL 語句，普遍我們都是使用下面這條命令進(jìn)行連接：

# -h 指定 MySQL 服務(wù)得 IP 地址，如果是連接本地的 MySQL服務(wù)，可以不用這個參數(shù)；
# -u 指定用戶名，管理員角色名為 root；
# -p 指定密碼，如果命令行中不填寫密碼（為了密碼安全，建議不要在命令行寫密碼），就需要在交互對話里面輸入密碼
mysql -h$ip -u$user -p

連接的過程需要先經(jīng)過 TCP 三次握手，因為 MySQL 是基于 TCP 協(xié)議進(jìn)行傳輸?shù)模绻?MySQL 服務(wù)并沒有啟動，則會收到如下的報錯：

如果  MySQL 服務(wù)正常運行，完成 TCP 連接的建立后，連接器就要開始驗證你的用戶名和密碼，如果用戶名或密碼不對，就收到一個"Access denied for user"的錯誤，然后客戶端程序結(jié)束執(zhí)行。

如果用戶密碼都沒有問題，連接器就會獲取該用戶的權(quán)限，然后保存起來，后續(xù)該用戶在此連接里的任何操作，都會基于連接開始時讀到的權(quán)限進(jìn)行權(quán)限邏輯的判斷。

所以，如果一個用戶已經(jīng)建立了連接，即使管理員中途修改了該用戶的權(quán)限，也不會影響已經(jīng)存在連接的權(quán)限。修改完成后，只有再新建的連接才會使用新的權(quán)限設(shè)置。

如何查看 MySQL 服務(wù)被多少個客戶端連接了？

如果你想知道當(dāng)前  MySQL 服務(wù)被多少個客戶端連接了，你可以執(zhí)行 show processlist 命令進(jìn)行查看。

比如上圖的顯示結(jié)果，共有兩個用戶名為 root 的用戶連接了 MySQL 服務(wù)，其中 id 為 6 的用戶的 Command 列的狀態(tài)為 Sleep ，這意味著該用戶連接完 MySQL 服務(wù)就沒有再執(zhí)行過任何命令，也就是說這是一個空閑的連接，并且空閑的時長是 736 秒（ Time 列）。

空閑連接會一直占用著嗎？

當(dāng)然不是了，MySQL 定義了空閑連接的最大空閑時長，由 wait_timeout 參數(shù)控制的，默認(rèn)值是 8 小時（28880秒），如果空閑連接超過了這個時間，連接器就會自動將它斷開。

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

當(dāng)然，我們自己也可以手動斷開空閑的連接，使用的是 kill connection + id 的命令。

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

一個處于空閑狀態(tài)的連接被服務(wù)端主動斷開后，這個客戶端并不會馬上知道，等到客戶端在發(fā)起下一個請求的時候，才會收到這樣的報錯“ERROR 2013 (HY000): Lost connection to MySQL server during query”。

MySQL 的連接數(shù)有限制嗎？

MySQL 服務(wù)支持的最大連接數(shù)由 max_connections 參數(shù)控制，比如我的 MySQL 服務(wù)默認(rèn)是 151 個,超過這個值，系統(tǒng)就會拒絕接下來的連接請求，并報錯提示“Too many connections”。

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

MySQL 的連接也跟 HTTP 一樣，有短連接和長連接的概念，它們的區(qū)別如下：

// 短連接
連接 mysql 服務(wù)（TCP 三次握手）
執(zhí)行sql
斷開 mysql 服務(wù)（TCP 四次揮手）

// 長連接
連接 mysql 服務(wù)（TCP 三次握手）
執(zhí)行sql
執(zhí)行sql
執(zhí)行sql
....
斷開 mysql 服務(wù)（TCP 四次揮手）

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

但是，使用長連接后可能會占用內(nèi)存增多，因為 MySQL 在執(zhí)行查詢過程中臨時使用內(nèi)存管理連接對象，這些連接對象資源只有在連接斷開時才會釋放。如果長連接累計很多，將導(dǎo)致 MySQL 服務(wù)占用內(nèi)存太大，有可能會被系統(tǒng)強制殺掉，這樣會發(fā)生 MySQL 服務(wù)異常重啟的現(xiàn)象。

怎么解決長連接占用內(nèi)存的問題？

有兩種解決方式：

• 第一種，定期斷開長連接。既然斷開連接后就會釋放連接占用的內(nèi)存資源，那么我們可以定期斷開長連接。
• 第二種，客戶端主動重置連接。MySQL 5.7 版本實現(xiàn)了 mysql_reset_connection() 函數(shù)的接口，注意這是接口函數(shù)不是命令，那么當(dāng)客戶端執(zhí)行了一個很大的操作后，在代碼里調(diào)用 mysql_reset_connection 函數(shù)來重置連接，達(dá)到釋放內(nèi)存的效果。這個過程不需要重連和重新做權(quán)限驗證，但是會將連接恢復(fù)到剛剛創(chuàng)建完時的狀態(tài)。

至此，連接器的工作做完了，簡單總結(jié)一下：

• 與客戶端進(jìn)行 TCP 三次握手建立連接；
• 校驗客戶端的用戶名和密碼，如果用戶名或密碼不對，則會報錯；
• 如果用戶名和密碼都對了，會讀取該用戶的權(quán)限，然后后面的權(quán)限邏輯判斷都基于此時讀取到的權(quán)限；

第二步：查詢緩存

連接器得工作完成后，客戶端就可以向 MySQL 服務(wù)發(fā)送 SQL 語句了，MySQL 服務(wù)收到 SQL 語句后，就會解析出 SQL 語句的第一個字段，看看是什么類型的語句。

如果 SQL 是查詢語句（select 語句），MySQL 就會先去查詢緩存（ Query Cache ）里查找緩存數(shù)據(jù)，看看之前有沒有執(zhí)行過這一條命令，這個查詢緩存是以 key-value 形式保存在內(nèi)存中的，key 為 SQL 查詢語句，value 為 SQL 語句查詢的結(jié)果。

如果查詢的語句命中查詢緩存，那么就會直接返回 value 給客戶端。如果查詢的語句沒有命中查詢緩存中，那么就要往下繼續(xù)執(zhí)行，等執(zhí)行完后，查詢的結(jié)果就會被存入查詢緩存中。

這么看，查詢緩存還挺有用，但是其實查詢緩存挺雞肋的。

對于更新比較頻繁的表，查詢緩存的命中率很低的，因為只要一個表有更新操作，那么這個表的查詢緩存就會被清空。如果剛緩存了一個查詢結(jié)果很大的數(shù)據(jù)，還沒被使用的時候，剛好這個表有更新操作，查詢緩沖就被清空了，相當(dāng)于緩存了個寂寞。

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

對于 MySQL 8.0 之前的版本，如果想關(guān)閉查詢緩存，我們可以通過將參數(shù) query_cache_type 設(shè)置成 DEMAND。

第三步：解析器

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

• 第一件事情，詞法分析。MySQL 會根據(jù)你輸入的字符串識別出關(guān)鍵字出來，構(gòu)建出 SQL 語法樹，這樣方面后面模塊獲取 SQL 類型、表名、字段名、 where 條件等等。
• 第二件事情，語法分析。根據(jù)詞法分析的結(jié)果，語法解析器會根據(jù)語法規(guī)則，判斷你輸入的這個 SQL 語句是否滿足 MySQL 語法。

如果我們輸入的 SQL 語句語法不對，或者數(shù)據(jù)表或者字段不存在，都會在解析器這個階段報錯。

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

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

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

第四步：優(yōu)化器

經(jīng)過解析器后，接著就要執(zhí)行 SQL 查詢語句了，但是在真正執(zhí)行之前，會檢查用戶是否有訪問該數(shù)據(jù)庫表的權(quán)限，如果沒有就直接報錯了。

如果有權(quán)限，就進(jìn)入 SQL 查詢語句的執(zhí)行階段，而 SQL 查詢語句真正執(zhí)行之前需要先制定一個執(zhí)行計劃，這個工作交由「優(yōu)化器」來完成的。

優(yōu)化器主要負(fù)責(zé)將 SQL 查詢語句的執(zhí)行方案確定下來，比如在表里面有多個索引的時候，優(yōu)化器會基于查詢成本的考慮，來決定選擇使用哪個索引。

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

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

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

這張 product 表只有一個索引就是主鍵，現(xiàn)在我在表中將 name 設(shè)置為普通索引（二級索引）。

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

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

這條查詢語句的結(jié)果既可以使用主鍵索引，也可以使用普通索引，但是執(zhí)行的效率會不同。這時，就需要優(yōu)化器來決定使用哪個索引了。

很顯然這條查詢語句是覆蓋索引，直接在二級索引就能查找到結(jié)果（因為二級索引的 B+ 樹的葉子節(jié)點的數(shù)據(jù)存儲的是主鍵值），就沒必要在主鍵索引查找了，因為查詢主鍵索引的 B+ 樹的成本會比查詢二級索引的 B+ 的成本大，優(yōu)化器基于查詢成本的考慮，會選擇查詢代價小的普通索引。

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

第五步：執(zhí)行器

經(jīng)歷完優(yōu)化器后，就確定了執(zhí)行方案，接下來 MySQL 就真正開始執(zhí)行語句了，這個工作是由「執(zhí)行器」完成的。在執(zhí)行的過程中，執(zhí)行器就會和存儲引擎交互了，交互是以記錄為單位的。

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

• 主鍵索引查詢
• 全表掃描
• 索引嚇退

主鍵索引查詢

以本文開頭查詢語句為例，看看執(zhí)行器是怎么工作的。

select * from product where id = 1;

這條查詢語句的查詢條件用到了主鍵索引，而且是等值查詢，同時主鍵 id 是唯一，不會有 id 相同的記錄，所以優(yōu)化器決定選用訪問類型為 const 進(jìn)行查詢，也就是使用主鍵索引查詢一條記錄，那么執(zhí)行器與存儲引擎的執(zhí)行流程是這樣的：

• 執(zhí)行器第一次查詢，會調(diào)用 read_first_record 函數(shù)指針指向的函數(shù)，因為優(yōu)化器選擇的訪問類型為 const，這個函數(shù)指針被指向為 InnoDB 引擎索引查詢的接口，把條件id = 1 交給存儲引擎，讓存儲引擎定位符合條件的第一條記錄。
• 存儲引擎通過主鍵索引的 B+ 樹結(jié)構(gòu)定位到 id = 1的第一條記錄，如果記錄是不存在的，就會向執(zhí)行器上報記錄找不到的錯誤，然后查詢結(jié)束。如果記錄是存在的，就會將記錄返回給執(zhí)行器；
• 執(zhí)行器從存儲引擎讀到記錄后，接著判斷記錄是否符合查詢條件，如果符合則發(fā)送給客戶端，如果不符合則跳過該記錄。
• 執(zhí)行器查詢的過程是一個 while 循環(huán)，所以還會再查一次，但是這次因為不是第一次查詢了，所以會調(diào)用 read_record 函數(shù)指針指向的函數(shù)，因為優(yōu)化器選擇的訪問類型為 const，這個函數(shù)指針被指向為一個永遠(yuǎn)返回 - 1 的函數(shù)，所以當(dāng)調(diào)用該函數(shù)的時候，執(zhí)行器就退出循環(huán)，也就是結(jié)束查詢了。

至此，這個語句就執(zhí)行完成了。

全表掃描

舉個全表掃描的例子：

select * from product where name = 'iphone';

這條查詢語句的查詢條件沒有用到索引，所以優(yōu)化器決定選用訪問類型為 ALL 進(jìn)行查詢，也就是全表掃描的方式查詢，那么這時執(zhí)行器與存儲引擎的執(zhí)行流程是這樣的：

• 執(zhí)行器第一次查詢，會調(diào)用 read_first_record 函數(shù)指針指向的函數(shù)，因為優(yōu)化器選擇的訪問類型為 all，這個函數(shù)指針被指向為 InnoDB 引擎全掃描的接口，讓存儲引擎讀取表中的第一條記錄；
• 執(zhí)行器會判斷讀到的這條記錄的 name 是不是 iphone，如果不是則跳過；如果是則將記錄發(fā)給客戶的（是的沒錯，Server 層每從存儲引擎讀到一條記錄就會發(fā)送給客戶端，之所以客戶端顯示的時候是直接顯示所有記錄的，是因為客戶端是等查詢語句查詢完成后，才會顯示出所有的記錄）。
• 執(zhí)行器查詢的過程是一個 while 循環(huán)，所以還會再查一次，會調(diào)用 read_record 函數(shù)指針指向的函數(shù)，因為優(yōu)化器選擇的訪問類型為 all，read_record 函數(shù)指針指向的還是 InnoDB 引擎全掃描的接口，所以接著向存儲引擎層要求繼續(xù)讀剛才那條記錄的下一條記錄，存儲引擎把下一條記錄取出后就將其返回給執(zhí)行器（Server層），執(zhí)行器繼續(xù)判斷條件，不符合查詢條件即跳過該記錄，否則發(fā)送到客戶端；
• 一直重復(fù)上述過程，直到存儲引擎把表中的所有記錄讀完，然后向執(zhí)行器（Server層） 返回了讀取完畢的信息；
• 執(zhí)行器收到存儲引擎報告的查詢完畢的信息，退出循環(huán)，停止查詢。

至此，這個語句就執(zhí)行完成了。

索引下推

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

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

舉一個具體的例子，方便大家理解，這里一張用戶表如下，我對 age 和 reword 字段建立了聯(lián)合索引（age，reword）：

現(xiàn)在有下面這條查詢語句：

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

聯(lián)合索引當(dāng)遇到范圍查詢 (>、<、between、like) 就會停止匹配，也就是 a 字段能用到聯(lián)合索引，但是 reward 字段則無法利用到索引。具體原因這里可以看這篇：索引常見面試題

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

• Server 層首先調(diào)用存儲引擎的接口定位到滿足查詢條件的第一條二級索引記錄，也就是定位到 age > 20 的第一條記錄；
• 存儲引起根據(jù)二級索引的 B+ 樹快速定位到這條記錄后，獲取主鍵值，然后進(jìn)行回表操作，將完整的記錄返回給 Server 層；
• Server 層在判斷該記錄的 reward 是否等于 100000，如果成立則將其發(fā)送給客戶端；否則跳過該記錄；
• 接著，繼續(xù)向存儲引擎索要下一條記錄，存儲引擎在二級索引定位到記錄后，獲取主鍵值，然后回表操作，將完整的記錄返回給 Server 層；
• 如此往復(fù)，直到存儲引擎把表中的所有記錄讀完。

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

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

• Server 層首先調(diào)用存儲引擎的接口定位到滿足查詢條件的第一條二級索引記錄，也就是定位到 age > 20 的第一條記錄；
• 存儲引擎定位到二級索引后，先不執(zhí)行回表操作，而是先判斷一下該索引中包含的列（reward列）的條件（reward 是否等于 100000）是否成立。如果條件不成立，則直接跳過該二級索引。如果成立，則執(zhí)行回表操作，將完成記錄返回給 Server 層。
• Server 層在判斷其他的查詢條件（本次查詢沒有其他條件）是否成立，如果成立則將其發(fā)送給客戶端；否則跳過該記錄，然后向存儲引擎索要下一條記錄。
• 如此往復(fù)，直到存儲引擎把表中的所有記錄讀完。

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

當(dāng)你發(fā)現(xiàn)執(zhí)行計劃里的 Extr 部分顯示了 “Using index condition”，說明使用了索引下推。

? ?