一條 SQL 語句執行過程

2022-03-16 16:00:27

一、MySQL 體系架構

- 連線池元件

1、負責與使用者端的通訊，是半雙工模式，這就意味著某一固定時刻只能由使用者端向伺服器請求或者伺服器向用戶端傳送資料，而不能同時進行。
2、驗證使用者名稱和密碼是否正確（資料庫 MySQL 的 user 表中進行驗證），如果錯誤返回錯誤通知Access denied for user 'root'@'localhost'（using password：YES）；如果正確，則會去 MySQL 的許可權表查詢當前使用者的許可權。

- 快取元件

也稱為查詢快取，儲存的資料是以鍵值對的形式進行儲存，如果開啟了快取，那麼在一條查詢 SQL 語句進來時會先判斷快取中是否包含當前的 SQL 語句鍵值對，如果存在直接將其對應的結果返回，如果不存在再執行後面一系列操作。如果沒有開啟則直接跳過。

show  variables  like  'have_query_cache'; # 檢視快取設定：
show  variables  like  'query_cache_type'; # 檢視是否開啟
show  variables  like  'query_cache_size'; # 檢視快取佔用大小
show  status  like  'Qcache%'; # 檢視快取狀態資訊

快取失效場景：

查詢語句不一致。前後兩條查詢 SQL 必須完全一致；
查詢語句中含有一些不確定的值時，則不會快取。比如 now()、current_date()、curdate()、curtime()、rand()、uuid() 等；
不使用任何表查詢。如 select 'A'；
查詢 mysql、information_schema 或 performance_schema 資料庫中的表時，不會走查詢快取；
在儲存的函數，觸發器或事件的主體內執行的查詢；
如果表更改，則使用該表的所有快取記憶體查詢都變為無效並從快取中刪除，這包括使用 MERGE 對映到已更改表的表的查詢。一個表可以被許多型別的語句改變，如 insert、update、delete、truncate table、alter table、drop table、drop database。

通過上面的失效場景可以看出快取是很容易失效的，所以如果不是查詢次數遠大於修改次數的話，使用快取不僅不能提升查詢效率還會拉低效率（每次讀取後需要向快取中儲存一份，而快取又容易被清除）。所以在 MySQL5.6 預設是關閉快取的，並且在 8.0 直接被移除了。當然，如果場景需要用到，還是可以使用的。

開啟：

在組態檔（linux 下是安裝目錄的 cnf 檔案，windows 是安裝目錄下的 ini 檔案）中，增加設定： query_cache_type = 1

# 指定 SQL_NO_CACHE，SQL_CACHE 同理。
SELECT  SQL_NO_CACHE  *  FROM  student  WHERE age > 20;

- 分析器

對使用者端傳來的 SQL 進行分析，這將包括預處理與解析過程，並進行關鍵詞的提取、解析，並組成一個解析樹。具體的解析詞包括但不侷限於 select/update/delete/or/in/where/group by/having/count/limit 等，如果分析到語法錯誤，會直接拋給

使用者端異常：ERROR:You have an error in your SQL syntax.。

select *  from user where userId = 1234;

在分析器中就通過語意規則器將 select from where 這些關鍵詞提取和匹配出來，MySQL 會自動判斷關鍵詞和非關鍵詞，將使用者的匹配欄位和自定義語句識別出來。這個階段也會做一些校驗：比如校驗當前資料庫是否存在 user 表，同時假如 user 表中不存在 userId 這個欄位同樣會報錯：unknown column in field list.。

- 優化器

進入優化器說明 SQL 語句是符合標準語意規則並且可以執行。優化器會根據執行計劃選擇最優的選擇，匹配合適的索引，選擇最佳的方案。比如一個典型的例子是這樣的：

表 T，對 A、B、C 列建立聯合索引 —— (A,B,C)，在進行查詢的時候，當 SQL 查詢條件是：select xx where B=x and A=x and C=x。很多人會以為是用不到索引的，但其實會用到，雖然索引必須符合最左原則才能使用，但是本質上，優化器會自動將這條 SQL 優化為：where A=x and B=x and C=x，這種優化會為了底層能夠匹配到索引，同時在這個階段是自動按照執行計劃進行預處理，MySQL 會計算各個執行方法的最佳時間，最終確定一條執行的 SQL 交給最後的執行器。

優化器會根據掃描行數、是否使用臨時表、是否排序等來判斷是否使用某個索引，其中掃描行數的計算可以通過統計資訊來估算得出，而統計資訊可以看作是索引唯一數的數量，可以使用部分取樣來估算，具體就是選擇 N 個資料頁，統計這些頁上資料的不同值，得到一個平均值，然後乘以這個索引的頁面數，就得到了。但是因為索引資料會變化，所以索引的統計資訊也會變化。當變更的資料行數超過 1/M 的時候，就會重新計算一次統計資訊。

關於統計資訊可以選擇是否持久化：：通過 innodb_stats_persistent，設定為 on 的時候，表示統計資訊會持久化儲存。這時，預設的 N 是 20，M 是 10。設定為 off 的時候，表示統計資訊只儲存在記憶體中。這時，預設的 N 是 8，M 是 16。

沒有使用最優索引如何優化：：

1、雖然會自動更新統計資訊，但是但是不能保證統計資訊是最新值，這就可能導致優化器選擇了不同的索引導致執行變慢，所以可以通過 analyze table 表名來重新計算索引的統計資訊;
2、在表名後面新增 force index(索引名) 語句來強制使用索引（不建議）;
3、將 SQL 進行修改成優化器可以選最優索引的實現方式；
4、新建一個最優索引或者刪除優化器誤用的索引；