mysql联表时,联表条件的索引失效
序:因为在项目开发中,有个查询功能非常的慢,连了五六张表,然后我就用explain分析了一下,发现两张大表连接时竟然没用上索引。
a left join b on a.ccc = b.yyyy
ccc和yyyy都分别建有索引
baidu一下,大家都说的差不多(一些什么情况可能会导致索引失效,以后整理一下,这里就不说了),我就问同事,为什么这两个字段都建立了索引就是用不上呢,同事突然灵光一动“是不是字符编码不一样”。
我赶紧去两个表查看这两个字段的字符编码,“嗯,是一样的。”
——但是!排序规则不一样,如下图:
我用这两个字段相等进行连表:a.ccc = b.yyyy(假设)
可以看到,这两个字段的字符集是一样的,都是utf8mb4
而排序规则不一样,a表的ccc的排序规则是utfmb4_general_ci,而b表的yyyy的排序规则是utfmb4_bin,因为字符集不同会导致索引失效,所以我合理怀疑排序规则不同也会导致索引失效。
然后,我就让我们开发经理将两个字段的排序规则修改一致,再次explain,果然索引用上了!!!(瞬间从20多秒减少到0点几秒,当然还需继续优化)
——在此记录一下,如果也有别的小伙伴遇到了这样的问题,可以参考一下哦~
附:
各种排序规则的大致区别:
(utf8_bin,utf8_unicode_ci、utf8_general_cs、utf8_general_ci等等)
以_bin结尾的表示用二进制数据编译存储,是区分大小写的
以_ci结尾的表示不区分大小写(ci是case insensetive)
以_cs结尾的表示区分大小写(cs是case sensetive)
utf8_unicode_ci:通常用unicode对中文字符做处理,防止出现乱码,在英文下没有区别
一般来说,设置了字符集,也就设置了默认的排序规则,比如:
字符集:utf8
排序规则默认就是:utf8_general_ci
更多深入了解就需要自己去找Baidu了。