01_MySQL基礎架構

MySQL 45 講Note：

課程專欄名稱：《MySQL實戰45講》課程

筆記參考：MYSQL45 講

01_基礎架構：一條SQL查詢語句是如何執行的？

一條SQL查詢是如何執行的

先看一下下麵這個圖

​​

我們首先理解一下 Mysql 的基礎架構，理解如果執行一條簡單的查詢語句，Mysql 進行了哪些操作。

在 MySql 的基礎架構種，他分為了Service 層和存儲引擎；

其中存儲引擎負責存儲和提取數據，Service 層包含了連接器，查詢緩存，優化器和執行層等，蘊含了Mysql 大多數的核心功能。

接下來我們先來瞭解一下他們的基礎概念。

‍

存儲引擎

Mysql 常見的存儲引擎有 InnoDB、MyISAM、Memory 等多種，最常用的存儲引擎是​ InnoDB，它從 MySQL 5.5.5 版本開始成為了預設存儲引擎。

‍

連接器

我們使用客戶端和 Mysql 進行連接的時候，Mysql 連接器就負責管理連接，建立連接，獲取許可權，維持連接

具體的一個連接操作：

連接命令中的 mysql 是客戶端工具，用來跟服務端建立連接。在完成經典的 TCP 握手後，連接器就要開始認證你的身份，這個時候用的就是你輸入的用戶名和密碼。

• 如果用戶名或密碼不對，你就會收到一個"Access denied for user"的錯誤，然後客戶端程式結束執行。
• 如果用戶名密碼認證通過，連接器會到許可權表裡面查出你擁有的許可權。之後，這個連接裡面的許可權判斷邏輯，都將依賴於此時讀到的許可權。

這就意味著，一個用戶成功建立連接後，即使你用管理員賬號對這個用戶的許可權做了修改，也不會影響已經存在連接的許可權。修改完成後，只有再新建的連接才會使用新的許可權設置。

客戶端如果太長時間沒動靜，連接器就會自動將它斷開。這個時間是由參數 wait_timeout 控制的，預設值是 8 小時。

建立連接的過程通常是比較複雜的，所以我建議你在使用中要儘量減少建立連接的動作，也就是儘量使用長連接****。

但如果全部使用長連接的話，會出現一個問題：

MySQL 建立連接的時候，每個客戶端連接都會有一個對應的連接對象（Connection Object），這個連接對象會維護連接過程中的一些狀態信息，比如事務狀態、鎖信息、臨時表等。同時，連接對象也會維護一些緩存信息，比如查詢結果緩存、語句緩存等。這些緩存信息會占用一定的記憶體空間。

當MySQL執行查詢語句時，會為查詢分配一些記憶體空間，用於存儲臨時表、排序緩存、哈希表等中間結果。這些記憶體空間是從連接對象中分配的，因此被稱為連接記憶體（Connection Memory）。這些記憶體空間只有在連接斷開的時候才會被釋放，因為它們是綁定在連接對象上的，只有當連接對象被銷毀時，這些記憶體空間才會被系統回收。

如果使用長連接，那麼連接對象會一直存在，連接記憶體也就會一直被占用。如果多個長連接同時存在，那麼這些連接對象和連接記憶體就會累積，導致MySQL占用的記憶體空間越來越大。因此，長連接也需要註意記憶體占用問題，需要在代碼中合理管理連接對象和連接記憶體的生命周期，避免記憶體泄漏和OOM問題的發生。

所以如果長連接累積下來，可能導致記憶體占用太大，被系統強行殺掉（OOM），從現象看就是 MySQL 異常重啟了。

‍

怎麼解決這個問題呢？你可以考慮以下兩種方案。

1. 定期斷開長連接。使用一段時間，或者程式裡面判斷執行過一個占用記憶體的大查詢後，斷開連接，之後要查詢再重連****。
2. 如果你用的是 MySQL 5.7 或更新版本，可以在每次執行一個比較大的操作後，通過執行 mysql_reset_connection 來重新初始化連接資源。這個過程不需要重連和重新做許可權驗證，但是​會將連接恢復到剛剛創建完時的狀態。

‍

‍

查詢緩存

MySQL 拿到一個查詢請求後，會先到查詢緩存看看，之前是不是執行過這條語句。

之前執行過的語句及其結果可能會以 key-value 對的形式，被直接緩存在記憶體中。key 是查詢的語句，value 是查詢的結果。

如果你的查詢能夠直接在這個緩存中找到 key，那麼這個 value 就會被直接返回給客戶端。

但是大多數情況下建議不要使用查詢緩存，查詢緩存往往弊大於利。

查詢緩存的失效非常頻繁，只要有對一個表的更新，這個表上所有的查詢緩存都會被清空。

查詢緩存是以查詢語句作為 key，如果查詢的數據發生了變化，那麼查詢語句所對應的結果也會發生變化，即使查詢語句不變。

