一、问题复盘与解决

1.问题描述

生产上有一段sql，大概几百行，做的是inser into …select ;的操作，但是执行了一个多小时都出不来，正常情况下不会这么久，通过一步步定位，最后发现是其中某一段的join 特别慢导致的，后面对这段sql重点分析发现，这里的表竟然没有走索引，对于几十万以上的表之间的连接，若是没有索引那就是灾难性的，加不加索引就好比一个人力三轮与超跑的区别。至于为什么没走索引最后定位到了是因为字符集与排序规则与其他表不一致，所以导致了索引失效。下面是犯罪现场：

..........
left join 
 (
  select
   tmvrr.project_code,
   COUNT(1) as project_abnormal_sum, 
   COUNT(DISTINCT tmvrr.customer_id, tmvrr.room_relation_id) as customer_room_car_sum, 
   sum(case when tmvrr.rule_type = '1' then 1 else 0 end) as project_accuracy, 
   sum(case when tmvrr.rule_type = '2' then 1 else 0 end) as project_integrity, 
   sum(case when tmvrr.rule_type = '3' then 1 else 0 end) as project_timeliness, 
   case when tmvrr.estate_type in (1, 3) then '住宅' else '非住宅' end as estate_type  
  FROM
   sunacwy_mdm.tb_mdm_verification_rules_result tmvrr
  left join sunacwy_mdm.tb_unqualify_sc_pool tusp on 
   tmvrr.project_code = tusp.project_code
   and tmvrr.customer_id = tusp.mdm_data_id
   and tmvrr.rule_id = tusp.rule_codes
   and tmvrr.room_relation_id = tusp.mdm_data_id2
  where
   tmvrr.group_code <> ''
   and (tusp.project_code is null or tmvrr.commit_content <> tusp.commit_content)  
  group by
   tmvrr.project_code,
   case when tmvrr.estate_type in (1, 3) then '住宅' else '非住宅' end 
 ) tmvrr on 
  base_org.project_code  = tmvrr.project_code and base_org.estate_type   = tmvrr.estate_type
 .......

2.问题解决

问题解决其实很简单，就是将所有字段的字符集和排序规则更改成了mysql5.7的默认值（笔者生产库是mysql5.7），字符集是utf8mb4,排序规则是utf8_general_ci，然后问题就解决了，更改完以后只能说是天差地别。之前几个小时出不来，更改后几分钟就ok了。

-- 更改全表的字符集与排序规则，除了字符串其他字段其实无需指定排序规则，指定了也不生效
ALTER TABLE tb_mdm_verification_rules_result CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ;
-- 单个字段的更改排序规则
alter table tb_mdm_verification_rules_result change group_name  group_name varchar(50) character set utf8mb4 collate utf8mb4_general_ci COMMENT '集团名称';

3.总结

其实产生原因很简单，但是找起来确实费了一番劲，这与explain使用不是这么熟练也是有关系的（当然最坑的是建表的人干得事）下面一节笔者会复现这个问题，并分析怎么排查这种问题，方便下次碰到一眼就可以解决。

二、mysql5.7与mysql8.0.3中问题复现

无论mysql5.7还是mysql8.0.3都具有相同的以下环境，我们就使用两个表的连接来复现该问题。

### 环境 预设###
表：tb_mdm_unit ，数据量：54280，索引字段：id
表：tb_mdm_floor，数据量：441849，索引字段：unit_id

1.mysql5.7环境问题复现

①相同字符集和排序规则时count

这里字符集都是utf8mb4，排序规则都是utf8mb4_general_ci

select count(1) from tb_mdm_unit un 
left join tb_mdm_floor fl on un.id = fl.unit_id ;

可以看到此时非常的快，只需要0.5s就出来结果了。

②看下相同字符集和排序规则的执行计划

执行计划key列里是关联条件使用的索引，rows是索引关联到的行数（这个是效率高低的直观体现），Extra是非常重要的一列，sql真正底层在怎么运行是需要看这列的,Using index表示关联完全走了索引，是很高效的行为了。

③不相同字符集和排序规则count

alter table tb_mdm_floor change unit_id unit_id char(20) character set utf8 collate utf8_bin DEFAULT NULL COMMENT '单元id';
select count(1) from tb_mdm_unit un 
left join tb_mdm_floor fl on un.id = fl.unit_id ;

可以看到此时是巨慢的，到达92s仍是没有出来结果，只能终止这个进程了。由此可见在更改了字符集和排序规则以后对sql的影响是灾难性的。

④看下不相同字符集和排序规则的执行计划

