設(shè)為“置頂或星標(biāo)”，第一時(shí)間送達(dá)干貨
SQL專欄
SQL基礎(chǔ)知識(shí)第二版
SQL高級(jí)知識(shí)第二版

對(duì)于 MySQL 的 JOIN，不知道大家有沒(méi)有去想過(guò)他的執(zhí)行流程，亦或有沒(méi)有懷疑過(guò)自己的理解（自信滿滿的自我認(rèn)為?。蝗绻蠹也恢涝趺礄z驗(yàn)，可以試著回答如下的問(wèn)題：

1. 驅(qū)動(dòng)表的選擇

MySQL 會(huì)如何選擇驅(qū)動(dòng)表，按從左至右的順序選擇第一個(gè)？

2. 多表連接的順序

假設(shè)我們有 3 張表：A、B、C，和如下 SQL

-- 偽 SQL，不能直接執(zhí)行
A LEFT JOIN B ON B.aId = A.id
LEFT JOIN C ON C.aId = A.id
WHERE A.name = '666' AND B.state = 1 AND C.create_time > '2019-11-22 12:12:30'

是 A 和 B 聯(lián)表處理完之后的結(jié)果再和 C 進(jìn)行聯(lián)表處理，還是 A、B、C 一起聯(lián)表之后再進(jìn)行過(guò)濾處理 ，還是說(shuō)這兩種都不對(duì)，有其他的處理方式 ？

3. ON、WHERE 的生效時(shí)機(jī)

樓主無(wú)意之間逛到了一篇博文，它里面有如下介紹

摘自 Mysql - JOIN詳解

看完這個(gè)，樓主第一時(shí)間有發(fā)現(xiàn)新大陸的感覺(jué)，原來(lái) JOIN 的執(zhí)行順序是這樣的（不是顛覆了樓主之前的認(rèn)知，因?yàn)闃侵髦熬蜎](méi)想過(guò)這個(gè)問(wèn)題，而是有種新技能獲取的滿足），可后面越想越不對(duì)，感覺(jué)像是學(xué)錯(cuò)了技能。

如果兩表各有幾百上千萬(wàn)的數(shù)據(jù)，那這兩張表做笛卡爾積，結(jié)果不敢想象！也就是說(shuō)上圖中的順序還有待商榷，ON 和 WHERE 的生效時(shí)間也有待商榷。

本篇文章就是針對(duì)上述問(wèn)題還不是特別清楚的小伙伴寫出的！

1. 驅(qū)動(dòng)表

何謂驅(qū)動(dòng)表，指多表關(guān)聯(lián)查詢時(shí)，第一個(gè)被處理的表，亦可稱之為基表，然后再使用此表的記錄去關(guān)聯(lián)其他表。驅(qū)動(dòng)表的選擇遵循一個(gè)原則：在對(duì)最終結(jié)果集沒(méi)影響的前提下，優(yōu)先選擇結(jié)果集最少的那張表作為驅(qū)動(dòng)表。這個(gè)原則說(shuō)的不好懂，結(jié)果集最少，這個(gè)也許我們能估出來(lái)，但對(duì)最終結(jié)果集不影響，這個(gè)就不好判斷了，難歸難，但還是有一定規(guī)律的：

LEFT JOIN 某些情況下會(huì)被查詢優(yōu)化器優(yōu)化成 INNER JOIN；結(jié)果集指的是表中記錄過(guò)濾后的結(jié)果，而不是表中的所有記錄，如果無(wú)過(guò)濾條件則是表中所有記錄

2. SQL 執(zhí)行的流程圖

當(dāng)我們向 MySQL 發(fā)送一個(gè)請(qǐng)求的時(shí)候，MySQL 到底做了些了什么

 SQL 執(zhí)行路徑，摘自《高性能MySQL》

可以看到，執(zhí)行計(jì)劃是查詢優(yōu)化器的輸出結(jié)果，執(zhí)行引擎根據(jù)執(zhí)行計(jì)劃來(lái)查詢數(shù)據(jù)

