MySQL學習（一）---->SQL查詢執行流程

来源:https://www.cnblogs.com/donleo123/archive/2023/03/15/17217850.html
-Advertisement-
Play Games

MySQL查詢執行流程 架構總覽 下麵就是MySQL 的邏輯架構，sql layer主要負責如下功能：許可權判斷、sql解析、執行計劃優化、query cache的處理等操作，這些操作都是在資料庫系統處理底層數據之前的工作； Storage Engine Layer主要負責底層數據存取的實現，由多種存 ...


MySQL查詢執行流程

架構總覽

下麵就是MySQL 的邏輯架構，sql layer主要負責如下功能：許可權判斷、sql解析、執行計劃優化、query cache的處理等操作，這些操作都是在資料庫系統處理底層數據之前的工作；

Storage Engine Layer主要負責底層數據存取的實現，由多種存儲引擎共同組成。

SQL Layer 中包含了多個子模塊，

結構圖如下

1、初始化模塊

顧名思議，初始化模塊就是在mysql Server 啟動的時候，對整個系統做各種各樣的初始化操作，比如各種buffer，cache 結構的初始化和記憶體空間的申請，各種系統變數的初始化設定，各種存儲引擎的初始化設置，等等。

2、核心API

核心API 模塊主要是為了提供一些需要非常高效的底層操作功能的優化實現，包括各種底層數據結構的實現，特殊演算法的實現，字元串處理，數字處理等，小文件I/O，格式化輸

出，以及最重要的記憶體管理部分。核心API 模塊的所有源代碼都集中在mysys 和strings文件夾下麵，有興趣的讀者可以研究研究。

3、網路交互模塊

底層網路交互模塊抽象出底層網路交互所使用的介面api，實現底層網路數據的接收與發送，以方便其他各個模塊調用，以及對這一部分的維護。所有源碼都在vio 文件夾下麵。

4、Client & Server 交互協議模塊

任何C/S 結構的軟體系統，都肯定會有自己獨有的信息交互協議，MySQL 也不例外。MySQL的Client & Server 交互協議模塊部分，實現了客戶端與MySQL 交互過程中的所有協議。當然這些協議都是建立在現有的OS 和網路協議之上的，如TCP/IP 以及Unix Socket。

5、用戶模塊

用戶模塊所實現的功能，主要包括用戶的登錄連接許可權控制和用戶的授權管理。他就像MySQL 的大門守衛一樣，決定是否給來訪者“開門”。

6、訪問控制模塊

造訪客人進門了就可以想幹嘛就幹嘛嗎？為了安全考慮，肯定不能如此隨意。這時候就需要訪問控制模塊實時監控客人的每一個動作，給不同的客人以不同的許可權。訪問控制模塊實現的功能就是根據用戶模塊中各用戶的授權信息，以及資料庫自身特有的各種約束，來控制用戶對數據的訪問。用戶模塊和訪問控制模塊兩者結合起來，組成了MySQL 整個資料庫系統的許可權安全管理的功能。

7、連接管理、連接線程和線程管理

連接管理模塊負責監聽對MySQL Server 的各種請求，接收連接請求，轉發所有連接請求到線程管理模塊。每一個連接上MySQL Server 的客戶端請求都會被分配（或創建）一個連接線程為其單獨服務。而連接線程的主要工作就是負責MySQL Server 與客戶端的通信，接受客戶端的命令請求，傳遞Server 端的結果信息等。線程管理模塊則負責管理維護這些連接線程。包括線程的創建，線程的cache 等。

8、Query 解析和轉發模塊

在MySQL 中我們習慣將所有Client 端發送給Server 端的命令都稱為query，在MySQLServer 裡面，連接線程接收到客戶端的一個Query 後，會直接將該query 傳遞給專門負責

將各種Query 進行分類然後轉發給各個對應的處理模塊，這個模塊就是query 解析和轉發模塊。其主要工作就是將query 語句進行語義和語法的分析，然後按照不同的操作類型進行分類，然後做出針對性的轉發。

9、Query Cache 模塊

Query Cache 模塊在MySQL 中是一個非常重要的模塊，他的主要功能是將客戶端提交給MySQL 的Select 類query 請求的返回結果集cache 到記憶體中，與該query 的一個hash 值做一個對應。該Query 所取數據的基表發生任何數據的變化之後，MySQL 會自動使該query 的Cache 失效。在讀寫比例非常高的應用系統中，Query Cache 對性能的提高是非常顯著的。當然它對記憶體的消耗也是非常大的。

10、Query 優化器模塊

Query 優化器，顧名思義，就是優化客戶端請求的query，根據客戶端請求的query 語句，和資料庫中的一些統計信息，在一系列演算法的基礎上進行分析，得出一個最優的策略，告訴後面的程式如何取得這個query 語句的結果。

11、表變更管理模塊

表變更管理模塊主要是負責完成一些DML 和DDL 的query，如：update，delte，insert，create table，alter table 等語句的處理。

12、表維護模塊

