神奇的 SQL 之 聯表細節 → MySQL JOIN 的執行過程(一)

来源:https://www.cnblogs.com/youzhibing/archive/2019/12/09/12004986.html

開心一刻 我:嗨,老闆娘,有冰紅茶沒 老闆娘:有 我:多少錢一瓶 老闆娘:3塊 我:給我來一瓶,給,3塊 老闆娘:來,你的冰紅茶 我:玩吶,我要冰紅茶,你給我個瓶蓋乾哈? 老闆娘:這是再來一瓶,我家賣完了,你去隔壁家換一下 問題背景 對於 MySQL 的 JOIN,不知道大家有沒有去想過他的執行流程 ...


開心一刻

  我:嗨,老闆娘,有冰紅茶沒
  老闆娘:有
  我:多少錢一瓶
  老闆娘:3塊
  我:給我來一瓶,給,3塊
  老闆娘:來,你的冰紅茶
  我:玩吶,我要冰紅茶,你給我個瓶蓋乾哈?
  老闆娘:這是再來一瓶,我家賣完了,你去隔壁家換一下

問題背景

  對於 MySQL 的 JOIN,不知道大家有沒有去想過他的執行流程,亦或有沒有懷疑過自己的理解(自信滿滿的自我認為!);如果大家不知道怎麼檢驗,可以試著回答如下的問題

  驅動表的選擇

    MySQL 會如何選擇驅動表,按從左至右的順序選擇第一個?

  多表連接的順序

    假設我們有 3 張表:A、B、C,和如下 SQL

-- 偽 SQL,不能直接執行
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 聯表處理完之後的結果再和 C 進行聯表處理,還是 A、B、C 一起聯表之後再進行過濾處理 ,還是說這兩種都不對,有其他的處理方式 ?

  ON、WHERE 的生效時機

    樓主無意之間逛到了一篇博文,它裡面有如下介紹

正經圖1 摘自 Mysql - JOIN詳解

    看完這個,樓主第一時間有發現新大陸的感覺,原來 JOIN 的執行順序是這樣的(不是顛覆了樓主之前的認知,因為樓主之前就沒想過這個問題,而是有種新技能獲取的滿足),可後面越想越不對,感覺像是學錯了技能(6級沒學大!)

    如果兩表各有幾百上千萬的數據,那這兩張表做笛卡爾積,結果不敢想象!也就是說 正經圖1 中的順序還有待商榷,ON 和 WHERE 的生效時間也有待商榷

  如果你對上述問題都瞭如指掌,那請你走開,別妨礙我裝逼;如果你對上述問題還不是特別清楚,那麼請坐好,我要開始裝逼了

前提準備

  正式開講之前了,先給大家預備一些花生、瓜子和啤酒,裝逼就得有裝逼的氛圍,不然怎麼看的下去,你說是吧 ?(樓主,你個騙子,貨了?)

  驅動表

    何謂驅動表,指多表關聯查詢時,第一個被處理的表,亦可稱之為基表,然後再使用此表的記錄去關聯其他表。驅動表的選擇遵循一個原則:在對最終結果集沒影響的前提下,優先選擇結果集最少的那張表作為驅動表。這個原則說的不好懂,結果集最少,這個也許我們能估出來,但對最終結果集不影響,這個就不好判斷了,難歸難,但還是有一定規律的:

LEFT JOIN 一般以左表為驅動表(RIGHT JOIN一般則是右表 ),INNER JOIN 一般以結果集少的表為驅動表,如果還覺得有疑問,則可用 EXPLAIN 來找驅動表,其結果的第一張表即是驅動表。
你以為 EXPLAIN 就一定准嗎 ? 執行計劃在真正執行的時候是可能改變的! 絕大多少情況下是適用的,特別是 EXPLAIN

    LEFT JOIN 某些情況下會被查詢優化器優化成 INNER JOIN;結果集指的是表中記錄過濾後的結果,而不是表中的所有記錄,如果無過濾條件則是表中所有記錄

    更多信息可查看:Mysql多表連接查詢的執行細節(一)

  SQL 執行的流程圖

    當我們向 MySQL 發送一個請求的時候,MySQL 到底做了些了什麼

 SQL 執行路徑,摘自《高性能MySQL》

    可以看到,執行計劃是查詢優化器的輸出結果,執行引擎根據執行計劃來查詢數據

  數據準備

    MySQL 5.7.1,InnoDB 引擎;建表 SQL 和 數據初始 SQL

-- 表創建與數據初始化
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 '創建時間',
  update_time datetime NOT NULL COMMENT '更新時間',
    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 '創建時間',
  PRIMARY KEY (id)
) COMMENT='登錄日誌';
INSERT INTO tbl_user(user_name,sex,create_time,update_time,remark) VALUES
('何天香',1,NOW(), NOW(),'朗眉星目,一表人材'),
('薛沉香',0,NOW(), NOW(),'天星樓的總樓主薛搖紅的女兒,也是天星樓的少總樓主,體態豐盈,烏髮飄逸,指若春蔥,袖臂如玉,風姿卓然,高貴典雅,人稱“天星絕香”的武林第一大美女'),
('慕容蘭娟',0,NOW(), NOW(),'武林東南西北四大世家之北世家慕容長明的獨生女兒,生得玲瓏剔透,粉雕玉琢,脾氣卻是剛烈無比,又喜著火紅,所以人送綽號“火鳳凰”,是除天星樓薛沉香之外的武林第二大美女'),
('萇婷',0,NOW(), NOW(),'當今皇上最寵愛的侄女,北王府的郡主,腰肢纖細,遍體羅綺,眉若墨畫,唇點櫻紅;雖無沉香之雅重,蘭娟之熱烈,卻別現出一種空靈'),
('柳含姻',0,NOW(), NOW(),'武林四絕之一的添愁仙子董婉婉的徒弟,體態窈窕,姿容秀麗,真個是秋水為神玉為骨,芙蓉如面柳如腰,眉若墨畫,唇若點櫻,不弱西子半分,更勝玉環一籌; 搖紅樓、聽雨軒,琵琶一曲值千金!'),
('李凝雪',0,NOW(), NOW(),'李相國的女兒,神采奕奕,英姿颯爽,愛憎分明'),
('周遺夢',0,NOW(), NOW(),'音神傳人,湘妃竹琴的擁有者,雲髻高盤,穿了一身黑色蟬翼紗衫,愈覺得冰肌玉骨,粉面櫻唇,格外嬌艷動人'),
('葉留痕',0,NOW(), NOW(),'聖域聖女,膚白如雪,白衣飄飄,宛如仙女一般,微笑中帶著說不出的柔和之美'),
('郭疏影',0,NOW(), NOW(),'揚灰右使的徒弟,秀髮細眉,玉肌豐滑,嬌潤脫俗'),
('鐘鈞天',0,NOW(), NOW(),'天界,玄天九部 - 鈞天部的部主,超凡脫俗,仙氣逼人'),
('王雁雲',0,NOW(), NOW(),'塵緣山莊二小姐,刁蠻任性'),
('許侍霜',0,NOW(), NOW(),'藥王穀穀主女兒,醫術高明'),
('馮黯凝',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'),
('周遺夢', '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;
View Code

  單表查詢

    單表查詢的過程比較好理解,大致如下

    關於單表查詢就不細講了,主要涉及到:聚集索引,覆蓋索引、回表操作,知道這 3 點,上圖就好理解了(不知道的趕快去查資料,暴露了就丟人了!)。

聯表演算法

  MySQL 的聯表演算法是基於嵌套迴圈演算法(nested-loop algorithm)而衍生出來的一系列演算法,根據不同條件而選用不同的演算法

在使用索引關聯的情況下,有 Index Nested-Loop join 和 Batched Key Access join 兩種演算法;
在未使用索引關聯的情況下,有 Simple Nested-Loop join 和 Block Nested-Loop join 兩種演算法;  

  Simple Nested-Loop

    簡單嵌套迴圈,簡稱 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
    }
  }
}
View Code

    這種演算法簡單粗暴,但毫無性能可言,時間性能上來說是 n(表中記錄數) 的 m(表的數量) 次方,所以 MySQL 做了優化,聯表查詢的時候不會出現這種演算法,即使在無 WHERE  條件且 ON 的連接鍵上無索引時,也不會選用這種演算法

  Block Nested-Loop

    緩存塊嵌套迴圈連接,簡稱 BNL,是對 INL 的一種優化;一次性緩存多條驅動表的數據,然後拿 Join Buffer 里的數據批量與內層迴圈讀取的數據進行匹配,就像這樣

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
    }
  }
}
View Code

    將內部迴圈中讀取的每一行與緩衝區中的所有記錄進行比較,這樣就可以減少內層迴圈的讀表次數。舉個例子,如果沒有 Join Buffer,驅動表有 30 條記錄,被驅動表有 50 條記錄,那麼內層迴圈的讀表次數應該是 30 * 50 = 1500,如果 Join Buffer 可用並可以存 10 條記錄(Join Buffer 存儲的是驅動表中參與查詢的列,包括 SELECT 的列、ON 的列、WHERE 的列,而不是驅動表中整行整行的完整記錄),那麼內層迴圈的讀表次數應該是 30 / 10 * 50 = 150,被驅動表必須讀取的次數減少了一個數量級。

    當被驅動表在連接鍵上無索引且被驅動表在 WHERE 過濾條件上也沒索引時,常常會採用此種演算法來完成聯表,如下所示

  Index Nested-Loop

    索引嵌套迴圈,簡稱 INL,是基於被驅動表的索引進行連接的演算法;驅動表的記錄逐條與被驅動表的索引進行匹配,避免和被驅動表的每條記錄進行比較,減少了對被驅動表的匹配次數,大致流程如下圖

    我們來看看實際案例,先給 tbl_user_login_log 添加索引 ALTER TABLE tbl_user_login_log ADD INDEX idx_user_name (user_name); ,我們再來看聯表執行計劃

     可以看到 tbl_user_login_log 的索引生效了,我們再往下看

     有趣的事發生了,驅動表變成了 tbl_user_login_log ,而 tbl_user 成了被驅動表, tbl_user_login_log 走索引過濾後得到結果集,再通過 BNL 演算法將結果集與 tbl_user 進行匹配。這其實是 MySQL進行了優化,因為 tbl_user_login_log 走索引過濾後得到的結果集比 tbl_user 記錄數要少,所以選擇了 tbl_user_login_log 作為驅動表,後面的也就理所當然了,是不是感覺 MySQL 好強大?

  Batched Key Access

    批量key訪問,簡稱 BKA,是對 INL 演算法的一種優化;

    BKA 對 INL 的優化類似於 BNL 對 SNL 的優化,但又有不同; 鑒於篇幅原因,BKA 我們放到下期講解,希望各位老哥見諒!實在是不行,你來打我呀!

