引言

? ?在上篇文章中补鼻，我們以《MySQL架構(gòu)篇》拉開(kāi)了MySQL數(shù)據(jù)庫(kù)的的序幕，上篇文章中將MySQL分層架構(gòu)中的每一層都進(jìn)行了詳細(xì)闡述誓禁。而在本篇中懈息，則會(huì)進(jìn)一步站在一條SQL的角度，從SQL的誕生開(kāi)始摹恰，到SQL執(zhí)行辫继、數(shù)據(jù)返回等全鏈路進(jìn)行分析。

? ?在此之前呢俗慈，其實(shí)也寫(xiě)過(guò)兩篇類似的姊妹篇姑宽，第一篇講的是《一個(gè)網(wǎng)絡(luò)請(qǐng)求的神奇之旅！》姜盈，在其中化身一個(gè)網(wǎng)絡(luò)請(qǐng)求低千，切身感受了從瀏覽器發(fā)出后配阵，到響應(yīng)數(shù)據(jù)的返回馏颂。而在更早的時(shí)候，《JVM系列》中也有一篇文章棋傍，講的是《一個(gè)Java對(duì)象從誕生到死亡的歷程》救拉，其中聊到了Java對(duì)象是如何誕生的，運(yùn)行時(shí)會(huì)經(jīng)歷什么過(guò)程瘫拣？使用結(jié)束后又是如何回收的亿絮。

在本篇中，則會(huì)在站在數(shù)據(jù)庫(kù)的視角麸拄，再次感受“一條SQL多姿多彩的歷程”派昧！你如果認(rèn)真的看完了“一個(gè)請(qǐng)求、一個(gè)對(duì)象拢切、一條SQL”這三部曲后蒂萎，相信你對(duì)于程序開(kāi)發(fā)又會(huì)有一個(gè)全新的深刻認(rèn)知。

一淮椰、一條SQL是如何誕生的五慈？

? ?SQL語(yǔ)句都誕生于客戶端，主要有兩種方式產(chǎn)生一條SQL主穗，一種是由開(kāi)發(fā)者自己手動(dòng)編寫(xiě)泻拦，另一種則是相關(guān)的ORM框架自動(dòng)生成，一般情況下忽媒，MySQL運(yùn)行過(guò)程中收到的大部分SQL都是由ORM框架生成的争拐，比如Java中的MyBatis、Hibernate框架等晦雨。

? ?同時(shí)架曹，SQL生成的時(shí)機(jī)一般都與用戶的請(qǐng)求有關(guān)灯抛，當(dāng)用戶在系統(tǒng)中進(jìn)行了某項(xiàng)操作，一般都會(huì)產(chǎn)生一條SQL音瓷，例如我們?cè)跒g覽器上輸入如下網(wǎng)址：

https://juejin.cn/user/862486453028888/posts

此時(shí)对嚼，就會(huì)先請(qǐng)求掘金的服務(wù)器，然后由掘金內(nèi)部實(shí)現(xiàn)中的ORM框架绳慎，根據(jù)請(qǐng)求參數(shù)生成一條SQL纵竖，類似于下述的偽SQL：

select * from juejin_article where userid = 862486453028888;

這條SQL大致描述的意思就是：根據(jù)用戶請(qǐng)求的「作者ID」，在掘金數(shù)據(jù)庫(kù)的文章表中杏愤，查詢?cè)撟髡叩乃形恼滦畔ⅰ?/p>

? ?從上述這個(gè)案例中可以明顯感受出來(lái)靡砌，用戶瀏覽器上看到的數(shù)據(jù)一般都來(lái)自于數(shù)據(jù)庫(kù)，而數(shù)據(jù)庫(kù)執(zhí)行的SQL則源自于用戶操作珊楼，兩者是相輔相成的關(guān)系通殃，也包括任何寫(xiě)操作（增、刪厕宗、改）画舌，本質(zhì)上也會(huì)被轉(zhuǎn)換一條條SQL，也舉個(gè)簡(jiǎn)單的例子：

# 請(qǐng)求網(wǎng)址（Request URL）
https://www.xxx.com/user/register

# 請(qǐng)求參數(shù)（Request Param）
{
    user_name : "竹子愛(ài)熊貓",
    user_pwd : "123456",
    user_sex : "男",
    user_phone : "18888888888",
    ......
}
# 這里對(duì)于用戶密碼的處理不夠嚴(yán)謹(jǐn)已慢，沒(méi)有做加密操作不要在意~

比如上述這個(gè)用戶注冊(cè)的案例曲聂，當(dāng)用戶在網(wǎng)頁(yè)上點(diǎn)擊「注冊(cè)」按鈕時(shí)，會(huì)向目標(biāo)網(wǎng)站的服務(wù)器發(fā)送一個(gè)post請(qǐng)求佑惠，緊接著同樣會(huì)根據(jù)請(qǐng)求參數(shù)朋腋，生成一條SQL，如下：

insert into table_user (user_name,user_pwd,user_sex,user_phone,....)
    VALUES ("竹子愛(ài)熊貓", "123456", "男", "18888888888", ....);

也就是說(shuō)膜楷，一條SQL的誕生都源自于一個(gè)用戶請(qǐng)求旭咽，在開(kāi)發(fā)程序時(shí)，SQL的大體邏輯我們都會(huì)由業(yè)務(wù)層的編碼決定赌厅，具體的SQL語(yǔ)句則是根據(jù)用戶的請(qǐng)求參數(shù)穷绵，以及提前定制好的“SQL骨架”拼揍而成。當(dāng)然察蹲，在Java程序或其他語(yǔ)言編寫(xiě)的程序中请垛，只能生成SQL，而SQL真正的執(zhí)行工作是需要交給數(shù)據(jù)庫(kù)去完成的洽议。

二宗收、一條SQL執(zhí)行前會(huì)經(jīng)歷的過(guò)程

? ?經(jīng)過(guò)上一步之后，一條完整的SQL就誕生了亚兄，為了SQL能夠正常執(zhí)行混稽，首先會(huì)先去獲取一個(gè)數(shù)據(jù)庫(kù)連接對(duì)象，上篇關(guān)于MySQL的架構(gòu)篇曾聊到過(guò)，MySQL連接層中會(huì)維護(hù)著一個(gè)名為「連接池」的玩意兒匈勋，但相信大家也都接觸過(guò)「數(shù)據(jù)庫(kù)連接池」這個(gè)東西礼旅，比如Java中的C3P0、Druid洽洁、DBCP....等各類連接池痘系。

