第三十四讲：join语句怎么优化？

嘉嘉宝贝

第三十四讲：join语句怎么优化？

简概：

万年不变的开头

​        在上一篇文章中，我和你介绍了 join 语句的两种算法，分别是 Index Nested-Loop Join(NLJ) 和 Block Nested-Loop Join(BNL)。我们发现在使用 NLJ 算法的时候，其实效果还是不错的，比通过应用层拆分成多个语句然后再拼接查询结果更方便，而且性能也不会差。
​        但是，BNL 算法在大表 join 的时候性能就差多了，比较次数等于两个表参与 join 的行数的乘积，很消耗 CPU 资源。当然了，这两个算法都还有继续优化的空间，我们今天就来聊聊这个话题。
​        为了便于分析，我还是创建两个表 t1、t2 来和你展开今天的问题。

1. create table t1(id int primary key, a int, b int, index(a));
2. create table t2 like t1;
3. drop procedure idata;
4. delimiter ;;
5. create procedure idata()
6. begin
7.   declare i int;
8.   set i=1;
9.   while(i<=1000)do
10.     insert into t1 values(i, 1001-i, i);
11.     set i=i+1;
12.   end while;
13.   
14.   set i=1;
15.   while(i<=1000000)do
16.     insert into t2 values(i, i, i);
17.     set i=i+1;
18.   end while;
20. end;;
21. delimiter ;
22. call idata();

复制代码

​        现在为了得到最快的执行速度，如果让你来设计表 t1、t2、t3 上的索引，来支持这个 join 语句，你会加哪些索引呢？
​        同时，如果我希望你用 straight_join 来重写这个语句，配合你创建的索引，你就需要安排连接顺序，你主要考虑的因素是什么呢？
答案

​        第一原则是要尽量使用 BKA 算法。
​        需要注意的是，使用 BKA 算法的时候，并不是“先计算两个表 join 的结果，再跟第三个表 join”，而是直接嵌套查询的。具体实现是：在 t1.c>=X、t2.c>=Y、t3.c>=Z 这三个条件里，选择一个经过过滤以后，数据最少的那个表，作为第一个驱动表。
​        此时，可能会出现如下两种情况。第一种情况，如果选出来是表 t1 或者 t3，那剩下的部分就固定了。

• 如果驱动表是 t1，则连接顺序是 t1->t2->t3，要在被驱动表字段创建上索引，也就是 t2.a 和 t3.b 上创建索引；
  
• 如果驱动表是 t3，则连接顺序是 t3->t2->t1，需要在 t2.b 和 t1.a 上创建索引。
  

​        同时，我们还需要在第一个驱动表的字段 c 上创建索引。
​        第二种情况是，如果选出来的第一个驱动表是表 t2 的话，则需要评估另外两个条件的过滤效果。
​        总之，整体的思路就是，尽量让每一次参与 join 的驱动表的数据集，越小越好，因为这样我们的驱动表就会越小。
对答案具体操作

第一种情况

驱动表是 t1

连接顺序为 t1 -> t2 -> t3，需要在 t2.a 和 t3.b 上创建索引。

1. select * from t1 where a>=1 and a<=100;

复制代码

驱动表是 t3

连接顺序为 t3 -> t2 -> t1，需要在 t2.b 和 t1.a 上创建索引。

1. set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

复制代码

第二种情况

驱动表是 t2

在这种情况下，需要评估 t1.c >= X 和 t3.c >= Z 的过滤效果。如果这两个条件的过滤效果都非常好（即能显著减少结果集的大小），那么可以考虑将 t2 作为驱动表。
连接顺序为 t2 -> t1 或 t2 -> t3，然后连接剩下的表。在这种情况下，我们可能需要在 t1.a 和 t3.b 上创建索引。但需要注意，选择哪个表先与 t2 连接，取决于哪个连接能更有效地利用 WHERE 条件中的过滤效果。
假设我们优先连接 t1（即 t2 -> t1 -> t3），则 SQL 语句如下：

1. select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;

复制代码

假设我们优先连接 t3（即 t2 -> t3 -> t1），则 SQL 语句如下：

1. create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
2. insert into temp_t select * from t2 where b>=1 and b<=2000;
3. select * from t1 join temp_t on (t1.b=temp_t.b);

复制代码

注意事项

• 过滤效果评估：选择驱动表时，需要评估 WHERE 条件中的过滤效果，优先使用过滤效果好的表作为驱动表。
  
• 索引选择性：索引的选择性越高（即索引列中唯一值的比例越高），查询性能越好。
  
• 数据大小：小表优先作为驱动表，可以减少 I/O 操作。
  

在实际应用中，可以通过 EXPLAIN 语句来评估查询计划，从而确定最佳的连接顺序和索引策略。

来源:https://www.cnblogs.com/guixiangyyds/p/18528738
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！