干涉MySQL優(yōu)化器使用hash?join的方法
GreatSQL社區(qū)原創(chuàng)內(nèi)容未經(jīng)授權(quán)不得隨意使用,轉(zhuǎn)載請(qǐng)聯(lián)系小編并注明來源。GreatSQL是MySQL的國(guó)產(chǎn)分支版本,使用上與MySQL一致。
前言
數(shù)據(jù)庫的優(yōu)化器相當(dāng)于人類的大腦,大部分時(shí)候都能做出正確的決策,制定正確的執(zhí)行計(jì)劃,走出一條高效的路,但是它畢竟是基于某些固定的規(guī)則、算法來做的判斷,有時(shí)候并沒有我們?nèi)四X思維靈活,當(dāng)我們確定優(yōu)化器選擇執(zhí)行計(jì)劃錯(cuò)誤時(shí)該怎么辦呢,語句上加hint,提示它選擇哪條路是一種常見的優(yōu)化方法。
我們知道Oracle提供了比較靈活的hint提示來指示優(yōu)化器在多表連接時(shí)選擇哪種表連接方式,比如use_nl
,no_use_nl
控制是否使用Nest Loop Join,use_hash
,no_use_hash
控制是否使用hash join。
但是MySQL長(zhǎng)期以來只有一種表連接方式,那就是Nest Loop Join
,直到MySQL8.0.18版本才出現(xiàn)了hash join, 所以MySQL在控制表連接方式上沒有提供那么多豐富的hint給我們使用,hash_join
與no_hash_join
的hint只是驚鴻一瞥,只在8.0.18版本存在,8.0.19及后面的版本又將這個(gè)hint給廢棄了,那如果我們想讓兩個(gè)表做hash join該怎么辦呢?
實(shí)驗(yàn)
我們來以MySQL8.0.25的單機(jī)環(huán)境做一個(gè)實(shí)驗(yàn)。建兩個(gè)表,分別插入10000行數(shù)據(jù),使用主鍵做這兩個(gè)表的關(guān)聯(lián)查詢。
create table t1(id int primary key,c1 int,c2 int); create table t2(id int primary key,c1 int,c2 int); delimiter // CREATE PROCEDURE p_test() BEGIN declare i int; set i=1; while i<10001 do insert into t1 values(i,i,i); insert into t2 values(i,i,i); SET i = i + 1; end while; END; // delimiter ;
查詢一下兩表使用主鍵字段關(guān)聯(lián)查詢時(shí)實(shí)際的執(zhí)行計(jì)劃,如下圖所示:
查詢一下兩表使用非索引字段關(guān)聯(lián)查詢時(shí)實(shí)際的執(zhí)行計(jì)劃,如下圖所示:
從執(zhí)行計(jì)劃可以看出,被驅(qū)動(dòng)表的關(guān)聯(lián)字段上有索引,優(yōu)化器在選擇表連接方式時(shí)會(huì)傾向于選擇Nest Loop Join,當(dāng)沒有可用索引時(shí)傾向于選擇hash join。
基于這一點(diǎn)那我們可以使用no_index
提示來禁止語句使用關(guān)聯(lián)字段的索引。
從上面的執(zhí)行計(jì)劃可以看出使用no_index提示后,優(yōu)化器選擇了使用hash join。
當(dāng)索引的選擇性不好時(shí),優(yōu)化器選擇使用索引做Nest Loop Join是效率是很低的。
我們將實(shí)驗(yàn)的兩個(gè)表中c1列的數(shù)據(jù)做一下更改,使其選擇性變差,并在c1列上建普通索引。
update t1 set c1=1 where id<5000; update t2 set c1=1 where id<5000; create index idx_t1 on t1(c1); create index idx_t2 on t2(c1);
當(dāng)我們執(zhí)行sql :
select t1.*,t2.* from t1 join t2 on t1.c1=t2.c1;
這個(gè)查詢結(jié)果會(huì)返回大量數(shù)據(jù),被驅(qū)動(dòng)表的關(guān)聯(lián)字段c1列的索引選擇性差,此時(shí)選擇hash join是更明智的選擇,但是優(yōu)化器會(huì)選擇走Nest Loop Join。我們可以通過實(shí)驗(yàn)驗(yàn)證一下hash join 與 Nest Loop Join的性能差異。
可以看出使用hash join的耗時(shí)是使用Nest Loop Join的1/6,但是優(yōu)化器根據(jù)成本估算時(shí),使用Nest Loop Join的成本要比使用hash join的成本低很多,所以會(huì)去選擇Nest Loop Join,這個(gè)時(shí)候就需要加上hint 提示禁止使用關(guān)聯(lián)字段的索引,被驅(qū)動(dòng)表上每次都全表掃描的代價(jià)是很高的,這樣優(yōu)化器估算后就會(huì)選擇走h(yuǎn)ash join。
MySQL官方文檔里提到用BNL
,NO_BNL
的hint提示來影響hash join的優(yōu)化,但是經(jīng)過實(shí)驗(yàn)證明,在表連接關(guān)聯(lián)字段上沒有可用索引時(shí),優(yōu)化器估算成本后不會(huì)對(duì)被驅(qū)動(dòng)表使用BNL全表掃描的方式做嵌套循環(huán)連接,而是會(huì)選擇使用hash join,那這樣NO_BNL在這個(gè)場(chǎng)景下就沒有用武之地了。
那么既然不用這個(gè)索引,把這個(gè)索引去掉不就可以了嗎?為什么非要使用no_index的hint提示呢,我們要知道業(yè)務(wù)使用的場(chǎng)景何其多,此處不用,別處使用了這個(gè)索引效率可能會(huì)有大的提升啊,這個(gè)時(shí)候就凸顯了hint的優(yōu)勢(shì),只需要控制此語句的使用就好了。
總結(jié)
Nest Loop Join有其優(yōu)勢(shì),它是response最快的連接方式,適用于返回?cái)?shù)據(jù)量小的場(chǎng)景。當(dāng)兩個(gè)大表連接,返回大量數(shù)據(jù),且關(guān)聯(lián)字段的索引比較低效時(shí),使用hash join就會(huì)比較高效,我們可以使用no_index的hint提示禁用關(guān)聯(lián)字段的低效索引,促使優(yōu)化器選擇hash join。
到此這篇關(guān)于MySQL優(yōu)化器使用hash join的的文章就介紹到這了,更多相關(guān)MySQL優(yōu)化器使用hash join內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
update.where無索引導(dǎo)致MySQL死鎖問題解決
這篇文章主要為大家介紹了update.where無索引導(dǎo)致MySQL死鎖問題解決,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2022-11-11mysql中l(wèi)eft join設(shè)置條件在on與where時(shí)的用法區(qū)別分析
這篇文章主要介紹了mysql中l(wèi)eft join設(shè)置條件在on與where時(shí)的用法區(qū)別,結(jié)合實(shí)例形式分析了mysql中l(wèi)eft join設(shè)置條件在on與where時(shí)的相關(guān)用法區(qū)別與操作注意事項(xiàng),需要的朋友可以參考下2020-02-02MySQL數(shù)據(jù)庫列的增刪改實(shí)現(xiàn)方法
這篇文章主要介紹了MySQL數(shù)據(jù)庫列的增刪改實(shí)現(xiàn)方法,結(jié)合實(shí)例形式分析了mysql數(shù)據(jù)庫針對(duì)列的增加、修改、刪除等相關(guān)操作sql命令及使用技巧,需要的朋友可以參考下2019-03-03MySQL日志專項(xiàng)之redo log和undo log介紹
MySQL日志記錄了MySQL數(shù)據(jù)庫日常操作和錯(cuò)誤信息,MySQL有不同類型的日志文件(各自存儲(chǔ)了不同類型的日志),從日志當(dāng)中可以查詢到MySQL數(shù)據(jù)庫的運(yùn)行情況、用戶操作、錯(cuò)誤信息等2022-08-08mysql中關(guān)于between和in的區(qū)別
這篇文章主要介紹了mysql中關(guān)于between和in的區(qū)別說明,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-07-07MySQL數(shù)據(jù)同步Elasticsearch的4種方案
本文主要介紹了MySQL數(shù)據(jù)同步Elasticsearch的4種方案,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-03-03