表的狀態檢查，錯誤修複，以及優化和分析等工作都是表維護模塊需要做的事情。

13、系統狀態管理模塊

系統狀態管理模塊負責在客戶端請求系統狀態的時候，將各種狀態數據返回給用戶，像DBA 常用的各種show status 命令，show variables 命令等，所得到的結果都是由這個模塊返回的。

14、表管理器

這個模塊從名字上看來很容易和上面的表變更和表維護模塊相混淆，但是其功能與變更及維護模塊卻完全不同。大家知道，每一個MySQL 的表都有一個表的定義文件，也就是*.frm文件。表管理器的工作主要就是維護這些文件，以及一個cache，該cache 中的主要內容是各個表的結構信息。此外它還維護table 級別的鎖管理。

15、日誌記錄模塊

日誌記錄模塊主要負責整個系統級別的邏輯層的日誌的記錄，包括error log，binarylog，slow query log 等。

16、複製模塊

複製模塊又可分為Master 模塊和Slave 模塊兩部分， Master 模塊主要負責在Replication 環境中讀取Master 端的binary 日誌，以及與Slave 端的I/O 線程交互等工作。

Slave 模塊比Master 模塊所要做的事情稍多一些，在系統中主要體現在兩個線程上面。一個是負責從Master 請求和接受binary 日誌，並寫入本地relay log 中的I/O 線程。另外一個是負責從relay log 中讀取相關日誌事件，然後解析成可以在Slave 端正確執行並得到和Master 端完全相同的結果的命令並再交給Slave 執行的SQL 線程。

17、存儲引擎介面模塊

存儲引擎介面模塊可以說是MySQL 資料庫中最有特色的一點了。目前各種資料庫產品中，基本上只有MySQL 可以實現其底層數據存儲引擎的插件式管理。這個模塊實際上只是一個抽象類，但正是因為它成功地將各種數據處理高度抽象化，才成就了今天MySQL 可插拔存儲引擎的特色。

查詢執行流程

查詢執行的流程：

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語句的前世今生。

首先看一下示例語句

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 >

然而它的執行順序是這樣的

FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT 
DISTINCT <select_list>
ORDER BY <order_by_condition>
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 |
+-----+------+-----+------+
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 |
+-----+------+-----+------+
rows in set (0.00 sec)

(1-J3)添加外部列

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

如果FROM子句包含兩個以上的表，則對上一個聯接生成的結果表和下一個表重覆執行步驟1到步驟3，直到處理完所有的表為止。

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 |
+-----+------+------+------+
rows in set (0.00 sec)

下麵從網上找到一張很形象的關於‘SQL JOINS'的解釋圖

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 |
+-----+------+------+------+
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 |
+-----+------+------+------+
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 |
+-----+------+------+------+
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
 ->
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • gRPC之.Net6中的客戶端和服務端共用proto協議文件
    代碼編譯器是將一段源代碼（C#或VisualBasic）編譯成程式集，它的工作方式與 Emit 不一樣。從 .net standard 開始，代碼編譯器就採用了 Roslyn 來編譯源代碼，前幾篇文章里提到的 SourceGenerator 也正是基於此。 ...
  • Linux LVM磁碟分區管理
    很多時候，在linux我們源碼編譯庫代碼時候會自定義安裝路徑，這使得man查詢的時候無法找到庫文檔，預設的man搜索路徑可以使用下麵命令查看： $ man -w /usr/local/share/man:/usr/share/man 我們可以通過修改man的命令搜索配置文件/etc/man_db.c ...
  • Linux進程通信 | 管道與FIFO
    Linux進程間通信通常使用的方式有很多種，其中比較常用的包括管道（pipe）和 FIFO（命名管道）。本文將介紹這兩種通信方式的基本概念，並用C語言編寫示例代碼，來說明如何在兩個進程之間使用這些IPC機制進行通信。 管道（pipe） 管道是一種半雙工的通信方式，用於父進程和子進程之間的通信。在 L ...
  • Jenkins上傳文件報錯Support for FileParameters in the input step is disabled
    【報錯信息】 ERROR: Support for FileParameters in the input step is disabled and will be removed in a future release. Details on how to migrate your pipelin ...
  • 使用iptabels將主機的所有流量轉發至其他IP地址
    iptables是一個Linux內核中的包過濾工具，可以用來過濾、轉發、修改、控制網路流量等。如果想要將主機的所有流量轉發至其他機器，可以使用iptables進行配置。 以下是具體步驟： 1. 首先需要在其他機器上開啟轉發功能，可以使用以下命令開啟： echo 1 > /proc/sys/net/i ...
  • mysql 隱式類型轉換規則
    規則： 1、兩個參數至少有一個是 NULL 時，比較的結果也是 NULL，例外是使用 <=> 對兩個 NULL 做比較時會返回 1，這兩種情況都不需要做類型轉換2、兩個參數都是字元串，會按照字元串來比較，不做類型轉換3、兩個參數都是整數，按照整數來比較，不做類型轉換4、十六進位的值和非數字做比較時， ...
  • ChunJun 順利晉級“2022 年中國開源創新大賽”決賽，並榮獲“優秀開源項目/社區”獎項
    日前，“2022年中國開源創新大賽”初賽晉級名單公佈，易用、穩定、高效的批流統一的數據集成框架 ChunJun 順利晉級決賽，並榮獲“2022年中國開源創新大賽優秀開源項目/社區”獎項。 感謝組委會專家的認可，ChunJun也將繼續秉承技術初心，砥礪前行，不遺餘力地助力國內開源發展，為更多的開發者傳 ...
  • 分散式資料庫架構路線大揭秘
    摘要：這些年大家都在談分散式資料庫，各大企業也紛紛開始做資料庫的分散式改造。那麼所謂的分散式資料庫是什麼？採用什麼架構，優勢在哪？為什麼越來越多企業選擇它？我們不妨一起來深入瞭解下。 本文分享自華為雲社區《GaussDB分散式架構大揭秘》，作者：華為雲資料庫首席架構師 馮柯。 這些年大家都在談分散式 ...
