• 前言

• 一條select語句的執(zhí)行流程

• 建立連接

• 查詢緩存

• 解析器和預(yù)處理器

• 詞法解析和語法解析(Parser)

• 預(yù)處理器(Preprocessor)

• 查詢優(yōu)化器(Query Optimizer)

• 優(yōu)化器可以做哪些優(yōu)化

• 優(yōu)化器并不是萬能的

• 優(yōu)化器如何得到查詢計劃

• 存儲引擎查詢

• 返回結(jié)果

• 一條update語句的執(zhí)行流程

• Buffer Pool

• redo log

• Write-Ahead Logging（WAL）

• redo log是如何刷盤的

• bin log

• bin log和redo log的區(qū)別

• update語句的執(zhí)行流程

• 兩階段提交

• 假如不采用兩階段提交法

• 宕機后的數(shù)據(jù)恢復(fù)規(guī)則

• 總結(jié)

前言

本文基于MySQL5.7版本。

前面幾篇MySQL系列的文章介紹了索引，事務(wù)和鎖相關(guān)知識，那么今天就讓我們來看看當我們執(zhí)行一條select語句和一條update語句的時候,MySQL要經(jīng)過哪些步驟，才能返回我們想要的數(shù)據(jù)。

一條select語句的執(zhí)行流程

MySQL從大方向來說，可以分為 Server 層和存儲引擎層。而Server層包括連接器、查詢緩存、解析器、預(yù)處理器、優(yōu)化器、執(zhí)行器等，最后Server層再通過API接口形式調(diào)用對應(yīng)的存儲引擎層提供的接口。如下圖所示(圖片來源于《高性能MySQL》)：

在這里插入圖片描述

根據(jù)流程圖，一條select查詢大致經(jīng)過以下六個步驟：
1、客戶端發(fā)起一個請求時，首先會建立一個連接
2、服務(wù)端會檢查緩存，如果命中則直接返回，否則繼續(xù)之后后面步驟
3、服務(wù)器端根據(jù)收到的sql語句進行解析，然后對其進行詞法分析，語法分析以及預(yù)處理
4、由優(yōu)化器生成執(zhí)行計劃
5、調(diào)用存儲引擎層API來執(zhí)行查詢
6、返回查詢到的結(jié)果

查詢流程也可以通過如下圖表示(圖片來源于丁奇MySQL45將)：

在這里插入圖片描述

建立連接

第一步建立連接，這個很容易理解，需要特別指出的是MySQL服務(wù)端和客戶端的通信方式采用的是半雙工協(xié)議。

通信方式主要可以分為三種：單工，半雙工，全雙工，如下圖：

在這里插入圖片描述

• 單工：通信的時候，數(shù)據(jù)只能單向傳輸。比如說遙控器，我們只能用遙控器來控制電視機，而不能用電視機來控制遙控器。

• 半雙工：通信的時候，數(shù)據(jù)可以雙向傳輸，但是同一時間只能有一臺服務(wù)器在發(fā)送數(shù)據(jù)，當A給B發(fā)送數(shù)據(jù)的時候，那么B就不能給A發(fā)送數(shù)據(jù)，必須等到A發(fā)送結(jié)束之后，B才能給A發(fā)送數(shù)據(jù)。比如說對講機。

• 全雙工：通信的時候，數(shù)據(jù)可以雙向傳輸，并且可以同時傳輸。比如說我們打電話或者用通信軟件進行語音和視頻通話等。

半雙工協(xié)議讓MySQL通信簡單快速，但是也在一定程度上限制了MySQL的性能，因為一旦從一端開始發(fā)送數(shù)據(jù)，另一端必須要接收完全部數(shù)據(jù)才能做出響應(yīng)。所以說我們批量插入的時候盡量拆分成多次插入而不要一次插入太大數(shù)據(jù)，同樣的查詢語句最好也帶上limit限制條數(shù)，避免一次返回過多數(shù)據(jù)。

MySQL單次傳輸數(shù)據(jù)包的大小可以通過參數(shù)max_allowed_packet控制,默認大小為4MB

SHOW VARIABLES LIKE 'max_allowed_packet';

在這里插入圖片描述

查詢緩存

連接上了之后，如果緩存是打開的，那么就會進入查詢緩存階段，可以通過如下命令查看緩存是否開啟：

SHOW VARIABLES LIKE 'query_cache_type';

在這里插入圖片描述

