Mysql中sql的執(zhí)行流程分析

這篇文章將為大家詳細(xì)講解有關(guān)Mysql中sql的執(zhí)行流程分析，小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。

1. 流程

2. 核心架構(gòu)

簡(jiǎn)單來(lái)說(shuō) MySQL 主要分為 Server 層和存儲(chǔ)引擎層：

• Server 層：主要包括連接器、查詢緩存、分析器、優(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 模塊?，F(xiàn)在最常用的存儲(chǔ)引擎是 InnoDB，它從 MySQL 5.5.5 版本開始就被當(dāng)做默認(rèn)存儲(chǔ)引擎了。

 2.1 Server 層基本組件介紹

1.連接器

連接器主要和身份認(rèn)證和權(quán)限相關(guān)的功能相關(guān)，就好比一個(gè)級(jí)別很高的門衛(wèi)一樣。

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

2.查詢緩存(MySQL 8.0 版本后移除)

查詢緩存主要用來(lái)緩存我們所執(zhí)行的 SELECT 語(yǔ)句以及該語(yǔ)句的結(jié)果集。

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

MySQL 查詢不建議使用緩存，因?yàn)椴樵兙彺媸г趯?shí)際業(yè)務(wù)場(chǎng)景中可能會(huì)非常頻繁，假如你對(duì)一個(gè)表更新的話，這個(gè)表上的所有的查詢緩存都會(huì)被清空。對(duì)于不經(jīng)常更新的數(shù)據(jù)來(lái)說(shuō)，使用緩存還是可以的。

所以，一般在大多數(shù)情況下我們都是不推薦去使用查詢緩存的。

MySQL 8.0 版本后刪除了緩存的功能，官方也是認(rèn)為該功能在實(shí)際的應(yīng)用場(chǎng)景比較少，所以干脆直接刪掉了。

3.分析器

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

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

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

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

4.優(yōu)化器

優(yōu)化器的作用就是它認(rèn)為的最優(yōu)的執(zhí)行方案去執(zhí)行（有時(shí)候可能也不是最優(yōu)，這篇文章涉及對(duì)這部分知識(shí)的深入講解），比如多個(gè)索引的時(shí)候該如何選擇索引，多表查詢的時(shí)候如何選擇關(guān)聯(lián)順序等。

可以說(shuō)，經(jīng)過(guò)了優(yōu)化器之后可以說(shuō)這個(gè)語(yǔ)句具體該如何執(zhí)行就已經(jīng)定下來(lái)。

5.執(zhí)行器

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

3. 語(yǔ)句分析

3.1 查詢語(yǔ)句

sql 可以分為兩種，一種是查詢，一種是更新（增加，更新，刪除）。我們先分析下查詢語(yǔ)句，語(yǔ)句如下：

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

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

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

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

接下來(lái)就是優(yōu)化器進(jìn)行確定執(zhí)行方案，上面的 sql 語(yǔ)句，可以有兩種執(zhí)行方案：

a. 先查詢學(xué)生表中姓名為“張三”的學(xué)生，然后判斷是否年齡是 18。
b. 先找出學(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)限就會(huì)返回錯(cuò)誤信息，如果有權(quán)限就會(huì)調(diào)用數(shù)據(jù)庫(kù)引擎接口，返回引擎的執(zhí)行結(jié)果。

3.2 更新語(yǔ)句

以上就是一條查詢 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è)查詢的流程走，只不過(guò)執(zhí)行更新的時(shí)候肯定要記錄日志啦，這就會(huì)引入日志模塊了，MySQL 自帶的日志模塊式 binlog（歸檔日志） ，所有的存儲(chǔ)引擎都可以使用，我們常用的 InnoDB 引擎還自帶了一個(gè)日志模塊 redo log（重做日志），我們就以 InnoDB 模式下來(lái)探討這個(gè)語(yǔ)句的執(zhí)行流程。流程如下：

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

然后拿到查詢的語(yǔ)句，把 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é)會(huì)問(wèn)，為什么要用兩個(gè)日志模塊，用一個(gè)日志模塊不行嗎?

這是因?yàn)樽铋_始 MySQL 并沒與 InnoDB 引擎( InnoDB 引擎是其他公司以插件形式插入 MySQL 的) ，MySQL 自帶的引擎是 MyISAM，但是我們知道 redo log 是 InnoDB 引擎特有的，其他存儲(chǔ)引擎都沒有，這就導(dǎo)致會(huì)沒有 crash-safe 的能力(crash-safe 的能力即使數(shù)據(jù)庫(kù)發(fā)生異常重啟，之前提交的記錄都不會(huì)丟失)，binlog 日志只能用來(lái)歸檔。

并不是說(shuō)只用一個(gè)日志模塊不可以，只是 InnoDB 引擎就是通過(guò) redo log 來(lái)支持事務(wù)的。那么，又會(huì)有同學(xué)問(wèn)，我用兩個(gè)日志模塊，但是不要這么復(fù)雜行不行，為什么 redo log 要引入 prepare 預(yù)提交狀態(tài)？這里我們用反證法來(lái)說(shuō)明下為什么要這么做？

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

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

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

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

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

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

關(guān)于“Mysql中sql的執(zhí)行流程分析”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，請(qǐng)把它分享出去讓更多的人看到。