一周排行
    -Advertisement-
    Play Games
  • Dapr Outbox 執行流程
    Dapr Outbox 是1.12中的功能。 本文只介紹Dapr Outbox 執行流程，Dapr Outbox基本用法請閱讀官方文檔 。本文中appID=order-processor，topic=orders 本文前提知識：熟悉Dapr狀態管理、Dapr發佈訂閱和Outbox 模式。 Outbo ...
  • Advanced .Net Debugging 7：托管堆與垃圾收集
    引言 在前幾章我們深度講解了單元測試和集成測試的基礎知識，這一章我們來講解一下代碼覆蓋率,代碼覆蓋率是單元測試運行的度量值,覆蓋率通常以百分比表示，用於衡量代碼被測試覆蓋的程度，幫助開發人員評估測試用例的質量和代碼的健壯性。常見的覆蓋率包括語句覆蓋率（Line Coverage）、分支覆蓋率（Bra ...
  • C#S7.NET實現西門子PLCDB塊數據採集的完整步驟
    前言 本文介紹瞭如何使用S7.NET庫實現對西門子PLC DB塊數據的讀寫，記錄了使用電腦模擬，模擬PLC，自至完成測試的詳細流程，並重點介紹了在這個過程中的易錯點，供參考。 用到的軟體： 1.Windows環境下鏈路層網路訪問的行業標準工具（WinPcap_4_1_3.exe）下載鏈接：http ...
  • WPF控制項：密碼框綁定MVVM
    從依賴倒置原則（Dependency Inversion Principle, DIP）到控制反轉（Inversion of Control, IoC）再到依賴註入（Dependency Injection, DI）的演進過程，我們可以理解為一種逐步抽象和解耦的設計思想。這種思想在C#等面向對象的編 ...
  • Python中的私有屬性與私有方法
    關於Python中的私有屬性和私有方法 Python對於類的成員沒有嚴格的訪問控制限制，這與其他面相對對象語言有區別。關於私有屬性和私有方法，有如下要點： 1、通常我們約定，兩個下劃線開頭的屬性是私有的（private）。其他為公共的（public）； 2、類內部可以訪問私有屬性（方法）； 3、類外 ...
  • C++ 訪問說明符詳解：封裝數據，控制訪問，提升安全性
    C++ 訪問說明符 訪問說明符是 C++ 中控制類成員（屬性和方法）可訪問性的關鍵字。它們用於封裝類數據並保護其免受意外修改或濫用。 三種訪問說明符： public：允許從類外部的任何地方訪問成員。 private：僅允許在類內部訪問成員。 protected：允許在類內部及其派生類中訪問成員。 示 ...
  • static_cast與dynamic_cast到底是什麼？
    寫這個隨筆說一下C++的static_cast和dynamic_cast用在子類與父類的指針轉換時的一些事宜。首先，【static_cast，dynamic_cast】【父類指針，子類指針】，兩兩一組，共有4種組合：用 static_cast 父類轉子類、用 static_cast 子類轉父類、使用 ...
  • C語言數據結構：雙向鏈表的增刪操作
    /******************************************************************************************************** * * * 設計雙向鏈表的介面 * * * * Copyright (c) 2023-2 ...
  • @ComponentScan註解的實現，Spring掃描包的過程
    相信接觸過spring做開發的小伙伴們一定使用過@ComponentScan註解 @ComponentScan("com.wangm.lifecycle") public class AppConfig { } @ComponentScan指定basePackage，將包下的類按照一定規則註冊成Be ...
  • opensips開啟python支持
    操作系統 ：CentOS 7.6_x64 opensips版本： 2.4.9 python版本：2.7.5 python作為腳本語言，使用起來很方便，查了下opensips的文檔，支持使用python腳本寫邏輯代碼。今天整理下CentOS7環境下opensips2.4.9的python模塊筆記及使用 ...
所有分類