你一定要知道的MySQL的運行機制
每次當我們執(zhí)行一條sql的時候，比如：
mysql> select * from T where ID=10；
復制代碼大家肯定會很好奇，這條mysql到底是怎么解析，從數(shù)據(jù)庫查詢到對應的數(shù)據(jù)的呢？這節(jié)課我跟大家一起解讀一下MySQL的運行原理
大體來說，MySQL可以分為Server 層和存儲引擎層兩部分。
Server 層包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等，涵蓋 MySQL 的大多數(shù)核心服務功能，以及所有的內(nèi)置函數(shù)（如日期、時間、數(shù)學和加密函數(shù)等），所有跨存儲引擎的功能都在這一層實現(xiàn)，比如存儲過程、觸發(fā)器、視圖等。
而存儲引擎層負責數(shù)據(jù)的存儲和提取。其架構(gòu)模式是插件式的，支持 InnoDB、MyISAM、Memory 等多個存儲引擎。現(xiàn)在最常用的存儲引擎是 InnoDB，它從 MySQL 5.5.5 版本開始成為了默認存儲引擎。
也就是說，你執(zhí)行 create table 建表的時候，如果不指定引擎類型，默認使用的就是 InnoDB。不過，你也可以通過指定存儲引擎的類型來選擇別的引擎，比如在 create table 語句中使用 engine=memory, 來指定使用內(nèi)存引擎創(chuàng)建表。不同存儲引擎的表數(shù)據(jù)存取方式不同，支持的功能也不同。不同的存儲引擎，共有一個Server層，也就是從連接器到執(zhí)行器的部分。下面，先從Server層開始講起。
Server層
建立連接(Connectors&Connection Pool)
通過客戶端/服務器通信協(xié)議與MySQL建立連 接。MySQL 客戶端與服務端的通信方式是 “ 半雙工 ”。對于每一個 MySQL 的連接，時刻都有一個線程狀態(tài)來標識這個連接正在做什么。
通訊機制：
全雙工:能同時發(fā)送和接收數(shù)據(jù)，例如平時打電話。
半雙工:指的某一時刻，要么發(fā)送數(shù)據(jù)，要么接收數(shù)據(jù)，不能同時。例如早期對講機
單工:只能發(fā)送數(shù)據(jù)或只能接收數(shù)據(jù)。例如單行道
線程狀態(tài): show processlist; //查看用戶正在運行的線程信息，root用戶能查看所有線程，其他用戶只能看自 己的
查詢緩存(Cache&Buffer)
連接建立完成之后，就可以執(zhí)行select語句了，這時候就到了第二步邏輯：查詢緩存了。這是MySQL一個可以優(yōu)化的地方，如果開啟了查詢緩存且在查詢緩存過程中查詢到完全相同的SQL語句，則將查詢結(jié)果直接返回給客戶端，如果沒有開啟查詢緩存或者沒有查詢到完全相同的SQL語句則會交給解析器進行語法語義解析，并生成"解析樹"
show variables like '%query_cache%'; //查看查詢緩存是否啟用，空間大小，限制等
show status like 'Qcache%'; //查看更詳細的緩存參數(shù)，可用緩存空間，緩存塊，緩存多少等
這里查詢緩存雖然是一個可以優(yōu)化的點，但是我覺得查詢緩存能別用還是盡量別用，因為查詢緩存的弊大于利。
查詢緩存的失效特別頻繁，只要有對一個表的更新，這個表上所有的查詢緩存都會清空。因此你可能前一秒存上了，后一秒就清空了。
對于更新壓力大的數(shù)據(jù)庫來說，查詢緩存的命中率會非常低。除非你的業(yè)務就是有一張靜態(tài)表，很長時間才會更新一次。比如，一個系統(tǒng)配置表，那這張表上的查詢才適合使用查詢緩存。
好在 MySQL 也提供了這種“按需使用”的方式。你可以將參數(shù) 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"這個關(guān)鍵字識別出來，這是一個查詢語句。它也要把字符串“T”識別成“表名 T”，把字符串“ID”識別成“列 ID”。
做完了這些識別以后，就要做“語法分析”。根據(jù)詞法分析的結(jié)果，語法分析器會根據(jù)語法規(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)錯誤的位置，所以你要關(guān)注的是緊接“use near”的內(nèi)容。
解析樹：解析樹是通過解析器來解析SQL的關(guān)鍵字和非關(guān)鍵字，比如select username from userinfo按照關(guān)鍵字和非關(guān)鍵字進行分類，生成樹：
預處理器：根據(jù)一些mysql規(guī)則進一步檢查解析樹是否合法。如檢查查詢的表名、列名是否正確，是否有表的權(quán)限等
優(yōu)化器(Optimizer)
根據(jù)解析樹生成最優(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)合索引，調(diào)整條件位置等
優(yōu)化count、min、max等函數(shù)
InnoDB引擎min函數(shù)只需要找索引最左邊
InnoDB引擎max函數(shù)只需要找索引最右邊
MyISAM引擎count(*)，不需要計算，直接返回
提前終止查詢
使用了limit查詢，獲取limit所需的數(shù)據(jù)，就不在繼續(xù)遍歷后面數(shù)據(jù)
in的優(yōu)化
MySQL對in查詢，會先進行排序，再采用二分法查找數(shù)據(jù)。比如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 值，再根據(jù) ID 值關(guān)聯(lián)到表 t2，再判斷 t2 里面 d 的值是否等于 20。
也可以先從表 t2 里面取出 d=20 的記錄的 ID 值，再根據(jù) ID 值關(guān)聯(lián)到 t1，再判斷 t1 里面 c 的值是否等于 10。
這兩種執(zhí)行方法的邏輯結(jié)果是一樣的，但是執(zhí)行的效率會有不同，而優(yōu)化器的作用就是決定選擇使用哪一個方案。
執(zhí)行引擎
查詢執(zhí)行引擎負責執(zhí)行 SQL 語句，開始執(zhí)行的時候，要先判斷一下你對這個表 T 有沒有執(zhí)行查詢的權(quán)限，如果沒有，就會返回沒有權(quán)限的錯誤，如下所示 (在工程實現(xiàn)上，如果命中查詢緩存，會在查詢緩存返回結(jié)果的時候，做權(quán)限驗證。查詢也會在優(yōu)化器之前調(diào)用 precheck 驗證權(quán)限)。如果有權(quán)限就會打開表繼續(xù)執(zhí)行，此時查詢執(zhí)行引擎會根據(jù) SQL 語句中表的存儲引擎類型，以 及對應的API接口與底層存儲引擎緩存或者物理文件的交互，得到查詢結(jié)果并返回給客戶端。若開 啟用查詢緩存，這時會將SQL 語句和結(jié)果完整地保存到查詢緩存(Cache&Buffer)中，以后若有 相同的 SQL 語句執(zhí)行則直接返回結(jié)果。
到此，MySQL的運行機制就結(jié)束了。
作者：Five在努力
鏈接：https://juejin.cn/post/6969839499731795999
來源：掘金
著作權(quán)歸作者所有。商業(yè)轉(zhuǎn)載請聯(lián)系作者獲得授權(quán)，非商業(yè)轉(zhuǎn)載請注明出處。