MySQL邏輯架構

一、概述

深入學習Mysql，從概覽Mysql邏輯架構開始。

首先來看一下MySQL的邏輯架構圖：

MySQL最重要、最與眾不同的特性就是它的存儲引擎架構，這種架構將：查詢處理、其他系統任務、數據的存儲與提取 三部分分離。所以，帶來的好處就是可以在使用時根據性能、特性，以及其他需求來選擇數據存儲方式。

存儲引擎架構分為三層，自上而下，分為第一層：連接層；第二層：服務層；第三層：引擎層。

第一層：連接層：

MySQL的最上層是連接服務，引入了線程池的概念，允許多臺客戶端連接。主要工作是：連接處理、授權認證、安全防護等。

連接層為通過安全認證的接入用戶提供線程，同樣，在該層上可以實現基于SSL 的安全連接。

第二層：服務層：

服務層用于處理核心服務，如標準的SQL接口、查詢解析、SQL優化和統計、全局的和引擎依賴的緩存與緩沖器等等。所有的與存儲引擎無關的工作，如過程、函數等，都會在這一層來處理。在該層上，服務器會解析查詢并創建相應的內部解析樹，并對其完成優化，如確定查詢表的順序，是否利用索引等，最后生成相關的執行操作。如果是SELECT 語句，服務器還會查詢內部的緩存。如果緩存空間足夠大，這樣在解決大量讀操作的環境中能夠很好的提升系統的性能。

第三層：引擎層：

存儲引擎層，存儲引擎負責實際的MySQL數據的存儲與提取，服務器通過API 與 存儲引擎進行通信。不同的存儲引擎功能和特性有所不同，這樣可以根據實際需要有針對性的使用不同的存儲引擎。

二、連接器

當客戶端（應用）連接到MySQL服務器時，服務器需要對其進行認證。認證基于用戶名、原始主機信息和密碼。

除了基本認證之外，連接器還會進行一些線程的處理。

每個客戶端連接都會在服務器進程中擁有一個線程，這個連接的查詢只會在這個單獨的線程中執行，該線程只能輪流在某個CPU核心或者CPU中運行。服務器會負責緩存線程，因此不需要為每一個新建的連接創建或者銷毀線程。

連接器：負責跟客戶端建立連接 | 獲取權限 | 維持和管理連接。連接命令一般如下形式:

mysql -h$ip -P$port -u$user -p

通過如下語句可以查看當前連接的信息:

mysql> show processlist; +----+------+--------------------+-------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+--------------------+-------+---------+------+----------+------------------+ | 5 | root | 192.168.33.1:57721 | mysql | Sleep | 29 | | NULL | | 6 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+------+--------------------+-------+---------+------+----------+------------------+ 2 rows in set (0.00 sec)

其中"Sleep"代表現在系統里面有一個空閑連接.

客戶端如果太長時間不活動, 連接器就會自動斷開. 這個是由等待超時時間wait_timeout控制的,默認值是8小時.

數據庫長連接: 指連接成功后, 如果客戶端持續有請求, 則一直使用同一個連接.

數據庫短連接: 指每次執行完很少的幾次查詢后就斷開連接, 下次查詢重新再建立一個.

兩種連接實踐方案:

定期斷開長連接. 使用一段時間, 或者程序里面判斷執行過一個占用內存的大查詢后, 斷開連接, 之后要查詢再重連.

如果使用的MySQL版本是5.7或更新版本, 可以在執行一個比較大的操作后, 通過執行mysql_reset_connection來重新初始化連接資源. 這個過程不需要重連和重新做權限驗證, 但是會將連接恢復到剛剛創建完時的狀態。

三、查詢緩存

對于SELECT語句，在解析查詢之前，服務器會先檢查查詢緩存（Query Cache），如果能夠在其中找到對應的查詢，服務器就不必再執行查詢解析、優化和執行的整個過程，而是直接返回查詢緩存中的結果集。

但不推薦使用查詢緩存，即大多數情況下不建議使用查詢緩存。為什么呢？

原因：查詢緩存的失效非常頻繁，只要有對一個表的更新，這個表上所有的查詢緩存都會被清空。對于更新壓力大的數據庫來說，查詢緩存的命中率會非常低。除非你的業務就是有一張靜態表，很長時間才會更新一次。比如，一個系統配置表，那這張表上的查詢才適合使用查詢緩存。