我們可以看到，緩存默認是關(guān)閉的。這是因為MySQL的緩存使用條件非常苛刻，是通過一個大小寫敏感的哈希值去匹配的，這樣就是說一條查詢語句哪怕只是有一個空格不一致，都會導(dǎo)致無法使用緩存。而且一旦表里面有一行數(shù)據(jù)變動了，那么關(guān)于這種表的所有緩存都會失效。所以一般我們都是不建議使用緩存，MySQL最新的8.0版本已經(jīng)將緩存模塊去掉了。

解析器和預(yù)處理器

跳過了緩存模塊之后，查詢語句會進入解析器進行解析。

詞法解析和語法解析(Parser)

這一步主要的工作就是檢查sql語句的語法對不對，在這里，首先會把我們整個SQL語句打碎，比如：select name from test where id=1，就會被打散成select，name，from，test，where，id，=，1 這8個字符，并且能識別出關(guān)鍵字和非關(guān)鍵字，然后根據(jù)sql語句生成一個數(shù)據(jù)結(jié)構(gòu)，也叫做解析樹(select_lex)，如下圖：

在這里插入圖片描述

預(yù)處理器(Preprocessor)

經(jīng)過了前面的詞法和語法解析，那么至少我們一條sql語句的語法格式是滿足要求了，接下來我們還需要做什么呢？自然是檢查表名，列名以及其他一些信息等是不是真實存在的，預(yù)處理就是做一個表名和字段名等相關(guān)信息合法性的檢測。

查詢優(yōu)化器(Query Optimizer)

經(jīng)過上面的步驟，到這里就得到了一句有效的sql語句了。而對一個查詢語句，尤其是復(fù)雜的多表查詢語句，我們可以有很多種執(zhí)行方式，每種執(zhí)行方式的效率也不一樣，所以這時候就需要查詢優(yōu)化器去選擇一種它認為最高效的執(zhí)行方式。

查詢優(yōu)化器的目的就是根據(jù)解析樹生成不同的執(zhí)行計劃（Execution Plan），然后選擇一種最優(yōu)的執(zhí)行計劃，MySQL 里面使用的是基于開銷（cost）的優(yōu)化器，哪種執(zhí)行計劃開銷最小，就選擇哪種。

我們可以通過變量Last_query_cost來查詢開銷：

SELECT * FROM test;
show status like 'Last_query_cost';

在這里插入圖片描述

上圖中展示的結(jié)果就表示MySQL認為SELECT * FROM test 查詢語句需要做至少2個數(shù)據(jù)頁的隨機查找才能完成上面的查詢。
這個結(jié)果是通過一系列復(fù)雜的運算得到的，包括每個表或者索引的頁面?zhèn)€數(shù)，索引的基數(shù)，索引和數(shù)據(jù)行的長度，索引分布的情況。

優(yōu)化器在評估成本的時候，不會考慮任何緩存的作用，而是假設(shè)讀取任何數(shù)據(jù)都需要經(jīng)過一次IO操作。

優(yōu)化器可以做哪些優(yōu)化

優(yōu)化器可以替我們做很多優(yōu)化，下面列舉一些常用的優(yōu)化：

• 重新定義關(guān)聯(lián)的順序。優(yōu)化器并不一定按照我們寫的查詢關(guān)聯(lián)語句中的關(guān)聯(lián)順序，而是會按照優(yōu)化后的順序進行查詢。

• 將外連接轉(zhuǎn)為為內(nèi)連接。

• 使用等價轉(zhuǎn)換原則。比如a5

• 優(yōu)化COUNT(),MIN()和MAX()

• 預(yù)估并轉(zhuǎn)化為常數(shù)表達式

• 覆蓋索引掃描。想要詳細了解覆蓋索引的可以點擊這里。

• 的查詢優(yōu)化。

• 提前終止查詢。比如我們使用了一個不成立的條件，則會立刻返回空。

• 等值傳播。

• 優(yōu)化IN()語句。在其他很多數(shù)據(jù)庫中in等同于or語句，但是MySQL中會講in中的值先進行排序，然后按照二分查找的方法來確定是否滿足條件。

實際當中優(yōu)化器能做的優(yōu)化遠遠比上面列舉的更多，所以有時候我們不要覺得比優(yōu)化器更聰明，所以大部分情況下我們都可以讓優(yōu)化器做出優(yōu)化就可以了，如果有些我們確定優(yōu)化器沒有選擇最優(yōu)的查詢方案，我們也可以在查詢中通過添加hint提示告知到優(yōu)化器，比如通過force index強制使用索引或者straight_join語句強制優(yōu)化器按我們想要的表順序進行關(guān)聯(lián)。

優(yōu)化器并不是萬能的

