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

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

開心一刻 一頭母牛在吃草，突然一頭公牛從遠處狂奔而來說：“快跑啊！！樓主來了！” 母牛說：“樓主來了關我屁事啊？” 公牛急忙說：“樓主吹牛逼呀！” 母牛大驚，拔腿就跑，邊跑邊問：“你是公牛你怕什麼啊？” 公牛無奈道：“現在的樓主不僅吹牛逼，還扯蛋！” 然後小牛也在跟著跑，公牛和母牛問：“兒子你跑什麼 ...


開心一刻

　　一頭母牛在吃草，突然一頭公牛從遠處狂奔而來說：“快跑啊！！樓主來了！”

　　母牛說：“樓主來了關我屁事啊？”

　　公牛急忙說：“樓主吹牛逼呀！”

　　母牛大驚，拔腿就跑，邊跑邊問：“你是公牛你怕什麼啊？”

　　公牛無奈道：“現在的樓主不僅吹牛逼，還扯蛋！”

　　然後小牛也在跟著跑，公牛和母牛問：“兒子你跑什麼呢？”

　　小牛說：“樓主還扯犢子啊”

前情回顧

　　神奇的 SQL 之 聯表細節 → MySQL JOIN 的執行過程（一）中，我們講到了 JOIN 的部分內容，像：驅動表、JOIN 大致流程等。什麼，還沒看？趕緊去看呀，啊？ 你都知道呀，那你走吧

　　走就走，你把欠的內容還上我就走；我欠什麼了？ 我欠，我欠...  我好像是欠點東西

　　　　1、BKA（Batched Key Access）

　　　　2、ON 和 WHERE

　　請各位坐好，我要開始表演了

環境準備

　　資料庫： MySQL 5.7.1 

　　存儲引擎： InnoDB 

　　建表和初始化數據

-- 查看版本和存儲引擎
SELECT VERSION();
SHOW ENGINES;
SHOW VARIABLES LIKE '%storage_engine%';

-- 表創建與數據初始化
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;


DROP TABLE IF EXISTS tbl_range_access;
CREATE TABLE tbl_range_access (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
  a INT(11) NOT NULL COMMENT '測試索引', 
    name VARCHAR(50) NOT NULL COMMENT '姓名',
    age TINYINT(3) NOT NULL COMMENT '年齡',
  PRIMARY KEY (id),
    INDEX i_a(a)
) COMMENT='mrr測試';
INSERT INTO tbl_range_access(a,name,age) VALUES
(5,'123654', 23),
(8, 'asdf',20),
(1,'lljl',19),
(4, '98459',64),
(7,'zhangsan', 45),
(9,'lisi',46),
(2,'zhaoqian',25),
(6,'hello', 23),
(3,'world',100),
(10,'666',66),
(88, '888',88);

SELECT * FROM tbl_range_access;
View Code

　　表 tbl_range_access 的數據要多一點，像上面示例只有 11 條記錄，那麼即使 a 欄位上有索引， SELECT * FROM tbl_range_access WHERE a BETWEEN 4 AND 9; 也不會走索引，執行計劃如下

　　數據太少，優化器覺得走索引，然後回表查詢數據，還不如直接走聚簇索引全表查詢來的快，所以沒有選擇走索引 i_a 

　　既然數據太少，我們就多造點數據，運行 data-init 下的 RangeAccessTest.java 中的 batchAddData 方法就好，輕輕鬆松 10W 到手！ 此時執行計劃如下

MRR

　　講 BKA 之前了，我們不得不先看下 MRR，它是 BKA 的重要支柱

　　全稱 Multi-Range Read ，是對多行 IO 查詢進行優化的一種策略，詳情可看 MySQL 的 mrr-optimization 或者 MariaDB 的 Multi Range Read Optimization（MySQL 和 MariaDB 是什麼關係？ 呃，這麼說吧，他們是一個爹的兒子）。簡單點來說，MRR 是優化器將隨機 IO 轉化為順序 IO 以降低查詢過程中 IO 開銷的一種手段

什麼是讀盤與落盤（IO）

    當前絕大多少情況下，MySQL 的數據是存在機械硬碟（SATA 盤）上的，極少數情況下是存在固態硬碟（SSD）上的；讀盤指的是從磁碟讀取數據的過程，落盤指的是從記憶體持久化到磁碟的過程

為什麼順序讀盤比隨機讀盤快

    這不是絕對的，多數情況下是這樣的；至於為什麼，這涉及到機械硬碟的硬體知識了，包括其組織結構，以及磁碟的讀盤過程，另外還需要瞭解 MySQL 數據的落盤與讀盤（頁為單位），內容太多，就不在本篇講了（其實是觸及到了樓主的知識盲區，樓主不會！）
    MariaDB 中說明瞭如下 3 個原因
    1、Rotating drives do not have to move the head back and forth
    2、One can take advantage of IO-prefetching done at various levels
    3、Each disk page will be read exactly once, which means we won't rely on disk cache (or buffer pool) to save us from reading the same page multiple times

