查詢語句執行流程

查詢語句的執行流程如下：權限校驗、查詢緩存、分析器、優化器、權限校驗、執行器、引擎。

舉個例子，查詢語句如下：

select * from user where id > 1 and name = 'seven';

1. 首先，通過連接器，客戶端與MySQL服務器建立連接，并完成身份認證和權限驗證過程。在此過程中，客戶端需要提供用戶名和密碼以證明其合法性，服務器則會對這些信息進行核對。
2. 檢查是否開啟緩存。MySQL 8.0之前，Query Cache 確實會緩存完全相同的查詢結果，以便重復執行相同查詢時直接返回緩存數據。然而，MySQL 8.0及以后版本已經完全棄用Query Cache，因此在MySQL 8.0及更高版本中這一步驟不在適用。
3. MySQL的解析器會對查詢語句進行解析，檢查語法是否正確，并將查詢語句轉換為內部數據結構。預處理器則會根據MySQL的規則進一步檢查解析樹是否合法，如檢查數據表或數據列是否存在等。
4. 優化器會根據查詢語句的結構、表的統計信息等因素，生成多個可能的執行計劃，并通過成本估算器選出最優的執行計劃。兩種執行方案，先查 id > 1 還是 name = 'seven'，優化器根據自己的優化算法選擇執行效率最好的方案；這一步旨在提高查詢效率，降低資源消耗。
5. 執行器按照優化器選擇的執行計劃，調用存儲引擎的API來執行查詢。存儲引擎負責實際的數據存儲和檢索，根據執行器的請求，讀取或寫入數據。
6. 存儲引擎負責實際的數據存儲和檢索工作，根據執行器的請求，讀取或寫入數據。
7. 如果開啟了Query Cache且查詢結果能夠命中緩存，查詢結果會從緩存中直接返回。而如果沒有開啟Query Cache或緩存沒有命中，MySQL會直接返回查詢結果。

更新語句執行過程

更新語句執行流程如下：分析器、權限校驗、執行器、引擎、redo log（prepare狀態）、binlog、redo log（commit狀態）

舉個例子，更新語句如下：

update user set name = 'seven' where id = 1;

具體的執行流程如下圖：

1. 找存儲引擎取到 id = 1 這一行記錄。
2. 根據主鍵索引樹找到這一行，如果 id = 1 這一行所在的數據頁本來就在內存池（Buffer Pool）中，就直接返回給執行器；否則，需要先從磁盤讀入內存池，然后再返回。
3. 記錄Undo Log日志，對數據進行備份，便于回滾。
4. 拿到存儲引擎返回的行記錄，把 name 字段設置為 “seven”，得到一行新的記錄，然后再調用存儲引擎的接口寫入這行新記錄。
5. 將這行新數據更新到內存中，同時將這個更新操作記錄到 Redo Log 里面，為 Redo Log 中的事務打上 prepare 標識。然后告知執行器執行完成了，隨時可以提交事務。
6. 生成這個操作的 Binlog，并把 Binlog 寫入磁盤。
7. 提交事務。
8. 把剛剛寫入的 Redo Log 狀態改成提交（commit）狀態，更新完成。

關于以上日志的介紹，可以看這篇文章

以上只是一個簡單的case，方便我們能夠簡單的熟悉流程。接下來，我們對update過程中的全流程進行梳理，具體的流程如下圖:

