輸入 SQL 到返回數據，到底發(fā)生了什么？

大家好，我是魚皮，相信很多朋友都寫過 SQL 語句來查詢和操作數據。但是當我們從輸入 SQL 到數據庫返回數據，這中間到底發(fā)生了什么呢？

今天給大家分享一篇文章，一起來扒一扒這里面的細節(jié)。了解這些，有助于我們寫出性能更高的 SQL 語句。

* 本文轉載至陳樹義

以下講解中，數據庫以?MySQL 為例。

SQL 執(zhí)行流程

其實一個 SQL 從輸入到返回數據，其過程大致為：建立連接、分析 SQL、優(yōu)化 SQL、執(zhí)行 SQL。

建立連接

當我們發(fā)送 SQL 給 MySQL 之前，我們都會輸入賬號和密碼，從而與 MySQL 建立連接。這部分的工作，其實就是 MySQL 的連接器處理的。連接器負責跟客戶端建立連接、獲取權限、維持和管理連接。

當我們用管理員賬號對賬號權限做修改后，不影響已經存在的連接的權限，只有新建的連接才會使用新的權限設置。我們可以通過show processlist命令查看目前的連接情況，如下圖所示。

上圖中的 Command 列顯示 Sleep 有好幾個空閑的連接。如果客戶端太久沒動靜，連接器就會自動斷開，這個參數由 wait_timeout 控制，默認是 8 小時。

分析 SQL

在 MySQL 8.0 版本之前，MySQL 拿到一個查詢請求后，會先到查詢緩存中看看是否有查過。如果有，那么直接返回緩存的結果。但在 8.0 版本之后，查詢緩存功能直接被刪除了。主要是因為查詢緩存弊大于利。

因為只要對一個表進行更新，這個表上的查詢緩存就會被清空?？赡苣銊倓偘呀Y果緩存起來了，一個更新操作一來，這些緩存就全部失效了。所以查詢緩存適合那些更新不頻繁的表，用來提高查詢效率。

當拿到 SQL 之后，MySQL 會對 SQL 進行詞法分析和語法分析。詞法分析會解析每個詞的含義，而語法分析則是解析語法是否準確，分析器先會做詞法分析，再做語法分析。

你輸入的是由多個字符串和空格組成的一條 SQL 語句，MySQL 需要識別出里面的字符串分別是什么，代表什么。例如：select 表示查詢，t 表示 t 這個表，字符串 ID 識別成列 ID。做完詞法分析之后，就會做語法分析。

根據詞法分析的結果，語法分析器會根據語法規(guī)則，判斷輸入的 SQL 語句是否滿足 MySQL 語法。如果不滿足語法，會有「You have an error in your SQL syntax」的錯誤提醒。

優(yōu)化 SQL

經過分析器，MySQL 就知道你要做什么了。但在開始執(zhí)行之前，還要先經過優(yōu)化器的處理。優(yōu)化器是在表里面有多個索引的時候，決定使用哪個索引?；蛘咴谝粋€語句有多表關聯(lián)（join）的時候，決定各個表的連接順序。

有時候兩種執(zhí)行方法的邏輯結果是一樣的，但是執(zhí)行的效率會有不同，而優(yōu)化器的作用就是決定選擇使用哪一個方案。優(yōu)化器階段完成后，這個語句的執(zhí)行方案就確定下來了，然后進入執(zhí)行器階段。

執(zhí)行 SQL

MySQL 通過分析器知道了你要做什么，通過優(yōu)化器知道了該怎么做，于是就進入了執(zhí)行器階段，開始執(zhí)行語句。開始執(zhí)行的時候，要先判斷一下你對這個表 T 有沒有執(zhí)行查詢的權限，如果沒有，就會返回沒有權限的錯誤。

如果有權限，就打開表繼續(xù)執(zhí)行。打開表的時候，執(zhí)行器就會根據表的引擎定義，去使用這個引擎提供的接口。例如對于select * from T where ID=10;這條語句，ID 字段沒有索引，那么執(zhí)行器的執(zhí)行流程是這樣的：

