開(kāi)篇

相信廣大程序員朋友經(jīng)常使用MySQL數(shù)據(jù)庫(kù)作為數(shù)據(jù)持久化的工具，我們最常使用的就是MySQL中的SQL語(yǔ)句，從客戶端向MySQL發(fā)出一條條指令，然后獲取返回的數(shù)據(jù)結(jié)果進(jìn)行后面的邏輯處理。盡管大家經(jīng)常使用SQL語(yǔ)句完成工作，你是否關(guān)注過(guò)其執(zhí)行的階段，利用了哪些技術(shù)完成？今天，就帶大家一起看看MySQL數(shù)據(jù)庫(kù)處理SQL請(qǐng)求的全過(guò)程。下面將會(huì)講述如下內(nèi)容：
查詢(xún)請(qǐng)求在MySQL中的處理流程
MySQL 中處理SQL的組件介紹，包括：

連接器
查詢(xún)緩存
分析器
優(yōu)化器
執(zhí)行器

查詢(xún)請(qǐng)求的執(zhí)行流程

眾所周知在MySQL數(shù)據(jù)庫(kù)應(yīng)用中查詢(xún)請(qǐng)求是使用最多的，假設(shè)我們輸入代碼段1 中的SQL，通過(guò)客戶端請(qǐng)求MySQL服務(wù)器，會(huì)得到一個(gè)包含user的結(jié)果集。但是，其中MySQL的處理過(guò)程我們并不了解，那么下面就讓我們一起看看在查詢(xún)請(qǐng)求前后MySQL服務(wù)端發(fā)生了些什么吧。

Select * from user where userId=1

代碼段1

如圖1 所示：

圖1?MySQL 查詢(xún)請(qǐng)求處理流程

整張圖由三部分組成，從上到下分別是客戶端（紫色）、MySQL Server層（綠色）、MySQL存儲(chǔ)引擎層（黃色）。

客戶端不言而喻，主要負(fù)責(zé)與MySQL Server層建立連接，發(fā)送查詢(xún)請(qǐng)求以及接受響應(yīng)的結(jié)果集。
MySQL Server層，主要包括連接器、查詢(xún)緩存、分析器、優(yōu)化器、執(zhí)行器等。這些組件包含了MySQL的大部分主要功能，例如平時(shí)使用最多的存儲(chǔ)過(guò)程、觸發(fā)器、視圖都在這一層中。還有一個(gè)通用的日志模塊 bin log。
MySQL 存儲(chǔ)引擎層，主要負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取。其支持多個(gè)存儲(chǔ)引擎，例如：InnoDB、MyISAM等。常用的有InnoDB，它從MySQL 5.5.5版本開(kāi)始成為了MySQL的默認(rèn)存儲(chǔ)引擎，重要的是InnoDB 引擎包含了自帶的日志模塊 redo log，這個(gè)在后面講述更新語(yǔ)句的時(shí)候會(huì)著重提到。

上面介紹了MySQL的組件結(jié)構(gòu)，那么這里將其處理SQL語(yǔ)句的流程簡(jiǎn)單梳理一遍，之后再對(duì)每個(gè)組件逐一進(jìn)行介紹。如圖2 所示，在圖1 的基礎(chǔ)上加上了流程處理的編號(hào)，順著編號(hào)來(lái)看看MySQL的各個(gè)組件是如何處理SQL查詢(xún)請(qǐng)求的。

1. 連接器：當(dāng)客戶端登陸MySQL的時(shí)候，對(duì)身份認(rèn)證和權(quán)限判斷。

2. 查詢(xún)緩存: 執(zhí)行查詢(xún)語(yǔ)句的時(shí)候，會(huì)先查詢(xún)緩存（MySQL 8.0 版本后移除）。

3. 分析器: 假設(shè)在沒(méi)有命中查詢(xún)緩存的情況下，SQL請(qǐng)求就會(huì)來(lái)到分析器。分析器負(fù)責(zé)明確SQL要完成的功能，以及檢查SQL的語(yǔ)法是否正確。

