数据库系列之MySQL中Join语句优化问题

最近使用MySQL 8.0.25版本时候遇到一个SQL问题，两张表做等值Join操作执行很慢，当对Join连接字段添加索引优化后，执行效率反而变得更差，其中的原因值得分析。因此本文介绍下MySQL中常见的Join算法，并对比使用不同Join算法时候的性能情况。

1、SQL中的Join操作

在这里插入图片描述

在关系型数据库中进行多表关联时，通常使用Join连接，常见的Join如图所示，包括七种：

1）左连接LEFT JOIN

左连接是将左表作为主表，右表作为从表。左表中的所有记录作为外层循环，在右表中进行匹配，如果右表中没有匹配的行，则将左表记录的右表项补空值。对应的SQL语句如下：

SELECT * FROM TableA a LEFT JOIN TableB b ON a.KEY = b.KEY

2）左外连接LEFT OUTER JOIN

左外连接是将左表作为主表，右表作为从表，循环遍历右表，查找与条件满足的项，如果在右表中没有匹配的项，则补空值，并且在结果集中选择只在左表中存在的数据。对应的SQL语句如下：

SELECT * FROM TableA a RIGHT JOIN TableB b ON a.KEY = b.KEY WHERE b.KEY IS NULL

3）右连接RIGHT JOIN

右连接是将右表作为主表，左表作为从表。右表中的所有记录作为外层循环，在左表中进行匹配，如果左表中没有匹配的行，则将右表记录的左表项补空值。对应的SQL语句如下：

SELECT * FROM TableA a RIGHT JOIN TableB b ON a.KEY = b.KEY

4）右外连接RIGHT OUTER JOIN

右外连接选择将右表作为主表，左表作为从表，循环遍历左表，查找与join条件满足的项，如果在左表中没有匹配的项，则补空值，并且在结果集中选择只在右表中存在的数据。对应的SQL语句如下：

SELECT * FROM TableA a RIGHT JOIN TableB b ON a.KEY = b.KEY WHERE a.KEY IS NULL

5）内连接INNER JOIN

内连接是将左表和右表对于条件相匹配的项进行组合，返回相关列值相等的结果。对应的SQL语句如下：

SELECT * FROM TableA a INNER JOIN TableB b ON a.KEY = b.KEY

6）全连接FULL JOIN

全连接是将左表和右表的所有记录进行匹配，如果在另外表项中不存在记录，则补空值。对应的SQL语句如下：

SELECT * FROM TableA a FULL OUTER JOIN TableB b ON a.KEY = b.KEY

7）全外连接FULL OUTER JOIN

全外连接是将全连接中表相交的部分去除掉。对应的SQL语句如下：

SELECT * FROM TableA a FULL OUTER JOIN TableB b ON a.KEY = b.KEY WHERE a.KEY IS NULL OR b.KEY IS NULL
2、MySQL中Join算法介绍

MySQL中在多表Join查询时，可以使用多种Join算法，比如Nested Loop Join（嵌套循环连接）、Index Nested-Loop Join（索引嵌套循环连接）、Block Nested-Loop Join（块嵌套循环连接）、Sort-Merge Join（排序合并连接）和Hash Join（哈希连接）。

在MySQL 5.5版本之前，只支持一种关联算法Nested Loop Join，在5.5版本后通过引入Index Nested-Loop Join和Block Nested-Loop Join算法来优化嵌套查询。从MySQL 8.0.18开始，MySQL实现了对于相等条件下的Hash Join，并且join条件中无法使用任何索引。相对于Blocked Nested Loop算法，hash join性能更高，并且两者的使用场景相同，所以从8.0.20开始，Blocked Nested Loop算法已经被移除，使用hash join替代之。

2.1 Nested Loop Join

Nested Loop Join（NLJ）本质上是一个双层for循环，对于外表中的每一行数据，MySQL检查内表中是否满足JOIN条件。如果满足，则将其添加到结果集中。Nested Loop Join的执行流程如下图所示：

