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

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

背景

一个表的字符集为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使用不到索引,引起严重的性能问题;

参考

http://www.voidcn.com/article/p-xwbolqxw-bcx.html

展开阅读全文

没有更多推荐了,返回首页