前言数据库的优化器相当于人的大脑。大多数时候，它能够做出正确的决策，制定正确的执行计划，找到一条高效的路径。但是，它毕竟是建立在一定的固定规则和算法之上的。有时我们做出的判断不如人脑灵活。当我们确定优化器选择了错误的执行计划时，我们应该怎么做呢？在语句中添加提示，提醒它选择哪种方式是一种常见的优化方法。我们知道Oracle提供了一个更灵活的hint提示来指示优化器在连接多个表时选择哪种表连接方式，比如use_nl、no_use_nl控制是否使用NestLoopJoin，use_hash、no_use_hash控制是否使用hashjoin。然而，长期以来MySQL只有一种表连接方式，那就是NestLoopJoin，而hashjoin直到MySQL8.0才出现。hint只是一瞥，8.0.18版本才存在，8.0.19及以后的版本hint被丢弃了，那么我们要两张表做hashjoin怎么办呢？实验我们在MySQL8.0.25的单机环境下做一个实验。创建两张表，分别插入10000行数据，两张表之间使用主键进行关联查询。createtablet1(idintprimarykey,c1int,c2int);createtablet2(idintprimarykey,c1int,c2int);delimiter//CREATEPROCEDUREp_test()BEGINdeclareiint;seti=1;whilei<10001doinsertintot1values(i,i,i);insertintot2values(i,i,i);SETi=i+1;endwhile;END;//delimiter;使用主键字段查询两张表关联查询的实际执行计划如下图所示：使用非索引字段关联查询查询两张表的实际执行计划如下图所示：来自执行计划，可以看出被驱动表的关联字段上有索引，优化器在选择表连接方式时倾向于选择NestLoopJoin，在没有可用索引时倾向于选择hashjoin。基于此，我们可以使用no_indexhint来禁止语句使用关联字段的索引。从上面的执行计划可以看出，在使用了no_indexhint之后，优化器选择了使用hashjoin。当索引的选择性不好时，优化器选择使用索引进行NestLoopJoin是非常低效的。我们将改变两个实验表中c1列的数据，使其选择性变差，并在c1列上建立一个公共索引。更新t1设置c1=1，其中id<5000；更新t2设置c1=1，其中id<5000；在t1(c1)上创建索引idx_t1；在t2(c1)上创建索引idx_t2；当我们执行sql时：selectt1.*,t2.*fromt1joint2ont1.c1=t2.c1;该查询结果会返回大量数据，被驱动表的关联字段c1列的索引选择性差。这时候选择hashjoin是比较明智??的选择，但是优化器会选择使用NestLoopJoin。我们可以通过实验验证hashjoin和NestLoopJoin的性能差异。可以看出使用hashjoin的耗时是使用NestLoopJoin的1/6，但是当优化器根据成本估算成本时，使用NestLoopJoin的成本远低于使用NestLoopJoin的成本使用hashjoin，所以我会选择NestLoopJoin，这时候需要添加一个提示，禁止使用关联字段的索引。每次对驱动表进行全表扫描的代价是非常高的，所以优化器会在估算后选择使用hashjoin。MySQL官方文档提到BNL和NO_BNL提示用于影响哈希连接的优化。但是，实验表明，当表连接的关联字段上没有可用索引时，优化器在估算成本后不会对驱动表使用BNL。表扫描的方式是做nestedloopjoin，但是选择使用hashjoin，所以NO_BNL在这种场景下是没用的。那么既然没有用到这个索引，难道去掉这个索引不就好了吗？为什么一定要用到no_index的hint提示呢？我们需要知道使用了多少业务场景。它不在这里使用。在其他地方使用这个索引可能会大大提高效率。这时候hint的优势就凸显出来了。我们只需要控制这条语句使用就好了。总结NestLoopJoin有它的优势。是响应速度最快的连接方式，适用于返回数据量较小的场景。当连接两个大表返回大量数据，而关联字段的索引效率相对较低时，使用hashjoin效率更高。我们可以使用no_index的提示来禁用关联字段的低效索引，提示优化器选择hashjoin。
