- 1vmware centos8.0使用NAT配置网络_centos8 nat
- 2【渗透测试】JWT令牌漏洞攻击_jwt渗透
- 3git远程库与本地联系报错：fatal: Not a git repository (or any of the parent directories): .git_$ git remote -v fatal: not a git repository (or an
- 4LLM充当数据分析Agent，如何实现？
- 5软件部署-NEMO在LIinux系统上的安装教程_nemo搭建
- 6（服务端篇）如何在本地私有化部署chatgpt？github上那些助力搭建私有chatgpt的项目_本地部署chatgpt
- 7day15-硅谷课堂-直播对接和微信分享_zhibodun
- 8设置SqlServer服务启动方式及网络配置_sqlserver网络配置在哪
- 9Live2D虚拟人物应用(前端vue3)_vue引入live2d
- 1010 个实用的 Python 自动化脚本_自动化脚本的代码
数据库系列之MySQL中Join语句优化问题_数据库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
- 1
2）左外连接LEFT OUTER JOIN
左外连接是将左表作为主表，右表作为从表，循环遍历右表，查找与条件满足的项，如果在右表中没有匹配的项，则补空值，并且在结果集中选择只在左表中存在的数据。对应的SQL语句如下：
SELECT * FROM TableA a RIGHT JOIN TableB b ON a.KEY = b.KEY WHERE b.KEY IS NULL
- 1
3）右连接RIGHT JOIN
右连接是将右表作为主表，左表作为从表。右表中的所有记录作为外层循环，在左表中进行匹配，如果左表中没有匹配的行，则将右表记录的左表项补空值。对应的SQL语句如下：
SELECT * FROM TableA a RIGHT JOIN TableB b ON a.KEY = b.KEY
- 1
4）右外连接RIGHT OUTER JOIN
右外连接选择将右表作为主表，左表作为从表，循环遍历左表，查找与join条件满足的项，如果在左表中没有匹配的项，则补空值，并且在结果集中选择只在右表中存在的数据。对应的SQL语句如下：
SELECT * FROM TableA a RIGHT JOIN TableB b ON a.KEY = b.KEY WHERE a.KEY IS NULL
- 1
5）内连接INNER JOIN
内连接是将左表和右表对于条件相匹配的项进行组合，返回相关列值相等的结果。对应的SQL语句如下：
SELECT * FROM TableA a INNER JOIN TableB b ON a.KEY = b.KEY
- 1
6）全连接FULL JOIN
全连接是将左表和右表的所有记录进行匹配，如果在另外表项中不存在记录，则补空值。对应的SQL语句如下：
SELECT * FROM TableA a FULL OUTER JOIN TableB b ON a.KEY = b.KEY
- 1
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
- 1
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的执行流程如下图所示：
- SQL从外表T2中读取一行记录，取出关联字段id到内表T1中逐条查找；
- 取出T1表中满足条件的记录与T2表中获取的结果进行合并，并将结果放入结果集中；
- 循环上述过程直到无法满足条件，将结果返回给客户端。
Nested Loop Join伪代码实现如下：
for each row in t1 matching range {
for each row in t2 matching reference key {
if(t1.id==t2.id) {
//返回结果
}
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
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执行过程如下所示：
- SQL从外表T2中读取一行记录，取出关联字段id到内表T1的索引树中查找；
- 从T1表中取出辅助索引树中满足条件的记录查到主键ID，再到主键索引中根据主键ID将剩下字段的数据取出与T2中获取到的结果进行合并，并将结果放入结果集
- 循环上述过程直到无法满足条件，将结果返回给客户端。
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执行过程如下所示：
- 将外表T2中的数据读入到join_buffer中（默认内存大小为256k,如果数据量多,会进行分段存放,然后进行比较）
- 把表T1的每一行数据，跟join_buffer中的数据批量进行对比，匹配的数据与T2表中获取的结果进行合并，并将结果放入结果集中；
- 循环以上步骤直到无法满足条件，将结果集返回给客户端
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_switch
Value: 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
- 1
- 2
- 3
- 4
- 5
2）查看join_buffer_size参数配置
Show variables like 'join_buffer_size%';
mysql> show variables like 'join_buffer_size';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
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列进行全扫描后排序，然后逐行比较它们以找到匹配的行。执行过程如下所示：
- 对两个表T1和T2进行排序，排序可以通过使用索引或执行额外的排序操作来完成。确保两个表中的数据按照连接键（JOIN条件）进行排序。
- 排序完成后，对两张表逐行进行比较和归并操作，从每个表中选取若港行并检查是否满足Join条件，如果满足Join条件将匹配的行添加到结果集中。
- 继续从每个表中选取下一行，并重复以上步骤直到扫描完所有行。
以上图为例，两个表T1和T2已经排好序进行等值Join连接查询，并假设内存中buffer能容纳2条记录：
- 首先取出T1(1,3)和T2(1,4)进行比较，此时只有(1,1)是匹配的，把记录1放入结果集中
- 再依次取出T1和T2表中的其它值，T1中的(5,15)和T2中的(5,10)，因为是等值Join，T1中的3和T2中的4被舍弃了。此时匹配到(5,5)并将记录5放入结果集中
- 进行新的一轮循环，直到两个表中的记录比对完成
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)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
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表。
- Build过程：遍历外表，以连接条件”countries.country_id”为hash key，查询需要的列作为value创建hash表。通常优化器优先选择占用内存最小的表作为外表构建hash表。
- 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 |
+------------------+--------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
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()
begin
declare 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()
begin
declare 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();
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
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)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
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)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
测试外连接
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)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
测试不等连接
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)
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
可以看到在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;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
已经变成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)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
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)
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
对比看到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优化器控制的。
参考资料：
- https://www.cnblogs.com/mlcn-2000/p/14604236.html
- https://blog.csdn.net/chuyanju7641/article/details/110524107
- MySQL Join算法与调优白皮书，姜
- https://blog.csdn.net/orangleliu/article/details/72850659
- https://dev.mysql.com/blog-archive/hash-join-in-mysql-8/
- https://blog.csdn.net/ddhe9527/article/details/103659977
- https://dev.mysql.com/doc/refman/8.1/en/hash-joins.html