　　使用場景

　　　　不是任何情況下 MySQL 都會使用 MRR 的，只是在某些情況下會用 MRR 來進行優化

摘自 Multi Range Read Optimization

　　　　MySQL 中的 NDB 也會用到 MRR，一般而言，我們無需關註，我們只關註上圖中的情況就行了

　　理論之後來點案例，完美！

　　range access

　　　　表 tbl_range_access 的 a 欄位上我們已經建了索引 i_a ，我們來個範圍查詢，看下執行計劃 EXPLAIN SELECT * FROM tbl_range_access WHERE a BETWEEN 4 AND 9;  如下

　　　　此時沒有用到 MRR，執行此查詢時，磁碟 IO 訪問模式將遵循下圖中的紅線

　　　　因為是 SELECT * ，所以通過索引 i_a 先找到主鍵 ID，然後通過主鍵 ID 回表（從聚簇索引）查詢完整記錄；a 在索引 i_a 中是有序的，但不保證主鍵在 i_a 中也是有序的（關於 MySQL 的索引，推薦大家去看：MySQL的索引），這就導致回表的過程是隨機 IO 

　　　　為什麼 MySQL 沒有採用 MRR 來保證回表的過程是順序 IO 呢？mrr-optimization 中有這麼一段話

Two optimizer_switch system variable flags provide an interface to the use of MRR optimization. The mrr flag controls whether MRR is enabled. If mrr is enabled (on), the mrr_cost_based flag controls whether the optimizer attempts to make a cost-based choice between using and not using MRR (on) or uses MRR whenever possible (off). By default, mrr is on and mrr_cost_based is on

　　　　 mrr 和 mrr_cost_based 的預設值是 on ；我簡單畫個圖，大家就明白這兩個開關的作用了

　　　　上面的示例之所以沒使用 MRR，是優化器覺得使用 MRR 反而提升了成本，還不如不使用

　　　　我們強制優化器使用 MRR：

-- 查看所有開關及其預設值
SELECT @@optimizer_switch;

-- mrr_cost_based設置成off,強制優化器使用 mrr
SET optimizer_switch='mrr_cost_based=off';

　　　　我們再來看看執行計劃是什麼樣的

　　　　此時用到 MRR，執行此查詢時，磁碟 IO 訪問模式將遵循下圖中的紅線

　　　　此時回表查詢的主鍵是有序的，會採用順序 IO 來讀取數據，從而提高查詢效率

MySQL 中有個 rowids_buffer，用來緩存從索引 i_a 中查詢到的數據記錄（包含欄位 a 和主鍵 ID），緩存滿了或者索引查完了，再對緩存中記錄按照主鍵 id 進行排序,再用排序後的主鍵 id 進行回表，使得回表查詢的過程是順序 IO

　　是不是感覺 MRR 有點像二級索引與主鍵的 JOIN 操作，有這感覺就對了，後面的 BKA 也就好理解了

BKA

　　BKA 全稱是： Batched Key Access ，是對  INL 優化後的一種聯表演算法，類似與 BNL 對 SNL 的優化，但又有些不同，具體我們往下看

　　先在表 tbl_user 新增一個索引  ALTER TABLE tbl_user ADD index i_aaa(user_name); ，此時查看執行計劃 EXPLAIN SELECT * FROM tbl_user_login_log tl LEFT JOIN tbl_user tu ON tl.user_name = tu.user_name; 如下圖

　　此時的聯表演算法就是 INL，因為表 tbl_user_login_log 的 user_name 是無索引的，那麼從表 tbl_user_login_log 取出的 user_name 的值就是無序的，再去關聯 tbl_user ，就會隨機匹配索引 i_aaa ，類似下圖

　　是不是有點類似於前面講過的回表隨機 IO ？

　　BKA 功能預設是關閉的（ batched_key_access=off ），開啟它 

SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

　　我們再來看執行計劃

 

 　　從tbl_user_login_log 查詢到的 user_name 的值先放到 join buffer，當 join buffer 滿了或者數據查完了，再對 join buffer 裡面的值進行排序，然後再去關聯 tbl_user ，此時就會順序匹配索引 i_aaa ，類似下圖

　　如果需要回表，那麼 MySQL 會按之前講到過的回表流程再優化一次

預設值的思考

　　MRR 相關的 3 個開關的預設值是這樣的 mrr=on,mrr_cost_based=on,batched_key_access=off 

　　 mrr=on 表示 mrr 功能是開啟的，開啟並不代表一定會使用，但不開啟則一定享受不到 mrr 帶來的優化； 

　　mrr_cost_based=on 表示優化器會基於成本考慮來決定是否使用 mrr，使用 mrr 反而使成本變高，那為什麼使用 mrr ？只有 mrr 確實是帶來了效率上的提升，那麼使用它才有意義，但是成本的計算又是優化器來完成的，而且是一個比較複雜的過程，一定能保證優化器的成本計算是準確的嗎？ 100%準確肯定不敢保證，但經過這麼多年的沉澱，絕大多數情況下，優化器的成本計算是準確的，所以 mrr_cost_based 建議就採用預設值 on ，由優化器來決定是否採用 mrr

