MySQL索引失效及使用索引的优缺点

悠悠 2022-01-12 16:17 318阅读 0赞

> 本文所有实验基于MySQL5.7.21，实验将会用到Explain工具，不了解的同学可参考此文章：MySQL性能优化神器Explain详解

##### 联合索引失效 #####

先创建一个包含三个字段的联合索引，索引顺序如下：  
![1][]  
由以下三张图的key\_len字段我们可以得出三个索引的长度分别为：title长303，author长122，price长5.  
![1][1 1]![1][1 2]![1][1 3]

联合索引使用时遵循最左匹配原则，如果不是从最左列开始时，整个索引失效，如果最左匹配则依次往右使用索引，直到碰到不匹配的地方之后生效之前匹配到的索引  
![1][1 4]![1][1 5]

##### 范围查询会引起索引失效 #####

为以上联合索引更换字段顺序为title，price，author。如果查询时包含范围查询则范围字段后面的字段失效  
![1][1 6]

##### like查询失效 #####

使用模糊查询时，%只有在最右方的时候才能生效

为title设置一个单独的索引

![1][1 7]![1][1 8]![1][1 9]

##### 查询中含有不等于或者or则索引不生效 #####

![1][1 10]![1][1 11]![1][1 12]

##### 在索引列上做计算或函数导致失效 #####

删除刚才创建的联合索引，为price字段创建一个单独的索引

![1][1 13]![1][1 14]

##### 字符串类型不加引号同样会失效 #####

![1][1 15]

##### is null和 is not null #####

为title字段创建一个单独的索引  
![1][1 16]![1][1 17]

##### 注意事项 #####

在进行索引使用测试时，可能会因为测试数据太少从而MySQL会认为查询语句走全表扫描比走索引更有效，所以会自动去除索引，为避免测试结果误导可使用如下方式强制MySQL使用索引

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 </pre></td> 
 <td><pre>explain select * from book FORCE index(索引名称) where price=1+1; </pre></td> 
 </tr> 
 </tbody> 
</table>

##### 使用索引的优点 #####

*  唯一索引或主键索引可以保证数据库表的唯一性
 *  可以提高查询效率和性能
 *  加速表连接的速度
 *  加快排序的效率

##### 使用索引的缺点 #####

*  每次更新修改删除都需要维护索引、从而消耗性能
 *  索引文件会占用物理空间

#### 推荐阅读 ####

1.  [SpringCloud学习系列汇总][SpringCloud]
2.  [为什么一线大厂面试必问redis，有啥好问的？][redis]
3.  [多线程面试必备基础知识汇总][Link 1]
4.  [Java集合源码分析汇总-JDK1.8][Java_-JDK1.8]
5.  [Linux常用命令速查-汇总篇][Linux_-]
6.  [JVM系列文章汇总][JVM]

#### 博客所有文章首发于公众号《Java学习录》转载请保留   扫码关注公众号即可领取2000GJava学习资源 ####

![1][1 18]

转载于:https://blog.51cto.com/12980017/2401809

