mysql在我們的開(kāi)發(fā)中基本每天都要面對(duì)的,作為開(kāi)發(fā)中的數(shù)據(jù)的來(lái)源,mysql承擔(dān)者存儲(chǔ)數(shù)據(jù)和讀寫(xiě)數(shù)據(jù)的職責(zé)。因?yàn)閷W(xué)習(xí)和了解mysql是至關(guān)重要的,那么當(dāng)我們?cè)诳蛻?hù)端發(fā)起一個(gè)sql到出現(xiàn)詳細(xì)的查詢(xún)數(shù)據(jù),這其中究竟經(jīng)歷了什么樣的過(guò)程？mysql服務(wù)端是如何處理請(qǐng)求的,又是如何執(zhí)行sql語(yǔ)句的？本篇博客將來(lái)探討這個(gè)問(wèn)題：

一:mysql執(zhí)行過(guò)程

mysql整體的執(zhí)行過(guò)程如下圖所示：

1.1:連接器

連接器的主要職責(zé)就是:

①負(fù)責(zé)與客戶(hù)端的通信,是半雙工模式,這就意味著某一固定時(shí)刻只能由客戶(hù)端向服務(wù)器請(qǐng)求或者服務(wù)器向客戶(hù)端發(fā)送數(shù)據(jù),而不能同時(shí)進(jìn)行,其中mysql在與客戶(hù)端連接TC/IP的

②驗(yàn)證請(qǐng)求用戶(hù)的賬戶(hù)和密碼是否正確,如果賬戶(hù)和密碼錯(cuò)誤,會(huì)報(bào)錯(cuò):Access denied for user 'root'@'localhost' (using password: YES)

③如果用戶(hù)的賬戶(hù)和密碼驗(yàn)證通過(guò),會(huì)在mysql自帶的權(quán)限表中查詢(xún)當(dāng)前用戶(hù)的權(quán)限:

mysql中存在4個(gè)控制權(quán)限的表，分別為user表，db表，tables_priv表，columns_priv表,mysql權(quán)限表的驗(yàn)證過(guò)程為：

1:User表:存放用戶(hù)賬戶(hù)信息以及全局級(jí)別（所有數(shù)據(jù)庫(kù)）權(quán)限，決定了來(lái)自哪些主機(jī)的哪些用戶(hù)可以訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)實(shí)例
    Db表:存放數(shù)據(jù)庫(kù)級(jí)別的權(quán)限，決定了來(lái)自哪些主機(jī)的哪些用戶(hù)可以訪(fǎng)問(wèn)此數(shù)據(jù)庫(kù)
    Tables_priv表：存放表級(jí)別的權(quán)限，決定了來(lái)自哪些主機(jī)的哪些用戶(hù)可以訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)的這個(gè)表
    Columns_priv表：存放列級(jí)別的權(quán)限，決定了來(lái)自哪些主機(jī)的哪些用戶(hù)可以訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)表的這個(gè)字段
    Procs_priv表：存放存儲(chǔ)過(guò)程和函數(shù)級(jí)別的權(quán)限

2:先從user表中的Host,User,Password這3個(gè)字段中判斷連接的ip、用戶(hù)名、密碼是否存在，存在則通過(guò)驗(yàn)證。

3:通過(guò)身份認(rèn)證后，進(jìn)行權(quán)限分配，按照user，db，tables_priv，columns_priv的順序進(jìn)行驗(yàn)證。即先檢查全局權(quán)限表user，如果user中對(duì)應(yīng)的權(quán)限為Y，則此用戶(hù)對(duì)所有數(shù)據(jù)庫(kù)的權(quán)限都為Y，將不再檢查db, tables_priv,columns_priv；如果為N，則到db表中檢查此用戶(hù)對(duì)應(yīng)的具體數(shù)據(jù)庫(kù)，并得到db中為Y的權(quán)限；如果db中為N，則檢查tables_priv中此數(shù)據(jù)庫(kù)對(duì)應(yīng)的具體表，取得表中的權(quán)限Y，以此類(lèi)推

