前言

相信大家用了這么久的MySQL，一定很好奇自己寫(xiě)的SQL是如何執(zhí)行并返回結(jié)果的，今天我們就來(lái)一層一層剝開(kāi)MySQL這顆洋蔥。

首先我們通過(guò)一張圖來(lái)了解下整個(gè)過(guò)程，然后再開(kāi)始一步一步解析。

第一步：客戶端發(fā)送SQL語(yǔ)句到MySQL服務(wù)端

假如項(xiàng)目中用到了Mybatis來(lái)操作數(shù)據(jù)庫(kù)，那么Mybatis就會(huì)通過(guò)JDBC來(lái)連接數(shù)據(jù)庫(kù)，并發(fā)送語(yǔ)句給數(shù)據(jù)庫(kù)，因?yàn)橐话氵\(yùn)行Web后臺(tái)服務(wù)的機(jī)器和MySQL服務(wù)都是物理上隔開(kāi)的，是一個(gè)分布式架構(gòu)，所以需要通過(guò)網(wǎng)絡(luò)來(lái)訪問(wèn)，JDBC采用TCP連接的方式與MySQL服務(wù)端進(jìn)行通信，通信的內(nèi)容包括發(fā)送語(yǔ)句、接收?qǐng)?zhí)行結(jié)果等。雖然TCP是全雙工的，但是Mysql的TCP是半雙工的，這意味著同一時(shí)刻要么客戶端在發(fā)送數(shù)據(jù)，要么服務(wù)端在發(fā)送數(shù)據(jù)。

第二步：驗(yàn)證連接合法性

JDBC與數(shù)據(jù)庫(kù)建立的連接的時(shí)候，會(huì)要求輸入用戶名和密碼，Mysql需要驗(yàn)證用戶名是否存在，密碼是否正確。驗(yàn)證通過(guò)后，再根據(jù)mysql.user表中的host字段來(lái)驗(yàn)證客戶端IP是否是允許的IP，這個(gè)host字段相當(dāng)于一個(gè)白名單。

前面的合法性都通過(guò)后，JDBC才會(huì)發(fā)送實(shí)際的SQL語(yǔ)句給MySQL服務(wù)端。

第三步：查詢緩存

像上面這種SELECT語(yǔ)句，MySQL服務(wù)端收到這個(gè)SQL時(shí)，如果開(kāi)啟了查詢緩存，就會(huì)根據(jù)SQL語(yǔ)句在查詢緩存中查找，查找成功就直接返回查詢緩存中的結(jié)果給客戶端，而不會(huì)執(zhí)行下面這些操作。

請(qǐng)注意，這里的查找方式是根據(jù)SQL語(yǔ)句進(jìn)行hash運(yùn)算，只要SQL中有一個(gè)字節(jié)不同都不會(huì)命中緩存。

第四步：語(yǔ)法解析和預(yù)處理

當(dāng)查詢緩存沒(méi)有命中時(shí)，才會(huì)開(kāi)始進(jìn)行語(yǔ)法解析和預(yù)處理。語(yǔ)法解析就像一個(gè)編譯程序一樣，根據(jù)語(yǔ)句生成語(yǔ)法樹(shù)，并檢查語(yǔ)法樹(shù)中的關(guān)鍵字是否正確，順序是否正確，引號(hào)是否前后匹配等。

經(jīng)過(guò)語(yǔ)法解析后，預(yù)處理就會(huì)檢查sql中的表、列是否存在，列名是否有歧義等，同時(shí)預(yù)處理還會(huì)對(duì)SQL進(jìn)行權(quán)限認(rèn)證，比如該用戶是否有SELECT權(quán)限、INSERT權(quán)限..., 是否有對(duì)應(yīng)數(shù)據(jù)庫(kù)的權(quán)限、表的權(quán)限等等。

第五步：查詢優(yōu)化

查詢優(yōu)化主要分為兩部分，一是靜態(tài)優(yōu)化二是動(dòng)態(tài)優(yōu)化。靜態(tài)優(yōu)化可以把語(yǔ)句中一些where條件進(jìn)行等價(jià)交換，比如：WHERE 1=1 AND a > 2將被替換為WHERE a > 2;靜態(tài)優(yōu)化不依賴sql語(yǔ)句的具體值，就像Java靜態(tài)編譯器的語(yǔ)法糖一樣。

動(dòng)態(tài)優(yōu)化：因?yàn)閯?dòng)態(tài)優(yōu)化以頁(yè)為最小單元來(lái)評(píng)估成本，所以需要分析SQL語(yǔ)句所對(duì)應(yīng)的表的索引頁(yè)或者數(shù)據(jù)頁(yè)的數(shù)量，以此來(lái)確定是走索引還是全表掃描。這些信息都是通過(guò)存儲(chǔ)引擎來(lái)獲得的，所以如果存儲(chǔ)引擎給出的結(jié)果不精確，那么查詢優(yōu)化的執(zhí)行計(jì)劃可能就不是最優(yōu)的。