那此時(shí)在這里可以思考一個(gè)問(wèn)題，為什么數(shù)據(jù)庫(kù)自己維護(hù)了連接池的情況下饿自，在MySQL客戶端中還需要再次維護(hù)一個(gè)數(shù)據(jù)庫(kù)連接池呢汰翠？接下來(lái)一起聊一聊。

2.1昭雌、數(shù)據(jù)庫(kù)連接池的必要性

? ?眾所周知复唤，當(dāng)要在Java中創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)連接時(shí)，首先會(huì)去讀取配置文件中的連接地址烛卧、賬號(hào)密碼等信息佛纫，然后根據(jù)配置的地址信息，發(fā)起網(wǎng)絡(luò)請(qǐng)求獲取數(shù)據(jù)庫(kù)連接對(duì)象总放。在這個(gè)過(guò)程中呈宇，由于涉及到了網(wǎng)絡(luò)請(qǐng)求，那此時(shí)必然會(huì)先經(jīng)歷TCP三次握手的過(guò)程间聊，同時(shí)獲取到連接對(duì)象完成SQL操作后攒盈，又要釋放這個(gè)數(shù)據(jù)庫(kù)連接抵拘，此時(shí)又需要經(jīng)歷TCP四次揮手過(guò)程哎榴。

從上面的描述中可以明顯感知出，在Java中創(chuàng)建僵蛛、關(guān)閉數(shù)據(jù)庫(kù)連接的過(guò)程尚蝌，過(guò)程開(kāi)銷其實(shí)比較大，而在程序上線后充尉，又需要頻繁進(jìn)行數(shù)據(jù)庫(kù)操作飘言。因此如果每次操作數(shù)據(jù)庫(kù)時(shí)，都獲取新的連接對(duì)象驼侠，那整個(gè)Java程序至少會(huì)有四分之一的時(shí)間內(nèi)在做TCP三次握手/四次揮手工作姿鸿，這對(duì)整個(gè)系統(tǒng)造成的后果可想而知....

也正是由于上述原因，因此大名鼎鼎的「數(shù)據(jù)庫(kù)連接池」登場(chǎng)了倒源，「數(shù)據(jù)庫(kù)連接池」和「線程池」的思想相同苛预，會(huì)將數(shù)據(jù)庫(kù)連接這種較為珍貴的資源，利用池化技術(shù)對(duì)這種資源進(jìn)行維護(hù)笋熬。也就代表著之后需要進(jìn)行數(shù)據(jù)庫(kù)操作時(shí)热某，不需要自己去建立連接了，而是直接從「數(shù)據(jù)庫(kù)連接池」中獲取，用完之后再歸還給連接池昔馋，以此達(dá)到復(fù)用的效果筹吐。

當(dāng)然，連接池中維護(hù)的連接對(duì)象也不會(huì)一直都在秘遏，當(dāng)長(zhǎng)時(shí)間未進(jìn)行SQL操作時(shí)丘薛，連接池也會(huì)銷毀這些連接對(duì)象，而后當(dāng)需要時(shí)再次創(chuàng)建邦危，不過(guò)何時(shí)創(chuàng)建榔袋、何時(shí)銷毀、連接數(shù)限制等等這些工作铡俐，都交給了連接池去完成凰兑，無(wú)需開(kāi)發(fā)者自身再去關(guān)注。

在Java中审丘，目前最常用的數(shù)據(jù)庫(kù)連接池就是阿里的Druid吏够，一般咱們都會(huì)用它作為生產(chǎn)環(huán)境中的連接池：
[圖片上傳失敗...(image-7aa740-1670309104421)]

目前Druid已經(jīng)被阿里貢獻(xiàn)給Apache軟件基金會(huì)維護(hù)了~

OK~，回到前面拋出的問(wèn)題滩报，有了MySQL連接池為何還需要在客戶端維護(hù)一個(gè)連接池锅知？

對(duì)于這個(gè)問(wèn)題，相信大家在心里多少都有點(diǎn)答案了脓钾，原因很簡(jiǎn)單硝岗，兩者都是利用池化技術(shù)去達(dá)到復(fù)用資源短条、節(jié)省開(kāi)銷、提升性能的目的，只不過(guò)針對(duì)的方向不同背零。

MySQL的連接池主要是為了實(shí)現(xiàn)復(fù)用線程的目的赴魁，因?yàn)槊總€(gè)數(shù)據(jù)庫(kù)連接在MySQL中都會(huì)使用一條線程維護(hù)拐邪，而每次為客戶端分配連接對(duì)象時(shí)丹弱，都需要經(jīng)歷創(chuàng)建線程、分配椊靼空間....這些繁重的工作固歪，這個(gè)過(guò)程需要時(shí)間，同時(shí)資源開(kāi)銷也不小胯努，所以MySQL利用池化技術(shù)解決了這些問(wèn)題牢裳。

而客戶端的連接池，主要是為了實(shí)現(xiàn)復(fù)用數(shù)據(jù)庫(kù)連接的目的叶沛，因?yàn)槊看?code>SQL操作都需要經(jīng)過(guò)TCP三次握手/四次揮手的過(guò)程蒲讯，過(guò)程同樣耗時(shí)且占用資源，因此也利用池化技術(shù)解決了這個(gè)問(wèn)題恬汁。

其實(shí)也可以這樣理解伶椿，MySQL連接池維護(hù)的是工作線程辜伟，客戶端連接池則維護(hù)的是網(wǎng)絡(luò)連接。

2.2脊另、SQL執(zhí)行前會(huì)發(fā)生的事情

? ?回歸本文主題导狡，當(dāng)完整的SQL生成后，會(huì)先去連接池中嘗試獲取一個(gè)連接對(duì)象偎痛，那接下來(lái)會(huì)發(fā)生什么事情呢旱捧？如下圖：
[圖片上傳失敗...(image-85f48b-1670309104421)]
當(dāng)嘗試從連接池中獲取連接時(shí)，如果此時(shí)連接池中有空閑連接踩麦，可以直接拿到復(fù)用枚赡，但如果沒(méi)有，則要先判斷一下當(dāng)前池中的連接數(shù)是否已達(dá)到最大連接數(shù)谓谦，如果連接數(shù)已經(jīng)滿了贫橙，當(dāng)前線程則需要等待其他線程釋放連接對(duì)象，沒(méi)滿則可以直接再創(chuàng)建一個(gè)新的數(shù)據(jù)庫(kù)連接使用反粥。

