MySQL8.18版本開始，優化引擎已經開始支持hash join，這個功能添加，可以說是mysql粉絲的重大利好消息。在沒有hash join之前，只能使用nested loop，這種模式非常消耗cpu資源，而且當sql語句缺乏索引時，性能簡直不能忍受，而hash join就能解決nested loop的痛點。
下面來看看nested loop和hash join的應用場景和對比
下面就來測試一下
測試表準備
<code>create table t_test1(id int,name char(20),depno int,primary key(id),key (depno));insert into t_test1 values(1,'test1',200);insert into t_test1 values(2,'test2',200);insert into t_test1 values(3,'test3',300);insert into t_test1 values(4,'test4',300);insert into t_test1 values(5,'test5',300);insert into t_test1 values(6,'test6',400);create table t_test2(depno int,name char(20),primary key(depno),key(name));insert into t_test2 values(200,'test200');insert into t_test2 values(300,'test300');insert into t_test2 values(400,'test400');/<code>
查看優化器參數
<code>mysql> show variables like 'optimizer_switch';+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Variable_name | Value |+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on |+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)/<code>
可以看到已經有hash_join=on的值。
sql測試
<code>mysql> EXPLAIN FORMAT=tree select a.name,a.depno,b.name from t_test1 a inner join t_test2 b on a.depno=b.depno;+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| EXPLAIN |+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| -> Inner hash join (a.depno = b.depno) (cost=2.60 rows=6) -> Table scan on a (cost=0.15 rows=6) -> Hash -> Index scan on b using name (cost=0.55 rows=3) |+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)/<code>
從測試結果可以看到，sql的執行計劃已經用到了hash join。
分享到:
閱讀更多 架構師技術之棧 的文章
