作者     沈啟超，19年碩士畢業(yè)于東南大學(xué)，目前在騰訊CSIG企業(yè)產(chǎn)品部擔(dān)任后臺開發(fā)，同時(shí)也參與公司內(nèi)部存儲開源組件MySync的開發(fā)。

Server層主要 包含連接器、檢索內(nèi)存、分析器、優(yōu)化器、執(zhí)行器等，所有跨存儲引擎的功能均于這一層構(gòu)建，例如存儲過程、觸發(fā)器、視圖，函數(shù)等，有一個標(biāo)準(zhǔn)化的binglog日志模塊。

存儲引擎負(fù)責(zé)數(shù)據(jù)的存儲與存取，使用可更換的插件式架構(gòu)，擁有InnoDB、MyISAM、Memory等多個存儲引擎，其中InnoDB引擎有redo log日志模塊。如下圖所示

實(shí)驗(yàn)環(huán)境 

操作系統(tǒng)內(nèi)核版本：Tencent tlinux release 2.2

MySQL數(shù)據(jù)庫版本：5.7.10

創(chuàng)建新表tb_article，創(chuàng)建了兩個索引：index_title、index_author_id，表結(jié)構(gòu)如下：

我們嘗試插入一些數(shù)據(jù)：

現(xiàn)執(zhí)行SQL語句，select * from tb_article where author_id=20 and title='b'; 分析該SQL語句的執(zhí)行過程和優(yōu)化策略。

MySQL執(zhí)行SQL語句過程 

一、MySQL客戶端和服務(wù)器通訊

客戶端按照MySQL通信協(xié)議將SQL發(fā)送到服務(wù)端，SQL到達(dá)服務(wù)端后，服務(wù)端會單起一個線程執(zhí)行SQL。MySQL客戶端和服務(wù)器之間的通訊協(xié)議是“半雙工”的。

二、查詢狀態(tài)

對于MySQL連接，任何時(shí)刻都有一個狀態(tài)，該狀態(tài)表示了MySQL當(dāng)前正在做什么。使用show full processlist命令查看當(dāng)前狀態(tài)。在一個查詢生命周期中，狀態(tài)會變化很多次，下面是這些狀態(tài)的解釋：

1. sleep： 線程正在等待客戶端發(fā)送新的請求；

2. query： 線程正在執(zhí)行查詢或者正在將結(jié)果發(fā)送給客戶端；

3. locked： 在MySQL服務(wù)器層，該線程正在等待表鎖。 在存儲引擎級別實(shí)現(xiàn)的鎖，例如InnoDB的行鎖，并不會體現(xiàn)在線程狀態(tài)中。 對于MyISAM來說這是一個比較典型的狀態(tài)；

4. analyzing and statistics： 線程正在收集存儲引擎的統(tǒng)計(jì)信息，并生成查詢的執(zhí)行計(jì)劃；

5. copying to tmp table： 線程在執(zhí)行查詢，并且將其結(jié)果集復(fù)制到一個臨時(shí)表中，這種狀態(tài)一般要么是做group by操作，要么是文件排序操作，或者union操作。 如果這個狀態(tài)后面還有on disk標(biāo)記，那表示MySQL正在將一個內(nèi)存臨時(shí)表放到磁盤上；

6. sorting result： 線程正在對結(jié)果集進(jìn)行排序；

7. sending data： 線程可能在多個狀態(tài)間傳送數(shù)據(jù)，或者在生成結(jié)果集，或者在想客戶端返回?cái)?shù)據(jù)。

三、查詢緩存

MySQL的緩存主要的作用是為了提升查詢的效率，緩存以key和value的哈希表形式存儲，key是具體的sql語句，value是結(jié)果的集合。如果無法命中緩存,就繼續(xù)走到分析器的的一步,如果命中緩存就直接返回給客戶端 。

如果使用查詢緩存，在進(jìn)行讀寫操作時(shí)會帶來額外的資源消耗，如果在一個寫多讀少的環(huán)境中，緩存會頻繁的新增和失效。MySQL8.0版本開始取消查詢緩存。

四、查詢優(yōu)化處理

查詢的生命周期的下一步是將一個SQL轉(zhuǎn)換成一個執(zhí)行計(jì)劃，MySQL在依照這個執(zhí)行計(jì)劃和存儲引擎進(jìn)行交互。這包含多個子階段：解析SQL、預(yù)處理、優(yōu)化SQL執(zhí)行計(jì)劃。這個過程中任何錯誤都可能終止查詢。

1. 語 法解析器和預(yù)處理： 首先MySQL通過關(guān)鍵字將SQL語句進(jìn)行解析，并生成一顆對應(yīng)的“解析樹”。 MySQL解析器將使用mysql語法規(guī)則驗(yàn)證和解析查詢； 預(yù)處理器則根據(jù)一些MySQL規(guī)則進(jìn)一步檢查解析數(shù)是否合法。

2. 查詢優(yōu)化器： 當(dāng)語法樹被認(rèn)為是合法的了，并且由優(yōu)化器將其轉(zhuǎn)化成執(zhí)行計(jì)劃。 一條查詢可以有很多種執(zhí)行方式，最后都返回相同的結(jié)果。 優(yōu)化器的作用就是找到這其中最好的執(zhí)行計(jì)劃。

