一條 SQL 查詢語句是如何執行的？

MySQL 是典型的C/S架構（客戶端 / 服務器架構），客戶端進程向服務端進程發送一段文本（MySQL 指令），服務器進程進行語句處理然後返回執行結果。

問題來了。服務器進程對客戶端發送的請求究竟做了什麼處理呢？本文以查詢請求爲例，講解 MySQL 服務器進程的處理流程。

如下圖所示，服務器進程在處理客戶端請求的時候，大致需要進行 3 個步驟：

• 處理連接

• 解析與優化

• 存儲引擎

接下來我們來詳細瞭解一下這 3 步具體都做了什麼。

1. 處理連接

客戶端向服務器發送請求並最終收到響應，本質上是一個進程間通信的過程。

MySQL 有專門用於處理連接的模塊——連接器。

1.1 客戶端和服務端的通信方式

1.1.1 TCP/IP 協議

TCP/IP協議是 MySQL 客戶端和服務器最常用的通信方式。

我們平時所說的 MySQL 服務器默認監聽的端口是3306，這句話的前提是客戶端進程和服務器進程使用的是TCP/IP協議進行通信。

我們在使用mysql命令啓動客戶端程序時，只要在-h參數後跟隨 IP 地址作爲服務器進程所在的主機地址，那麼通訊方式便是TCP/IP協議。

如果客戶端進程和服務器進程位於同一臺主機，且要使用TCP/IP協議進行通信，則 IP 地址需要指定爲 127.0.0.1，而不能使用 localhost

1.1.2 UNIX 域套接字

如果客戶端進程和服務器進程都位於類 UNIX 操作系統（MacOS、Centos、Ubuntu 等）的主機之上，並且在啓動客戶端程序時沒有指定主機名，或者指定的主機名爲localhost，又或者指定了--protocol=socket的啓動參數，那麼客戶端進程和服務器進程就會使用UNIX域套接字進行進程間通信。

MySQL 服務器進程默認監聽的UNIX域套接字文件爲/temp/mysql.sock，客戶端進程啓動時也默認會連接到這個 UNIX 域套接字文件之上。

如果不明白UNIX域套接字到底是什麼也沒關係，只要知道這是進程之間的一種通訊方式就可以了，這裏提及的主要目的是希望讀者知曉 MySQL 客戶端和進程通訊方式不止於TCP/IP協議

1.1.3 命名管道和共享內存

如果你的 MySQL 是安裝在 Windows 主機之上，客戶端和服務器進程可以使用命名管道和共享內存的方式進行通信。

不過使用這些通信方式需要在服務端和客戶端啓動時添加一些啓動參數。

• 使用命名管道進行通信。需要在啓動服務器時添加--enable-named-pipe參數，同時在啓動客戶端進程時添加--pipe或者--protocol=pipe參數

• 使用共享內存進行通信。需要在啓動服務器時添加--shared-memory參數，啓動成功後，共享內存便成爲本地客戶端程序的默認連接方式；也可以在啓動客戶端進程的命令中加上--protocol=memory參數明確指定使用共享內存進行通信

如果不明白命名管道和共享內存到底是什麼沒關係，只要知道這是進程之間的一種通訊方式就可以了，這裏提及的主要目的是希望讀者知曉 MySQL 客戶端和進程通訊方式不止於TCP/IP協議

1.2 權限驗證

確認通信方式並且成功建立連接之後，連接器就要開始驗證你的身份了，使用的信息就是你的用戶名和密碼。

• 如果用戶名或者密碼錯誤，客戶端連接會立即斷開

• 如果用戶名密碼認證通過，連接器會到權限表裏面查出當前登陸用戶擁有的權限。之後這個連接裏面的權限判斷邏輯，都將依賴於此時讀到的權限。

1.3 查看 MySQL 連接

每當一個客戶端連接到服務端時，服務端進程都會創建一個單獨的線程來處理當前客戶端的交互操作。

那麼如何查看 MySQL 當前所有的連接？

mysql> show global status like 'Thread%';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 1     |
| Threads_created   | 1     |
| Threads_running   | 1     |
+-------------------+-------+

各字段含義如下表

PWcUPf

建立連接之後，除非客戶端主動斷開連接，否則服務器會等待客戶端發送請求。但是線程的創建和保持是需要消耗服務器資源的，因此服務器會把長時間不活動的客戶端連接斷開。

