最近有粉絲面試互聯(lián)網(wǎng)公司被問(wèn)到：你知道select語(yǔ)句和update語(yǔ)句分別是怎么執(zhí)行的嗎？，要我寫(xiě)一篇這兩者執(zhí)行SQL語(yǔ)句的區(qū)別，這不就來(lái)了。

總的來(lái)說(shuō)，select和update執(zhí)行的邏輯大體一樣，但是具體的實(shí)現(xiàn)還是有區(qū)別的。

當(dāng)然深入了解select和update的具體區(qū)別并不是只為了面試，當(dāng)希望Mysql能夠高效的執(zhí)行的時(shí)候，最好的辦法就是清楚的了解Mysql是如何執(zhí)行查詢(xún)的，只有更加全面的了解SQL執(zhí)行的每一個(gè)過(guò)程，才能更好的進(jìn)行SQl的優(yōu)化。

select語(yǔ)句

當(dāng)執(zhí)行一條查詢(xún)的SQl的時(shí)候大概發(fā)生了以下的步驟：

客戶(hù)端發(fā)送查詢(xún)語(yǔ)句給服務(wù)器。

服務(wù)器首先進(jìn)行用戶(hù)名和密碼的驗(yàn)證以及權(quán)限的校驗(yàn)。

然后會(huì)檢查緩存中是否存在該查詢(xún)，若存在，返回緩存中存在的結(jié)果。若是不存在就進(jìn)行下一步。

接著進(jìn)行語(yǔ)法和詞法的分析，對(duì)SQl的解析、語(yǔ)法檢測(cè)和預(yù)處理，再由優(yōu)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃。

Mysql的執(zhí)行器根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃執(zhí)行，調(diào)用存儲(chǔ)引擎的接口進(jìn)行查詢(xún)。

服務(wù)器將查詢(xún)的結(jié)果返回客戶(hù)端。

執(zhí)行的流程

Mysql中語(yǔ)句的執(zhí)行都是都是分層執(zhí)行，每一層執(zhí)行的任務(wù)都不同，直到最后拿到結(jié)果返回，主要分為Service層和引擎層，在Service層中包含：連接器、分析器、優(yōu)化器、執(zhí)行器。引擎層以插件的形式可以兼容各種不同的存儲(chǔ)引擎。

Mysql的執(zhí)行的流程圖如下圖所示：

這里以一個(gè)實(shí)例進(jìn)行說(shuō)明Mysql的的執(zhí)行過(guò)程，新建一個(gè)User表，如下：

//新建一個(gè)表 DROPTABLEIFEXISTSUser; CREATETABLE`User`( `id`int(11)NOTNULLAUTO_INCREMENT, `name`varchar(10)DEFAULTNULL, `age`intDEFAULT0, `address`varchar(255)DEFAULTNULL, `phone`varchar(255)DEFAULTNULL, `dept`int, PRIMARYKEY(`id`) )ENGINE=InnoDBAUTO_INCREMENT=40DEFAULTCHARSET=utf8; //并初始化數(shù)據(jù)，如下 INSERTINTOUser(name,age,address,phone,dept)VALUES('張三',24,'北京','13265543552',2); INSERTINTOUser(name,age,address,phone,dept)VALUES('張三三',20,'北京','13265543557',2); INSERTINTOUser(name,age,address,phone,dept)VALUES('李四',23,'上海','13265543553',2); INSERTINTOUser(name,age,address,phone,dept)VALUES('李四四',21,'上海','13265543556',2); INSERTINTOUser(name,age,address,phone,dept)VALUES('王五',27,'廣州','13265543558',3); INSERTINTOUser(name,age,address,phone,dept)VALUES('王五五',26,'廣州','13265543559',3); INSERTINTOUser(name,age,address,phone,dept)VALUES('趙六',25,'深圳','13265543550',3); INSERTINTOUser(name,age,address,phone,dept)VALUES('趙六六',28,'廣州','13265543561',3); INSERTINTOUser(name,age,address,phone,dept)VALUES('七七',29,'廣州','13265543562',4); INSERTINTOUser(name,age,address,phone,dept)VALUES('八八',23,'廣州','13265543563',4); INSERTINTOUser(name,age,address,phone,dept)VALUES('九九',24,'廣州','13265543564',4);