4. 優(yōu)化器：為SQL提供優(yōu)化執(zhí)行的方案。

5. 執(zhí)行器: 將語(yǔ)句分發(fā)到對(duì)應(yīng)的存儲(chǔ)引擎執(zhí)行，并返回?cái)?shù)據(jù)。

執(zhí)行器?對(duì)"表"?的權(quán)限進(jìn)行判斷。而連接器是驗(yàn)證用戶身份。?

MySQL組件定義

下面通過(guò)一張大圖將SQL執(zhí)行流程進(jìn)行了梳理，這里將對(duì)應(yīng)的組件進(jìn)行詳細(xì)介紹。

圖2?SQL?請(qǐng)求執(zhí)行流程

連接器

客戶端需要通過(guò)連接器訪問(wèn)MySQL Server，連接器主要負(fù)責(zé)身份認(rèn)證和權(quán)限鑒別的工作。也就是負(fù)責(zé)用戶登錄數(shù)據(jù)庫(kù)的相關(guān)認(rèn)證操作，例如：校驗(yàn)賬戶密碼，權(quán)限等。在用戶名密碼合法的前提下，會(huì)在權(quán)限表中查詢(xún)用戶對(duì)應(yīng)的權(quán)限，并且將該權(quán)限分配給用戶。在連接完成以后可以通過(guò)圖3看到連接狀態(tài)，可以通過(guò)命令行“show processlist”生成圖3的查詢(xún)結(jié)果。其中“Command”列返回的內(nèi)容中，“Sleep”表示MySQL相同中對(duì)應(yīng)一個(gè)空閑連接。而“Query”表示正在查詢(xún)的連接。

圖3?連接狀態(tài)

上面提到了連接狀態(tài)，這里將5種連接狀態(tài)整理為如下表格，方便大家參考。

Command	含義
sleep	線程正在等待客戶端發(fā)數(shù)據(jù)
query	連接線程正在執(zhí)行查詢(xún)
locked	線程正在等待表鎖的釋放
sorting result	線程正在對(duì)結(jié)果進(jìn)行排序
sending data	向請(qǐng)求端返回?cái)?shù)據(jù)

MySQL將連接器中的連接分為長(zhǎng)連接和短連接。

長(zhǎng)連接是指連接成功后，客戶端請(qǐng)求一直使用是同一個(gè)連接。
短連接是指每次執(zhí)行完SQL請(qǐng)求的操作之后會(huì)斷開(kāi)連接，如果再有SQL請(qǐng)求會(huì)重新建立連接。由于短連接會(huì)反復(fù)創(chuàng)建連接消耗相同資源，因此多數(shù)情況下會(huì)選擇長(zhǎng)連接。但是為了保持長(zhǎng)連接，會(huì)占用系統(tǒng)內(nèi)存，而這些被占用的內(nèi)存知道連接斷開(kāi)以后才會(huì)釋放。這里提出了兩個(gè)解決方案：

1.定期斷開(kāi)長(zhǎng)連接，每隔一段時(shí)間或者執(zhí)行一個(gè)占用內(nèi)存的大查詢(xún)以后斷開(kāi)連接，從而釋放內(nèi)存，當(dāng)查詢(xún)的時(shí)候再重新創(chuàng)建連接。?

2.MySQL 5.7 或者更高的版本，通過(guò)執(zhí)行 mysql_reset_connection 來(lái)重新初始化連接。此過(guò)程不會(huì)重新建立連接，但是會(huì)釋放占用的內(nèi)存，將連接恢復(fù)到剛剛創(chuàng)立連接的狀態(tài)。

查詢(xún)緩存

在建立與數(shù)據(jù)庫(kù)的連接以后就可以執(zhí)行SQL語(yǔ)句來(lái)，不過(guò)在執(zhí)行之前會(huì)先查詢(xún)緩存，其目的是查看是否之前執(zhí)行過(guò)該語(yǔ)句，并且將執(zhí)行結(jié)果按照key-value的形式緩存在內(nèi)存中了。