有 2 個參數控制這個自動斷開連接的行爲，每個參數都默認爲 28800 秒，8 小時。

-- 非交互式超時時間，如JDBC連接
mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+

-- 交互式超時時間，如數據庫查看工具Navicat等
mysql> show global variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+

既然連接消耗資源，那是不是 MySQL 的最大連接數也有默認限制呢？沒錯！默認最大連接數爲 151。

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

題外話：細心的讀者可能會發現 MySQL 某些查詢語句帶有global關鍵字，這個關鍵字有什麼含義呢？

MySQL 的系統變量有兩個作用範圍（不區分大小寫），分別是

• GLOBAL（全局範圍）：變量的設置影響服務器和所有客戶端

• SESSION（會話範圍）：變量的設置僅影響當前連接（會話）

但是並非每個參數都具有兩個作用範圍，比如允許同時連接到服務器的客戶端的數量max_connections就只有全局級別。

當沒有帶作用範圍關鍵字時，默認是SESSION級別，包括查詢和修改操作。

比如修改一個參數之後，在當前窗口生效了，但是在其他窗口卻沒有生效

show VARIABLES like 'autocommit';
set autocommit = on;

因此，如果只是臨時修改，請使用SESSION級別，如果需要當前設置在其他會話中生效，需要使用GLOBAL關鍵字。

到此爲止，服務器進程已經和客戶端進程建立了連接，下一步將處理客戶端傳來的請求了。

2. 解析與優化

服務器收到客戶端傳來的請求之後，還需要經過查詢緩存、詞法語法解析和預處理、查詢優化的處理。

2.1 查詢緩存

如果我們兩次都執行同一條查詢指令，第二次的響應時間會不會比第一次的響應時間短一些？

之前使用過 Redis 緩存工具的讀者應該會有這個很自然的想法，MySQL 收到查詢請求之後應該先到緩存中查看一下，看一下之前是不是執行過這條指令。如果緩存命中，則直接返回結果；否則重新進行查詢，然後加入緩存。

MySQL 確實內部自帶了一個緩存模塊。

現在有一張 500W 行且沒有添加索引的數據表，我執行以下命令兩次，第二次會不會變得很快？

SELECT * FROM t_user WHERE user_name = '蟬沐風'

並不會！說明緩存沒有生效，爲什麼？MySQL 默認是關閉自身的緩存功能的，查看一下query_cache_type變量設置。

mysql> show variables like 'query_cache_type';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_type             | OFF     |
+------------------------------+---------+

默認關閉就意味着不推薦，MySQL 爲什麼不推薦用戶使用自己的緩存功能呢？

1. MySQL 自帶的緩存系統應用場景非常有限，它要求 SQL 語句必須一模一樣，多一個空格，變一個大小寫都被認爲是兩條不同的 SQL 語句

2. 緩存失效非常頻繁。只要一個表的數據有任何修改，針對該表的所有緩存都會失效。對於更新頻繁的數據表而言，緩存命中率非常低！

所以緩存的功能還是交給專業的 ORM 框架（比如 MyBatis 默認開啓一級緩存）或者獨立的緩存服務 Redis 更加適合。

MySQL8.0 已經徹底移除了緩存功能

2.2 解析器 & 預處理器（Parser & Preprocessor）

現在跳過緩存這一步了，接下來需要做什麼了？

如果我隨便在客戶端終端裏輸入一個字符串chanmufeng，服務器返回了一個 1064 的錯誤

mysql> chanmufeng;
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 'chanmufeng' at line 1

服務器是怎麼判斷出我的輸入是錯誤的呢？這就是 MySQL 的 Parser 解析器的作用了，它主要包含兩步，分別是詞法解析和語法分析。

2.2.1 詞法解析

以下面的 SQL 語句爲例

SELECT * FROM t_user WHERE user_name = '蟬沐風' AND age > 3;

分析器先會做 “詞法分析”，就是把一條完整的 SQL 語句打碎成一個個單詞，比如一條簡單的 SQL 語句，會打碎成 8 個符號，每個符號是什麼類型，從哪裏開始到哪裏結束。

MySQL 從你輸入的SELECT這個關鍵字識別出來，這是一個查詢語句。它也要把字符串t_user識 別成 “表名 t_user”，把字符串user_name識別成 “列 user_name"。

2.2.2 語法分析