　　 batched_key_access=off 表示預設不啟用 BKA，說實話，我沒太理解這麼做的意圖；既然是否使用 mrr 交由優化器來決定了，沒什麼不把是否使用 BKA 也交由優化器來決定？我能猜到的可能原因之一是 基本用不到 ，為什麼這麼說？ 我們回想下 BKA 會在什麼情況下使用： 驅動表在關聯的欄位上無索引，而被驅動表在關聯的欄位上有索引 ，而如果驅動表在關聯的欄位上有索引了，還有必要進行緩存、排序、再關聯被驅動表嗎 ？ 很顯然不必了，因為索引的欄位本來就是有序的了；而實際應用中，關聯的欄位，不管是驅動表還是被驅動表，往往是同時存在索引的，而不是一個存在索引而另一個不存在索引。這隻是我個人的猜想，望知道的大神能解惑下，小弟不勝感激！

總結

　　1、mrr 帶來的性能上的提升就是將隨機 IO 優化成 順序 IO，從而提高查詢效率

　　2、mrr 的使用場景比較有限， range access 和基於 req、eq_ref access 的 BKA，至於其他不適用的場景，我們可以結合 mrr 的特性分析出原因

　　3、mrr 相關的 3 個開關的預設值不建議改動，這可是 MySQL 這麼多年的經驗總結

　　　　有人可能會這樣說了，既然這 3 個開關不推薦改，那看與不看這篇博文沒什麼區別，額...，你好像說的對

　　4、關於 ON 和 WHERE，我只能說真的抱歉了，又要往後拖了，實在是不行，你，你......，你來打我呀


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

-Advertisement-
Play Games
更多相關文章
  • 手冊
    Linux初級指令 ls ——List ls 介紹 這是我學Linux的第一個命令，相信也是很多人學習Linux的第一個命令。ls全稱list. List information about the FILEs (the current directory by default). Sort ent ...
  • Android電源管理基礎知識整理
    前言 1. 待機、睡眠與休眠的區別？ 2. Android開發者官網當中提到“idle states”，該如何理解，這個狀態會對設備及我們的程式造成何種影響？ 3. 進入Doze模式中的idle狀態，我們的程式還能運行嗎？ 4. 手機睡眠之後，為何我們寫Alarm程式、來電顯示程式依舊會生效？ 如果 ...
  • 資料庫中間件分片演算法之hash
    前言 夜深人靜的時候，打開 ，點上一曲 ，帶上真無線藍牙耳機，瞬間燃到爆，鍵盤打字如飛倦意全無。 分片規則 這幾天有人問我，dble和MyCat到底有什麼不同。其實dble作為MyCAT的同門，吸收了MyCat的精華，同時也相應的做了一些減法。只支持MySQL顯得更加的純粹。所以選擇對比學習兩者我覺 ...
  • 本地服務開啟MySQL57提示本地電腦上MySQL服務啟動後停止。。。。
    1、首先以管理員身份啟動cmd，要不然服務禁止訪問。 2、然後在cmd中輸入 mysqld --remove mysql或者mysqld --remove mysql57來移除服務。 3、然後進入MySQL安裝目錄中找到data文件，清空其中全部文件。我的目錄是D:\MySQL\MySQL Serv ...
  • MySQL學習 2019-12-30
    啟動mysql服務： net start mysql 關閉mysql服務： net stop mysql cmd清屏： cls mysql -V 輸出版本信息並且退出 mysql -u 用戶名 mysql -p 密碼 mysql -P 埠號 mysql退出： exciting；quit；\q 登陸 ...
  • mysql將bin-log日誌文件轉為sql文件
    查看mysqlbinlog版本 mysqlbinlog -V [--version] 查看binlog日誌開啟狀態 show variables like '%log_bin%'; mysql打開bin-log日誌後，mysql資料庫的非查詢操作會將記錄保存到bin-log文件中。一般bin-log ...
  • MySQL在Windows中設置環境變數
    在桌面選擇“電腦”的圖標(或者是我的電腦)，右鍵-->屬性-->點擊“高級系統設置” 然後選擇 高級 選項點擊環境變數 然後點擊新建-> 變數名為MYSQL_HOME 變數值為mysql的安裝目錄(一定要在系統變數下新建變數) 然後編輯系統變數中的PATH變數點擊編輯,添加剛纔設置的變數名%MYS ...
  • SQL Server資料庫、表、數據類型基本概念
    一、SQL Server的數據存儲結構 SQL Server是一個資料庫管理系統，需要以有效方式存儲高容量數據。要更好地理解SQL Server處理數據的方式，就需要瞭解數據的存儲結構。 1、文件類型 資料庫在磁碟上是以文件為單位存儲的，由數據文件和事務日誌文件組成。一個資料庫至少應該包含一個數據文 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類