SQL是怎樣執(zhí)行的

下面的查詢SQL語(yǔ)句是經(jīng)過(guò)哪些階段，然后把數(shù)據(jù)返回給客戶端的？

select * from t where a = 1;

下面的更新語(yǔ)句又是怎樣執(zhí)行的？

update t set a = a+1 where id =1;

我們都知道磁盤IO性能的問(wèn)題，那么針對(duì)更新操作這種隨機(jī)寫，MySQL又是怎么進(jìn)行優(yōu)化的呢？

帶著這些疑問(wèn)，我們一起來(lái)學(xué)習(xí)下MySQL的相關(guān)思想和機(jī)制。

資料參考來(lái)自極客時(shí)間MySQL實(shí)戰(zhàn)45講

MySQL 服務(wù)架構(gòu)

MySQL 整體邏輯架構(gòu)如下圖，從圖中可以清楚的看到一個(gè)SQL在各個(gè)模塊中的執(zhí)行過(guò)程。

圖一：MySQL邏輯架構(gòu)

從總體來(lái)看，可以分為服務(wù)層和存儲(chǔ)引擎層。服務(wù)層主要用于連接管理，語(yǔ)法分析檢查，SQL優(yōu)化，以及存儲(chǔ)引擎的調(diào)用來(lái)獲取結(jié)果。除了這些，服務(wù)層還內(nèi)置了所有的函數(shù)，如日期、時(shí)間、數(shù)學(xué)操作等，所有的跨存儲(chǔ)引擎的功能也在這一層實(shí)現(xiàn)，比如存儲(chǔ)過(guò)程，觸發(fā)器、試圖等。

存儲(chǔ)引擎層主要提供數(shù)據(jù)的讀取和存儲(chǔ)的能力，存儲(chǔ)引擎層架構(gòu)是采用插件式開發(fā)的，目前的常見的存儲(chǔ)引擎主要有InnoDB、MyISAM、Memory等。MySQL5.5 以后默認(rèn)的存儲(chǔ)引擎是InnoDB，后面我們不特殊說(shuō)明的情況下，所描述的引擎都是InnoDB 引擎。

連接器

客戶端發(fā)起連接請(qǐng)求，完成TCP連接后，連接器去權(quán)限表查詢?cè)撜?qǐng)求的用戶的權(quán)限，之后該鏈接的權(quán)限判斷邏輯，都依賴此時(shí)讀取到的權(quán)限。也就是說(shuō)，連接建立完畢，此時(shí)修改用戶的權(quán)限，不會(huì)對(duì)該鏈接產(chǎn)生任何影響。

查詢緩存

完成連接后，執(zhí)行select 語(yǔ)句就會(huì)進(jìn)入到查詢緩存，如果命中了，則判斷下是否有該表的讀權(quán)限，然后返回。緩存是通過(guò)key-value 形式存儲(chǔ)的，key你之前執(zhí)行的SQL，value 結(jié)果集。不過(guò)select 語(yǔ)句所涉及的表任何一個(gè)發(fā)生了更新，這個(gè)緩存就會(huì)失效。因此查詢緩存的命中率不高，后續(xù)MySQL8.0 已廢棄該功能?？梢酝ㄟ^(guò)設(shè)置query_cache_type，來(lái)控制使用查詢緩存的機(jī)制。

分析器

這一模塊主要是針對(duì)SQL進(jìn)行詞法分析和語(yǔ)法分析。語(yǔ)句執(zhí)行到這一步，先進(jìn)行詞法分析，提取關(guān)鍵詞，獲取對(duì)應(yīng)的語(yǔ)句特征詞，如 表名 t，做完詞法分析后，在進(jìn)行語(yǔ)法分析，檢查語(yǔ)法是否存在異常。分析器還會(huì)檢測(cè)表、列等是否存在。

優(yōu)化器

分析器分析校驗(yàn)通過(guò)后，來(lái)到優(yōu)化器階段。優(yōu)化器會(huì)基于成本模型去分析SQL，然后給出相應(yīng)的執(zhí)行計(jì)劃。不同的執(zhí)行計(jì)劃，邏輯結(jié)果相同，但是性能可能相差甚遠(yuǎn)。

執(zhí)行器

MySQL 通過(guò)分析器知道你要做什么，通過(guò)優(yōu)化器知道該怎么做，那么執(zhí)行器就是去執(zhí)行了。不過(guò)在執(zhí)行前，會(huì)校驗(yàn)下你有沒(méi)有對(duì)應(yīng)表的操作權(quán)限，如果沒(méi)有則返回沒(méi)有權(quán)限的錯(cuò)誤。與查詢緩存不同的是，查詢緩存是返回結(jié)果的時(shí)候，才進(jìn)行表操作權(quán)限的校驗(yàn)。

