步步深入:MySQL 架構總覽->查詢執行流程->SQL 解析順序

2021-12-28 PHP開源社區

前言：

一直是想知道一條SQL語句是怎麼被執行的，它執行的順序是怎樣的，然後查看總結各方資料，就有了下面這一篇博文了。

本文將從MySQL總體架構--->查詢執行流程--->語句執行順序來探討一下其中的知識。

一、MySQL架構總覽：

架構最好看圖，再配上必要的說明文字。

下圖根據參考書籍中一圖為原本，再在其上添加上了自己的理解。

img

從上圖中我們可以看到，整個架構分為兩層，上層是MySQLD的被稱為的『SQL Layer』，下層是各種各樣對上提供接口的存儲引擎，被稱為『Storage Engine Layer』。其它各個模塊和組件，從名字上就可以簡單了解到它們的作用，這裡就不再累述了。

二、查詢執行流程

下面再向前走一些，容我根據自己的認識說一下查詢執行的流程是怎樣的：

1.連接

1.1客戶端發起一條Query請求，監聽客戶端的『連接管理模塊』接收請求

1.2將請求轉發到『連接進/線程模塊』

1.3調用『用戶模塊』來進行授權檢查

1.4通過檢查後，『連接進/線程模塊』從『線程連接池』中取出空閒的被緩存的連接線程和客戶端請求對接，如果失敗則創建一個新的連接請求

2.處理

2.1先查詢緩存，檢查Query語句是否完全匹配，接著再檢查是否具有權限，都成功則直接取數據返回

2.2上一步有失敗則轉交給『命令解析器』，經過詞法分析，語法分析後生成解析樹

2.3接下來是預處理階段，處理解析器無法解決的語義，檢查權限等，生成新的解析樹

2.4再轉交給對應的模塊處理

2.5如果是SELECT查詢還會經由『查詢優化器』做大量的優化，生成執行計劃

2.6模塊收到請求後，通過『訪問控制模塊』檢查所連接的用戶是否有訪問目標表和目標欄位的權限

2.7有則調用『表管理模塊』，先是查看table cache中是否存在，有則直接對應的表和獲取鎖，否則重新打開表文件

2.8根據表的meta數據，獲取表的存儲引擎類型等信息，通過接口調用對應的存儲引擎處理

2.9上述過程中產生數據變化的時候，若打開日誌功能，則會記錄到相應二進位日誌文件中

3.結果

3.1Query請求完成後，將結果集返回給『連接進/線程模塊』

3.2返回的也可以是相應的狀態標識，如成功或失敗等

3.3『連接進/線程模塊』進行後續的清理工作，並繼續等待請求或斷開與客戶端的連接

一圖小總結

三、SQL解析順序

接下來再走一步，讓我們看看一條SQL語句的前世今生。

首先看一下示例語句

SELECT DISTINCT
    < select_list >
FROM
    < left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
    < where_condition >
GROUP BY
    < group_by_list >
HAVING
    < having_condition >
ORDER BY
    < order_by_condition >
LIMIT < limit_number >

然而它的執行順序是這樣的
 1 FROM <left_table>
 2 ON <join_condition>
 3 <join_type> JOIN <right_table>
 4 WHERE <where_condition>
 5 GROUP BY <group_by_list>
 6 HAVING <having_condition>
 7 SELECT 
 8 DISTINCT <select_list>
 9 ORDER BY <order_by_condition>
10 LIMIT <limit_number>

雖然自己沒想到是這樣的，不過一看還是很自然和諧的，從哪裡獲取，不斷的過濾條件，要選擇一樣或不一樣的，排好序，那才知道要取前幾條呢。
既然如此了，那就讓我們一步步來看看其中的細節吧。
準備工作
1.創建測試資料庫
create database testQuery
2.創建測試表

