偷偷摘套内射激情视频,久久精品99国产国产精,中文字幕无线乱码人妻,中文在线中文a,性爽19p

如何干涉MySQL優(yōu)化器使用hash join?

數(shù)據(jù)庫 MySQL
數(shù)據(jù)庫的優(yōu)化器相當于人類的大腦,大部分時候都能做出正確的決策,制定正確的執(zhí)行計劃,走出一條高效的路,但是它畢竟是基于某些固定的規(guī)則、算法來做的判斷,有時候并沒有我們?nèi)四X思維靈活,當我們確定優(yōu)化器選擇執(zhí)行計劃錯誤時該怎么辦呢,語句上加hint,提示它選擇哪條路是一種常見的優(yōu)化方法。

前言

數(shù)據(jù)庫的優(yōu)化器相當于人類的大腦,大部分時候都能做出正確的決策,制定正確的執(zhí)行計劃,走出一條高效的路,但是它畢竟是基于某些固定的規(guī)則、算法來做的判斷,有時候并沒有我們?nèi)四X思維靈活,當我們確定優(yōu)化器選擇執(zhí)行計劃錯誤時該怎么辦呢,語句上加hint,提示它選擇哪條路是一種常見的優(yōu)化方法。

我們知道Oracle提供了比較靈活的hint提示來指示優(yōu)化器在多表連接時選擇哪種表連接方式,比如use_nl,no_use_nl控制是否使用Nest Loop Join,use_hash,no_use_hash控制是否使用hash join。

但是MySQL長期以來只有一種表連接方式,那就是Nest Loop Join,直到MySQL8.0.18版本才出現(xiàn)了hash join, 所以MySQL在控制表連接方式上沒有提供那么多豐富的hint給我們使用,hash_join與no_hash_join的hint只是驚鴻一瞥,只在8.0.18版本存在,8.0.19及后面的版本又將這個hint給廢棄了,那如果我們想讓兩個表做hash join該怎么辦呢?

實驗

我們來以MySQL8.0.25的單機環(huán)境做一個實驗。建兩個表,分別插入10000行數(shù)據(jù),使用主鍵做這兩個表的關聯(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 ;

查詢一下兩表使用主鍵字段關聯(lián)查詢時實際的執(zhí)行計劃,如下圖所示:

圖片

查詢一下兩表使用非索引字段關聯(lián)查詢時實際的執(zhí)行計劃,如下圖所示:

圖片

從執(zhí)行計劃可以看出,被驅動表的關聯(lián)字段上有索引,優(yōu)化器在選擇表連接方式時會傾向于選擇Nest Loop Join,當沒有可用索引時傾向于選擇hash join。

基于這一點那我們可以使用no_index提示來禁止語句使用關聯(lián)字段的索引。

圖片

從上面的執(zhí)行計劃可以看出使用no_index提示后,優(yōu)化器選擇了使用hash join。

當索引的選擇性不好時,優(yōu)化器選擇使用索引做Nest Loop Join是效率是很低的。

我們將實驗的兩個表中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);

當我們執(zhí)行sql :

select t1.*,t2.* from t1 join t2 on t1.c1=t2.c1;

這個查詢結果會返回大量數(shù)據(jù),被驅動表的關聯(lián)字段c1列的索引選擇性差,此時選擇hash join是更明智的選擇,但是優(yōu)化器會選擇走Nest Loop Join。我們可以通過實驗驗證一下hash join 與 Nest Loop Join的性能差異。

圖片

可以看出使用hash join的耗時是使用Nest Loop Join的1/6,但是優(yōu)化器根據(jù)成本估算時,使用Nest Loop Join的成本要比使用hash join的成本低很多,所以會去選擇Nest Loop Join,這個時候就需要加上hint 提示禁止使用關聯(lián)字段的索引,被驅動表上每次都全表掃描的代價是很高的,這樣優(yōu)化器估算后就會選擇走hash join。

MySQL官方文檔里提到用BNL,NO_BNL的hint提示來影響hash join的優(yōu)化,但是經(jīng)過實驗證明,在表連接關聯(lián)字段上沒有可用索引時,優(yōu)化器估算成本后不會對被驅動表使用BNL全表掃描的方式做嵌套循環(huán)連接,而是會選擇使用hash join,那這樣NO_BNL在這個場景下就沒有用武之地了。

那么既然不用這個索引,把這個索引去掉不就可以了嗎?為什么非要使用no_index的hint提示呢,我們要知道業(yè)務使用的場景何其多,此處不用,別處使用了這個索引效率可能會有大的提升啊,這個時候就凸顯了hint的優(yōu)勢,只需要控制此語句的使用就好了。

總結

Nest Loop Join有其優(yōu)勢,它是response最快的連接方式,適用于返回數(shù)據(jù)量小的場景。當兩個大表連接,返回大量數(shù)據(jù),且關聯(lián)字段的索引比較低效時,使用hash join就會比較高效,我們可以使用no_index的hint提示禁用關聯(lián)字段的低效索引,促使優(yōu)化器選擇hash join。

責任編輯:華軒 來源: GreatSQL社區(qū)
相關推薦

2017-08-31 16:17:35

SQL優(yōu)化器原理

2010-04-19 13:43:38

Oracle分析函數(shù)

2024-12-10 00:00:10

MySQLJOIN算法

2025-01-15 08:05:06

MySQLLEFT JOIN數(shù)據(jù)庫

2015-02-03 14:34:52

庫克蘋果安卓

2009-05-19 14:34:52

Oraclehash優(yōu)化

2025-04-29 10:24:01

大數(shù)據(jù)StarRocksJOIN

2010-02-03 09:26:52

Linux Mysql

2012-12-17 09:54:08

2022-10-30 10:24:43

Join優(yōu)化Runtime

2015-08-19 09:15:01

設計老板干涉

2023-10-24 15:29:54

MySQL

2019-04-25 14:25:24

Spring Bootif elseJava

2010-05-21 14:01:23

MySQL數(shù)據(jù)庫

2010-05-21 17:30:28

2020-02-20 15:19:56

JavaSQL注入

2010-05-26 16:09:09

MySQL Repli

2025-07-01 08:43:48

MySQL 8.0join查詢

2023-11-28 09:31:55

MySQL算法

2025-06-04 07:48:46

點贊
收藏

51CTO技術棧公眾號