Key 是查詢(xún)的SQL語(yǔ)句，Value 是查詢(xún)的結(jié)果。如果緩存 Key 被命中，就會(huì)直接返回給客戶端，如果沒(méi)有命中，就會(huì)執(zhí)行后續(xù)的操作，執(zhí)行完SQL仍舊會(huì)把結(jié)果緩存起來(lái)，方便下一次調(diào)用。

MySQL 查詢(xún)不建議使用緩存，因?yàn)闀?huì)出現(xiàn)這樣的場(chǎng)景：如果針對(duì)某張表進(jìn)行更新，針對(duì)這張表的查詢(xún)緩存就會(huì)被清空。如果張表不斷地被使用（更新、查詢(xún)），那么查詢(xún)緩存會(huì)頻繁地失效，獲取查詢(xún)緩存也失去了意義。不過(guò)可以運(yùn)用在一些修改不頻繁的數(shù)據(jù)表，例如：系統(tǒng)配置、或者修改不頻繁的表。緩存的淘汰策略是先進(jìn)先出，適用于查詢(xún)遠(yuǎn)大于修改的情況下，否則建議使用Redis或者其他做緩存工具。因此大多數(shù)情況下不推薦使用查詢(xún)緩存。MySQL 8.0 版本后刪除了查詢(xún)緩存的功能，官方認(rèn)為該功能應(yīng)用場(chǎng)景較少，所以將其刪除。

如果你不需要在MySQL中使用查詢(xún)緩存，也可以將參數(shù)query_cache_type設(shè)置成 DEMAND，那么默認(rèn)情況下的執(zhí)行SQL語(yǔ)句時(shí)就不會(huì)使用查詢(xún)緩存了。如果打開(kāi)了緩存可以通過(guò)“show status like 'Qcache%'”命令查看緩存的情況。

如圖4?緩存狀態(tài)

如圖4 所示，其中幾個(gè)使用較多的狀態(tài)值如下：

Qcache_inserts 是否有新的數(shù)據(jù)添加，每有一條數(shù)據(jù)添加Value會(huì)加一。

Qcache_hits 查詢(xún)語(yǔ)句是否命中緩存，每有一條語(yǔ)句命中Value會(huì)加一。

Qcache_free_memory 緩存空閑大小。

分析器

如果查詢(xún)緩存沒(méi)有命中，那么SQL請(qǐng)求會(huì)進(jìn)入分析器，分析器是用來(lái)分辨SQL語(yǔ)句的執(zhí)行目的，其執(zhí)行過(guò)程大致分為兩步：

第一步，詞法分析(Lexical scanner)，主要負(fù)責(zé)從SQL 語(yǔ)句中提取關(guān)鍵字，比如：查詢(xún)的表，字段名，查詢(xún)條件等等。

第二步，語(yǔ)法規(guī)則(Grammar rule module)，主要判斷SQL語(yǔ)句是否合乎MySQL的語(yǔ)法。

其實(shí)說(shuō)白了詞法分析(Lexical scanner) 就是將整個(gè)SQL語(yǔ)句拆分成一個(gè)個(gè)單詞，而語(yǔ)法規(guī)則(Grammar rule module)則根據(jù)MySQL定義的語(yǔ)法規(guī)則生成對(duì)應(yīng)的數(shù)據(jù)結(jié)構(gòu)，并存儲(chǔ)在對(duì)象結(jié)構(gòu)當(dāng)中。

其結(jié)果供優(yōu)化器生成執(zhí)行計(jì)劃，再調(diào)用存儲(chǔ)引擎接口執(zhí)行。來(lái)看下面這個(gè)例子，假設(shè)有這樣一個(gè)SQL語(yǔ)句“select username from userinfo”。

