步步深入MySQL：架構->查詢執行流程->SQL解析順序
一、前言
一直是想知道一條SQL語句是怎么被執行的，它執行的順序是怎樣的，然后查看總結各方資料，就有了下面這一篇博文了。
本文將從MySQL總體架構--->查詢執行流程--->語句執行順序來探討一下其中的知識。
二、MySQL架構總覽
架構***看圖，再配上必要的說明文字。
下圖根據參考書籍中一圖為原本，再在其上添加上了自己的理解。
從上圖中我們可以看到，整個架構分為兩層，上層是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.1、Query請求完成后，將結果集返回給‘連接進/線程模塊’;
3.2、返回的也可以是相應的狀態標識，如成功或失敗等;
3.3、‘連接進/線程模塊’進行后續的清理工作，并繼續等待請求或斷開與客戶端的連接;
4、一圖小總結
四、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、準備工作
1.1、創建測試數據庫
- create database testQuery
1.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;
1.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);
1.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解析之旅吧!
2、FROM
當涉及多個表的時候，左邊表的輸出會作為右邊表的輸入，之后會生成一個虛擬表VT1。
2.1、(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)
2.2、(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)
2.3、(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'的解釋圖，如若侵犯了你的權益，請勞煩告知刪除，謝謝。
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的解析之旅就結束了，上圖總結一下：
