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

r囧r小猫 2024-04-18 20:30 66阅读 0赞

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

**联合索引失效**

先创建一个包含三个字段的联合索引，索引顺序如下：

![640?wx\_fmt=png][640_wx_fmt_png]

由以下三张图的key\_len字段我们可以得出三个索引的长度分别为：title长303，author长122，price长5.

![640?wx\_fmt=png][640_wx_fmt_png 1]

![640?wx\_fmt=png][640_wx_fmt_png 2]

![640?wx\_fmt=png][640_wx_fmt_png 3]

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

![640?wx\_fmt=png][640_wx_fmt_png 4]

![640?wx\_fmt=png][640_wx_fmt_png 5]

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

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

![640?wx\_fmt=png][640_wx_fmt_png 6]

**like查询失效**

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

为title设置一个单独的索引

![640?wx\_fmt=png][640_wx_fmt_png 7]

![640?wx\_fmt=png][640_wx_fmt_png 8]

![640?wx\_fmt=png][640_wx_fmt_png 9]

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

![640?wx\_fmt=png][640_wx_fmt_png 10]

![640?wx\_fmt=png][640_wx_fmt_png 11]

![640?wx\_fmt=png][640_wx_fmt_png 12]

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

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

![640?wx\_fmt=png][640_wx_fmt_png 13]

![640?wx\_fmt=png][640_wx_fmt_png 14]

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

![640?wx\_fmt=png][640_wx_fmt_png 15]

**is null和 is not null**

为title字段创建一个单独的索引

![640?wx\_fmt=png][640_wx_fmt_png 16]

![640?wx\_fmt=png][640_wx_fmt_png 17]

**注意事项**

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

explain select * from book FORCE index(索引名称) where price=1+1;

**使用索引的优点**

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

**使用索引的缺点**

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

![640?wx\_fmt=gif][640_wx_fmt_gif]

不得不看

1.[SpringCloud系列博客汇总][SpringCloud]

2.[为啥一线大厂面试必问Redis，有啥好问的？][Redis]

3.[Java多线程面试必备基础知识汇总][Java]

4.[Java集合源码分析汇总][Java 1]

5.[Linux常用命令汇总][Linux]

6.[JVM系列文章汇总][JVM]

![640?wx\_fmt=jpeg][640_wx_fmt_jpeg]

万水千山总是情，点个 “在看” 行不行！！！

![640?wx\_fmt=png][640_wx_fmt_png 18]

[MySQL_Explain]: http://mp.weixin.qq.com/s?__biz=MzU5MDgzOTYzMw==&mid=2247484276&idx=1&sn=089b7015e2da01c63dd7969fbbca7385&chksm=fe39694ac94ee05c49fc1ced477ef0d1a0b6f179f43bc78ed97fe17557dc23b946174b5c6a64&scene=21#wechat_redirect
[640_wx_fmt_png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/b295b371ce0447afb7f45366d07e5e08.png
[640_wx_fmt_png 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/0d759d70b486448ab642a818b80730bb.png
[640_wx_fmt_png 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/0ff7bbe308014fa2ba70f113013bb28b.png
[640_wx_fmt_png 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/03912c7f4b974e2dbf2c5be9b38d8ac2.png
[640_wx_fmt_png 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/605a17f118494d2490481388a819b96b.png
[640_wx_fmt_png 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/44d17413464745058c050543f1286da0.png
[640_wx_fmt_png 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/d04f50ea089946229ccdf8624ec913e3.png
[640_wx_fmt_png 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/5090827e035a47f1a9188f67e2a250f9.png
[640_wx_fmt_png 8]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/9d1a5f7bf51b423cb12b71b7811852f1.png
[640_wx_fmt_png 9]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/8e634770539e4b6883047eb09e0d71b0.png
[640_wx_fmt_png 10]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/d69a72d9d3f1429f9c362f03f6171ace.png
[640_wx_fmt_png 11]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/8a0b4670a5214b97bfdf97df9381eda8.png
[640_wx_fmt_png 12]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/4975cacaffd848e981e2e5491ce1f0dd.png
[640_wx_fmt_png 13]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/4f4226d5b8124fcfbd39316e7193e099.png
[640_wx_fmt_png 14]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/cfbf6f6010864a19afbd3424a5635d49.png
[640_wx_fmt_png 15]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/679045edbd9a4fc386d8d5b76e0c9fcb.png
[640_wx_fmt_png 16]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/377d7f4241c34e2e9a1ca91bf90b8414.png
[640_wx_fmt_png 17]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/8dc982b25feb4bb7b6656ed2c8c43a99.png
[640_wx_fmt_gif]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/aaf82fad926b414fb75b0f82f48c2cf7.gif
[SpringCloud]: http://mp.weixin.qq.com/s?__biz=MzU5MDgzOTYzMw==&mid=2247484030&idx=1&sn=7ad29239aca876e29155f5b1439281bb&chksm=fe396840c94ee156adfcd44c63a3b5c1cf93805cf592ad7d45bc5222c349ba6f552b553a5c09&scene=21#wechat_redirect
[Redis]: http://mp.weixin.qq.com/s?__biz=MzU5MDgzOTYzMw==&mid=2247484172&idx=1&sn=cad78126f0367dbd67e0336b0b369b5d&chksm=fe396932c94ee0248ce1527844d9664a01e29a43b11b1910aa415facdc569c82a63cb07e44cf&scene=21#wechat_redirect
[Java]: http://mp.weixin.qq.com/s?__biz=MzU5MDgzOTYzMw==&mid=2247484030&idx=2&sn=ddf120fcbfc45238e64662fd183156de&chksm=fe396840c94ee156a80b744033ac34d67e331545193d2f9d4f2943995aa4412a53fec023d599&scene=21#wechat_redirect
[Java 1]: http://mp.weixin.qq.com/s?__biz=MzU5MDgzOTYzMw==&mid=2247484098&idx=1&sn=ba605ae1b35d6f2a8caef5901f0b86f6&chksm=fe3968fcc94ee1ea2493ce7faaab40316fa3954effa1613165b47955f5c097ef96c400b1485b&scene=21#wechat_redirect
[Linux]: http://mp.weixin.qq.com/s?__biz=MzU5MDgzOTYzMw==&mid=2247484117&idx=1&sn=b4c6eb9b4027390767e92efad018a08e&chksm=fe3968ebc94ee1fdf40b5192f574b0e7cdc176663411c7f9f44c814a71a14139c510b0c35bf2&scene=21#wechat_redirect
[JVM]: http://mp.weixin.qq.com/s?__biz=MzU5MDgzOTYzMw==&mid=2247484238&idx=1&sn=a30cd56a84f93da11bf916591612ef8d&chksm=fe396970c94ee066ef23c65375f88dd6ff727fd1c03c60ba61d1a9072a879cbb54084f779107&scene=21#wechat_redirect
[640_wx_fmt_jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/d817d2537e6148ffaa3bfce9a50f6f14.png
[640_wx_fmt_png 18]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/cd3ea003677a43b7acfa852d8192f295.png