五丁香天堂美女,丝袜诱惑国产精品

大家好，我是咔咔 不期速成，日拱一卒

通過上期文章知道了在MySQL中存在三種join的算法，分別為NLJ、BNLJ、BNL，總結(jié)來說分為索引嵌套循環(huán)連接、緩存塊嵌套循環(huán)連接、粗暴循環(huán)連接。

另外還知道了一個(gè)新的概念join_buffer，作用就是把關(guān)聯(lián)表的數(shù)據(jù)全部讀入join_buffer中，然后從join_buffer中一行一行的拿數(shù)據(jù)去被驅(qū)動(dòng)表中查詢。由于是在內(nèi)存中獲取數(shù)據(jù)，因此效率還是會(huì)有所提升。

同時(shí)在上期文章中遇到了一個(gè)陌生的概念hash_join，在上期中沒有詳細(xì)說明，本期會(huì)進(jìn)行詳述。

3b028aa329a84a00ab652d15b894dd8f.png

一、Multi-Range Read優(yōu)化

在介紹本期主題時(shí)先來了解一個(gè)知識(shí)點(diǎn)Multi-Range Read，主要的作用是盡量讓順序讀盤，在任何領(lǐng)域只要是有順序的都會(huì)有一定的性能提升。

比如MySQL的索引，現(xiàn)在你應(yīng)該知道索引天生具有有序性從而避免服務(wù)器對(duì)數(shù)據(jù)再次排序和建立臨時(shí)表的問題。

接下來使用一個(gè)案例來實(shí)操一下這個(gè)優(yōu)化是怎么做的

創(chuàng)建join_test1、join_test2兩張表

CREATE TABLE `join_test1` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `a` int(11) unsigned NOT NULL,
 `b` int(11) unsigned NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `join_test2` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `a` int(11) unsigned NOT NULL,
 `b` int(11) unsigned NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

給兩張表添加一些數(shù)據(jù)，用于案例演示

drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into join_test1 (a,b) values ( 1001-i, i);
    set i=i+1;
  end while;
  
  set i=1;
  while(i<=1000000)do
    insert into join_test2 (a,b)  values (i, i);
    set i=i+1;
  end while;

end;;
delimiter ;
call idata();

表join_test1的字段a上存在索引的，那么在查詢時(shí)就會(huì)使用該索引。

執(zhí)行流程大致為獲取到字段a所有的值，然后根據(jù)a的值一行一行的進(jìn)行回表到主鍵索引上獲取數(shù)據(jù)

現(xiàn)在的情況是如果隨著a的值遞增順序查詢的話，id的值就會(huì)變相的為倒敘，雖然看起來是根據(jù)主鍵ID連續(xù)倒敘的，但在生產(chǎn)環(huán)境下肯定不是連續(xù)的，就會(huì)造成隨機(jī)訪問，那就肯定會(huì)造成性能變差。

為什么說隨機(jī)訪問會(huì)影響性能？

MySQL的索引天生具有有序性，同時(shí)MySQL也同樣借鑒了局部性原理，局部性原理是數(shù)據(jù)和程序都默認(rèn)有聚集成群的傾向，在訪問到一行數(shù)據(jù)后，會(huì)有極大可能性再次訪問到這條數(shù)據(jù)或這條數(shù)據(jù)相鄰的數(shù)據(jù)。

現(xiàn)在你應(yīng)該知道了MySQL在讀取數(shù)據(jù)時(shí)并不是只讀查詢的數(shù)據(jù)，默認(rèn)會(huì)讀取16kb的數(shù)據(jù)，這個(gè)值是根據(jù)innodb_page_size決定的。

因此順序查詢是非?？斓模且?yàn)椴挥妹看味纪ㄟ^執(zhí)行器獲取數(shù)據(jù)，而是直接在內(nèi)存中獲取，但若訪問變?yōu)殡S機(jī)性就會(huì)每次通過執(zhí)行器進(jìn)行獲取數(shù)據(jù)，所以這才是性能變差的原因。

MRR的作用

說了這么多現(xiàn)在你應(yīng)該知道了MRR的作用就是把查詢變?yōu)橹麈IID的遞增查詢，對(duì)磁盤的讀盡可能的接近順序讀，就可以提升性能。

因此，執(zhí)行語句的執(zhí)行流程就會(huì)變成這樣

先根據(jù)索a，獲取到所有滿足條件的數(shù)據(jù)，并且將主鍵id的值放入read_rnd_buffer中
在read_rnd_buffer中把id的值進(jìn)行正序排序
再根據(jù)排序后得主鍵ID值，依次到主鍵索引上獲取數(shù)據(jù)，并返回結(jié)果集

如何開啟read_rnd_buffer

read_rnd_buffer的大小是由read_rnd_buffer_size參數(shù)控制的，默認(rèn)值為256kb，但你要知道的是對(duì)于MRR的優(yōu)化在優(yōu)化器的判斷策略中會(huì)更傾向于不使用，如果要使用則需要進(jìn)行配置修改即可。

set optimizer_switch="mrr_cost_based=off"

