MySQL中執(zhí)行SQL語(yǔ)句方法介紹

本文主要給大家簡(jiǎn)單講講MySQL中執(zhí)行SQL語(yǔ)句方法，相關(guān)專業(yè)術(shù)語(yǔ)大家可以上網(wǎng)查查或者找一些相關(guān)書籍補(bǔ)充一下，這里就不涉獵了，我們就直奔主題吧，希望MySQL中執(zhí)行SQL語(yǔ)句方法這篇文章可以給大家?guī)?lái)一些實(shí)際幫助。                                                          

一 MySQL 基礎(chǔ)架構(gòu)分析

1.1 MySQL 基本架構(gòu)概覽

下圖是 MySQL  的一個(gè)簡(jiǎn)要架構(gòu)圖，從下圖你可以很清晰的看到用戶的 SQL 語(yǔ)句在 MySQL 內(nèi)部是如何執(zhí)行的。

先簡(jiǎn)單介紹一下下圖涉及的一些組件的基本作用幫助大家理解這幅圖，在 1.2 節(jié)中會(huì)詳細(xì)介紹到這些組件的作用。

連接器： 身份認(rèn)證和權(quán)限相關(guān)(登錄 MySQL 的時(shí)候)。

查詢緩存:  執(zhí)行查詢語(yǔ)句的時(shí)候，會(huì)先查詢緩存（MySQL 8.0 版本后移除，因?yàn)檫@個(gè)功能不太實(shí)用）。

分析器:  沒(méi)有命中緩存的話，SQL 語(yǔ)句就會(huì)經(jīng)過(guò)分析器，分析器說(shuō)白了就是要先看你的 SQL 語(yǔ)句要干嘛，再檢查你的 SQL 語(yǔ)句語(yǔ)法是否正確。

優(yōu)化器：  按照 MySQL 認(rèn)為最優(yōu)的方案去執(zhí)行。

執(zhí)行器: 執(zhí)行語(yǔ)句，然后從存儲(chǔ)引擎返回?cái)?shù)據(jù)。

簡(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 模塊。現(xiàn)在最常用的存儲(chǔ)引擎是 InnoDB，它從 MySQL 5.5.5 版本開始就被當(dāng)做默認(rèn)存儲(chǔ)引擎了。

1.2 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ì)直接返回給客戶端，如果沒(méi)有命中，就會(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 沒(méi)有命中緩存，那么就會(huì)進(jìn)入分析器，分析器主要是用來(lái)分析 SQL 語(yǔ)句是來(lái)干嘛的，分析器也會(huì)分為幾步：

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

第二步，語(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)該用戶有沒(méi)有權(quán)限，如果沒(méi)有權(quán)限，就會(huì)返回錯(cuò)誤信息，如果有權(quán)限，就會(huì)去調(diào)用引擎的接口，返回接口執(zhí)行的結(jié)果。

二 語(yǔ)句分析

2.1 查詢語(yǔ)句

說(shuō)了以上這么多，那么究竟一條 sql 語(yǔ)句是如何執(zhí)行的呢？其實(shí)我們的 sql 可以分為兩種，一種是查詢，一種是更新（增加，更新，刪除）。我們先分析下查詢語(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ì)先查詢緩存，以這條 sql 語(yǔ)句為 key 在內(nèi)存中查詢是否有結(jié)果，如果有直接緩存，如果沒(méi)有，執(zhí)行下一步。

通過(guò)分析器進(jìn)行詞法分析，提取 sql 語(yǔ)句的關(guān)鍵元素，比如提取上面這個(gè)語(yǔ)句是查詢 select，提取需要查詢的表名為 tb_student,需要查詢所有的列，查詢條件是這個(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í)行方案：

  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)，如果沒(méi)有權(quán)限就會(huì)返回錯(cuò)誤信息，如果有權(quán)限就會(huì)調(diào)用數(shù)據(jù)庫(kù)引擎接口，返回引擎的執(zhí)行結(jié)果。

2.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 并沒(méi)與 InnoDB 引擎( InnoDB 引擎是其他公司以插件形式插入 MySQL 的) ，MySQL 自帶的引擎是 MyISAM，但是我們知道 redo log 是 InnoDB 引擎特有的，其他存儲(chǔ)引擎都沒(méi)有，這就導(dǎo)致會(huì)沒(méi)有 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 日志沒(méi)有被寫入，那么機(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ù)。

先寫 binlog，然后寫 redo log，假設(shè)寫完了 binlog，機(jī)器異常重啟了，由于沒(méi)有 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)了，有沒(mé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)題。

三 總結(jié)

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

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

查詢語(yǔ)句的執(zhí)行流程如下：權(quán)限校驗(yàn)（如果命中緩存）---》查詢緩存---》分析器---》優(yōu)化器---》權(quán)限校驗(yàn)---》執(zhí)行器---》引擎

更新語(yǔ)句執(zhí)行流程如下：分析器----》權(quán)限校驗(yàn)----》執(zhí)行器---》引擎---redo log(prepare 狀態(tài)---》binlog---》redo log(commit狀態(tài))【相關(guān)推薦：MySQL教程】

MySQL中執(zhí)行SQL語(yǔ)句方法介紹就先給大家講到這里，對(duì)于其它相關(guān)問(wèn)題大家想要了解的可以持續(xù)關(guān)注我們的行業(yè)資訊。我們的板塊內(nèi)容每天都會(huì)捕捉一些行業(yè)新聞及專業(yè)知識(shí)分享給大家的。