一、mysql架構(gòu)分析

下面是mysql的一個(gè)簡要架構(gòu)圖：

mysql主要分為Server層和存儲引擎層

Server層：主要包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等，所有跨存儲引擎的功能都在這一層實(shí)現(xiàn)，比如存儲過程、觸發(fā)器、視圖，函數(shù)等，還有一個(gè)通用的日志模塊 binglog日志模塊。

存儲引擎： 主要負(fù)責(zé)數(shù)據(jù)的存儲和讀取，采用可以替換的插件式架構(gòu)，支持InnoDB、MyISAM、Memory等多個(gè)存儲引擎，其中InnoDB引擎有自有的日志模塊redolog 模塊。

InnoDB 5.5.5版本作為默認(rèn)引擎。

1.1 連接器

主要負(fù)責(zé)用戶登錄數(shù)據(jù)庫，進(jìn)行用戶的身份認(rèn)證，包括校驗(yàn)賬戶密碼，權(quán)限等操作，如果用戶賬戶密碼已通過，連接器會到權(quán)限表中查詢該用戶的所有權(quán)限，之后在這個(gè)連接里的權(quán)限邏輯判斷都是會依賴此時(shí)讀取到的權(quán)限數(shù)據(jù)，也就是說，后續(xù)只要這個(gè)連接不斷開，即時(shí)管理員修改了該用戶的權(quán)限，該用戶也是不受影響的。

1.2 查詢緩存

連接建立后，執(zhí)行查詢語句的時(shí)候，會先查詢緩存，Mysql會先校驗(yàn)這個(gè)sql是否執(zhí)行過，以Key-Value的形式緩存在內(nèi)存中，Key是查詢預(yù)計(jì)，Value是結(jié)果集。如果緩存key被命中，就會直接返回給客戶端，如果沒有命中，就會執(zhí)行后續(xù)的操作，完成后也會把結(jié)果緩存起來，方便下一次調(diào)用。當(dāng)然在真正執(zhí)行緩存查詢的時(shí)候還是會校驗(yàn)用戶的權(quán)限，是否有該表的查詢條件。

Mysql 查詢不建議使用緩存，因?yàn)閷τ诮?jīng)常更新的數(shù)據(jù)來說，緩存的有效時(shí)間太短了，往往帶來的效果并不好，對于不經(jīng)常更新的數(shù)據(jù)來說，使用緩存還是可以的，Mysql 8.0 版本后刪除了緩存的功能，官方也是認(rèn)為該功能在實(shí)際的應(yīng)用場景比較少，所以干脆直接刪掉了。

1.3 分析器

mysql 沒有命中緩存，那么就會進(jìn)入分析器，分析器主要是用來分析SQL語句是來干嘛的，分析器也會分為幾步：

第一步，詞法分析，一條SQL語句有多個(gè)字符串組成，首先要提取關(guān)鍵字，比如select，提出查詢的表，提出字段名，提出查詢條件等等。做完這些操作后，就會進(jìn)入第二步。

第二步，語法分析，主要就是判斷你輸入的sql是否正確，是否符合mysql的語法。

完成這2步之后，mysql就準(zhǔn)備開始執(zhí)行了，但是如何執(zhí)行，怎么執(zhí)行是最好的結(jié)果呢？這個(gè)時(shí)候就需要優(yōu)化器上場了。

1.4 優(yōu)化器

優(yōu)化器的作用就是它認(rèn)為的最優(yōu)的執(zhí)行方案去執(zhí)行（雖然有時(shí)候也不是最優(yōu)），比如多個(gè)索引的時(shí)候該如何選擇索引，多表查詢的時(shí)候如何選擇關(guān)聯(lián)順序等。

1.5 執(zhí)行器

當(dāng)選擇了執(zhí)行方案后，mysql就準(zhǔn)備開始執(zhí)行了，首先執(zhí)行前會校驗(yàn)該用戶有沒有權(quán)限，如果沒有權(quán)限，就會返回錯(cuò)誤信息，如果有權(quán)限，就會去調(diào)用引擎的接口，返回接口執(zhí)行的結(jié)果。

二、語句分析

2.1 查詢語句

說了以上這么多，那么究竟一條sql語句是如何執(zhí)行的呢？其實(shí)我們的sql可以分為2中，一種是查詢，一種是更新（增加，更新，刪除）。我們先分析下查詢語句，語句如下：

結(jié)合上面的說明，我們分析下這個(gè)語句的執(zhí)行流程：

• 先檢查該語句是否有權(quán)限，如果沒有權(quán)限，直接返回錯(cuò)誤信息，如果有權(quán)限，在mysql8.0版本以前，會先查詢緩存，以這條sql語句為key在內(nèi)存中查詢是否有結(jié)果，如果有直接緩存，如果沒有，執(zhí)行下一步。
• 通過分析器進(jìn)行詞法分析，提取sql語句的關(guān)鍵元素，比如提取上面這個(gè)語句是查詢select，提取需要查詢的表名為tb_student,需要查詢所有的列，查詢條件是這個(gè)表的id='1'。然后判斷這個(gè)sql語句是否有語法錯(cuò)誤，比如關(guān)鍵詞是否正確等等，如果檢查沒問題就執(zhí)行下一步。
• 接下來就是優(yōu)化器進(jìn)行確定執(zhí)行方案，上面的sql語句，可以有兩種執(zhí)行方案：（1）.先查詢學(xué)生表中姓名為“張三”的學(xué)生，然后判斷是否年齡是18。（2）.先找出學(xué)生中年齡18歲的學(xué)生，然后再查詢姓名為“張三”的學(xué)生。
• 那么優(yōu)化器根據(jù)自己的優(yōu)化算法進(jìn)行選擇執(zhí)行效率最好的一個(gè)方案（優(yōu)化器認(rèn)為，有時(shí)候不一定最好）。那么確認(rèn)了執(zhí)行計(jì)劃后就準(zhǔn)備開始執(zhí)行了。
• 進(jìn)行權(quán)限校驗(yàn)，如果沒有權(quán)限就會返回錯(cuò)誤信息，如果有權(quán)限就會調(diào)用數(shù)據(jù)庫引擎接口，返回引擎的執(zhí)行結(jié)果。

