在线二区中文字幕三区,欧美成人相交视频在线观看,国产欧美成人在线

[導(dǎo)讀]↓推薦關(guān)注↓前言：一直是想知道一條SQL語句是怎么被執(zhí)行的，它執(zhí)行的順序是怎樣的，然后查看總結(jié)各方資料，就有了下面這一篇博文了。本文將從MySQL總體架構(gòu)--->查詢執(zhí)行流程--->語句執(zhí)行順序來探討一下其中的知識。一、MySQL架構(gòu)總覽：架構(gòu)最好看圖，再配上必要的說明文字。下圖...

↓推薦關(guān)注↓

前言：

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

本文將從MySQL總體架構(gòu)--->查詢執(zhí)行流程--->語句執(zhí)行順序來探討一下其中的知識。

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

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

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

img

從上圖中我們可以看到，整個架構(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ā)到‘連接進(jìn)/線程模塊’

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

1.4通過檢查后，‘連接進(jìn)/線程模塊’從‘線程連接池’中取出空閑的被緩存的連接線程和客戶端請求對接，如果失敗則創(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í)行計(jì)劃

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)二進(jìn)制日志文件中

3.結(jié)果

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

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

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

一圖小總結(jié)

三、SQL解析順序

接下來再走一步，讓我們看看一條SQL語句的前世今生。

首先看一下示例語句

SELECT?DISTINCT
????
FROM
?????
JOIN??ON?
WHERE
????
GROUP?BY
????
HAVING
????
ORDER?BY
????
LIMIT?

然而它的執(zhí)行順序是這樣的

?1?FROM?
?2?ON?
?3??JOIN?
?4?WHERE?
?5?GROUP?BY?
?6?HAVING?
?7?SELECT?
?8?DISTINCT?
?9?ORDER?BY?
10?LIMIT?

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

既然如此了，那就讓我們一步步來看看其中的細(xì)節(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?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)?ORDER?BY
????total?DESC
LIMIT?1;

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

1. FROM

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

(1-J1)笛卡爾積

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

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

注意：這里因?yàn)檎Z法限制，使用了'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,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)上找到一張很形象的關(guān)于‘SQL JOINS'的解釋圖，如若侵犯了你的權(quán)益，請勞煩告知刪除，謝謝。

img

2. WHERE

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

注意：

此時因?yàn)榉纸M，不能使用聚合運(yùn)算；也不能使用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?|
 ----- ------ ------ ------ 
4?rows?in?set?(0.00?sec)

3. GROUP BY

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

注意：

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

原因：

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

我的理解是：

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

這個子句對VT3表中的不同的組進(jìn)行過濾，只作用于分組后的數(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)? ----- ------ ------ ------ 
|?uid?|?name?|?oid??|?uid??|
 ----- ------ ------ ------ 
|?ccc?|?mike?|????6?|?ccc??|
|?ddd?|?mike?|?NULL?|?NULL?|
 ----- ------ ------ ------ 
2?rows?in?set?(0.00?sec)

5. SELECT

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

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

(5-J2)DISTINCT

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

如果在查詢中指定了DISTINCT子句，則會創(chuàng)建一張內(nèi)存臨時表（如果內(nèi)存放不下，就需要存放在硬盤了）。這張臨時表的表結(jié)構(gòu)和上一步產(chǎn)生的虛擬表VT5是一樣的，不同的是對進(jìn)行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)? ----- ------- 
|?uid?|?total?|
 ----- ------- 
|?ccc?|?????1?|
|?ddd?|?????0?|
 ----- ------- 
2?rows?in?set?(0.00?sec)

6.ORDER BY

從VT5-J2中的表中，根據(jù)ORDER BY 子句的條件對結(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)?????->?ORDER?BY
????->?total?DESC;
 ----- ------- 
|?uid?|?total?|
 ----- ------- 
|?ccc?|?????1?|
|?ddd?|?????0?|
 ----- ------- 
2?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)?????->?ORDER?BY
????->?total?DESC
????->?LIMIT?1;
 ----- ------- 
|?uid?|?total?|
 ----- ------- 
|?ccc?|?????1?|
 ----- ------- 
1?row?in?set?(0.00?sec)