MySQL 的一條語句是怎么執(zhí)行的

該文為《 MySQL 實(shí)戰(zhàn) 45 講》的學(xué)習(xí)筆記垛贤，感謝查看日川，如有錯誤寓盗，歡迎指正

一、MySQL 的基礎(chǔ)架構(gòu)

以下就是 MySQL 的基礎(chǔ)架構(gòu)圖望侈。

在這里插入圖片描述

在 Linux 中安裝 MySQL 時印蔬，最少需要安裝 mysql-server 以及 mysql-client，而服務(wù)端中又包含了 Server 層和存儲引擎脱衙。

Server 層包含了連接器侥猬，查詢緩存，分析器捐韩，優(yōu)化器退唠，執(zhí)行器，以及內(nèi)置函數(shù)（日期荤胁，時間瞧预，數(shù)學(xué)和加密函數(shù)等），所有跨存儲引擎的功能都在這一層實(shí)現(xiàn)仅政。比如存儲過程垢油，觸發(fā)器，視圖等圆丹。

存儲引擎層是獨(dú)立的滩愁，可以理解為插件形式，Server 層接入了好幾種存儲引擎辫封，比如MyISAM硝枉、InnoDB、Memory等倦微，MySQL 5.5.5 之后默認(rèn)的存儲引擎是InnoDB妻味。不管你的 MySQL 使用了多少種存儲引擎，它們都是共享一個 Server 層欣福。

如果在建表時（create table）责球，想指定使用其它引擎，可以加上engine=MyISAM實(shí)現(xiàn)劣欢。

二棕诵、查詢語句的執(zhí)行過程

語句示例：

mysql> select * from T where id=10

2.1 連接器

連接器負(fù)責(zé)接收處理客戶端發(fā)送過來的連接請求裁良，獲取權(quán)限凿将，維持和管理連接。

客戶端可以使用 mysql-client价脾，通過命令行mysql -uroot -p進(jìn)行建立連接牧抵。也可以使用第三方工具如Navicat建立連接。連接過程也是走的TCP/IP協(xié)議，有經(jīng)典的3次握手過程犀变。

連接器先判斷用戶名密碼是否正確妹孙，不正確會返回Access denied for user錯誤；正確的話获枝，會到權(quán)限表中查詢出該用戶的權(quán)限蠢正，只要該連接未斷開，將會一直使用該權(quán)限省店。

這也就是為什么有時候我們即使修改了用戶的權(quán)限嚣崭，也不會立刻生效，必須斷開重連懦傍，才能生效雹舀。當(dāng)然如果該連接長時間處于空閑狀態(tài)（連接上以后沒有動作），默認(rèn) 8 小時以后就會自動斷開該連接粗俱。這個 8 小時是由wait_timeout來控制的说榆。

通過show processlist可以查看哪些連接處于空閑狀態(tài)，Command為Sleep的就是空閑連接寸认，可以通過kill Id來手動斷開連接签财，一般在死鎖或者事務(wù)阻塞的時候會用到。

mysql> show processlist;
+----+---------+-----------------+------+---------+------+-------+------------------+
| Id | User    | Host            | db   | Command | Time | State | Info             |
+----+---------+-----------------+------+---------+------+-------+------------------+
|  3 | root    | localhost:64511 | NULL | Query   |    0 | init  | show processlist |
|  4 | ruhrbim | localhost:64519 | NULL | Sleep   |    3 |       | NULL             |
+----+---------+-----------------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

mysql>

MySQL 5.7及更新版本偏塞，可以使用mysql_reset_connection來初始化連接資源荠卷，這個操作不需要重新做權(quán)限驗(yàn)證，直接恢復(fù)到剛創(chuàng)建完連接時的狀態(tài)烛愧。

2.2 查詢緩存