2.2 更新語句

以上就是一條查詢sql的執(zhí)行流程，那么接下來我們看看一條更新語句如何執(zhí)行的呢？sql語句如下：

我們來給張三修改下年齡，在實(shí)際數(shù)據(jù)庫肯定不會設(shè)置年齡這個(gè)字段的，不然要被技術(shù)負(fù)責(zé)人打的。其實(shí)條語句也基本上會沿著上一個(gè)查詢的流程走，只不過執(zhí)行更新的時(shí)候肯定要記錄日志啦，這就會引入日志模塊了，mysql 自帶的日志模塊式binlog（歸檔日志），所有的存儲引擎都可以使用，我們常用的InnoDB引擎還自帶了一個(gè)日志模塊redo log，我們就以InnoDB模式下來探討這個(gè)語句的執(zhí)行流程。流程如下：

• 先查詢到張三這一條數(shù)據(jù)，如果有緩存，也是會用到緩存。
• 然后拿到查詢的語句，把 age 改為19，然后調(diào)用引擎API接口，寫入這一行數(shù)據(jù)，InnoDB引擎把數(shù)據(jù)保存在內(nèi)存中，同時(shí)記錄redo log，此時(shí)redo log進(jìn)入prepare狀態(tài)，然后告訴執(zhí)行器，執(zhí)行完成了，隨時(shí)可以提交。
• 執(zhí)行器收到通知后記錄binlog，然后調(diào)用引擎接口，提交redo log 為提交狀態(tài)。
• 更新完成。

這里肯定有同學(xué)會問，為什么要用兩個(gè)日志模塊，用一個(gè)日志模塊不行嗎？這就是之前mysql的模式了，MyISAM引擎是沒有redo log的，那么我們知道它是不支持事務(wù)的，所以并不是說只用一個(gè)日志模塊不可以，只是InnoDB引擎就是通過redo log來支持事務(wù)的。那么，又會有同學(xué)問，我用兩個(gè)日志模塊，但是不要這么復(fù)雜行不行，為什么redo log 要引入prepare預(yù)提交狀態(tài)？這里我們用反證法來說明下為什么要這么做？

• 先寫redo log 直接提交，然后寫 binlog，假設(shè)寫完redo log 后，機(jī)器掛了，binlog日志沒有被寫入，那么機(jī)器重啟后，這臺機(jī)器會通過redo log恢復(fù)數(shù)據(jù)，但是這個(gè)時(shí)候bingog并沒有記錄該數(shù)據(jù)，后續(xù)進(jìn)行機(jī)器備份的時(shí)候，就會丟失這一條數(shù)據(jù)，同時(shí)主從同步也會丟失這一條數(shù)據(jù)。
• 先寫binlog，然后寫redo log，假設(shè)寫完了binlog，機(jī)器異常重啟了，由于沒有redo log，本機(jī)是無法恢復(fù)這一條記錄的，但是binlog又有記錄，那么和上面同樣的道理，就會產(chǎn)生數(shù)據(jù)不一致的情況。

如果采用redo log 兩階段提交的方式就不一樣了，寫完binglog后，然后再提交redo log就會防止出現(xiàn)上述的問題，從而保證了數(shù)據(jù)的一致性。那么問題來了，有沒有一個(gè)極端的情況呢？假設(shè)redo log 處于預(yù)提交狀態(tài)，binglog也已經(jīng)寫完了，這個(gè)時(shí)候發(fā)生了異常重啟會怎么樣呢？ 這個(gè)就要依賴于mysql的處理機(jī)制了，mysql的處理過程如下：

• 判斷redo log 是否完整，如果判斷是完整的，就立即提交。
• 如果redo log 只是預(yù)提交但不是commit狀態(tài)，這個(gè)時(shí)候就會去判斷binlog是否完整，如果完整就提交 redo log, 不完整就回滾事務(wù)。

這樣就解決了數(shù)據(jù)一致性的問題。

三、總結(jié)

• Mysql 主要分為Server曾和引擎層，Server層主要包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器，同時(shí)還有一個(gè)日志模塊（binlog），這個(gè)日志模塊所有執(zhí)行引擎都可以共用。
• 引擎層是插件式的，目前主要包括，MyISAM,InnoDB,Memory等。
• sql等執(zhí)行過程分為兩類，一類對于查詢等過程如下：權(quán)限校驗(yàn)---》查詢緩存---》分析器---》優(yōu)化器---》權(quán)限校驗(yàn)---》執(zhí)行器---》引擎
• 對于更新等語句執(zhí)行流程如下：分析器----》權(quán)限校驗(yàn)----》執(zhí)行器---》引擎---redo log prepare---》binlog---》redo log commit

到此這篇關(guān)于一條SQL語句在MySQL中是如何執(zhí)行的的文章就介紹到這了,更多相關(guān)SQL語句在MySQL中是如何執(zhí)行的內(nèi)容請搜索服務(wù)器之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持服務(wù)器之家！

原文鏈接：https://zhuanlan.zhihu.com/p/61757521