Mysql8重大性能提升,支持hash Join

MySQL8.18版本开始,优化引擎已经开始支持hash join,这个功能添加,可以说是mysql粉丝的重大利好消息。在没有hash join之前,只能使用nested loop,这种模式非常消耗cpu资源,而且当sql语句缺乏索引时,性能简直不能忍受,而hash join就能解决nested loop的痛点。

Mysql8重大性能提升,支持hash Join

下面来看看nested loop和hash join的应用场景和对比

Mysql8重大性能提升,支持hash Join

下面就来测试一下

测试表准备

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');

查看优化器参数

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)

可以看到已经有hash_join=on的值。

sql测试

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)

从测试结果可以看到,sql的执行计划已经用到了hash join。

Mysql8重大性能提升,支持hash Join

您可能还会对下面的文章感兴趣: