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

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

開心一刻 我：嗨，老闆娘，有冰紅茶沒 老闆娘：有 我：多少錢一瓶 老闆娘：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多表連接查詢的執行細節（一）


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

-Advertisement-
Play Games
更多相關文章
  • Windows中redis的下載及安裝、設置
    本文是轉載自：https://www.cnblogs.com/jylee/p/9844965.html 一、下載： 下載地址： https://github.com/MicrosoftArchive/redis/releases 根據系統下載的版本：以（64位為例） 下載後一般解壓到根目錄下：如（E ...
  • MSSQL - 最佳實踐 - 使用SSL加密連接
    MSSQL - 最佳實踐 - 使用SSL加密連接 author: 風移 摘要 在SQL Server安全系列專題月報分享中，往期我們已經陸續分享了：如何使用對稱密鑰實現SQL Server列加密技術、使用非對稱密鑰實現SQL Server列加密、使用混合密鑰實現SQL Server列加密技術、列加密 ...
  • MsSQL使用加密連接SSL/TLS
    MsSQL使用加密連接SSL/TLS 說明 應用程式通過未加密的通道與資料庫伺服器通信, 這可能會造成重大的安全風險。在這種情況下, 攻擊者可以修改用戶輸入的數據, 甚至對資料庫伺服器執行任意 SQL 命令。 例如，當您使用以下連接字元串時，就可能存在這種風險： <connectionStrings ...
  • Mac 停止redis服務
    停止redis服務： 開始redis服務： ...
  • 'OracleInternal.MTS.DTCPSPEManager' 類型初始值設定項引發異常
    環境：VS2010，.NET Framework 4.0，Oracle.ManagedDataAccess 在最近做一個項目中，用到了Oracle資料庫，使用Oracle.ManagedDataAccess來進行資料庫連接。但是在執行事務的時候拋出異常 “'OracleInternal.MTS.DT ...
  • An example of polybase for Oracle
    https://sqlserver.code.blog/2019/12/09/an-example-of-polybase-for-oracle/ ...
  • Sqlite—修改語句(Update)
    SQLite 的 UPDATE 語句用於修改表中已有的記錄。可以使用帶有 WHERE 子句的 UPDATE 查詢來更新選定行，否則所有的行都會被更新。 基本語法：UPDATE table_name SET column1 = value1, column2 = value2...., columnN ...
  • Sql like模糊查詢 區分大小寫
    Sql模糊查詢，Like預設是不區分大小寫的 使用Like時，怎麼支持大小寫呢？ upper、lower，只能模糊所有的內容，不能區分內容中的大小寫。 sqlite資料庫對text欄位預設是大小寫敏感的，但是唯獨在模糊查詢時不起作用。 解決方法是：查詢前先執行PRAGMA case_sensitiv ...
一周排行
    -Advertisement-
    Play Games
  • GoF之工廠模式
    GoF之工廠模式 @目錄GoF之工廠模式每博一文案1. 簡單說明“23種設計模式”1.2 介紹工廠模式的三種形態1.3 簡單工廠模式（靜態工廠模式）1.3.1 簡單工廠模式的優缺點:1.4 工廠方法模式1.4.1 工廠方法模式的優缺點:1.5 抽象工廠模式1.6 抽象工廠模式的優缺點：2. 總結:3 ...
  • dotnet C# 簡單的追加文件夾到 ZipArchive 壓縮文件的方法
    新改進提供的Taurus Rpc 功能，可以簡化微服務間的調用，同時可以不用再手動輸出模塊名稱，或調用路徑，包括負載均衡，這一切，由框架實現並提供了。新的Taurus Rpc 功能，將使得服務間的調用，更加輕鬆、簡約、高效。 ...
  • Elasticsearch 系列（六）- ES數據同步和ES集群
    本章將和大家分享ES的數據同步方案和ES集群相關知識。廢話不多說，下麵我們直接進入主題。 一、ES數據同步 1、數據同步問題 Elasticsearch中的酒店數據來自於mysql資料庫，因此mysql數據發生改變時，Elasticsearch也必須跟著改變，這個就是Elasticsearch與my ...
  • 自動化測試數據生成：Asp.Net Core單元測試利器AutoFixture詳解
    引言 在我們之前的文章中介紹過使用Bogus生成模擬測試數據，今天來講解一下功能更加強大自動生成測試數據的工具的庫"AutoFixture"。 什麼是AutoFixture? AutoFixture 是一個針對 .NET 的開源庫，旨在最大程度地減少單元測試中的“安排（Arrange）”階段，以提高 ...
  • .NET Emit 入門教程：第七部分：實戰項目1：將 DbDataReader 轉實體
    經過前面幾個部分學習，相信學過的同學已經能夠掌握 .NET Emit 這種中間語言，並能使得它來編寫一些應用，以提高程式的性能。隨著 IL 指令篇的結束，本系列也已經接近尾聲，在這接近結束的最後，會提供幾個可供直接使用的示例，以供大伙分析或使用在項目中。 ...
  • Windows下綁定線程到指定的CPU核心
    當從不同來源導入Excel數據時，可能存在重覆的記錄。為了確保數據的準確性，通常需要刪除這些重覆的行。手動查找並刪除可能會非常耗費時間，而通過編程腳本則可以實現在短時間內處理大量數據。本文將提供一個使用C# 快速查找並刪除Excel重覆項的免費解決方案。 以下是實現步驟： 1. 首先安裝免費.NET ...
  • mORMot 1.18 第07章 簡單的讀寫操作
    C++ 異常處理 C++ 異常處理機制允許程式在運行時處理錯誤或意外情況。它提供了捕獲和處理錯誤的一種結構化方式，使程式更加健壯和可靠。 異常處理的基本概念： 異常: 程式在運行時發生的錯誤或意外情況。 拋出異常: 使用 throw 關鍵字將異常傳遞給調用堆棧。 捕獲異常: 使用 try-catch ...
  • mORMot 1.18 第06章 概念
    優秀且經驗豐富的Java開發人員的特征之一是對API的廣泛瞭解，包括JDK和第三方庫。 我花了很多時間來學習API，尤其是在閱讀了Effective Java 3rd Edition之後 ，Joshua Bloch建議在Java 3rd Edition中使用現有的API進行開發，而不是為常見的東西編 ...
  • php 開發規範
    框架 · 使用laravel框架，原因：tp的框架路由和orm沒有laravel好用 · 使用強制路由，方便介面多時，分多版本，分文件夾等操作 介面 · 介面開發註意欄位類型，欄位是int，查詢成功失敗都要返回int（對接java等強類型語言方便） · 查詢介面用GET、其他用POST 代碼 · 所 ...
  • 2024 年 4 月 28 日 周日 晴 熱（1253 字）
    正文 下午找企業的人去鎮上做貸後。 車上聽同事跟那個司機對罵，火星子都快出來了。司機跟那同事更熟一些，連我在內一共就三個人，同事那一手指桑罵槐給我都聽愣了。司機也是老社會人了，馬上聽出來了，為那個無辜的企業經辦人辯護，實際上是為自己辯護。 “這個事情你不能怪企業。”“但他們總不能讓銀行的人全權負責， ...
所有分類