在这里插入图片描述

  1. SQL从外表T2中读取一行记录，取出关联字段id到内表T1中逐条查找；
  2. 取出T1表中满足条件的记录与T2表中获取的结果进行合并，并将结果放入结果集中；
  3. 循环上述过程直到无法满足条件，将结果返回给客户端。

Nested Loop Join伪代码实现如下：

    for each row in t1 matching range {for each row in t2 matching reference key {if(t1.id==t2.id) {//返回结果}}}

Nested Loop Join算法在处理小数据集时可能非常有效，但是对于大型数据集，可能会导致性能下降。通过双层循环来进行比较值获取结果，就是对外表和内表进行笛卡尔积运算，比如t1和t2表的数据量分别为R和S，运算的成本为O(R*S)，当表数据量大时候，执行效率会非常低。

2.2 Index Nested-Loop Join

Index Nested-Loop Join（INLJ）是Nested-Loop Join的改进版，其优化思路是通过索引访问减少内层循环的匹配次数，也就是通过外层数据与内存的索引数据进行循环匹配，以减少数据访问提高查询效率。INLJ执行过程如下所示：

在这里插入图片描述

  1. SQL从外表T2中读取一行记录，取出关联字段id到内表T1的索引树中查找；
  2. 从T1表中取出辅助索引树中满足条件的记录查到主键ID，再到主键索引中根据主键ID将剩下字段的数据取出与T2中获取到的结果进行合并，并将结果放入结果集
  3. 循环上述过程直到无法满足条件，将结果返回给客户端。

Index Nested-Loop Join被很多人诟病效率不高，主要是因为在Join过程很多时候用到的不是主键的cluster index而是辅助索引。如果关联字段id在T1表的主键索引字段中，则直接通过主键索引获取到数据，索引查找的开销非常小，并且访问模式也是顺序的；如果关联字段在辅助索引字段中，如果查询需要访问聚集索引上的列，那么必要需要进行回表取数据。辅助索引是随机IO访问、再回表查询又是随机IO访问，因此执行效率会降低。

2.3 Block Nested-Loop Join

Block Nested-Loop Join（BNLJ）也是Nested-Loop Join的优化方法，如果Join的关联字段不是索引或者有一个字段不在索引中，则会采用该算法进行查询。在BNLJ算法中增加一个join_buffer缓存块，在Join操作时候会把外表的数据放入缓存块中，然后扫描内表，把内表每一行取出来跟join_buffer中的数据批量做对比。BNLJ执行过程如下所示：

在这里插入图片描述

  1. 将外表T2中的数据读入到join_buffer中（默认内存大小为256k,如果数据量多,会进行分段存放,然后进行比较）
  2. 把表T1的每一行数据，跟join_buffer中的数据批量进行对比，匹配的数据与T2表中获取的结果进行合并，并将结果放入结果集中；
  3. 循环以上步骤直到无法满足条件，将结果集返回给客户端

Block Nested-Loop Join的优化思路是利用join_buffer减少外表的循环次数，通过一次性缓存多条记录数，将参与查询的列放入join_buffer中，然后拿join buffer里的数据批量与内层表的数据进行匹配，从而减少对外表的访问IO次数。在MySQL 8.0.18版本之前，不使用Index Nested-Loop Join的时候，默认使用的是Block Nested-Loop Join。在8.0.20版本以后，MySQL中不再使用Block Nested-Loop Join，由hash join进行替代优化。

Prior to MySQL 8.0.18, this algorithm was applied for equi-joins when no indexes could be used; in MySQL 8.0.18 and later, the hash join optimization is employed in such cases. Starting with MySQL 8.0.20, the block nested loop is no longer used by MySQL, and a hash join is employed for in all cases where the block nested loop was used previously.

使用Block Nested-Loop Join算法需要开启优化器管理配置的optimizer_switch的设置block_nested_loop为on，默认为开启。

1）查看block_nested_loop配置

Show variables like 'optimizer_switc%';
mysql> show variables like 'optimizer_switch'\G
*************************** 1. row ***************************
Variable_name: optimizer_switchValue: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on

2）查看join_buffer_size参数配置

Show variables like 'join_buffer_size%';
mysql> show variables like 'join_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
2.3.1 Join Buffer特性

Join buffer的大小由参数join_buffer_size控制，默认是256KB，对于一些复杂的SQL语句为了提升性能可以调整该参数值。需要注意的是变量 join_buffer_size 的最大值在MySQL 5.1.22 版本前是4G，而之后的版本才能在64位操作系统下申请大于4G的Join Buffer空间。
在MySQL中Join buffer还有以下特性：

  • Join Buffer的使用条件：Join Buffer只有在join类型为all、index、range的时候才可以使用。也就是join字段没有使用到索引或部分字段不在索引列中会用到。
  • Join Buffer的分配与释放：在join之前就会分配Join Buffer，每个join都会分配一个buffer。在查询执行完毕后就会释放Join Buffer。
  • Join Buffer中保存的数据：Join Buffer中只会保存参与join的列，并非整个数据行。即使通过调整参数使其变大，也并不会加速查询，因为其内部逻辑和数据的排布方式不会因此而改变。

由于每次Join都会分配一个Join Buffer，假设高并发P查询有N张表参与Join，每张表之间使用Block Nested-Loop Join算法，需要分配P*(N-1)个Join buffer。因此Join Buffer的设置需要考量，设置不当有可能会引起内存分配不足导致数据库宕机。

2.4 Sort-merge Join

Sort-Merge Join算法是MySQL中一种用于执行JOIN操作的算法。其原理是先对两个表根据Join列进行全扫描后排序，然后逐行比较它们以找到匹配的行。执行过程如下所示：

在这里插入图片描述

  1. 对两个表T1和T2进行排序，排序可以通过使用索引或执行额外的排序操作来完成。确保两个表中的数据按照连接键（JOIN条件）进行排序。
  2. 排序完成后，对两张表逐行进行比较和归并操作，从每个表中选取若港行并检查是否满足Join条件，如果满足Join条件将匹配的行添加到结果集中。
  3. 继续从每个表中选取下一行，并重复以上步骤直到扫描完所有行。

在这里插入图片描述

以上图为例，两个表T1和T2已经排好序进行等值Join连接查询，并假设内存中buffer能容纳2条记录：

  1. 首先取出T1(1,3)和T2(1,4)进行比较，此时只有(1,1)是匹配的，把记录1放入结果集中
  2. 再依次取出T1和T2表中的其它值，T1中的(5,15)和T2中的(5,10)，因为是等值Join，T1中的3和T2中的4被舍弃了。此时匹配到(5,5)并将记录5放入结果集中
  3. 进行新的一轮循环，直到两个表中的记录比对完成

Sort-Merge Join算法在处理大型数据集时可能非常有效，因为它通过排序和逐行比较来减少I/O操作次数。但是需要进行额外的排序操作，可能会增加查询的执行时间，通常情况下CBO模式下优化器不会优先选择该种Join算法。Sort Merge join一般适用于以下场景：

  • RBO模式（基于规则的优化方式）
  • 不等价关联(>,<,>=,<=,<>)
  • HASH_JOIN_ENABLED=false
  • 数据源已排序
2.5 Hash Join

MySQL 8.0.18版本开始增加了对Hash Join算法的支持，以提升Join的性能，在8.0.20版本以后，MySQL中不再使用Block Nested-Loop Join，由hash join进行替代优化。在MySQL 8.0.18中hash join的使用前提条件是表与表之间是等值连接并且连接字段上不使用索引，或者是不包含任何连接条件的笛卡尔连接，否则hash join会退化。

为了支持hash join，mysql在优化器optimizer_switch中新增了hash join开关选项hash_join，默认是ON状态。同时新增了两个hint：HASH_JOIN和NO_HASH_JOIN，用于在SQL级别控制hash join行为。但是从MySQL 8.0.19开始，这两个hint被置为无效，hash join的使用就不受用户控制，由优化器决定。并且在MySQL 8.0.20及更高版本中，取消了对等值条件的约束，可以全面支持non-equi-join，Semijoin，Antijoin，Left outer join/Right outer join。比如非等值join使用hash join算法：

mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.c1 < t2.c1\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t1.c1 < t2.c1)  (cost=4.70 rows=12)-> Inner hash join (no condition)  (cost=4.70 rows=12)-> Table scan on t2  (cost=0.08 rows=6)-> Hash-> Table scan on t1  (cost=0.85 rows=6)

Hash join的基本原理是通过Hash的方式降低复杂度，MySQL根据连接条件对外表建立Hash表，对于内表的每一行记录也根据连接条件计算Hash值，只需要验证对应的hash值能否匹配完成连接操作。但是如果外表过大或者hash join可使用的内存过小，外表数据不能全部加载到内存中，优化器会把外表切分为不同的partition，使得切分后的分片能够放入内存，不能放入内存的会写入磁盘的chunk files中。

2.5.1 In-memory Hash-join

在这里插入图片描述

外表数据能够全部放入内存中，称为in-memory hash-join，hash join分为两个过程：build过程构建hash表和probe过程探测hash表。

  1. Build过程：遍历外表，以连接条件”countries.country_id”为hash key，查询需要的列作为value创建hash表。通常优化器优先选择占用内存最小的表作为外表构建hash表。
  2. Probe过程：逐行遍历内表，对于内表的每行记录，根据连接条件”persons.country_id”计算hash值，并在hash表中查找。如果匹配到外表的记录，则输出，否则跳过，直到遍历完成所有内表的记录

上述场景适用于表数据能够存放在内存中的场景，这个内存由参数join_buffer_size控制，并且可以动态调整生效。

2.5.2 On-disk Hash-join

在build阶段如果内存不够，优化器会将外表分成若干个partition执行，这些partition是保存在磁盘上的chunks。优化器会根据内存的大小计算出合适的chunks数，但是在mysql中chunk file数目硬限制为128个。分片的过程如下图所示：

在这里插入图片描述

在build阶段优化器根据hash算法将外表数据存放到磁盘中对应的chunks文件中，在probe阶段对内表数据使用同样的hash算法进行分区并存放在磁盘的chunks文件中。由于使用相同的hash函数，那么key相同（join条件相同）必然在同一个分片编号的chunk文件中。接下来，再对外表和内表中相同分片编号的数据放入到内存中进行Hash Join计算，所有分片计算完成后，整个join过程结束。这种算法的代价是外表和内表在build阶段进行一次读IO和一次写IO，在probe阶段进行了一次读IO。整个过程如下图所示：

在这里插入图片描述

上述算法能够解决内存不足的Join问题，但是如果数据倾斜严重导致哈希后的分片仍然超过内存的大小，MySQL优化器的处理方法是：读满内存中的hash表后停止build过程，然后执行一次probe。待处理这批数据后，清空hash表，在上次build停止的位点继续build过程来填充hash表，填充满再做一趟内表分片完整的probe。直到处理完所有build数据。

2.6 不同Join算法的代价对比

上面介绍了Netsted Loop Join（NLJ）、Index Nested-Loop Join（INLJ）、Block Nested-Loop Join（BNLJ）、Sort-merge Join（SMJ）和Hash Join几种Join算法，不同算法的开销对比如下（其中RN为外表记录数、SN为内表记录数）：

在这里插入图片描述

3、不同Join算法性能测试
3.1 环境准备

1）安装MySQL 8.0.25版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.25    |
+-----------+mysql> show variables like 'join_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+

2）准备数据

##创建表
CREATE TABLE `t1` (`id` int(11) NOT NULL AUTO_INCREMENT,`c1` int(11) DEFAULT NULL,`c2` varchar(300) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;mysql> create table t2 like t1;##插入数据到表中
-- 往t1表插入5万行记录
drop procedure if exists insert_t1; 
delimiter ;;
create procedure insert_t1()        
begindeclare i int;                    set i=1;                          while(i<=50000)do                 INSERT INTO t1 (c1, c2) VALUES (RAND() * 100000, CONCAT('Record ', i));set i=i+1;                       end while;
end;;
delimiter ;
call insert_t1();-- 往t2表插入1000行记录
drop procedure if exists insert_t2; 
delimiter ;;
create procedure insert_t2()        
begindeclare i int;                    set i=1;                          while(i<=1000)do                 INSERT INTO t2 (c1, c2) VALUES (RAND() * 100, CONCAT('Record ', i));set i=i+1;                       end while;
end;;
delimiter ;
call insert_t2();
3.2 不同Join算法对比
3.2.1 使用Hash join
mysql> explain select * from t1 join t2 on t1.c1=t2.c1;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  1000 |   100.00 | NULL                                       |
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 50200 |    10.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

Explain format=tree或explain analyze看到hash join关键字

mysql> explain format=tree select * from t1 join t2 on t1.c1=t2.c1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (t1.c1 = t2.c1)  (cost=5020281.38 rows=5020000)-> Table scan on t1  (cost=0.68 rows=50200)-> Hash-> Table scan on t2  (cost=101.25 rows=1000)|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

测试外连接

mysql> explain format=tree select * from t1 left join t2 on t1.c1=t2.c1;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                               |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Left hash join (t2.c1 = t1.c1)  (cost=5020219.32 rows=50200000)-> Table scan on t1  (cost=5060.25 rows=50200)-> Hash-> Table scan on t2  (cost=0.01 rows=1000)|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

测试不等连接

mysql> explain format=tree select * from t1 join t2 on t1.c1<t2.c1;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                          |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (t1.c1 < t2.c1)  (cost=5020281.38 rows=16731660)-> Inner hash join (no condition)  (cost=5020281.38 rows=16731660)-> Table scan on t1  (cost=1.85 rows=50200)-> Hash-> Table scan on t2  (cost=101.25 rows=1000)|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

可以看到在MySQL 8.0.25版本中已经支持非等值连接、外连接等Join。

3.2.2 Join字段添加索引
#t1表添加索引
mysql> create index idx_c1 on t2(c1);
mysql> explain format=tree select * from t1 join t2 on t1.c1=t2.c1;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                              |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join  (cost=179020.65 rows=497030)-> Filter: (t1.c1 is not null)  (cost=5060.25 rows=50200)-> Table scan on t1  (cost=5060.25 rows=50200)-> Index lookup on t2 using idx_c1 (c1=t1.c1)  (cost=2.48 rows=10)|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+#清理索引
mysql> alter table t2 drop index idx_c1;

已经变成Index Nested Loop Join

3.2.3 使用hint（BNL和NO_BNL）将hash join启用和关闭

使用NO_BNL(t1,t2)退化为Nested loop inner join

mysql> explain format=tree select /*+ NO_BNL(t1,t2)*/ * from t1 join t2 on t1.c1=t2.c1;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join  (cost=5060351.25 rows=5020000)-> Table scan on t2  (cost=101.25 rows=1000)-> Filter: (t1.c1 = t2.c1)  (cost=40.75 rows=5020)-> Table scan on t1  (cost=40.75 rows=50200)|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3.2.4 几种Join算法性能对比如下：

在这里插入图片描述

T1表记录50000条，T2表记录1000条，各种Join算法执行情况如上表：

  • 使用Hash Join：查询时间0.0299s、Rows_examined记录51000条，为T1+T2表记录和
  • 使用简单Nested loop join：查询时间20.64s、Rows_examined记录50001000条，为T1*T2笛卡尔积
  • Index Nested loop join（小表index）：查询时间0.125s、Rows_examined记录50517条
  • Index Nested loop join（大表index）：查询时间0.007s、Rows_examined记录1517条
  • Index Nested loop join（两表都有index）：查询时间0.0064s、Rows_examined记录1517条

可以看到使用hash join比普通的netsted loop join性能好很多，而index Nested loop join如果索引使用不当或者索引字段过滤系数不高，性能并不能比hash join好。通过explain analyze分析下上面两个场景：

####Hash join结果
mysql> explain analyze select * from t1 join t2 on t1.c1=t2.c1;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                       |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (t1.c1 = t2.c1)  (cost=5020281.38 rows=5020000) (actual time=1.515..49.142 rows=517 loops=1)-> Table scan on t1  (cost=0.68 rows=50200) (actual time=0.039..32.930 rows=50000 loops=1)-> Hash-> Table scan on t2  (cost=101.25 rows=1000) (actual time=0.051..0.580 rows=1000 loops=1)|####Hash join结果
mysql> explain analyze select * from t1 join t2 on t1.c1=t2.c1;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join  (cost=179020.65 rows=497030) (actual time=1.964..150.722 rows=517 loops=1)-> Filter: (t1.c1 is not null)  (cost=5060.25 rows=50200) (actual time=0.049..31.721 rows=50000 loops=1)-> Table scan on t1  (cost=5060.25 rows=50200) (actual time=0.048..25.023 rows=50000 loops=1)-> Index lookup on t2 using idx_c1 (c1=t1.c1)  (cost=2.48 rows=10) (actual time=0.002..0.002 rows=0 loops=50000)|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

对比看到hash join时候，T1表和T2表进行的是table scan，但是只需要loop一次；而在index Nested loop join时候，虽然T2表走了index lookup，但是需要loops=50000次，也就是对T1表的所有记录循环比对，整个过程下来性能相比较差了很多。如果T2表的索引匹配效率不高，这个执行效率会更差。

4、总结

回到最开始的问题，为什么对Join连接字段添加索引优化后，执行效率反而变得更差。从上文的分析中知道，MySQL 8.0.25版本中当两张表进行等值Join时候，没有索引的情况下默认会使用到hash join算法。当对Join连接字段添加索引后，MySQL优化器使用index Nested loop join算法，但是当小表使用到索引并且过滤系数不高的时候，会进行大量的loop操作，进而导致整个执行效率变差。因此在Join查询优化的时候，加索引并不一定能提升效率，有可能会适得其反，这些都是MySQL优化器控制的。

参考资料：

  1. https://www.cnblogs.com/mlcn-2000/p/14604236.html
  2. https://blog.csdn.net/chuyanju7641/article/details/110524107
  3. MySQL Join算法与调优白皮书，姜
  4. https://blog.csdn.net/orangleliu/article/details/72850659
  5. https://dev.mysql.com/blog-archive/hash-join-in-mysql-8/
  6. https://blog.csdn.net/ddhe9527/article/details/103659977
  7. https://dev.mysql.com/doc/refman/8.1/en/hash-joins.html

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/164483.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

百度文心一言 4.0 ：如何申请百度文心一言 4.0

百度文心一言 4.0 ：如何申请百度文心一言 4.0

本心、输入输出、结果 文章目录 百度文心一言 4.0 &#xff1a;如何申请百度文心一言 4.0前言文心一言 4.0 ERNIE-Bot 4.0 &#xff1a;ERNIE-Bot 4.0 大模型深度测试体验报告如何申请千帆大模型试用百度文心一言 4.0 主要功能介绍配套发布的十余款AI原生应用插件、API 生态 百…
阅读更多...
图论相关算法

图论相关算法

一、迪杰斯特拉(Dijkstra)算法 迪杰斯特拉算法使用类似广度优先搜索的方法解决了带权图的单源最短路径问题。这是一个贪心算法。 1.核心思想 &#xff08;1&#xff09;每次选中一个点&#xff0c;这个点满足两个条件&#xff1a; 未被选过距离最短 &#xff08;2&#xf…
阅读更多...
如何使用 MiniGPT-v2

如何使用 MiniGPT-v2

MiniGPT-v2 是一个基于视觉语言模型&#xff08;LLM&#xff09;的多任务学习系统。它可以用于各种视觉语言任务&#xff0c;包括图像描述、图像识别、图像-文本对话等。 本文将介绍如何使用 MiniGPT-v2。 MiniGPT-v2 提供了一个简单的在线演示&#xff0c;可以用于测试模型。…
阅读更多...
ENVI IDL:对于GEOTIFF结构体的说明

ENVI IDL:对于GEOTIFF结构体的说明

Tag标签-前言 其中最关键的只有两个标签Tag&#xff0c;一个是MODELPIXELSCALETAG&#xff0c;一个是MODELTIEPOINTTAG。 至于ModelTransformationTag我没用过不了解&#xff0c;但是应该是关于仿射变换相关的&#xff0c;用于将像素坐标与地理/投影坐标进行转换的矩阵。 对于…
阅读更多...
k8s kubernetes 1.23.6 + flannel公网环境安装

k8s kubernetes 1.23.6 + flannel公网环境安装

准备环境&#xff0c;必须是同一个云服务厂商&#xff0c;如&#xff1a;华为&#xff0c;阿里、腾讯等&#xff0c;不要存在跨平台安装K8S&#xff0c;跨平台安装需要处理网络隧道才能实现所有节点在一个网络集群中&#xff0c;这里推荐使用同一家云服务厂商安装即可 这里使用…
阅读更多...
黄金眼PAAS化数据服务DIFF测试工具的建设实践 | 京东云技术团队

黄金眼PAAS化数据服务DIFF测试工具的建设实践 | 京东云技术团队

一、背景介绍 黄金眼PAAS化数据服务是一系列实现相同指标服务协议的数据服务&#xff0c;各个服务间按照所生产指标的主题作划分&#xff0c;比如交易实时服务提供实时交易指标的查询&#xff0c;财务离线服务提供离线财务指标的查询。黄金眼PAAS化数据服务支撑了黄金眼APP、黄…
阅读更多...
用Python获取网络数据

用Python获取网络数据

用Python获取网络数据 网络数据采集是 Python 语言非常擅长的领域&#xff0c;上节课我们讲到&#xff0c;实现网络数据采集的程序通常称之为网络爬虫或蜘蛛程序。即便是在大数据时代&#xff0c;数据对于中小企业来说仍然是硬伤和短板&#xff0c;有些数据需要通过开放或付费…
阅读更多...
b树和b+树

b树和b+树

二叉树和平衡二叉树 二叉树&#xff0c;每个节点支持两个分支的树结构&#xff0c;相比于单向链表&#xff0c;多了一个分支。 二叉查找树&#xff0c;在二叉树的基础上增加了一个规则&#xff0c;左子树的所有节点的值都小于它的根 节点&#xff0c;右子树的所有子节点都大于它…
阅读更多...
了解活动聊天机器人如何革新活动行业

了解活动聊天机器人如何革新活动行业

在如今快节奏的时代&#xff0c;活动策划和管理对于任何活动的成功变得至关重要。无论是会议、展览会还是企业聚会&#xff0c;组织者都努力为参与者创造难忘的体验&#xff0c;同时确保幕后的顺利执行。然而&#xff0c;由于有许多任务需要处理且资源有限&#xff0c;管理活动…
阅读更多...
双指针——盛水最多的容器

双指针——盛水最多的容器

一, 题目要求 给定一个长度为 n 的整数数组 height 。有 n 条垂线&#xff0c;第 i 条线的两个端点是 (i, 0) 和 (i, height[i]) 。 找出其中的两条线&#xff0c;使得它们与 x 轴共同构成的容器可以容纳最多的水。 返回容器可以储存的最大水量。 说明&#xff1a;你不能倾斜容…
阅读更多...
Django小白开发指南

Django小白开发指南

文章目录 HTTP协议socket实现一个web服务器WSGI实现一个web服务器WSGI实现支持多URL的web服务器WSGI实现图片显示的web服务器MVC && MTV1.MVC2.MTV3.总结 一、创建Django项目1.创建项目2.创建app3.第一次django 请求 二、模板1.配置settings.py2.模板语法3.继承模板 三…
阅读更多...
LLM ReAct: 将推理和行为相结合的通用范式 学习记录

LLM ReAct: 将推理和行为相结合的通用范式 学习记录

LLM ReAct 什么是ReAct? LLM ReAct 是一种将推理和行为相结合的通用范式，可以让大型语言模型（LLM）根据逻辑推理（Reason），构建完整系列行动（Act），从而达成期望目标。LLM ReAct 可以应用于多种语言和决策任务，例如问答、事实验证、交互式决策等，提高了 LLM 的效率、…
阅读更多...
小程序搭建OA项目首页布局界面

小程序搭建OA项目首页布局界面

首先让我们来学习以下Flex布局 一&#xff0c;Flex布局简介 布局的传统解决方案&#xff0c;基于盒状模型&#xff0c;依赖 display属性 position属性 float属性 Flex布局简介 Flex是Flexible Box的缩写&#xff0c;意为”弹性布局”&#xff0c;用来为盒状模型提供最大的…
阅读更多...
centos 7.9 安装sshpass

centos 7.9 安装sshpass

1.作用 sshpass是一个用于非交互式SSH密码验证的实用程序。它可以用于自动输入密码以进行SSH登录&#xff0c;从而简化了自动化脚本和批处理作业中的SSH连接过程。 sshpass命令可以与ssh命令一起使用&#xff0c;通过在命令行中提供密码参数来执行远程命令。以下是一个示例命…
阅读更多...
客观来说这两年确实是香港优才计划申请的红利期！

客观来说这两年确实是香港优才计划申请的红利期！

客观来说这两年确实是香港优才计划申请的红利期&#xff01; 最明显的网上关于香港优才计划申请的帖子都比之前多了不少&#xff0c;首页经常随便一刷就是分享香港优才计划申请攻略的。 今年以来香港优才计划的政策也发生了很多变化&#xff1a; 1、取消年度配额限制&#xff0…
阅读更多...
常见面试题-Redis专栏（一）

常见面试题-Redis专栏（一）

typora-copy-images-to: imgs了解 redis 中的大key吗&#xff1f;多大算是大key呢&#xff1f;如何解决&#xff1f; 答&#xff1a; redis 的大 key 指的是 key 对应的 value 所占用的内存比较大。 对于 string 类型来说&#xff0c;一般情况下超过 10KB 则认为是大 key&…
阅读更多...
周记之学习总结

周记之学习总结

你在人群中看到的每一个耀眼的女孩&#xff0c;都是踩着刀尖过来的。你如履平地般地舒适坦然&#xff0c;当然不配拥有任何光芒&#xff1b; 10.11-10.12 思来想去还是不舍得&#xff0c;搞了一下这个jwt&#xff0c;看了很多视频和博客&#xff0c;一直没看懂&#xff0c;两…
阅读更多...
增加并行度后，发现Flink窗口不会计算的问题。

增加并行度后，发现Flink窗口不会计算的问题。

文章目录 前言一、现象二、结论三、解决 前言 窗口没有关闭计算的问题&#xff0c;一直困扰了很久&#xff0c;经过多次验证&#xff0c;确定了问题的根源。 一、现象 Flink使用了window&#xff0c;同时使用了watermark &#xff0c;并且还设置了较高的并行度。生产是设置了…
阅读更多...
从入门到进阶 之 ElasticSearch 节点配置 集群篇

从入门到进阶 之 ElasticSearch 节点配置 集群篇

&#x1f339; 以上分享 ElasticSearch 安装部署&#xff0c;如有问题请指教写。&#x1f339;&#x1f339; 如你对技术也感兴趣&#xff0c;欢迎交流。&#x1f339;&#x1f339;&#x1f339; 如有需要&#xff0c;请&#x1f44d;点赞&#x1f496;收藏&#x1f431;‍&a…
阅读更多...
PHP 变量

PHP 变量

变量 变量的声明、使用、释放 变量定义 形式 $ 变量名;严格区分大小写 $name; $Name; $NAME //三个变量不是同一个变量字母、数字、下划线组成&#xff0c;不能以数字开头&#xff0c;不能包含其他字符(空白字符、特殊字符) 驼峰式命名法、下划线式命名法 $first_name; $fi…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023