image.png

原文：MySQL系統(tǒng)學(xué)習(xí)（01）：基礎(chǔ)架構(gòu)磨总，一條SQL查詢語句是如何執(zhí)行的

前言

學(xué)習(xí)技術(shù)嗦明，不應(yīng)該直接陷進(jìn)細(xì)節(jié)里，我們應(yīng)該首先鳥瞰其全貌蚪燕，這樣可以幫助我們從高維度理解問題娶牌，然后在一步步的糾其細(xì)節(jié)。同樣對于MySQL的學(xué)習(xí)也是如此馆纳。

MySQL作為一個最基礎(chǔ)的DB存儲诗良，同時也是我們開發(fā)中使用最多的一個DB，可能你會跟我之前一樣覺得是只要會常用的SQL鲁驶，能夠滿足我們?nèi)粘５拈_發(fā)就足夠了鉴裹，但是作為一名開發(fā)人員，對應(yīng)MYSQL原理和細(xì)節(jié)的了解可以幫助我們更好的使用它钥弯。所以有必要系統(tǒng)的學(xué)習(xí)它径荔，我認(rèn)為只要徹底的掌握了MYSQL，那再去學(xué)習(xí)其他的關(guān)系型DB或者NoSQL也就更容易上手了寿羞。

平時我們在使用數(shù)據(jù)庫猖凛，看到的通常都是一個整體。比如绪穆，我有個最簡單的表，表里只有一個ID字段虱岂，在執(zhí)行下面這個查詢語句時：

mysql> select * from T where ID = 10;

我們看到的只是輸入一條語句玖院，返回一個結(jié)果，卻不知道這條語句在MYSQL內(nèi)部的執(zhí)行過程第岖。

所以今天我想把MYSQL拆解下难菌，看看里面都有哪些“零件”，希望可以借由這個拆解的過程蔑滓，對MYSQL有更深的理解郊酒。這樣當(dāng)我們碰到MYSQL的一些異常或者問題時键袱，就能夠直戳本質(zhì)燎窘，更為快捷的定位并解決問題。

MySQL基礎(chǔ)架構(gòu)

這個是我畫的一個MySQL基礎(chǔ)架構(gòu)示意圖蹄咖，從中可以清楚到的看到SQL語句在MySQL的各個模塊中的執(zhí)行過程褐健。

image.png

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

Server層包括連接器澜汤、查詢緩存蚜迅、分析器舵匾、優(yōu)化器、執(zhí)行器等谁不，涵蓋MySQL大部分核心服務(wù)功能坐梯，以及所有的內(nèi)置函數(shù)(如日期、時間刹帕、數(shù)學(xué)和加密函數(shù)等)吵血，所有跨存儲引擎的過程都在這一層實現(xiàn)，如存儲過程轩拨、觸發(fā)器践瓷、視圖等。

而存儲引擎層負(fù)責(zé)數(shù)據(jù)的存儲和提取亡蓉。其架構(gòu)模式是插件式的晕翠，支持InnoDB、MyISAM砍濒、Memory等多個存儲引擎×苌觯現(xiàn)在最長用的存儲引擎是InnoDB，他從5.5.5版本開始成為默認(rèn)存儲引擎爸邢。

也就是說樊卓，我們執(zhí)行create table建表的時候如果不指定存儲引擎，默認(rèn)使用的就是InnoDB杠河。不過碌尔，我們也可以通過指定存儲引擎的類型選擇別的存儲引擎。比如在create table的時候指定negine=memory券敌，來制定內(nèi)存引擎來創(chuàng)建表唾戚。不同存儲引擎的表數(shù)據(jù)存取方式不同，支持的功能也不同待诅，后面的學(xué)習(xí)筆記中會專門抽出去記錄的叹坦。

從圖中不難看出，不同的存儲引擎共用一個Server層卑雁，也就是從連接器到執(zhí)行器部分募书。我們暫時只需要對各個組件的名字有個印象，接下來我會結(jié)合開頭提到的那條SQL語句测蹲，一塊走一遍執(zhí)行流程莹捡，依次看下每個組件的作用。

