记一次sql join没有走索引的问题及排查原因

问题描述

有个业务需要根据文件与标签之间的关联关系进行查询操作，而文件表拥有一百万的数据量，导致关联非常耗时。通过执行EXPLAIN排查问题后发现数据库执行LEFT JOIN关联操作时没有走文件表的索引，如下图所示，type为all表示查询整表。

问题排查

在排除了基数小(表数据量少，可使用foce index)、数据类型不匹配(关联字段类型及长度)、前导模糊查询(like)、索引列有函数等导致索引失效的问题后最终确认是表的字符集不同，relation与label表是utf8mb4，而file表是utf8。

解决方案

对file表执行修改字符集的SQL语句：

ALTER TABLE file_table CONVERT TO CHARACTER SET utf8mb4;

之后再进行EXPLAIN分析语句可看到file表的type变为了eq_ref，此时再进行查询耗时从接近10秒缩减至0.04秒。

原理

两表的字符集不一样，由于utf8mb4是utf8的超集，在进行联表操作时会对utf8表的字段进行一次转换编码的操作，相当于对索引字段加了convert函数，而索引字段存在函数会导致索引失去有序性（函数的结果是无序的），就只能通过全表扫描进行查询了。