現(xiàn)在針對(duì)這個(gè)表發(fā)出一條SQl查詢(xún)：查詢(xún)每個(gè)部門(mén)中25歲以下的員工個(gè)數(shù)大于3的員工個(gè)數(shù)和部門(mén)編號(hào)，并按照人工個(gè)數(shù)降序排序和部門(mén)編號(hào)升序排序的前兩個(gè)部門(mén)。

SELECTdept,COUNT(phone)ASnumFROMUserWHEREage=3ORDERBYnumDESC,deptASCLIMIT0,2;

連接器

開(kāi)始執(zhí)行這條sql時(shí)，首先會(huì)校驗(yàn)?zāi)愕挠脩?hù)名和密碼是否正確，若是不正確會(huì)返回錯(cuò)誤信息："Access denied for user"；

若是用戶(hù)名和密碼校驗(yàn)通過(guò)，然后就會(huì)到權(quán)限表獲取當(dāng)前用戶(hù)擁有的權(quán)限，會(huì)檢查該語(yǔ)句是否有權(quán)限，若是沒(méi)有權(quán)限就直接返回錯(cuò)誤信息，有權(quán)限會(huì)進(jìn)行下一步，校驗(yàn)權(quán)限的這一步是在圖一的連接器進(jìn)行的，對(duì)連接用戶(hù)權(quán)限的校驗(yàn)。

注意：后續(xù)的一些列操作都是依賴(lài)于這個(gè)權(quán)限的范圍內(nèi)的。

檢索緩存

當(dāng)建立連接，履行查詢(xún)語(yǔ)句的時(shí)候，會(huì)先行檢查在緩存區(qū)域看看這個(gè)sql與否履行過(guò)，若是之前執(zhí)行過(guò)，它的執(zhí)行結(jié)果會(huì)以Key-Value的形式緩存于內(nèi)存中，Key是執(zhí)行的sql，Value是結(jié)果集。

假如，緩存中key遭擊中，便會(huì)直接將結(jié)果返回給客戶(hù)端，假如沒(méi)命中，便會(huì)履行后續(xù)的操作，完工之后亦會(huì)將結(jié)果緩存起來(lái)以便再次查詢(xún)獲取，當(dāng)下一次進(jìn)行查詢(xún)的時(shí)候也是如此的循環(huán)操作。

注意：Mysql中的緩存比較適合于那些靜態(tài)的表，更新不頻繁的表，因?yàn)橹灰?dāng)前表有數(shù)據(jù)更新，有關(guān)于該表的緩存就會(huì)失效，若是表更新頻繁緩存頻繁的失效，這樣維護(hù)緩存的消耗的性能遠(yuǎn)大于使用緩存帶來(lái)的性能優(yōu)化，這樣就會(huì)得不償失，嚴(yán)重影響Mysql的性能，所以在Mysql 8版本中的時(shí)候把緩存這一塊給砍掉了。

在個(gè)人的觀(guān)點(diǎn)中對(duì)于緩存這一塊的看法是，沒(méi)必要砍掉，可以設(shè)置成默認(rèn)關(guān)閉緩存，需要的時(shí)候再設(shè)置開(kāi)啟，并且可以通過(guò)配置參數(shù)指定特定的表使用緩存，那些表不使用緩存，這樣或許使用緩存更有效。

分析器

分析器主要有兩步：（1）詞法分析（2）語(yǔ)法分析

詞法分析主要執(zhí)行提煉關(guān)鍵性字，比如select，提交檢索的表，提交字段名，提交檢索條件，確定該語(yǔ)句是select還是update或者是delete語(yǔ)句。

語(yǔ)法分析主要執(zhí)行辨別你輸出的sql與否準(zhǔn)確，是否合乎mysql的語(yǔ)法，若是不符合sql語(yǔ)法就會(huì)拋出：You have an error in your SQL syntax。