假設(shè)此時(shí)連接池中沒(méi)有空閑連接卢肃，需要再次創(chuàng)建一個(gè)新連接，那么就會(huì)先發(fā)起網(wǎng)絡(luò)請(qǐng)求建立連接才顿。

首先會(huì)經(jīng)過(guò)《TCP的三次握手過(guò)程》莫湘，對(duì)于這塊就不再細(xì)聊了，畢竟之前聊過(guò)很多次了郑气。當(dāng)網(wǎng)絡(luò)連接建立成功后幅垮，也就等價(jià)于在MySQL中創(chuàng)建了一個(gè)客戶端會(huì)話，然后會(huì)發(fā)生下圖一系列工作：
[圖片上傳失敗...(image-2d7987-1670309104421)]

• ①首先會(huì)驗(yàn)證客戶端的用戶名和密碼是否正確：
  • 如果用戶名不存在或密碼錯(cuò)誤尾组，則拋出1045的錯(cuò)誤碼及錯(cuò)誤信息忙芒。
  • 如果用戶名和密碼驗(yàn)證通過(guò)，則進(jìn)入第②步演怎。
• ②判斷MySQL連接池中是否存在空閑線程：
  • 存在：直接從連接池中分配一條空閑線程維護(hù)當(dāng)前客戶端的連接匕争。
  • 不存在：創(chuàng)建一條新的工作線程（映射內(nèi)核線程、分配椧空間....）。
• ③工作線程會(huì)先查詢MySQL自身的用戶權(quán)限表拍皮，獲取當(dāng)前登錄用戶的權(quán)限信息并授權(quán)歹叮。

到這里為止，執(zhí)行SQL前的準(zhǔn)備工作就完成了铆帽，已經(jīng)打通了執(zhí)行SQL的通道咆耿，下一步則是準(zhǔn)備執(zhí)行SQL語(yǔ)句，工作線程會(huì)等待客戶端將SQL傳遞過(guò)來(lái)爹橱。

三萨螺、一條SQL語(yǔ)句在數(shù)據(jù)庫(kù)中是如何執(zhí)行的？

? ?經(jīng)過(guò)連接層的一系列工作后，接著客戶端會(huì)將要執(zhí)行的SQL語(yǔ)句通過(guò)連接發(fā)送過(guò)來(lái)慰技，然后會(huì)進(jìn)行MySQL服務(wù)層進(jìn)行處理椭盏，不過(guò)根據(jù)用戶的操作不同，MySQL執(zhí)行SQL語(yǔ)句時(shí)也會(huì)存在些許差異吻商，這里是指讀操作和寫(xiě)操作掏颊，兩者SQL的執(zhí)行過(guò)程并不相同，下面先來(lái)看看select語(yǔ)句的執(zhí)行過(guò)程艾帐。

3.1乌叶、一條查詢SQL的執(zhí)行過(guò)程

在分析查詢SQL的執(zhí)行流程之前，咱們先模擬一個(gè)案例柒爸，以便于后續(xù)分析：

-- SQL語(yǔ)句
SELECT user_id FROM `zz_user` WHERE user_sex = "男" AND user_name = "竹子④號(hào)";

-- 表數(shù)據(jù)
+---------+--------------+----------+-------------+
| user_id | user_name    | user_sex | user_phone  |
+---------+--------------+----------+-------------+
|       1 | 竹子①號(hào)      | 男       | 18888888888 |
|       2 | 竹子②號(hào)      | 男       | 13588888888 |
|       3 | 竹子③號(hào)      | 男       | 15688888888 |
|       4 | 熊貓①號(hào)      | 女       | 13488888888 |
|       5 | 熊貓②號(hào)      | 女       | 18588888888 |
|       6 | 竹子④號(hào)      | 男       | 17777777777 |
|       7 | 熊貓③號(hào)      | 女       | 16666666666 |
+---------+--------------+----------+-------------+

先上個(gè)SQL執(zhí)行的完整流程圖准浴，后續(xù)再逐步分析每個(gè)過(guò)程：
[圖片上傳失敗...(image-41304b-1670309104421)]

• ①先將SQL發(fā)送給SQL接口，SQL接口會(huì)對(duì)SQL語(yǔ)句進(jìn)行哈希處理捎稚。
• ②SQL接口在緩存中根據(jù)哈希值檢索數(shù)據(jù)兄裂，如果緩存中有則直接返回?cái)?shù)據(jù)。
• ③緩存中未命中時(shí)會(huì)將SQL交給解析器阳藻，解析器會(huì)判斷SQL語(yǔ)句是否正確：
  • 錯(cuò)誤：拋出1064錯(cuò)誤碼及相關(guān)的語(yǔ)法錯(cuò)誤信息晰奖。
  • 正確：將SQL語(yǔ)句交給優(yōu)化器處理，進(jìn)入第④步腥泥。
• ④優(yōu)化器根據(jù)SQL制定出不同的執(zhí)行方案匾南，并擇選出最優(yōu)的執(zhí)行計(jì)劃。
• ⑤工作線程根據(jù)執(zhí)行計(jì)劃蛔外，調(diào)用存儲(chǔ)引擎所提供的API獲取數(shù)據(jù)蛆楞。
• ⑥存儲(chǔ)引擎根據(jù)API調(diào)用方的操作，去磁盤(pán)中檢索數(shù)據(jù)（索引夹厌、表數(shù)據(jù)....）豹爹。
• ⑦發(fā)送磁盤(pán)IO后，對(duì)于磁盤(pán)中符合要求的數(shù)據(jù)逐條返回給SQL接口矛纹。
• ⑧SQL接口會(huì)對(duì)所有的結(jié)果集進(jìn)行處理（剔除列臂聋、合并數(shù)據(jù)....）并返回。

上述是一個(gè)簡(jiǎn)單的流程概述或南，一般情況下查詢SQL的執(zhí)行都會(huì)經(jīng)過(guò)這些步驟孩等，下面再將每一步拆開(kāi)詳細(xì)聊一聊。

SQL接口會(huì)干的工作

