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

開心一刻

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

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

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

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

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

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

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

前情回顧

　　神奇的 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;

　　表 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，我只能說真的抱歉了，又要往后拖了，實在是不行，你，你......，你來打我呀

標籤：MySQL

上一篇：MySQL優化、鎖

下一篇：MySQL在Windows中設定環境變數