---
title: 一句查詢在MySQL底層是如何運作的？
date: 2020-01-05 18:23:15
tags:
- MySQL
thumbnail: /uploads/mysql.jpg
---

平常我們使用MySQL都是進行查詢然後資料庫返回結果

mysql > SELECT * FROM table WHERE id = 1

但是我們卻不知道這句查詢語句在MySQL內部的執行過程，所以我們一起來看看MySQL底層對查詢到底做了些什麼事情，這樣當我們碰到一些異常或者問題時，就可以直指本質，更快速的找出問題發生點並解決。

客戶端 → 連接器 → 分析器 → 優化器 → 執行器
↘ ↙
查詢緩存

### 連接器
負責建立維持和管理連線、獲取權限。

mysql -u root -p

一般來說command line連線的指令會是上述這句，在完成TCP握手之後，連接器會開始驗證你的身份，這時候用的就是你的帳號和密碼。
1. 如果帳號或者密碼錯誤，你會收到 ”Access denied for user” 的錯誤，然後客戶端（海豚, phpmyadmin, cli, etc….）會結束此條連線請求
2. 如果帳號密碼皆正確，連接器會到權限資料表查出你擁有的權限，之後這個連接的權限判斷邏輯，都會依賴此時讀到的權限。

在這邊解釋了，為什麼我們每次修改權限，都需要再次新建連線，才會讀到新的權限配置。

連接完成後，如果沒有執行任何動作，這條連線就會處於Sleep狀態


那條Sleep連線是我剛剛建立的新連線，各位如果想實際操作，可以建立新連線後，在舊有的連線中使用這句指令

mysql > show processlist #查看mysql目前的用戶連線進程

如果連線太長時間沒有操作，連接器就會自動斷開連線，預設是8小時，如果需要修改可以變更 **wait_tomeout**。

連接被斷開之後，客戶端再次發送請求的話，就會收到 “Lost connection to MySQL server during query”的錯誤，這時候如果要繼續，就必須要重新建立新連線，然後再執行請求。

建立連線過程，通常都是比較複雜的，所以建議盡量減少使用建立連線的動作，也就是說盡量使用長連線，但是全部使用長連線的話，有可能會導致MySQL佔用記憶體上漲很快，這是因為MySQL在執行過程中臨時使用的記憶體是管理在連線對象裡面的，這些資源如果沒做其他動作，那麼只有在斷開連線的時候才會釋放，所以如果長時間累積下來，可能會導致記憶體佔用過大，被系統強行kill。

如何解決這個問題？
1. 在完成一個佔用大量內存的大查詢後，斷開連線，之後再重建連線。
2. 可以使用 mysql_reset_connection 不過這個指令只支持MySQL5.7包含以上的版本，來重新初始化連線，他會將連線恢復到剛創建完的狀態。

### 查詢緩存

連線完成後，就可以使用SELECT語句，這時候會來到第二步，查詢緩存。

MySQL得到一句SELECT後，會先到緩存搜尋看看之前是不是有執行過這條查詢，之前執行的語句會以key-value的形式緩存在記憶體中。key是查詢的語句，value則是結果，如果有匹配的緩存，就會直接返回value。如果緩存中沒有，MySQL會進行查詢後，將查詢結果寫進緩存當中。如果使用了查詢緩存返回資料，那麼資料庫就不需要執行後面複雜的操作，直接可以返回結果，這樣可以大大提高這個查詢在資料庫的效率。

但是大多數的情況下，建議不要使用緩存。為什麼呢？
1. 在一個頻繁會更改的表，在更新的時候該表內所有的緩存都將全部被清空，因此很有可能在未使用到緩存的時候，該表的緩存已經被全部清空了。除非是使用長期不會變動的表，否則不太建議使用查詢緩存。
2. MySQL 8.0 已經把緩存功能徹底拔除。

### 分析器
如果緩存沒有查到你要的結果，那麼就會開始真正執行搜尋語句，首先，MySQL會需要分析你的查詢語句的意思。

MySQL會抓取關鍵字，以我們一開始的那句為例，SELECT判斷出這是一個搜尋語句，table判斷為表名，id判斷為欄位名。同時也會判斷你這句查詢是否符合MySQL的查詢語法規則。

如果判斷輸入的MySQL查詢語句是錯的，那麼他就會返回錯誤給客戶端

1064 - 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 'how processlist # 123123123' at line 1, Time: 0.003000s

一般的語法錯誤會出現在 **use near** 後面

### 優化器

經過了分析器，MySQL已經知道你想做什麼，在開始執行前也需要經過優化器處理。

優化器主要的工作是當表裡有多個索引，決定使用哪個索引，或者在一個查詢語法裡面有Join的時候，決定各個表的連接順序。優化器工作完成之後，這句的執行流程方式，基本上就已經定型了。

### 執行器

MySQL透過分析器知道你要做什麼，通過優化器知道該如何做，接下來就是要執行查詢語句了。

開始執行時，會先判斷你是否有對這個表搜尋的權限，如果有查詢緩存，會在緩存返回時，進行權限判斷。如果沒有權限則會返回以下訊息

mysql > SELECT command denied to user 'test'@'localhost' for table 'table'

如果有權限就會打開表繼續執行，打開表的時候，執行器會根據表的引擎去選擇該引擎的接口。

執行流程
1. 調用引擎取這個表的第一行，判斷ID是否為1，如果不是則跳過，如果是則將這筆結果保存在結果集內。
2. 調用引擎取下一筆資料，重複驗證判斷，直至取到這個表的最後一行
3. 執行器將所有滿足條件的結果返回給客戶端

到此，這個查詢就已經完成了。