一條SQL語(yǔ)句在MySQL中執(zhí)行的過(guò)程詳解

本篇內(nèi)容介紹了“一條SQL語(yǔ)句在MySQL中執(zhí)行的過(guò)程詳解”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

目錄

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

• 1.1 連接器

• 1.2 查詢(xún)緩存

• 1.3 分析器

• 1.4 優(yōu)化器

• 1.5 執(zhí)行器

• 二、語(yǔ)句分析

• 2.1 查詢(xún)語(yǔ)句
  

• 2.2 更新語(yǔ)句
  

• 三、總結(jié)

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

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

mysql主要分為Server層和存儲(chǔ)引擎層

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

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

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

1.1 連接器

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

1.2 查詢(xún)緩存

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

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

1.3 分析器

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

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

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

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

1.4 優(yōu)化器

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

1.5 執(zhí)行器

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

二、語(yǔ)句分析

2.1 查詢(xún)語(yǔ)句

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

select * from tb_student  A where A.age='18' and A.name='張三';

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

• 先檢查該語(yǔ)句是否有權(quán)限，如果沒(méi)有權(quán)限，直接返回錯(cuò)誤信息，如果有權(quán)限，在mysql8.0版本以前，會(huì)先查詢(xún)緩存，以這條sql語(yǔ)句為key在內(nèi)存中查詢(xún)是否有結(jié)果，如果有直接緩存，如果沒(méi)有，執(zhí)行下一步。

• 通過(guò)分析器進(jìn)行詞法分析，提取sql語(yǔ)句的關(guān)鍵元素，比如提取上面這個(gè)語(yǔ)句是查詢(xún)select，提取需要查詢(xún)的表名為tb_student,需要查詢(xún)所有的列，查詢(xún)條件是這個(gè)表的id='1'。然后判斷這個(gè)sql語(yǔ)句是否有語(yǔ)法錯(cuò)誤，比如關(guān)鍵詞是否正確等等，如果檢查沒(méi)問(wèn)題就執(zhí)行下一步。

• 接下來(lái)就是優(yōu)化器進(jìn)行確定執(zhí)行方案，上面的sql語(yǔ)句，可以有兩種執(zhí)行方案：（1）.先查詢(xún)學(xué)生表中姓名為“張三”的學(xué)生，然后判斷是否年齡是18。（2）.先找出學(xué)生中年齡18歲的學(xué)生，然后再查詢(xún)姓名為“張三”的學(xué)生。

• 那么優(yōu)化器根據(jù)自己的優(yōu)化算法進(jìn)行選擇執(zhí)行效率最好的一個(gè)方案（優(yōu)化器認(rèn)為，有時(shí)候不一定最好）。那么確認(rèn)了執(zhí)行計(jì)劃后就準(zhǔn)備開(kāi)始執(zhí)行了。

• 進(jìn)行權(quán)限校驗(yàn)，如果沒(méi)有權(quán)限就會(huì)返回錯(cuò)誤信息，如果有權(quán)限就會(huì)調(diào)用數(shù)據(jù)庫(kù)引擎接口，返回引擎的執(zhí)行結(jié)果。
  

2.2 更新語(yǔ)句

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

update tb_student A set A.age='19' where A.name='張三';

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

• 先查詢(xún)到張三這一條數(shù)據(jù)，如果有緩存，也是會(huì)用到緩存。

• 然后拿到查詢(xún)的語(yǔ)句，把 age 改為19，然后調(diào)用引擎API接口，寫(xiě)入這一行數(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é)會(huì)問(wèn)，為什么要用兩個(gè)日志模塊，用一個(gè)日志模塊不行嗎？這就是之前mysql的模式了，MyISAM引擎是沒(méi)有redo log的，那么我們知道它是不支持事務(wù)的，所以并不是說(shuō)只用一個(gè)日志模塊不可以，只是InnoDB引擎就是通過(guò)redo log來(lái)支持事務(wù)的。那么，又會(huì)有同學(xué)問(wèn)，我用兩個(gè)日志模塊，但是不要這么復(fù)雜行不行，為什么redo log 要引入prepare預(yù)提交狀態(tài)？這里我們用反證法來(lái)說(shuō)明下為什么要這么做？

• 先寫(xiě)redo log 直接提交，然后寫(xiě) binlog，假設(shè)寫(xiě)完redo log 后，機(jī)器掛了，binlog日志沒(méi)有被寫(xiě)入，那么機(jī)器重啟后，這臺(tái)機(jī)器會(huì)通過(guò)redo log恢復(fù)數(shù)據(jù)，但是這個(gè)時(shí)候bingog并沒(méi)有記錄該數(shù)據(jù)，后續(xù)進(jìn)行機(jī)器備份的時(shí)候，就會(huì)丟失這一條數(shù)據(jù)，同時(shí)主從同步也會(huì)丟失這一條數(shù)據(jù)。

• 先寫(xiě)binlog，然后寫(xiě)redo log，假設(shè)寫(xiě)完了binlog，機(jī)器異常重啟了，由于沒(méi)有redo log，本機(jī)是無(wú)法恢復(fù)這一條記錄的，但是binlog又有記錄，那么和上面同樣的道理，就會(huì)產(chǎn)生數(shù)據(jù)不一致的情況。
  

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

• 判斷redo log 是否完整，如果判斷是完整的，就立即提交。

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

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

三、總結(jié)

• Mysql 主要分為Server曾和引擎層，Server層主要包括連接器、查詢(xún)緩存、分析器、優(yōu)化器、執(zhí)行器，同時(shí)還有一個(gè)日志模塊（binlog），這個(gè)日志模塊所有執(zhí)行引擎都可以共用。

• 引擎層是插件式的，目前主要包括，MyISAM,InnoDB,Memory等。

• sql等執(zhí)行過(guò)程分為兩類(lèi)，一類(lèi)對(duì)于查詢(xún)等過(guò)程如下：權(quán)限校驗(yàn)---》查詢(xún)緩存---》分析器---》優(yōu)化器---》權(quán)限校驗(yàn)---》執(zhí)行器---》引擎

• 對(duì)于更新等語(yǔ)句執(zhí)行流程如下：分析器----》權(quán)限校驗(yàn)----》執(zhí)行器---》引擎---redo log prepare---》binlog---》redo log commit

“一條SQL語(yǔ)句在MySQL中執(zhí)行的過(guò)程詳解”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！