開心一刻
樓主：心都讓你吓出來了！
獅王：淡定，打個小噴嚏而已
前情回顧
神奇的 SQL 之 聯表細節 → MySQL JOIN 的執行過程（一）中，我們講到了 3 種聯表算法：SNL、BNL 和 INL，了解了資料的查詢方式是 one by one，聯表方式也是 one by one ；并談到了 ON 和 WHERE，對下圖中所說的提出了質疑
認為 ON 和 WHERE 的生效時機有待商榷；此時樓主開始了欠大家的賬
神奇的 SQL 之 聯表細節 → MySQL JOIN 的執行過程（二）中對聯表算法進行了補充，詳細介紹了 MRR 和 BKA，但還是未介紹 ON 和 WHERE，樓主依舊欠着大家的賬，内心湧滿了愧疚
終于在今天，樓主痛定思痛，決定将這筆賬還上；此刻樓主的内心獨白是這樣的
此時各位看官的内心肯定嘀咕着：你特麼欠賬欠的這麼義憤填膺 ？ 不過我好喜歡
咳咳，閑話不多說，進入我們今天的正題
SQL 執行順序
SQL 的執行順序相信大家多少有所了解，上網一搜也很快就能找到答案
除了 WITH 用的比較少之外，其他都比較常用，相信大家對上面的執行順序也沒有什麼疑問；我們重點關注下 JOIN、ON 和 WHERE
那麼 WHERE 是不是一定是在 ON 之後生效了 ？ 我們帶着這個疑問往下看
ON 和 WHERE 的正常差別
on 針對的關聯條件，是表與表之間通過哪些列、以什麼條件進行關聯，而 where 針對的是過濾條件；兩者從概念上來講是不同的
另外 on 一定是與 join 一并使用的，join 會添加外部行，并将外部行中被驅動表的字段填充 null ，而 where 進行過濾的時候，隻有邏輯判斷為 true 的記錄才會保留，邏輯值為 false 和 unknown 的記錄都會過濾掉（更多詳情：神奇的 SQL 之溫柔的陷阱 → 三值邏輯 與 NULL ！）；兩者得到的結果會有所不同
上面說的可能有些抽象，我們結合具體示例來看；MySQL 版本 5.7.21 ，準備表和初始資料
create table tbl_a (a int primary key, b int, c int, d int, e varchar(50));
insert into tbl_a values (4,3,1,1,'a');
insert into tbl_a values (1,1,1,2,'d');
insert into tbl_a values (8,8,7,8,'h');
insert into tbl_a values (2,2,1,2,'g');
insert into tbl_a values (5,2,2,5,'e');
insert into tbl_a values (3,3,2,1,'c');
insert into tbl_a values (7,4,0,5,'b');
insert into tbl_a values (6,5,2,4,'f');
create table tbl_b like tbl_a;
insert into tbl_b SELECT * from tbl_a;
insert into tbl_a values (9,9,9,9,'9');
insert into tbl_b values (10,10,10,10,'10'); View Code
我們先來看看 left join（right join類似）
SELECT * FROM tbl_a a LEFT JOIN tbl_b b ON a.a = b.a AND a.b = b.b; /*query_on*/
SELECT * FROM tbl_a a LEFT JOIN tbl_b b ON a.a = b.a WHERE a.b = b.b; /*query_where*/
我們可以看到：
語句 query_on 傳回了 tbl_a 中的全部記錄，tbl_b 無對應記錄的字段值填成 NULL，這是因為 join 會添加外部行，将 tbl_a 有而 tbl_b 中沒有的記錄添加到結果集
語句 query_where 傳回的是 8 行。因為最後的一行，在表 tbl_b 中沒有比對的字段，是以 where 後的 b.b 的值是 NULL，而 a.b 的值是 9，那麼 where 9 = NULL 的結果是 unknown 而不是 true，是以這條記錄不能作為結果集的一部分
我們再來看看 inner join
SELECT * FROM tbl_a a INNER JOIN tbl_b b ON a.a = b.a AND a.b = b.b; /*query_on*/
SELECT * FROM tbl_a a INNER JOIN tbl_b b ON a.a = b.a WHERE a.b = b.b; /*query_where*/
我們可以看到，執行結果是一樣的，inner join 查詢的就是驅動表與被驅動表同時存在的記錄，是以過濾條件不管放在 ON 裡，還是放在 WHERE 裡，執行結果是一樣的
ON 和 WHERE 的生效時機
ON 後的關聯條件與 WHERE 後的過濾條件，這兩者的執行順序是否如 SQL 執行順序圖中說的那樣，ON 一定先與 WHERE ？
問題先放着，我們以 left join 為例，來看看 4 個案例，也許從中能找到我們想要的答案
1、左表與右表都沒二級索引
剛好上面的 tbl_a 和 tbl_b 滿足條件，我們來看看 SQL 的執行計劃
EXPLAIN SELECT * FROM tbl_a a
LEFT JOIN tbl_b b
ON a.b = b.b AND a.c = b.c
WHERE a.b >= 2 AND a.b < 10 AND a.c > 0
AND a.d != 1 AND a.e != 'a' View Code
驅動表是 tbl_a，這個相信大家沒問題，我們重點看下 type 和 Extra
type：上面的 ALL 表示全表掃描 a 表，下面的 ALL 表示全表關聯，a 表中每一條滿足條件的記錄都會與 b 表中全部 9 條記錄逐條進行關聯
Extra：Using where 表示要進行 WHERE 條件過濾，Using join buffer (Block Nested Loop) 表示用到了 BNL
這條 SQL 的執行流程應該是這樣的：
此時大家看出什麼了沒 ？ ON 後的關聯條件是在 WHERE 後的過濾條件之前生效的嗎 ？
這個案例不太常見，因為表沒有二級索引，我們接着往下看看有二級索引的情況
2、左表有二級索引，右表無二級索引
我們在 tbl_a 建一個組合索引 create index idx_bcd on tbl_a(b, c, d); ，然後往 tbl_a 和 tbl_b 中各插入 10W 條記錄，我們再來看執行計劃
上圖中紅框标記的字段重點關注下，不知道字段含義的小夥伴，可以去翻翻我之前關于 explain的部落格
那麼此時 SQL 的執行流程應該是這樣的：
就步驟 1 與 示例 1 中的步驟 1 不同，其餘 2 步是一樣的
此時 WHERE 後的過濾條件的生效時機也是早于 ON 後的關聯條件的
3、左表無二級索引，右表有二級索引
将 tbl_b 作為左表，tbl_a 作為右表，我們來看效果
此時 SQL 的執行流程應該是這樣的：
此時 ON 後的關聯條件的生效時機是早于 WHERE 後的過濾條件的
4、左表與右表都有二級索引
我們在 tbl_b 表上建一個組合索引 create index idx_bcd on tbl_b(b, c, d); 我們來看看 SQL 的執行計劃
此時 SQL 的執行流程應該是這樣的：
先是 WHERE 中的 Index Filter 條件生效，然後是 ON 後的關聯條件生效，最後是 WHERE 中的 Table Filter 生效，關聯條件的生效時間穿插在過濾條件的生效時間中
自此，關于 ON 和 WHERE 的生效時機，你清楚了嗎 ？
總結
1、關聯部落格
如果沒有讀樓主的前幾篇部落格，那麼有些概念可能不了解，樓主把相關聯的部落格都列一下
神奇的 SQL 之溫柔的陷阱 → 三值邏輯 與 NULL ！
神奇的 SQL 之 MySQL 執行計劃 → EXPLAIN，讓我們了解 SQL 的執行過程！
神奇的 SQL 之 聯表細節 → MySQL JOIN 的執行過程（一）
神奇的 SQL 之 聯表細節 → MySQL JOIN 的執行過程（二）
神奇的 SQL 之 WHERE 條件的提取與應用
神奇的 SQL 之 ICP → 索引條件下推
對相關概念不了解的可以去對應的部落格查閱
2、ON 和 WHERE
兩者好區分，也容易混淆，他們在概念上就做了明确區分，但是又可以做概念之外的事，是以用着用着就開始混淆了
樓主推薦：嚴格按他們的概念來處理，ON 後跟關聯條件，其他的都放到 WHERE 後做過濾條件；盡量保證 SQL 語義清晰
至于他兩的生效時機，需要結合表結構，以及具體的 SQL 來分析，而不是 ON 一定先于 WHERE
參考
What is the meaning of filtered in MySQL explain?
MySQL的server層和存儲引擎層是如何互動的