MySQL的基礎架構

首先就是客戶端，其次Server服務層，大多數MySQL的核心服務都在這一層，包括連接、分析、優化、緩存以及所有的內置函數（時間、日期、加密函數），所有跨存儲引擎功能都在這一層實現：存儲過程、觸發器、視圖等；最后存儲引擎層，負責MySQL中數據的存儲和提取，Server層通過API與存儲引擎進行通信，這些接口屏蔽了不同存儲引擎之間差異，使得這些差異對上層（Server）的查詢過程比較透明（清晰，沒有阻礙，差異不存在一樣）。

SQL語句在MySQL中如何執行

• 客戶端發送SQL查詢語句到MySQL的服務器

• MySQL服務器的連接器開始處理這個請求，跟客戶端建立連接，獲取權限，管理連接

• （Mysql8.0之前還有查詢緩存，不過后面數據不一會就變更需要更新緩存，就顯得雞肋了點，就刪除了）

• 使用解析器（分析器）去對SQL語句進行解析，檢查語法規則，確保引用的數據庫、表和列都存在，并處理SQL語句中的名稱解析和權限驗證。（首先詞法分析，然后語法分析和分析機不斷循環遍歷關鍵字添加到語法樹中，生成語法樹）

• 使用優化器負責確定SQL語句的執行計劃，這包括選擇使用哪些索引，以及決定表之間的連接順序，會嘗試找出最高效的方式來執行查詢。

• 執行器會調用存儲引擎的API來進行數據的讀寫（使用鎖）

• 在引擎層中寫一個undolog版本鏈用于MVCC回滾

  寫redolog，寫下所有命令，用于故障恢復

  若有開啟binlog，這時會寫binlog用于主從同步

  提交事務，刷redolog進磁盤，刷binlog進磁盤，二階段提交保證數據一致性。

• MySQL的存儲引擎是插件式的，不同的存儲引擎在細節上面有很大不同，如InnoDB支持事務，MyISAM不支持。將執行結果返回給客戶端

• 客戶端接收到查詢結果，完成這次查詢請求。

詳細講解

1. 客戶端發送 SQL 查詢語句到 MySQL 服務器及連接器處理

   • 連接建立：
     • 客戶端通過網絡發送 SQL 查詢語句到 MySQL 服務器指定的端口（通常是 3306）。服務器的連接器首先會處理這個連接請求。它會驗證客戶端提供的連接參數，包括主機地址、端口、用戶名和密碼。例如，當使用 MySQL 命令行客戶端連接時，用戶輸入mysql -h [服務器地址] -u [用戶名] -p，然后輸入密碼，服務器會根據配置文件（如user表中的用戶記錄）來檢查用戶名和密碼是否匹配。
   • 權限獲取：
     • 一旦連接通過驗證，連接器會根據用戶賬戶的權限設置來確定該用戶對數據庫的操作權限。這些權限包括對特定數據庫、表、列的讀取、寫入、修改等權限。例如，一個用戶可能被授予對某個數據庫中某些表的SELECT和INSERT權限，但沒有DELETE權限。權限信息存儲在 MySQL 的系統數據庫（如mysql數據庫中的相關權限表）中，連接器會查詢這些表來獲取用戶的權限范圍。
   • 連接管理：
     • 連接器會維護連接的狀態，包括跟蹤連接是否處于活動狀態、是否超時等。它還會管理連接池（如果配置了連接池），在有多個客戶端連接時，合理地分配和復用連接資源。例如，當一個客戶端長時間沒有發送任何請求時，連接器可能會根據服務器的配置（如wait_timeout參數）來判斷是否關閉該連接，以釋放資源。

