MySQL索引失效有哪些原因

62次阅读
没有评论

共计 2279 个字符,预计需要花费 6 分钟才能阅读完成。

MySQL 索引失效有哪些原因,相信很多没有经验的人对此束手无策,为此本文总结了问题出现的原因和解决方法,通过这篇文章希望你能解决这个问题。

单个索引

1、使用!= 或者 导致索引失效

SELECT * FROM `user` WHERE `name` !=  冰峰 

我们给 name 字段建立了索引,但是如果!= 或者 这种都会导致索引失效,进行全表扫描,所以如果数据量大的话,谨慎使用

可以通过分析 SQL 看到,type 类型是 ALL,扫描了 10 行数据,进行了全表扫描。也是同样的结果。

2、类型不一致导致的索引失效

在说这个之前,一定要说一下设计表字段的时候,千万、一定、必须要保持字段类型的一致性,啥意思?比如 user 表的 id 是 int 自增,到了用户的账户表 user_id 这个字段,一定、必须也是 int 类型,千万不要写成 varchar、char 什么的骚操作。

SELECT * FROM `user` WHERE height= 175;

这个 SQL 诸位一定要看清楚,height 表字段类型是 varchar,但是我查询的时候使用了数字类型,因为这个中间存在一个隐式的类型转换,所以就会导致索引失效,进行全表扫描。

现在明白我为啥说设计字段的时候一定要保持类型的一致性了不,如果你不保证一致性,一个 int 一个 varchar,在进行多表联合查询(eg: 1 = 1)必然走不了索引。

遇到这样的表,里面有几千万数据,改又不能改,那种痛可能你们暂时还体会。

少年们,切记,切记。

3、函数导致的索引失效

SELECT * FROM `user` WHERE DATE(create_time) =  2020-09-03

如果你的索引字段使用了索引,对不起,他是真的不走索引的。

4、运算符导致的索引失效

SELECT * FROM `user` WHERE age - 1 = 20;

如果你对列进行了(+,-,*,/,!), 那么都将不会走索引。

5、OR 引起的索引失效

SELECT * FROM `user` WHERE `name` =  张三  OR height =  175

OR 导致索引是在特定情况下的,并不是所有的 OR 都是使索引失效,如果 OR 连接的是同一个字段,那么索引不会失效,反之索引失效。

6、模糊搜索导致的索引失效

SELECT * FROM `user` WHERE `name` LIKE  % 冰 

这个我相信大家都明白,模糊搜索如果你前缀也进行模糊搜索,那么不会走索引。

7、NOT IN、NOT EXISTS 导致索引失效

SELECT s.* FROM `user` s WHERE NOT EXISTS (SELECT * FROM `user` u WHERE u.name = s.`name` AND u.`name` =  冰峰)
SELECT * FROM `user` WHERE `name` NOT IN (冰峰 

这两种用法,也将使索引失效。但是 NOT IN 还是走索引的,千万不要误解为 IN 全部是不走索引的。我之前就有误解(丢人了 …)。

8、IS NULL 不走索引,IS NOT NULL 走索引

SELECT * FROM `user` WHERE address IS NULL

不走索引。

SELECT * FROM `user` WHERE address IS NOT NULL;

走索引。

根据这个情况,建议大家这设计字段的时候,如果没有必要的要求必须为 NULL,那么最好给个默认值空字符串,这可以解决很多后续的麻烦(有深刻的体验 体验 = 教训)。

符合索引

1、最左匹配原则

EXPLAIN SELECT * FROM `user` WHERE sex =  男 
EXPLAIN SELECT * FROM `user` WHERE name =  冰峰  AND sex =  男 

测试之前,删除其他的单列索引。

啥叫最左匹配原则,就是对于符合索引来说,它的一个索引的顺序是从左往右依次进行比较的,像第二个查询语句,name 走索引,接下来回去找 age,结果条件中没有 age 那么后面的 sex 也将不走索引。

注意:

SELECT * FROM `user` WHERE sex =  男  AND age = 22 AND `name` =  冰峰 

可能有些搬砖工可能跟我最开始有个误解,我们的索引顺序明明是 name、sex、age,你现在的查询顺序是 sex、age、name,这肯定不走索引啊,你要是自己没测试过,也有这种不成熟的想法,那跟我一样还是太年轻了,它其实跟顺序是没有任何关系的,因为 mysql 的底层会帮我们做一个优化,它会把你的 SQL 优化为它认为一个效率最高的样子进行执行。所以千万不要有这种误解。

2、如果使用了!= 会导致后面的索引全部失效

SELECT * FROM `user` WHERE sex =  男  AND `name` !=  冰峰  AND age = 22;

我们在 name 字段使用了 !=,由于 name 字段是最左边的一个字段,根据最左匹配原则,如果 name 不走索引,后面的字段也将不走索引。

MySQL 索引失效有哪些原因

关于符合索引导致索引失效的情况能说的目前就这两种,其实我觉得对于符合索引来说,重要的是如何建立高效的索引,千万不能说我用到那个字段我就去建立一个单独的索引,不是就可以全局用了嘛。这样是可以,但是这样并没有符合索引高效,所以为了成为高级的搬砖工,我们还是要继续学习,如何创建高效的索引。

看完上述内容,你们掌握 MySQL 索引失效有哪些原因的方法了吗?如果还想学到更多技能或想了解更多相关内容,欢迎关注丸趣 TV 行业资讯频道,感谢各位的阅读!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-08-03发表,共计2279字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)