連接器

第一步弛房，我們會連接都這個數(shù)據(jù)庫上道盏，這時候接待我們的就是連接器。連接器負(fù)責(zé)跟客戶端建立連接、獲取權(quán)限荷逞、維持和管理連接媒咳。連接命令一般這么寫：

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

輸完命令之后，我們需要在交互對話框中輸入密碼种远。雖然密碼也可以直接跟在-p后面涩澡，但這樣寫可能會導(dǎo)致密碼泄露等。如果我們連接的生產(chǎn)服務(wù)器坠敷，強(qiáng)烈建議不要這么做妙同。

連接命令中的mysql是客戶端工具，用來跟服務(wù)端建立連接膝迎。在完成經(jīng)典的TCP握手之后粥帚，連接器就要開始認(rèn)證我的身份，這個時候用的就是我輸入的用戶名和密碼限次。

（1）如果用戶名和密碼不對芒涡，我們就會收到一個“Access denied for user”的錯誤，然后客戶端程序結(jié)束執(zhí)行卖漫。
（2）如果用戶名跟密碼認(rèn)證通過费尽，連接器會在權(quán)限表里查出我所擁有的權(quán)限。之后這個連接里面的權(quán)限判斷邏輯羊始，都將依賴于此時讀到的權(quán)限旱幼。

這就意味著，一個用戶成功建立連接后突委，即使我們用管理員賬號對這個用戶權(quán)限做了修改柏卤，也不會影響已經(jīng)存在連接的權(quán)限。修改完成后匀油，只有再新建連接才會使用新的權(quán)限設(shè)置闷旧。

連接完成后，如果我們沒有后續(xù)的動作钧唐，這個連接就處于空閑狀態(tài)，我們可以在show processlist命令中看到它匠襟。文本中這個圖是show processlist的結(jié)果钝侠，其中的Command列顯示為“Sleep”的這幾行，就表示現(xiàn)在系統(tǒng)里面存在幾個空閑連接酸舍。

image.png

客戶端如果太長時間沒有動靜帅韧，連接器就會將它斷開，這個時間是由wait_timeout控制的啃勉，默認(rèn)時間是8小時忽舟。

如果在連接被斷開之后，再次發(fā)起請求的話，就會收到一個錯誤提醒：“Lost connection to MySQL server during query” 這時候如果我們要繼續(xù)請求叮阅，就需要重連刁品，然后在執(zhí)行請求。

數(shù)據(jù)庫里面浩姥，長連接是指連接成功后挑随，如果客戶端持續(xù)有請求，則一直使用同一個連接勒叠。
短連接指每次執(zhí)行完很少的幾次查詢就斷開了連接兜挨，下次查詢在重新建立一個。

建立連接的過程通常是比較復(fù)雜的眯分，所以我建議在使用中盡量減少建立連接的動作拌汇，也就是盡量使用長連接。

但是全部使用長連接后弊决，我們又會發(fā)現(xiàn)有些時候會出現(xiàn)MySQL占用內(nèi)存飆升的情況發(fā)生噪舀，這是因為MySQL在執(zhí)行過程當(dāng)中臨時使用的內(nèi)存是管理在連接對象當(dāng)中的。這些資源會在連接斷開的時候才釋放丢氢。所以如果長連接累積下來可能導(dǎo)致內(nèi)存占用過大傅联，被系統(tǒng)強(qiáng)行殺掉（OOM），現(xiàn)象表現(xiàn)為MySQL異常重啟了疚察。

那我們要怎么解決這個問題呢蒸走？可以考慮一下兩種方案：
（1）定期的斷開長連接。使用一段時間貌嫡，或者程序里面判斷執(zhí)行過一個占用高內(nèi)存的大查詢之后比驻，斷開連接，只要要查詢在重連岛抄。
（2）如果我們使用的是mySQL5.7 或更新的版本别惦，可以在每次執(zhí)行一個比較大的操作后，通過執(zhí)行mysql_reset_connection 來重新初始化連接資源夫椭。這個過程不需要重連或權(quán)限驗證掸掸，但是會將連接恢復(fù)至剛剛創(chuàng)建完成時的狀態(tài)。

