每次當我們執(zhí)行一條sql的時候，比如：

mysql> select * from T where ID=10；
復制代碼

大家肯定會很好奇，這條mysql到底是怎么解析，從數據庫查詢到對應的數據的呢？這節(jié)課我跟大家一起解讀一下MySQL的運行原理

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

Server 層包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等，涵蓋 MySQL 的大多數核心服務功能，以及所有的內置函數（如日期、時間、數學和加密函數等），所有跨存儲引擎的功能都在這一層實現(xiàn)，比如存儲過程、觸發(fā)器、視圖等。

而存儲引擎層負責數據的存儲和提取。其架構模式是插件式的，支持 InnoDB、MyISAM、Memory 等多個存儲引擎?，F(xiàn)在最常用的存儲引擎是 InnoDB，它從 MySQL 5.5.5 版本開始成為了默認存儲引擎。

也就是說，你執(zhí)行 create table 建表的時候，如果不指定引擎類型，默認使用的就是 InnoDB。不過，你也可以通過指定存儲引擎的類型來選擇別的引擎，比如在 create table 語句中使用 engine=memory, 來指定使用內存引擎創(chuàng)建表。不同存儲引擎的表數據存取方式不同，支持的功能也不同。不同的存儲引擎，共有一個Server層，也就是從連接器到執(zhí)行器的部分。下面，先從Server層開始講起。

Server層

建立連接(Connectors&Connection Pool)

通過客戶端/服務器通信協(xié)議與MySQL建立連接。MySQL 客戶端與服務端的通信方式是 “ 半雙工 ”。對于每一個 MySQL 的連接，時刻都有一個線程狀態(tài)來標識這個連接正在做什么。

通訊機制：

全雙工:能同時發(fā)送和接收數據，例如平時打電話。
半雙工:指的某一時刻，要么發(fā)送數據，要么接收數據，不能同時。例如早期對講機
單工:只能發(fā)送數據或只能接收數據。例如單行道

線程狀態(tài): show processlist; //查看用戶正在運行的線程信息，root用戶能查看所有線程，其他用戶只能看自己的

查詢緩存(Cache&Buffer)

連接建立完成之后，就可以執(zhí)行select語句了，這時候就到了第二步邏輯：查詢緩存了。這是MySQL一個可以優(yōu)化的地方，如果開啟了查詢緩存且在查詢緩存過程中查詢到完全相同的SQL語句，則將查詢結果直接返回給客戶端，如果沒有開啟查詢緩存或者沒有查詢到完全相同的SQL語句則會交給解析器進行語法語義解析，并生成"解析樹"

show variables like '%query_cache%'; //查看查詢緩存是否啟用，空間大小，限制等

show status like 'Qcache%'; //查看更詳細的緩存參數，可用緩存空間，緩存塊，緩存多少等

這里查詢緩存雖然是一個可以優(yōu)化的點，但是我覺得查詢緩存能別用還是盡量別用，因為查詢緩存的弊大于利。

查詢緩存的失效特別頻繁，只要有對一個表的更新，這個表上所有的查詢緩存都會清空。因此你可能前一秒存上了，后一秒就清空了。
對于更新壓力大的數據庫來說，查詢緩存的命中率會非常低。除非你的業(yè)務就是有一張靜態(tài)表，很長時間才會更新一次。比如，一個系統(tǒng)配置表，那這張表上的查詢才適合使用查詢緩存。

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

mysql> select SQL_CACHE * from T where ID=10；
復制代碼

需要注意的是，MySQL 8.0 版本直接將查詢緩存的整塊功能刪掉了，也就是說 8.0 開始徹底沒有這個功能了。

解析器(Parser)

如果沒有命中緩存，或者查詢緩存根本沒有開啟，就到了解析器這一步。這里解析器也可以叫做分析器，將客戶端發(fā)送的SQL進行語法解析，生成"解析樹"。

語法解析：一條SQL語句是由多個字符串和空格組成的，MySQL需要識別里面字符分別是什么，代表什么。

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

做完了這些識別以后，就要做“語法分析”。根據詞法分析的結果，語法分析器會根據語法規(guī)則，判斷你輸入的這個 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
復制代碼

一般語法錯誤會提示第一個出現(xiàn)錯誤的位置，所以你要關注的是緊接“use near”的內容。

解析樹：解析樹是通過解析器來解析SQL的關鍵字和非關鍵字，比如select username from userinfo按照關鍵字和非關鍵字進行分類，生成樹：

預處理器：根據一些mysql規(guī)則進一步檢查解析樹是否合法。如檢查查詢的表名、列名是否正確，是否有表的權限等

優(yōu)化器(Optimizer)

根據解析樹生成最優(yōu)的執(zhí)行計劃。MySQL使用很多優(yōu)化策略生成最優(yōu)的執(zhí)行計劃，可以分為兩類：靜態(tài)優(yōu)化（編譯時的優(yōu)化），動態(tài)優(yōu)化（運行時的優(yōu)化）

等價變換策略

5=5 and a>5 改成 a > 5
a < b and a=5 改成b>5 and a=5
基于聯(lián)合索引，調整條件位置等

優(yōu)化count、min、max等函數

InnoDB引擎min函數只需要找索引最左邊
InnoDB引擎max函數只需要找索引最右邊
MyISAM引擎count(*)，不需要計算，直接返回

提前終止查詢

使用了limit查詢，獲取limit所需的數據，就不在繼續(xù)遍歷后面數據

in的優(yōu)化

MySQL對in查詢，會先進行排序，再采用二分法查找數據。比如where id in (2,1,3)，變

成 in (1,2,3)

比如：

mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;
復制代碼

既可以先從表 t1 里面取出 c=10 的記錄的 ID 值，再根據 ID 值關聯(lián)到表 t2，再判斷 t2 里面 d 的值是否等于 20。
也可以先從表 t2 里面取出 d=20 的記錄的 ID 值，再根據 ID 值關聯(lián)到 t1，再判斷 t1 里面 c 的值是否等于 10。

這兩種執(zhí)行方法的邏輯結果是一樣的，但是執(zhí)行的效率會有不同，而優(yōu)化器的作用就是決定選擇使用哪一個方案。

執(zhí)行引擎

查詢執(zhí)行引擎負責執(zhí)行 SQL 語句，開始執(zhí)行的時候，要先判斷一下你對這個表 T 有沒有執(zhí)行查詢的權限，如果沒有，就會返回沒有權限的錯誤，如下所示 (在工程實現(xiàn)上，如果命中查詢緩存，會在查詢緩存返回結果的時候，做權限驗證。查詢也會在優(yōu)化器之前調用 precheck 驗證權限)。如果有權限就會打開表繼續(xù)執(zhí)行，此時查詢執(zhí)行引擎會根據 SQL 語句中表的存儲引擎類型，以及對應的API接口與底層存儲引擎緩存或者物理文件的交互，得到查詢結果并返回給客戶端。若開啟用查詢緩存，這時會將SQL 語句和結果完整地保存到查詢緩存(Cache&Buffer)中，以后若有相同的 SQL 語句執(zhí)行則直接返回結果。

到此，MySQL的運行機制就結束了。

作者：Five在努力
鏈接：https://juejin.cn/post/6969839499731795999
來源：掘金
著作權歸作者所有。商業(yè)轉載請聯(lián)系作者獲得授權，非商業(yè)轉載請注明出處。

你一定要知道的MySQL的運行機制