1. 首先客戶端發送一條 SQL 語句到 Server 層的 SQL interface。
2. SQL interface 接到該請求后，先對該條語句進行解析，驗證權限是否匹配，也就是在我們上文中講到的執行器中在執行。
3. 驗證通過以后，分析器會對該語句分析，是否語法有錯誤等。
4. 接下來是優化器生成相應的執行計劃，選擇最優的執行計劃，然后是執行器根據執行計劃執行這條語句。
5. 執行器從Buffer Pool中獲取數據頁的數據，如果數據頁沒有，需要從磁盤中進行加載。
6. 開啟事務，修改數據之前先記錄Undo Log，寫入Buffer Pool的Undo Page。
7. 開始更新數據頁中的記錄，被修改的數據頁稱為臟頁，修改會被記錄到內存中的 Redo Log Buffer中，再刷盤到磁盤的Redo Log文件，此時事務是 perpare階段。
8. 這個時候更新就完成了，當時臟頁不會立即寫入磁盤，而是由后臺線程完成，這里會用double write來保證臟頁刷盤的可靠性。
9. 通知Server層，可以正式提交數據了， 執行器記錄Binlog cache，事務提交時才會將該事務中的Binlog刷新到磁盤中。
10. 這個時候Update語句完成了Buffer Pool中數據頁的修改、Undo Log、Redo Log緩存記錄，以及記錄Binlog cache緩存。
11. commit階段，這個階段是將Redo Log中事務狀態標記為commit。
12. 此時Binlog和Redo Log都已經寫入磁盤，如果觸發了刷新臟頁的操作，先把臟頁copy到double write buffer里，double write buffer 的內存數據刷到磁盤中的共享表空間 ibdata，再刷到數據磁盤上數據文件 ibd。

以上就是修改語句的全部流程，為什么記錄完redo log，不直接提交，而是先進入prepare狀態？
這里涉及到兩階段提交問題。假設先寫redo log直接提交，然后寫binlog，寫完redo log后，機器掛了，binlog日志沒有被寫入，那么機器重啟后，這臺機器會通過redo log恢復數據，但是這個時候binlog并沒有記錄該數據，后續進行機器備份的時候，就會丟失這一條數據，同時主從同步也會丟失這一條數據。

Mysql從連接到執行的全過程詳解

執行流程圖

MySQL 的架構可以大致劃分為四個層次：連接層、服務層、存儲引擎層和文件系統層。

• 連接層：負責對來自客戶端的連接進行權限驗證，并將連接信息存入連接池中，方便后續的連接復用。
• 服務層：主要負責 SQL 語句的解析與優化，還包括查詢緩存和 MySQL 內置函數的實現。
• 存儲引擎層：提供多種可插拔的存儲引擎，允許我們通過不同的引擎進行數據的存取操作。存儲引擎使得 MySQL 能夠直接與硬盤上的數據和日志進行交互，用戶可以根據需求選擇合適的引擎。從 MySQL 5.5 版本開始， InnoDB 成為了 MySQL 的默認存儲引擎。
• 文件系統層：這一層主要包括日志文件、數據文件及與 MySQL 相關的其他程序。在這四個層次中，服務層和存儲引擎層構成了架構的核心。服務層負責處理 MySQL 的核心邏輯，而存儲引擎層則直接負責數據的存取操作。

也可以將其簡單的分成兩層：Server 層和存儲引擎層，如圖

• Server 層：負責建立連接、分析和執行SQL。主要包括連接器、查詢緩存、分析器、優化器、執行器等。這些組件包含了MySQL的大部分主要功能。
• 存儲引擎層：負責數據的存儲和提取。

連接器

客戶端需要通過連接器訪問MySQL Server，連接器主要負責身份認證和權限鑒別的工作。也就是負責用戶登錄數據庫的相關認證操作，例如：校驗賬戶密碼，權限等。在用戶名密碼合法的前提下，會在權限表中查詢用戶對應的權限，并且將該權限分配給用戶。

如何查看有多少連接？

執行 show processlist 命令進行查看

其中”Command”列返回的內容中，“Sleep”表示MySQL相同中對應一個空閑連接。而“Query”表示正在查詢的連接。

連接狀態：

Command	含義
sleep	線程正在等待客戶端發數據
query	連接線程正在執行查詢
locked	線程正在等待表鎖的釋放
sorting result	線程正在對結果進行排序
sending data	向請求端返回數據