因此，當數據發生變化時，MySQL會自動使查詢緩存失效，下次查詢時會重新執行查詢語句並緩存新的結果。這也是為什麼有時候查詢緩存機制反而會降低性能的原因，因為每次數據發生變化時都需要重新查詢並緩存結果，而且查詢緩存本身也會占用一定的記憶體空間。

除非你的業務就是有一張靜態表，很長時間才會更新一次。

比如，一個系統配置表，那這張表上的查詢才適合使用查詢緩存。

MySQL 也提供了這種“按需使用”的方式。可以將參數 query_cache_type 設置成 DEMAND，這樣對於預設的 SQL 語句都不使用查詢緩存。

而對於你確定要使用查詢緩存的語句，可以用 SQL_CACHE 顯式指定，像下麵這個語句一樣：

mysql> select SQL_CACHE * from T where ID=10；

需要註意的是，MySQL 8.0 版本直接將查詢緩存的整塊功能刪掉了，也就是說 8.0 開始徹底沒有這個功能了。

‍

分析器

如果沒有命中查詢緩存，就要開始真正執行語句了。首先語句經歷的第一步就是這個分析器。

對SQL語句進行解析，Mysql 才能知道你要做什麼。首先分析器先會做“詞法分析”。你輸入的是由多個字元串和空格組成的一條 SQL 語句，MySQL 需要識別出裡面的字元串分別是什麼，代表什麼。

MySQL 從你輸入的"select"這個關鍵字識別出來，這是一個查詢語句。它也要把字元串“T”識別成“表名 T”，把字元串“ID”識別成“列 ID”。

做完了這些識別以後，就要做“語法分析”。根據詞法分析的結果，語法分析器會根據語法規則，判斷你輸入的這個 SQL 語句是否滿足 MySQL 語法。

如果你的語句不對，就會收到“You have an error in your SQL syntax”的錯誤提醒，比如下麵這個語句 select 少打了開頭的字母“s”。

mysql> elect * from t where 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 'elect * from t where ID=1' at line 1

一般語法錯誤會提示第一個出現錯誤的位置，所以你要關註的是緊接“use near”的內容。

‍

優化器

經過了分析器（詞法分析和語法分析），MySQL 就知道你要做什麼了。在開始執行之前，還要先經過優化器的處理。

優化器是在表裡面有多個索引的時候，決定使用哪個索引；或者在一個語句有多表關聯（join）的時候，決定各個表的連接順序。

比如你執行下麵這樣的語句，這個語句是執行兩個表的 join：

mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;

• 既可以先從表 t1 裡面取出 c=10 的記錄的 ID 值，再根據 ID 值關聯到表 t2，再判斷 t2 裡面 d 的值是否等於 20。
• 也可以先從表 t2 裡面取出 d=20 的記錄的 ID 值，再根據 ID 值關聯到 t1，再判斷 t1 裡面 c 的值是否等於 10。

這兩種執行方法的邏輯結果是一樣的，但是執行的效率會有不同，而優化器的作用就是決定選擇使用哪一個方案。

‍

執行器

MySQL 通過分析器知道了你要做什麼，通過優化器知道了該怎麼做，於是就進入了執行器階段，開始執行語句。

執行器的主要作用是將SQL語句轉換為操作存儲引擎的指令，並將結果返回給客戶端。

在執行器中，會根據SQL語句的類型（SELECT、INSERT、UPDATE、DELETE等）和表的引擎類型，調用相應的存儲引擎介面來執行操作。

同時，在執行查詢SQL的時候，會先判斷一下你對這個表 T 有沒有執行查詢的許可權，如果沒有，就會返回沒有許可權的錯誤(在工程實現上，如果命中查詢緩存，會在查詢緩存返回結果的時候，做許可權驗證。查詢也會在優化器之前調用 precheck 驗證許可權)。

mysql> select * from T where ID=10;
 
ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

precheck 驗證許可權 和 執行器驗證許可權的區別：

• 預驗證是在執行查詢之前進行的，主要是為了避免無效查詢的開銷。在預驗證中，MySQL會檢查當前用戶是否具有執行該查詢的許可權，如果沒有，就可以直接返回沒有許可權的錯誤，避免了執行查詢的開銷。預驗證只是簡單地檢查當前用戶是否具有執行該查詢的許可權，不會涉及到表的引擎類型、表的結構等因素。
• 執行器中的許可權驗證是在執行查詢時進行的，主要是為了確保操作的合法性。在執行器中，MySQL會根據SQL語句的類型和表的引擎類型，調用相應的存儲引擎介面來執行操作。在執行操作之前，MySQL會進行許可權驗證，檢查當前用戶是否具有執行該操作的許可權，以及表的引擎類型、表的結構等因素是否符合要求。這樣可以確保操作的合法性，避免了惡意操作或者誤操作。
• 因此，預驗證和執行器中的許可權驗證雖然都是為了驗證當前用戶是否具有執行查詢的許可權，但它們的目的和方式是不同的。預驗證主要是為了避免無效查詢的開銷，而執行器中的許可權驗證主要是為了確保操作的合法性。

‍

‍