字符集不一致带来的性能大坑

排查过程

     一个区块链的业务即将上线，研发在做上线前的测试工作，晚上在review当天的工作的时候接到研发同事打来的电话，反馈测试的时候会出现SQL很慢的情况。登录DB查看，发现基本都是同一类型的慢SQL导致服务器的负载飙升，从而导致简单的SQL也出现响应慢的问题，下面是截取了部分show full processlist的现场：

 

尝试explain查看SQL的执行计划，发现扫描的记录很多，执行计划如下：

 

通过查看bl_record表和bl_record _name表的索引，发现record_id在bl_record表上是主键，在bl_record _name上是索引，bl_record _name表的name_value也有索引，而且过滤性很好。下面是name_value字段值为BJ的总记录数，可以看出过滤性是非常好的。

 

比较奇怪，record_id在bl_record表上是主键，在bl_record _name上是索引，而且bl_record _name表的name_value字段过滤型非常强，并且这个字段是索引，正常情况下应该是使用bl_record表的record_id主键索引和使用bl_record _name的name_value字段索引。

 

尝试对SQL进行改写，修改成in的形式，继续观察：

 select * from bl_record where record_id in (select record_id from bl_record_name where name_value='BJ' and list_name in ('SAN', 'MPSTE', 'SOC', 'OOL', 'PRIVATE', 'RCA', 'COUNTRY', 'XJ_TERROR', 'SI', 'OEL', 'SOR', 'HT100', 'PEP', 'UNCON', 'TERROR')) ;

发现bl_record这个表变成了全表扫描，奇怪，正常应该能用到record_id列的主键索引才对。

 

再尝试对SQL进行简化：

select * from bl_record where record_id in ('SI_4848788','SI_960475');

从上图可以看到单纯的记录是可以使用到主键索引的。

 

脑子里突然灵光一现，是否bl_record表和bl_record_name表的record_id字段类型不一样？或者是两个表的字符集不一样？

带着问题继续排查，发现两个表字段类型是一样的，但是字符集不一样，一个是utf8mb4，一个是utf8，如下截图所示：

 

 

确认问题后，将bl_record表的字符集修改为utf8mb4

ALTER TABLE bl_record CONVERT TO CHARACTER SET utf8mb4;

 

修改完成后，再explain看看，发现SQL的能完美的使用到对应的索引了。

再观察这个SQL的运行，执行非常快，机器负载立马下降到到低负载水平。

 

原因分析

这里的本质原因是record_id字段在做比较的时候做了字符集转换，导致索引没有使用上，我们可以看一下explain完了以后的warning对应的内容就明白了。

这里个其实也很容易理解，在进行比较的时候，由于字符集不一致，mysql将bl_record表的record_id字段转换成utfmb4（ 字符集转换遵循由小到大的原则，utf8mb4是utf8的超集，所以这里把utf8转换成utf8mb4），转换后，对应的record_id的值是utfmb4字符集了，但是bl_record表的record_id字段的索引还是utf8的字符集，因此索引就没办法使用上。

 

问题总结

在mysql中经常会遇到一些无法正确使用索引的场景，概括如下，希望大家在设计表或者编写SQL的时候注意这些坑：

1、字符集或者校对规则不同的表之间进行join操作的时候，会由于字符集转换导致索引无法很好的利用；

2、字段类型不一致的时候，会由于字段类型转换导致表连接查询无法使用索引；

3、编写SQL的时候，对于int类型的条件查询不添加单引号，对应varchar或者char类型的条件查询添加单引号；

4、做好表结构审核和SQL review；