深入解析MySQL中SQL的執(zhí)行流程（圖文結(jié)合）

3111

本篇文章帶大家了解mysql中sql的執(zhí)行流程，看看mysql 是如何執(zhí)行一條查詢語(yǔ)句的？希望對(duì)大家有所幫助！

深入解析MySQL中SQL的執(zhí)行流程（圖文結(jié)合）

對(duì)于一個(gè)開發(fā)工程師來(lái)說(shuō)，了解一下 MySQL 是如何執(zhí)行一條查詢語(yǔ)句的，我想是非常有必要的。【相關(guān)推薦：mysql視頻教程】

首先我們要了解一下MYSQL的體系架構(gòu)是什么樣子的？然后再來(lái)聊聊一條查詢語(yǔ)句的執(zhí)行流程是如何？

MYSQL體系結(jié)構(gòu)

先看一張架構(gòu)圖，如下：

深入解析MySQL中SQL的執(zhí)行流程（圖文結(jié)合）

模塊詳解

  • Connector：用來(lái)支持各種語(yǔ)言和 SQL 的交互，比如 PHP，Python，Java 的 JDBC；

  • Management Serveices & Utilities：系統(tǒng)管理和控制工具，包括備份恢復(fù)、MySQL 復(fù)制、集群等；

  • Connection Pool：連接池，管理需要緩沖的資源，包括用戶密碼權(quán)限線程等等；

  • SQL Interface：用來(lái)接收用戶的 SQL 命令，返回用戶需要的查詢結(jié)果 ；

  • Parser：用來(lái)解析 SQL 語(yǔ)句；

  • Optimizer：查詢優(yōu)化器；

  • Cache and Buffer：查詢緩存，除了行記錄的緩存之外，還有表緩存，Key 緩存，權(quán)限緩存等等；

  • Pluggable Storage Engines：插件式存儲(chǔ)引擎，它提供 API 給服務(wù)層使用，跟具體的文件打交道。

架構(gòu)分層

把 MySQL 分成三層，跟客戶端對(duì)接的連接層，真正執(zhí)行操作的服務(wù)層，和跟硬件打交道的存儲(chǔ)引擎層。

深入解析MySQL中SQL的執(zhí)行流程（圖文結(jié)合）

連接層

我們的客戶端要連接到 MySQL 服務(wù)器 3306 端口，必須要跟服務(wù)端建立連接，那么管理所有的連接，驗(yàn)證客戶端的身份和權(quán)限，這些功能就在連接層完成。

服務(wù)層

連接層會(huì)把 SQL 語(yǔ)句交給服務(wù)層，這里面又包含一系列的流程：

比如查詢緩存的判斷、根據(jù) SQL 調(diào)用相應(yīng)的接口，對(duì)我們的 SQL 語(yǔ)句進(jìn)行詞法和語(yǔ)法的解析（比如關(guān)鍵字怎么識(shí)別，別名怎么識(shí)別，語(yǔ)法有沒(méi)有錯(cuò)誤等等）。

然后就是優(yōu)化器，MySQL 底層會(huì)根據(jù)一定的規(guī)則對(duì)我們的 SQL 語(yǔ)句進(jìn)行優(yōu)化，最后再交給執(zhí)行器去執(zhí)行。

存儲(chǔ)引擎

存儲(chǔ)引擎就是我們的數(shù)據(jù)真正存放的地方，在 MySQL 里面支持不同的存儲(chǔ)引擎。再往下就是內(nèi)存或者磁盤。

SQL的執(zhí)行流程

以一條查詢語(yǔ)句為例，我們來(lái)看下 MySQL 的工作流程是什么樣的。

select?name?from?user?where?id=1?and?age>20;

首先咱們先來(lái)看一張圖，接下來(lái)的過(guò)程都是基于這張圖來(lái)講的：

深入解析MySQL中SQL的執(zhí)行流程（圖文結(jié)合）

連接

程序或者工具要操作數(shù)據(jù)庫(kù)，第一步要跟數(shù)據(jù)庫(kù)建立連接。

在數(shù)據(jù)庫(kù)中有兩種連接:

  • 短連接：短連接就是操作完畢以后，馬上 close 掉。
  • 長(zhǎng)連接：長(zhǎng)連接可以保持打開，減少服務(wù)端創(chuàng)建和釋放連接的消耗，后面的程序訪問(wèn)的時(shí)候還可以使用這個(gè)連接。

