一條查詢sql的執行之路

 目錄

聯結器

查詢快取

分析器

優化器

執行器

小結

思考解惑

---

   資料庫增查改刪，我們平時用的很熟悉，執行一個sql語句，然後返回一個結果，但是這個過程到底是什麼樣的呢，下面來簡單看一下其中的過程？

 

  大體來說，MySql可以分為兩部分：Server層和儲存引擎。

    Server層包括聯結器／查詢快取／分析器／優化器／執行器等，涵蓋MySql的大多核心服務功能，以及所有的內建函式「日期，時間，數學和加密函式」等，所有跨儲存引擎的功能都在這一層實現，比如儲存過程／觸發器／試圖等。從圖中可以看到，不同的儲存引擎公用一個Server層，也就是從聯結器到執行器的部分。

    儲存引擎層負責資料的儲存和讀取。其架構模式是外掛式的，支援InnoDB/MyISAM/Memory等多個儲存引擎。現在最常用的是儲存引擎室InnoDB,它從MySQL5.5版本開始成為預設引擎。不同的儲存引擎表資料的儲存格式不一樣，支援的功能也不一樣。我們在建立表create table時，如果不指定引擎型別，預設使用的就是InnoDB.我們也可以通過指定儲存引擎來選擇引擎，比如在create table的時候採用engine=memory，來指定使用記憶體引擎建立表。

聯結器

   要使用資料庫，首先我們要連線上資料庫，這個時候我們就需要使用聯結器。聯結器負責跟客戶端建立連線／獲取連結／維持和管理連線。我們一般使用如下命令：

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

   輸入完命令，我們需要在互動框裡輸入密碼，這裡不建議直接用命令列模式直接將密碼寫在-p後面，可能導致密碼的洩漏，尤其是生產伺服器。連線命令中的mysql是客戶端工具，用來跟服務端建立連線。在完成tcp握手後，聯結器就開始認證你的身份，這個時候用的就是輸入的使用者名稱和密碼。

⚠️如果使用者名稱和密碼不對，我們經常會收到一個“Access deied for user”的錯誤，然後客戶端的程式執行結束。

如果密碼和使用者名稱通過驗證，聯結器會到許可權表裡查詢改使用者擁有的許可權。連線階段只是獲取了許可權資訊，真正進行查詢和操作的時候才會判斷“有沒有操作這個表的許可權”。拿到使用者資訊和許可權資訊。之後，這個連線裡設計到的許可權判斷都將依賴於此時讀到的許可權。這也就意味著，一個使用者成功建立連線後，即使你用管理員賬號對這個 使用者的許可權做了修改，也不會影響已經存在的連線許可權。修改完成後，只有重新新建連線才會使用新的許可權設定。

    連線完成後，我們可以通過如下命令來檢視連線的狀態：

show processlist;

其中通過command這個命令來檢視該連線是在做增刪改查，還是處於空閒sleep狀態；

如果太長時間沒動靜，聯結器就會自動將其斷開。這個時間是由引數wait_timeout來控制，預設是8小時。

⚠️當連線斷開時，我們經常會收到“Lost connection to MySql server during query.”這時候你要繼續，就需要重連，然後再執行請求了。

資料庫裡面的長連線和短連線：

• 長連線：是指連線成功後，如果客戶端持續有請求，則一直使用同一個連線；
• 短連線：是指每執行幾次命令後就會斷開連線，下次查詢再新建一個；

       建立連線的過程比較耗時和複雜，所以儘量使用長連線。在全部使用長連線後，你會發現記憶體長的特別快，這是因為Mysql在執行過程中臨時使用的記憶體管理師管理在連線物件裡面的。這些資源會在連線斷開的時候才釋放。所以長期積累下來，可能會導致記憶體佔用量過大，被系統強行殺掉OOM，從現象上來看就時mysql異常重啟了。

長連線導致記憶體增長解決的方案一般有兩個：
1、定期的斷開連線。使用一段時間後，或者程式裡面判斷執行過一個佔用記憶體大的查詢後，斷開連線，之後再重新查詢連線；
2、對於mysql5.7以後的版本，我們可以通過mysql_reset_connnection來重新初始化資源。這個過程不需要重連喝重新做許可權驗證，但是會將連線恢復到剛剛建立完的狀態，釋放掉積累的記憶體空間。

