MySQL 关于表字符集不一致引起的一次慢查询问题

问题描述

数据库版本5.7.3，目前库里面有两张表。
表A数据量12万+，主键id，表字符集utf8；
表B数据量15万+，主键id，表字符集utf8mb4；
问题1：
要求输出A表中id在B表中不存在的数据

SQL1 select * from A left join B on A.id = B.id where B.id is null;
SQL2 select * from A where id not in (select id from B)；

SQL1执行结果耗时一秒不到，SQL2执行结果耗时一秒出头

问题2：
要求输出B表中id在A表中不存在的数据

SQL3 select * from B left join A on B.id = A.id where A.id is null;

出现问题，SQL一直运行无结果输出

分析问题

使用explain查看SQL执行计划

SQL1：A表全表扫描，B表走主键索引。
A表参数：rows = 12万+、filtered = 100
B表参数：rows = 1、filtered = 100
很好

SQL3：B表全表扫描，A表走主键索引。
B表参数：rows = 15万+、filtered = 100
A表参数：rows = 12万+、filtered = 100
开始不理解，为什么A表走索引还会全表扫描，执行 12万*15万=180亿数据量，怪不得不出结果。

查询资料：
1.当使用left join时，左表是驱动表，右表是被驱动表
2.当使用right join时，右表是驱动表，左表是被驱动表
3.当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表
每次在树搜索里面做一次查找都是log(n), 所以对比的是Alog(B)和
Blog(A)，写法上理应是小表驱动大表，但是现在业务上需要这样写。
继续检查

解决问题

查看两个表结构：

show create table A;
show create table B;

发现两个表的字符集不一致
开始测试
latin1 去 join utf8 时，索引是正常使用的，反过来虽然走索引但还是全表扫描。又测试了 utf8 和 utf8mb4 的情况，utf8 去 join utf8mb4 正常，反过来则索引失效。

猜想原因：被驱动表字段的字符集更大时，索引可以正常使用，反之则索引失效。

新建B_test测试表，字符集改为utf8，导入B表数据，测试

SQL4 select * from B_test B left join A on B.id = A.id where A.id is null;

查寻结果秒出，问题解决
后续修改B表字符集