因?yàn)橐粭lsql可以選擇的執(zhí)行方式有很多種，比如一張表里有多個(gè)索引，SQL語(yǔ)句涉及多個(gè)表的連接查詢，那么得到上述信息后，就需要評(píng)估使用哪些索引、哪個(gè)表關(guān)聯(lián)的順序是最優(yōu)的，并以此來(lái)生成一條執(zhí)行計(jì)劃。這部分也是Mysql服務(wù)層最復(fù)雜的地方，因?yàn)樾枰剂康囊蛩赜泻芏?，這里筆者只是列出了一小部分。

第六步：調(diào)用存儲(chǔ)引擎執(zhí)行

其實(shí)在MySQL中，真正決定怎么存儲(chǔ)數(shù)據(jù)和查詢數(shù)據(jù)的組件是存儲(chǔ)引擎。所以在第五步中得到了執(zhí)行計(jì)劃后，MySQL會(huì)調(diào)用表所對(duì)應(yīng)的存儲(chǔ)引擎的API，來(lái)執(zhí)行真正的查詢。Mysql定義了一系列存儲(chǔ)引擎接口，來(lái)讓編寫(xiě)存儲(chǔ)引擎的人來(lái)實(shí)現(xiàn)，所以只要符合接口定義的存儲(chǔ)引擎都是可以放入MySQL中去使用的。其中使用最廣泛的引擎莫過(guò)于InnoDB，InnoDB是一個(gè)支持事務(wù)、支持崩潰快速恢復(fù)的高性能存儲(chǔ)引擎。

Mysql服務(wù)層和存儲(chǔ)引擎層最大的區(qū)別是：服務(wù)層實(shí)現(xiàn)了一些不依賴于具體存儲(chǔ)引擎的通用操作，比如上面的連接驗(yàn)證、SQL驗(yàn)證這些。而存儲(chǔ)引擎則完成具體的查詢存儲(chǔ)操作，所以好的存儲(chǔ)引擎是Mysql的關(guān)鍵。

第七步：將結(jié)果返回給客戶端

容易想到的一種方式是MySQL服務(wù)端先把查詢結(jié)果緩存到內(nèi)存中，然后再一次性發(fā)送給客戶端，可實(shí)際上不是這樣的。實(shí)際是拿到符合條件的第一條數(shù)據(jù)就返回給客戶端，這是一個(gè)增量過(guò)程。這樣做的原因，是可以緩解服務(wù)端的內(nèi)存壓力。

如果開(kāi)啟了查詢緩存，并且語(yǔ)句是UPDATE、DELETE、INSERT之類的操作，那么這個(gè)時(shí)候也會(huì)更新查詢緩存。

總結(jié)

在整個(gè)過(guò)程中，最復(fù)雜的部分是第五步的查詢優(yōu)化和第六步中具體的存儲(chǔ)引擎，實(shí)現(xiàn)細(xì)節(jié)是造就了MySQL長(zhǎng)盛不衰的原因。如果想要優(yōu)化MySQL的性能，有幾步可以優(yōu)化：

客戶端使用連接池，這樣可以讓連接復(fù)用，因?yàn)镸ySQL每接收一個(gè)連接都要用一個(gè)線程去處理，和其他Web服務(wù)器的連接池解決的問(wèn)題一樣，這里也可以解決。

查詢緩存雖然在查詢時(shí)可以避免很多后續(xù)操作的成本，但是維護(hù)它的成本也挺高的，因?yàn)槊看蜺PDATE、DELETE、INSERT都需要互斥地更新對(duì)應(yīng)表的查詢緩存，這會(huì)成為MySQL的可擴(kuò)展性瓶頸。根據(jù)阿姆達(dá)爾定律，決定一個(gè)系統(tǒng)能否水平擴(kuò)展的是程序串行的部分。在MySQL8.0以上版本中，默認(rèn)禁用了查詢緩存。所以除非你能確定查詢緩存確實(shí)對(duì)吞吐量有幫助，否則禁用查詢緩存是個(gè)好建議。

默認(rèn)情況下，客戶端在第七步的接收過(guò)程中，其實(shí)是在自己的內(nèi)存里緩存了全部結(jié)果之后，才會(huì)解除阻塞，這些會(huì)創(chuàng)建很多對(duì)象，當(dāng)并發(fā)增高時(shí)，可能會(huì)引起JVM的OOM。所以這里可以改為每次只接收部分?jǐn)?shù)據(jù)，處理完后再接收部分。但這里服務(wù)端對(duì)于資源都是持有狀態(tài)，所以是一個(gè)空間和時(shí)間上的權(quán)衡。

如果有必要，你可以干涉第五步的查詢優(yōu)化過(guò)程，MySQL提供一些hint語(yǔ)句，比如強(qiáng)制走規(guī)定的關(guān)聯(lián)表順序或者強(qiáng)制使用某些索引。但是大多數(shù)情況下，請(qǐng)不要以為自己比查詢優(yōu)化器更聰明，使用推薦的方案可能更好。

設(shè)計(jì)一個(gè)好的索引對(duì)于查詢的性能影響非常之大，所以對(duì)于使用關(guān)系型數(shù)據(jù)庫(kù)來(lái)說(shuō)，索引設(shè)計(jì)是非常重要的一環(huán)。