mrr默認(rèn)值
[圖片上傳失敗...(image-78b6d0-1650937161200)]

read_rnd_buffer存不下怎么辦？

回憶下在上期中提到的join_buffer不夠用是怎么處理的，會(huì)把上次讀取的數(shù)據(jù)從buffer中清空，再放入剩下的數(shù)據(jù)，在MySQL中對(duì)于存儲(chǔ)結(jié)果集的buffer內(nèi)存不夠情況下大多數(shù)都是這么處理的。

使用了read_rnd_buffer后的SQL執(zhí)行流程就變成了這樣

[圖片上傳失敗...(image-dc0f29-1650937161200)]

explain的結(jié)果顯示

[圖片上傳失敗...(image-2c0161-1650937161200)]

注意點(diǎn)

假設(shè)現(xiàn)在把查詢范圍擴(kuò)大，看一下會(huì)有什么變化

[圖片上傳失敗...(image-57d885-1650937161200)]

可以看到當(dāng)把范圍擴(kuò)大至接近全表數(shù)據(jù)時(shí)，會(huì)不再使用索引a從而進(jìn)行了全表掃描，也就無法再使用mrr優(yōu)化了

因此想要使用MRR進(jìn)行提升性能是基于兩個(gè)非常重要的點(diǎn)，一個(gè)是在索引上進(jìn)行范圍查詢，另一個(gè)就是必須能使用上索引，當(dāng)然這個(gè)索引要是范圍查詢的列

二、Nested-Loop Join優(yōu)化

快一個(gè)月沒更文了，對(duì)Nested-Loop Join的算法還能回憶多少，SQL的執(zhí)行流程大致如下：

[圖片上傳失敗...(image-ce49df-1650937161200)]

從join_test1表讀取一行數(shù)據(jù)R
從R中取id字段到表join_test2去查找索引a，并通過主鍵ID獲取到滿足的行
取出join_test2中滿足條件的行，跟R組成一行
重復(fù)前三個(gè)步驟，直到表join_test1滿足條件的數(shù)據(jù)掃描結(jié)束

NLJ算法的邏輯就是從驅(qū)動(dòng)表取一行數(shù)據(jù)后就直接到被驅(qū)動(dòng)表中做join操作，對(duì)于驅(qū)動(dòng)表來說就變成了每次都匹配一個(gè)值，這時(shí)就不滿足MRR優(yōu)化的條件了。

通過上期文章，現(xiàn)在你應(yīng)該知道了join_buffer在BNL算法中的作用，但在NLJ算法中并沒有使用。

那想辦法把驅(qū)動(dòng)表的數(shù)據(jù)批量傳給被驅(qū)動(dòng)表進(jìn)行join操作不就行了？

沒錯(cuò)，MySQL團(tuán)隊(duì)在5.6版本引入了此方案，在驅(qū)動(dòng)表中取出一部分?jǐn)?shù)據(jù)，放到臨時(shí)內(nèi)存，這個(gè)臨時(shí)內(nèi)存就是上期的join_buffer。

那么執(zhí)行流程圖就會(huì)變成這樣

這里需要注意沒有把索引a在read_rnd_buffer中的流程畫出來，如果不理解就到上文去看那副圖哈！

[圖片上傳失敗...(image-47129-1650937161200)]

上圖中，我們依然查詢了1000條數(shù)據(jù)，那么join_buffer就會(huì)存著1000條數(shù)據(jù)，如果存不下就會(huì)分段進(jìn)行，直到執(zhí)行結(jié)束。

對(duì)于NLJ算法的優(yōu)化官方也給起來了一個(gè)名為Batched Key Access

BKA算法的啟用

既然要使用MRR優(yōu)化，那就要開啟MRR，開啟MRR的同時(shí)還要開啟batched_key_access=on即可

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

三、Block Nested-Loop Join算法優(yōu)化

非常簡(jiǎn)單的優(yōu)化就是在被驅(qū)動(dòng)表上添加索引，這時(shí)BNL的算法就自然而然的變?yōu)锽KA算法了

select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;

[圖片上傳失敗...(image-ddb49c-1650937161200)]

這條SQL在join_test2上只查詢了2000行數(shù)據(jù)，如果你的MySQL機(jī)器對(duì)內(nèi)存不那么看重的話直接給字段b加個(gè)索引即可。

反之，就需要另辟奇徑了

再來復(fù)習(xí)下BNL算法的執(zhí)行流程

取出join_test1的所有數(shù)據(jù)，存儲(chǔ)join_buffer中
掃描join_test2用每行數(shù)據(jù)跟join_buffer中的數(shù)據(jù)進(jìn)行對(duì)比，不滿足跳過，滿足存儲(chǔ)結(jié)果集

由于被驅(qū)動(dòng)表字段b是沒有索引的，因此從join_buffer中讀取出來的每條數(shù)據(jù)都要對(duì)join_test2進(jìn)行全表掃描。

