• 技术文章 >数据库 >mysql教程

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

    WBOYWBOY2022-09-15 19:47:27转载275

    php入门到就业线上直播课:进入学习

    推荐学习:mysql视频教程

    GreatSQL社区原创内容未经授权不得随意使用,转载请联系小编并注明来源。GreatSQL是MySQL的国产分支版本,使用上与MySQL一致。

    前言

    数据库的优化器相当于人类的大脑,大部分时候都能做出正确的决策,制定正确的执行计划,走出一条高效的路,但是它毕竟是基于某些固定的规则、算法来做的判断,有时候并没有我们人脑思维灵活,当我们确定优化器选择执行计划错误时该怎么办呢,语句上加hint,提示它选择哪条路是一种常见的优化方法。

    我们知道Oracle提供了比较灵活的hint提示来指示优化器在多表连接时选择哪种表连接方式,比如use_nlno_use_nl控制是否使用Nest Loop Join,use_hash,no_use_hash控制是否使用hash join。

    但是MySQL长期以来只有一种表连接方式,那就是Nest Loop Join,直到MySQL8.0.18版本才出现了hash join, 所以MySQL在控制表连接方式上没有提供那么多丰富的hint给我们使用,hash_joinno_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官方文档里提到用BNLNO_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视频教程

    以上就是干涉MySQL优化器使用hash join的方法的详细内容,更多请关注php中文网其它相关文章!

    声明:本文转载于:脚本之家,如有侵犯,请联系admin@php.cn删除
    专题推荐:mysql
    上一篇:MySQL慢查询日志实操(图文解析) 下一篇:【必知必会】强化MySQL应采取的五个重要安全技巧
    VIP课程(WEB全栈开发)

    相关文章推荐

    • ❤️‍🔥共22门课程,总价3725元,会员免费学• mysql怎么解决错误2013• mysql的2002错误是什么• MySQL深入浅出精讲触发器用法• 总结分享MySQL中的用户创建与权限管理• 实例详解MySQL索引最左匹配原则
    1/1

    PHP中文网