MySQL優(yōu)化器也并不是萬能的，并不是總能把我們寫的糟糕的sql語句優(yōu)化成一個高效的查詢語句，而且也有很多種原因會導(dǎo)致優(yōu)化器做出錯誤的選擇：

• 統(tǒng)計信息不準確。MySQL評估成本依賴于存儲引擎提供的的統(tǒng)計信息，然而存儲引擎提供的統(tǒng)計信息有時候會有較大偏差。

• 執(zhí)行計劃的成本估算不等于實際的執(zhí)行成本。比如估算成本的時候不考慮緩存，而實際執(zhí)行有些數(shù)據(jù)在緩存中。

• 優(yōu)化器認為的最優(yōu)可能并不是我們需要的最優(yōu)。比如有時候我們想要時間最短，但是優(yōu)化器

• 優(yōu)化器從不考慮其他并發(fā)的查詢。

• 優(yōu)化器并不總是基本成本的優(yōu)化。有時候也會基于規(guī)則，比如當存在全文索引，查詢時使用了match()子句時，即使選擇其他索引更優(yōu)，優(yōu)化器仍然會選擇全文索引。

• 優(yōu)化器不將不受其控制的操作計算為成本。如執(zhí)行存儲過程或者用戶自定義函數(shù)的成本。

• 優(yōu)化器有時候無法估算所有的執(zhí)行計劃，所以也有可能錯過最優(yōu)執(zhí)行計劃。

優(yōu)化器如何得到查詢計劃

優(yōu)化器聽起來比較抽象，給人一種看不見摸不著的感覺，但是實際上我們也可以通過參數(shù)打開優(yōu)化器追蹤，優(yōu)化器追蹤默認是關(guān)閉的，因為開啟后會影響性能，所以建議是在需要定位問題的時候開啟，并及時關(guān)閉。

SHOW VARIABLES LIKE 'optimizer_trace';
set optimizer_trace='enabled=on';

接下來執(zhí)行一句查詢語句：

SELECT t1.name AS name1,t2.name AS name2 FROM test t1 INNER JOIN test2 t2 ON t1.id=t2.id

這時候優(yōu)化器的分析過程已經(jīng)被記錄下來了，可以通過下面語句查詢：

SELECT * FROM information_schema.optimizer_trace;

得到如下結(jié)果：

上面的圖是為了看數(shù)據(jù)效果，如果需要自己操作的話，需要用shelll命令窗口去執(zhí)行，sqlyog工具中直接查詢出來TRACE列是空的，shell中返回的TRACE列信息如下：

從截圖中的輪廓可以看出來這是一個json數(shù)據(jù)格式。

跟蹤信息主要分為以下三部分(上圖并未將全部內(nèi)容展示出來，感興趣的可以自己去嘗試一下，開啟之后記得及時關(guān)閉哦)：

• 準備階段(join_preparation)：expanded_query中的查詢語句就是優(yōu)化后的sql

• 優(yōu)化階段(join_optimization)：considered_execution_plans中列出來所有的執(zhí)行計劃

• 執(zhí)行階段(join_execution)

存儲引擎查詢

當Server層得到了一條sql語句的執(zhí)行計劃后，這時候就會去調(diào)用存儲引擎層對應(yīng)的API，執(zhí)行查詢了。因為MySQL的存儲引擎是插件式的，所以每種存儲引擎都會對Server提供了一些對應(yīng)的API調(diào)用。

返回結(jié)果

最后，將查詢出得到的結(jié)果返回Server層，如果開啟了緩存，Server層返回數(shù)據(jù)的同時還會寫入緩存。

MySQL將查詢結(jié)果返回是一個增量的逐步返回過程。例如：當我們處理完所有查詢邏輯并開始執(zhí)行查詢并且生成第一條結(jié)果數(shù)據(jù)的時候，MySQL就可以開始逐步的向客戶端傳輸數(shù)據(jù)了。這么做的好處是服務(wù)端無需存儲太多結(jié)果，從而減少內(nèi)存消耗(這個操作可以通過sql _buffer_result來提示優(yōu)化器，和上文說的force index，straight_join一樣都是人為強制優(yōu)化器執(zhí)行我們想要的操作)。

一條update語句的執(zhí)行流程

一條更新語句，其實是增，刪，查的綜合體，查詢語句需要經(jīng)過的流程，更新語句全部需要執(zhí)行一次，因為更新之前必須要先拿到(查詢)需要更新的數(shù)據(jù)。

Buffer Pool

InnnoDB的數(shù)據(jù)都是放在磁盤上的，而磁盤的速度和CPU的速度之間有難以逾越的鴻溝，為了提升效率，就引入了緩沖池技術(shù)，在InnoDB中稱之為Buffer Pool。