3. 數(shù)據(jù)準(zhǔn)備

MySQL 5.7.1，InnoDB 引擎；建表 SQL 和 數(shù)據(jù)初始 SQL

-- 表創(chuàng)建與數(shù)據(jù)初始化DROP TABLE IF EXISTS tbl_user;CREATE TABLE tbl_user (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主鍵', user_name VARCHAR(50) NOT NULL COMMENT '用戶名',
  sex TINYINT(1) NOT NULL COMMENT '性別, 1:男，0:女',
  create_time datetime NOT NULL COMMENT '創(chuàng)建時(shí)間',
  update_time datetime NOT NULL COMMENT '更新時(shí)間',
    remark VARCHAR(255) NOT NULL DEFAULT '' COMMENT '備注', PRIMARY KEY (id)
) COMMENT='用戶表';DROP TABLE IF EXISTS tbl_user_login_log;CREATE TABLE tbl_user_login_log (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主鍵', user_name VARCHAR(50) NOT NULL COMMENT '用戶名',
  ip VARCHAR(15) NOT NULL COMMENT '登錄IP',
  client TINYINT(1) NOT NULL COMMENT '登錄端, 1:android, 2:ios, 3:PC, 4:H5',
  create_time datetime NOT NULL COMMENT '創(chuàng)建時(shí)間', PRIMARY KEY (id)
) COMMENT='登錄日志';INSERT INTO tbl_user(user_name,sex,create_time,update_time,remark) VALUES('何天香',1,NOW(), NOW(),'朗眉星目，一表人材'),
('薛沉香',0,NOW(), NOW(),'天星樓的總樓主薛搖紅的女兒，也是天星樓的少總樓主，體態(tài)豐盈，烏發(fā)飄逸，指若春蔥，袖臂如玉，風(fēng)姿卓然，高貴典雅，人稱“天星絕香”的武林第一大美女'),
('慕容蘭娟',0,NOW(), NOW(),'武林東南西北四大世家之北世家慕容長(zhǎng)明的獨(dú)生女兒，生得玲瓏剔透，粉雕玉琢，脾氣卻是剛烈無(wú)比，又喜著火紅，所以人送綽號(hào)“火鳳凰”，是除天星樓薛沉香之外的武林第二大美女'),
('萇婷',0,NOW(), NOW(),'當(dāng)今皇上最寵愛(ài)的侄女，北王府的郡主，腰肢纖細(xì)，遍體羅綺，眉若墨畫(huà)，唇點(diǎn)櫻紅；雖無(wú)沉香之雅重，蘭娟之熱烈，卻別現(xiàn)出一種空靈'),
('柳含姻',0,NOW(), NOW(),'武林四絕之一的添愁仙子董婉婉的徒弟，體態(tài)窈窕，姿容秀麗，真?zhèn)€是秋水為神玉為骨，芙蓉如面柳如腰，眉若墨畫(huà)，唇若點(diǎn)櫻，不弱西子半分，更勝玉環(huán)一籌; 搖紅樓、聽(tīng)雨軒，琵琶一曲值千金!'),
('李凝雪',0,NOW(), NOW(),'李相國(guó)的女兒，神采奕奕，英姿颯爽，愛(ài)憎分明'),
('周遺夢(mèng)',0,NOW(), NOW(),'音神傳人，湘妃竹琴的擁有者，云髻高盤，穿了一身黑色蟬翼紗衫，愈覺(jué)得冰肌玉骨，粉面櫻唇，格外嬌艷動(dòng)人'),
('葉留痕',0,NOW(), NOW(),'圣域圣女，膚白如雪，白衣飄飄，宛如仙女一般，微笑中帶著說(shuō)不出的柔和之美'),
('郭疏影',0,NOW(), NOW(),'揚(yáng)灰右使的徒弟，秀發(fā)細(xì)眉，玉肌豐滑，嬌潤(rùn)脫俗'),
('鐘鈞天',0,NOW(), NOW(),'天界，玄天九部 - 鈞天部的部主，超凡脫俗，仙氣逼人'),
('王雁云',0,NOW(), NOW(),'塵緣山莊二小姐，刁蠻任性'),
('許侍霜',0,NOW(), NOW(),'藥王谷谷主女兒，醫(yī)術(shù)高明'),
('馮黯凝',0,NOW(), NOW(),'桃花門門主，嬌艷如火，千嬌百媚');INSERT INTO tbl_user_login_log(user_name, ip, client, create_time) VALUES('薛沉香', '10.53.56.78',2, '2019-10-12 12:23:45'),
('萇婷', '10.53.56.78',2, '2019-10-12 22:23:45'),
('慕容蘭娟', '10.53.56.12',1, '2018-08-12 22:23:45'),
('何天香', '10.53.56.12',1, '2019-10-19 10:23:45'),
('柳含姻', '198.11.132.198',2, '2018-05-12 22:23:45'),
('馮黯凝', '198.11.132.198',2, '2018-11-11 22:23:45'),
('周遺夢(mèng)', '198.11.132.198',2, '2019-06-18 22:23:45'),
('郭疏影', '220.181.38.148',3, '2019-10-21 09:45:56'),
('薛沉香', '220.181.38.148',3, '2019-10-26 22:23:45'),
('萇婷', '104.69.160.60',4, '2019-10-12 10:23:45'),
('王雁云', '104.69.160.61',4, '2019-10-16 20:23:45'),
('李凝雪', '104.69.160.62',4, '2019-10-17 20:23:45'),
('許侍霜', '104.69.160.63',4, '2019-10-18 20:23:45'),
('葉留痕', '104.69.160.64',4, '2019-10-19 20:23:45'),
('王雁云', '104.69.160.65',4, '2019-10-20 20:23:45'),
('葉留痕', '104.69.160.66',4, '2019-10-21 20:23:45');
SELECT * FROM tbl_user;
SELECT * FROM tbl_user_login_log;

