神奇的SQL之聯(lián)表細(xì)節(jié)：MySQL JOIN的執(zhí)行過程（二）

點擊關(guān)注上方“SQL數(shù)據(jù)庫開發(fā)”，

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

神奇的SQL之聯(lián)表細(xì)節(jié)：MySQL JOIN的執(zhí)行過程（一）中，我們講到了 JOIN 的部分內(nèi)容，像：驅(qū)動表、JOIN 大致流程等。還沒看的小伙伴趕緊去補課！下面是這篇的主要內(nèi)容：

-- 查看版本和存儲引擎SELECT VERSION();
SHOW ENGINES;
SHOW VARIABLES LIKE '%storage_engine%';-- 表創(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)建時間',
  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 '創(chuàng)建時間', 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(),'武林東南西北四大世家之北世家慕容長明的獨生女兒，生得玲瓏剔透，粉雕玉琢，脾氣卻是剛烈無比，又喜著火紅，所以人送綽號“火鳳凰”，是除天星樓薛沉香之外的武林第二大美女'),
('萇婷',0,NOW(), NOW(),'當(dāng)今皇上最寵愛的侄女，北王府的郡主，腰肢纖細(xì)，遍體羅綺，眉若墨畫，唇點櫻紅；雖無沉香之雅重，蘭娟之熱烈，卻別現(xiàn)出一種空靈'),
('柳含姻',0,NOW(), NOW(),'武林四絕之一的添愁仙子董婉婉的徒弟，體態(tài)窈窕，姿容秀麗，真?zhèn)€是秋水為神玉為骨，芙蓉如面柳如腰，眉若墨畫，唇若點櫻，不弱西子半分，更勝玉環(huán)一籌; 搖紅樓、聽雨軒，琵琶一曲值千金!'),
('李凝雪',0,NOW(), NOW(),'李相國的女兒，神采奕奕，英姿颯爽，愛憎分明'),
('周遺夢',0,NOW(), NOW(),'音神傳人，湘妃竹琴的擁有者，云髻高盤，穿了一身黑色蟬翼紗衫，愈覺得冰肌玉骨，粉面櫻唇，格外嬌艷動人'),
('葉留痕',0,NOW(), NOW(),'圣域圣女，膚白如雪，白衣飄飄，宛如仙女一般，微笑中帶著說不出的柔和之美'),
('郭疏影',0,NOW(), NOW(),'揚灰右使的徒弟，秀發(fā)細(xì)眉，玉肌豐滑，嬌潤脫俗'),
('鐘鈞天',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'),
('周遺夢', '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 的數(shù)據(jù)要多一點，像上面示例只有 11 條記錄，那么即使 a 字段上有索引， SELECT * FROM tbl_range_access WHERE a BETWEEN 4 AND 9; 也不會走索引，執(zhí)行計劃如下：

數(shù)據(jù)太少，優(yōu)化器覺得走索引，然后回表查詢數(shù)據(jù)，還不如直接走聚簇索引全表查詢來的快，所以沒有選擇走索引 i_a 

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

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

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

當(dāng)前絕大多少情況下，MySQL 的數(shù)據(jù)是存在機械硬盤（SATA 盤）上的，極少數(shù)情況下是存在固態(tài)硬盤（SSD）上的；讀盤指的是從磁盤讀取數(shù)據(jù)的過程，落盤指的是從內(nèi)存持久化到磁盤的過程

這不是絕對的，多數(shù)情況下是這樣的；至于為什么，這涉及到機械硬盤的硬件知識了，包括其組織結(jié)構(gòu)，以及磁盤的讀盤過程，另外還需要了解 MySQL 數(shù)據(jù)的落盤與讀盤（頁為單位），內(nèi)容太多，就不在本篇講了。

1. 使用場景

不是任何情況下 MySQL 都會使用 MRR 的，只是在某些情況下會用 MRR 來進(jìn)行優(yōu)化。

摘自 Multi Range Read Optimization

MySQL 中的 NDB 也會用到 MRR，一般而言，我們無需關(guān)注，我們只關(guān)注上圖中的情況就行了。理論之后來點案例，完美！

2. range access

表 tbl_range_access 的 a 字段上我們已經(jīng)建了索引 i_a ，我們來個范圍查詢，看下執(zhí)行計劃 EXPLAIN SELECT * FROM tbl_range_access WHERE a BETWEEN 4 AND 9;  如下

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

因為是 SELECT * ，所以通過索引 i_a 先找到主鍵 ID，然后通過主鍵 ID 回表（從聚簇索引）查詢完整記錄；a 在索引 i_a 中是有序的，但不保證主鍵在 i_a 中也是有序的（關(guān)于 MySQL 的索引，推薦大家去看：MySQL的索引），這就導(dǎo)致回表的過程是隨機 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 的默認(rèn)值是 on ；我簡單畫個圖，大家就明白這兩個開關(guān)的作用了

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

我們強制優(yōu)化器使用 MRR：

-- 查看所有開關(guān)及其默認(rèn)值 
SELECT @@optimizer_switch; 

-- mrr_cost_based設(shè)置成off,強制優(yōu)化器使用 
mrr SET optimizer_switch='mrr_cost_based=off';

我們再來看看執(zhí)行計劃是什么樣的

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

此時回表查詢的主鍵是有序的，會采用順序 IO 來讀取數(shù)據(jù)，從而提高查詢效率。

MySQL 中有個 rowids_buffer，用來緩存從索引 i_a 中查詢到的數(shù)據(jù)記錄（包含字段 a 和主鍵 ID），緩存滿了或者索引查完了，再對緩存中記錄按照主鍵 id 進(jìn)行排序，再用排序后的主鍵 id 進(jìn)行回表，使得回表查詢的過程是順序 IO

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

BKA 全稱是：Batched Key Access ，是對INL優(yōu)化后的一種聯(lián)表算法，類似與 BNL 對 SNL 的優(yōu)化，但又有些不同，具體我們往下看

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

此時的聯(lián)表算法就是 INL，因為表 tbl_user_login_log 的 user_name 是無索引的，那么從表 tbl_user_login_log 取出的 user_name 的值就是無序的，再去關(guān)聯(lián) tbl_user ，就會隨機匹配索引 i_aaa ，類似下圖

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

BKA 功能默認(rèn)是關(guān)閉的（ batched_key_access=off ），開啟它 

我們再來看執(zhí)行計劃

從tbl_user_login_log 查詢到的 user_name 的值先放到 join buffer，當(dāng) join buffer 滿了或者數(shù)據(jù)查完了，再對 join buffer 里面的值進(jìn)行排序，然后再去關(guān)聯(lián) tbl_user ，此時就會順序匹配索引 i_aaa ，類似下圖

如果需要回表，那么 MySQL 會按之前講到過的回表流程再優(yōu)化一次

MRR 相關(guān)的 3 個開關(guān)的默認(rèn)值是這樣的 mrr=on,mrr_cost_based=on,batched_key_access=off 

作者：青石路

來源：博客園

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