干涉MySQL优化器使用hash join的方法
作者:GreatSQL 发布时间:2024-01-21 17:29:52
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的优势,只需要控制此语句的使用就好了。
来源:https://www.cnblogs.com/greatsql/p/16688860.html


猜你喜欢
- 表查询: 合并查询:使用union关键字,可将满足条件的重复行去掉。 select ename,sal,job from emp where
- Select字句在逻辑上是SQL语句最后进行处理的最后一步,所以,以下查询会发生错误:SELECT YEAR(OrderDate) AS O
- 问题jupyter notebook读入csv数据时出现错误“SyntaxError: (unicode error) ‘unicodees
- 很多人在使用AJAX调用别人站点内容的时候,JS会提示"没有权限"错误,这是XMLHTTP组件的限制-安全起见禁止访问非
- 0x01 OpenCV安装 通过命令pip install opencv-python 安装pip install opencv-
- 由于想要使用pycharm连接Window子系统Ubuntu进行开发,找了很多教程都不够详细,花了点儿时间,最后配置成功。将pycharm连
- 效果图如下:图1(头像图片剪成圆形的,其他为透明)图2(给图片的4个角加椭圆)以前没处理过,处理起来真是有点费力呀。用到的模块:import
- 前言Django框架功能齐全自带数据库操作功能,本文主要介绍Django的ORM框架到目前为止,当我们的程序涉及到数据库相关操作时,我们一般
- 先看一下br怎么玩转“清除浮动”了。使用以下代码<br clear="all" />以下是代码效果演示:运行
- 版本信息:python:3.6mysql:5.7pyMysql:0.7.11################################
- DataFrame的行和列:df[‘行’, ‘列’]Data
- 什么是mock?mock在翻译过来有模拟的意思。它允许您用模拟对象替换您的系统的部分,并对它们已使用的方式进行断言。Mock通常是指,在测试
- python程序运行中,可由程序抛出异常。异常触发:使用raise命令抛出异常,即可使用异常基类Exception,也可使用自定义异常类(继
- 本文实例为大家分享了python环境路径设置方法,以及命令行运行python脚本,供大家参考,具体内容如下找Python安装目录,设置环境路
- 相关文章推荐:各种loading加载图标下载 gif格式loadinfo和ajaxload一样,也是一个在线Ajax载入动画生成工
- 上一篇我们写了Django基于类如何增删改数据的方法,方法虽然简单,但新手可能对其原理不是很清楚,那么我们这次就用Django提供的Mode
- 那你也许会问及,怎样获取当前系统日期的最大时间值,如yyyy-MM-dd 23:59:59.997。 我们可以使用DATEADD函数,来实现
- 今天在研究Plotly绘制散点图的方法,供大家参考,具体内容如下使用Python3.6 + PlotlyPlotly版本2.0.0在开始之前
- 本文实例讲述了CI框架教程之优化验证码机制。分享给大家供大家参考,具体如下:验证码机制在CI框架中是通过一个辅助函数captcha()进行实
- magpierss中就用到了snoopy,这让我有点兴趣去研究下这个咚咚。再SF上,找到了这个源代码。居然就是一个类,但不要笑看哦,功能可是