總結

  1、驅動表的選擇有它的一套演算法,有興趣的可以去專研下;比較靠譜的確定方法是用 EXPLAIN

  2、聯表順序,不是兩兩聯合之後,再去聯合第三張表,而是驅動表的一條記錄穿到底,匹配完所有關聯表之後,再取驅動表的下一條記錄重覆聯表操作;

  3、MySQL 的連接演算法基於嵌套迴圈演算法,基於不同的情況而採用不同的衍生演算法

  4、關於 ON 和 WHERE,我們下篇詳細講解,大家可以先考慮下它們的區別,以及生效時間

參考

  Mysql多表連接查詢的執行細節(一)


您的分享是我們最大的動力!

更多相關文章
  • 本文是轉載自:https://www.cnblogs.com/jylee/p/9844965.html 一、下載: 下載地址: https://github.com/MicrosoftArchive/redis/releases 根據系統下載的版本:以(64位為例) 下載後一般解壓到根目錄下:如(E ...
  • MSSQL - 最佳實踐 - 使用SSL加密連接 author: 風移 摘要 在SQL Server安全系列專題月報分享中,往期我們已經陸續分享了:如何使用對稱密鑰實現SQL Server列加密技術、使用非對稱密鑰實現SQL Server列加密、使用混合密鑰實現SQL Server列加密技術、列加密 ...
  • MsSQL使用加密連接SSL/TLS 說明 應用程式通過未加密的通道與資料庫伺服器通信, 這可能會造成重大的安全風險。在這種情況下, 攻擊者可以修改用戶輸入的數據, 甚至對資料庫伺服器執行任意 SQL 命令。 例如,當您使用以下連接字元串時,就可能存在這種風險: <connectionStrings ...
  • 停止redis服務: 開始redis服務: ...
  • 環境:VS2010,.NET Framework 4.0,Oracle.ManagedDataAccess 在最近做一個項目中,用到了Oracle資料庫,使用Oracle.ManagedDataAccess來進行資料庫連接。但是在執行事務的時候拋出異常 “'OracleInternal.MTS.DT ...
  • https://sqlserver.code.blog/2019/12/09/an-example-of-polybase-for-oracle/ ...
  • SQLite 的 UPDATE 語句用於修改表中已有的記錄。可以使用帶有 WHERE 子句的 UPDATE 查詢來更新選定行,否則所有的行都會被更新。 基本語法:UPDATE table_name SET column1 = value1, column2 = value2...., columnN ...
  • Sql模糊查詢,Like預設是不區分大小寫的 使用Like時,怎麼支持大小寫呢? upper、lower,只能模糊所有的內容,不能區分內容中的大小寫。 sqlite資料庫對text欄位預設是大小寫敏感的,但是唯獨在模糊查詢時不起作用。 解決方法是:查詢前先執行PRAGMA case_sensitiv ...