查詢緩存中存儲的是之前的查詢語句及結(jié)果油宜，以key-value的形式存儲，key是查詢語句怜姿，value是查詢結(jié)果慎冤。在執(zhí)行select語句之前，會先去查詢緩存看一下沧卢，如果有結(jié)果蚁堤，直接從查詢緩存返回，不經(jīng)過后面的分析器但狭、優(yōu)化器披诗、執(zhí)行器等。如果沒有結(jié)果立磁，就會往后依次執(zhí)行一遍呈队，最后把語句及結(jié)果存入查詢緩存，以便下次使用唱歧。

查詢緩存主要針對的是變動不頻繁的表宪摧，只要表發(fā)生了變更粒竖，那么這個表上的查詢緩存都會被清空。MySQL也提供了"按需使用"的方法几于，將query_cache_type設(shè)置為DEMAND蕊苗，這樣默認(rèn) SQL 語句都不使用查詢緩存，要使用的時候沿彭，可以通過SQL CACHE顯式指定朽砰。

mysql> select SQL_CACHE * from T where ID=10；

Tips：MySQL 8.0 將查詢緩存功能直接去掉了

2.3 分析器

沒有命中查詢緩存喉刘，就會到分析器這一步锅移。分析器是對 SQL 語句做解析的。

首先進(jìn)行「詞法分析」饱搏，根據(jù)select判斷是一個查詢語句非剃，還要把字符串T識別為表名 T，字符串ID識別為列 ID推沸。

然后進(jìn)行「語法分析」备绽，分析這一行 SQL 語句是否滿足 MySQL 語法。

mysql> select * fron huanzi;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fron huanzi' at line 1
mysql>

我們把from寫成了fron鬓催，分析器做語法分析時識別出這里有問題肺素，并將問題點(diǎn)定位出來了，在use near后面就是宇驾。

2.4 優(yōu)化器

現(xiàn)在 MySQL 已經(jīng)知道要做什么了倍靡，但在開始執(zhí)行 SQL 語句之前還要經(jīng)過優(yōu)化器的處理。
優(yōu)化器能夠選擇使用哪個索引课舍，或者在多表關(guān)聯(lián)的時候塌西，選擇連接的順序。

當(dāng)然有時候優(yōu)化器也會選擇錯索引筝尾，我們可以使用force index(有索引的列名)來強(qiáng)制指定使用某一個索引捡需。

mysql> select * from t force index(a) where a between 10000 and 20000;

2.5 執(zhí)行器

SQL 語句經(jīng)過了以上步驟，最終到達(dá)執(zhí)行器筹淫，執(zhí)行器的作用就是執(zhí)行 SQL 語句站辉。

開始執(zhí)行的時候，要先判斷一下是否有執(zhí)行該語句的權(quán)限损姜，沒有權(quán)限會返回錯誤饰剥。

mysql> select * from T where ID=10;
ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T
mysql>

如果命中了查詢緩存，不走執(zhí)行器摧阅，也會在查詢緩存返回結(jié)果的時候做權(quán)限驗(yàn)證汰蓉。

如果有權(quán)限，就繼續(xù)執(zhí)行逸尖，以上述查詢語句為例古沥，執(zhí)行流程如下：

調(diào)用 InnoDB 引擎接口取這個表的第一行，判斷 ID 值是不是 10娇跟，如果不是則跳過岩齿，如果是則將這行存在結(jié)果集中；
調(diào)用引擎接口取“下一行”苞俘，重復(fù)相同的判斷邏輯盹沈，直到取到這個表的最后一行。
執(zhí)行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結(jié)果集返回給客戶端吃谣。

三乞封、更新語句的執(zhí)行過程

給出一個表的建表語句：

mysql> create table T(ID int primary key, c int);

更新語句也有查詢語句的那些流程，以update T set c=c+1 where ID=2;為例岗憋，首先連接到連接器肃晚，然后將表T上的緩存全部清空，分析器分析以后知道這是一個更新語句仔戈，優(yōu)化器決定使用 ID 這個索引关串。執(zhí)行器負(fù)責(zé)執(zhí)行語句。

