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

文 / @WordPress主题

MySQL优化器是一个很重要的组件,它决定了查询语句的执行计划和效率。有时候,该优化器会做出错误的决策,选择了一条效率低下的执行路径,那我们应该怎么办呢?

一种常见的优化方法是在语句中加入hint提示,指示优化器选择正确的执行计划。Oracle提供了很多灵活的提示,如use_nl,no_use_nl用于控制是否使用NestLoopJoin;use_hash,no_use_hash用于控制是否使用hashjoin等,但是MySQL长期以来只有NestLoopJoin这一种表连接方式,直到MySQL8.0.18版本才引入了hashjoin,因此在控制执行计划上没有那么多的hint可供使用。但如果我们想让两个表使用hashjoin呢?接下来,我们将介绍如何使用no_index提示来使得优化器选择hashjoin。

我们在MySQL8.0.25单机环境下做一个实验,建立两个表,分别插入10000行数据,并使用主键做这两个表的关联查询,查询实际的执行计划,结果如下图所示:

从执行计划可以看出,当被驱动表上的关联字段有索引时,优化器倾向于使用NestLoopJoin,没有索引时倾向于使用hashjoin。但当索引的选择性不好时,调用NestLoopJoin是低效的。我们可以通过使用no_index提示禁止关联字段的索引,这时优化器会选择使用hashjoin。

我们也可以通过实验来验证一下hashjoin与NestLoopJoin的性能差异,实验结果显示使用hashjoin的耗时约为NestLoopJoin的1/6。

最后,我们需要知道,虽然BNL和NO_BNL这些hint提示可以影响hashjoin的优化,但当被驱动表上的关联字段没有可用索引时,优化器不会选择使用BNL的全表扫描方式做嵌套循环连接,而是会选择hashjoin。因此,在这个场景下,NO_BNL是没有用的。同时,虽然可以去除索引来解决这个问题,但不同的业务场景可能有不同的需求,这时候使用hint提示就非常方便,能够有针对性地控制查询语句的执行计划。

综上所述,我们了解了如何使用no_index提示来干涉MySQL优化器的执行计划,使其选择更合适的执行路径,提高查询效率。

添加UTHEME为好友
扫码添加UTHEME微信为好友
· 分享WordPress相关技术文章,主题上新与优惠动态早知道。
· 微信端最大WordPress社群,限时免费入群。