從磁盤中讀取數(shù)據(jù)的時候，會先將從磁盤中讀取到的頁放在緩沖池中，這樣下次讀相同的頁的時候，就可以直接從Buffer Pool中獲取。

更新數(shù)據(jù)的時候首先會看數(shù)據(jù)在不在緩沖池中，在的話就直接修改緩沖池中的數(shù)據(jù)，注意，前提是我們不需要對這條數(shù)據(jù)進行唯一性檢查（因為如果要進行唯一性檢查就必須加載磁盤中的數(shù)據(jù)來判斷是否唯一了）

如果只修改了Buffer Pool中的數(shù)據(jù)而不修改磁盤中數(shù)據(jù)，這時候就會造成內(nèi)存和磁盤中數(shù)據(jù)不一致，這種也叫做臟頁。InnoDB 里面有專門的后臺線程把 Buffer Pool 的數(shù)據(jù)寫入到磁盤， 每隔一段時間就一次性地把多個修改寫入磁盤，這個動作就叫做刷臟。

那么現(xiàn)在有一個問題，假如我們更新都需要把數(shù)據(jù)寫入數(shù)據(jù)磁盤，然后磁盤也要找到對應(yīng)的那條記錄，然后再更新，整個過程 IO 成本、查找成本都很高。為了解決這個問題，InnoDB就有了redo log,并且采用了Write-Ahead Logging(WAL)方案實現(xiàn)。

redo log

redo log，即重做日志，是InnoDB引擎所特有,主要用于崩潰修復(fù)(crash-safe)。

Write-Ahead Logging（WAL）

Write-Ahead Logging，即先寫日志，也就是說我們執(zhí)行一個操作的時候會先將操作寫入日志，然后再寫入數(shù)據(jù)磁盤，那么有人就會問了，寫入數(shù)據(jù)表是磁盤操作，寫入redo log也是磁盤操作，同樣都是寫入磁盤，為什么不直接寫入數(shù)據(jù)，而要先寫入日志呢？這不是多此一舉嗎？

設(shè)想一下，假如我們所需要的數(shù)據(jù)是隨機分散在不同頁的不同扇區(qū)中，那么我們?nèi)フ覕?shù)據(jù)的時候就是隨機IO操作，而redo log是循環(huán)寫入的，也就是順序IO。一句話：
刷盤是隨機 I/O，而記錄日志是順序 I/O，順序 I/O 效率更高。因此先把修改寫入日 志，可以延遲刷盤時機，進而提升系統(tǒng)吞吐

redo log是如何刷盤的

InnoDB中的 redo log是固定大小的，也就是說redo log并不是隨著文件寫入慢慢變大，而是一開始就分配好了空間，空間一旦寫滿了，前面的空間就會被覆蓋掉，刷盤的操作是通過Checkpoint實現(xiàn)的。如下圖：

check point 是當前要覆蓋的位置。write pos是當前寫入日志的位置。寫日志的時候是循環(huán)寫的，覆蓋舊記錄前要把記錄更新到數(shù)據(jù)文件。如果write pos和 check point 重疊，說明redo log 已經(jīng)寫滿，這時候需要同步redo log刷到磁盤中。

bin log

MySQL整體來看，其實就有兩塊：一塊是 Server 層，它主要做的是 MySQL功能層面的事情；還有一塊是引擎層，負責(zé)存儲相關(guān)的具體事宜。上面講的redo log是InnoDB 引擎特有的日志，而Server 層也有自己的日志，稱為 binlog（歸檔日志），也叫做二進制日志。

可能有人會問，為什么會有兩份日志呢？
因為最開始 MySQL 里并沒有 InnoDB 引擎。MySQL 自帶的引擎是 MyISAM，但是 MyISAM是不支持事物的，也沒有崩潰恢復(fù)(crash-safe)的能力，binlog日志只能用于歸檔。那么既然InnoDB是需要支持事務(wù)的，那么就必須要有崩潰恢復(fù)(crash-safe)能力，所以就使用另外一套自己的日志系統(tǒng)，也就是基于redo log 來實現(xiàn) crash-safe 能力。

bin log和redo log的區(qū)別

1、redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的Server層實現(xiàn)的，所有引擎都可以使用。
2、redo log 是物理日志，記錄的是“在某個數(shù)據(jù)頁上做了什么修改”；binlog 是邏輯日志，記錄的是這個語句的原始邏輯，比如“給id=2 這一行的c字段加 1 ”。
3、redo log 是循環(huán)寫的，空間固定會用完；binlog 是可以追加寫入的。“追加寫”是指 binlog 文件寫到一定大小后會切換到下一個，并不會覆蓋以前的日志。

