MySQL架構設計實例分析

1. MySQL整體架構

由圖中可以看到MySQL架構主要分為Server層和儲存引擎層。

Server層又分為連接器、快取、分析器、最佳化器、執行器。所有跨儲存引擎的功能都在這層實現，例如：函數、預存程序、觸發器、視圖等。

儲存引擎是可插拔式的，常見的儲存引擎有MyISAM、InnoDB、Memory等，MySQL5.5之前預設的是MyISAM，之後預設的是InnoDB。

2. 連接器

連接器主要用來管理客戶端的連線和使用者認證。

TCP協定被用於客戶端和伺服器端的連接，連接透過進行TCP握手建立，並在此基礎上，連接器開始進行身份驗證。

> mysql -hlocalhost -P3306 -uroot -p

如果認證失敗，就會出現錯誤 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)。

可以透過 show processlist 指令查看系統所有連線的資訊：

其中Commond列表示連線狀態，Daemon表示後台程式，Query表示查詢，Sleep表示空閒連線。

3. 查詢快取

客戶端請求的查詢不會直接存取儲存引擎，而是先檢查快取中是否存在結果。如果結果已經緩存，直接回傳；否則，重新執行查詢過程，查詢結束後再將結果快取。

失效快取將在資料表發生變更時清空，如執行insert、update、delete、alter操作。

對於頻繁變更的資料表來說，快取命中率很低。由於使用快取會降低讀寫效能，因此在 MySQL 8.0 之後的版本中，快取模組會被移除。

可以透過下面指令查看是否開啟了快取：

#4. 分析器

#分析器主要對SQL語句進行詞法分析與語法分析。

對於MySQL的關鍵字和每個字詞的意義，需要進行詞法分析。接著進行語法分析，偵測SQL語句是否符合MySQL語法要求。

MySQL透過辨識字串中列名、表名、where、select/update/insert 等MySQL關鍵字，在根據語法規則判斷sql是否滿足語法，最終會產生一個抽象語法樹(AST) 。

例如：SQL語句中少寫個where關鍵字，就會提示錯誤。

mysql> select * from user id=1;
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 '=1' at line 1

5. 最佳化器

在真正執行SQL語句之前，還需要經過最佳化器處理。

我們熟知的執行計劃（Explain）就是優化器產生的。

優化器主要有兩個作用：邏輯最佳化和物理最佳化。

邏輯優化主要進行等價謂詞重寫、條件化簡、子查詢消除、連接消除、語義優化、分組合併、選擇下推、索引優化查詢、表查詢替換視圖查詢、Union替換or操作等。

透過貪心演算法和代價估算模型，物理最佳化的主要目的是估算每種執行方式的代價。並使用索引優化表連接，最終產生查詢執行計劃。

附上MySQL優化器架構圖，可以清楚的看到最佳化過程：

6.執行器

在最佳化器最佳化完SQL，並且產生了執行計畫後，就會把執行計畫傳遞給執行器。

執行器呼叫儲存引擎接口，真正的執行SQL查詢。 SQL語句的執行在取得儲存引擎傳回的查詢結果，然後將其傳回給客戶端後結束。

以上是MySQL架構設計實例分析的詳細內容。更多資訊請關注PHP中文網其他相關文章！