今天遇到一个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)
- 排查关联条件字段类型 一致
- 排查索引是否正确 没问题
尝试修改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,为什么字符集不一致的情况下,使用等连接和调换两表位置后可以正常查询呢?
这个搜了一些内容,没啥结果,希望知道的童鞋告知~
总结
其实还是索引问题,总结一下不走索引的原因,或者说关联条件走索引的必要条件:
- 关联条件字段类型保持一致
- 关联条件字段字符集保持一致
- 关联条件不能使用函数
- 模糊匹配不能使用前模糊 '%hh'
- 正则表达式不走索引
