一條SQL語(yǔ)句是怎么被執(zhí)行的

一直是想知道一條SQL語(yǔ)句是怎么被執(zhí)行的，它執(zhí)行的順序是怎樣的，然后查看總結(jié)各方資料，就有了下面這一篇博文了。

本文將從MySQL總體架構(gòu)---》查詢(xún)執(zhí)行流程---》語(yǔ)句執(zhí)行順序來(lái)探討一下其中的知識(shí)。

一、MySQL架構(gòu)總覽：

架構(gòu)最好看圖，再配上必要的說(shuō)明文字。

下圖根據(jù)參考書(shū)籍中一圖為原本，再在其上添加上了自己的理解。

從上圖中我們可以看到，整個(gè)架構(gòu)分為兩層，上層是MySQLD的被稱(chēng)為的‘SQL Layer’，下層是各種各樣對(duì)上提供接口的存儲(chǔ)引擎，被稱(chēng)為‘Storage Engine Layer’。其它各個(gè)模塊和組件，從名字上就可以簡(jiǎn)單了解到它們的作用，這里就不再累述了。

二、查詢(xún)執(zhí)行流程

下面再向前走一些，容我根據(jù)自己的認(rèn)識(shí)說(shuō)一下查詢(xún)執(zhí)行的流程是怎樣的：

1.連接

1.1客戶(hù)端發(fā)起一條Query請(qǐng)求，監(jiān)聽(tīng)客戶(hù)端的‘連接管理模塊’接收請(qǐng)求

1.2將請(qǐng)求轉(zhuǎn)發(fā)到‘連接進(jìn)/線程模塊’

1.3調(diào)用‘用戶(hù)模塊’來(lái)進(jìn)行授權(quán)檢查

1.4通過(guò)檢查后，‘連接進(jìn)/線程模塊’從‘線程連接池’中取出空閑的被緩存的連接線程和客戶(hù)端請(qǐng)求對(duì)接，如果失敗則創(chuàng)建一個(gè)新的連接請(qǐng)求

2.處理

2.1先查詢(xún)緩存，檢查Query語(yǔ)句是否完全匹配，接著再檢查是否具有權(quán)限，都成功則直接取數(shù)據(jù)返回

2.2上一步有失敗則轉(zhuǎn)交給‘命令解析器’，經(jīng)過(guò)詞法分析，語(yǔ)法分析后生成解析樹(shù)

2.3接下來(lái)是預(yù)處理階段，處理解析器無(wú)法解決的語(yǔ)義，檢查權(quán)限等，生成新的解析樹(shù)

2.4再轉(zhuǎn)交給對(duì)應(yīng)的模塊處理

2.5如果是SELECT查詢(xún)還會(huì)經(jīng)由‘查詢(xún)優(yōu)化器’做大量的優(yōu)化，生成執(zhí)行計(jì)劃

2.6模塊收到請(qǐng)求后，通過(guò)‘訪問(wèn)控制模塊’檢查所連接的用戶(hù)是否有訪問(wèn)目標(biāo)表和目標(biāo)字段的權(quán)限

2.7有則調(diào)用‘表管理模塊’，先是查看table cache中是否存在，有則直接對(duì)應(yīng)的表和獲取鎖，否則重新打開(kāi)表文件

2.8根據(jù)表的meta數(shù)據(jù)，獲取表的存儲(chǔ)引擎類(lèi)型等信息，通過(guò)接口調(diào)用對(duì)應(yīng)的存儲(chǔ)引擎處理

2.9上述過(guò)程中產(chǎn)生數(shù)據(jù)變化的時(shí)候，若打開(kāi)日志功能，則會(huì)記錄到相應(yīng)二進(jìn)制日志文件中

3.結(jié)果

3.1Query請(qǐng)求完成后，將結(jié)果集返回給‘連接進(jìn)/線程模塊’

3.2返回的也可以是相應(yīng)的狀態(tài)標(biāo)識(shí)，如成功或失敗等

3.3‘連接進(jìn)/線程模塊’進(jìn)行后續(xù)的清理工作，并繼續(xù)等待請(qǐng)求或斷開(kāi)與客戶(hù)端的連接

三、SQL解析順序

接下來(lái)再走一步，讓我們看看一條SQL語(yǔ)句的前世今生。

首先看一下示例語(yǔ)句

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í)行順序是這樣的

1 FROM 《left_table》

2 ON 《join_condition》

3 《join_type》 JOIN 《right_table》

4 WHERE 《where_condition》

5 GROUP BY 《group_by_list》

6 HAVING 《having_condition》

7 SELECT

8 DISTINCT 《select_list》

9 ORDER BY 《order_by_condition》

10 LIMIT 《limit_number》

雖然自己沒(méi)想到是這樣的，不過(guò)一看還是很自然和諧的，從哪里獲取，不斷的過(guò)濾條件，要選擇一樣或不一樣的，排好序，那才知道要取前幾條呢。

既然如此了，那就讓我們一步步來(lái)看看其中的細(xì)節(jié)吧。

準(zhǔn)備工作

1.創(chuàng)建測(cè)試數(shù)據(jù)庫(kù)

create database testQuery

2.創(chuàng)建測(cè)試表

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（‘a(chǎn)aa’，‘mike’），（‘bbb’，‘jack’），（‘ccc’，‘mike’），（‘ddd’，‘mike’）;

INSERT INTO table2（uid） VALUES（‘a(chǎn)aa’），（‘a(chǎn)aa’），（‘bbb’），（‘bbb’），（‘bbb’），（‘ccc’），（NULL）;