查詢語(yǔ)句是怎樣執(zhí)行的

如開題的查詢SQL，如果a 列不是索引，那么可以理解MySQL執(zhí)行如下操作：

1. 調(diào)用存儲(chǔ)引擎接口，獲取 t 表一行數(shù)據(jù)。校驗(yàn)數(shù)據(jù)中 a 的值是否 等于1，是則保存在結(jié)果集，不是則跳過(guò)。
2. 調(diào)用引擎接口，獲取下一行，重復(fù)進(jìn)行判斷邏輯，直至取完表的最后一行。
3. 執(zhí)行器將上述遍歷過(guò)程中，產(chǎn)生的結(jié)果集，返回給客戶端。

可以看出執(zhí)行器查詢的過(guò)程，就是重復(fù)循環(huán)獲取和判斷的過(guò)程。如果循環(huán)很多次的話，那么性能就會(huì)變得非常糟糕。如果觸發(fā)了索引，那么存儲(chǔ)引擎將通過(guò)索引進(jìn)行返回行的過(guò)濾，減少循環(huán)次數(shù)。

更新語(yǔ)句是怎樣執(zhí)行的

我們看下 update t set c=c+1 where ID = 2 這個(gè)更新語(yǔ)句的執(zhí)行過(guò)程。

圖二：MySQL兩階段提交

如圖，深綠色背景的動(dòng)作發(fā)生在MySQL server層，淺綠色背景的發(fā)生在InnoDB引擎層。MySQL 通過(guò)redo log 和binlog的兩階段，保證了事務(wù)的完整性。

日志系統(tǒng)

我們都知道，磁盤的IO是很稀缺的系統(tǒng)資源，其中可以分為順序讀、順序?qū)?、隨機(jī)讀、隨機(jī)寫，其中順序操作效率高于隨機(jī)操作。

我們操作數(shù)據(jù)庫(kù)，增刪改查，肯定不能保證磁盤順序的操作，其中更新、插入、刪除都可以看做對(duì)磁盤的寫操作，查詢看做讀操作。我們先不去關(guān)心讀的問(wèn)題，先來(lái)看看MySQL是怎樣應(yīng)對(duì)隨機(jī)寫。

我們先來(lái)看下這樣的一個(gè)場(chǎng)景：

小明 從學(xué)校圖書館借了一本《高性能MySQL》，經(jīng)過(guò)1個(gè)月的努力，終于看完了，于是決定把書還了。來(lái)到圖書館門口，在門衛(wèi)處登記：小明還《高性能MySQL》，然后門衛(wèi)放行；來(lái)到圖書管理員處，將書交給管理員，管理員將這本書打上標(biāo)簽，4號(hào)書架第一行的第三個(gè)的位置；如果此時(shí)，每來(lái)一個(gè)人還書，管理員都將書放回到書架，那是多么恐怖的一件事情，估摸著管理員也是衣帶漸寬終不悔了。這個(gè)時(shí)候，管理員一般都是在附近放幾個(gè)收納箱，然后將書按照順序放置到收納箱中；然后小明離開，門衛(wèi)處登記：小明還《高性能MySQL》完成；志愿者會(huì)定期的將收納箱中的書按照管理員標(biāo)記的位置，放置到對(duì)應(yīng)的書架上。當(dāng)然門衛(wèi)登記這事是我添加的。

從場(chǎng)景中，我們可以如下抽出

門衛(wèi)處登記：binlog，記錄一些邏輯操作，如小明還《高性能MySQL》。

管理員登記：redo log，記錄物理操作，將《高性能MySQL》放還至4號(hào)書架，第一行的第三個(gè)位置。

志愿者：InnoDB后臺(tái)進(jìn)程，將數(shù)據(jù)頁(yè)刷到磁盤上；即志愿者將《高性能MySQL》，書放還至4號(hào)書架，第一行的第三個(gè)位置。

這種先寫日志，再寫磁盤的技術(shù)，我們稱之為WAL（Write-Ahead logging）。

redo log

redo log 主要記錄了InnoDB 引擎的數(shù)據(jù)頁(yè)的物理操作日志，就好比上述場(chǎng)景的收納箱。InnoDB 的redo log 是固定大小的，比如可以配置一組4個(gè)文件，每個(gè)文件1GB，那么這寫”收納箱“可以暫存4GB 的操作。如下圖，從頭開始寫，寫到文件末尾，又回到開頭循環(huán)寫。

圖三：redo log

?

write pos：當(dāng)前寫記錄的位置，每記錄一條，順時(shí)針移動(dòng)一次。