查詢緩存

連接建立完成時蹭秋，我們就可以執(zhí)行select語句了扰付。這個時候執(zhí)行邏輯就會來到第二步：查詢緩存。

MySQL拿到一個查詢請求后仁讨，會先到查詢緩存看看羽莺，之前是不是執(zhí)行過這條語句及其結(jié)果可能會以key-value對的形式，被直接緩存在內(nèi)存中洞豁。key是查詢的語句盐固，value是查詢的結(jié)果荒给。如果我們的查詢能夠直接在這個緩存中找到key，那么這個value就會被直接緩存到客戶端刁卜。

如果語句不在查詢緩存中志电，就會繼續(xù)后面的執(zhí)行階段。執(zhí)行完成后长酗，執(zhí)行結(jié)果會被存到查詢緩存中溪北。你可以看到，如果查詢命中緩存夺脾，MySQL不需要執(zhí)行后面的查詢操作之拨，這個效率會很高。

但是大多數(shù)情況下個人是不建議使用查詢緩存的咧叭，為什么呢蚀乔？因為查詢緩存往往弊大于利

因為查詢緩存的失效非常頻繁，只要有對一個表的更新菲茬，這個表上的所有查詢緩存都會被清空吉挣。因此很有可能我們費勁的把結(jié)果存起來，還沒使用呢婉弹，就被一個更新全清空了睬魂。對于更新壓力大的DB來說，查詢緩存的命中率會非常的低镀赌。除非我們的業(yè)務(wù)就是一張靜態(tài)表氯哮，或者很少去變動的這樣一張表。比如一個系統(tǒng)配置表商佛，那這種表上的查詢才適合使用查詢緩存喉钢。

好在mysql也提供了這種“按需使用”的方法。我們可以將參數(shù)query_cache_type設(shè)置成DEMAND良姆，這樣對于默認(rèn)的SQL語句都不使用查詢緩存肠虽。而對于你確定要使用查詢緩存的語句可以這樣寫：

mysql> select SQL_CACHE * from T where ID = 10;

需要注意的是MySQL8.0版本直接將查詢緩存的整個模塊給拿掉了，也就是說8.0開始徹底沒有這個功能了

分析器

如果沒有命中查詢緩存就要開始真正執(zhí)行查詢語句了玛追。首先税课，MySQL需要知道我們真正要做什么，因此需要對SQL語句進(jìn)行解析痊剖，這時就需要用到分析器伯复。

首先分析器的主要作用是：“詞法分析”和“語法分析”。

分析器回先做“詞法分析”邢笙。我們輸入的是由多個字符串和空格組成的一條SQL語句，MySQL需要識別出來里面的字符串分別是什么侍匙，代表什么氮惯。

MySQL從我輸入的select關(guān)鍵詞識別出來這時一條查詢語句叮雳。他要把字符串“T”識別成“表名T”，把字符串“ID”識別成“字段ID”妇汗。

做完這些識別以后帘不，就要做“語法分析”。根據(jù)詞法分析的結(jié)果杨箭，預(yù)發(fā)分析器就會根據(jù)語法規(guī)則寞焙，判斷我輸入的這個SQL語句是否滿足SQL的語法規(guī)則。

如果我們輸入的語句不對互婿，就會收到“You have an error in your SQL syntax”的錯誤提示捣郊，比如下面這個語句的select少寫了一個s

mysql> elect * from T where ID = 10;

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 SQL_CACHE  * from china_region_info_v2 where code=110101001011' at line 1

一般語法錯誤提示會提示第一個出現(xiàn)錯誤的位置，所以我們排查問題一般需要關(guān)注的是緊接“use near”的內(nèi)容慈参。

優(yōu)化器