優(yōu)化器

查詢(xún)優(yōu)化器會(huì)將解析樹(shù)轉(zhuǎn)化成執(zhí)行計(jì)劃。一條查詢(xún)可以有多種執(zhí)行方法，最后都是返回相同結(jié)果。優(yōu)化器的作用就是找到這其中最好的執(zhí)行計(jì)劃。

例如：在查詢(xún)語(yǔ)句中有多個(gè)索引的時(shí)候，優(yōu)化器決定使用哪一個(gè)索引，或者有多表關(guān)聯(lián)的時(shí)候，決定表的連接順序等這些操作都是在優(yōu)化器決定的。

生成執(zhí)行計(jì)劃的過(guò)程會(huì)消耗較多的時(shí)間，特別是存在許多可選的執(zhí)行計(jì)劃時(shí)。如果在一條SQL語(yǔ)句執(zhí)行的過(guò)程中將該語(yǔ)句對(duì)應(yīng)的最終執(zhí)行計(jì)劃進(jìn)行緩存。

當(dāng)相似的語(yǔ)句再次被輸入服務(wù)器時(shí)，就可以直接使用已緩存的執(zhí)行計(jì)劃，從而跳過(guò)SQL語(yǔ)句生成執(zhí)行計(jì)劃的整個(gè)過(guò)程，進(jìn)而可以提高語(yǔ)句的執(zhí)行速度。

MySQL使用基于成本的查詢(xún)優(yōu)化器。它會(huì)嘗試預(yù)測(cè)一個(gè)查詢(xún)使用某種執(zhí)行計(jì)劃時(shí)的成本，并選擇其中成本最少的一個(gè)。

執(zhí)行器

優(yōu)化器生成得執(zhí)行計(jì)劃，交由執(zhí)行器進(jìn)行執(zhí)行，執(zhí)行器調(diào)用存儲(chǔ)引擎得讀接口，執(zhí)行器中循環(huán)的調(diào)用存儲(chǔ)引擎的讀接口，以此換取滿(mǎn)足條件的數(shù)據(jù)行，并把它放在一個(gè)結(jié)果集中，遍歷并獲取了所有滿(mǎn)足條件的數(shù)據(jù)行，最后將結(jié)果集返回，結(jié)束整個(gè)查詢(xún)得過(guò)程。

update語(yǔ)句

上面我們說(shuō)完了select語(yǔ)句，select語(yǔ)句的執(zhí)行過(guò)程會(huì)經(jīng)過(guò)連接器、分析器、優(yōu)化器、執(zhí)行器、存儲(chǔ)引擎，同樣的update語(yǔ)句也會(huì)同樣走一遍select語(yǔ)句的執(zhí)行過(guò)程。

但是和select最大不同的是，update語(yǔ)句會(huì)涉及到兩個(gè)日志的操作redo log（重做日志）和binlog（歸檔日志）。對(duì)于這兩個(gè)日志的詳細(xì)介紹，我之前寫(xiě)過(guò)一篇文章進(jìn)行介紹，有興趣的可以看一看[]：

那么Mysql中又是怎么使用redo log和binlog？為什么要使用redo log和binlog呢？直接執(zhí)行更新然后存庫(kù)不就行了嗎？還要放在redo log和binlog中，這不是多此一舉嗎？且聽(tīng)我慢慢道來(lái)，這里面大有文章。

redo log

大家都是知道Mysql是關(guān)系型數(shù)據(jù)庫(kù)，用來(lái)存儲(chǔ)數(shù)據(jù)的，在訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)量大的時(shí)候，Mysql讀寫(xiě)磁盤(pán)訪(fǎng)問(wèn)的效率是非常低的，加上sql中的條件對(duì)數(shù)據(jù)的篩選過(guò)濾，那么效率就更低了。

