MySQL中一條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     |
+-------------------+-------+

各欄位含義如下表

建立連線之後，除非使用者端主動斷開連線，否則伺服器會等待使用者端傳送請求。但是執行緒的建立和保持是需要消耗伺服器資源的，因此伺服器會把長時間不活動的使用者端連線斷開。

有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為什麼不推薦使用者使用自己的快取功能呢？

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

• 快取失效非常頻繁。只要一個表的資料有任何修改，針對該表的所有快取都會失效。對於更新頻繁的資料表而言，快取命中率非常低！

所以快取的功能還是交給專業的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 優化器究竟做了什麼？

舉兩個簡單的例子∶

• 當我們對多張表進行關聯查詢的時候，以哪個表的資料作為基準表。

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

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

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

• 子查詢優化

• 等價謂詞重寫

• 條件化簡

• 外連線消除

• 巢狀連線消除

• 連線消除

• 語意優化

本文不會對優化的細節展開講解，大家先對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

總結

到此這篇關於MySQL中一條SQL查詢語句是如何執行的文章就介紹到這了,更多相關MySQL SQL查詢語句執行內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！