好在MySQL也提供了這種“按需使用”的方式。可以將參數query_cache_type設置成DEMAND，這樣對于默認的SQL語句都不使用查詢緩存。而對于確定要使用查詢緩存的語句，可以用SQL_CACHE顯式指定，如下：

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

四、解析器

詞法分析

語法分析

如果緩存沒有命中的話，MySQL會對查詢語句進行解析。簡單說解析的作用將我們人能看懂的SQL解析成MySQ能識別的語言。

解析器先會做“詞法解析”。輸入的是由多個字符串和空格組成的一條SQL語句，MySQL需要識別出里面的字符串分別是什么，代表什么。

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

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

五、優化器

選擇合適的索引

決定各個表的連接順序

經過了解析器，MySQL知道我們要干什么。

接下來并不是直接執行，而是會在優化器這一層進行優化，優化器是個非常復雜的部件，它會幫我們去使用他自己認為的最好的方式去優化這條 SQL 語句，并生成一條條的執行計劃。

例如在表里面有多個索引的時候，決定使用哪個索引；或者在一個語句有多表關聯（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的執行。

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

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

如果有權限，就打開表繼續執行。打開表的時候，執行器就會根據表的引擎定義，去使用這個引擎提供的接口。

比如我們這個例子中的表T中，ID字段沒有索引，那么執行器的執行流程是這樣的：

調用InnoDB引擎接口取這個表的第一行，判斷ID值是不是10，如果不是則跳過，如果是則將這行存在結果集中；

調用引擎接口取“下一行”，重復相同的判斷邏輯，直到取到這個表的最后一行。

執行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結果集返回給客戶端。

至此，這個語句就執行完成了。

對于有索引的表，執行的邏輯也差不多。第一次調用的是“取滿足條件的第一行”這個接口，之后循環取“滿足條件的下一行”這個接口，這些接口都是引擎中已經定義好的。

七、問題1：MySQL的工作流程

最上層：客戶端連接

1、連接處理：客戶端同數據庫服務層建立TCP連接，連接管理模塊會建立連接，并請求一個連接線程。如果連接池中有空閑的連接線程，則分配給這個連接，如果沒有，在沒有超過最大連接數的情況下，創建新的連接線程負責這個客戶端。

２、授權認證：在真正的操作之前，還需要調用用戶模塊進行授權檢查，來驗證用戶是否有權限。通過后，方才提供服務，連接線程開始接收并處理來自客戶端的SQL語句。

第二層：核心服務

1、連接線程接收到SQL語句之后，將語句交給SQL語句解析模塊進行語法分析和語義分析。

2、如果是一個查詢語句，則可以先看查詢緩存中是否有結果，如果有結果可以直接返回給客戶端。

3、如果查詢緩存中沒有結果，就需要真的查詢數據庫引擎層了，于是發給SQL優化器，進行查詢的優化。如果是表變更，則分別交給insert、update、delete、create、alter處理模塊進行處理。

第三層：數據庫引擎層

1、打開表，如果需要的話獲取相應的鎖。

2、先查詢緩存頁中有沒有相應的數據，如果有則可以直接返回，如果沒有就要從磁盤上去讀取。

3、當在磁盤中找到相應的數據之后，則會加載到緩存中來，從而使得后面的查詢更加高效，由于內存有限，多采用變通的LRU表來管理緩存頁，保證緩存的都是經常訪問的數據。

最后，獲取數據后返回給客戶端，關閉連接，釋放連接線程。

八、問題2

如果表 T 中沒有字段 k，而你執行了這個語句 select * from T where k=1, 那肯定是會報“不存在這個列”的錯誤：“Unknown column ‘k’ in ‘where clause’”。你覺得這個錯誤是在哪個階段報出來的呢？

答案：分析器.

MySQL會解析查詢, 并創建內部數據結構(解析樹). 分析器會檢查: 數據表和數據列是否存在, 別名是否有歧義等. 所以此為分析器階段. ----- 參考自 01節課后討論@圈圈圓圓的回答.

參考資料

[1] MYSQL提升筆記（1）：MySQL邏輯架構

[2] MySQL 高級-MySQL邏輯架構圖簡介

[3]

MySQL SQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。