建立連接是比較麻煩的，首先要發(fā)送請(qǐng)求，發(fā)送了請(qǐng)求要去驗(yàn)證賬號(hào)密碼，驗(yàn)證完了要去看你所擁有的權(quán)限，所以在使用過(guò)程中，盡量使用長(zhǎng)連接。

保持長(zhǎng)連接會(huì)消耗內(nèi)存。長(zhǎng)時(shí)間不活動(dòng)的連接，MySQL 服務(wù)器會(huì)斷開。可以使用sql語(yǔ)句查看默認(rèn)時(shí)間：

show?global?variables?like?'wait_timeout';

這個(gè)時(shí)間是由 wait_timeout 來(lái)控制的，默認(rèn)都是 28800 秒，8 小時(shí)。

查詢緩存

MySQL 內(nèi)部自帶了一個(gè)緩存模塊。執(zhí)行相同的查詢之后我們發(fā)現(xiàn)緩存沒(méi)有生效，為什么？MySQL 的緩存默認(rèn)是關(guān)閉的。

show?variables?like?'query_cache%';

默認(rèn)關(guān)閉的意思就是不推薦使用，為什么 MySQL 不推薦使用它自帶的緩存呢？

主要是因?yàn)?MySQL 自帶的緩存的應(yīng)用場(chǎng)景有限：

第一個(gè)是它要求 SQL 語(yǔ)句必須一模一樣，中間多一個(gè)空格，字母大小寫不同都被認(rèn)為是不同的的 SQL。

第二個(gè)是表里面任何一條數(shù)據(jù)發(fā)生變化的時(shí)候，這張表所有緩存都會(huì)失效，所以對(duì)于有大量數(shù)據(jù)更新的應(yīng)用，也不適合。

所以緩存還是交給 ORM 框架（比如 MyBatis 默認(rèn)開啟了一級(jí)緩存），或者獨(dú)立的緩存服務(wù)，比如 Redis 來(lái)處理更合適。

在 MySQL 8.0 中，查詢緩存已經(jīng)被移除了。

語(yǔ)法解析和預(yù)處理

為什么一條 SQL 語(yǔ)句能夠被識(shí)別呢？假如隨便執(zhí)行一個(gè)字符串 hello，服務(wù)器報(bào)了一個(gè) 1064 的錯(cuò)：

[Err] 1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘hello’ at line 1

這個(gè)就是 MySQL 的解析器和預(yù)處理模塊。

這一步主要做的事情是對(duì)語(yǔ)句基于 SQL 語(yǔ)法進(jìn)行詞法和語(yǔ)法分析和語(yǔ)義的解析。

詞法解析

詞法分析就是把一個(gè)完整的 SQL 語(yǔ)句打碎成一個(gè)個(gè)的單詞。

比如一個(gè)簡(jiǎn)單的 SQL 語(yǔ)句：select name from user where id = 1 and age >20;

深入解析MySQL中SQL的執(zhí)行流程（圖文結(jié)合）

它會(huì)將 select 識(shí)別出來(lái)，這是一個(gè)查詢語(yǔ)句，接下來(lái)會(huì)將 user 也識(shí)別出來(lái)，你是想要在這個(gè)表中做查詢，然后將 where 后面的條件也識(shí)別出來(lái)，原來(lái)我需要去查找這些內(nèi)容。

語(yǔ)法分析

語(yǔ)法分析會(huì)對(duì) SQL 做一些語(yǔ)法檢查，比如單引號(hào)有沒(méi)有閉合，然后根據(jù) MySQL 定義的語(yǔ)法規(guī)則，根據(jù) SQL 語(yǔ)句生成一個(gè)數(shù)據(jù)結(jié)構(gòu)。這個(gè)數(shù)據(jù)結(jié)構(gòu)我們把它叫做解析樹（select_lex）。

就比如英語(yǔ)里面的語(yǔ)法 “我用 is ， 你用 are ”這種，如果不對(duì)肯定是不可以的，語(yǔ)法分析之后發(fā)現(xiàn)你的 SQL 語(yǔ)句不符合規(guī)則，就會(huì)收到 You hava an error in your SQL syntax 的錯(cuò)誤提示。

預(yù)處理器

如果寫了一個(gè)詞法和語(yǔ)法都正確的 SQL，但是表名或者字段不存在，會(huì)在哪里報(bào)錯(cuò)？ 是在數(shù)據(jù)庫(kù)的執(zhí)行層還是解析器？比如：select * from hello;