4.最后想要的結(jié)果

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;

！現(xiàn)在開(kāi)始SQL解析之旅吧！

1. FROM

當(dāng)涉及多個(gè)表的時(shí)候，左邊表的輸出會(huì)作為右邊表的輸入，之后會(huì)生成一個(gè)虛擬表VT1。

（1-J1）笛卡爾積

計(jì)算兩個(gè)相關(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 |

+-----+------+-----+------+

28 rows in set （0.00 sec）

（1-J2）ON過(guò)濾

基于虛擬表VT1-J1這一個(gè)虛擬表進(jìn)行過(guò)濾，過(guò)濾出所有滿足ON 謂詞條件的列，生成虛擬表VT1-J2。

注意：這里因?yàn)檎Z(yǔ)法限制，使用了‘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 |

+-----+------+-----+------+

6 rows in set （0.00 sec）

（1-J3）添加外部列

如果使用了外連接（LEFT，RIGHT，F(xiàn)ULL），主表（保留表）中的不符合ON條件的列也會(huì)被加入到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)上找到一張很形象的關(guān)于‘SQL JOINS‘的解釋圖，如若侵犯了你的權(quán)益，請(qǐng)勞煩告知?jiǎng)h除，謝謝。

2. WHERE

對(duì)VT1過(guò)程中生成的臨時(shí)表進(jìn)行過(guò)濾，滿足WHERE子句的列被插入到VT2表中。

注意：

此時(shí)因?yàn)榉纸M，不能使用聚合運(yùn)算；也不能使用SELECT中創(chuàng)建的別名；

與ON的區(qū)別：

如果有外部列，ON針對(duì)過(guò)濾的是關(guān)聯(lián)表，主表（保留表）會(huì)返回所有的列；

如果沒(méi)有添加外部列，兩者的效果是一樣的；

應(yīng)用：

對(duì)主表的過(guò)濾應(yīng)該放在WHERE；

對(duì)于關(guān)聯(lián)表，先條件查詢(xún)后連接則用ON，先連接后條件查詢(xún)則用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

這個(gè)子句會(huì)把VT2中生成的表按照GROUP BY中的列進(jìn)行分組。生成VT3表。

注意：

其后處理過(guò)程的語(yǔ)句，如SELECT，HAVING，所用到的列必須包含在GROUP BY中，對(duì)于沒(méi)有出現(xiàn)的，得用聚合函數(shù)；

原因：

GROUP BY改變了對(duì)表的引用，將其轉(zhuǎn)換為新的引用方式，能夠?qū)ζ溥M(jìn)行下一級(jí)邏輯操作的列會(huì)減少；

我的理解是：

根據(jù)分組字段，將具有相同分組字段的記錄歸并成一條記錄，因?yàn)槊恳粋€(gè)分組只能返回一條記錄，除非是被過(guò)濾掉了，而不在分組字段里面的字段可能會(huì)有多個(gè)值，多個(gè)值是無(wú)法放進(jìn)一條記錄的，所以必須通過(guò)聚合函數(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 |

+-----+------+------+------+

3 rows in set （0.00 sec）

4. HAVING

這個(gè)子句對(duì)VT3表中的不同的組進(jìn)行過(guò)濾，只作用于分組后的數(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）

5. SELECT

這個(gè)子句對(duì)SELECT子句中的元素進(jìn)行處理，生成VT5表。

（5-J1）計(jì)算表達(dá)式 計(jì)算SELECT 子句中的表達(dá)式，生成VT5-J1

（5-J2）DISTINCT

尋找VT5-1中的重復(fù)列，并刪掉，生成VT5-J2

如果在查詢(xún)中指定了DISTINCT子句，則會(huì)創(chuàng)建一張內(nèi)存臨時(shí)表（如果內(nèi)存放不下，就需要存放在硬盤(pán)了）。這張臨時(shí)表的表結(jié)構(gòu)和上一步產(chǎn)生的虛擬表VT5是一樣的，不同的是對(duì)進(jìn)行DISTINCT操作的列增加了一個(gè)唯一索引，以此來(lái)除重復(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 |

+-----+-------+

2 rows in set （0.00 sec）

6.ORDER BY

從VT5-J2中的表中，根據(jù)ORDER BY 子句的條件對(duì)結(jié)果進(jìn)行排序，生成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虛擬表中選出從指定位置開(kāi)始的指定行數(shù)據(jù)。

注意：

offset和rows的正負(fù)帶來(lái)的影響；

當(dāng)偏移量很大時(shí)效率是很低的，可以這么做：

采用子查詢(xún)的方式優(yōu)化，在子查詢(xún)里先從索引獲取到最大id，然后倒序排，再取N行結(jié)果集

采用INNER JOIN優(yōu)化，JOIN子句里也優(yōu)先從索引獲取ID列表，然后直接關(guān)聯(lián)查詢(xú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 |

+-----+-------+

1 row in set （0.00 sec）

至此SQL的解析之旅就結(jié)束了

參考書(shū)籍：

《MySQL性能調(diào)優(yōu)與架構(gòu)實(shí)踐》

《MySQL技術(shù)內(nèi)幕：SQL編程》

尾聲：

嗯，到這里這一次的深入了解之旅就差不多真的結(jié)束了，雖然也不是很深入，只是一些東西將其東拼西湊在一起而已，參考了一些以前看過(guò)的書(shū)籍，大師之筆果然不一樣。而且在這過(guò)程中也是get到了蠻多東西的，最重要的是更進(jìn)一步意識(shí)到，計(jì)算機(jī)軟件世界的宏大呀~

責(zé)任編輯：haq