這也是為什么引入非關(guān)系型數(shù)據(jù)庫(kù)作為作為數(shù)據(jù)緩存原因，例如：Redis、MongoDB等，就是為了減少sql執(zhí)行期間的數(shù)據(jù)庫(kù)io操作。

同樣的道理，若是每次執(zhí)行update語(yǔ)句都要進(jìn)行磁盤(pán)的io操作、以及數(shù)據(jù)的過(guò)濾篩選，小量的訪(fǎng)問(wèn)和數(shù)據(jù)量數(shù)據(jù)庫(kù)還可以撐住，那么訪(fǎng)問(wèn)量一大以及數(shù)據(jù)量一大，這樣數(shù)據(jù)庫(kù)肯定頂不住。

基于上面的問(wèn)題于是出現(xiàn)了redo log日志，redo log日志也叫做WAL技術(shù)（Write- Ahead Logging），他是一種先寫(xiě)日志，并更新內(nèi)存，最后再更新磁盤(pán)的技術(shù)，并且更新磁盤(pán)往往是在Mysql比較閑的時(shí)候，這樣就大大減輕了Mysql的壓力。

redo log的特點(diǎn)就是：redo log是固定大小，是物理日志，屬于InnoDB引擎的，并且寫(xiě)redo log是環(huán)狀寫(xiě)日志的形式：

如上圖所示：若是四組的redo log文件，一組為1G的大小，那么四組就是4G的大小，其中write pos是記錄當(dāng)前的位置，有數(shù)據(jù)寫(xiě)入當(dāng)前位置，那么write pos就會(huì)邊寫(xiě)入邊往后移。

而check point是擦除的位置，因?yàn)閞edo log是固定大小，所以當(dāng)redo log滿(mǎn)的時(shí)候，也就是write pos追上check point的時(shí)候，需要清除redo log的部分?jǐn)?shù)據(jù)，清除的數(shù)據(jù)會(huì)被持久化到磁盤(pán)中，然后將check point向前移動(dòng)。

redo log日志實(shí)現(xiàn)了即使在數(shù)據(jù)庫(kù)出現(xiàn)異常宕機(jī)的時(shí)候，重啟后之前的記錄也不會(huì)丟失，這就是crash-safe能力。

binlog

binlog稱(chēng)為歸檔日志，是邏輯上的日志，它屬于Mysql的Server層面的日志，記錄著sql的原始邏輯，主要有兩種模式，一個(gè)是statement格式記錄的是原始的sql，而row格式則是記錄行內(nèi)容。

那么這樣看來(lái)redo log和binlog雖然記錄的形式、內(nèi)容不同，但是這兩者日志都能通過(guò)自己記錄的內(nèi)容恢復(fù)數(shù)據(jù)，那么為什么還要這兩個(gè)日志同時(shí)存在呢？只要其中一個(gè)不就行了嘛，兩個(gè)同時(shí)存在不就多此一舉了嘛。且聽(tīng)我慢慢道來(lái)，這里面也大有文章。

因?yàn)閯傞_(kāi)Mysql自帶的引擎MyISAM就沒(méi)有crash-safe功能的，并且在此之前Mysql還沒(méi)有InnoDB引擎，Mysql自帶的binlog日志只是用來(lái)歸檔日志的，所以InnoDB引擎也就通過(guò)自己redo log日志來(lái)實(shí)現(xiàn)crash-safe功能。

update執(zhí)行過(guò)程

上面說(shuō)了那么久兩種日志的作用和特點(diǎn)，那么這兩種日志究竟和update執(zhí)行語(yǔ)句有什么關(guān)系呢？

先來(lái)看圖：

前提：當(dāng)前的引擎是使用InnoDB，update語(yǔ)句與select語(yǔ)句區(qū)別主要是這兩日志的使用主要是在執(zhí)行器和引擎之間進(jìn)行交互時(shí)體現(xiàn)的區(qū)別。假如執(zhí)行如下一條簡(jiǎn)單的更新語(yǔ)句是：

updateusersetage=age+1whereid =2；

上面說(shuō)過(guò)select語(yǔ)句走過(guò)的流程update語(yǔ)句也會(huì)走一遍，當(dāng)來(lái)到執(zhí)行器的時(shí)候：