? ?當(dāng)客戶端將SQL發(fā)送過(guò)來(lái)之后采够，SQL緊接著會(huì)交給SQL接口處理肄方，首先會(huì)對(duì)SQL做哈希處理，也就是根據(jù)SQL語(yǔ)句計(jì)算出一個(gè)哈希值蹬癌，然后去「查詢緩存」中比對(duì)权她，如果緩存中存在相同的哈希值虹茶，則代表著之前緩存過(guò)相同SQL語(yǔ)句的結(jié)果，那此時(shí)則直接從緩存中獲取結(jié)果并響應(yīng)給客戶端隅要。

在這里蝴罪，如果沒(méi)有從緩存中查詢到數(shù)據(jù)，緊接著會(huì)將SQL語(yǔ)句交給解析器去處理拾徙。

SQL接口除開(kāi)對(duì)SQL進(jìn)行上述的處理外洲炊，后續(xù)還會(huì)負(fù)責(zé)處理結(jié)果集（稍后分析）。

解析器中會(huì)干的工作

? ?解析器收到SQL后尼啡，會(huì)開(kāi)始檢測(cè)SQL是否正確暂衡，也就是做詞法分析、語(yǔ)義分析等工作崖瞭，在這一步狂巢，解析器會(huì)根據(jù)SQL語(yǔ)言的語(yǔ)法規(guī)則，判斷客戶端傳遞的SQL語(yǔ)句是否合規(guī)书聚，如果不合規(guī)就會(huì)返回1064錯(cuò)誤碼及錯(cuò)誤信息：

ERROR 1064 (42000): You have an error in your SQL syntax; check....

? ?但如果SQL語(yǔ)句沒(méi)有問(wèn)題唧领，此時(shí)就會(huì)對(duì)SQL語(yǔ)句進(jìn)行關(guān)鍵字分析，也就是根據(jù)SQL中的SELECT雌续、UPDATE斩个、DELETE等關(guān)鍵字，先判斷SQL語(yǔ)句的操作類型驯杜，是讀操作還是寫(xiě)操作受啥，然后再根據(jù)FROM關(guān)鍵字來(lái)確定本次SQL語(yǔ)句要操作的是哪張表，也會(huì)根據(jù)WHERE關(guān)鍵字后面的內(nèi)容鸽心，確定本次SQL的一些結(jié)果篩選條件.....滚局。

總之，經(jīng)過(guò)關(guān)鍵字分析后顽频，一條SQL語(yǔ)句要干的具體工作就會(huì)被解析出來(lái)藤肢。

解析了SQL語(yǔ)句中的關(guān)鍵字之后，解析器會(huì)根據(jù)分析出的關(guān)鍵字信息糯景，生成對(duì)應(yīng)的語(yǔ)法樹(shù)嘁圈，然后交給優(yōu)化器處理。

在這一步也就相當(dāng)于Java中的.java源代碼變?yōu)?code>.class字節(jié)碼的過(guò)程莺奸，目的就是將SQL語(yǔ)句翻譯成數(shù)據(jù)庫(kù)可以看懂的指令丑孩。

優(yōu)化器中會(huì)干的工作

? ?經(jīng)過(guò)解析器的工作后會(huì)得到一個(gè)SQL語(yǔ)法樹(shù)，也就是知道了客戶端的SQL大體要干什么事情了灭贷，接著優(yōu)化器會(huì)對(duì)于這條SQL，給出一個(gè)最優(yōu)的執(zhí)行方案略贮，也就是告訴工作線程怎么執(zhí)行效率最高甚疟、最節(jié)省資源以及時(shí)間仗岖。

? ?優(yōu)化器最開(kāi)始會(huì)根據(jù)語(yǔ)法樹(shù)制定出多個(gè)執(zhí)行計(jì)劃，然后從多個(gè)執(zhí)行計(jì)劃中選擇出一個(gè)最好的計(jì)劃览妖，交給工作線程去執(zhí)行轧拄，但這里究竟是如何選擇最優(yōu)執(zhí)行計(jì)劃的，相信大家也比較好奇讽膏，那此時(shí)我們結(jié)合前面給出的案例分析一下檩电。

SELECT user_id FROM `zz_user` WHERE user_sex = "男" AND user_name = "竹子④號(hào)";

先來(lái)看看，對(duì)于這條SQL而言府树，總共有幾種執(zhí)行方案呢俐末？答案是兩種。

• ①先從表中將所有user_sex="男"的數(shù)據(jù)查出來(lái)奄侠，再?gòu)慕Y(jié)果中獲取user_name="竹子④號(hào)"的數(shù)據(jù)卓箫。
• ②先從表中尋找user_name="竹子④號(hào)"的數(shù)據(jù)，再?gòu)慕Y(jié)果中獲得user_sex="男"的數(shù)據(jù)垄潮。

再結(jié)合前面給出的表數(shù)據(jù)烹卒，暫且分析一下上述兩種執(zhí)行計(jì)劃哪個(gè)更好呢？

+---------+--------------+----------+-------------+
| user_id | user_name    | user_sex | user_phone  |
+---------+--------------+----------+-------------+
|       1 | 竹子①號(hào)      | 男       | 18888888888 |
|       2 | 竹子②號(hào)      | 男       | 13588888888 |
|       3 | 竹子③號(hào)      | 男       | 15688888888 |
|       4 | 熊貓①號(hào)      | 女       | 13488888888 |
|       5 | 熊貓②號(hào)      | 女       | 18588888888 |
|       6 | 竹子④號(hào)      | 男       | 17777777777 |
|       7 | 熊貓③號(hào)      | 女       | 16666666666 |
+---------+--------------+----------+-------------+

如果按照第①種方案執(zhí)行弯洗，此時(shí)會(huì)先得到四條user_sex="男"的數(shù)據(jù)旅急，然后再?gòu)乃臈l數(shù)據(jù)中查找user_name="竹子④號(hào)"的數(shù)據(jù)。

如果按照第②中方案執(zhí)行牡整，此時(shí)會(huì)直接得到一條user_name="竹子④號(hào)"的數(shù)據(jù)藐吮，然后再判斷一下user_sex是否為"男"，是則直接返回果正，否則返回空炎码。