CREATE TABLE table1
(
    uid VARCHAR(10) NOT NULL,
    name VARCHAR(10) NOT NULL,
    PRIMARY KEY(uid)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;

CREATE TABLE table2
(
    oid INT NOT NULL auto_increment,
    uid VARCHAR(10),
    PRIMARY KEY(oid)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;

3.插入數據
INSERT INTO table1(uid,name) VALUES('aaa','mike'),('bbb','jack'),('ccc','mike'),('ddd','mike');

INSERT INTO table2(uid) VALUES('aaa'),('aaa'),('bbb'),('bbb'),('bbb'),('ccc'),(NULL);
4.最後想要的結果

SELECT
    a.uid,
    count(b.oid) AS total
FROM
    table1 AS a
LEFT JOIN table2 AS b ON a.uid = b.uid
WHERE
    a. NAME = 'mike'
GROUP BY
    a.uid
HAVING
    count(b.oid) < 2
ORDER BY
    total DESC
LIMIT 1;

！現在開始SQL解析之旅吧！
1. FROM
當涉及多個表的時候，左邊表的輸出會作為右邊表的輸入，之後會生成一個虛擬表VT1。
(1-J1)笛卡爾積
計算兩個相關聯表的笛卡爾積(CROSS JOIN) ，生成虛擬表VT1-J1。

mysql> select * from table1,table2;
++-++-+
| uid | name | oid | uid  |
++-++-+
| aaa | mike |   1 | aaa  |
| bbb | jack |   1 | aaa  |
| ccc | mike |   1 | aaa  |
| ddd | mike |   1 | aaa  |
| aaa | mike |   2 | aaa  |
| bbb | jack |   2 | aaa  |
| ccc | mike |   2 | aaa  |
| ddd | mike |   2 | aaa  |
| aaa | mike |   3 | bbb  |
| bbb | jack |   3 | bbb  |
| ccc | mike |   3 | bbb  |
| ddd | mike |   3 | bbb  |
| aaa | mike |   4 | bbb  |
| bbb | jack |   4 | bbb  |
| ccc | mike |   4 | bbb  |
| ddd | mike |   4 | bbb  |
| aaa | mike |   5 | bbb  |
| bbb | jack |   5 | bbb  |
| ccc | mike |   5 | bbb  |
| ddd | mike |   5 | bbb  |
| aaa | mike |   6 | ccc  |
| bbb | jack |   6 | ccc  |
| ccc | mike |   6 | ccc  |
| ddd | mike |   6 | ccc  |
| aaa | mike |   7 | NULL |
| bbb | jack |   7 | NULL |
| ccc | mike |   7 | NULL |
| ddd | mike |   7 | NULL |
++-++-+
28 rows in set (0.00 sec)

(1-J2)ON過濾
基於虛擬表VT1-J1這一個虛擬表進行過濾，過濾出所有滿足ON 謂詞條件的列，生成虛擬表VT1-J2。
注意：這裡因為語法限制，使用了'WHERE'代替，從中讀者也可以感受到兩者之間微妙的關係；

mysql> SELECT
    -> *
    -> FROM
    -> table1,
    -> table2
    -> WHERE
    -> table1.uid = table2.uid
    -> ;
++-++-+
| uid | name | oid | uid  |
++-++-+
| aaa | mike |   1 | aaa  |
| aaa | mike |   2 | aaa  |
| bbb | jack |   3 | bbb  |
| bbb | jack |   4 | bbb  |
| bbb | jack |   5 | bbb  |
| ccc | mike |   6 | ccc  |
++-++-+
6 rows in set (0.00 sec)

(1-J3)添加外部列
如果使用了外連接(LEFT,RIGHT,FULL)，主表（保留表）中的不符合ON條件的列也會被加入到VT1-J2中，作為外部行，生成虛擬表VT1-J3。

mysql> SELECT
    -> *
    -> FROM
    -> table1 AS a
    -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid;
++-+-+-+
| uid | name | oid  | uid  |
++-+-+-+
| aaa | mike |    1 | aaa  |
| aaa | mike |    2 | aaa  |
| bbb | jack |    3 | bbb  |
| bbb | jack |    4 | bbb  |
| bbb | jack |    5 | bbb  |
| ccc | mike |    6 | ccc  |
| ddd | mike | NULL | NULL |
++-+-+-+
7 rows in set (0.00 sec)

下面從網上找到一張很形象的關於『SQL JOINS'的解釋圖，如若侵犯了你的權益，請勞煩告知刪除，謝謝。
img
2. WHERE
對VT1過程中生成的臨時表進行過濾，滿足WHERE子句的列被插入到VT2表中。
注意：
此時因為分組，不能使用聚合運算；也不能使用SELECT中創建的別名；
與ON的區別：
如果有外部列，ON針對過濾的是關聯表，主表（保留表）會返回所有的列；
如果沒有添加外部列，兩者的效果是一樣的；
應用：
對主表的過濾應該放在WHERE；
對於關聯表，先條件查詢後連接則用ON，先連接後條件查詢則用WHERE；

mysql> SELECT
    -> *
    -> FROM
    -> table1 AS a
    -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid
    -> WHERE
    -> a. NAME = 'mike';
++-+-+-+
| uid | name | oid  | uid  |
++-+-+-+
| aaa | mike |    1 | aaa  |
| aaa | mike |    2 | aaa  |
| ccc | mike |    6 | ccc  |
| ddd | mike | NULL | NULL |
++-+-+-+
4 rows in set (0.00 sec)

3. GROUP BY
這個子句會把VT2中生成的表按照GROUP BY中的列進行分組。生成VT3表。
注意：
其後處理過程的語句，如SELECT,HAVING，所用到的列必須包含在GROUP BY中，對於沒有出現的，得用聚合函數；
原因：
GROUP BY改變了對表的引用，將其轉換為新的引用方式，能夠對其進行下一級邏輯操作的列會減少；
我的理解是：
根據分組欄位，將具有相同分組欄位的記錄歸併成一條記錄，因為每一個分組只能返回一條記錄，除非是被過濾掉了，而不在分組欄位裡面的欄位可能會有多個值，多個值是無法放進一條記錄的，所以必須通過聚合函數將這些具有多值的列轉換成單值；

mysql> SELECT
    -> *
    -> FROM
    -> table1 AS a
    -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid
    -> WHERE
    -> a. NAME = 'mike'
    -> GROUP BY
    -> a.uid;
++-+-+-+
| uid | name | oid  | uid  |
++-+-+-+
| aaa | mike |    1 | aaa  |
| ccc | mike |    6 | ccc  |
| ddd | mike | NULL | NULL |
++-+-+-+
3 rows in set (0.00 sec)

4. HAVING
這個子句對VT3表中的不同的組進行過濾，只作用於分組後的數據，滿足HAVING條件的子句被加入到VT4表中。

mysql> SELECT
    -> *
    -> FROM
    -> table1 AS a
    -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid
    -> WHERE
    -> a. NAME = 'mike'
    -> GROUP BY
    -> a.uid
    -> HAVING
    -> count(b.oid) < 2;
++-+-+-+
| uid | name | oid  | uid  |
++-+-+-+
| ccc | mike |    6 | ccc  |
| ddd | mike | NULL | NULL |
++-+-+-+
2 rows in set (0.00 sec)

5. SELECT
這個子句對SELECT子句中的元素進行處理，生成VT5表。
(5-J1)計算表達式 計算SELECT 子句中的表達式，生成VT5-J1
(5-J2)DISTINCT
尋找VT5-1中的重複列，並刪掉，生成VT5-J2
如果在查詢中指定了DISTINCT子句，則會創建一張內存臨時表（如果內存放不下，就需要存放在硬碟了）。這張臨時表的表結構和上一步產生的虛擬表VT5是一樣的，不同的是對進行DISTINCT操作的列增加了一個唯一索引，以此來除重複數據。

mysql> SELECT
    -> a.uid,
    -> count(b.oid) AS total
    -> FROM
    -> table1 AS a
    -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid
    -> WHERE
    -> a. NAME = 'mike'
    -> GROUP BY
    -> a.uid
    -> HAVING
    -> count(b.oid) < 2;
++--+
| uid | total |
++--+
| ccc |     1 |
| ddd |     0 |
++--+
2 rows in set (0.00 sec)

6.ORDER BY
從VT5-J2中的表中，根據ORDER BY 子句的條件對結果進行排序，生成VT6表。
注意：
唯一可使用SELECT中別名的地方；

mysql> SELECT
    -> a.uid,
    -> count(b.oid) AS total
    -> FROM
    -> table1 AS a
    -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid
    -> WHERE
    -> a. NAME = 'mike'
    -> GROUP BY
    -> a.uid
    -> HAVING
    -> count(b.oid) < 2
    -> ORDER BY
    -> total DESC;
++--+
| uid | total |
++--+
| ccc |     1 |
| ddd |     0 |
++--+
2 rows in set (0.00 sec)

7.LIMIT
LIMIT子句從上一步得到的VT6虛擬表中選出從指定位置開始的指定行數據。
注意：
offset和rows的正負帶來的影響；
當偏移量很大時效率是很低的，可以這麼做：
採用子查詢的方式優化，在子查詢裡先從索引獲取到最大id，然後倒序排，再取N行結果集
採用INNER JOIN優化，JOIN子句裡也優先從索引獲取ID列表，然後直接關聯查詢獲得最終結果

mysql> SELECT
    -> a.uid,
    -> count(b.oid) AS total
    -> FROM
    -> table1 AS a
    -> LEFT JOIN table2 AS b ON a.uid = b.uid
    -> WHERE
    -> a. NAME = 'mike'
    -> GROUP BY
    -> a.uid
    -> HAVING
    -> count(b.oid) < 2
    -> ORDER BY
    -> total DESC
    -> LIMIT 1;
++--+
| uid | total |
++--+
| ccc |     1 |
++--+
1 row in set (0.00 sec)

至此SQL的解析之旅就結束了，上圖總結一下：
img
參考書籍：
《MySQL性能調優與架構實踐》
《MySQL技術內幕：SQL編程》
尾聲：
嗯，到這裡這一次的深入了解之旅就差不多真的結束了，雖然也不是很深入，只是一些東西將其東拼西湊在一起而已，參考了一些以前看過的書籍，大師之筆果然不一樣。而且在這過程中也是get到了蠻多東西的，最重要的是更進一步意識到，計算機軟體世界的宏大呀~
來源：https://www.cnblogs.com/annsshadow/p/5037667.html
掃描關注  進入」PHP資料「
免費獲取進階
面試、文檔、視頻資源     

相關焦點

  • MySQL 架構總覽、從查詢執行流程到SQL 解析順序
    前言一直是想知道一條SQL語句是怎麼被執行的，它執行的順序是怎樣的，然後查看總結各方資料，就有了下面這一篇博文了。本文將從MySQL總體架構--->查詢執行流程--->語句執行順序來探討一下其中的知識。
  • 步步深入MySQL:架構-查詢執行流程-SQL解析順序!
    ，它執行的順序是怎樣的，然後查看總結各方資料，就有了下面這一篇博文了。本文將從MySQL總體架構--->查詢執行流程--->語句執行順序來探討一下其中的知識。二、MySQL架構總覽架構最好看圖，再配上必要的說明文字。下圖根據參考書籍中一圖為原本，再在其上添加上了自己的理解。
  • 步步深入MySQL:架構->查詢執行流程->SQL解析順序!
    ，它執行的順序是怎樣的，然後查看總結各方資料，就有了下面這一篇博文了。本文將從MySQL總體架構--->查詢執行流程--->語句執行順序來探討一下其中的知識。二、MySQL架構總覽架構最好看圖，再配上必要的說明文字。下圖根據參考書籍中一圖為原本，再在其上添加上了自己的理解。
  • Mysql中一條SQL查詢語句是如何執行的?
    1.前言在深入學習前我們應該跳出來,從整體的架構上來了解一下MySQL,這樣更有利於我們學習。2.查詢流程解析select * from table1 where ID=10;這條語句相信大家再熟悉不過了，下面我們就看看這一條語句在mysql中是怎麼執行的。第一步：一條sql語句要經過連接器，客戶端要和mysql建立連接。
  • MySQL-SQL語句執行流程
    MySQL 基礎架構分析MySQL 基本架構概覽連接器： 身份認證和權限相關(登錄 MySQL 的時候)。查詢緩存:  執行查詢語句的時候，會先查詢緩存（MySQL 8.0 版本後移除，因為這個功能不太實用）。
  • 「MySQL系列」分析Sql執行時間及查詢執行計劃(附資料庫和一千萬數據)
    接下來我將更新MySQL系列的文章，主要涉及MySQL的索引、調優、整體架構、引擎、分庫分表、擴容、索引的深入探究等等。感興趣的可以持續關注，不感興趣的略過。我們幹開發面試工作的時候，發現對資料庫的面試比重很大。說明對資料庫的知識掌握對我們程式設計師越來越重要了。
  • 一條查詢SQL在MySQL中是怎麼執行的
    ，在這個過程中逐步的揭開MySQL的面紗,對MySQL有個深入的理解。下邊通過一張圖來看一下SQL的執行流程，從中可以清楚的看到SQL語句在MySQL的各個功能模塊中執行的過程。從上圖來看，MySQL大體上可以分為Server層和存儲引擎層兩部分。
  • 深度剖析,原來大名鼎鼎的MySQL是這樣執行的!
    因為學習和了解mysql是至關重要的,那麼當我們在客戶端發起一個sql到出現詳細的查詢數據,這其中究竟經歷了什麼樣的過程？mysql服務端是如何處理請求的,又是如何執行sql語句的？對於某些更新壓力大的資料庫來說，查詢緩存的命中率會非常低,mysql為了維護緩存可能會出現一定的伸縮性的問題,目前在5.6的版本中已經默認關閉了，比較推薦的一種做法是將緩存放在客戶端，性能大概會提升5倍左右分析器的主要作用是將客戶端發過來的sql語句進行分析，這將包括預處理與解析過程，在這個階段會解析sql語句的語義，並進行關鍵詞和非關鍵詞進行提取、解析，並組成一個解析樹。
  • mysql的一個select需要經歷什麼查詢出結果
    語法分析報錯，詞法分析目的是識別出sql裡面的字符串代表什麼，語法分析的目的是識別出sql語句是否滿足mysql語法。對於表的操作權限驗證是在哪裡進行的？執行器驗證執行器的執行查詢語句的流程是什麼樣的？見下文為什麼不在優化器前做權限判斷？因為查詢的表不一定是sql語句，比如觸發器。此時優化器階段並不知道，只能在執行階段知道。
  • Select * from user的千層套路——一個sql是如何執行的
    就比如下面這條平平無奇的SQL語句：select * from user where id='1';    對於這麼一條簡單的不能再簡單的sql語句來說mysql伺服器做了哪些處理呢？下面我們將說一下關於mysql資料庫中對sql語句的處理邏輯MySql的基本架構     要想要了解sql語句的內部處理邏輯，我們需要先學習Mysql的基本架構圖，這樣才能醍醐灌頂，有更加整體的把握MySQL基本框架    從這個MySQL的架構圖中我們可以看到大體來說MySQL的架構分為兩層，分別是：Server層和存儲引擎層。
  • |sql|mysql|數據源|java|...
    JDBC 架構　　分為雙層架構和三層架構。　　雙層　　作用：此架構中，Java Applet 或應用直接訪問數據源。　　條件：要求 Driver 能與訪問的資料庫交互。　　機制：用戶命令傳給資料庫或其他數據源，隨之結果被返回。
  • 面對MySQL 查詢索引失效,程式設計師的六大優化技巧!
    要找到這個原因，我們需要首先了解下SQL在mysql中的執行過程，MYSQL 的整個架構可以分為 server 層 和存儲引擎層2個部分。Server 層 包括連接器，查詢緩存，分析器，優化器，執行器等模塊； 存儲引擎層 負責數據的存儲與提取。其架構模式是插件式的，支持InnoDB、MyISAM、Memory等多個存儲引擎，默認的是InnoDB。
  • 《MySQL》系列 - select 語句是怎麼執行的?
    select * from user where id = 1; 01 mysql 架構概覽要想理解這個問題就必須要知道 mysql 的內部架構。為此，我畫了張 mysql 的架構圖（你也可以理解為 sql 查詢語句的執行過程），如下所示：Mysql 架構圖首先 msql 分為 server 層和存儲引擎層兩個部分。server 層包括四個功能模塊，分別是：連接器、查詢緩存、優化器、執行器。
  • 一條SQL查詢語句是如何執行的?
    MySQL是典型的C/S架構（客戶端/伺服器架構），客戶端進程向服務端進程發送一段文本（MySQL指令），伺服器進程進行語句處理然後返回執行結果。問題來了。伺服器進程對客戶端發送的請求究竟做了什麼處理呢？本文以查詢請求為例，講解MySQL伺服器進程的處理流程。
  • MySQL慢查詢記錄原理和內容解析
    想閱讀八怪源碼文章歡迎訂閱本文建議橫屏觀看，效果更佳源碼版本：percona 5.7.14本文並不準備說明如何開啟記錄慢查詢，只是將一些重要的部分進行解析。二、慢查詢記錄的依據long_query_time：如果執行時間超過本參數設置記錄慢查詢。log_queries_not_using_indexes：如果語句未使用索引記錄慢查詢。log_slow_admin_statements：是否記錄管理語句。
  • MySQL如何完成一次查詢?
    mysql完成一次查詢過程是比較複雜的，在說明查詢過程前先介紹一下它的基礎概念和結構原理來幫助理解。下面從四個方面介紹，分別是mysql語句，mysql結構原理，mysql查詢過程，最後設置幾個有趣問題。
  • MySQL優化:定位慢查詢的兩種方法以及使用explain分析SQL
    一條SQL查詢語句在經過MySQL查詢優化器處理後會生成一個所謂的執行計劃，這個執行計劃展示了具體執行查詢的方式，比如多表連接的順序是什麼，對於每個表採用什麼訪問方法來具體執行查詢等等。本章的內容就是為了幫助大家看懂EXPLAIN語句的各個輸出項都是幹嘛使的，從而可以有針對性的提升我們查詢語句的性能。
  • Mysql專欄 - Linux底層交互和Raid存儲架構
    這一節比較特殊，講述的是和Linux有關的交互原理，因為多數的mysql都是部署在linux的伺服器上面，本節會簡單介紹一下linux是如何處理mysql的請求的，以及linux系統會帶來哪些問題概述介紹mysql的隨機讀寫和順序讀寫，那一部分是隨機讀寫，那一部分是順序讀寫linux的系統分層邏輯結構，以及和mysql系統的交互步驟。
  • mysql常用sql語句總結
    （嵌套查詢）三種子查詢 ：where子查詢、from子查詢、exists子查詢where子查詢先執行where後的語句，得到內層結果。sql的內連接、外連接和交叉連接查詢等。FROM 子句中的表或視圖可通過內聯接或完整外部聯接按任意順序指定；但是，用左或右向外聯接指定表或視圖時，表或視圖的順序很重要。有關使用左或右向外聯接排列表的更多信息，請參見使用外聯接。聯合查詢union查詢語法：sql1 union all sql2;UNION 操作符用於合併兩個或多個 SELECT 語句的結果集。
  • MySQL執行計劃及SQL優化策略
    ， 外層查詢則被標記為primarySUBQUERY：在select 或 where列表中包含了子查詢DERIVED：在from列表中包含的子查詢被標記為derived（衍生），mysql或遞歸執行這些子查詢，把結果放在零時表裡