一文讀懂MySQL查詢語句的執(zhí)行過程

需要從數(shù)據(jù)庫檢索某些符合要求的數(shù)據(jù)，我們很容易寫出 Select A B C FROM T WHERE ID = XX ?這樣的SQL，那么當(dāng)我們向數(shù)據(jù)庫發(fā)送這樣一個(gè)請求時(shí)，數(shù)據(jù)庫到底做了什么？

我們今天以MYSQL為例，揭示一下MySQL數(shù)據(jù)庫的查詢過程，并讓大家對數(shù)據(jù)庫里的一些零件有所了解。

MYSQL架構(gòu)

MySQL 主要可以分為 Server 層和存儲(chǔ)引擎層。

Server層?包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等，所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)，比如存儲(chǔ)過程、觸發(fā)器、視圖、函數(shù)等，還有個(gè)通用的日志模塊binlog日志模塊；

存儲(chǔ)引擎層?負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取。其架構(gòu)模式是插件式的，支持 InnoDB、MyISAM、Memory 等多個(gè)存儲(chǔ)引擎。現(xiàn)在最常用的存儲(chǔ)引擎是 InnoDB（支持事務(wù)），它從 MySQL5.5.5 版本開始成為默認(rèn)存儲(chǔ)引擎。

連接器

連接器主要負(fù)責(zé)用戶登錄數(shù)據(jù)庫，進(jìn)行用戶的身份認(rèn)證，包括校驗(yàn)賬戶密碼，權(quán)限等操作。

如果用戶密碼不對，你就會(huì)收到一個(gè)"Access denied for user"的錯(cuò)誤，然后客戶端程序結(jié)束執(zhí)行。

如果用戶賬戶密碼已通過，連接器會(huì)到權(quán)限表中查詢該用戶的所有權(quán)限，之后在這個(gè)連接里的權(quán)限邏輯判斷都是會(huì)依賴此時(shí)讀取到的權(quán)限數(shù)據(jù)，也就是說，后續(xù)只要這個(gè)連接不斷開，即時(shí)管理員修改了該用戶的權(quán)限，該用戶也是不受影響的。

查詢緩存（ Query cache）

客戶端與服務(wù)端建立連接后，MySQL 在執(zhí)行查詢語句時(shí)會(huì)先查詢緩存，校驗(yàn)這條SQL是不是在之前執(zhí)行過。之前執(zhí)行過的語句及其結(jié)果會(huì)以 key-value 對的形式，被直接緩存在內(nèi)存中。key 是查詢的語句，value 是查詢的結(jié)果。如果你的查詢能夠直接在這個(gè)緩存中找到 key，那么這個(gè)value 就會(huì)被直接返回給客戶端。如果沒有命中，則需要執(zhí)行后續(xù)的操作，完成后也會(huì)把結(jié)果緩存起來，方便下一次調(diào)用。

看到這里大家會(huì)不會(huì)眼前一亮，會(huì)不會(huì)有這個(gè)功能很好要好好利用的沖動(dòng)。

其實(shí)這里并不建議使用查詢緩存，查詢緩存的失效非常頻繁，只要有對一個(gè)表的更新，這個(gè)表上所有的查詢緩存都會(huì)被清空。因此很可能你費(fèi)勁地把結(jié)果存起來，還沒使用就被一個(gè)更新全清空了。對于更新壓力大的數(shù)據(jù)庫來說，查詢緩存的命中率會(huì)非常低。除非是那種很長時(shí)間不會(huì)更新的表，比如系統(tǒng)配置表，但是這種系統(tǒng)配置我們放在配置平臺(tái)不好嗎？

在MYSQL8.0中已經(jīng)刪除了查詢緩存這個(gè)功能，官方也是認(rèn)為該功能在實(shí)際的應(yīng)用場景比較少，所以干脆直接刪掉了。

分析器

Mysql沒有命中查詢緩存，那么就會(huì)進(jìn)入分析器，分析器主要是用來分析SQL語句是來干嘛的。分析器主要分為以下兩步：