相較于兩種執(zhí)行方案的過(guò)程，前者需要掃一次全表秋泳，然后再對(duì)結(jié)果集逐條判斷潦闲。而第二種方案掃一次全表后，只需要再判斷一次就可以了迫皱，很明顯可以感知出：第②種執(zhí)行計(jì)劃是最優(yōu)的歉闰，因此優(yōu)化器會(huì)給出第②種執(zhí)行計(jì)劃。

? ?經(jīng)過(guò)上述案例的講解后卓起，大家應(yīng)該能夠?qū)?yōu)化器的工作進(jìn)一步理解和敬。但上述案例僅是為了幫助大家理解，實(shí)際的SQL優(yōu)化過(guò)程會(huì)更加復(fù)雜戏阅，例如多表join查詢時(shí)昼弟，怎么查更合適？單表復(fù)雜SQL查詢時(shí)奕筐，有多條索引可以走舱痘，走哪條速度最快....变骡，因此一條SQL的最優(yōu)執(zhí)行計(jì)劃，需要結(jié)合多方面的優(yōu)化策略來(lái)生成芭逝，例如MySQL優(yōu)化器的一些優(yōu)化準(zhǔn)則如下：

• ?多條件查詢時(shí)塌碌，重排條件先后順序，將效率更好的字段條件放在前面旬盯。
• ?當(dāng)表中存在多個(gè)索引時(shí)台妆，選擇效率最高的索引作為本次查詢的目標(biāo)索引。
• ?使用分頁(yè)Limit關(guān)鍵字時(shí)胖翰，查詢到對(duì)應(yīng)的數(shù)據(jù)條數(shù)后終止掃表接剩。
• ?多表join聯(lián)查時(shí)，對(duì)查詢表的順序重新定義泡态，同樣以效率為準(zhǔn)搂漠。
• ?對(duì)于SQL中使用函數(shù)時(shí)，如count()某弦、max()桐汤、min()...，根據(jù)情況選擇最優(yōu)方案靶壮。
  • max()函數(shù)：走B+樹(shù)最右側(cè)的節(jié)點(diǎn)查詢（大的在右怔毛，小的在左）。
  • min()函數(shù)：走B+樹(shù)最左側(cè)的節(jié)點(diǎn)查詢腾降。
  • count()函數(shù)：如果是MyISAM引擎拣度，直接獲取引擎統(tǒng)計(jì)的總行數(shù)。
  • ......
• ?對(duì)于group by分組排序螃壤，會(huì)先查詢所有數(shù)據(jù)后再統(tǒng)一排序抗果，而不是一開(kāi)始就排序。
• ?......

總之奸晴，根據(jù)SQL不同冤馏，優(yōu)化器也會(huì)基于不同的優(yōu)化準(zhǔn)則選擇出最佳的執(zhí)行計(jì)劃。但需要牢記的一點(diǎn)是：MySQL雖然有優(yōu)化器寄啼，但對(duì)于效率影響最大的還是SQL本身逮光，因此編寫(xiě)出一條優(yōu)秀的SQL，才是提升效率的最大要素墩划。

存儲(chǔ)引擎中會(huì)干的工作

? ?經(jīng)過(guò)優(yōu)化器后涕刚，會(huì)得到一個(gè)最優(yōu)的執(zhí)行計(jì)劃，緊接著工作線程會(huì)根據(jù)最優(yōu)計(jì)劃乙帮，去依次調(diào)用存儲(chǔ)引擎提供的API杜漠，在上篇文章中提到過(guò)，存儲(chǔ)引擎主要就是負(fù)責(zé)在磁盤(pán)讀寫(xiě)數(shù)據(jù)的，不同的存儲(chǔ)引擎碑幅，存儲(chǔ)在本地磁盤(pán)中的數(shù)據(jù)結(jié)構(gòu)也并不相同戴陡，但這些底層實(shí)現(xiàn)并不需要MySQL的上層服務(wù)關(guān)心塞绿，因?yàn)樯蠈臃?wù)只需要負(fù)責(zé)調(diào)用對(duì)應(yīng)的API即可沟涨，存儲(chǔ)引擎的API功能都是相同的。

? ?工作線程根據(jù)執(zhí)行計(jì)劃調(diào)用存儲(chǔ)引擎的API查詢指定的表异吻，最終也就是會(huì)發(fā)生磁盤(pán)IO裹赴，從磁盤(pán)中檢索數(shù)據(jù)，當(dāng)然诀浪，檢索的數(shù)據(jù)有可能是磁盤(pán)中的索引文件棋返，也有可能是磁盤(pán)中的表數(shù)據(jù)文件，這點(diǎn)要根據(jù)執(zhí)行計(jì)劃來(lái)決定雷猪，我們只需要記住睛竣，經(jīng)過(guò)這一步之后總能夠得到執(zhí)行結(jié)果即可。

但有個(gè)小細(xì)節(jié)求摇，還記得最開(kāi)始創(chuàng)建數(shù)據(jù)庫(kù)連接時(shí)射沟，對(duì)登錄用戶的授權(quán)步驟嘛？當(dāng)工作線程去嘗試查詢某張表時(shí)与境，會(huì)首先判斷一下線程自身維護(hù)的客戶端連接验夯，其登錄的用戶是否具備這張表的操作權(quán)限，如果不具備則會(huì)直接返回權(quán)限不足的錯(cuò)誤信息摔刁。

? ?不過(guò)存儲(chǔ)引擎從磁盤(pán)中檢索出目標(biāo)數(shù)據(jù)后挥转，并不會(huì)將所有數(shù)據(jù)全部得到后再返回，而是會(huì)逐條返回給SQL接口共屈，然后會(huì)由SQL接口完成最后的數(shù)據(jù)聚合工作绑谣，對(duì)于這點(diǎn)稍后會(huì)詳細(xì)分析。

下來(lái)再來(lái)看看寫(xiě)入SQL的執(zhí)行過(guò)程拗引，因?yàn)樽x取和寫(xiě)入操作之間借宵，也會(huì)存在些許差異。

3.2寺擂、一條寫(xiě)入SQL的執(zhí)行過(guò)程

? ?假設(shè)此時(shí)要執(zhí)行下述這一條寫(xiě)入類型的SQL語(yǔ)句（還是基于之前的表數(shù)據(jù)）：

UPDATE `zz_user` SET user_sex = "女" WHERE user_id = 6;

