步步深入：MySQL 架構總覽->查詢執(zhí)行流程->SQL 解析順序
你知道的越多，不知道的就越多，業(yè)余的像一棵小草！
你來，我們一起精進！你不來，我和你的競爭對手一起精進！
編輯：業(yè)余草
cnblogs.com/annsshadow/p/5355090.html
推薦：https://www.xttblog.com/?p=5271
一直是想知道一條 SQL 語句是怎么被執(zhí)行的，它執(zhí)行的順序是怎樣的，然后查看總結各方資料，就有了下面這一篇博文了。
本文將從 MySQL 總體架構 -> 查詢執(zhí)行流程 -> 語句執(zhí)行順序來探討一下其中的知識。
MySQL 架構總覽
架構最好看圖，再配上必要的說明文字。
下圖根據(jù)參考書籍中一圖為原本，再在其上添加上了自己的理解。
從上圖中我們可以看到，整個架構分為兩層，上層是 MySQLD 的被稱為的SQL Layer，下層是各種各樣對上提供接口的存儲引擎，被稱為Storage Engine Layer。其它各個模塊和組件，從名字上就可以簡單了解到它們的作用，這里就不再累述了。
查詢執(zhí)行流程
下面再向前走一些，容我根據(jù)自己的認識說一下查詢執(zhí)行的流程是怎樣的：
連接
客戶端發(fā)起一條 Query 請求，監(jiān)聽客戶端的
連接管理模塊接收請求；將請求轉發(fā)到
連接進/線程模塊；調用
用戶模塊來進行授權檢查；通過檢查后，
連接進/線程模塊從線程連接池中取出空閑的被緩存的連接線程和客戶端請求對接，如果失敗則創(chuàng)建一個新的連接請求。
處理
先查詢緩存，檢查 Query 語句是否完全匹配，接著再檢查是否具有權限，都成功則直接取數(shù)據(jù)返回；
上一步有失敗則轉交給‘命令解析器’，經(jīng)過詞法分析，語法分析后生成解析樹；
接下來是預處理階段，處理解析器無法解決的語義，檢查權限等，生成新的解析樹；
再轉交給對應的模塊處理；
如果是 SELECT 查詢還會經(jīng)由‘查詢優(yōu)化器’做大量的優(yōu)化，生成執(zhí)行計劃；
模塊收到請求后，通過
訪問控制模塊檢查所連接的用戶是否有訪問目標表和目標字段的權限；有則調用
表管理模塊，先是查看 table cache 中是否存在，有則直接對應的表和獲取鎖，否則重新打開表文件；根據(jù)表的 meta 數(shù)據(jù)，獲取表的存儲引擎類型等信息，通過接口調用對應的存儲引擎處理；
上述過程中產(chǎn)生數(shù)據(jù)變化的時候，若打開日志功能，則會記錄到相應二進制日志文件中。
結果
Query 請求完成后，將結果集返回給
連接進/線程模塊；返回的也可以是相應的狀態(tài)標識，如成功或失敗等；
連接進/線程模塊進行后續(xù)的清理工作，并繼續(xù)等待請求或斷開與客戶端的連接。
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 >
然而它的執(zhí)行順序是這樣的：
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>
雖然自己沒想到是這樣的，不過一看還是很自然和諧的，從哪里獲取，不斷的過濾條件，要選擇一樣或不一樣的，排好序，那才知道要取前幾條呢。
既然如此了，那就讓我們一步步來看看其中的細節(jié)吧。
準備工作
創(chuàng)建測試數(shù)據(jù)庫
create database testQuery
創(chuàng)建測試表
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;
插入數(shù)據(jù)
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);
最后想要的結果
SELECT a.uid, count(b.oid) AS total
FROM table1 AS aLEFT
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;
下面我們現(xiàn)在開始 SQL 解析之旅吧！
FROM
當涉及多個表的時候，左邊表的輸出會作為右邊表的輸入，之后會生成一個虛擬表 VT1。
(1-J1)笛卡爾積
計算兩個相關聯(lián)表的笛卡爾積 (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)
下面從網(wǎng)上找到一張很形象的關于SQL JOINS的解釋圖，如若侵犯了你的權益，請勞煩告知刪除，謝謝。
WHERE
對 VT1 過程中生成的臨時表進行過濾，滿足 WHERE 子句的列被插入到 VT2 表中。
注意：此時因為分組，不能使用聚合運算；也不能使用 SELECT 中創(chuàng)建的別名；
與 ON 的區(qū)別
如果有外部列，ON 針對過濾的是關聯(lián)表，主表（保留表）會返回所有的列； 如果沒有添加外部列，兩者的效果是一樣的。
「應用」：
對主表的過濾應該放在 WHERE； 對于關聯(lián)表，先條件查詢后連接則用 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)
GROUP BY
這個子句會把 VT2 中生成的表按照 GROUP BY 中的列進行分組，生成 VT3 表。
注意：其后處理過程的語句，如 SELECT、HAVING，所用到的列必須包含在 GROUP BY 中，對于沒有出現(xiàn)的，得用聚合函數(shù)；
「原因」：GROUP BY 改變了對表的引用，將其轉換為新的引用方式，能夠對其進行下一級邏輯操作的列會減少。
「我的理解是」：根據(jù)分組字段，將具有相同分組字段的記錄歸并成一條記錄，因為每一個分組只能返回一條記錄，除非是被過濾掉了，而不在分組字段里面的字段可能會有多個值，多個值是無法放進一條記錄的，所以必須通過聚合函數(shù)將這些具有多值的列轉換成單值；
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)
HAVING
這個子句對 VT3 表中的不同的組進行過濾，只作用于分組后的數(shù)據(jù)，滿足 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)
SELECT
這個子句對 SELECT 子句中的元素進行處理，生成 VT5 表。
(5-J1) 計算表達式 計算 SELECT 子句中的表達式，生成 VT5-J1。
(5-J2) DISTINCT
尋找 VT5-1 中的重復列，并刪掉，生成 VT5-J2。
如果在查詢中指定了 DISTINCT 子句，則會創(chuàng)建一張內存臨時表（如果內存放不下，就需要存放在硬盤了）。這張臨時表的表結構和上一步產(chǎn)生的虛擬表 VT5 是一樣的，不同的是對進行 DISTINCT 操作的列增加了一個唯一索引，以此來除重復數(shù)據(jù)。
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)
ORDER BY
從 VT5-J2 中的表中，根據(jù) 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)
LIMIT
LIMIT 子句從上一步得到的 VT6 虛擬表中選出從指定位置開始的指定行數(shù)據(jù)。
注意：
offset 和 rows 的正負帶來的影響； 當偏移量很大時效率是很低的，可以這么做； 采用子查詢的方式優(yōu)化，在子查詢里先從索引獲取到最大 id，然后倒序排，再取 N 行結果集； 采用 INNER JOIN 優(yōu)化，JOIN 子句里也優(yōu)先從索引獲取 ID 列表，然后直接關聯(lián)查詢獲得最終結果。
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 的解析之旅就結束了，上圖總結一下：
「參考書籍」
《MySQL性能調優(yōu)與架構實踐》 《MySQL技術內幕：SQL編程》
「尾聲」
嗯，到這里這一次的深入了解之旅就差不多真的結束了，雖然也不是很深入，只是一些東西將其東拼西湊在一起而已，參考了一些以前看過的書籍，大師之筆果然不一樣。而且在這過程中也是 get 到了蠻多東西的。