還是在解析的時(shí)候報(bào)錯(cuò)，解析 SQL 的環(huán)節(jié)里面有個(gè)預(yù)處理器。它會(huì)檢查生成的解析樹，解決解析器無(wú)法解析的語(yǔ)義。比如，它會(huì)檢查表和列名是否存在，檢查名字和別名， 保證沒(méi)有歧義。預(yù)處理之后得到一個(gè)新的解析樹。

查詢優(yōu)化器

一條SQL語(yǔ)句是不是只有一種執(zhí)行方式？或者說(shuō)數(shù)據(jù)庫(kù)最終執(zhí)行的SQL是不是就是我們發(fā)送的 SQL？

這個(gè)答案是否定的。一條 SQL 語(yǔ)句是可以有很多種執(zhí)行方式的，最終返回相同的結(jié)果，他們是等價(jià)的。但是如果有這么多種執(zhí)行方式，這些執(zhí)行方式怎么得到的？最終選擇哪一種去執(zhí)行？根據(jù)什么判斷標(biāo)準(zhǔn)去選擇？

這個(gè)就是 MySQL 的查詢優(yōu)化器的模塊（Optimizer）。 查詢優(yōu)化器的目的就是根據(jù)解析樹生成不同的執(zhí)行計(jì)劃（Execution Plan），然后選 擇一種最優(yōu)的執(zhí)行計(jì)劃，MySQL 里面使用的是基于開銷（cost）的優(yōu)化器，那種執(zhí)行計(jì)劃開銷最小，就用哪種。

可以使用這個(gè)命令查看查詢的開銷：

show?status?like?'Last_query_cost';

MySQL 的優(yōu)化器能處理哪些優(yōu)化類型呢？

舉兩個(gè)簡(jiǎn)單的例子：

1、當(dāng)我們對(duì)多張表進(jìn)行關(guān)聯(lián)查詢的時(shí)候，以哪個(gè)表的數(shù)據(jù)作為基準(zhǔn)表。

2、有多個(gè)索引可以使用的時(shí)候，選擇哪個(gè)索引。

實(shí)際上，對(duì)于每一種數(shù)據(jù)庫(kù)來(lái)說(shuō)，優(yōu)化器的模塊都是必不可少的，他們通過(guò)復(fù)雜的算法實(shí)現(xiàn)盡可能優(yōu)化查詢效率的目標(biāo)。但是優(yōu)化器也不是萬(wàn)能的，并不是再垃圾的 SQL 語(yǔ)句都能自動(dòng)優(yōu)化，也不是每次都能選擇到最優(yōu)的執(zhí)行計(jì)劃，大家在編寫 SQL 語(yǔ)句的時(shí)候還是要注意。

執(zhí)行計(jì)劃

優(yōu)化器最終會(huì)把解析樹變成一個(gè)執(zhí)行計(jì)劃（execution_plans），執(zhí)行計(jì)劃是一個(gè)數(shù)據(jù)結(jié)構(gòu)。當(dāng)然，這個(gè)執(zhí)行計(jì)劃不一定是最優(yōu)的執(zhí)行計(jì)劃，因?yàn)?MySQL 也有可能覆蓋不到所有的執(zhí)行計(jì)劃。

我們?cè)趺床榭?MySQL 的執(zhí)行計(jì)劃呢？比如多張表關(guān)聯(lián)查詢，先查詢哪張表？在執(zhí)行查詢的時(shí)候可能用到哪些索引，實(shí)際上用到了什么索引？

MySQL 提供了一個(gè)執(zhí)行計(jì)劃的工具。我們?cè)?SQL 語(yǔ)句前面加上 EXPLAIN，就可以看到執(zhí)行計(jì)劃的信息。

EXPLAIN?select?name?from?user?where?id=1;

存儲(chǔ)引擎

在介紹存儲(chǔ)引擎先來(lái)問(wèn)兩個(gè)問(wèn)題：

1、從邏輯的角度來(lái)說(shuō)，我們的數(shù)據(jù)是放在哪里的，或者說(shuō)放在一個(gè)什么結(jié)構(gòu)里面？

2、執(zhí)行計(jì)劃在哪里執(zhí)行？是誰(shuí)去執(zhí)行？

存儲(chǔ)引擎基本介紹

在關(guān)系型數(shù)據(jù)庫(kù)里面，數(shù)據(jù)是放在表 Table 里面的。我們可以把這個(gè)表理解成 Excel 電子表格的形式。所以我們的表在存儲(chǔ)數(shù)據(jù)的同時(shí)，還要組織數(shù)據(jù)的存儲(chǔ)結(jié)構(gòu)，這個(gè)存儲(chǔ)結(jié)構(gòu)就是由我們的存儲(chǔ)引擎決定的，所以我們也可以把存儲(chǔ)引擎叫做表類型。