案例中join_test2表共100W數(shù)據(jù)，那么需要掃描的行數(shù)就是1000*100W = 10億次，只需要2000條數(shù)據(jù)卻要執(zhí)行10億次，這個(gè)性能可想而知。

這時(shí)，我們就可以使用奇徑臨時(shí)表來解決這個(gè)問題，實(shí)現(xiàn)思路大致如下

先把join_test2中滿足條件的數(shù)據(jù)存放在臨時(shí)表中tmp_join_test2中
此時(shí)臨時(shí)表的數(shù)據(jù)只有條件范圍的2000數(shù)據(jù)，因此是完全可以給字段b添加索引的
最后再讓join_buffer跟tmp_join_test2做join操作

對(duì)應(yīng)的SQL操作如下

create temporary table tmp_join_test2 (id int primary key, a int, b int, index(b))engine=innodb;
insert into tmp_join_test2 select * from join_test2 where b>=1 and b<=2000;
explain select * from join_test1 join tmp_join_test2 on (join_test1.b=tmp_join_test2.b);

掃描行數(shù)

insert 是對(duì)表join_test2進(jìn)行的全表掃描，此時(shí)掃描行數(shù)為100W行

join_test1進(jìn)行全表掃描一次掃描行數(shù)為1000行

每次join操作是一條數(shù)據(jù)，共計(jì)1000次，掃描行數(shù)為1000行

使用了臨時(shí)表后總體掃描行數(shù)從10億次到了100W+2000次，執(zhí)行查詢的結(jié)果返回預(yù)計(jì)都不到一秒時(shí)間。

總結(jié)

不管是使用BKA算法還是使用臨時(shí)表都有一個(gè)共同點(diǎn)，那就是讓被驅(qū)動(dòng)表上能用上索引來主動(dòng)觸發(fā)BKA算法，從而提升性能。

四、Hash join

大家還記得這幅圖吧！上期文章中復(fù)現(xiàn)Block Nested-Loop Join算法呢！結(jié)果返回了一個(gè)hash_join，上期并沒有說明。

因?yàn)閔ash_join算法是在MySQL8.0.18才有的

[圖片上傳失敗...(image-93d08a-1650937161200)]

hash_join生效的前提是被驅(qū)動(dòng)表join的字段沒有索引，在MySQL8.0.18中還有一個(gè)約束就是條件對(duì)等，例如案例中的join_test1.b=tmp_join_test2.b

但在8.0.20中取消了條件對(duì)等的約束，并全面支持non-equi-join，Semijoin，Antijoin，Left outer join/Right outer join

其實(shí)hash_join算法的實(shí)現(xiàn)原理很簡(jiǎn)單

驅(qū)動(dòng)表中的join字段進(jìn)行計(jì)算hash值
在內(nèi)存中創(chuàng)建一個(gè)hash_table，把驅(qū)動(dòng)表所有的hash值存放進(jìn)去
獲取被驅(qū)動(dòng)表中滿足條件的數(shù)據(jù)，例如join_test2中的select * from join_test2 where b>=1 and b<=20002000行數(shù)據(jù)
把這2000行數(shù)據(jù)，一行一行的跟hash_table中的數(shù)據(jù)進(jìn)行對(duì)比，條件滿足的數(shù)據(jù)作為結(jié)果集進(jìn)行返回

可以看到hash_join算法的掃描行數(shù)跟臨時(shí)表大差不差，那么為什么MySQL會(huì)默認(rèn)使用hash_join這種算法呢？這個(gè)問題就要留給大家去深究了

五、總結(jié)

本期主要分享了NLJ、BNJ的算法優(yōu)化

在這些優(yōu)化中，hash_join在MySQL8.0.18中已經(jīng)內(nèi)置支持了，但低版本的還是默認(rèn)為BKA算法

建議給被驅(qū)動(dòng)表需要join字段加上索引，把BNL算法轉(zhuǎn)為BKA或者h(yuǎn)ash_join算法

同時(shí)還給大家提供了一個(gè)臨時(shí)表的方案，臨時(shí)表在開發(fā)過程中是非常容易忽略的一個(gè)優(yōu)化點(diǎn)，可以在適當(dāng)?shù)沫h(huán)境下學(xué)會(huì)使用臨時(shí)表

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

MySQL對(duì)JOIN做了那些不為人知的優(yōu)化？

MySQL對(duì)JOIN做了那些不為人知的優(yōu)化？

一、Multi-Range Read優(yōu)化

二、Nested-Loop Join優(yōu)化

三、Block Nested-Loop Join算法優(yōu)化

四、Hash join

五、總結(jié)

推薦閱讀

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

MySQL對(duì)JOIN做了那些不為人知的優(yōu)化？

一、Multi-Range Read優(yōu)化

二、Nested-Loop Join優(yōu)化

三、Block Nested-Loop Join算法優(yōu)化

四、Hash join

五、總結(jié)

推薦閱讀

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

MySQL對(duì)JOIN做了那些不為人知的優(yōu)化？

一、Multi-Range Read優(yōu)化

三、Block Nested-Loop Join算法優(yōu)化

四、Hash join

五、總結(jié)