做完詞法解析，接下來需要做語法分析了。

根據詞法分析的結果，語法分析器會根據語法規則，判斷你輸入的這個 SQL 語句是否滿足 MySQL 語法，比如單引號是否閉合，關鍵詞拼寫是否正確等。

解析器會根據 SQL 語句生成一個數據結構，這個數據結構我們成爲解析樹。

我故意拼錯了SELECT關鍵字，MySQL 報了語法錯誤，就是在語法分析這一步。

mysql> ELECT * FROM t_user WHERE user_name = '蟬沐風' AND age > 3;
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 'ELECT * FROM t_user WHERE user_name = '蟬沐風'' at line 1

詞法語法分析是一個非常基礎的功能，Java 的編譯器、百度搜索引擎如果要識別語句，必須也要有詞法語法分析功能。

任何數據庫的中間件，要解析 SQL 完成路由功能，也必須要有詞法和語法分析功能，比如 Mycat，Sharding-JDBC（用到了 Druid Parser）等都是如此。在市面上也有很多的開源的詞法解析的工具，比如 LEX，Yacc 等。

2.2.3 預處理器

如果我們寫了一條語法和詞法都沒有問題的 SQL，但是字段名和表名卻不存在，這個錯誤是在哪一個階段爆出的呢？

詞法解析和語法分析是無法知道數據庫裏有什麼表，有哪些字段的。要知道這些信息還需要解析階段的另一個工具——預處理器。

它會檢查生成的解析樹，解決解析器無法解析的語義。比如，它會檢查表和列名是否存在，檢查名字和別名，保證沒有歧義。預處理之後得到一個新的解析樹。

本質上，解析和預處理是一個編譯過程，涉及到詞法解析、語法和語義分析，更多細節我們不會探究，感興趣的讀者可以看一下編譯原理方面的書籍。

2.3 查詢優化器（Optimizer）與查詢執行計劃

到了這一步，MySQL 終於知道我們想查詢的表和列以及相應的搜索條件了，是不是可以直接進行查詢了？

還不行。MySQL 作者擔心我們寫的 SQL 太垃圾，所以有設計出一個叫做查詢優化器的東東，輔助我們提高查詢效率。

2.3.1 什麼是查詢優化器？

一條 SQL 語句是不是隻有一種執行方式？或者說數據庫最終執行的 SQL 是不是就是我們發送的 SQL?

不是。一條 SQL 語句是可以有很多種執行方式的，最終返回相同的結果，他們是等價的。

舉一個非常簡單的例子，比如你執行下面這樣的語句：

SELECT * FROM t1, t2 WHERE t1.id = 10 AND t2.id = 20

• 既可以先從表 t1 裏面取出 id=10 的記錄，再根據 id 值關聯到表 t2，再判斷 t2 裏面 id 的值是否等於 20。

• 也可以先從表 t2 裏面取出 id=20 的記錄，再根據 id 值關聯到表 t1，再判斷 t1 裏面 id 的值是否等於 10。

這兩種執行方法的邏輯結果是一樣的，但是執行的效率會有不同，如果有這麼多種執行方式，這些執行方式怎麼得到的？最終選擇哪一種去執行？根據什麼判斷標準去選擇？

這個就是 MySQL 的查詢優化器的模塊（Optimizer）的工作。

查詢優化器的目的就是根據解析樹生成不同的執行計劃（Execution Plan），然後選擇一種最優的執行計劃，MySQL 裏面使用的是基於開銷（cost）的優化器，哪種執行計劃開銷最小，就用哪種。

2.3.2 優化器究竟做了什麼？

舉兩個簡單的例子∶

1. 當我們對多張表進行關聯查詢的時候，以哪個表的數據作爲基準表。

2. 有多個索引可以使用的時候，選擇哪個索引。

實際上，對於每一種數據庫來說，優化器的模塊都是必不可少的，他們通過複雜的算法實現儘可能優化查詢效率。

往細節上說，查詢優化器主要做了下面幾方面的優化：

• 子查詢優化

• 等價謂詞重寫

• 條件化簡

• 外連接消除

• 嵌套連接消除

• 連接消除

• 語義優化

本文不會對優化的細節展開講解，大家先對 MySQL 的整體架構有所瞭解就可以了，具體細節之後單獨開篇介紹