執(zhí)行器會(huì)調(diào)用引擎的讀接口，然后找到id=2的數(shù)據(jù)行，因?yàn)閕d是主鍵索引，索引按照樹(shù)的搜索找到這一行，若是數(shù)據(jù)行已經(jīng)存在于內(nèi)存的數(shù)據(jù)頁(yè)中就會(huì)立即將結(jié)果返回，若是不在內(nèi)存中，就會(huì)從磁盤(pán)中進(jìn)行加載到內(nèi)存中，然后將查詢(xún)的結(jié)果返回。

然后，執(zhí)行器將返回的結(jié)果的age字段+1，并調(diào)用引擎的寫(xiě)接口寫(xiě)入更新后的數(shù)據(jù)行。

引擎獲取到更新后的數(shù)據(jù)行更新到內(nèi)存和redo log中，并告訴執(zhí)行器可以隨時(shí)提交事務(wù)，此時(shí)的redo log處于prepare階段。

執(zhí)行器收到引擎的告知后，生成binlog日志，并且調(diào)用引擎的接口提交事務(wù)，引擎將redo log的狀態(tài)修改為commit狀態(tài)，這樣這個(gè)更新操作算是完成。

與select語(yǔ)句相比，因?yàn)閟elect沒(méi)有更新數(shù)據(jù)，只是將引擎查詢(xún)的數(shù)據(jù)返回給執(zhí)行器就算是完后，而update涉及數(shù)據(jù)的更新并且重新調(diào)用引擎接口寫(xiě)會(huì)存儲(chǔ)引擎中的交互過(guò)程。

兩階段提交

上面詳細(xì)的說(shuō)了update語(yǔ)句的執(zhí)行流程，提到了redo log的prepare和commit兩個(gè)階段，這就是兩階段提交，兩階段提交的目的是為了保證redo log日志與binlog日志保持?jǐn)?shù)據(jù)的一致性。

若是redo log寫(xiě)成功binlog寫(xiě)失敗，或者redo log寫(xiě)失敗binlog寫(xiě)成功，最后使用這兩者日志進(jìn)行數(shù)據(jù)恢復(fù)得到的結(jié)果數(shù)據(jù)都是不一致性的，所以為了保證兩個(gè)日志邏輯上的一致，使用兩階段進(jìn)行提交。

redo log與binlog的總結(jié)

最后來(lái)對(duì)比一下這兩種日志：redo是物理的，binlog是邏輯的，redo的大小固定，并且以環(huán)狀的形式寫(xiě)入數(shù)據(jù)，數(shù)據(jù)滿(mǎn)的時(shí)候需要將redo日志中擦除數(shù)據(jù)，并且將擦除的數(shù)據(jù)持久化到磁盤(pán)中。

而binlog以追加日志的形式寫(xiě)入，也就是當(dāng)日志寫(xiě)到一定大小后，就會(huì)切換到下一個(gè)，并不會(huì)覆蓋以前寫(xiě)的日志。

binlog是在Mysql的Server層中使用，因?yàn)閎inlog沒(méi)有crash-safe功能，所以InnoDB引擎自己實(shí)現(xiàn)了redo log日志的crash-safe的功能，為了保證這兩個(gè)日志邏輯上的一致使用兩階段提交。

在使用redo和binlog這兩種日志的時(shí)候，可以將參數(shù)innodb_flush_log_at_trx_commit和sync_binlog都設(shè)置為1，它表示每次事務(wù)提交的時(shí)候，都會(huì)將日志持久化到磁盤(pán)中。

責(zé)任編輯：xj

原文標(biāo)題：面試官：你知道 select 語(yǔ)句和 update 語(yǔ)句分別是怎么執(zhí)行的嗎？

文章出處：【微信公眾號(hào)：數(shù)據(jù)分析與開(kāi)發(fā)】歡迎添加關(guān)注！文章轉(zhuǎn)載請(qǐng)注明出處。