先通過(guò)詞法分析，從左到右逐個(gè)字符進(jìn)行解析，獲得如表1的四個(gè)單詞。

關(guān)鍵字	非關(guān)鍵字	關(guān)鍵字	非關(guān)鍵字
select	username	from	userinfo

表1?語(yǔ)法分析關(guān)鍵字

然后再通過(guò)語(yǔ)法規(guī)則解析，判斷輸入的SQL 語(yǔ)句是否滿足MySQL語(yǔ)法，并且生成圖5的語(yǔ)法樹(shù)。由SQL語(yǔ)句生成的四個(gè)單詞中，識(shí)別出兩個(gè)關(guān)鍵字，分別是select 和from。根據(jù)MySQL的語(yǔ)法Select 和 from之間對(duì)應(yīng)的是fields 字段，下面應(yīng)該掛接username；在from后面跟隨的是Tables字段，其下掛接的是userinfo。

圖5?語(yǔ)法規(guī)則生成語(yǔ)法樹(shù)

優(yōu)化器

優(yōu)化器的作用是對(duì)SQL進(jìn)行優(yōu)化，生成最優(yōu)的執(zhí)行方案。如圖6所示，前面提到的SQL解析器通過(guò)語(yǔ)法分析和語(yǔ)法規(guī)則生成了SQL語(yǔ)法樹(shù)。這個(gè)語(yǔ)法樹(shù)作為優(yōu)化器的輸入，而優(yōu)化器（黃色的部分）包含了邏輯變換和代價(jià)優(yōu)化兩部分的內(nèi)容。在優(yōu)化完成以后會(huì)生成SQL執(zhí)行計(jì)劃作為整個(gè)優(yōu)化過(guò)程的輸出，交給執(zhí)行器在存儲(chǔ)引擎上執(zhí)行。

圖6?優(yōu)化器所處的位置

如上圖所示，這節(jié)的重點(diǎn)在優(yōu)化器中的邏輯變換和代價(jià)優(yōu)化上。

邏輯變換

邏輯變換也就是在關(guān)系代數(shù)基礎(chǔ)上進(jìn)行變換，其目的是為了化簡(jiǎn)，同時(shí)保證SQL變化前后的結(jié)果一致，也就是邏輯變化并不會(huì)帶來(lái)結(jié)果集的變化。其主要包括以下幾個(gè)方面：

否定消除：針對(duì)表達(dá)式“和取”或“析取”前面出現(xiàn)“否定”的情況，應(yīng)將關(guān)系條件進(jìn)行拆分，從而將外層的“NOT”消除。
等值常量傳遞：利用了等值關(guān)系的傳遞特性，為了能夠盡早執(zhí)行“下推”運(yùn)算。“下推”的基本策略是，始終將過(guò)濾表達(dá)式盡可能移至靠近數(shù)據(jù)源的位置。
常量表達(dá)式計(jì)算：對(duì)于能立刻計(jì)算出結(jié)果的表達(dá)式，直接計(jì)算結(jié)果，同時(shí)將結(jié)果與其他條件盡量提前進(jìn)行化簡(jiǎn)。

這樣講概念或許有些抽象，通過(guò)圖7 來(lái)看看邏輯變化如何在SQL中執(zhí)行的吧。

圖7?邏輯變換

如圖7所示，從上往下共有4個(gè)步驟：

1.針對(duì)存在的SQL語(yǔ)句，首先通過(guò)“否定消除”，去掉條件判斷中的“NOT”。語(yǔ)句由原來(lái)的“or”轉(zhuǎn)換成“and”，并且大于小于符號(hào)進(jìn)行變號(hào)。藍(lán)色部分為修改前的SQL，紅色是修改以后的SQL。

2.等值傳遞，這一步很好理解分別將”t2.a=9” 和”t2.b=5”分別替換掉SQL中對(duì)應(yīng)的值。

3.接下來(lái)就是常量表達(dá)式計(jì)算，將“5+7”計(jì)算得到“12”。

