SQL語句執(zhí)行過程介紹

?昔日庖丁解牛，未見全牛，所賴者是其對(duì)牛內(nèi)部骨架結(jié)構(gòu)的了解，對(duì)于MySQL亦是如此，只有更加全面地了解SQL語句執(zhí)行的每個(gè)過程，才能更好的進(jìn)行SQL的設(shè)計(jì)和優(yōu)化。
?當(dāng)希望MySQL能夠以更高的性能運(yùn)行查詢時(shí)，最好的辦法就是弄清楚MySQL是如何優(yōu)化和執(zhí)行查詢的。一旦理解了這一點(diǎn)，很多查詢優(yōu)化工作實(shí)際上就是遵循一些原則能夠按照預(yù)想的合理的方式運(yùn)行。
?如下圖所示，當(dāng)向MySQL發(fā)送一個(gè)請(qǐng)求的時(shí)候，MySQL到底做了什么：

1. 客戶端發(fā)送一條查詢給服務(wù)器。

2. 服務(wù)器先檢查查詢緩存，如果命中了緩存，則立刻返回存儲(chǔ)在緩存中的結(jié)果。否則進(jìn)入下一階段。

3. 服務(wù)器端進(jìn)行SQL解析、預(yù)處理，再由優(yōu)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃。

4. MySQL根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃，再調(diào)用存儲(chǔ)引擎的API來執(zhí)行查詢。

5. 將結(jié)果返回給客戶端。

 

查詢緩存

?MySQL查詢緩存保存查詢返回的完整結(jié)構(gòu)。當(dāng)查詢命中該緩存時(shí)，MySQL會(huì)立刻返回結(jié)果，跳過了解析、優(yōu)化和執(zhí)行階段。
?查詢緩存系統(tǒng)會(huì)跟蹤查詢中涉及的每個(gè)表，如果這些表發(fā)生了變化，那么和這個(gè)表相關(guān)的所有緩存數(shù)據(jù)都將失效。
?MySQL將緩存存放在一個(gè)引用表中，通過一個(gè)哈希值引用，這個(gè)哈希值包括了以下因素，即查詢本身、當(dāng)前要查詢的數(shù)據(jù)庫(kù)、客戶端協(xié)議的版本等一些其他可能影響返回結(jié)果的信息。
?當(dāng)判斷緩存是否命中時(shí)，MySQL不會(huì)進(jìn)行解析查詢語句，而是直接使用SQL語句和客戶端發(fā)送過來的其他原始信息。所以，任何字符上的不同，例如空格、注解等都會(huì)導(dǎo)致緩存的不命中。
?當(dāng)查詢語句中有一些不確定的數(shù)據(jù)時(shí)，則不會(huì)被緩存。例如包含函數(shù)NOW()或者CURRENT_DATE()的查詢不會(huì)緩存。包含任何用戶自定義函數(shù)，存儲(chǔ)函數(shù)，用戶變量，臨時(shí)表，mysql數(shù)據(jù)庫(kù)中的系統(tǒng)表或者包含任何列級(jí)別權(quán)限的表，都不會(huì)被緩存。
?有一點(diǎn)需要注意，MySQL并不是會(huì)因?yàn)椴樵冎邪粋€(gè)不確定的函數(shù)而不檢查查詢緩存，因?yàn)闄z查查詢緩存之前，MySQL不會(huì)解析查詢語句，所以也無法知道語句中是否有不確定的函數(shù)。
?事實(shí)則是，如果查詢語句中包含任何的不確定的函數(shù)，那么其查詢結(jié)果不會(huì)被緩存，因?yàn)椴樵兙彺嬷幸矡o法找到對(duì)應(yīng)的緩存結(jié)果。
?有關(guān)查詢緩存的配置如下所示。

• query_cache_type:是否打開查詢緩存?？梢栽O(shè)置為OFF、ON和DEMAND。DEMAND表示只有在查詢語句中明確寫明SQL_CACHE的語句才會(huì)放入查詢緩存。

• query_cache_size:查詢緩存使用的總內(nèi)存空間。

• query_cache_min_res_unit:在查詢緩存中分配內(nèi)存塊時(shí)的最小單元。較小的該值可以減少碎片導(dǎo)致的內(nèi)存空間浪費(fèi)，但是會(huì)導(dǎo)致更頻繁的內(nèi)存塊操作。

• query_cache_limit:MySQL能夠查詢的最大查詢結(jié)果。如果查詢結(jié)果大于這個(gè)值，則不會(huì)被緩存。因?yàn)椴樵兙彺嬖跀?shù)據(jù)生成的時(shí)候就開始嘗試緩存數(shù)據(jù)，所以當(dāng)結(jié)果全部返回后，MySQL才知道查詢結(jié)果是否超出限制。超出之后，才會(huì)將結(jié)果從查詢緩存中刪除。