3. 執(zhí)行計(jì)劃： MySQL不會生成查詢字節(jié)碼來執(zhí)行查詢，MySQL生成查詢的一棵指令樹，然后通過存儲引擎執(zhí)行完成這棵指令樹并返回結(jié)果。 最終的執(zhí)行計(jì)劃包含了重構(gòu)查詢的全部信息。

五、查詢執(zhí)行引擎

在解析和優(yōu)化階段，MySQL將生成查詢對應(yīng)的執(zhí)行計(jì)劃，MySQL根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃，調(diào)用存儲引擎的API來執(zhí)行查詢。

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

了解select * from tb_article where author_id=20 and title='b';性能和優(yōu)化策略，一般采用explain命令進(jìn)行分析。

MySQL explain

MySQL Query Optimizer通過執(zhí)行explain命令來獲取一個Query在當(dāng)前狀態(tài)的數(shù)據(jù)庫中的執(zhí)行計(jì)劃。expain出來的信息有10列，分別是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

下面對這些字段出現(xiàn)的可能進(jìn)行解釋：

1. id

id列的編號是 select 的序列號，有幾個 select 就有幾個id，并且id的順序是按 select 出現(xiàn)的順序增長的。MySQL將 select 查詢分為簡單查詢和復(fù)雜查詢。復(fù)雜查詢分為三類：簡單子查詢、派生表（from語句中的子查詢）、union 查詢。

2.select_type

(1) SIMPLE(簡單SELECT,不使用UNION或子查詢等)；

(2) PRIMARY(查詢中若包含任何復(fù)雜的子部分,最外層的select被標(biāo)記為PRIMARY)；

(3) UNION(UNION中的第二個或后面的SELECT語句)；

(4) DEPENDENT UNION(UNION中的第二個或后面的SELECT語句，取決于外面的查詢)；

(5) UNION RESULT(UNION的結(jié)果)；

(6) SUBQUERY(子查詢中的第一個SELECT)；

(7) DEPENDENT SUBQUERY(子查詢中的第一個SELECT，取決于外面的查詢)；

(8) DERIVED(派生表的SELECT, FROM子句的子查詢)；

(9) UNCACHEABLE SUBQUERY(一個子查詢的結(jié)果不能被緩存，必須重新評估外鏈接的第一行)。

3. table

這一列表示 explain 的一行正在訪問哪個表。當(dāng) from 子句中有子查詢時(shí)，table列是 <derivenN> 格式，表示當(dāng)前查詢依賴 id=N 的查詢，于是先執(zhí)行 id=N 的查詢。當(dāng)有union時(shí)，UNION RESULT的table列的值為 <union1,2>，1和2表示參與 union 的select行id。

4. type

表示MySQL在表中找到所需行的方式，又稱“訪問類型”。

常用的類型有：ALL, index,  range, ref, eq_ref, const, system, NULL（從左到右，性能從差到好）

ALL：Full Table Scan， MySQL將遍歷全表以找到匹配的行

index: Full Index Scan，index與ALL區(qū)別為index類型只遍歷索引樹

range:只檢索給定范圍的行，使用一個索引來選擇行

ref: 表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值。相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前綴，索引要和某個值相比較，可能會找到多個符合條件的行。

eq_ref: 類似ref，區(qū)別就在使用的索引是唯一索引，對于每個索引鍵值，表中只有一條記錄匹配，簡單來說，就是多表連接中使用primary key或者 unique key作為關(guān)聯(lián)條件

const、system: 當(dāng)MySQL對查詢某部分進(jìn)行優(yōu)化，并轉(zhuǎn)換為一個常量時(shí)，使用這些類型訪問。如將主鍵置于where列表中，MySQL就能將該查詢轉(zhuǎn)換為一個常量,system是const類型的特例，當(dāng)查詢的表只有一行的情況下，使用system

NULL: MySQL在優(yōu)化過程中分解語句，執(zhí)行時(shí)甚至不用訪問表或索引，例如從一個索引列里選取最小值可以通過單獨(dú)索引查找完成。

5. possible_keys

這一列顯示查詢可能使用哪些索引來查找。 

explain 時(shí)可能出現(xiàn) possible_keys 有列，而 key 顯示 NULL 的情況，這種情況是因?yàn)楸碇袛?shù)據(jù)不多，MySQL認(rèn)為索引對此查詢幫助不大，選擇了全表查詢。 

如果該列是NULL，則沒有相關(guān)的索引。在這種情況下，可以通過檢查 where 子句看是否可以創(chuàng)造一個適當(dāng)?shù)乃饕齺硖岣卟樵冃阅?，然后?explain 查看效果。

6. key

key列顯示MySQL實(shí)際決定使用的鍵（索引）

如果沒有選擇索引，鍵是NULL。要想強(qiáng)制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

7. key_len