空閑連接是否一直存在

從上圖可以看出有許多空閑連接，MySQL 定義了空閑連接的最大空閑時長，由 wait_timeout 參數控制的，默認值是 8 小時（28880秒），如果空閑連接超過了這個時間，連接器就會自動將它斷開。

手動斷開空閑的連接，使用的是 kill connection + id 的命令

最大連接數

長連接和短連接

• 長連接是指連接成功后，客戶端請求一直使用是同一個連接。
• 短連接是指每次執行完SQL請求的操作之后會斷開連接，如果再有SQL請求會重新建立連接。

由于短連接會反復創建連接消耗相同資源，因此多數情況下會選擇長連接。但是為了保持長連接，會占用系統內存，而這些被占用的內存知道連接斷開以后才會釋放。這里提出了兩個解決方案：

1. 定期斷開長連接，每隔一段時間或者執行一個占用內存的大查詢以后斷開連接，從而釋放內存，當查詢的時候再重新創建連接。
2. 客戶端主動重置連接。MySQL 5.7 或者更高的版本，通過執行 mysql_reset_connection 來重新初始化連接。此過程不會重新建立連接，但是會釋放占用的內存，將連接恢復到剛剛創立連接的狀態。

查詢緩存

在建立與數據庫的連接以后就可以執行SQL語句了

如果 SQL 是查詢語句（select 語句），MySQL 就會先去查詢緩存（ Query Cache ）里查找緩存數據，看看之前有沒有執行過這一條命令，并且將執行結果按照key-value的形式緩存在內存中了。

Key 是查詢的SQL語句，Value 是查詢的結果。如果緩存 Key 被命中，就會直接返回給客戶端，如果沒有命中，就會執行后續的操作，執行完SQL仍舊會把結果緩存起來，方便下一次調用。

Mysql的機制是只要一個表有更新操作，那么這個表的查詢緩存就會被清空。如果張表不斷地被使用（更新、查詢），那么查詢緩存會頻繁地失效，獲取查詢緩存也失去了意義。不過可以運用在一些修改不頻繁的數據表。例如：系統配置、或者修改不頻繁的表。

緩存的淘汰策略是先進先出，適用于查詢遠大于修改的情況下， 否則建議使用Redis或者其他做緩存工具。因此大多數情況下不推薦使用查詢緩存。MySQL 8.0 版本后刪除了查詢緩存的功能，官方認為該功能應用場景較少，所以將其刪除。

這里說的查詢緩存是 server 層的，與Innodb 存儲引擎中的 buffer pool的緩存無關。也就是 MySQL 8.0 版本移除的是 server 層的查詢緩存，

解析 SQL

在正式執行 SQL 查詢語句之前， MySQL 會先對 SQL 語句做解析，這個工作交由「解析器」來完成。

解析器會做兩件事情

1. 詞法分析。MySQL 會根據你輸入的字符串識別出關鍵字出來，構建出 SQL 語法樹，這樣方便后面模塊獲取 SQL 類型、表名、字段名、 where 條件等等。
2. 語法分析。根據詞法分析的結果，語法解析器會根據語法規則，判斷你輸入的這個 SQL 語句是否滿足 MySQL 語法。
3. 語義分析：語義分析主要是檢查 SQL 語句中的每個對象是否符合數據庫的實際情況。如表名、字段名是否存在，用戶是否對相關表和列擁有執行權限，數據類型是否匹配等。

如果輸入的 SQL 語句語法不對，就會在解析器這個階段報錯。比如把 from 寫成了 form，這時 MySQL 解析器就會給報錯.

執行SQL語句

每條SELECT 查詢語句流程可以分為三個階段：

1. prepare 階段，也就是預處理階段；
2. optimize 階段，也就是優化階段；
3. execute 階段，也就是執行階段；

預處理器

預處理器的作用：