check point : 是當(dāng)前擦除記錄的位置，每擦除一條記錄，順時(shí)針移動(dòng)一次。

write pos 和check point 之間，代表著”收納箱“還可以放書的地方。如果write pos 追上了 checkpoint，代表著收納箱滿了，此時(shí)暫停接收還書，先把收納箱的書放置一部分到對(duì)應(yīng)的書架上，把checkpoint 往前推進(jìn)下，再繼續(xù)執(zhí)行還書的流程。

有了redo log，InnoDB 就可以保證數(shù)據(jù)庫(kù)發(fā)生異常重啟，之前提交的記錄都不會(huì)丟失。也就是crash-safe能力。

binlog

binlog 就類似于上面的場(chǎng)景中的門衛(wèi)登記，記錄著邏輯日志，如小明還《高性能MySQL》，小明還《高性能MySQL》完成。

binlog采用追加寫的方式，發(fā)生在MySQL的服務(wù)層，主要用來(lái)歸檔使用，MySQL 主從同步，是基于binlog的。日志記錄有三種模式，ROW、statement、mixed。

ROW：可以理解為記錄的變化，可以從日志里看出行記錄的變化前和變化后的狀態(tài)。

Statement：可以理解為SQL語(yǔ)句。

Mixed：是ROW 和Statement 混合著使用。

我們先看下這兩個(gè)日志的不同之處：

1. redo log 是InnoDB 引擎特有的；binlog 是MySQL Server層實(shí)現(xiàn)的，所有的引擎都可以使用。
2. redo log 是物理日志，記錄的是某個(gè)數(shù)據(jù)頁(yè)上做了什么修改；binlog 是邏輯日志，記錄了語(yǔ)句的原始邏輯，如 給 id= 1 這行的 a字段加1。
3. redo log 是循環(huán)寫，空間固定會(huì)用完；binlog 是追加寫，binlog文件寫到一定大小，會(huì)切換下一個(gè)，不會(huì)覆蓋以前的日志。
4. redo log 可以保證提交的數(shù)據(jù)，在數(shù)據(jù)庫(kù)異常重啟后，不丟失。binlog不可以保證。

為什么binlog沒(méi)有crash-safe 能力

前面我們提到，MySQL采用WAL技術(shù)，來(lái)保證高效的性能的。如果沒(méi)有redo log，引擎層先把數(shù)據(jù)寫入內(nèi)存，然后binlog commit，這時(shí)數(shù)據(jù)庫(kù)發(fā)生了重啟，但是引擎層是無(wú)法確保內(nèi)存中的數(shù)據(jù)頁(yè)已經(jīng)持久化到磁盤的。但是如果redo log 存在就不一樣了，redo log會(huì)將數(shù)據(jù)頁(yè)的物理操作寫到日志里，這個(gè)時(shí)候數(shù)據(jù)庫(kù)異常重新，可以通過(guò)redo log 進(jìn)行數(shù)據(jù)頁(yè)恢復(fù)。

為什么兩階段提交

如圖二所示，redo log 和binlog 通過(guò)兩階段提交，保證一個(gè)事務(wù)的完整性的。為什么要兩階段提交，直接提交redo log 或者直接提交binlog為什么不可以？我們看下面兩個(gè)場(chǎng)景。

redo log prepare 階段直接commit，然后再binlog commit；如果redo log commit了，binlog 尚未commit，此時(shí)如果crash，那么主從模式下，從庫(kù)比主庫(kù)少了一次數(shù)據(jù)庫(kù)操作。

先binlog commit，然后redo log commit，redo log不存在prepare 階段。這樣的場(chǎng)景，如果redo log commit 前binlog commit 后發(fā)生了crash，則從庫(kù)會(huì)比主庫(kù)多了一次數(shù)據(jù)庫(kù)操作，總之不采用兩階段提交，會(huì)導(dǎo)致主從不一致，事務(wù)的完整性得不到保障。

如圖三所示，

1. 如果redo log 在prepare階段發(fā)生了crash，那么可以肯定的，本次數(shù)據(jù)回滾。
2. 如果在redo log commit 前，prepare 后發(fā)生了crash，這種情況分下面兩種：
   1. binlog commit 則 完成redo log commit。
   2. binlog 事務(wù)不完整，即沒(méi)有commit，則整個(gè)事務(wù)rollback。

以上就是一個(gè)SQL在MySQL中是怎樣執(zhí)行的，以及MySQL日志系統(tǒng)是怎么保證事務(wù)的。后面有時(shí)間，我們?cè)谝黄饘W(xué)習(xí)MySQL 多版本