4.最后是常量表達(dá)式計(jì)算后的化簡(jiǎn)，將”9<=10”化簡(jiǎn)為”true”帶入到最終的SQL表達(dá)式中完成優(yōu)化。

代價(jià)優(yōu)化

代價(jià)優(yōu)化是用來(lái)確定每個(gè)表，根據(jù)條件是否應(yīng)用索引，應(yīng)用哪個(gè)索引和確定多表連接的順序等問(wèn)題。為了完成代價(jià)優(yōu)化，需要找到一個(gè)代價(jià)最小的方案。

因此，優(yōu)化器是通過(guò)基于代價(jià)的計(jì)算方法來(lái)決定如何執(zhí)行查詢(xún)的（Cost-based Optimization）。

簡(jiǎn)化的過(guò)程如下：

賦值操作代價(jià)：針對(duì)每個(gè)數(shù)據(jù)庫(kù)操作（創(chuàng)建表、返回?cái)?shù)據(jù)集）設(shè)置對(duì)應(yīng)的代價(jià)，這個(gè)代價(jià)值一般設(shè)置為1、0.2之類(lèi)的值，沒(méi)有具體的含義就是對(duì)操作的代價(jià)定義。
計(jì)算操作數(shù)量：將SQL語(yǔ)句中涉及到的操作進(jìn)行邏輯，并且做計(jì)算。說(shuō)白了就是看這次SQL請(qǐng)求需要做哪些具體的數(shù)據(jù)庫(kù)操作。
求和操作代價(jià)：既然知道SQL由哪些數(shù)據(jù)庫(kù)操作組成，同時(shí)知道每個(gè)操作對(duì)應(yīng)的代價(jià)，求和以后就是知道整體SQL執(zhí)行的代價(jià)。
選擇代價(jià)計(jì)劃：如果說(shuō)沒(méi)給SQL執(zhí)行的操作都是一個(gè)計(jì)劃，那么這些操作的不同組合就會(huì)對(duì)應(yīng)不同的計(jì)劃，這里需要選擇整體執(zhí)行代價(jià)最低的操作計(jì)劃，作為這次執(zhí)行SQL語(yǔ)句的代價(jià)計(jì)劃，從而達(dá)到總代價(jià)最低。

這里將配置操作的代價(jià)分為MySQL 服務(wù)層和MySQL 引擎層，MySQL 服務(wù)層主要是定義CPU的代價(jià)，而MySQL 引擎層主要定義IO代價(jià)。MySQL 5.7 引入了兩個(gè)系統(tǒng)表mysql.server_cost和mysql.engine_cost來(lái)分別配置這兩個(gè)層的代價(jià)，如下：

MySQL 服務(wù)層代價(jià)保存在表server_cost中，其具體內(nèi)容如下：
row_evaluate_cost (default 0.2) 計(jì)算符合條件的行的代價(jià)，行數(shù)越多，此項(xiàng)代價(jià)越大
memory_temptable_create_cost (default 2.0) 內(nèi)存臨時(shí)表的創(chuàng)建代價(jià)
memory_temptable_row_cost (default 0.2) 內(nèi)存臨時(shí)表的行代價(jià)
key_compare_cost (default 0.1) 鍵比較的代價(jià)，例如排序
disk_temptable_create_cost (default 40.0) 內(nèi)部myisam或innodb臨時(shí)表的創(chuàng)建代價(jià)
disk_temptable_row_cost (default 1.0) 內(nèi)部myisam或innodb臨時(shí)表的行代價(jià)

由上可以看出創(chuàng)建臨時(shí)表的代價(jià)是很高的，尤其是內(nèi)部的myisam或innodb臨時(shí)表。

MySQL 引擎層代價(jià)保存在表engine_cost中，其具體內(nèi)容如下：

