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會進行權限驗證，檢查當前用戶是否具有執行該操作的權限，以及表的引擎型別、表的結構等因素是否符合要求，這樣可以確保操作的合法性，避免了惡意操作或者誤操作，
• 因此，預驗證和執行器中的權限驗證雖然都是為了驗證當前用戶是否具有執行查詢的權限，但它們的目的和方式是不同的，預驗證主要是為了避免無效查詢的開銷，而執行器中的權限驗證主要是為了確保操作的合法性，

?

?

標籤：其他

上一篇：150萬學術名詞中英對照字典ACCESS資料庫

下一篇：返回列表