查詢快取

   完成第一步的建立連線，我們就可以執行sql命令select語句了。執行邏輯來到了第二步：查詢快取。

   Mysql拿到一個查詢請求後，先到快取裡查詢該命令是否執行過，因為之前執行過的命令會以key-value的形式儲存在記憶體中(當然這個是可以設定的)，key是查詢的語句，value是查詢的結果。如果查詢能夠直接在這個快取中找到這個key，這個value就會被直接返回給客戶端。如果不存在，這個命令就會繼續執行，當然其結果將會被儲存在快取中。可以看到，快取大大提高了查詢效率，省去了後面的分析-優化-執行的階段。

   但是查詢快取失效十分頻繁，只要有對一個表有更新，表上所有的查詢快取都會被清空。所以對於頻繁更新的資料庫來說不適合開啟查詢快取，因為每一次更新將導致快取全部無效。但是對於查詢頻繁，修改少的靜態表可以採取查詢快取的方式，例如系統配置表。當然了，我們也可以採用“按需使用”的方式，使用query_cache_type來設定是否開啟快取查詢，或者使用SQL_CACHE來顯示指定：

select SQL_CACHE * from USER_TABLE where ID = 6;

⚠️ mysql8.0以後的版本已經將快取查詢去掉了。

分析器

如果沒有命中快取，就需要開始執行語句了，首先mysql需要知道你要做什麼，因此需要對sql做解析。
詞法分析
   分析器線會做“詞法分析”。因為我們輸入的是由多個字串和空格組成的一條sql語句，mysql需要識別出裡面的字串分別是什麼，代表什麼。MySql從你輸入的select這個關鍵字識別出來，這是一個查詢語句。它也要把字串 “USER_TABLE”識別成”表名TABLE”, 把字串id識別成“列ID”.
語法分析
  做完這些識別以後，就要做“語法分析”。根據詞法分析的結果，語法分析會根據語法規則，判斷你輸入的這個sql語句是否滿足mysql語法。如果你輸入的sql不對，⚠️這個時候我們就會遇到我們經常看到的一個錯誤：“You hava an error in your SQL syntax. ”的錯誤提醒。例如下面的語句就會報錯。一般語法錯誤會提示第一個出現錯誤的位置，所以你要關注的是緊接“use near”的內容。
優化器。

優化器

  經過了分析器，Mysql就知道你要做什麼了，接下來mysql需要知道如何最優完成？在開始執行之前，還要進行優化器的處理。優化器是在表裡有多個索引的時候，決定用那個索引；或者在一個語句有多表關聯(join)的時候，決定各個表的連線順序。
比如你執行下面的這樣的語句，查詢使用者123的成功的訂單。假如這個語句是執行兩個表的join：

select * from USER_TABLE join ORDER_TABLE  where USER_TABLE.id=123 and ORDER_TABLE.statu=1;

上面的語句執行：
方案一：可以先從USER_TABLE中找出id=123的使用者，然後再根據id關聯到ORDER_TABLE找到狀態為1的成功的訂單；
方案二：也可以先找到狀態為1的成功的訂單，然後再根據id匹配123的使用者；
雖然最後執行的邏輯結果一樣，但是很明顯id是有索引的，statu一般很少加索引。另外id=123的使用者一幫很少，但是如果先執行statu=1的語句，其返回的結果會非常多，而優化器就是為了決定選擇那種方案而生的。
優化器階段完成後，這個語句的執行方案就確定下來了，然後進入執行器階段。

執行器

      MySql通過分析器知道了你要做什麼，通過優化器知道了該怎麼做，於是就進入了執行階段：
    開始執行的時候需要判斷一下你對這個表USER_TABLE有沒有執行查詢許可權，如果沒有，就會返回沒有許可權的錯誤。如果是查詢快取，如果命中快取，在返回結果的時候，做許可權驗證。查詢也會在優化器之前呼叫precheck來驗證許可權。