?對(duì)查詢緩存的優(yōu)化是數(shù)據(jù)庫(kù)性能優(yōu)化的重要一環(huán)。判斷流程大致如下圖所示。

 

?緩存命中率可以通過如下公式計(jì)算：Qcache_hits/(Qcache_hits + Com_select)來計(jì)算。

解析和預(yù)處理

?解析器通過關(guān)鍵字將SQL語句進(jìn)行解析，并生成對(duì)應(yīng)的解析樹。MySQL解析器將使用MySQL語法規(guī)則驗(yàn)證和解析查詢。
?預(yù)處理器則根據(jù)一些MySQL規(guī)則進(jìn)行進(jìn)一步檢查解析書是否合法，例如檢查數(shù)據(jù)表和數(shù)據(jù)列是否存在，還會(huì)解析名字和別名，看看它們是否有歧義。

查詢優(yōu)化器

?查詢優(yōu)化器會(huì)將解析樹轉(zhuǎn)化成執(zhí)行計(jì)劃。一條查詢可以有多種執(zhí)行方法，最后都是返回相同結(jié)果。優(yōu)化器的作用就是找到這其中最好的執(zhí)行計(jì)劃。
?生成執(zhí)行計(jì)劃的過程會(huì)消耗較多的時(shí)間，特別是存在許多可選的執(zhí)行計(jì)劃時(shí)。如果在一條SQL語句執(zhí)行的過程中將該語句對(duì)應(yīng)的最終執(zhí)行計(jì)劃進(jìn)行緩存，當(dāng)相似的語句再次被輸入服務(wù)器時(shí)，就可以直接使用已緩存的執(zhí)行計(jì)劃，從而跳過SQL語句生成執(zhí)行計(jì)劃的整個(gè)過程，進(jìn)而可以提高語句的執(zhí)行速度。

 

?MySQL使用基于成本的查詢優(yōu)化器(Cost-Based Optimizer，CBO)。它會(huì)嘗試預(yù)測(cè)一個(gè)查詢使用某種執(zhí)行計(jì)劃時(shí)的成本，并選擇其中成本最少的一個(gè)。
?優(yōu)化器會(huì)根據(jù)優(yōu)化規(guī)則對(duì)關(guān)系表達(dá)式進(jìn)行轉(zhuǎn)換，這里的轉(zhuǎn)換是說一個(gè)關(guān)系表達(dá)式經(jīng)過優(yōu)化規(guī)則后會(huì)生成另外一個(gè)關(guān)系表達(dá)式，同時(shí)原有表達(dá)式也會(huì)保留，經(jīng)過一系列轉(zhuǎn)換后會(huì)生成多個(gè)執(zhí)行計(jì)劃，然后CBO會(huì)根據(jù)統(tǒng)計(jì)信息和代價(jià)模型(Cost Model)計(jì)算每個(gè)執(zhí)行計(jì)劃的Cost，從中挑選Cost最小的執(zhí)行計(jì)劃。由上可知，CBO中有兩個(gè)依賴：統(tǒng)計(jì)信息和代價(jià)模型。統(tǒng)計(jì)信息的準(zhǔn)確與否、代價(jià)模型的合理與否都會(huì)影響CBO選擇最優(yōu)計(jì)劃。
?有關(guān)優(yōu)化器的原理十分復(fù)雜，這里就不進(jìn)行詳細(xì)講解了，大家可以自行學(xué)習(xí)。

查詢執(zhí)行引擎

?在解析和優(yōu)化階段，MySQL將生成查詢對(duì)應(yīng)的執(zhí)行計(jì)劃，MySQL的查詢執(zhí)行引擎根據(jù)這個(gè)執(zhí)行計(jì)劃來完成整個(gè)查詢。這里執(zhí)行計(jì)劃是一個(gè)數(shù)據(jù)結(jié)構(gòu)，而不是和其他的關(guān)系型數(shù)據(jù)庫(kù)那樣生成對(duì)應(yīng)的字節(jié)碼。

返回結(jié)果給客戶端

?如果查詢可以被緩存，那么MySQL在這個(gè)階段頁會(huì)將結(jié)果存放到查詢緩存中。
?MySQL將結(jié)果集返回給客戶端是一個(gè)增量、逐步返回的過程。在查詢生成第一條結(jié)果時(shí)，MySQL就可以開始向客戶端逐步返回結(jié)果集了。