Mysql查詢語句執(zhí)行原理

數(shù)據(jù)庫查詢語句如何執(zhí)行？

• DML語句首先進(jìn)行語法分析，對使用sql表示的查詢進(jìn)行語法分析，生成查詢語法分析樹。
• 語義檢查：檢查sql中所涉及的對象以及是否在數(shù)據(jù)庫中存在，用戶是否具有操作權(quán)限等
• 視圖轉(zhuǎn)換：將語法分析樹轉(zhuǎn)換成關(guān)系代數(shù)表達(dá)式，稱為邏輯查詢計劃；
• 查詢優(yōu)化：在選擇邏輯查詢計劃時，會有多個不同的表達(dá)式，選擇最佳的邏輯查詢計劃；
• 代碼生成：必須將邏輯查詢計劃轉(zhuǎn)換成物理查詢計劃，物理查詢計劃不僅能指明要執(zhí)行的操作，也給出了這些操作的執(zhí)行順序，每步所用的算法，存儲數(shù)據(jù)的方式以及從一個操作傳遞給另一個操作的方式。
• 將DML轉(zhuǎn)換成一串可執(zhí)行的存取操作的過程稱為束縛過程，

Mysql查詢語句執(zhí)行過程

這里簡單介紹一下mysql數(shù)據(jù)庫，mysql數(shù)據(jù)庫是一款關(guān)系型數(shù)據(jù)庫，所謂關(guān)系型數(shù)據(jù)庫就是以二維表的形式存儲數(shù)據(jù)，使用行和列方便我們對數(shù)據(jù)的增刪改查。

這篇博客，我們以mysql數(shù)據(jù)庫為例，對一條sql語句的執(zhí)行流程進(jìn)行分析。（本篇博客不涉及到表連接）

首先，創(chuàng)建一張student表，字段有自增主鍵id，學(xué)生姓名name，學(xué)科subject，成績grade

建表語句

DROP TABLE IF EXISTS student;
CREATE TABLE `student` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `subject` varchar(10) DEFAULT NULL,
  `grade` double(4,1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8;

初始化數(shù)據(jù)

INSERT INTO student(`name`,`subject`,grade)VALUES('aom','語文',88);
INSERT INTO student(`name`,`subject`,grade)VALUES('aom','數(shù)學(xué)',99);
INSERT INTO student(`name`,`subject`,grade)VALUES('aom','外語',55);
INSERT INTO student(`name`,`subject`,grade)VALUES('jack','語文',67);
INSERT INTO student(`name`,`subject`,grade)VALUES('jack','數(shù)學(xué)',44);
INSERT INTO student(`name`,`subject`,grade)VALUES('jack','外語',55);
INSERT INTO student(`name`,`subject`,grade)VALUES('susan','語文',56);
INSERT INTO student(`name`,`subject`,grade)VALUES('susan','數(shù)學(xué)',35);
INSERT INTO student(`name`,`subject`,grade)VALUES('susan','外語',77);
INSERT INTO student(`name`,`subject`,grade)VALUES('alice','語文',88);
INSERT INTO student(`name`,`subject`,grade)VALUES('alice','數(shù)學(xué)',77);
INSERT INTO student(`name`,`subject`,grade)VALUES('alice','外語',100);
INSERT INTO student(`name`,`subject`,grade)VALUES('rajo','語文',33);
INSERT INTO student(`name`,`subject`,grade)VALUES('rajo','數(shù)學(xué)',55);
INSERT INTO student(`name`,`subject`,grade)VALUES('rajo','外語',55);

下面我們來看一下，數(shù)據(jù)在數(shù)據(jù)庫中的存儲形式。

（圖1.0）

現(xiàn)在針對這張student表中的數(shù)據(jù)提出一個問題：要求查詢出掛科數(shù)目多于兩門（包含兩門）的前兩名學(xué)生的姓名，如果掛科數(shù)目相同按學(xué)生姓名升序排列。

下面是這條查詢的sql語句

SELECT `name`,COUNT(`name`) AS num FROM student WHERE grade < 60 GROUP BY `name` HAVING num >= 2 ORDER BY num DESC,`name` ASC LIMIT 0,2;

執(zhí)行結(jié)果：

圖（1.1）

以上這條sql語句基本上概括了單表查詢中所有要注意的點，那么我們就以這條sql為例來分析一下一條語句的執(zhí)行流程。

1，一條查詢的sql語句先執(zhí)行的是 FROM student 負(fù)責(zé)把數(shù)據(jù)庫的表文件加載到內(nèi)存中去，如圖1.0中所示。（mysql數(shù)據(jù)庫在計算機(jī)上也是一個進(jìn)程，cpu會給該進(jìn)程分配一塊內(nèi)存空間，在計算機(jī)‘服務(wù)’中可以看到，該進(jìn)程的狀態(tài)）

圖（1.2）

2，WHERE grade < 60，會把（圖1.0）所示表中的數(shù)據(jù)進(jìn)行過濾，取出符合條件的記錄行，生成一張臨時表，如下圖所示。

圖（1.3）

3，GROUP BY `name`會把圖（1.3）的臨時表切分成若干臨時表，分為四個分組，我們用下圖來表示內(nèi)存中這個切分的過程。

圖（1.4)

