步步深入：MySQL?架構(gòu)總覽-

↓推薦關(guān)注↓

前言：

一直是想知道一條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)/線(xiàn)程模塊’

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

1.4通過(guò)檢查后，‘連接進(jìn)/線(xiàn)程模塊’從‘線(xiàn)程連接池’中取出空閑的被緩存的連接線(xià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)/線(xiàn)程模塊’

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

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

一圖小總結(jié)

三、SQL解析順序

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

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

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?




雖然自己沒(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('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)在開(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ò)濾出所有滿(mǎn)足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,FULL)，主表（保留表）中的不符合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ò)濾，滿(mǎn)足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ù)，滿(mǎn)足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

這個(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)? ----- -------
|?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)?????->?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)?????->?ORDER?BY
????->?total?DESC
????->?LIMIT?1;
----- -------
|?uid?|?total?|
----- -------
|?ccc?|?????1?|
----- -------
1?row?in?set?(0.00?sec)


至此SQL的解析之旅就結(jié)束了，上圖總結(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ī)軟件世界的宏大呀~

作者：AnnsShadoW

https://www.cnblogs.com/annsshadow/p/5037667.html

- EOF -