上面這條SQL是一條典型的修改SQL暇务，但除開(kāi)修改操作外，新增怔软、刪除等操作也屬于寫(xiě)操作垦细，寫(xiě)操作的意思是指會(huì)對(duì)表中的數(shù)據(jù)進(jìn)行更改。同樣先上一個(gè)完整的流程圖：
[圖片上傳失敗...(image-c5b383-1670309104421)]
從上圖來(lái)看挡逼，相較于查詢SQL括改，寫(xiě)操作的SQL執(zhí)行流程明顯會(huì)更復(fù)雜一些，這里也先簡(jiǎn)單總結(jié)一下每一步流程家坎，然后再詳細(xì)分析一下其中一些與查詢SQL中不同的步驟：

• ①先將SQL發(fā)送給SQL接口嘱能，SQL接口會(huì)對(duì)SQL語(yǔ)句進(jìn)行哈希處理吝梅。
• ②在緩存中根據(jù)哈希值檢索數(shù)據(jù)，如果緩存中有則將對(duì)應(yīng)表的所有緩存全部刪除惹骂。
• ③經(jīng)過(guò)緩存后會(huì)將SQL交給解析器苏携，解析器會(huì)判斷SQL語(yǔ)句是否正確：
  • 錯(cuò)誤：拋出1064錯(cuò)誤碼及相關(guān)的語(yǔ)法錯(cuò)誤信息。
  • 正確：將SQL語(yǔ)句交給優(yōu)化器處理对粪，進(jìn)入第④步右冻。
• ④優(yōu)化器根據(jù)SQL制定出不同的執(zhí)行方案，并擇選出最優(yōu)的執(zhí)行計(jì)劃著拭。
• ⑤在執(zhí)行開(kāi)始之前纱扭，先記錄一下undo-log日志和redo-log(prepare狀態(tài))日志。
• ⑥在緩沖區(qū)中查找是否存在當(dāng)前要操作的行記錄或表數(shù)據(jù)（內(nèi)存中）：
  • 存在：
    • ⑦直接對(duì)緩沖區(qū)中的數(shù)據(jù)進(jìn)行寫(xiě)操作儡遮。
    • ⑧然后利用Checkpoint機(jī)制刷寫(xiě)到磁盤(pán)乳蛾。
  • 不存在：
    • ⑦根據(jù)執(zhí)行計(jì)劃，調(diào)用存儲(chǔ)引擎的API鄙币。
    • ⑧發(fā)生磁盤(pán)IO肃叶，對(duì)磁盤(pán)中的數(shù)據(jù)做寫(xiě)操作。
• ⑨寫(xiě)操作完成后爱榔，記錄bin-log日志被环，同時(shí)將redo-log日志中的記錄改為commit狀態(tài)。
• ⑩將SQL執(zhí)行耗時(shí)及操作成功的結(jié)果返回給SQL接口详幽，再由SQL接口返回給客戶端筛欢。

整個(gè)寫(xiě)SQL的執(zhí)行過(guò)程，前面的一些步驟與查SQL執(zhí)行的過(guò)程沒(méi)太大差異唇聘，唯一一點(diǎn)不同的在于緩存哪里版姑，原本查詢時(shí)是從緩存中嘗試獲取數(shù)據(jù)。而寫(xiě)操作時(shí)迟郎，由于要對(duì)表數(shù)據(jù)發(fā)生更改剥险，因此如果在緩存中發(fā)現(xiàn)了要操作的表存在緩存，則需要將整個(gè)表的所有緩存清空宪肖，確保緩存的強(qiáng)一致性表制。

OK~，除開(kāi)上述這點(diǎn)區(qū)別外控乾，另外多出了唯一性判斷么介、一個(gè)緩沖區(qū)寫(xiě)入，以及幾個(gè)寫(xiě)入日志的步驟蜕衡，接下來(lái)一起來(lái)聊聊這些壤短。

唯一性判斷主要是針對(duì)插入、修改語(yǔ)句來(lái)說(shuō)的，因?yàn)槿绻碇械哪硞€(gè)字段建立了唯一約束或唯一索引后久脯，當(dāng)插入/修改一條數(shù)據(jù)時(shí)纳胧，就會(huì)先檢測(cè)一下目前插入/修改的值，是否與表中的唯一字段存在沖突帘撰，如果表中已經(jīng)存在相同的值跑慕，則會(huì)直接拋出異常，反之會(huì)繼續(xù)執(zhí)行骡和。

很簡(jiǎn)單哈~相赁，接著再來(lái)聊聊緩沖區(qū)和日志！

其實(shí)在上篇中聊到過(guò)慰于，由于CPU和磁盤(pán)之間的性能差距實(shí)在過(guò)大，因此MySQL中會(huì)在內(nèi)存中設(shè)計(jì)一個(gè)「緩沖區(qū)」的概念唤衫，主要目的是在于彌補(bǔ)CPU與磁盤(pán)之間的性能差距婆赠。

緩沖區(qū)中會(huì)做的工作

??在真正調(diào)用存儲(chǔ)引擎的API操作磁盤(pán)之前，首先會(huì)在「緩沖區(qū)」中查找有沒(méi)有要操作的目標(biāo)數(shù)據(jù)/目標(biāo)表佳励，如果存在則直接對(duì)緩沖區(qū)中的數(shù)據(jù)進(jìn)行操作休里，然后MySQL會(huì)在后臺(tái)以一種名為Checkpoint的機(jī)制，將緩沖區(qū)中更新的數(shù)據(jù)刷回到磁盤(pán)赃承。只有當(dāng)緩沖區(qū)沒(méi)有找到目標(biāo)數(shù)據(jù)時(shí)妙黍，才會(huì)去真正調(diào)用存儲(chǔ)引擎的API，然后發(fā)生磁盤(pán)IO瞧剖，去對(duì)應(yīng)磁盤(pán)中的表數(shù)據(jù)進(jìn)行修改拭嫁。

不過(guò)值得注意的一點(diǎn)是：雖然緩沖區(qū)中有數(shù)據(jù)時(shí)會(huì)先操作緩沖區(qū)，然后再通過(guò)Checkpoint機(jī)制刷寫(xiě)磁盤(pán)抓于，但這兩個(gè)過(guò)程不是連續(xù)的做粤！也就是說(shuō)，當(dāng)線程對(duì)緩沖區(qū)中的數(shù)據(jù)操作完成后捉撮，會(huì)直接往下走怕品，數(shù)據(jù)落盤(pán)的工作則會(huì)交給后臺(tái)線程。
不過(guò)雖然兩者之間是異步的巾遭，但對(duì)于人而言肉康，這個(gè)過(guò)程不會(huì)有太大的感知，畢竟CPU在運(yùn)行的時(shí)候灼舍，都是按納秒吼和、微秒級(jí)作為單位。

