MySQL體系結構概覽

作者：IT王小二

部落格：https://itwxe.com

一條 SQL 語句在 MySQL 中怎麼執行的呢，這篇就來認識下 MySQL 的各個元件的作用。

一、結構元件

首先需要 MySQL 安裝的客官看這兩篇，小二示範使用的是 Docker 的安裝方式：

• Linux(CentOS7)下二進制安裝MySQL5.7.26
• Docker搭建MySQL并挂載資料

先上個圖，小二自己畫的，絕對的高清無碼，嘿嘿。不足之處歡迎指正哈。

1. 企業管理服務和工具

系統管理和控制工具，例如 MySQL 備份恢複、MySQL 複制、MySQL 叢集等工具。

2. 連接配接池

負責監聽對用戶端向 MySQL Server 端的各種請求，建立連接配接、權限校驗、維持和管理連接配接，通信方式是半雙工模式，資料可以雙向傳輸，但不能同時傳輸。

• 單工：資料單向發送。
• 半雙工：資料雙向傳輸，但不能同時傳輸。
• 全雙工：資料雙向傳輸，可以同時傳輸。

那麼 MySQL 是怎麼儲存連接配接得嘞？

每個成功連接配接 MySQL Server 的用戶端請求都會建立或配置設定一個線程，在記憶體中配置設定一塊空間存儲對應的會話資訊，其中包含權限等資訊，該線程負責用戶端與 MySQL Server 端的通信，接收用戶端發送的指令，傳遞服務端的結果資訊等。

使用者的權限表在系統表空間的 mysql 庫中的 user 表中，這就意味着，一個使用者成功建立連接配接後，即使你用管理者賬号對這個使用者的權限做了修改，也不會影響已經存在連接配接的權限。修改完成後，隻有再建立的連接配接才會使用新的權限設定。

一些有點用的指令。

MySQL 允許最大的連接配接數

show variables like '%max_connections%';
           

這個值可以在

my.cnf

檔案中配置，Docker 安裝完 MySQL 版本為

5.7.36

預設值為 151 個最大允許連接配接數。

項目中可能會遇到

MySQL: ERROR 1040: Too many connections

的異常情況，造成這種情況的原因有兩個。

• 一種是 MySQL 配置檔案中

  max_connections

  值過小，可以在配置檔案

  my.cnf

  中添加

  max_connections

  參數增大最大連接配接數，例如

  max_connections = 500

  。
• 一種是通路量過高，MySQL 伺服器抗不住，這個時候就要考慮增加從伺服器分散讀壓力。

查詢目前 MySQL 伺服器接收所有的連接配接資訊

show processlist;
           

State 狀态常見的就是 Sleep 和 Query，詳解自行度娘、谷哥，一般也沒啥人看。

• Sleep：線程正在等待用戶端發送新的請求。
• Query：線程正在執行查詢或者正在将結果發送給用戶端。

資料庫閑置連接配接逾時時間

#非互動式逾時時間，如 JDBC 程式
show global variables like 'wait_timeout'; 
#互動式逾時時間，如資料庫工具
show global variables like 'interactive_timeout'; 
           

可以看到逾時時間都是預設8小時，即當用戶端狀态連接配接後為 Sleep 的時候，如果8小時沒有收到請求那麼就會斷開連接配接。

MySQL狀态分析

show global status like 'Thread%';
           

• Threads_cached：伺服器端緩存連接配接
• Threads_connected：目前打開的連接配接數
• Threads_created：建立的線程數
• Threads_running：正在運作的線程

3. SQL接口

負責接收使用者 SQL 指令，如 DML，DDL 和存儲過程等，并将指令發送到其他部分，并接收其他部分傳回的結果資料，将結果資料傳回給用戶端。

關于這個元件小二也有點懵，那麼具體分發到哪些元件上面去了，翻了很多資料基本就是一句話解釋的，以後翻翻高性能 MySQL 看看有較長的描述沒~

4. 查詢緩存

首先需要說明：在 MySQL8.0 中已經删除了查詢緩存，MySQL5.7 中仍然存在查詢緩存。

如果開啟了 MySQL 緩存的話，成功擷取一個 MySQL 連接配接後，會先到查詢緩存看看，之前是不是執行過這條語句。

如果之前執行過，那麼這條語句及其結果可能會以 key-value 對的形式，被直接緩存在記憶體中。key 是查詢的語句，value 是查詢的結果。如果查詢能夠直接在這個緩存中找到 key，那麼這個 value 就會被直接傳回給用戶端。

如果語句不在查詢緩存中，就會繼續後面的執行階段，通過存儲引擎去查詢。執行完成後，執行結果會被存入查詢緩存中。

如果命中緩存的話那麼速度确實會很快，但是...但是...MySQL 的這個緩存功能往往卻比較雞肋，為什麼這麼說呢？

涉及到緩存，那當然就有和源資料保持一緻性的問題，或者說同步的問題。

那麼想一想，MySQL 如果要保持查詢查詢緩存資料結果的一緻性，同時那個表經常性的更新資料，那麼每更新一條資料，MySQL 為了保持一緻性就要把該表所有的 key 全部查詢一次，那麼對于一個頻繁更新的表來說那麼 MySQL 的壓力就太大了。

是以...，MySQL 選擇了最簡單粗暴的方式，如果該表一更新資料，就從查詢緩存删除該表所有的 key，即從查詢緩存删除不該表相關的所有查詢語句緩存。