io_block_read_cost (default 1.0) 從磁盤(pán)讀數(shù)據(jù)的代價(jià)，對(duì)innodb來(lái)說(shuō)，表示從磁盤(pán)讀一個(gè)page的代價(jià)
memory_block_read_cost (default 1.0) 從內(nèi)存讀數(shù)據(jù)的代價(jià)，對(duì)innodb來(lái)說(shuō)，表示從buffer pool讀一個(gè)page的代價(jià)

目前io_block_read_cost和memory_block_read_cost默認(rèn)值均為1，實(shí)際生產(chǎn)中建議酌情調(diào)大memory_block_read_cost，特別是對(duì)普通硬盤(pán)的場(chǎng)景。

MySQL會(huì)根據(jù)SQL查詢(xún)生成的查詢(xún)計(jì)劃中對(duì)應(yīng)的操作從上面兩張代價(jià)表中查找對(duì)應(yīng)的代價(jià)值，并且進(jìn)行累加形成最終執(zhí)行SQL計(jì)劃的代價(jià)。再將多種可能的執(zhí)行計(jì)劃進(jìn)行比較，選取最小代價(jià)的計(jì)劃執(zhí)行。

執(zhí)行器

當(dāng)分析器生成查詢(xún)計(jì)劃，并且經(jīng)過(guò)優(yōu)化器以后，就到了執(zhí)行器。執(zhí)行器會(huì)選擇執(zhí)行計(jì)劃開(kāi)始執(zhí)行，但在執(zhí)行之前會(huì)校驗(yàn)請(qǐng)求用戶是否擁有查詢(xún)的權(quán)限，如果沒(méi)有權(quán)限，就會(huì)返回錯(cuò)誤信息，否則將會(huì)去調(diào)用MySQL引擎層的接口，執(zhí)行對(duì)應(yīng)的SQL語(yǔ)句并且返回結(jié)果。

例如SQL：“SELECT * FROM userinfo WHERE username = 'Tom';“

假設(shè)?“username“?字段沒(méi)有設(shè)置索引，就會(huì)調(diào)用存儲(chǔ)引擎從第一條開(kāi)始查，如果碰到了用戶名字是” Tom“，就將結(jié)果集返回，沒(méi)有查找到就查看下一行，重復(fù)上一步的操作，直到讀完整個(gè)表或者找到對(duì)應(yīng)的記錄。

需要注意SQL語(yǔ)句的執(zhí)行順序并不是按照書(shū)寫(xiě)順序來(lái)的，順序的定義會(huì)在分析器中做好，一般是按照如下順序：

圖8?SQL的執(zhí)行順序

總結(jié)

本文從MySQL中SQL語(yǔ)句的執(zhí)行過(guò)程作為切入點(diǎn)，首先介紹了查詢(xún)請(qǐng)求的執(zhí)行流程，其中將MySQL的處理分為MySQL Server層和MySQL存儲(chǔ)引擎層。通過(guò)介紹SQL語(yǔ)句的流轉(zhuǎn)，引出了后面要介紹的5大組件，他們分別是：連接器、查詢(xún)緩存、分析器、優(yōu)化器、執(zhí)行器。

后面的內(nèi)容中對(duì)每個(gè)組件進(jìn)行了詳細(xì)的介紹。連接器，負(fù)責(zé)身份認(rèn)證和權(quán)限鑒別；查詢(xún)緩存，將查詢(xún)的結(jié)果集進(jìn)行緩存，提高查詢(xún)效率；分析器，對(duì)SQL語(yǔ)句執(zhí)行語(yǔ)法分析和語(yǔ)法規(guī)則，生成語(yǔ)法樹(shù)和執(zhí)行計(jì)劃；優(yōu)化器，包括邏輯變換和代價(jià)優(yōu)化；執(zhí)行器，在檢查用戶權(quán)限以后對(duì)數(shù)據(jù)進(jìn)行逐條查詢(xún)，整個(gè)過(guò)程遵守SQL語(yǔ)句的執(zhí)行順序。

END

8張圖，5大組件！了解MySQL查詢(xún)語(yǔ)句執(zhí)行過(guò)程