mysql> select * from USER_TABLE where ID=6;
ERROR 5345 (5356767): SELECT command denied to user ‘king’@‘localhost' for table ‘USER_TABLE’

   如果有許可權，就開啟表繼續執行。開啟表的時候，執行器就會根據表引擎的定義，去使用這個引擎提供的介面。

select * from USER_TABLE where ID=6 and name ="張三";

   欄位ID有索引， 而name沒有索引，那麼直接通過索引資料結構找到id=6的行記錄，然後返回到server層，判斷name的值是否是”張三“，如果是將這一行的值返回給客戶端；

select * from ORDER_TABLE where statu=1;

   對於上面的sql來說沒有索引，執行器的執行流程就是普通的查詢過程：
1、呼叫InnoDB引擎介面取這個表的第一行，判斷ID的值是不是6，如果不是則跳過，如果是就將這行的記錄儲存在結果集中；
2、呼叫引擎介面取“下一行”，重複相同的判斷邏輯，直到取到這個表的最後一行；
3、執行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結果集返回給客戶端；
    在我們執行前我們也可以通過explain來看一下執行計劃：

explain select * from ORDER_TABLE where statu=1;

    看當前的sql是否走了索引，掃描了多少行的記錄，做到心中有數，否則對於高併發的線上引起的慢查詢也不是好玩兒的。另外慢查詢中有rows_examined的欄位，記錄了語句執行過程中掃描了多少行記錄。

小結

Mysql查詢三步：
1、聯結器：連線管理模組，通過連線執行緒和客戶端對接，獲取許可權等資訊，驗證賬號密碼，維持連線，超時自動斷開，然後在有效的時間段內（interactive_timeout 和 wait_timeout控制）進行sql處理；
2、查詢

• 查詢快取[可設定關閉]，快取命中返回資料時對許可權校驗；如果快取沒有繼續執行：
• 分析器：內建解析樹，對其詞法分析，驗證表和欄位是否存在；語法進行檢查是否正確；
• 優化器：將前面解析樹轉換成執行計劃，並進行評估最優，得到最優執行方案交給執行器；
• 執行器：許可權判斷[真正進行curd的時候才進行許可權判斷i]，獲取鎖，開啟表，通過meta資料，獲取資料；
• 儲存引擎：真正儲存資料，提供資料的讀寫介面api；

3、返回結果給使用者，如果快取時開啟狀態，則更新快取；然後等待新的請求；

思考解惑

問題一：查詢“select * from TABLE where names = "張三" ”; 時返回  “Unknown column ‘names’ in ‘where clause”錯是那個階段呢？“you hava an error in your SQLsynax" 呢?

  答案：是分析器的詞法分析階段，後面是語義分析階段。這兩個階段mysql會將表名和欄位名解析成具體的mysql能識別的關鍵字及相應的語法分析。

問題二：為什麼對資料庫許可權的檢查不在優化器之前執行？或者是連線階段檢查？

  答案：因為有些時候sql的執行不僅僅是表面一些執行命令，還包括一些觸發器，這些操作只有在執行階段才確定，所以分析器、聯結器和優化器是無能為力的。

問題三：Mysql的框架有幾個元件和各有什麼作用？
 答：大體分為：server層 和 儲存引擎；

•   Server 層包括：聯結器；分析器；優化器；執行器；主要作用為：“連線、鑑權、計算”；
•   儲存引擎：讀寫和儲存資料；

問題四：長連線會導致記憶體的增加，這些是由於什麼產生？為什麼不提前釋放？
  答： 排序、變數和大查詢等 都會佔用記憶體；因為是長連線所以需要複用，提前釋放反而影響效能；

問題五：mysql中connetion_timeout 和 wait_timeout的區別？

• connection_timeout:連線過程中的等待時間；
• wait_time:是指連線完成後，使用過程中等待的時間;

問題六：mysql中表許可權驗證是什麼階段執行？
   答： 聯結器階段 “取”許可權；執行器“用”許可權。

 

學習筆記，內容簡單，用於複習，原內容2月有更新。
##參考資料，《MySql實戰詳解》