一周排行
  • 比如要拆分“呵呵呵90909086676喝喝999”,下麵當type=0返回的是中文字元串“呵呵呵,喝喝”,type=1返回的是數字字元串“90909086676,999”, private string GetStrings(string str,int type=0) { IList<strin ...
  • Swagger一個優秀的Api介面文檔生成工具。Swagger可以可以動態生成Api介面文檔,有效的降低前後端人員關於Api介面的溝通成本,促進項目高效開發。 1、使用NuGet安裝最新的包:Swashbuckle.AspNetCore。 2、編輯項目文件(NetCoreTemplate.Web.c ...
  • 2020 年 7 月 30 日, 由.NET基金會和微軟 將舉辦一個線上和為期一天的活動,包括 微軟 .NET 團隊的演講者以及社區的演講者。本次線上大會 專註.NET框架構建微服務,演講者分享構建和部署雲原生應用程式的最佳實踐、模式、提示和技巧。有關更多信息和隨時瞭解情況:https://focu... ...
  • #abp框架Excel導出——基於vue #1.技術棧 ##1.1 前端採用vue,官方提供 UI套件用的是iview ##1.2 後臺是abp——aspnetboilerplate 即abp v1,https://github.com/aspnetboilerplate/aspnetboilerp ...
  • 前言 本文的文字及圖片來源於網路,僅供學習、交流使用,不具有任何商業用途,版權歸原作者所有,如有問題請及時聯繫我們以作處理。 作者:碧茂大數據 PS:如有需要Python學習資料的小伙伴可以加下方的群去找免費管理員領取 input()輸入 Python提供了 input() 內置函數從標準輸入讀入一 ...
  • 從12年到20年,python以肉眼可見的趨勢超過了java,成為了當今It界人人皆知的編程語言。 python為什麼這麼火? 網路編程語言搜索指數 適合初學者 Python具有語法簡單、語句清晰的特點,這就讓初學者在學習階段可以把精力集中在編程對象和思維方法上。 大佬都在用 Google,YouT ...
  • 在社會上存在一種普遍的對培訓機構的學生一種歧視的現象,具體表現在,比如:當你去公司面試的時候,一旦你說了你是培訓機構出來的,那麼基本上你就涼了,那麼你瞞著不說,然後又通過了面試成功入職,但是以後一旦在公司被髮現有培訓經歷,可能會面臨被降薪,甚至被辭退,培訓機構出來的學生,在用人單位眼裡就是能力低下的 ...
  • from typing import List# 這道題看了大佬寫的代碼,經過自己的理解寫出來了。# 從最外圍的四周找有沒有為O的,如果有的話就進入深搜函數,然後深搜遍歷# 判斷上下左右的位置是否為Oclass Solution: def solve(self, board: List[List[s ...
  • import requests; import re; import os; # 1.請求網頁 header = { "user-agent":'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_5) AppleWebKit/537.36 (KHTML, li ...
  • import requests; import re; import os; import parsel; 1.請求網頁 header = { "user-agent":'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_5) AppleWebKit/537. ...