經(jīng)過分析器呛牲，MySQL就知道我要做什么事情了。在開始執(zhí)行之前驮配，還要先經(jīng)過優(yōu)化器的處理娘扩。

優(yōu)化器是在表里面存在多個索引的時候決定使用哪個索引；或者在一個語句有多表關(guān)聯(lián)（join）的時候壮锻，決定各個表的連接順序琐旁。比如我們執(zhí)行下面這樣的語句，這個語句是執(zhí)行兩個表的join：

mysql> select * from T1 t1 join T2 t2 using(ID) where t1.c = 10 and t2.d = 20;

(1) 即可以先從表t1里取出c等于10對應(yīng)記錄的ID值猜绣，在根據(jù)ID值關(guān)聯(lián)t2表中d值等于20的記錄灰殴。

(2)也可以先從表t2中找到d值等于20的記錄對應(yīng)的ID值，在根據(jù)ID值關(guān)聯(lián)t1表中c等于10的記錄途事。

這兩種執(zhí)行方法的結(jié)果是一樣的验懊，但是執(zhí)行的效率會有不同，而優(yōu)化器的作用就是決定使用哪一種方案尸变。

優(yōu)化器階段完成后义图，這個語句的執(zhí)行方案就確定下來了，然后進(jìn)入執(zhí)行器階段召烂。如果目前為止你還有一些疑問碱工，不要著急，這篇文章只是讓我們了解一下MYSQL的基礎(chǔ)結(jié)構(gòu)及SQL的執(zhí)行過程奏夫，至于優(yōu)化器具體是如何優(yōu)化怕篷，如何確定方案我會在后面的筆記中一步步記錄下來，你可以提前了解一下酗昼，后面跟我記錄的在比對一下廊谓，看看有什么不一樣的，我們可以一塊討論麻削，共同學(xué)習(xí)蒸痹。

執(zhí)行器

MySQL通過分析器知道了我們要做什么春弥，通過優(yōu)化器知道了要怎么做，于是就進(jìn)入了執(zhí)行器階段叠荠，開始執(zhí)行語句匿沛。

開始執(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ù)表的引擎定義拾碌，去使用這個引擎提供的接口。

比如我舉例中的T表的ID字段沒有索引街望，那么執(zhí)行器的執(zhí)行流程是這樣的：
（1）調(diào)用InnoDB引擎接口取這張表的第一行校翔，判斷ID是否滿足條件，看ID的值是不是10灾前，如果是則將結(jié)果緩存到結(jié)果集中防症。如果不是則跳過。

（2）調(diào)用引擎接口取“下一行”哎甲，重復(fù)相同的邏輯判斷蔫敲，直到取到這個表的最后一行。

（3）執(zhí)行器將上述過程中篩選出來的滿足條件的記錄行組成的記錄及作為結(jié)果集返回給客戶端炭玫。

至此奈嘿，這個語句就執(zhí)行完了。

對于有索引的表吞加，執(zhí)行的過程也差不多裙犹。第一次調(diào)用的是“取滿足條件的第一行”這個接口，之后循環(huán)取“滿足條件的下一行”這個接口衔憨，這些接口都是引擎中已經(jīng)定義好的叶圃。

你會數(shù)據(jù)庫的慢查詢中日志中看到一個rows_examined的字段，表示這條語句執(zhí)行過程中掃描了多少行践图。這個值就是執(zhí)行器每次調(diào)用存儲引擎獲取數(shù)據(jù)行的時候累加的掺冠。

總結(jié)

這篇筆記主要是想介紹一下MySQL的邏輯架構(gòu)，希望幫助大家也幫助自己對SQL語句的完整執(zhí)行流程的各個階段有一個初步的了解码党。同時對MySQL內(nèi)部最基礎(chǔ)且最核心的幾大“零件”及作用有一個初步的認(rèn)識赫舒，這個前置的了解有助于我們后續(xù)深入的學(xué)習(xí)悍及。

我的更多興趣分享：[個人網(wǎng)站RelaxHeart網(wǎng) - TEC博客](http://www.relaxheart.cn/to/master/blog