但是優化器也不是萬能的，如果 SQL 語句寫得實在太垃圾，再牛的優化器也救不了你了。因此大家在編寫 SQL 語句的時候還是要有意識地進行優化。

2.3.3 執行計劃

優化完之後，得到一個什麼東西呢？優化器最終會把解析樹變成一個查詢執行計劃。

查詢執行計劃展示了接下來執行查詢的具體方式，比如多張表關聯查詢，先查詢哪張表，在執行查詢的時候有多個索引可以使用，實際上該使用哪些索引。

MySQL 提供了一個查看執行計劃的工具。我們在 SQL 語句前面加上 EXPLAIN就可以看到執行計劃的信息。

mysql> EXPLAIN SELECT * FROM t_user WHERE user_name = '';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

如果要得到更加詳細的信息，還可以用FORMAT=JSON，或者開啓optimizer trace。

mysql> EXPLAIN FORMAT=JSON SELECT * FROM t_user WHERE user_name = '';

文本不會帶大家詳細瞭解執行計劃的每一個參數，內容很龐雜，大家先對 MySQL 的整體架構有所瞭解就可以了，具體細節之後單獨開篇介紹

3. 存儲引擎

經歷千辛萬苦，MySQL 終於算出了最終的執行計劃，然後就可以直接執行了嗎？

好吧。。。依然還不可以。

我們知道，表是由一行一行的記錄組成的，但這只是邏輯上的概念，或者說只是看上去是這樣而已。

3.1 什麼是存儲引擎

到底該把數據存儲在什麼位置，是內存還是磁盤？怎麼從表裏讀取數據，以及怎麼把數據寫入具體的表中，這都是存儲引擎 負責的事情。

好吧，看到這裏或許你還不知道存儲引擎到底是什麼。畢竟存儲引擎這個名字聽起來太玄乎了，它的前身叫做表處理器，是不是就接地氣了許多呢？

3.2 爲什麼需要存儲引擎

因爲存儲的需求不同。

試想一下：

• 如果一張表，需要很高的訪問速度，而不需要考慮持久化的問題，是不是最好把數據放在內存呢？

• 如果一張表，是用來做歷史數據存檔的，不需要修改，也不需要索引，那是不是要支持數據的壓縮?

• 如果一張表用在讀寫併發很多的業務中，是不是要支持讀寫互不干擾，而且要保證比較高的數據一致性呢?

大家應該明白了，爲什麼要支持這麼多的存儲引擎，因爲一種存儲引擎不能提供所有的特性。

存儲引擎是計算機抽象的典型代表，它的功能就是接受上層指令，然後對錶中數據進行讀取和寫入，而這些操作對上層完全是屏蔽的。你甚至可以查閱 MySQL 文檔定義自己的存儲引擎，只要對外實現同樣的接口就可以了。

存儲引擎就是 MySQL 對數據進行讀寫的插件而已，可以根據不同目的隨意更換（插拔）

3.3 存儲引擎怎麼用

3.3.1 創建表的時候指定存儲引擎

在創建表的時候可以指定當前表的存儲引擎，如果沒有指定，默認的存儲引擎爲InnoDB，如果想顯式指定存儲引擎，可以這樣