1. 調用 InnoDB 引擎接口取這個表的第一行，判斷 ID 值是不是 10，如果不是則跳過，如果是則將這行存在結果集中。
2. 調用引擎接口取「下一行」，重復相同的判斷邏輯，直到取到這個表的最后一行。
3. 執(zhí)行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結果集返回給客戶端。

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

對于有索引的表，執(zhí)行的邏輯也差不多。第一次調用的是「取滿足條件的第一行」這個接口，之后循環(huán)取「滿足條件的下一行」這個接口，這些接口都是引擎中已經定義好的。

你會在數據庫的慢查詢日志中看到一個rows_examined的字段，表示這個語句在執(zhí)行器執(zhí)行過程中掃描了多少行。這個值就是在執(zhí)行器每次調用引擎獲取數據行的時候累加的。在有些場景下，執(zhí)行器調用一次，在引擎內部則掃描了多行，因此引擎掃描行數跟 rows_examined 并不是完全相同的。

MySQL 技術架構

其實上面的過程，就是按著 MySQL 的技術架構來的，其技術架構如下圖所示。

大體來說，MySQL 技術架構可以分為 Server 層和存儲引擎層兩部分。

Server 層負責建立連接、分析 SQL 等功能。所有跨存儲引擎的功能都在這一層實現(xiàn)，例如存儲過程、觸發(fā)器、視圖等。

存儲引擎層負責數據的存儲和提取。其架構模式是插件式的，支持 InnoDB、MyISAM、Memory 等多個存儲引擎?，F(xiàn)在最常用的是 InnoDB 存儲引擎，從 MySQL 5.5.5 開始成為了默認的存儲引擎。

InnoDB 存儲引擎

目前使用最廣泛的是 InnoDB 存儲引擎，其體系架構分為三大塊，分別是：后臺線程、內存池、文件，其體系架構如下圖所示。

在上圖中，后臺線程負責刷新內存池的數據，內存池負責緩存磁盤的數據，文件則是具體的數據存儲。

后臺線程的主要工作是負責刷新內存池的數據，保證緩沖池中的內存緩存的是最近的數據。InnoDB 存儲引擎是多線程的模型，因此其后臺有多個不同的后臺線程，負責處理不同的任務。目前有 4 種不同類型的處理線程，分別是：Master Tread、IO Thread、Purge Thread、Page Cleaner Thread。

內存池是 InnoDB 所管理內存的統(tǒng)稱，主要用于緩存磁盤數據，從而加快數據的讀取。根據其用途不同，內存池還可以分為：緩沖池、重做日志緩沖、額外內存池三大塊。

文件則是最終存取數據庫數據的地方，其存儲了包括索引文件、數據文件等相關的數據文件。

總結

最后我們總結一下一條 SQL 語句從查詢到返回數據的 5 個階段，分別是：

1. 建立連接。客戶端會首先與 MySQL 建立 TCP 連接，在連接器中會進行連接管理、權限驗證等操作。
2. 分析 SQL。分析器進行詞法、語法分析，詞法分析知道要查詢什么內容，語法分析判斷語法是否有問題。
3. 優(yōu)化 SQL。優(yōu)化器根據 SQL 情況，判斷使用哪種執(zhí)行方式更好，例如使用哪個索引，哪種表連接方式。
4. 執(zhí)行 SQL。根據優(yōu)化器的優(yōu)化結果，生成執(zhí)行計劃，執(zhí)行器調用存儲引擎的 API 來執(zhí)行查詢，最終將數據返回給客戶端。

最后，歡迎加入 魚皮的編程知識星球（點擊了解詳情），和 8000 名小伙伴們一起交流學習，向魚皮和大廠同學 1 對 1 提問、幫你制定學習計劃不迷茫、跟著魚皮直播做項目（往期項目可無限回看）。