在 MySQL 里面，支持多種存儲(chǔ)引擎，他們是可以替換的，所以叫做插件式的存儲(chǔ)引擎。為什么要支持這么多存儲(chǔ)引擎呢？一種還不夠用嗎？

在 MySQL 里面，每一張表都可以指定它的存儲(chǔ)引擎，而不是一個(gè)數(shù)據(jù)庫(kù)只能使用一個(gè)存儲(chǔ)引擎。存儲(chǔ)引擎的使用是以表為單位的。而且，創(chuàng)建表之后還可以修改存儲(chǔ)引擎。

如何選擇存儲(chǔ)引擎？

  • 如果對(duì)數(shù)據(jù)一致性要求比較高，需要事務(wù)支持，可以選擇 InnoDB。

  • 如果數(shù)據(jù)查詢多更新少，對(duì)查詢性能要求比較高，可以選擇 MyISAM。

  • 如果需要一個(gè)用于查詢的臨時(shí)表，可以選擇 Memory。

  • 如果所有的存儲(chǔ)引擎都不能滿足你的需求，并且技術(shù)能力足夠，可以根據(jù)官網(wǎng)內(nèi)部手冊(cè)用 C 語(yǔ)言開發(fā)一個(gè)存儲(chǔ)引擎。（https://dev.mysql.com/doc/internals/en/custom-engine.html%EF%BC%89?）

執(zhí)行引擎

誰(shuí)使用執(zhí)行計(jì)劃去操作存儲(chǔ)引擎呢？這就是執(zhí)行引擎（執(zhí)行器），它利用存儲(chǔ)引擎提供的相應(yīng)的 API 來(lái)完成操作。

為什么我們修改了表的存儲(chǔ)引擎，操作方式不需要做任何改變？因?yàn)椴煌δ艿拇鎯?chǔ)引擎實(shí)現(xiàn)的 API 是相同的。

最后把數(shù)據(jù)返回給客戶端，即使沒(méi)有結(jié)果也要返回。

栗子

還是以上面的sql語(yǔ)句為例，再來(lái)梳理一下整個(gè)sql執(zhí)行流程。

select?name?from?user?where?id?=?1?and?age?>20;

  • 通過(guò)連接器查詢當(dāng)前執(zhí)行者的角色是否有權(quán)限，進(jìn)行查詢。如果有的話，就繼續(xù)往下走，如果沒(méi)有的話，就會(huì)被拒絕掉，同時(shí)報(bào)出 Access denied for user 的錯(cuò)誤信息；

  • 接下來(lái)就是去查詢緩存，首先看緩存里面有沒(méi)有，如果有呢，那就沒(méi)有必要向下走，直接返回給客戶端結(jié)果就可以了；如果緩存中沒(méi)有的話，那就去執(zhí)行語(yǔ)法解析器和預(yù)處理模塊。（ MySQL 8.0 版本直接將查詢緩存的整塊功能都給刪掉了）

  • 語(yǔ)法解析器和預(yù)處理主要是分析sql語(yǔ)句的詞法和語(yǔ)法是否正確，沒(méi)啥問(wèn)題就會(huì)進(jìn)行下一步，來(lái)到查詢優(yōu)化器；

  • 查詢優(yōu)化器就會(huì)對(duì)sql語(yǔ)句進(jìn)行一些優(yōu)化，看哪種方式是最節(jié)省開銷，就會(huì)執(zhí)行哪種sql語(yǔ)句，上面的sql有兩種優(yōu)化方案：

    • 先查詢表 user 中 id 為 1 的人的姓名，然后再?gòu)睦锩嬲夷挲g大于 20 歲的。
    • 先查詢表 user 中年齡大于 20 歲的所有人，然后再?gòu)睦锩嬲?id 為 1 的。

  • 優(yōu)化器決定選擇哪個(gè)方案之后，執(zhí)行引擎就去執(zhí)行了。然后返回給客戶端結(jié)果。

更多編程相關(guān)知識(shí)，請(qǐng)?jiān)L問(wèn)：mysql視頻教程！！

? 版權(quán)聲明
THE END
數(shù)據(jù)庫(kù)
# mysql
喜歡就支持一下吧
點(diǎn)贊11 分享