技術文章 » 干涉MySQL優化器使用hash join的方法

干涉MySQL優化器使用hash join的方法

2022-09-15 18:00:49

推薦學習：

GreatSQL社群原創內容未經授權不得隨意使用，轉載請聯絡小編並註明來源。GreatSQL是MySQL的國產分支版本，使用上與MySQL一致。

前言

資料庫的優化器相當於人類的大腦，大部分時候都能做出正確的決策，制定正確的執行計劃，走出一條高效的路，但是它畢竟是基於某些固定的規則、演演算法來做的判斷，有時候並沒有我們人腦思維靈活，當我們確定優化器選擇執行計劃錯誤時該怎麼辦呢，語句上加hint，提示它選擇哪條路是一種常見的優化方法。

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

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

實驗

我們來以MySQL8.0.25的單機環境做一個實驗。建兩個表，分別插入10000行資料，使用主鍵做這兩個表的關聯查詢。

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 ;

查詢一下兩表使用主鍵欄位關聯查詢時實際的執行計劃，如下圖所示：

查詢一下兩表使用非索引欄位關聯查詢時實際的執行計劃，如下圖所示：

從執行計劃可以看出，被驅動表的關聯欄位上有索引，優化器在選擇表連線方式時會傾向於選擇Nest Loop Join，當沒有可用索引時傾向於選擇hash join。

基於這一點那我們可以使用no_index提示來禁止語句使用關聯欄位的索引。

從上面的執行計劃可以看出使用no_index提示後，優化器選擇了使用hash join。

當索引的選擇性不好時，優化器選擇使用索引做Nest Loop Join是效率是很低的。

我們將實驗的兩個表中c1列的資料做一下更改,使其選擇性變差，並在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);

當我們執行sql ：

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

這個查詢結果會返回大量資料，被驅動表的關聯欄位c1列的索引選擇性差，此時選擇hash join是更明智的選擇，但是優化器會選擇走Nest Loop Join。我們可以通過實驗驗證一下hash join 與 Nest Loop Join的效能差異。

可以看出使用hash join的耗時是使用Nest Loop Join的1/6，但是優化器根據成本估算時，使用Nest Loop Join的成本要比使用hash join的成本低很多，所以會去選擇Nest Loop Join，這個時候就需要加上hint 提示禁止使用關聯欄位的索引，被驅動表上每次都全表掃描的代價是很高的，這樣優化器估算後就會選擇走hash join。

MySQL官方檔案裡提到用BNL，NO_BNL的hint提示來影響hash join的優化，但是經過實驗證明，在表連線關聯欄位上沒有可用索引時，優化器估算成本後不會對被驅動表使用BNL全表掃描的方式做巢狀迴圈連線，而是會選擇使用hash join，那這樣NO_BNL在這個場景下就沒有用武之地了。

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

總結

Nest Loop Join有其優勢，它是response最快的連線方式，適用於返回資料量小的場景。當兩個大表連線，返回大量資料，且關聯欄位的索引比較低效時，使用hash join就會比較高效，我們可以使用no_index的hint提示禁用關聯欄位的低效索引，促使優化器選擇hash join。

推薦學習：

以上就是干涉MySQL優化器使用hash join的方法的詳細內容，更多請關注TW511.COM其它相關文章！