2. SQL 語句解析（解析器處理）

   • 詞法分析：
     • 解析器首先進行詞法分析，它會將 SQL 語句分解為一個個的單詞（也稱為詞法單元）。例如，對于語句SELECT * FROM users WHERE age > 30，解析器會將其分解為SELECT、*、FROM、users、WHERE、age、>、30等詞法單元。這些詞法單元是 SQL 語法的基本組成部分，解析器會根據預定義的詞法規則（如關鍵字、標識符、常量、操作符等的規則）來識別它們。
   • 語法分析和語法樹生成：
     • 在完成詞法分析后，解析器會進行語法分析。它會根據 SQL 的語法規則來檢查這些詞法單元的組合是否合法。解析器會使用一種類似于狀態機的機制，不斷循環遍歷這些關鍵字和符號，按照語法規則構建一個語法樹。例如，在上述語句中，解析器會識別出SELECT是查詢操作的關鍵字，*表示選擇所有列，FROM指定了要查詢的表是users，WHERE引導了篩選條件。它會將這些信息構建成一個層次結構的語法樹，其中SELECT節點是根節點，它的子節點包括*和一個表示FROM子句的節點，FROM子句節點的子節點是users，還會有一個表示WHERE子句的分支，其下包含age、>和30等節點。在這個過程中，解析器還會檢查引用的數據庫、表和列是否存在。例如，它會查詢數據庫的元數據（存儲在系統表中）來驗證users表是否存在，以及age列是否是users表中的列。同時，也會進行名稱解析和權限驗證。如果用戶沒有對users表的SELECT權限，解析器會返回權限錯誤。

3. 優化器確定執行計劃

   • 索引評估：
     • 優化器會首先查看 SQL 語句中涉及的表和列是否有可用的索引。例如，對于查詢SELECT * FROM users WHERE username = 'john'，如果username列有索引，優化器會考慮使用該索引來加速查詢。它會評估索引的類型（如 B - Tree 索引、哈希索引等）、索引的選擇性（即通過索引能夠過濾掉多少數據）等因素。例如，一個索引的選擇性高意味著通過該索引能夠快速定位到少量滿足條件的數據行，優化器會更傾向于使用這樣的索引。
   • 表連接順序確定：
     • 當 SQL 語句涉及多個表的連接時，優化器會決定表之間的連接順序。例如，對于連接查詢SELECT * FROM table1 JOIN table2 ON table1.id = table2.id JOIN table3 ON table2.id = table3.id，優化器會根據表的大小（通過統計信息，如每個表的行數）、索引情況等來判斷先連接哪兩個表更高效。如果table1是一個小表，并且id列有索引，而table2和table3相對較大，優化器可能會先將table1和table2進行連接，然后再連接table3，以減少中間結果集的大小，提高查詢效率。
   • 執行計劃生成和評估：
     • 優化器會生成多種可能的執行計劃，并根據成本模型來評估每個執行計劃的成本。成本模型會考慮多種因素，如磁盤 I/O 操作次數、CPU 計算量、內存使用等。例如，一個需要進行大量磁盤掃描的執行計劃成本可能較高，而一個能夠充分利用索引、減少磁盤 I/O 的執行計劃成本較低。優化器會選擇成本最低的執行計劃作為最終的執行計劃，這個計劃將指導執行器如何進行數據的讀寫操作。

4. 執行器調用存儲引擎 API 進行數據讀寫（使用鎖）

   • 讀寫操作啟動：
     • 執行器根據優化器確定的執行計劃，開始調用存儲引擎的 API 進行數據的讀寫操作。例如，對于查詢操作，執行器會按照計劃從存儲引擎中讀取數據。如果執行計劃是進行全表掃描，執行器會通過存儲引擎的接口逐行讀取表中的數據；如果是利用索引進行查詢，執行器會通過索引接口快速定位到滿足條件的數據行。
   • 鎖的使用：
     • 在進行數據讀寫時，執行器會根據 SQL 語句的性質（如是否是事務中的操作、是否涉及并發訪問等）和存儲引擎的鎖機制來使用鎖。例如，在 InnoDB 存儲引擎中，如果執行一個SELECT... FOR UPDATE語句，執行器會對查詢結果集對應的行加上排他鎖（X 鎖），以防止其他事務同時修改這些行。對于并發的事務，鎖可以保證數據的一致性和完整性。不同的存儲引擎有不同的鎖機制，執行器會根據存儲引擎的規則來正確地獲取和釋放鎖。