圖（1.5)

圖（1.6)

圖（1.7)

4，SELECT 的執(zhí)行讀取規(guī)則分為sql語句中有無GROUP BY兩種情況。

（1）當(dāng)沒有GROUP BY時，SELECT 會根據(jù)后面的字段名稱對內(nèi)存中的一張臨時表整列讀取。

（2）當(dāng)查詢sql中有GROUP BY時，會對內(nèi)存中的若干臨時表分別執(zhí)行SELECT，而且只取各臨時表中的第一條記錄，然后再形成新的臨時表。這就決定了查詢sql使用GROUP BY的場景下，SELECT后面跟的一般是參與分組的字段和聚合函數(shù)，否則查詢出的數(shù)據(jù)要是情況而定。另外聚合函數(shù)中的字段可以是表中的任意字段，需要注意的是聚合函數(shù)會自動忽略空值。

我們還是以本例中的查詢sql來分析，現(xiàn)在內(nèi)存中有四張被GROUP BY `name`切分成的臨時表，我們分別取名為 tempTable1,tempTable2,tempTable3,tempTable4分別對應(yīng)圖（1.4)、圖（1.5)、圖（1.6)，圖（1.7)下面寫四條"偽SQL"來說明這個查詢過程。

SELECT `name`,COUNT(`name`) AS num FROM tempTable1;
SELECT `name`,COUNT(`name`) AS num FROM tempTable2;
SELECT `name`,COUNT(`name`) AS num FROM tempTable3;
SELECT `name`,COUNT(`name`) AS num FROM tempTable4;

最后再次成新的臨時表，如下圖：

圖（1.8)

5，HAVING num >= 2對上圖所示臨時表中的數(shù)據(jù)再次過濾，與WHERE語句不同的是HAVING 用在GROUP BY之后，WHERE是對FROM student從數(shù)據(jù)庫表文件加載到內(nèi)存中的原生數(shù)據(jù)過濾，而HAVING 是對SELECT 語句執(zhí)行之后的臨時表中的數(shù)據(jù)過濾，所以說column AS otherName ,otherName這樣的字段在WHERE后不能使用，但在HAVING 后可以使用。

但HAVING的后使用的字段只能是SELECT 后的字段，SELECT后沒有的字段HAVING之后不能使用。

HAVING num >= 2語句執(zhí)行之后生成一張臨時表，如下：

圖（1.9)

6，ORDER BY num DESC,`name` ASC對以上的臨時表按照num，name進(jìn)行排序。

7，LIMIT 0,2取排序后的前兩個。

轉(zhuǎn)存失敗重新上傳取消

以上就是一條sql的執(zhí)行過程，同時我們在書寫查詢sql的時候應(yīng)當(dāng)遵守以下順序。

SELECT XXX FROM XXX WHERE XXX GROUP BY XXX HAVING XXX ORDER BY XXX LIMIT XXX;

總結(jié)

這些僅為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論