面試官：MySQL 是如何執行一條查詢語句的？

初念初戀發表於2022-03-01
面試MySql

對於一個開發工程師來說，瞭解一下 MySQL 是如何執行一條查詢語句的，我想是非常有必要的。

首先我們要了解一下MYSQL的體系架構是什麼樣子的？然後再來聊聊一條查詢語句的執行流程是如何？

MYSQL體系結構

先看一張架構圖，如下：

模組詳解

  1. Connector：用來支援各種語言和 SQL 的互動，比如 PHP，Python，Java 的 JDBC；
  2. Management Serveices & Utilities：系統管理和控制工具，包括備份恢復、MySQL 複製、叢集等；
  3. Connection Pool：連線池，管理需要緩衝的資源，包括使用者密碼許可權執行緒等等；
  4. SQL Interface：用來接收使用者的 SQL 命令，返回使用者需要的查詢結果 ；
  5. Parser：用來解析 SQL 語句；
  6. Optimizer：查詢優化器；
  7. Cache and Buffer：查詢快取，除了行記錄的快取之外，還有表快取，Key 快取，許可權快取等等；
  8. Pluggable Storage Engines：外掛式儲存引擎，它提供 API 給服務層使用，跟具體的檔案打交道。

架構分層

把 MySQL 分成三層，跟客戶端對接的連線層，真正執行操作的服務層，和跟硬體打交道的儲存引擎層。

image-20211007102305222

連線層

我們的客戶端要連線到 MySQL 伺服器 3306 埠，必須要跟服務端建立連線，那麼管理所有的連線，驗證客戶端的身份和許可權，這些功能就在連線層完成。

服務層

連線層會把 SQL 語句交給服務層，這裡面又包含一系列的流程：

比如查詢快取的判斷、根據 SQL 呼叫相應的介面，對我們的 SQL 語句進行詞法和語法的解析（比如關鍵字怎麼識別，別名怎麼識別，語法有沒有錯誤等等）。

然後就是優化器，MySQL 底層會根據一定的規則對我們的 SQL 語句進行優化，最後再交給執行器去執行。

儲存引擎

儲存引擎就是我們的資料真正存放的地方，在 MySQL 裡面支援不同的儲存引擎。再往下就是記憶體或者磁碟。

SQL的執行流程

以一條查詢語句為例，我們來看下 MySQL 的工作流程是什麼樣的。

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

首先我們們先來看一張圖，接下來的過程都是基於這張圖來講的：

image-20211006202806875

連線

程式或者工具要運算元據庫，第一步要跟資料庫建立連線。

在資料庫中有兩種連線:

  • 短連線：短連線就是操作完畢以後，馬上 close 掉。
  • 長連線：長連線可以保持開啟，減少服務端建立和釋放連線的消耗，後面的程式訪問的時候還可以使用這個連線。

建立連線是比較麻煩的，首先要傳送請求，傳送了請求要去驗證賬號密碼，驗證完了要去看你所擁有的許可權，所以在使用過程中，儘量使用長連線。

保持長連線會消耗記憶體。長時間不活動的連線，MySQL 伺服器會斷開。可以使用sql語句檢視預設時間：

show global variables like 'wait_timeout';

這個時間是由 wait_timeout 來控制的，預設都是 28800 秒，8 小時。

查詢快取

MySQL 內部自帶了一個快取模組。執行相同的查詢之後我們發現快取沒有生效，為什麼？MySQL 的快取預設是關閉的。

show variables like 'query_cache%';

預設關閉的意思就是不推薦使用，為什麼 MySQL 不推薦使用它自帶的快取呢？

主要是因為 MySQL 自帶的快取的應用場景有限：

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

第二個是表裡面任何一條資料發生變化的時候，這張表所有快取都會失效，所以對於有大量資料更新的應用，也不適合。

所以快取還是交給 ORM 框架（比如 MyBatis 預設開啟了一級快取），或者獨立的快取服務，比如 Redis 來處理更合適。

在 MySQL 8.0 中，查詢快取已經被移除了。

語法解析和預處理

為什麼一條 SQL 語句能夠被識別呢？假如隨便執行一個字串 hello，伺服器報了一個 1064 的錯：

