mysql 连表操作后字符集不同导致索引失效

发布时间：2019-08-22作者：laosun阅读（2419）

mysql 连表操作后字符集不同导致索引失效，字段1是utf8mb4字符集，字段2是utf8字符集。字符集转换遵循由小到大的原则，因为utf8mb4是utf8的超集，所以这里把utf8转换成utf8mb4，即把t1.code转换成utf8mb4字符集，转换了之后，由于t1.code上面的索引仍然是utf8字符集，所以这个索引就被执行计划忽略了，然后t1表只能选择全表扫描。更糟糕的是，如果t2筛选出来的记录不止1条，那么t1就会被全表扫描多次，性能之差可想而知

一个表的字符集为utf8mb4

一个表的字符集为utf8




连表查询后可以发现索引失效的情况，查询很慢。这时候可以通过show warnings;查询警告信息。发现了convert(testdb.t1.code using utf8mb4)之后，发现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就会被全表扫描多次，性能之差可想而知。




修改字符集

alter table t1 charset utf8mb4;




但这是错的，这只是改了表的默认字符集，即新的字段才会使用utf8mb4，已经存在的字段仍然是utf8。

show create table t1 会发现DEFAULT CHARSET=utf8mb4改变了，但是SHOW FULL COLUMNS FROM pay_log_all_from_mq;会发现字段的字符集没有改变。

alter table t1 convert to charset utf8mb4;




查看 SHOW FULL COLUMNS FROM t1 ;会发现字段的字符已经改变。




注意点

表字符集不同时，可能导致join的SQL使用不到索引，引起严重的性能问题；




下边这篇文章是如何将utf8库转换成utf8mb4编码的文章，大家可以参考一下

MySQL中 utf8 和 utf8mb4的选择使用，切记不要使用utf8的编码了

 发表评论

 请文明留言

  发表

共 0 条评论