面試官：MySQL中SQL語(yǔ)句是如何執(zhí)行的？
該篇章將開(kāi)始整理MySQL的優(yōu)化，不過(guò)開(kāi)始之前，我們想了解清楚那就是MySQL是怎么執(zhí)行的。
文章目錄
MySQL驅(qū)動(dòng) 應(yīng)用系統(tǒng)數(shù)據(jù)庫(kù)連接池 MySQL數(shù)據(jù)庫(kù)連接池 SQL執(zhí)行過(guò)程 線程監(jiān)聽(tīng)：監(jiān)聽(tīng)網(wǎng)絡(luò)請(qǐng)求中的SQL語(yǔ)句 SQL接口：負(fù)責(zé)處理接收到的SQL語(yǔ)句 查詢解析器：讓MySQL能看懂SQL語(yǔ)句 查詢優(yōu)化器：選擇最優(yōu)的查詢路徑 存儲(chǔ)引擎接口：真正執(zhí)行SQL語(yǔ)句 執(zhí)行器：根據(jù)執(zhí)行計(jì)劃調(diào)用存儲(chǔ)引擎的接口 總結(jié)
MySQL驅(qū)動(dòng)
大家都知道，我們?nèi)绻贘ava系統(tǒng)中去訪問(wèn)一個(gè)MySQL數(shù)據(jù)庫(kù)，必須得在系統(tǒng)的依賴中加入一個(gè)MySQL驅(qū)動(dòng)，有了這個(gè)MySQL驅(qū)動(dòng)才能跟MySQL數(shù)據(jù)庫(kù)建立連接，然后執(zhí)行各種各樣的SQL語(yǔ)句。
MySQL驅(qū)動(dòng)，他會(huì)在底層跟數(shù)據(jù)庫(kù)建立網(wǎng)絡(luò)連接，有網(wǎng)絡(luò)連接，接著才能去發(fā)送請(qǐng)求給數(shù)據(jù)庫(kù)服務(wù)器！當(dāng)我們跟數(shù)據(jù)庫(kù)之間有了網(wǎng)絡(luò)連接之后，我們的Java代碼才能基于這個(gè)連接去執(zhí)行各種各樣的增刪改查SQL語(yǔ)句。
應(yīng)用系統(tǒng)數(shù)據(jù)庫(kù)連接池
假設(shè)我們的系統(tǒng)是部署在Tomcat中的，那么Tomcat本身肯定是有多個(gè)線程來(lái)并發(fā)的處理同時(shí)接收到的多個(gè)請(qǐng)求的，如果Tomcat中的多個(gè)線程并發(fā)處理多個(gè)請(qǐng)求的時(shí)候，都要去搶奪一個(gè)連接去訪問(wèn)數(shù)據(jù)庫(kù)的話，那效率肯定是很低下的。
如果Tomcat中的每個(gè)線程在每次訪問(wèn)數(shù)據(jù)庫(kù)的時(shí)候，都基于MySQL驅(qū)動(dòng)去創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)連接，然后執(zhí)行SQL語(yǔ)句，然后執(zhí)行完之后再銷毀這個(gè)數(shù)據(jù)庫(kù)連接，可能Tomcat中上百個(gè)線程會(huì)并發(fā)的頻繁創(chuàng)建數(shù)據(jù)庫(kù)連接，執(zhí)行SQL語(yǔ)句，然后頻繁的銷毀數(shù)據(jù)庫(kù)連接，也是非常不好的，因?yàn)槊看谓⒁粋€(gè)數(shù)據(jù)庫(kù)連接都很耗時(shí)，好不容易建立好了連接，執(zhí)行完了SQL語(yǔ)句，你還把數(shù)據(jù)庫(kù)連接給銷毀了，下一次再重新建立數(shù)據(jù)庫(kù)連接，那肯定是效率很低下的！
所以一般我們必須要使用一個(gè)數(shù)據(jù)庫(kù)連接池，也就是說(shuō)在一個(gè)池子里維持多個(gè)數(shù)據(jù)庫(kù)連接，讓多個(gè)線程使用里面的不同的數(shù)據(jù)庫(kù)連接去執(zhí)行SQL語(yǔ)句，然后執(zhí)行完SQL語(yǔ)句之后，不要銷毀這個(gè)數(shù)據(jù)庫(kù)連接，而是把連接放回池子里，后續(xù)還可以繼續(xù)使用。基于這樣的一個(gè)數(shù)據(jù)庫(kù)連接池的機(jī)制，就可以解決多個(gè)線程并發(fā)的使用多個(gè)數(shù)據(jù)庫(kù)連接去執(zhí)行SQL語(yǔ)句的問(wèn)題，而且還避免了數(shù)據(jù)庫(kù)連接使用完之后就銷毀的問(wèn)題。
常見(jiàn)的數(shù)據(jù)庫(kù)連接池有DBCP，C3P0，Druid
MySQL數(shù)據(jù)庫(kù)連接池
任何一個(gè)系統(tǒng)都會(huì)有一個(gè)數(shù)據(jù)庫(kù)連接池去訪問(wèn)數(shù)據(jù)庫(kù)，也就是說(shuō)這個(gè)系統(tǒng)會(huì)有多個(gè)數(shù)據(jù)庫(kù)連接，供多線程并發(fā)的使用。同時(shí)我們可能會(huì)有多個(gè)系統(tǒng)同時(shí)去訪問(wèn)一個(gè)數(shù)據(jù)庫(kù)，這都是有可能的。MySQL也必然要維護(hù)與系統(tǒng)之間的多個(gè)連接。
實(shí)際上MySQL中的連接池就是維護(hù)了與系統(tǒng)之間的多個(gè)數(shù)據(jù)庫(kù)連接。除此之外，你的系統(tǒng)每次跟MySQL建立連接的時(shí)候，還會(huì)根據(jù)你傳遞過(guò)來(lái)的賬號(hào)和密碼，進(jìn)行賬號(hào)密碼的驗(yàn)證，庫(kù)表權(quán)限的驗(yàn)證。
SQL執(zhí)行過(guò)程
線程監(jiān)聽(tīng)：監(jiān)聽(tīng)網(wǎng)絡(luò)請(qǐng)求中的SQL語(yǔ)句
現(xiàn)在假設(shè)我們的數(shù)據(jù)庫(kù)服務(wù)器的連接池中的某個(gè)連接接收到了網(wǎng)絡(luò)請(qǐng)求，假設(shè)就是一條SQL語(yǔ)句，那么大家先思考一個(gè)問(wèn)題，誰(shuí)負(fù)責(zé)從這個(gè)連接中去監(jiān)聽(tīng)網(wǎng)絡(luò)請(qǐng)求？誰(shuí)負(fù)責(zé)從網(wǎng)絡(luò)連接里把請(qǐng)求數(shù)據(jù)讀取出來(lái)？
我想很多人恐怕都沒(méi)思考過(guò)這個(gè)問(wèn)題，但是如果大家對(duì)計(jì)算機(jī)基礎(chǔ)知識(shí)有一個(gè)簡(jiǎn)單了解的話，應(yīng)該或多或少知道一點(diǎn)，那就是網(wǎng)絡(luò)連接必須得分配給一個(gè)線程去進(jìn)行處理，由一個(gè)線程來(lái)監(jiān)聽(tīng)請(qǐng)求以及讀取請(qǐng)求數(shù)據(jù)，比如從網(wǎng)絡(luò)連接中讀取和解析出來(lái)一條我們的系統(tǒng)發(fā)送過(guò)去的SQL語(yǔ)句，如下圖所示
SQL接口：負(fù)責(zé)處理接收到的SQL語(yǔ)句
當(dāng)MySQL內(nèi)部的工作線程從一個(gè)網(wǎng)絡(luò)連接中讀取出來(lái)一個(gè)SQL語(yǔ)句之后，此時(shí)會(huì)如何來(lái)執(zhí)行這個(gè)SQL語(yǔ) 句呢？
MySQL內(nèi)部首先提供了一個(gè)組件，就是SQL接口（SQL Interface），他是一套執(zhí)行SQL語(yǔ)句的接口，專門用于執(zhí)行我們發(fā)送給MySQL的那些增刪改查的SQL語(yǔ)句。因此MySQL的工作線程接收到SQL語(yǔ)句之后，就會(huì)轉(zhuǎn)交給SQL接口去執(zhí)行，如下圖：
查詢解析器：讓MySQL能看懂SQL語(yǔ)句
SQL接口怎么執(zhí)行SQL語(yǔ)句呢？你直接把SQL語(yǔ)句交給MySQL，他能看懂和理解這些SQL語(yǔ)句嗎？
比如我們來(lái)舉一個(gè)例子，現(xiàn)在我們有這么一個(gè)SQL語(yǔ)句：
select?id,name,age?from?users?where?id=1
MySQL自己本身也是一個(gè)系統(tǒng)，是一個(gè)數(shù)據(jù)庫(kù)管理系統(tǒng)，他沒(méi)法直接理解這些SQL語(yǔ)句！
所以此時(shí)有一個(gè)關(guān)鍵的組件要出場(chǎng)了，那就是查詢解析器。
這個(gè)查詢解析器（Parser）就是負(fù)責(zé)對(duì)SQL語(yǔ)句進(jìn)行解析的，比如對(duì)上面那個(gè)SQL語(yǔ)句進(jìn)行一下拆解，拆解成以下幾個(gè)部分：
我們現(xiàn)在要從“users”表里查詢數(shù)據(jù)（from users） 查詢“id”字段的值等于1的那行數(shù)據(jù)（where id=1） 對(duì)查出來(lái)的那行數(shù)據(jù)要提取里面的“id,name,age”三個(gè)字段（select id,name,age）
所謂的SQL解析，就是按照既定的SQL語(yǔ)法，對(duì)我們按照SQL語(yǔ)法規(guī)則編寫的SQL語(yǔ)句進(jìn)行解析，然后理解這個(gè)SQL語(yǔ)句要干什么事情，如下圖所示
查詢優(yōu)化器：選擇最優(yōu)的查詢路徑
當(dāng)我們通過(guò)解析器理解了SQL語(yǔ)句要干什么之后，接著會(huì)找查詢優(yōu)化器（Optimizer）來(lái)選擇一個(gè)最優(yōu)的查詢路徑。
在語(yǔ)法分析階段：會(huì)對(duì)SQL語(yǔ)句進(jìn)行分析，比如如下sql語(yǔ)句
select?A.name,?A.age?from?tb_student?A?where?A.age=18?and?A.name='張三'
該SQL在被執(zhí)行之前，會(huì)先進(jìn)行語(yǔ)法檢測(cè)，判斷有無(wú)錯(cuò)誤，之后會(huì)分析出如下兩個(gè)sql查詢條件，
where A.age=18 where A.name=‘張三’
問(wèn)題就來(lái)了，這兩個(gè)條件誰(shuí)先執(zhí)行有區(qū)別嗎？答案是肯定的，在我們編寫SQL的時(shí)候，都會(huì)遵循一個(gè)原則，就是把區(qū)分度最高的放在左側(cè)。
where A.name=‘張三’ AND A.age=18 where A.age=18 AND A.name=‘張三’
上面這就是一個(gè)最簡(jiǎn)單的SQL語(yǔ)句的兩種實(shí)現(xiàn)路徑，其實(shí)我們會(huì)發(fā)現(xiàn)，要完成這個(gè)SQL語(yǔ)句的目標(biāo)，兩個(gè)路徑都可以做到，但是哪一種更好呢？顯然感覺(jué)上是第一種查詢路徑更好一些。因?yàn)樵谖覀償?shù)據(jù)庫(kù)系統(tǒng)中，叫張三的人可能只有幾百個(gè)，但是年齡為18歲的，可能有幾萬(wàn)個(gè)。
所以查詢優(yōu)化器大概就是干這個(gè)的，他會(huì)針對(duì)你編寫的幾十行、幾百行甚至上千行的復(fù)雜SQL語(yǔ)句生成查詢路徑樹(shù)，然后從里面選擇一條最優(yōu)的查詢路徑出來(lái)。相當(dāng)于他會(huì)告訴你，你應(yīng)該按照一個(gè)什么樣的步驟和順序，去執(zhí)行哪些操作，然后一步一步的把SQL語(yǔ)句就給完成了。
存儲(chǔ)引擎接口：真正執(zhí)行SQL語(yǔ)句
把查詢優(yōu)化器選擇的最優(yōu)查詢路徑，也就是你到底應(yīng)該按照一個(gè)什么樣的順序和步驟去執(zhí)行這個(gè)SQL語(yǔ)句的計(jì)劃，把這個(gè)計(jì)劃交給底層的存儲(chǔ)引擎去真正的執(zhí)行。這個(gè)存儲(chǔ)引擎是MySQL的架構(gòu)設(shè)計(jì)中很有特色的一個(gè)環(huán)節(jié)。
真正在執(zhí)行SQL語(yǔ)句的時(shí)候，要不然是更新數(shù)據(jù)，要不然是查詢數(shù)據(jù)，那么數(shù)據(jù)你覺(jué)得存放在哪里？
以對(duì)數(shù)據(jù)庫(kù)而言，我們的數(shù)據(jù)要不然是放在內(nèi)存里，要不然是放在磁盤文件里。
那么現(xiàn)在問(wèn)題來(lái)了，我們已經(jīng)知道一個(gè)SQL語(yǔ)句要如何執(zhí)行了，但是我們現(xiàn)在怎么知道哪些數(shù)據(jù)在內(nèi)存里？哪些數(shù)據(jù)在磁盤里？我們執(zhí)行的時(shí)候是更新內(nèi)存的數(shù)據(jù)？還是更新磁盤的數(shù)據(jù)？我們?nèi)绻麓疟P的數(shù)據(jù)，是先查詢哪個(gè)磁盤文件，再更新哪個(gè)磁盤文件？
所以這個(gè)時(shí)候就需要存儲(chǔ)引擎了，存儲(chǔ)引擎其實(shí)就是執(zhí)行SQL語(yǔ)句的，他會(huì)按照一定的步驟去查詢內(nèi)存緩存數(shù)據(jù)，更新磁盤數(shù)據(jù)，查詢磁盤數(shù)據(jù)，等等，執(zhí)行諸如此類的一系列的操作，如下圖所示。
存儲(chǔ)引擎支持各種各樣的存儲(chǔ)引擎的，比如我們常見(jiàn)的InnoDB、MyISAM、Memory等等，我們是可以選擇使用哪種存儲(chǔ)引擎來(lái)負(fù)責(zé)具體的SQL語(yǔ)句執(zhí)行的。當(dāng)然現(xiàn)在MySQL一般都是使用InnoDB存儲(chǔ)引擎。
執(zhí)行器：根據(jù)執(zhí)行計(jì)劃調(diào)用存儲(chǔ)引擎的接口
存儲(chǔ)引擎可以幫助我們?nèi)ピL問(wèn)內(nèi)存以及磁盤上的數(shù)據(jù)，那么是誰(shuí)來(lái)調(diào)用存儲(chǔ)引擎的接口呢？
其實(shí)我們現(xiàn)在還漏了一個(gè)執(zhí)行器的概念，這個(gè)執(zhí)行器會(huì)根據(jù)優(yōu)化器選擇的執(zhí)行方案，去調(diào)用存儲(chǔ)引擎的接口按照一定的順序和步驟，就把SQL語(yǔ)句的邏輯給執(zhí)行了。
執(zhí)行器就會(huì)去根據(jù)我們的優(yōu)化器生成的一套執(zhí)行計(jì)劃，然后不停的調(diào)用存儲(chǔ)引擎的各種接口去完成SQL
語(yǔ)句的執(zhí)行計(jì)劃，大致就是不停的更新或者提取一些數(shù)據(jù)出來(lái)。
總結(jié)
本文介紹內(nèi)容總體如下流程圖所示：