• 詞法分析?：一條SQL語句有多個(gè)字符串組成，首先要提取關(guān)鍵字，比如select，提出查詢的表，提出字段名，提出查詢條件等等。
• 語法分析：根據(jù)詞法分析的結(jié)果，語法分析主要就是判斷你輸入的SQL語句是否正確，是否符合MYSQL語法，如果你的語句不對，就會(huì)收到“You have an error in your SQL syntax”的錯(cuò)誤提醒。

詞法分析程序?qū)⒄麄€(gè)查詢語句分解成各類標(biāo)志，語法分析根據(jù)定義的系統(tǒng)語言將“各類標(biāo)志”轉(zhuǎn)為對MySQL有意義的組合。最后系統(tǒng)生成一個(gè)語法樹(AST)，語法樹便是優(yōu)化器依賴的數(shù)據(jù)結(jié)構(gòu)。

優(yōu)化器

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

為什么需要優(yōu)化器？

• 優(yōu)化器中包含了許多復(fù)雜的優(yōu)化技術(shù)，這些優(yōu)化技術(shù)往往比最好的程序員掌握的還要多。系統(tǒng)的自動(dòng)優(yōu)化相當(dāng)于使得所有人都擁有這些優(yōu)化技術(shù)。
• 優(yōu)化器可以從數(shù)據(jù)字典中獲取許多統(tǒng)計(jì)信息，例如表中的行數(shù)、表中的每個(gè)列的分布情況等。優(yōu)化器優(yōu)化器可以考慮百種不同的執(zhí)行計(jì)劃，而程序員一般只能考慮有限的幾種可能；
• 可以根據(jù)這些信息選擇有效的執(zhí)行計(jì)劃，而用戶程序則難以獲得這些信息；

總之優(yōu)化器是對語法分析樹的形態(tài)進(jìn)行修改，把語法分析樹變?yōu)椴樵儤?，確定執(zhí)行方案。

執(zhí)行器

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

開始執(zhí)行的時(shí)候，要先校驗(yàn)改用戶是否有執(zhí)行查詢的權(quán)限，如果沒有，就會(huì)返回沒有權(quán)限的錯(cuò)誤。如果有權(quán)限，就會(huì)去調(diào)用引擎的接口，返回接口執(zhí)行的結(jié)果。

語句分析

我們以下面一條真實(shí)SQL查詢語句來進(jìn)行分析下MYSQL查詢的執(zhí)行過程

select id,name,sex,phoone from user t where t.age='26' and t.account='javadaily'

• 首先客戶端需要連接上數(shù)據(jù)庫，如果賬號(hào)密碼錯(cuò)誤直接返回錯(cuò)誤信息，如果正確則進(jìn)入下一步。
  
  
• 在MYSQL8.0之前會(huì)先去查詢緩存中，以這條SQL語句作為key在內(nèi)存中查詢是否有結(jié)果，如果有則先判斷是否有權(quán)限，有權(quán)限則返回客戶端，否則報(bào)錯(cuò)；如果沒有從查詢緩存命中則進(jìn)入下一步
  
  
• 通過分析器進(jìn)行詞法分析，提取sql語句的關(guān)鍵元素，比如提取上面這個(gè)語句是查詢select，提取需要查詢的表名為user，需要查詢的列為id,name,sex,phoone，查詢條件是age=26 和account=javadailly。然后判斷這個(gè)sql語句是否有語法錯(cuò)誤，比如關(guān)鍵詞是否正確等等，如果檢查沒問題就執(zhí)行下一步。
  
  
• 上面的SQL有兩種執(zhí)行方案，優(yōu)化器根據(jù)自己的優(yōu)化算法選擇執(zhí)行效率最高的a方案（統(tǒng)計(jì)信息不準(zhǔn)可能導(dǎo)致優(yōu)化器選擇錯(cuò)誤的執(zhí)行方案），確定了優(yōu)化方案后就開始執(zhí)行。

a. 先查詢 account=javadaily 的用戶，然后判斷 age 是否等于26 b. 先找出 age=26 的用戶，再查詢 account=javadaily 的用戶

• 進(jìn)行權(quán)限校驗(yàn)，如果有查詢權(quán)限則調(diào)用數(shù)據(jù)庫引擎接口返回執(zhí)行結(jié)果；否則報(bào)錯(cuò)。