4:如果在任何一個(gè)過(guò)程中權(quán)限驗(yàn)證不通過(guò),都會(huì)報(bào)錯(cuò)

1.2:緩存

mysql的緩存主要的作用是為了提升查詢(xún)的效率，緩存以key和value的哈希表形式存儲(chǔ)，key是具體的sql語(yǔ)句，value是結(jié)果的集合。如果無(wú)法命中緩存,就繼續(xù)走到分析器的的一步,如果命中緩存就直接返回給客戶(hù)端。不過(guò)需要注意的是在mysql的8.0版本以后，緩存被官方刪除掉了。之所以刪除掉,是因?yàn)椴樵?xún)緩存的失效非常頻繁,如果在一個(gè)寫(xiě)多讀少的環(huán)境中,緩存會(huì)頻繁的新增和失效。對(duì)于某些更新壓力大的數(shù)據(jù)庫(kù)來(lái)說(shuō)，查詢(xún)緩存的命中率會(huì)非常低,mysql為了維護(hù)緩存可能會(huì)出現(xiàn)一定的伸縮性的問(wèn)題,目前在5.6的版本中已經(jīng)默認(rèn)關(guān)閉了，比較推薦的一種做法是將緩存放在客戶(hù)端，性能大概會(huì)提升5倍左右

1.3:分析器

分析器的主要作用是將客戶(hù)端發(fā)過(guò)來(lái)的sql語(yǔ)句進(jìn)行分析，這將包括預(yù)處理與解析過(guò)程，在這個(gè)階段會(huì)解析sql語(yǔ)句的語(yǔ)義，并進(jìn)行關(guān)鍵詞和非關(guān)鍵詞進(jìn)行提取、解析，并組成一個(gè)解析樹(shù)。具體的關(guān)鍵詞包括不限定于以下：select/update/delete/or/in/where/group by/having/count/limit等.如果分析到語(yǔ)法錯(cuò)誤，會(huì)直接給客戶(hù)端拋出異常:ERROR:You have an error in your SQL syntax.

比如：select * from user where userId =1234;

在分析器中就通過(guò)語(yǔ)義規(guī)則器將select from where這些關(guān)鍵詞提取和匹配出來(lái),mysql會(huì)自動(dòng)判斷關(guān)鍵詞和非關(guān)鍵詞，將用戶(hù)的匹配字段和自定義語(yǔ)句識(shí)別出來(lái)。這個(gè)階段也會(huì)做一些校驗(yàn):比如校驗(yàn)當(dāng)前數(shù)據(jù)庫(kù)是否存在user表，同時(shí)假如User表中不存在userId這個(gè)字段同樣會(huì)報(bào)錯(cuò)：unknown column in field list.

1.4:優(yōu)化器

能夠進(jìn)入到優(yōu)化器階段表示sql是符合mysql的標(biāo)準(zhǔn)語(yǔ)義規(guī)則的并且可以執(zhí)行的，此階段主要是進(jìn)行sql語(yǔ)句的優(yōu)化，會(huì)根據(jù)執(zhí)行計(jì)劃進(jìn)行最優(yōu)的選擇,匹配合適的索引,選擇最佳的執(zhí)行方案。比如一個(gè)典型的例子是這樣的：

表T,對(duì)A、B、C列建立聯(lián)合索引，在進(jìn)行查詢(xún)的時(shí)候，當(dāng)sql查詢(xún)到的結(jié)果是:select xx where B=x and A=x and C=x.很多人會(huì)以為是用不到索引的，但其實(shí)會(huì)用到,雖然索引必須符合最左原則才能使用,但是本質(zhì)上,優(yōu)化器會(huì)自動(dòng)將這條sql優(yōu)化為:where A=x and B=x and C=X,這種優(yōu)化會(huì)為了底層能夠匹配到索引，同時(shí)在這個(gè)階段是自動(dòng)按照?qǐng)?zhí)行計(jì)劃進(jìn)行預(yù)處理,mysql會(huì)計(jì)算各個(gè)執(zhí)行方法的最佳時(shí)間,最終確定一條執(zhí)行的sql交給最后的執(zhí)行器

