mysql左关联不走索引的一个问题的排查过程及思考

今天遇到一个mysql查询不走索引的情况,记录一下排查过程及解决办法
Mysql版本 5.6 5.7

错误情况

两表关联,两表数据量不超过10w,查询速度极慢,基本约等于查不出来.

测试:
表1 user表 数据量10w
表2 user_role表 数据量10w

查询语句

select * from user_role a left join user b on a.user_no = b.user_no

排查过程

查看执行过程

发现a表并没有走索引,全表扫描
而b表的Extra里的信息显示使用了BNL算法,效率很低(相较于索引) 感兴趣的可以搜一下Using join buffer (Block Nested Loop)

1. 排查关联条件字段类型 一致
2. 排查索引是否正确 没问题

尝试修改sql如下

select * from user_role a , user b where a.user_no = b.user_no

发现where的情况下是走索引的.

再次尝试修改sql,调换left join的两个表的位置:

select * from user a left join user_role b on a.user_no = b.user_no

发现依然可以走索引

经过上面的两个测试,排除数据库的故障问题,猜测还是跟表字段/索引有关系

经过仔细排查后,发现了重大嫌疑对象:
user_role表的user_no字段的字符集是utf8mb4,与user表的user_no字符集不一致

修改字符集一致后再次测试,完美解决

延伸思考

上文中我们尝试修改了两次sql,为什么字符集不一致的情况下,使用等连接和调换两表位置后可以正常查询呢?

这个搜了一些内容,没啥结果,希望知道的童鞋告知~

总结

其实还是索引问题,总结一下不走索引的原因,或者说关联条件走索引的必要条件:

1. 关联条件字段类型保持一致
2. 关联条件字段字符集保持一致
3. 关联条件不能使用函数
4. 模糊匹配不能使用前模糊 ‘%hh’
5. 正则表达式不走索引