4. 單表查詢

單表查詢的過(guò)程比較好理解，大致如下

關(guān)于單表查詢就不細(xì)講了，主要涉及到：聚簇索引，覆蓋索引、回表操作，知道這 3 點(diǎn)，上圖就好理解了（不知道的趕快去查資料或者看一下我們往期的文章，暴露了就丟人了！）。

MySQL 的聯(lián)表算法是基于嵌套循環(huán)算法（nested-loop algorithm）而衍生出來(lái)的一系列算法，根據(jù)不同條件而選用不同的算法

1. Simple Nested-Loop

簡(jiǎn)單嵌套循環(huán)，簡(jiǎn)稱 SNL；逐條逐條匹配，就像這樣

for each row in t1 matching range {
  for each row in t2 matching reference key {
      for each row in t3 {
            if row satisfies join conditions, send to client
    }
  }
}

這種算法簡(jiǎn)單粗暴，但毫無(wú)性能可言，時(shí)間性能上來(lái)說(shuō)是 n（表中記錄數(shù)） 的 m（表的數(shù)量） 次方，所以 MySQL 做了優(yōu)化，聯(lián)表查詢的時(shí)候不會(huì)出現(xiàn)這種算法，即使在無(wú) WHERE  條件且 ON 的連接鍵上無(wú)索引時(shí)，也不會(huì)選用這種算法

2. Block Nested-Loop

緩存塊嵌套循環(huán)連接，簡(jiǎn)稱 BNL，是對(duì) INL 的一種優(yōu)化；一次性緩存多條驅(qū)動(dòng)表的數(shù)據(jù)到 Join Buffer，然后拿 Join Buffer 里的數(shù)據(jù)批量與內(nèi)層循環(huán)讀取的數(shù)據(jù)進(jìn)行匹配，就像這樣