1. 檢查 SQL 查詢語句中的表或者字段是否存在；
2. 將 select * 中的 * 符號，擴展為表上的所有列；

優化器

優化器的主要任務是對 SQL 查詢進行優化，生成一個最優的執行計劃，從而提高查詢性能。優化器的工作基于查詢的解析樹和元數據，它會嘗試在不同的查詢執行策略中選擇效率最高的一個。

在查詢優化器中，分為邏輯查詢優化和物理查詢優化兩個大塊

• 邏輯優化會進行一些邏輯層面的優化，主要目的是通過調整 SQL 語句的結構來提高查詢效率。包括：

  • 消除冗余的子查詢：將某些子查詢轉換為連接或合并查詢。
  • 重寫查詢：比如將 OR 條件轉換為 UNION 操作。
  • 查詢合并：將多個查詢合并成一個查詢。
  • 移除不必要的操作：例如消除不需要的 ORDER BY 或 DISTINCT。

• 物理查詢優化是根據數據庫的具體執行引擎、索引、統計信息等做出的決策。這個階段會根據優化器評估的成本模型選擇合適的執行計劃。具體的優化措施包括：

  • 選擇合適的連接方式：比如選擇 Nested Loop Join、Hash Join 或 Sort Merge Join。
  • 選擇索引：通過選擇合適的索引來加速數據訪問。
  • 選擇合適的排序方式：通過使用索引掃描或臨時表來避免全表掃描。

優化器會使用基于成本的模型（Cost-Based Optimization）來評估每種查詢執行計劃的成本，選擇成本最低的執行計劃。其核心是通過計算不同執行計劃的資源消耗（如 CPU 時間、I/O 操作等），并選出最優的執行策略。

優化器的目標是通過多種優化策略來降低查詢的執行成本，生成一個盡可能高效的執行計劃。它在邏輯層面和物理層面對 SQL 查詢進行優化，以減少查詢執行所需的資源。

執行器

當解析器生成查詢計劃，并且經過優化器以后，就到了執行器。

在執行之前，執行器會首先檢查用戶是否有權限執行相應的操作。如果沒有權限，則返回錯誤信息。

執行器的主要工作包括：

• 表掃描：根據查詢條件決定是否使用索引、是否全表掃描。
• 連接操作：根據優化器選擇的連接方式（如嵌套循環連接、哈希連接等）執行表之間的數據合并。
• 排序和聚合：執行查詢中的 ORDER BY、GROUP BY 等操作。
• 數據返回：查詢結果被返回給用戶，修改操作則會提交事務。

對于涉及數據修改的 SQL（如 INSERT、UPDATE、DELETE 等），執行器還需要管理事務的提交和回滾操作，確保數據的一致性和持久性。這些操作會與 MySQL 的日志系統（Undo Log、Redo Log、Binlog） 密切交互，確保事務的 ACID 屬性。

執行器根據優化器生成的執行計劃實際執行 SQL 查詢，完成數據操作，返回查詢結果或更新數據庫狀態。它是查詢執行的最后環節，直接與 MySQL 的存儲引擎進行交互。

總結

1. 連接器：建立連接，管理連接、校驗用戶身份；
2. 查詢緩存：查詢語句如果命中查詢緩存則直接返回，否則繼續往下執行。MySQL 8.0 已刪除該模塊；
3. 解析 SQL，通過解析器對 SQL 查詢語句進行詞法分析、語法分析，然后構建語法樹，方便后續模塊讀取表名、字段、語句類型；
4. 執行 SQL：執行 SQL 共有三個階段：
   • 預處理階段：檢查表或字段是否存在；將 select * 中的 * 符號擴展為表上的所有列。
   • 優化階段：基于查詢成本的考慮， 選擇查詢成本最小的執行計劃（選擇使用哪個索引）；
   • 執行階段：根據執行計劃執行 SQL 查詢語句，從存儲引擎讀取記錄，返回給客戶端；