[1]: https://shiyujun.cn/2019/05/29/MySQL%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88%E5%8F%8A%E4%BD%BF%E7%94%A8%E7%B4%A2%E5%BC%95%E7%9A%84%E4%BC%98%E7%BC%BA%E7%82%B9/1.jpg
[1 1]: https://shiyujun.cn/2019/05/29/MySQL%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88%E5%8F%8A%E4%BD%BF%E7%94%A8%E7%B4%A2%E5%BC%95%E7%9A%84%E4%BC%98%E7%BC%BA%E7%82%B9/2.jpg
[1 2]: https://shiyujun.cn/2019/05/29/MySQL%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88%E5%8F%8A%E4%BD%BF%E7%94%A8%E7%B4%A2%E5%BC%95%E7%9A%84%E4%BC%98%E7%BC%BA%E7%82%B9/3.jpg
[1 3]: https://shiyujun.cn/2019/05/29/MySQL%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88%E5%8F%8A%E4%BD%BF%E7%94%A8%E7%B4%A2%E5%BC%95%E7%9A%84%E4%BC%98%E7%BC%BA%E7%82%B9/4.jpg
[1 4]: https://shiyujun.cn/2019/05/29/MySQL%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88%E5%8F%8A%E4%BD%BF%E7%94%A8%E7%B4%A2%E5%BC%95%E7%9A%84%E4%BC%98%E7%BC%BA%E7%82%B9/5.jpg
[1 5]: https://shiyujun.cn/2019/05/29/MySQL%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88%E5%8F%8A%E4%BD%BF%E7%94%A8%E7%B4%A2%E5%BC%95%E7%9A%84%E4%BC%98%E7%BC%BA%E7%82%B9/6.jpg
[1 6]: https://shiyujun.cn/2019/05/29/MySQL%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88%E5%8F%8A%E4%BD%BF%E7%94%A8%E7%B4%A2%E5%BC%95%E7%9A%84%E4%BC%98%E7%BC%BA%E7%82%B9/7.jpg
[1 7]: https://shiyujun.cn/2019/05/29/MySQL%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88%E5%8F%8A%E4%BD%BF%E7%94%A8%E7%B4%A2%E5%BC%95%E7%9A%84%E4%BC%98%E7%BC%BA%E7%82%B9/8.jpg
[1 8]: https://shiyujun.cn/2019/05/29/MySQL%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88%E5%8F%8A%E4%BD%BF%E7%94%A8%E7%B4%A2%E5%BC%95%E7%9A%84%E4%BC%98%E7%BC%BA%E7%82%B9/9.jpg
[1 9]: https://shiyujun.cn/2019/05/29/MySQL%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88%E5%8F%8A%E4%BD%BF%E7%94%A8%E7%B4%A2%E5%BC%95%E7%9A%84%E4%BC%98%E7%BC%BA%E7%82%B9/10.jpg
[1 10]: https://shiyujun.cn/2019/05/29/MySQL%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88%E5%8F%8A%E4%BD%BF%E7%94%A8%E7%B4%A2%E5%BC%95%E7%9A%84%E4%BC%98%E7%BC%BA%E7%82%B9/11.jpg
[1 11]: https://shiyujun.cn/2019/05/29/MySQL%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88%E5%8F%8A%E4%BD%BF%E7%94%A8%E7%B4%A2%E5%BC%95%E7%9A%84%E4%BC%98%E7%BC%BA%E7%82%B9/12.jpg
[1 12]: https://shiyujun.cn/2019/05/29/MySQL%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88%E5%8F%8A%E4%BD%BF%E7%94%A8%E7%B4%A2%E5%BC%95%E7%9A%84%E4%BC%98%E7%BC%BA%E7%82%B9/13.jpg
[1 13]: https://shiyujun.cn/2019/05/29/MySQL%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88%E5%8F%8A%E4%BD%BF%E7%94%A8%E7%B4%A2%E5%BC%95%E7%9A%84%E4%BC%98%E7%BC%BA%E7%82%B9/14.jpg
[1 14]: https://shiyujun.cn/2019/05/29/MySQL%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88%E5%8F%8A%E4%BD%BF%E7%94%A8%E7%B4%A2%E5%BC%95%E7%9A%84%E4%BC%98%E7%BC%BA%E7%82%B9/15.jpg
[1 15]: https://shiyujun.cn/2019/05/29/MySQL%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88%E5%8F%8A%E4%BD%BF%E7%94%A8%E7%B4%A2%E5%BC%95%E7%9A%84%E4%BC%98%E7%BC%BA%E7%82%B9/16.jpg
[1 16]: https://shiyujun.cn/2019/05/29/MySQL%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88%E5%8F%8A%E4%BD%BF%E7%94%A8%E7%B4%A2%E5%BC%95%E7%9A%84%E4%BC%98%E7%BC%BA%E7%82%B9/17.jpg
[1 17]: https://shiyujun.cn/2019/05/29/MySQL%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88%E5%8F%8A%E4%BD%BF%E7%94%A8%E7%B4%A2%E5%BC%95%E7%9A%84%E4%BC%98%E7%BC%BA%E7%82%B9/18.jpg
[SpringCloud]: https://mp.weixin.qq.com/s/TL4w0u6QA-wFx19IH9Xs6A
[redis]: https://mp.weixin.qq.com/s/NSojCEA-lF8ocR1XeuXbsg
[Link 1]: https://mp.weixin.qq.com/s/c3ngvdAc6lAs4d1sIdg16Q/
[Java_-JDK1.8]: https://mp.weixin.qq.com/s/UAGihOEUPmFnwrgkSJZqpQ
[Linux_-]: https://mp.weixin.qq.com/s/WG4ZWfId-MRslWtcDtx0aA
[JVM]: https://mp.weixin.qq.com/s/uDftoMTwJqk7H9bfQaa0sw
[1 18]: /images/20220112/7ecb90a1d05a4fd0a7bff2a98f09af04.png