??但不管數(shù)據(jù)是在緩沖區(qū)還是磁盤(pán)片仿，本質(zhì)上數(shù)據(jù)更改的動(dòng)作都是發(fā)生在內(nèi)存的纹安，就算是修改磁盤(pán)數(shù)據(jù)，也是將數(shù)據(jù)讀到內(nèi)存中操作，然后再將數(shù)據(jù)寫(xiě)回磁盤(pán)厢岂。不過(guò)在「寫(xiě)SQL」執(zhí)行的前后都會(huì)記錄日志光督，這點(diǎn)下面詳細(xì)聊聊，這也是寫(xiě)SQL與讀SQL最大的區(qū)別塔粒。

寫(xiě)操作時(shí)的日志

??執(zhí)行「讀SQL」一般都不會(huì)有狀態(tài)结借，也就是說(shuō)：MySQL執(zhí)行一條select語(yǔ)句，幾乎不會(huì)留下什么痕跡卒茬。但這里為什么用幾乎這個(gè)詞呢船老？因?yàn)椴樵儠r(shí)也有些特殊情況會(huì)留下“痕跡”，就是慢查詢SQL：

慢查詢SQL：查詢執(zhí)行過(guò)程耗時(shí)較長(zhǎng)的SQL記錄圃酵。
在執(zhí)行查詢SQL時(shí)柳畔，大多數(shù)的普通查詢MySQL并不關(guān)心，但慢查詢SQL除外郭赐，這類SQL一般是引起響應(yīng)緩慢問(wèn)題的“始作俑者”薪韩，所以當(dāng)一條查詢SQL的執(zhí)行時(shí)長(zhǎng)超過(guò)規(guī)定的時(shí)間限制，就會(huì)被“記錄在案”捌锭，也就是會(huì)記錄到慢查詢?nèi)罩局小?/p>

??與「查詢SQL」恰恰相反俘陷，任何一條寫(xiě)入類型的SQL都是有狀態(tài)的，也就代表著只要是會(huì)對(duì)數(shù)據(jù)庫(kù)發(fā)生更改的SQL观谦，執(zhí)行時(shí)都會(huì)被記錄在日志中拉盾。首先所有的寫(xiě)SQL在執(zhí)行之前都會(huì)生成對(duì)應(yīng)的撤銷SQL，撤銷SQL也就是相反的操作豁状，比如現(xiàn)在執(zhí)行的是insert語(yǔ)句捉偏，那這里就生成對(duì)應(yīng)的delete語(yǔ)句....，然后記錄在undo-log撤銷/回滾日志中替蔬。但除此之外告私，還會(huì)記錄redo-log日志。

??redo-log日志是InnoDB引擎專屬的承桥，主要是為了保證事務(wù)的原子性和持久性驻粟，這里會(huì)將寫(xiě)SQL的事務(wù)過(guò)程記錄在案，如果服務(wù)器或者MySQL宕機(jī)凶异，重啟時(shí)就可以通過(guò)redo_log日志恢復(fù)更新的數(shù)據(jù)蜀撑。在「寫(xiě)SQL」正式執(zhí)行之前，就會(huì)先記錄一條prepare狀態(tài)的日志剩彬，表示當(dāng)前「寫(xiě)SQL」準(zhǔn)備執(zhí)行酷麦，然后當(dāng)執(zhí)行完成并且事務(wù)提交后，這條日志記錄的狀態(tài)才會(huì)更改為commit狀態(tài)喉恋。

除開(kāi)上述的redo-log沃饶、undo-log日志外母廷，同時(shí)還會(huì)記錄bin-log日志，這個(gè)日志和redo-log日志很像糊肤，都是記錄對(duì)數(shù)據(jù)庫(kù)發(fā)生更改的SQL琴昆，只不過(guò)redo-log是InnoDB引擎專屬的，而bin-log日志則是MySQL自帶的日志馆揉。

不過(guò)無(wú)論是什么日志业舍，都需要在磁盤(pán)中存儲(chǔ)，而本身「寫(xiě)SQL」在磁盤(pán)中寫(xiě)表數(shù)據(jù)效率就較低了升酣，此時(shí)還需寫(xiě)入多種日志舷暮，效率定然會(huì)更低。對(duì)于這個(gè)問(wèn)題MySQL以及存儲(chǔ)引擎的設(shè)計(jì)者自然也想到了噩茄，所以大部分日志記錄也是采用先寫(xiě)到緩沖區(qū)中下面，然后再異步刷寫(xiě)到磁盤(pán)中。

比如redo-log日志在內(nèi)存中會(huì)有一個(gè)redo_log緩沖區(qū)中巢墅，bin-log日志也同理诸狭，當(dāng)需要記錄日志時(shí)，都是先寫(xiě)到內(nèi)存中的緩沖區(qū)君纫。

那內(nèi)存中的日志數(shù)據(jù)何時(shí)會(huì)刷寫(xiě)到磁盤(pán)呢？對(duì)于這點(diǎn)則是由刷盤(pán)策略來(lái)決定的芹彬，redo-log日志的刷盤(pán)策略由innodb_flush_log_at_trx_commit參數(shù)控制蓄髓，而bin-log日志的刷盤(pán)策略則可以通過(guò)sync_binlog參數(shù)控制：

• innodb_flush_log_at_trx_commit：
  • 0：間隔一段時(shí)間，然后再刷寫(xiě)一次日志到磁盤(pán)（性能最佳）舒帮。
  • 1：每次提交事務(wù)時(shí)会喝，都刷寫(xiě)一次日志到磁盤(pán)（性能最差，最安全玩郊，默認(rèn)策略）肢执。
  • 2：有事務(wù)提交的情況下，每間隔一秒時(shí)間刷寫(xiě)一次日志到磁盤(pán)译红。
• sync_binlog：
  • 0：同上述innodb_flush_log_at_trx_commit參數(shù)的2预茄。
  • 1：同上述innodb_flush_log_at_trx_commit參數(shù)的1，每次提交事務(wù)都會(huì)刷盤(pán)侦厚，默認(rèn)策略耻陕。