for each row in t1 matching range { for each row in t2 matching reference key {
    store used columns from t1, t2 in join buffer    if buffer is full { for each row in t3 { for each t1, t2 combination in join buffer { if row satisfies join conditions, send to client
        }
      }
      empty join buffer
    }
  }
}if buffer is not empty { for each row in t3 { for each t1, t2 combination in join buffer { if row satisfies join conditions, send to client
    }
  }
}

將內(nèi)部循環(huán)中讀取的每一行與緩沖區(qū)中的所有記錄進(jìn)行比較，這樣就可以減少內(nèi)層循環(huán)的讀表次數(shù)。舉個(gè)例子，如果沒(méi)有 Join Buffer，驅(qū)動(dòng)表有 30 條記錄，被驅(qū)動(dòng)表有 50 條記錄，那么內(nèi)層循環(huán)的讀表次數(shù)應(yīng)該是 30 * 50 = 1500，如果 Join Buffer 可用并可以存 10 條記錄（Join Buffer 存儲(chǔ)的是驅(qū)動(dòng)表中參與查詢的列，包括 SELECT 的列、ON 的列、WHERE 的列，而不是驅(qū)動(dòng)表中整行整行的完整記錄），那么內(nèi)層循環(huán)的讀表次數(shù)應(yīng)該是 30 / 10 * 50 = 150，被驅(qū)動(dòng)表必須讀取的次數(shù)減少了一個(gè)數(shù)量級(jí)。

當(dāng)被驅(qū)動(dòng)表在連接鍵上無(wú)索引且被驅(qū)動(dòng)表在 WHERE 過(guò)濾條件上也沒(méi)索引時(shí)，常常會(huì)采用此種算法來(lái)完成聯(lián)表，如下所示

4. Index Nested-Loop

索引嵌套循環(huán)，簡(jiǎn)稱 INL，是基于被驅(qū)動(dòng)表的索引進(jìn)行連接的算法；驅(qū)動(dòng)表的記錄逐條與被驅(qū)動(dòng)表的索引進(jìn)行匹配，避免和被驅(qū)動(dòng)表的每條記錄進(jìn)行比較，減少了對(duì)被驅(qū)動(dòng)表的匹配次數(shù)，大致流程如下圖

我們來(lái)看看實(shí)際案例，先給 tbl_user_login_log 添加索引 ALTER TABLE tbl_user_login_log ADD INDEX idx_user_name (user_name); ，我們?cè)賮?lái)看聯(lián)表執(zhí)行計(jì)劃

可以看到 tbl_user_login_log 的索引生效了，我們?cè)偻驴?/span>

有趣的事發(fā)生了，驅(qū)動(dòng)表變成了 tbl_user_login_log ，而 tbl_user 成了被驅(qū)動(dòng)表， tbl_user_login_log 走索引過(guò)濾后得到結(jié)果集，再通過(guò) BNL 算法將結(jié)果集與 tbl_user 進(jìn)行匹配。這其實(shí)是 MySQL進(jìn)行了優(yōu)化，因?yàn)?tbl_user_login_log 走索引過(guò)濾后得到的結(jié)果集比 tbl_user 記錄數(shù)要少，所以選擇了 tbl_user_login_log 作為驅(qū)動(dòng)表，后面的也就理所當(dāng)然了，是不是感覺(jué) MySQL 好強(qiáng)大？

5. Batched Key Access

批量key訪問(wèn)，簡(jiǎn)稱 BKA，是對(duì) INL 算法的一種優(yōu)化；BKA 對(duì) INL 的優(yōu)化類似于 BNL 對(duì) SNL 的優(yōu)化，但又有不同。

作者：青石路

來(lái)源：https://www.cnblogs.com/youzhibing/p/12004986.html

本文為轉(zhuǎn)載分享，授權(quán)轉(zhuǎn)載請(qǐng)聯(lián)系原作者