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

至此SQL的決議之旅就結束了，上圖總結一下：

標籤：MySQL

上一篇：深入理解 Taier：MR on Yarn 的實作原理

下一篇：mysql 隱式型別轉換規則