1.5:執(zhí)行器

在執(zhí)行器的階段,此時(shí)會(huì)調(diào)用存儲(chǔ)引擎的API,API會(huì)調(diào)用存儲(chǔ)引擎，主要有一下存儲(chǔ)的引擎，不過(guò)常用的還是myisam和innodb:

引擎以前的名字叫做:表處理器(其實(shí)這個(gè)名字我覺(jué)得更能表達(dá)它存在的意義)負(fù)責(zé)對(duì)具體的數(shù)據(jù)文件進(jìn)行操作,對(duì)sql的語(yǔ)義比如select或者update進(jìn)行分析,執(zhí)行具體的操作。在執(zhí)行完以后會(huì)將具體的操作記錄到binlog中,需要注意的一點(diǎn)是:select不會(huì)記錄到binlog中,只有update/delete/insert才會(huì)記錄到binlog中。而update會(huì)采用兩階段提交的方式,記錄都redolog中

二：執(zhí)行的狀態(tài)

可以通過(guò)命令:show full processlist,展示所有的處理進(jìn)程,主要包含了以下的狀態(tài)，表示服務(wù)器處理客戶(hù)端的狀態(tài)，狀態(tài)包含了從客戶(hù)端發(fā)起請(qǐng)求到后臺(tái)服務(wù)器處理的過(guò)程，包括加鎖的過(guò)程、統(tǒng)計(jì)存儲(chǔ)引擎的信息，排序數(shù)據(jù)、搜索中間表、發(fā)送數(shù)據(jù)等。囊括了所有的mysql的所有狀態(tài),其中具體的含義如下圖：

三:sql的執(zhí)行順序

事實(shí)上,sql并不是按照我們的書(shū)寫(xiě)順序來(lái)從前往后、左往右依次執(zhí)行的,它是按照固定的順序解析的,主要的作用就是從上一個(gè)階段的執(zhí)行返回結(jié)果來(lái)提供給下一階段使用，sql在執(zhí)行的過(guò)程中會(huì)有不同的臨時(shí)中間表，一般是按照如下順序：

例子: select distinct s.id from T t join S s on t.id=s.id where t.name="Yrion" group by t.mobile having count(*)>2 order by s.create_time limit 5;

3.1：from

第一步就是選擇出from關(guān)鍵詞后面跟的表,這也是sql執(zhí)行的第一步:表示要從數(shù)據(jù)庫(kù)中執(zhí)行哪張表。

實(shí)例說(shuō)明:在這個(gè)例子中就是首先從數(shù)據(jù)庫(kù)中找到表T

3.2:join on

join是表示要關(guān)聯(lián)的表，on是連接的條件。通過(guò)from和join on選擇出需要執(zhí)行的數(shù)據(jù)庫(kù)表T和S,產(chǎn)生笛卡爾積,生成T和S合并的臨時(shí)中間表Temp1。on:確定表的綁定關(guān)系,通過(guò)on產(chǎn)生臨時(shí)中間表Temp2.

實(shí)例說(shuō)明：找到表S,生成臨時(shí)中間表Temp1,然后找到表T的id和S的id相同的部分組成成表Temp2,Temp2里面包含著T和Sid相等的所有數(shù)據(jù)

3.3:where

where表示篩選,根據(jù)where后面的條件進(jìn)行過(guò)濾,按照指定的字段的值(如果有and連接符會(huì)進(jìn)行聯(lián)合篩選)從臨時(shí)中間表Temp2中篩選需要的數(shù)據(jù),注意如果在此階段找不到數(shù)據(jù)，會(huì)直接返回客戶(hù)端,不會(huì)往下進(jìn)行.這個(gè)過(guò)程會(huì)生成一個(gè)臨時(shí)中間表Temp3。注意在where中不可以使用聚合函數(shù)，聚合函數(shù)主要是(min\max\count\sum等函數(shù))