那麼既然說是雞肋，那當然還是有一點點用的，隻要使用得當，那麼什麼場景下查詢緩存可以發揮那麼一點點作用呢？

如果說在項目中不想引入 Redis，那麼這個查詢緩存能不能在某些方面加快一些查詢速度呢，當然是可以的。

既然頻繁更新的表不适用查詢緩存，那麼我們開發中幾個月才會更新一次的表不就正好合用嗎，例如常見的系統配置表、字典表等，同時 MySQL 也正好提供了按需使用的政策方式。

怎麼按需使用呢？

首先把查詢緩存開啟按需配置，查詢

show global variables like "%query_cache_type%";

如果結果為

OFF

，那麼就需要在 MySQL 配置檔案

my.cnf

中配置如下參數後重新啟動 MySQL 即可。

# 查詢緩存開啟，OFF 關閉，ON 開啟
query_cache_type = ON

# 緩存政策，0代表關閉查詢緩存 OFF，1代表開啟 ON，2代表 DEMAND，DEMAND代表當sql語句中有SQL_CACHE 關鍵詞時才緩存
query_cache_type=2
           

如果你和小二一樣的的方式安裝的 MySQL，docker 安裝預設沒有

my.cnf

檔案的，那麼需要自己在 docker 映射目錄

/itwxe/dockerData/mysql/conf

下面建立檔案

my.cnf

，添加如下内容使用

docker restart mysql

重新開機容器即可開啟查詢緩存。

[mysqld]
query_cache_type = ON
query_cache_type = 2
           

添加

my.cnf

後重新開機容器，登入 MySQL 後可以看到查詢緩存已經開起來了。

那麼接下來隻需要在需要緩存結果的查詢語句上面加上一個

SQL_CACHE

顯示指定即可，例如：

SELECT SQL_CACHE * FROM test_innodb WHERE id = 6；
           

檢視一下緩存的運作資訊。

show status like'%Qcache%';
           

結果說明：

• Qcache_free_blocks：表示緩存中相鄰記憶體塊的個數。數目大說明可能有碎片。
• Qcache_free_memory：查詢緩存的記憶體大小，通過這個參數可以很清晰的知道目前系統的查詢記憶體是否夠用，是多了，還是不夠用，可以根據實際情況做出調整。預設從圖中可以看到預設大小為 1Mb(圖中以b為機關)，可以在

  my.cnf

  中通過

  query_cache_size = 20M

  指定緩存大小。
• Qcache_hits：表示有多少次命中緩存。
• Qcache_inserts：表示多少次未命中然後插入，意思是新的查詢 SQL 請求在緩存中未找到，需要執行查詢處理，執行查詢處理後把結果 insert 到查詢緩存中。
• Qcache_lowmem_prunes：該參數記錄有多少條查詢因為記憶體不足而被移除出查詢緩存，通過這個值可以适當的調整緩存大小。
• Qcache_not_cached：表示因為 query_cache_type 的設定而沒有被緩存的查詢數量。
• Qcache_queries_in_cache：目前緩存中緩存的查詢數量。
• Qcache_total_blocks：緩存中塊的數量。

各個參數和緩存的效果可以多查詢幾遍試試就明白啦，雞肋還是有一點點用的，哈哈，當然你要是已經有了 Redis 等緩存中間件就不需要查詢緩存啦，同時 MySQL8.0 中也已經移除查詢緩存功能。

5. 解析器

負責将接收到的 SQL 指令解析和驗證。解析器主要功能：

• 将 SQL 語句分解成資料結構，并将這個結構傳遞到後續步驟，以後 SQL 語句的傳遞和處理就是基于這個結構的。說人話就是将我們寫的 SQL 語句分解成 MySQL 認識的文法往下傳遞。
• 如果在分解構成中遇到錯誤，那麼就說明這個 sql 語句是不合理的。說人話就是看看我們寫的 SQL 語句有沒有文法錯誤。

6. 查詢優化器

負責 SQL 語句在查詢之前對查詢進行優化，這個過程會使用 optimizer trace 優化查詢 SQL，然後計算各種可以使用的索引和全表掃描的查詢成本相比較，選擇最優的查詢方式。

optimizer trace 工具會在後面的文章中說到，MySQL 到底通過什麼規則計算的查詢成本的，為什麼有時候明明有可以使用的索引最後還是走的全表掃描，在後面章節小二會提到哦，歡迎各位客官關注。

7. 可拔插存儲引擎

存儲引擎就是如何管理操作資料(存儲資料、更新資料、查詢資料等)的一種方法，當然在 MySQL 中。而可拔插就可以了解為 MySQL 提供了一個接口，隻要遵循規則即可以自定義實作存儲引擎，Java中接口與實作類的關系。

8. 檔案系統

檔案系統主要是将資料庫的資料存儲在作業系統的檔案系統之上，并完成與存儲引擎的互動。例如資料庫檔案，表檔案和各種日志檔案(bin log、redo log、undo log等)。

二、一條SQL語句的執行過程概覽

相信仔細看了查詢緩存的都不會問小二為啥新增、修改、删除都要走查詢緩存了吧，嘿嘿。總覽到此結束，接下來下一篇就講講 Explain 執行計劃。

都讀到這裡了，來個 點贊、評論、關注、收藏 吧！