仓廪实则知礼节,衣食足则知荣辱。这篇文章主要讲述#yyds干货盘点#MySQL索引优化系列:索引失效相关的知识,希望能为你提供帮助。
索引失效是什么
索引失效就是索引失去效果,索引失效的底层原理呢跟B+树有关,这个后面单独讲下,这里就不展开讲解,有兴趣的朋友也可以自行查询资料。我们可能知道,索引不是在任何时候都会生效的,在很多时候索引可能不会产生效果,那么到底什么情况会导致索引失效呢?接下来来详细了解以下
索引失效分类1、如果条件中有or这个比较很多人可能都知道,sql语句中有or就会让索引失效,即使其中有部分条件带索引也不会使用(这也是为什么尽量少用or的原因)但是注意,当or左右查询字段均为索引时,会生效!
2、like查询以%开头但是注意当like前缀没有%,后缀有%时,索引有效
3、组合索引,不是使用第一列索引(最左原则)注意口诀:带头大哥不准死、中间兄弟不可断!
4、数据类型出现隐式转化如varchar不加单引号的话可能会自动转换为int型,使索引无效,产生全表扫描。
5、where 子句里对索引列上有数学运算如果在查询列使用数学运算,则DBMS优化器先要处理数学运算也会影响查询效能。
例如:ELECT * FROM TABLE WHERE SUM*2<
50
6、在索引列上使用 IS NULL 或 IS NOT NULL操作使用不等于(!=或<
>
)、 is null, is not null 、OR时无法使用索引而使用全表扫描
EXPLAIN SELECT * FROM TABLE WHERE `name` = xiaoming OR `name` = xiaohua
7、where 子句里对有索引列使用函数注意,where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。
8、使用全表扫描要比使用索引快时可以分为两种情况:
1 只使用索引就能够返回查询结果的查询,比如聚合查询中的函数count,max,min。2 查询结果的记录数量小于表中记录一定比例的时候。对于大多数数据库来说,这个比例是10%(oracle,postgresql等),即先对结果数量估算,如果小于这个比例用索引,大于的话即直接表扫描。
总结从上述索引失效的条件可以总结出,以下用法会导致索引失效: - 计算,如:+、-、*、/、!=、<
>
、is null、is not null、or
- 函数,如:sum()、round()等等
- 手动/自动类型转换,如:id = "1010",本来是数字,给写成字符串了
致谢 1. [where
子句里对有索引列使用函数](https://zhidao.baidu.com/question/525877875.html)
2.[mysql 在索引列上使用不等于、is null、is not null、OR
查询无法使用索引](https://segmentfault.com/a/1190000039137011?utm_source=coffeephp.com)
3. ??基于索引的SQL语句优化??
4. [mysql关于where子句对列计算时
索引无效的问题](https://www.lampchina.net/ask/MTQyNTczOQ.html)
5. ??避免在WHERE条件中,在索引列上进行计算或使用函数,因为这将导致索引不被使用??
6. mysql 索引 最左前缀原则
7. 免在WHERE条件中,在索引列上进行计算或使用函数,因为这将导致索引不被使用...
8. mysql索引最左匹配原则的理解
9. ??什么时候全表扫描比使用索引扫描能更快???
【#yyds干货盘点#MySQL索引优化系列(索引失效)】以上就是今天的分享,如有错误,请大佬们多多包涵,多多指正!谢谢~
推荐阅读
- #yyds干货盘点#linux命令--rm,rmdir,mv
- #yyds干货盘点#SpringSecurity默认页面生成
- KubeCon 2021|使用 eBPF 代替 iptables 优化服务网格数据面性能
- mybatis缓存机制详解#yyds干货盘点#
- 一文搞懂TopK问题
- #yyds干货盘点#Vue-transition组件的Css动画+过渡,笔记总结
- Intellij IDEA 高效使用教程 #yyds干货盘点#
- Python 中的图形绘制
- Hive实战UDF 外部依赖文件找不到的问题#yyds干货盘点#