对比这个执行计划与上面那个，可以发现 rows列和Extra列有很大区别，rows列的第二行,由6直接变成了435420，那这个数字意味着什么呢？这个问题先放下，可以先看下Extra列，这一列由Using index变成了Using where; Using index; Using join buffer (Block Nested Loop)。出现Using where 其实是因为mysql执行优化器对于这条sql进行了优化，优化后的sql 会在末尾拼接 “where 1”，所以这里有个Using where（有兴趣的话，使用show warnings，即可看到这个执行优化器优化后的sql）。出现Using index是因为，确实走到了索引，但是因为字符集不一致即使走了也相当于没走，其实此时索引是不生效的。最后一个要说的也是最重要的是：Using join buffer (Block Nested Loop)；当sql中出现外连时若是连接条件不是索引，Extra中就会提示这个提示（想要详细了解的建议看下这个博主的这篇文章写得还是不错的：MySQL Join原理），其实mysql5.5之前若是没有索引就是会出现笛卡尔积，mysql5.5之后8.0之前就是使用的Block Nested Loop来解决join时没有索引的问题，这种算法虽然比笛卡尔积效率高很多，但是对于大数据量的表来说还是无法承受的，所以上面的查询结果还是出不来，mysql8.0.19之后则采用了hash join 来解决连表时不走索引的问题。

2.mysql8.0环境问题复现

①相同字符集和排序规则时count

这里字符集都是utf8mb4，排序规则都是utf8mb4_general_ci（mysql8.0以后默认排序规则不是他了，这里为了验证保持统一）

select count(1) from tb_mdm_unit un 
left join tb_mdm_floor fl on un.id = fl.unit_id ;

下面是执行结果，可以看到0.49s很快就出来了

②看下相同字符集和排序规则的执行计划

Extra列都是Using index，说明连接完全走的索引，key列也没有区别，有点区别的是rows，可以发现第二行只有1，在5.7里这个数是6，不过这个区别和今天验证的问题没有关系，这个区别能说明，mysql8的索引比mysql5.7的索引更高效（所以建议能将mysql5.7升级到5.8的就升级吧）。

③不相同字符集和排序规则count

alter table tb_mdm_floor change unit_id unit_id char(20) character set utf8 collate utf8_bin DEFAULT NULL COMMENT '单元id';
select count(1) from tb_mdm_unit un 
left join tb_mdm_floor fl on un.id = fl.unit_id ;

在mysql8.0中将字符集更改过以后好像还是很快，只是比以前慢了一点，并没有像5.7那样根本出不来结果，这是为什么呢？其实mysql8以后（准确说是8.0.19以后）对不走索引的join进行了优化，如果么有索引那么就使用hash算法将等值连接的两个值都使用hash算法进行计算，然后使用计算后的结果进行连接（有点像是给你建了两个hash索引一样），这样也会提升很多的速度。具体看下执行计划。

④看下不相同字符集和排序规则的执行计划

对比这个执行计划与mysql5.7同场景的执行计划可以发现，唯一的不同点就是 Using join buffer (Block Nested Loop) 变成了 Using join buffer (hash join)。上面也简单说了其实是mysql8对不走索引的等值连接进行的优化，等值连接的优化算法由Block Nested Loop变成了hash join。hash join 相当于是额外建立了两个hash索引，所以速度还是很快的，但是这个速度肯定不如走原来的索引快。

3.总结

left join 和right join 在 mysql5.7中若是连接条件不是索引，或者索引失效了，则会导致数据量的剧增，此时使用的连接算法BNL相当于是进行局部的笛卡尔积，效率也是很低的，到了mysql8.0以后，若是索引实现会使用hash算法对连接条件进行关联，这个效率是很高的，基本接近走索引的效率了。所以说升级到mysql8.0好处还是显而易见的（什么？msyql8.0还有什么优化？可以看看这一篇：关联子查询的前世今生），实在升级不了，那么我们就需要根据执行计划，来看连接表是否出现了BNL算法出现的情况，一旦出现请关注索引的建立和失效情况，不然sql的性能会非常低下。

4.延展问题：相同字符集不同排序规则会影响索引吗？

只调整排序规则，sql如下：

alter table tb_mdm_floor change unit_id unit_id char(20) character set utf8mb4 collate utf8mb4_polish_ci DEFAULT NULL COMMENT '单元id';
select count(1) from tb_mdm_unit un 
left join tb_mdm_floor fl on un.id = fl.unit_id ;

执行结果如下，直接报错，事实上笔者换了多个排序规则，都是会报这个错误，所以相同字符集不同排序规则会不会导致索引失效，直接就不用看了，因为sql执行不了。

4.延展问题：字符集为什么导致索引失效？

其实很简单，两个字段不是使用字符编码，就没办法判断相等，建立的索引其实也是一个列，这个列的字符集合别的列不同，那就无法比较是否相等，自然就会造成索引失效了。