實(shí)例說(shuō)明:在temp2臨時(shí)表集合中找到T表的name="Yrion"的數(shù)據(jù),找到數(shù)據(jù)后會(huì)成臨時(shí)中間表Temp3,temp3里包含name列為"Yrion"的所有表數(shù)據(jù)

3.4:group by

group by是進(jìn)行分組，對(duì)where條件過(guò)濾后的臨時(shí)表Temp3按照固定的字段進(jìn)行分組,產(chǎn)生臨時(shí)中間表Temp4，這個(gè)過(guò)程只是數(shù)據(jù)的順序發(fā)生改變,而數(shù)據(jù)總量不會(huì)變化,表中的數(shù)據(jù)以組的形式存在

實(shí)例說(shuō)明:在temp3表數(shù)據(jù)中對(duì)mobile進(jìn)行分組,查找出mobile一樣的數(shù)據(jù),然后放到一起，產(chǎn)生temp4臨時(shí)表。

3.5:Having

對(duì)臨時(shí)中間表Temp4進(jìn)行聚合,這里可以為count等計(jì)數(shù)，然后產(chǎn)生中間表Temp5，在此階段可以使用select中的別名

實(shí)例說(shuō)明:在temp4臨時(shí)表中找出條數(shù)大于2的數(shù)據(jù),如果小于2直接被舍棄掉，然后生成臨時(shí)中間表temp5

3.6:select

對(duì)分組聚合完的表挑選出需要查詢(xún)的數(shù)據(jù),如果為*會(huì)解析為所有數(shù)據(jù),此時(shí)會(huì)產(chǎn)生中間表Temp6

實(shí)例說(shuō)明：在此階段就是對(duì)temp5臨時(shí)聚合表中S表中的id進(jìn)行篩選產(chǎn)生Temp6,此時(shí)temp6就只包含有s表的id列數(shù)據(jù),并且name="Yrion"，通過(guò)mobile分組數(shù)量大于2的數(shù)據(jù)

3.7:Distinct

distinct對(duì)所有的數(shù)據(jù)進(jìn)行去重,此時(shí)如果有min、max函數(shù)會(huì)執(zhí)行字段函數(shù)計(jì)算，然后產(chǎn)生臨時(shí)表Temp7

實(shí)例說(shuō)明:此階段對(duì)temp5中的數(shù)據(jù)進(jìn)行去重,引擎API會(huì)調(diào)用去重函數(shù)進(jìn)行數(shù)據(jù)的過(guò)濾,最終只保留id第一次出現(xiàn)的那條數(shù)據(jù),然后產(chǎn)生臨時(shí)中間表temp7

3.8:order by

會(huì)根據(jù)Temp7進(jìn)行順序排列或者逆序排列，然后插入臨時(shí)中間表Temp8，這個(gè)過(guò)程比較耗費(fèi)資源

實(shí)例說(shuō)明：這段會(huì)將所有temp7臨時(shí)表中的數(shù)據(jù)按照創(chuàng)建時(shí)間(create_time)進(jìn)行排序,這個(gè)過(guò)程也不會(huì)有列或者行損失

3.9:limit

limit對(duì)中間表Temp8進(jìn)行分頁(yè),產(chǎn)生臨時(shí)中間表Temp9,返回給客戶(hù)端。

實(shí)例說(shuō)明:在temp7中排好序的數(shù)據(jù),然后取前五條插入到Temp9這個(gè)臨時(shí)表中，最終返回給客戶(hù)端

ps:實(shí)際上這個(gè)過(guò)程也并不是絕對(duì)這樣的，中間mysql會(huì)有部分的優(yōu)化以達(dá)到最佳的優(yōu)化效果，比如在select篩選出找到的數(shù)據(jù)集