update語句的執(zhí)行流程

前面鋪墊了這么多，主要是想讓大家先理解redo log和big log這兩個概念，因為更新操作離不開這兩個文件，接下來我們正式回到正題，一條update語句到底是如何執(zhí)行的，可以通過下圖表示：

上圖可以大概概括為以下幾步：
1、先根據(jù)更新語句的條件，查詢出對應(yīng)的記錄，如果有緩存，也會用到緩存
2、Server端調(diào)用InnoDB引擎API接口，InnoDB引擎將這條數(shù)據(jù)寫到內(nèi)存，同時寫入redo log，并將redo log狀態(tài)設(shè)置為prepare
3、通知Server層，可以正式提交數(shù)據(jù)了
4、Server層收到通知后立刻寫入bin log，然后調(diào)用InnoD對應(yīng)接口發(fā)出commit請求
5、InnoDB收到commit請求后將數(shù)據(jù)設(shè)置為commit狀態(tài)

上面的步驟中，我們注意到，redo log會經(jīng)過兩次提交，這就是兩階段提交。

兩階段提交

兩階段提交是分布式事務(wù)的設(shè)計思想，就是首先會有請求方發(fā)出請求到各個服務(wù)器，然后等其他各個服務(wù)器都準備好之后再通知請求方可以提交了，請求方收到請求后再發(fā)出指令，通知所有服務(wù)器一起提交。

而我們這里redo log是屬于存儲引擎層的日志，bin log是屬于Server層日志，屬于兩個獨立的日志文件，采用兩階段提交就是為了使兩個日志文件邏輯上保持一致

假如不采用兩階段提交法

假如有一條語句id=1,age=18，我們現(xiàn)在要把這條數(shù)據(jù)的age更新為19：

• 先寫 redo log 后寫 binlog
  假設(shè)在redo log 寫完，binlog還沒有寫完的時候，MySQL發(fā)生了宕機(crash)。重啟后因為redo log寫完了，所以會自動進行數(shù)據(jù)恢復(fù)，也就是age=19。但是由于binlog沒寫完就宕機( crash)了，這時候 binlog 里面就沒有記錄這個語句。因此，之后備份日志的時候，存起來的 binlog 里面就沒有這條語句。然后某一天假如我們把數(shù)據(jù)丟失了，需要用bin log進行數(shù)據(jù)恢復(fù)就會發(fā)現(xiàn)少了這一次更新。

• 先寫binlog后寫redo log
  假如在binlog寫完，redo log還沒有寫完的時候，MySQL發(fā)生了宕機(crash)。重啟后因為redo log沒寫完，所以無法進行自動恢復(fù)，那么數(shù)據(jù)就還是age=18了，然后某一天假如我們把數(shù)據(jù)丟失了，需要用binlog進行恢復(fù)又會發(fā)現(xiàn)恢復(fù)出來的數(shù)據(jù)age=19了。

通過以上的兩個假設(shè)我們就會發(fā)現(xiàn)，假如不采用兩階段提交法就會出現(xiàn)數(shù)據(jù)不一致的情況，尤其是在有主從庫的時候，因為主從復(fù)制是基于binlog實現(xiàn)的，如果redo log和bin log不一致，就會導(dǎo)致主從庫數(shù)據(jù)不一致。

宕機后的數(shù)據(jù)恢復(fù)規(guī)則

1、如果 redo log 里面的事務(wù)是完整的，也就是已經(jīng)有了 commit 標識，則直接提交；
2、如果 redo log 里面的事物只有完整的 prepare，則判斷對應(yīng)的事務(wù) binlog 是否存在并完整：如果是，則提交事務(wù)；否則，回滾事務(wù)。

總結(jié)

本文主要分析了select和update語句的執(zhí)行過程，而在分析update語句執(zhí)行過程中，又簡單介紹了redo log和bin log相關(guān)概念，這一部分內(nèi)容在本文中沒有過多深入的講解，僅僅只是為了讓大家去理解更新流程而做了簡單的介紹，像redo log和其對應(yīng)的緩存之間的關(guān)系，redo log刷盤策略，bin log寫入策略，有了bin log為何還需要redo log等等問題本文中并沒有給出明確的解釋，因為本文篇幅有限，深入之后就會涉及到InnoDB引擎的存儲結(jié)構(gòu)以及更底層的一些知識

— 本文結(jié)束 —