除了以上步驟监徘，更新語句還多了 2 個日志模塊晋修，分別是redo log（重做日志）和binlog（歸檔日志）。

3.1 redo log

更新的時候凰盔，如果每次都要去磁盤找到那條記錄墓卦，并且直接更新至磁盤，會產(chǎn)生很大的 IO 成本户敬，在 MySQL 中有 1 個 WAL 技術(shù)就是為了解決這個問題落剪，全稱叫做 Write-Ahead Logging，關(guān)鍵點(diǎn)在于先寫日志尿庐，再寫磁盤著榴。

具體來說，在更新數(shù)據(jù)庫的時候屁倔，InnoDB 引擎會先把記錄寫到 redo log 里面脑又，并更新內(nèi)存，這時候更新就算已經(jīng)完成了锐借，InnoDB 引擎會在適當(dāng)?shù)臅r候问麸，將記錄更新到磁盤，一般是在服務(wù)器負(fù)載較低的時候钞翔。

InnoDB 里面的 redo log 是固定大小的严卖，可以在/etc/my.cnf中進(jìn)行配置，一般是 1 組 4 個文件布轿，文件名是ib-logfile-0哮笆、ib-logfile-1来颤、ib-logfile-2、ib-logfile-3稠肘，會從 0 到 3 開始循環(huán)寫福铅，在 3 寫滿之后又會向 0 里面寫，因此要永遠(yuǎn)保證 redo log 中有剩余空間可以記錄信息项阴，如果已經(jīng)寫滿了滑黔，就會停下來先刷一部分?jǐn)?shù)據(jù)到磁盤，空間騰出來以后环揽，繼續(xù)記錄略荡。

在這里插入圖片描述

write pos 是當(dāng)前記錄的位置，checkpoint 是當(dāng)前要擦除的位置歉胶。write pos 和 checkpoint 之間的是“粉板”上還空著的部分汛兜，可以用來記錄新的操作。

有了 redo log通今，InnoDB 就可以保證即使數(shù)據(jù)庫發(fā)生異常重啟序无，之前提交的記錄都不會丟失，這個能力稱為 crash-safe衡创。

3.2 binlog

redo log 是 InnoDB 獨(dú)有的功能帝嗡，在 MySQL 還沒有引入 InnoDB 的時候，也有一個日志璃氢，就是 binlog 日志哟玷，主要功能是歸檔，以及主從復(fù)制使用一也。crash-safe 和 WAL 也都是 InnoDB 特有的巢寡。

binlog 是 Server 層的日志，并不是引擎層椰苟，因此所以的引擎都可以使用 binlog 日志抑月。
redo log 是物理日志，記錄的是“在某個數(shù)據(jù)頁上做了什么修改”舆蝴；binlog 是邏輯日志谦絮，記錄的是這個語句的原始邏輯
redo log 是循環(huán)寫的，空間固定會用完洁仗；binlog 是可以追加寫入的层皱，不會覆蓋舊的文件。

在這里插入圖片描述

回到更新語句中赠潦，InnoDB 將數(shù)據(jù)寫入 redo log 后還沒結(jié)束叫胖，此時 redo log 處于 prepare 狀態(tài)；

然后 InnoDB 告知執(zhí)行器執(zhí)行完成了她奥，隨時可以提交事務(wù)瓮增，執(zhí)行器生成這個操作的 binlog怎棱，并把 binlog 寫入磁盤。

執(zhí)行器調(diào)用引擎的提交事務(wù)接口绷跑，引擎把剛剛寫入的 redo log 改成提交（commit）狀態(tài)拳恋，更新完成。

這個就是 MySQL 中的兩階段提交你踩。

在這里插入圖片描述

感謝閱讀诅岩，有興趣的小伙伴可以關(guān)注我的公眾號DevOps探索之旅讳苦，大家一起學(xué)習(xí)進(jìn)步