1. 概述
昨天在一位同学的MySQL机器上面发现了这样一个问题，MySQL两张表做left join时，执行计划里面显示有一张表使用了全表扫描，扫描全表近100万行记录，大并发的这样的SQL过来数据库变得几乎不可用了，今天和大家一起分享下这个问题的原因及解决办法，希望可以帮助大家更好的学习MySQL数据库，一起来看看吧。MySQL版本为官方5.7.13。
2. 问题重现
首先创建表：
插入测试数据：
2张表left join的执行计划如下：
可以明显地看到，t2.name = 'dddd'使用了索引，而t1.code = t2.code这个关联条件没有使用到t1.code上面的索引，一开始Scott也百思不得其解，但是机器不会骗人。Scott用show warnings查看改写后的执行计划如下：
在发现了convert(testdb.t1.code using utf8mb4)之后，Scott发现2个表的字符集不一样。t1为utf8，t2为utf8mb4。但是为什么表字符集不一样（实际是字段字符集不一样）就会导致t1全表扫描呢？下面来做分析。
（1）首先t2 left join t1决定了t2是驱动表，这一步相当于执行了select * from t2 where t2.name = ‘dddd’，取出code字段的值，这里为’8a77a32a7e0825f7c8634226105c42e5’;
（2）然后拿t2查到的code的值根据join条件去t1里面查找，这一步就相当于执行了select * from t1 where t1.code = ‘8a77a32a7e0825f7c8634226105c42e5’;
（3）但是由于第（1）步里面t2表取出的code字段是utf8mb4字符集，而t1表里面的code是utf8字符集，这里需要做字符集转换，字符集转换遵循由小到大的原则，因为utf8mb4是utf8的超集，所以这里把utf8转换成utf8mb4，即把t1.code转换成utf8mb4字符集，转换了之后，由于t1.code上面的索引仍然是utf8字符集，所以这个索引就被执行计划忽略了，然后t1表只能选择全表扫描。更糟糕的是，如果t2筛选出来的记录不止1条，那么t1就会被全表扫描多次，性能之差可想而知。
3. 问题解决
既然原因已经清楚了，如何解决呢？当然是改字符集了，把t1改成和t2一样或者把t2改成t1都可以，这里选择把t1转成utf8mb4。那怎么转字符集呢？
有的同学会说用alter table t1 charset utf8mb4;但这是错的，这只是改了表的默认字符集，即新的字段才会使用utf8mb4，已经存在的字段仍然是utf8。
只有用alter table t1 convert to charset utf8mb4;才是正确的。
但是还要注意一点，alter table 改字符集的操作是阻塞写的（用lock = node会报错）所以业务高峰时请不要操作，即使在业务低峰时期，大表的操作仍然建议使用pt-online-schema-change在线修改字符集。
mysql> alter table t1 convert to charset utf8mb4, lock=none;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Cannot change column type INPLACE. Try LOCK=SHARED.
现在再来查看执行计划，可以看到已经没问题了。
4. 注意点
（1）表字符集不同时，可能导致join的SQL使用不到索引，引起严重的性能问题；
（2）SQL上线前要做好SQL Review工作，尽量在和生产环境一样的环境下Review；
（3）改字符集的alter table操作会阻塞写，尽量在业务低峰操作，建议用pt-online-schema-change;
（4）表结构字符集要保持一致，发布时要做好审核工作；
（5）如果要大批量修改表的字符集，同样做好SQL的Review工作，关联的表的字符集一起做修改。
5. 问题讨论
最后问一个问题，假设现在t1和t2表的字符集还未修改，如果上面那个问题SQL换成如下（即把t2 left join t1换成t1 left join t2），还会出现索引失效问题吗？为什么？
select * from t1 join t2 on t1.code = t2.code where t1.name = 'dddd'
参考文章：InsideMySQL
网友评论