表示索引中使用的字節(jié)數(shù)，可通過該列計(jì)算查詢中使用的索引的長度（key_len顯示的值為索引字段的最大可能長度，并非實(shí)際使用長度，即key_len是根據(jù)表定義計(jì)算而得，不是通過表內(nèi)檢索出的）。

不損失精確性的情況下，長度越短越好。

8. ref

表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值。

9. rows

表示MySQL根據(jù)表統(tǒng)計(jì)信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數(shù)，這個不是結(jié)果集里的行數(shù)。

10. Extra

該列包含MySQL解決查詢的詳細(xì)信息,有以下幾種情況：

Using index：這發(fā)生在對表的請求列都是同一索引的部分的時(shí)候，返回的列數(shù)據(jù)只使用了索引中的信息，而沒有再去訪問表中的行記錄，是性能高的表現(xiàn)。

Using where：列數(shù)據(jù)是從僅僅使用了索引中的信息而沒有讀取實(shí)際的行動的表返回的，這發(fā)生在對表的全部的請求列都是同一個索引的部分的時(shí)候，表示mysql服務(wù)器將在存儲引擎檢索行后再進(jìn)行過濾。

Using temporary：表示MySQL需要使用臨時(shí)表來存儲結(jié)果集，常見于排序和分組查詢。

Using filesort：MySQL中無法利用索引完成的排序操作稱為“文件排序”，對結(jié)果使用一個外部索引排序，而不是按索引次序從表里讀取行。此時(shí)mysql會根據(jù)聯(lián)接類型瀏覽所有符合條件的記錄，并保存排序關(guān)鍵字和行指針，然后排序關(guān)鍵字并按順序檢索行信息。這種情況下一般也是要考慮使用索引來優(yōu)化的。

Using join buffer：改值強(qiáng)調(diào)了在獲取連接條件時(shí)沒有使用索引，并且需要連接緩沖區(qū)來存儲中間結(jié)果。如果出現(xiàn)了這個值，那應(yīng)該注意，根據(jù)查詢的具體情況可能需要添加索引來改進(jìn)能。

Impossible where：這個值強(qiáng)調(diào)了where語句會導(dǎo)致沒有符合條件的行。

執(zhí)行explain語句

explain select * from tb_article where author_id=20 and title='b'; 

可以發(fā)現(xiàn)，執(zhí)行這條SQL語句實(shí)際上沒有走index_title索引，而是選擇走index_author_id索引。

打開optimizer trace功能：

SET optimizer_trace="enabled=on"; 
 
select * from information_schema.optimizer_trace\G; 

執(zhí)行計(jì)劃最終選擇了index_author_id索引，原因是index_author_id的cost小于index_title。這里需要介紹MySQL的代價(jià)計(jì)算模型。

MySQL代價(jià)模型 

總代價(jià)模型：COST = CPU Cost + IO Cost

MySQL在cost類型上分為IO、CPU和Memory，MySQL5.7的代價(jià)模型還在完善中，Memory的代價(jià)雖然已經(jīng)收集了，但還沒有計(jì)算在最終的代價(jià)中。

MySQL5.7在源碼上對cost模型進(jìn)行了大量重構(gòu)，代價(jià)分為server層和engine層。server層主要是CPU代價(jià)，而engine層主要是IO代價(jià)。MySQL5.7 引入了兩個系統(tǒng)表mysql.server_cost和mysql.engine_cost來分別配置這兩個層的代價(jià)。

以下分析均基于MySQL5.7.10

server_cost

1. row_evaluate_cost (default 0.2) 計(jì)算符合條件的行的代價(jià)，行數(shù)越多，此項(xiàng)代價(jià)越大；

2. memory_temptable_create_cost (default 2.0) 內(nèi)存臨時(shí)表的創(chuàng)建代價(jià)；

3. memory_temptable_row_cost (default 0.2) 內(nèi)存臨時(shí)表的行代價(jià)；

4. key_compare_cost (default 0.1) 鍵比較的代價(jià)，例如排序；

5. disk_temptable_create_cost (default 40.0) 內(nèi)部myisam或innodb臨時(shí)表的創(chuàng)建代價(jià)；

6. disk_temptable_row_cost (default 1.0) 內(nèi)部myisam或innodb臨時(shí)表的行代價(jià)；

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

engine_cost

1. io_block_read_cost (default 1.0) 從磁盤讀數(shù)據(jù)的cost，對innodb來說，表示從磁盤讀一個page的cost；

2. memory_block_read_cost (default 1.0）；

從內(nèi)存讀數(shù)據(jù)的cost，對innodb來說，表示從buffer pool讀一個page的cost。

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

對表tb_article創(chuàng)建復(fù)合索引index_title_author

ALTER TABLE tb_article ADD KEY index_title_author(`title`,`author_id`); 
 
select * from tb_article where author_id=20 and title='b'; 

index_author_id和index_title_author的cost相等，MySQL會優(yōu)先選擇葉子塊數(shù)量較少的索引。

對于SQL語句：select title, author_id from tb_article where author_id=20 and title='b';

MySQL會優(yōu)先選擇走復(fù)合索引index_title_author，原因是index_title_author是索引覆蓋掃描，不需要回表，性能較高。