CREATE TABLE `t_user_innodb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=innodb DEFAULT CHARSET=utf8mb4;

3.3.2 修改表的存儲引擎

ALTER TABLE 表名 ENGINE = 存儲引擎名稱;

3.4 存儲引擎底層區別

下面我們分別創建 3 張設置了不同存儲引擎的表，t_user_innodb、t_user_myisam、t_user_memory 我們看一下不同存儲引擎在底層存儲方面的差異，首先找到 MySQL 的數據存儲目錄

mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+

進入到目標目錄之後，找到當前數據庫對應的目錄（MySQL 會爲一個數據庫創建一個同名的目錄），數據庫中表的存儲結構如下不同的存儲引擎存放數據的方式不一樣，產生的文件數量和格式也不一樣，InnoDB 文件包含 2 個，MEMORY 文件包含 1 個，MYISAM 文件包含 3 個。

3.5 常見存儲引擎比較

首先我們查看一下當前 MySQL 服務器支持的存儲引擎都有哪一些。

mysql> SHOW ENGINES;
+--------------------+---------+--------------+------+------------+
| Engine             | Support | Transactions | XA   | Savepoints |
+--------------------+---------+--------------+------+------------+
| InnoDB             | DEFAULT | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | NO           | NO   | NO         |
| MEMORY             | YES     | NO           | NO   | NO         |
| BLACKHOLE          | YES     | NO           | NO   | NO         |
| MyISAM             | YES     | NO           | NO   | NO         |
| CSV                | YES     | NO           | NO   | NO         |
| ARCHIVE            | YES     | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | NO           | NO   | NO         |
| FEDERATED          | NO      | NULL         | NULL | NULL       |
+--------------------+---------+--------------+------+------------+

其中，

• Support 表示該存儲引擎是否可用；

• DEFAULT 表示當前 MySQL 服務器默認的存儲引擎；

• Transactions 表示該存儲引擎是否支持事務；

• XA 表示該存儲引擎是否支持分佈式事務；

• Savepoints 表示該存儲引擎是否支持事務的部分回滾。

3.5.1 MylSAM

應用範圍比較小，表級鎖定限制了讀 / 寫的性能，因此在 Web 和數據倉庫配置中，通常用於只讀或以讀爲主的工作。

特點:

• 支持表級別的鎖（插入和更新會鎖表），不支持事務；

• 擁有較高的插入（insert）和查詢（select）速度；

• 存儲了表的行數（count 速度更快）。

怎麼快速向數據庫插入 100 萬條數據？

可以先用 MylSAM 插入數據，然後修改存儲引擎爲 InnoDB。

3.5.2 InnoDB

MySQL 5.7 及更新版中的默認存儲引擎。InnoDB 是一個事務安全（與 ACID 兼容）的 MySQL 存儲引擎，它具有提交、回滾和崩潰恢復功能來保護用戶數據。InnoDB 行級鎖（不升級爲更粗粒度的鎖）和 Oracle 風格的一致非鎖讀提高了多用戶併發性。InnoDB 將用戶數據存儲在聚集索引中，以減少基於主鍵的常見查詢的 I/O。爲了保持數據完整性，InnoDB 還支持外鍵引用完整性約束。

特點：

• 支持事務，支持外鍵，因此數據的完整性、一致性更高；

• 支持行級別的鎖和表級別的鎖；

• 支持讀寫併發，寫不阻塞讀（MVCC）；

• 特殊的索引存放方式，可以減少 IO，提升査詢效率。

番外：InnoDB 本來是 InnobaseOy 公司開發的，它和 MySQL AB 公司合作開源了 InnoDB 的代碼。但是沒想到 MySQL 的競爭對手 Oracle 把 InnobaseOy 收購了。後來 08 年 Sun 公司（開發 Java 語言的 Sun）收購了 MySQL AB，09 年 Sun 公司又被 Oracle 收購了，所以 MySQL 和 InnoDB 又是一家了。有人覺得 MySQL 越來越像 Oracle, 其實也是這個原因。

3.5.3 Memory

將所有數據存儲在 RAM 中，以便快速訪問。這個引擎以前被稱爲堆引擎。

特點：

• 把數據放在內存裏面，讀寫的速度很快，但是數據庫重啓或者崩潰，數據會全部消失；

• 只適合做臨時表。

3.5.4 CSV

它的表實際上是帶有逗號分隔值的文本文件。csv 表允許以 CSV 格式導入或轉儲數據, 以便與讀寫相同格式的腳本和應用程序交換數據。因爲 CSV 表沒有索引，所以通常在正常操作期間將數據保存在 InnoDB 表中，只在導入或導出階段使用 csv 表。

特點：

• 不允許空行，不支持索引；

• 格式通用，可以直接編輯，適合在不同數據庫之間導入導出。

3.5.5 Archive

專用與存檔，空間經過壓縮，用於存儲和檢索大量很少引用的信息。

特點：

• 不支持索引；

• 不支持 update、delete。

3.6 如何選擇存儲引擎

• 如果對數據一致性要求比較高，需要事務支持，可以選擇 InnoDB。

• 如果數據查詢多更新少，對查詢性能要求比較高，可以選擇 MyISAM。

• 如果需要一個用於查詢的臨時表，可以選擇 Memory。

如果所有的存儲引擎都不能滿足你的需求，並且技術能力足夠，可以根據官網內部手冊用 C 語言開發一個存儲引擎：https://dev.mvsql.com/doc/internals/en/custom-engine.html

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/M9PmFMmXToJOMyzVWj0Z5g