到這里就大致闡述了一下「寫(xiě)SQL」執(zhí)行時(shí)，會(huì)寫(xiě)的一些日志記錄刨沦，這些日志在后續(xù)做數(shù)據(jù)恢復(fù)诗宣、遷移、線下排查時(shí)都較為重要想诅，因此后續(xù)也會(huì)單開(kāi)一篇詳細(xì)講解召庞。

四岛心、一條SQL執(zhí)行完成后是如何返回的？

? ?一條「讀SQL」或「寫(xiě)SQL」執(zhí)行完成后篮灼，由于SQL操作的屬性不同忘古，兩者之間也會(huì)存在差異性，

4.1穿稳、讀類型的SQL返回

? ?前面聊到過(guò)存皂，MySQL執(zhí)行一條查詢SQL時(shí)，數(shù)據(jù)是逐條返回的模式逢艘，因?yàn)槿绻却袛?shù)據(jù)全部查出來(lái)之后再一次性返回旦袋，必然會(huì)導(dǎo)致?lián)螡M內(nèi)存。

不過(guò)這里的返回它改，并不是指返回客戶端疤孕，而是指返回SQL接口，因?yàn)閺拇疟P(pán)中檢索出目標(biāo)數(shù)據(jù)時(shí)央拖，一般還需要對(duì)這些數(shù)據(jù)進(jìn)行再次處理祭阀，舉個(gè)例子理解一下。

SELECT user_id FROM `zz_user` WHERE user_sex = "男" AND user_name = "竹子④號(hào)";

還是之前那條查詢SQL鲜戒，這條SQL要求返回的結(jié)果字段僅有一個(gè)user_id专控，但在磁盤(pán)中檢索數(shù)據(jù)時(shí)，會(huì)直接將這個(gè)字段單獨(dú)查詢出來(lái)嗎遏餐？并不是的伦腐，而是會(huì)將整條行數(shù)據(jù)全部查詢出來(lái)，如下：

+---------+--------------+----------+-------------+
| user_id | user_name    | user_sex | user_phone  |
+---------+--------------+----------+-------------+
|    6    |   竹子④號(hào)    |    男    | 17777777777 |
+---------+--------------+----------+-------------+

從行記錄中篩選出最終所需的結(jié)果字段失都，這個(gè)工作是在SQL接口中完成的柏蘑，也包括多表聯(lián)查時(shí)，數(shù)據(jù)的合并工作粹庞，同樣也是在SQL接口完成咳焚，其他SQL亦是同理。

還有一點(diǎn)需要牢記：就算沒(méi)有查詢到數(shù)據(jù)庞溜，也會(huì)將執(zhí)行狀態(tài)革半、執(zhí)行耗時(shí)這些信息返回給SQL接口，然后由SQL接口向客戶端返回NULL强缘。

不過(guò)當(dāng)查詢到數(shù)據(jù)后督惰，在正式向客戶端返回之前，還會(huì)順手將結(jié)果集放入到緩存中旅掂。

4.2赏胚、寫(xiě)類型的SQL返回

? ?寫(xiě)SQL執(zhí)行的過(guò)程會(huì)比讀SQL復(fù)雜，但寫(xiě)SQL的結(jié)果返回卻很簡(jiǎn)單商虐，寫(xiě)類型的操作執(zhí)行完成之后觉阅，僅會(huì)返回執(zhí)行狀態(tài)崖疤、受影響的行數(shù)以及執(zhí)行耗時(shí)，比如：

UPDATE `zz_user` SET user_sex = "女" WHERE user_id = 6;

這條SQL執(zhí)行成功后典勇，會(huì)返回Query OK, 1 row affected (0.00 sec)這組結(jié)果劫哼，最終返回給客戶端的則只有「受影響的行數(shù)」，如果寫(xiě)SQL執(zhí)行成功割笙，這個(gè)值一般都會(huì)大于0权烧，反之則會(huì)小于0。

4.3伤溉、執(zhí)行結(jié)果是如何返回給客戶端的般码？

? ?對(duì)于這個(gè)問(wèn)題的答案其實(shí)很簡(jiǎn)單，由于執(zhí)行當(dāng)前SQL的工作線程乱顾，本身也維護(hù)著一個(gè)數(shù)據(jù)庫(kù)連接板祝，這個(gè)數(shù)據(jù)庫(kù)連接實(shí)際上也維持著客戶端的網(wǎng)絡(luò)連接，如下：
[圖片上傳失敗...(image-960bac-1670309104421)]
當(dāng)結(jié)果集處理好了之后走净，直接通過(guò)Host中記錄的地址券时，將結(jié)果集封裝成TCP數(shù)據(jù)報(bào)，然后返回即可伏伯。

數(shù)據(jù)返回給客戶端之后，除非客戶端主動(dòng)輸入exit等退出連接的命令震檩，否則連接不會(huì)立馬斷開(kāi)蜓堕。

如果要斷開(kāi)客戶端連接時(shí)，又會(huì)經(jīng)過(guò)TCP四次揮手的過(guò)程博其。

不過(guò)就算與客戶端斷開(kāi)了連接，MySQL中創(chuàng)建的線程并不會(huì)銷毀背伴，而是會(huì)放入到MySQL的連接池中，等待其他客戶端復(fù)用當(dāng)前連接峰髓。一般情況下傻寂，一條線程在八小時(shí)內(nèi)未被復(fù)用疾掰，才會(huì)觸發(fā)MySQL的銷毀工作徐紧。

五炭懊、SQL執(zhí)行篇總結(jié)

? ?看到這里拂檩，SQL執(zhí)行原理篇也走進(jìn)了尾聲稻励，其實(shí)SQL語(yǔ)句的執(zhí)行過(guò)程望抽，實(shí)際上也就是MySQL的架構(gòu)中一層層對(duì)其進(jìn)行處理，理解了MySQL架構(gòu)篇的內(nèi)容后荒椭，相信看SQL執(zhí)行篇也不會(huì)太難舰蟆，經(jīng)過(guò)這篇文章的學(xué)習(xí)后身害，相信大家對(duì)數(shù)據(jù)庫(kù)的原理知識(shí)也能夠進(jìn)一步掌握，那我們下篇再見(jiàn)~