[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

這個就是 MySQL 的解析器和預處理模組。

這一步主要做的事情是對語句基於 SQL 語法進行詞法和語法分析和語義的解析。

詞法解析

詞法分析就是把一個完整的 SQL 語句打碎成一個個的單詞。

比如一個簡單的 SQL 語句：select name from user where id = 1 and age >20;

image-20211006224637475

它會將 select 識別出來，這是一個查詢語句，接下來會將 user 也識別出來，你是想要在這個表中做查詢，然後將 where 後面的條件也識別出來，原來我需要去查詢這些內容。

語法分析

語法分析會對 SQL 做一些語法檢查，比如單引號有沒有閉合，然後根據 MySQL 定義的語法規則，根據 SQL 語句生成一個資料結構。這個資料結構我們把它叫做解析樹（select_lex）。

就比如英語裡面的語法 “我用 is ， 你用 are ”這種，如果不對肯定是不可以的，語法分析之後發現你的 SQL 語句不符合規則，就會收到 You hava an error in your SQL syntax 的錯誤提示。

前處理器

如果寫了一個詞法和語法都正確的 SQL，但是表名或者欄位不存在，會在哪裡報錯？ 是在資料庫的執行層還是解析器？比如：
select * from hello;

還是在解析的時候報錯，解析 SQL 的環節裡面有個前處理器。它會檢查生成的解析樹，解決解析器無法解析的語義。比如，它會檢查表和列名是否存在，檢查名字和別名， 保證沒有歧義。預處理之後得到一個新的解析樹。

查詢優化器

一條SQL語句是不是隻有一種執行方式？或者說資料庫最終執行的SQL是不是就是我們傳送的 SQL？

這個答案是否定的。一條 SQL 語句是可以有很多種執行方式的，最終返回相同的結果，他們是等價的。但是如果有這麼多種執行方式，這些執行方式怎麼得到的？最終選擇哪一種去執行？根據什麼判斷標準去選擇？

這個就是 MySQL 的查詢優化器的模組（Optimizer）。 查詢優化器的目的就是根據解析樹生成不同的執行計劃（Execution Plan），然後選 擇一種最優的執行計劃，MySQL 裡面使用的是基於開銷（cost）的優化器，那種執行計劃開銷最小，就用哪種。

可以使用這個命令檢視查詢的開銷：

show status like 'Last_query_cost';
MySQL 的優化器能處理哪些優化型別呢？

舉兩個簡單的例子：

1、當我們對多張表進行關聯查詢的時候，以哪個表的資料作為基準表。

2、有多個索引可以使用的時候，選擇哪個索引。

實際上，對於每一種資料庫來說，優化器的模組都是必不可少的，他們通過複雜的演算法實現儘可能優化查詢效率的目標。但是優化器也不是萬能的，並不是再垃圾的 SQL 語句都能自動優化，也不是每次都能選擇到最優的執行計劃，大家在編寫 SQL 語句的時候還是要注意。

執行計劃

優化器最終會把解析樹變成一個執行計劃（execution_plans），執行計劃是一個資料結構。當然，這個執行計劃不一定是最優的執行計劃，因為 MySQL 也有可能覆蓋不到所有的執行計劃。

我們怎麼檢視 MySQL 的執行計劃呢？比如多張表關聯查詢，先查詢哪張表？在執行查詢的時候可能用到哪些索引，實際上用到了什麼索引？

MySQL 提供了一個執行計劃的工具。我們在 SQL 語句前面加上 EXPLAIN，就可以看到執行計劃的資訊。

EXPLAIN select name from user where id=1;

儲存引擎

在介紹儲存引擎先來問兩個問題：

1、從邏輯的角度來說，我們的資料是放在哪裡的，或者說放在一個什麼結構裡面？

2、執行計劃在哪裡執行？是誰去執行？

儲存引擎基本介紹

在關係型資料庫裡面，資料是放在表 Table 裡面的。我們可以把這個表理解成 Excel 電子表格的形式。所以我們的表在儲存資料的同時，還要組織資料的儲存結構，這個儲存結構就是由我們的儲存引擎決定的，所以我們也可以把儲存引擎叫做表型別。

在 MySQL 裡面，支援多種儲存引擎，他們是可以替換的，所以叫做外掛式的儲存引擎。為什麼要支援這麼多儲存引擎呢？一種還不夠用嗎？

在 MySQL 裡面，每一張表都可以指定它的儲存引擎，而不是一個資料庫只能使用一個儲存引擎。儲存引擎的使用是以表為單位的。而且，建立表之後還可以修改儲存引擎。

如何選擇儲存引擎？

  • 如果對資料一致性要求比較高，需要事務支援，可以選擇 InnoDB。
  • 如果資料查詢多更新少，對查詢效能要求比較高，可以選擇 MyISAM。
  • 如果需要一個用於查詢的臨時表，可以選擇 Memory。
  • 如果所有的儲存引擎都不能滿足你的需求，並且技術能力足夠，可以根據官網內部手冊用 C 語言開發一個儲存引擎。（https://dev.mysql.com/doc/int...）

執行引擎

誰使用執行計劃去操作儲存引擎呢？這就是執行引擎（執行器），它利用儲存引擎提供的相應的 API 來完成操作。

為什麼我們修改了表的儲存引擎，操作方式不需要做任何改變？因為不同功能的儲存引擎實現的 API 是相同的。

最後把資料返回給客戶端，即使沒有結果也要返回。

栗子

還是以上面的sql語句為例，再來梳理一下整個sql執行流程。

select name from user where id = 1 and age >20;
  1. 通過聯結器查詢當前執行者的角色是否有許可權，進行查詢。如果有的話，就繼續往下走，如果沒有的話，就會被拒絕掉，同時報出 Access denied for user 的錯誤資訊；
  2. 接下來就是去查詢快取，首先看快取裡面有沒有，如果有呢，那就沒有必要向下走，直接返回給客戶端結果就可以了；如果快取中沒有的話，那就去執行語法解析器和預處理模組。（ MySQL 8.0 版本直接將查詢快取的整塊功能都給刪掉了）
  3. 語法解析器和預處理主要是分析sql語句的詞法和語法是否正確，沒啥問題就會進行下一步，來到查詢優化器；

  4. 查詢優化器就會對sql語句進行一些優化，看哪種方式是最節省開銷，就會執行哪種sql語句，上面的sql有兩種優化方案：

    • 先查詢表 user 中 id 為 1 的人的姓名，然後再從裡面找年齡大於 20 歲的。
    • 先查詢表 user 中年齡大於 20 歲的所有人，然後再從裡面找 id 為 1 的。
  5. 優化器決定選擇哪個方案之後，執行引擎就去執行了。然後返回給客戶端結果。

結語

如果文章對你有點幫助，還是希望你們看完動動小手指，點贊、關注和收藏。

相關文章