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

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

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

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

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

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

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

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

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

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

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

第一步：連接器

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

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

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

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

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

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

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

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

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

空閑連接會(huì)一直占用著嗎？

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

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

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

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

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

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

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

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

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

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

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

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

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

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

有兩種解決方式：

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

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

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

第二步：查詢緩存

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

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

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

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

對(duì)于更新比較頻繁的表，查詢緩存的命中率很低的，因?yàn)橹灰粋€(gè)表有更新操作，那么這個(gè)表的查詢緩存就會(huì)被清空。如果剛緩存了一個(gè)查詢結(jié)果很大的數(shù)據(jù)，還沒被使用的時(shí)候，剛好這個(gè)表有更新操作，查詢緩沖就被清空了，相當(dāng)于緩存了個(gè)寂寞。

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

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

第三步：解析器

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

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

如果我們輸入的 SQL 語句語法不對(duì)，或者數(shù)據(jù)表或者字段不存在，都會(huì)在解析器這個(gè)階段報(bào)錯(cuò)。

比如，我下面這條查詢語句，把 from 寫成了 form，這時(shí) MySQL 解析器就會(huì)給報(bào)錯(cuò)。

比如，我下面這條查詢語句，test 這張表是不存在的，這時(shí) MySQL 解析器就會(huì)給報(bào)錯(cuò)。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

第五步：執(zhí)行器

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

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

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

主鍵索引查詢

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

select * from product where id = 1;

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

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

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

全表掃描

舉個(gè)全表掃描的例子：

select * from product where name = 'iphone';

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

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

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

索引下推

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

索引下推能夠減少二級(jí)索引在查詢時(shí)的回表操作，提高查詢的效率，因?yàn)樗鼘?Server 層部分負(fù)責(zé)的事情，交給存儲(chǔ)引擎層去處理了。

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

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

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

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

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

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

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

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

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

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

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

? ?