有關(guān)mysql中sql的執(zhí)行順序的小問題

今天工作中碰到一個sql問題，關(guān)于left join的，后面雖然解決了，但是通過此問題了解了一下sql的執(zhí)行順序

場景還原

為避免安全糾紛，把場景模擬。

有一個學(xué)生表-S，一個成績表G

CREATE TABLE `test_student` (
 `id` bigint(20) NOT NULL COMMENT '學(xué)號',
	`sex` TINYINT DEFAULT '0' COMMENT '性別 0-男 1-女',
 `name` varchar(255) DEFAULT NULL COMMENT '姓名'
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='學(xué)生表';
 
CREATE TABLE `test_score` (
 `id` bigint(20) NOT NULL COMMENT '學(xué)號',
 `score` int NOT NULL COMMENT '分?jǐn)?shù)',
	`level` TINYINT COMMENT '成績 0-不及格 1-及格 2-優(yōu)良 3-優(yōu)秀'
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='成績表';
 
-- 初始化學(xué)生
INSERT INTO test_student VALUES(1, 0, '張三'), (2, 0, '李四'), (3, 1, '王X芳');

-- 初始化成績
INSERT INTO test_score VALUES(1, 10, 0), (2, 20, 0), (3, 100, 3);

現(xiàn)在有一個需求，查出學(xué)生的考試成績，可能的sql

SELECT ts.name AS '名字', tc.score AS '分?jǐn)?shù)'
FROM test_student ts
LEFT JOIN test_score tc
ON ts.id = tc.s_id;

一切都很順利，突然，剛考完試，就轉(zhuǎn)來一個學(xué)生

INSERT INTO test_student VALUES(4, 0, '新來的');

他沒成績,還是剛才的sql查

老師只要看參加考試的學(xué)生的成績，怎么辦

1.用inner join

SELECT ts.name AS '名字', tc.score AS '分?jǐn)?shù)'
FROM test_student ts
INNER JOIN test_score tc
ON ts.id = tc.s_id;

2.加條件過濾

SELECT ts.name AS '名字', tc.score AS '分?jǐn)?shù)'
FROM test_student ts
LEFT JOIN test_score tc
ON ts.id = tc.s_id
AND tc.score is NOT NULL
;

發(fā)現(xiàn)加了條件過濾還是不對，額，改用where呢

SELECT ts.name AS '名字', tc.score AS '分?jǐn)?shù)'
FROM test_student ts
LEFT JOIN test_score tc
ON ts.id = tc.s_id
WHERE tc.score is NOT NULL
;

bingo，那where為什么就對了呢。

這就涉及到sql的執(zhí)行順序了

where與join on

從上面的例子推斷

where會從結(jié)果集中過濾出符合條件的記錄，不符合條件的丟棄

join操作：有時為了得到完整的結(jié)果，我們需要從兩個或更多的表中獲取結(jié)果。我們就需要執(zhí)行 join。

除了我們在上面的例子中使用的 INNER JOIN（內(nèi)連接），我們還可以使用其他幾種連接。

下面列出了您可以使用的 JOIN 類型，以及它們之間的差異。

• JOIN: 如果表中有至少一個匹配，則返回行
• LEFT JOIN: 即使右表中沒有匹配，也從左表返回所有的行
• RIGHT JOIN: 即使左表中沒有匹配，也從右表返回所有的行
• FULL JOIN: 只要其中一個表中存在匹配，就返回行(mysql不支持，可以考慮用視圖實現(xiàn)）

這里推薦一個寫的通俗易懂的簡單學(xué)習(xí)SQL的各種連接Join

sql順序

從上面看到，在sql中，on是寫在了where條件之前，那么數(shù)據(jù)庫引擎分析執(zhí)行sql時，是否on也是在where前面呢?

一般sql的寫法順序

1.SELECT [列名稱 *代表所有的列]

2.FROM [表名稱]

3.join_type JOIN [表名稱]

4.ON [join條件]

5.WHERE [過濾條件]

6.GROUP BY [分組字段]

7.HAVING [分組條件]

8.ORDER BY [排序字段]

那么sql在執(zhí)行時，順序是怎樣的呢？

標(biāo)準(zhǔn)的sql解析順序為：

1.FROM    組裝數(shù)據(jù)，來自不同數(shù)據(jù)源(表）

2.WHERE    根據(jù)條件過濾記錄

3.GROUP BY 對數(shù)據(jù)分組

4.計算聚集函數(shù)，如avg,sum

5.使用HAVING子句篩選分組

6.計算所有表達(dá)式

7.使用ORDER BY對結(jié)果排序

那么sql的執(zhí)行順序呢？

1.FROM: 對前2個表執(zhí)行笛卡爾積，生成虛表vt1

2.ON: 對vt1應(yīng)用on條件，只有滿足join_condition條件的才能插入虛表vt2

3.OUTER(join)：如果指定了OUTER JOIN保留表(preserved table)中未找到的行將行作為外部行添加到vt2，生成t3，如果from包含兩個以上表，則對上一個聯(lián)結(jié)生成的結(jié)果表和下一個表重復(fù)執(zhí)行步驟和步驟直接結(jié)束

4.WHERE: 對vt3進(jìn)行where篩選，只有滿足where條件的才能插入vt4

5.GROUP BY: 對vt4按group by字段分組，得到vt5

6.HAVING：對vt5應(yīng)用HAVING篩選器只有使 having_condition 為true的組才插入vt6

7.SELECT：處理select列表產(chǎn)生vt7

8.DISTINCT：將重復(fù)的行從vt7中去除產(chǎn)生vt8

9.ORDER BY：將vt8的行按order by子句中的列 列表排序生成一個游標(biāo)vc9

10.LIMIT（Mysql）: 從vc9的開始處選擇指定數(shù)量的行生成vt10 并返回調(diào)用者

到了這里，應(yīng)該發(fā)現(xiàn)，要寫好sql不容易。但是了解了sql的執(zhí)行順序，能在開發(fā)的同理，更好的幫助寫出好的程序。

比如join表不能太多（先過濾條件然后再根據(jù)表連接 同時在表中建立相關(guān)查詢字段的索引這樣在大數(shù)據(jù)多表聯(lián)合查詢的情況下速度相當(dāng)快），這種sql優(yōu)化問題，下次再學(xué)習(xí)整理下。

實踐之！

以上就是本文的全部內(nèi)容，希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持億速云。