5. 引擎層 MVCC 回滾相關操作（undolog 版本鏈）

   • 版本鏈創建：
     • 在 InnoDB 存儲引擎中，為了支持多版本并發控制（MVCC），會為每一行數據創建一個版本鏈。當對一行數據進行修改時，存儲引擎不會直接覆蓋原來的數據，而是會將修改前的數據作為一個舊版本，通過一個鏈表結構（版本鏈）將舊版本和新版本連接起來。例如，最初有一行數據(id = 1, value = 'A')，當將value修改為'B'時，會在存儲引擎中保留舊版本(id = 1, value = 'A')，并創建一個新版本(id = 1, value = 'B')，這兩個版本通過版本鏈連接。
   • MVCC 和回滾操作：
     • MVCC 允許不同事務在不同時間點看到同一行數據的不同版本。在事務執行過程中，如果需要回滾操作，存儲引擎可以根據 undolog 版本鏈找到事務修改之前的數據版本，將數據恢復到事務開始之前的狀態。例如，一個事務讀取了(id = 1, value = 'A')，然后另一個事務將value修改為'B'，如果第一個事務設置了隔離級別為可重復讀（REPEATABLE READ），它仍然可以看到(id = 1, value = 'A')這個版本的數據。如果第二個事務需要回滾，存儲引擎可以通過 undolog 版本鏈將數據恢復為(id = 1, value = 'A')。

6. 日志相關操作（redolog 和 binlog）

   • redolog 記錄：
     • redolog 用于記錄數據庫的物理修改操作，它是一種基于磁盤的日志。在執行對數據的修改操作（如插入、更新、刪除）時，存儲引擎會先將修改操作記錄到 redolog 中。例如，當執行UPDATE users SET age = 31 WHERE id = 1時，存儲引擎會將這個修改操作的相關信息（如修改的表、列、新值和舊值等）記錄到 redolog 中。redolog 采用了預寫式日志（WAL）的機制，即先寫日志，后修改數據，這樣可以保證在數據庫發生故障（如突然斷電、系統崩潰等）時，通過 redolog 來恢復尚未完成的事務，保證數據的持久性。
   • binlog 記錄（如果開啟）：
     • binlog 主要用于數據庫的主從復制和數據恢復等用途。如果開啟了 binlog（通過配置參數），在執行 SQL 語句時，存儲引擎會將 SQL 語句（以事件的形式）記錄到 binlog 中。例如，在主從復制環境中，主數據庫上的每一個修改操作都會被記錄到 binlog 中，然后從數據庫會通過讀取主數據庫的 binlog 來同步數據。binlog 的記錄格式有多種（如 STATEMENT、ROW、MIXED），不同的格式記錄的內容和方式略有不同。例如，ROW 格式會記錄每一行數據的詳細修改情況，而 STATEMENT 格式會記錄執行的 SQL 語句。
   • 二階段提交保證數據一致性：
     • 在事務提交時，MySQL 會使用二階段提交（2PC）來保證 redolog 和 binlog 的一致性。首先，存儲引擎會準備好提交事務，將事務的狀態設置為可以提交，這個過程會涉及到將 redolog 從內存刷到磁盤（部分情況下）等操作。然后，在確保 redolog 已經準備好提交后，才會將 binlog 也刷到磁盤。只有當 redolog 和 binlog 都成功寫入磁盤后，事務才真正提交成功。這樣可以保證在數據庫恢復或者主從復制過程中，數據的一致性和完整性。

7. 提交事務及返回結果給客戶端

   • 事務提交：
     • 當所有的數據讀寫操作完成，日志也按照要求記錄后，執行器會提交事務。在提交事務過程中，會根據前面提到的二階段提交機制，確保數據的一致性。如果在提交過程中出現問題（如磁盤滿、網絡故障等），事務可能會根據日志進行回滾，以保證數據的完整性。
   • 結果返回：
     • 對于查詢操作，存儲引擎將查詢到的數據結果集返回給執行器，執行器再將結果返回給服務器的連接器，最后由連接器將結果發送給客戶端。客戶端接收到查詢結果后，可以根據自己的需求進行處理，例如在命令行中顯示結果、在圖形化客戶端中以表格形式展示結果等。對于非查詢操作（如插入、更新、刪除），如果操作成功，會返回相應的成功信息（如受影響的行數）給客戶端，完成這次查詢請求。