前言：
一直是想知道一條SQL語句是怎么被執(zhí)行的，它執(zhí)行的順序是怎樣的，然后查看總結(jié)各方資料，就有了下面這一篇博文了。
本文將從MySQL總體架構(gòu)--->查詢執(zhí)行流程--->語句執(zhí)行順序來探討一下其中的知識。
一、MySQL架構(gòu)總覽：
架構(gòu)最好看圖，再配上必要的說明文字。
下圖根據(jù)參考書籍中一圖為原本，再在其上添加上了自己的理解。
從上圖中我們可以看到，整個架構(gòu)分為兩層，上層是MySQLD的被稱為的‘SQL Layer'，下層是各種各樣對上提供接口的存儲引擎，被稱為‘Storage Engine Layer'。其它各個模塊和組件，從名字上就可以簡單了解到它們的作用，這里就不再累述了。
二、查詢執(zhí)行流程
下面再向前走一些，容我根據(jù)自己的認(rèn)識說一下查詢執(zhí)行的流程是怎樣的：
1.連接
1.1客戶端發(fā)起一條Query請求，監(jiān)聽客戶端的‘連接管理模塊'接收請求
1.2將請求轉(zhuǎn)發(fā)到‘連接進/線程模塊'
1.3調(diào)用‘用戶模塊'來進行授權(quán)檢查
1.4通過檢查后，‘連接進/線程模塊'從‘線程連接池'中取出空閑的被緩存的連接線程和客戶端請求對接，如果失敗則創(chuàng)建一個新的連接請求
2.處理
2.1先查詢緩存，檢查Query語句是否完全匹配，接著再檢查是否具有權(quán)限，都成功則直接取數(shù)據(jù)返回
2.2上一步有失敗則轉(zhuǎn)交給‘命令解析器'，經(jīng)過詞法分析，語法分析后生成解析樹
2.3接下來是預(yù)處理階段，處理解析器無法解決的語義，檢查權(quán)限等，生成新的解析樹
2.4再轉(zhuǎn)交給對應(yīng)的模塊處理
2.5如果是SELECT查詢還會經(jīng)由‘查詢優(yōu)化器'做大量的優(yōu)化，生成執(zhí)行計劃
2.6模塊收到請求后，通過‘訪問控制模塊'檢查所連接的用戶是否有訪問目標(biāo)表和目標(biāo)字段的權(quán)限
2.7有則調(diào)用‘表管理模塊'，先是查看table cache中是否存在，有則直接對應(yīng)的表和獲取鎖，否則重新打開表文件
2.8根據(jù)表的meta數(shù)據(jù)，獲取表的存儲引擎類型等信息，通過接口調(diào)用對應(yīng)的存儲引擎處理
2.9上述過程中產(chǎn)生數(shù)據(jù)變化的時候，若打開日志功能，則會記錄到相應(yīng)二進制日志文件中
3.結(jié)果
3.1Query請求完成后，將結(jié)果集返回給‘連接進/線程模塊'
3.2返回的也可以是相應(yīng)的狀態(tài)標(biāo)識，如成功或失敗等
3.3‘連接進/線程模塊'進行后續(xù)的清理工作，并繼續(xù)等待請求或斷開與客戶端的連接
一圖小總結(jié)
三、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é)吧。
準(zhǔn)備工作
1.創(chuàng)建測試數(shù)據(jù)庫
create database testQuery
2.創(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;
3.插入數(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);4.最后想要的結(jié)果
SELECT a.uid, count(b.oid) AS totalFROM table1 AS aLEFT JOIN table2 AS b ON a.uid = b.uidWHERE a. NAME = 'mike'GROUP BY a.uidHAVING count(b.oid) < 2ORDER BY total DESCLIMIT 1;
！現(xiàn)在開始SQL解析之旅吧！
1. FROM
當(dāng)涉及多個表的時候，左邊表的輸出會作為右邊表的輸入，之后會生成一個虛擬表VT1。
(1-J1)笛卡爾積
計算兩個相關(guān)聯(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 |+-----+------+-----+------+rows in set (0.00 sec)
(1-J2)ON過濾
基于虛擬表VT1-J1這一個虛擬表進行過濾，過濾出所有滿足ON 謂詞條件的列，生成虛擬表VT1-J2。
注意：這里因為語法限制，使用了'WHERE'代替，從中讀者也可以感受到兩者之間微妙的關(guān)系；
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。
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)
下面從網(wǎng)上找到一張很形象的關(guān)于‘SQL JOINS'的解釋圖，如若侵犯了你的權(quán)益，請勞煩告知刪除，謝謝。
2. WHERE
對VT1過程中生成的臨時表進行過濾，滿足WHERE子句的列被插入到VT2表中。
注意：
此時因為分組，不能使用聚合運算；也不能使用SELECT中創(chuàng)建的別名；
與ON的區(qū)別：
如果有外部列，ON針對過濾的是關(guān)聯(lián)表，主表（保留表）會返回所有的列；
如果沒有添加外部列，兩者的效果是一樣的；
應(yīng)用：
對主表的過濾應(yīng)該放在WHERE；
對于關(guān)聯(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 |+-----+------+------+------+rows in set (0.00 sec)
3. GROUP BY
這個子句會把VT2中生成的表按照GROUP BY中的列進行分組。生成VT3表。
注意：
其后處理過程的語句，如SELECT,HAVING，所用到的列必須包含在GROUP BY中，對于沒有出現(xiàn)的，得用聚合函數(shù)；
原因：
GROUP BY改變了對表的引用，將其轉(zhuǎn)換為新的引用方式，能夠?qū)ζ溥M行下一級邏輯操作的列會減少；
我的理解是：
根據(jù)分組字段，將具有相同分組字段的記錄歸并成一條記錄，因為每一個分組只能返回一條記錄，除非是被過濾掉了，而不在分組字段里面的字段可能會有多個值，多個值是無法放進一條記錄的，所以必須通過聚合函數(shù)將這些具有多值的列轉(zhuǎn)換成單值；
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表中的不同的組進行過濾，只作用于分組后的數(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 |+-----+------+------+------+rows in set (0.00 sec)
5. SELECT
這個子句對SELECT子句中的元素進行處理，生成VT5表。
(5-J1)計算表達式 計算SELECT 子句中的表達式，生成VT5-J1
(5-J2)DISTINCT
尋找VT5-1中的重復(fù)列，并刪掉，生成VT5-J2
如果在查詢中指定了DISTINCT子句，則會創(chuàng)建一張內(nèi)存臨時表（如果內(nèi)存放不下，就需要存放在硬盤了）。這張臨時表的表結(jié)構(gòu)和上一步產(chǎn)生的虛擬表VT5是一樣的，不同的是對進行DISTINCT操作的列增加了一個唯一索引，以此來除重復(fù)數(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 |+-----+-------+rows in set (0.00 sec)
6.ORDER BY
從VT5-J2中的表中，根據(jù)ORDER BY 子句的條件對結(jié)果進行排序，生成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虛擬表中選出從指定位置開始的指定行數(shù)據(jù)。
注意：
offset和rows的正負(fù)帶來的影響；
當(dāng)偏移量很大時效率是很低的，可以這么做：
采用子查詢的方式優(yōu)化，在子查詢里先從索引獲取到最大id，然后倒序排，再取N行結(jié)果集
采用INNER JOIN優(yōu)化，JOIN子句里也優(yōu)先從索引獲取ID列表，然后直接關(guān)聯(lián)查詢獲得最終結(jié)果
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的解析之旅就結(jié)束了，上圖總結(jié)一下：
參考書籍：
尾聲：
嗯，到這里這一次的深入了解之旅就差不多真的結(jié)束了，雖然也不是很深入，只是一些東西將其東拼西湊在一起而已，參考了一些以前看過的書籍，大師之筆果然不一樣。而且在這過程中也是get到了蠻多東西的，最重要的是更進一步意識到，計算機軟件世界的宏大呀~
另由于本人才疏學(xué)淺，其中難免存在紕漏錯誤之處，若發(fā)現(xiàn)勞煩告知修改，感謝~
總結(jié)
以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，如果有疑問大家可以留言交流，謝謝大家對VeVb武林網(wǎng)的支持。
新聞熱點
疑難解答
圖片精選