四:總結(jié)

了解mysql的執(zhí)行過(guò)程，以及sql的執(zhí)行順序，理解這些有助于我們對(duì)sql語(yǔ)句進(jìn)行優(yōu)化，以及明白mysql中的sql語(yǔ)句從寫(xiě)出來(lái)到最終執(zhí)行的軌跡，有助于我們對(duì)sql有比較深入和細(xì)致的理解，提高我們的數(shù)據(jù)庫(kù)理解能力。同時(shí)，對(duì)于復(fù)雜sql的執(zhí)行過(guò)程、編寫(xiě)都會(huì)有一定程度的意義。




推薦閱讀:
入門(mén): 最全的零基礎(chǔ)學(xué)Python的問(wèn)題  | 零基礎(chǔ)學(xué)了8個(gè)月的Python  | 實(shí)戰(zhàn)項(xiàng)目 |學(xué)Python就是這條捷徑

量化: 定投基金到底能賺多少錢(qián)？  | 我用Python對(duì)去年800只基金的數(shù)據(jù)分析  

干貨:爬取豆瓣短評(píng)，電影《后來(lái)的我們》 | 38年NBA最佳球員分析 |   從萬(wàn)眾期待到口碑撲街！唐探3令人失望  | 笑看新倚天屠龍記 | 燈謎答題王 ｜用Python做個(gè)海量小姐姐素描圖 ｜碟中諜這么火，我用機(jī)器學(xué)習(xí)做個(gè)迷你推薦系統(tǒng)電影

趣味:彈球游戲  | 九宮格  | 漂亮的花 | 兩百行Python《天天酷跑》游戲!

AI: 會(huì)做詩(shī)的機(jī)器人 | 給圖片上色 | 預(yù)測(cè)收入 | 碟中諜這么火，我用機(jī)器學(xué)習(xí)做個(gè)迷你推薦系統(tǒng)電影

小工具: Pdf轉(zhuǎn)Word，輕松搞定表格和水??！ | 一鍵把html網(wǎng)頁(yè)保存為pdf！|  再見(jiàn)PDF提取收費(fèi)！ | 用90行代碼打造最強(qiáng)PDF轉(zhuǎn)換器，word、PPT、excel、markdown、html一鍵轉(zhuǎn)換 | 制作一款釘釘?shù)蛢r(jià)機(jī)票提示器！ ｜60行代碼做了一個(gè)語(yǔ)音壁紙切換器天天看小姐姐！｜

年度爆款文案
1).臥槽！Pdf轉(zhuǎn)Word用Python輕松搞定！
2).學(xué)Python真香！我用100行代碼做了個(gè)網(wǎng)站，幫人PS旅行圖片，賺個(gè)雞腿吃
3).首播過(guò)億，火爆全網(wǎng)，我分析了《乘風(fēng)破浪的姐姐》，發(fā)現(xiàn)了這些秘密 
4).80行代碼！用Python做一個(gè)哆來(lái)A夢(mèng)分身 
5).你必須掌握的20個(gè)python代碼，短小精悍，用處無(wú)窮 
6).30個(gè)Python奇淫技巧集 
7).我總結(jié)的80頁(yè)《菜鳥(niǎo)學(xué)Python精選干貨.pdf》,都是干貨 
8).再見(jiàn)Python！我要學(xué)Go了！2500字深度分析！
9).發(fā)現(xiàn)一個(gè)舔狗福利！這個(gè)Python爬蟲(chóng)神器太爽了，自動(dòng)下載妹子圖片

點(diǎn)閱讀原文,領(lǐng)AI全套資料！

臥槽！深度剖析，原來(lái)大名鼎鼎的MySQL是這樣執(zhí)行的！

點(diǎn)閱讀原文,領(lǐng)AI全套資料！

臥槽！深度剖析，原來(lái)大名鼎鼎的MySQL是這樣執(zhí)行的！

點(diǎn)閱讀原文,領(lǐng)AI全套資料！