Mysql索引优化+小案例

￡神魔★判官ぃ 2023-09-26 09:14 415阅读 0赞

![在这里插入图片描述][166e5ace2da949b793b1e5f6fb773e29.png]  
表结构：  
![在这里插入图片描述][99812dd2d91341f781ce72d366fb2255.png]  
索引结构：  
![在这里插入图片描述][a0b350840e5a4de19d4b80a5bfbef96b.png]  
数据：

![在这里插入图片描述][e357df38d5714f5098b984172ee9da7d.png]  
（1）like查询是以%开头的，会走全表扫描  
![在这里插入图片描述][2c8e7640da8845ee81e4f446d8fc7bdd.png]  
（2）查询条件中带有or，即使带有索引的那些列也失效了。  
![在这里插入图片描述][66ff1eacb09641cea0797c8c0a7fbc9c.png]（3）如果列类型是字符串，那在查询条件中需要将数据用引号引用起来，否则不走索引。其实最常用的就是如果这一列存储的是数字，那么我们一般就忘记加引号了，这就会导致不走索引查询。  
![在这里插入图片描述][6bb59d14a9d042568fad8c5c9007f919.png]  
![在这里插入图片描述][9333ae3695564454b0b5ab2bc3df3eb3.png]  
（4）在索引列上参与计算会导致索引失效！计算包括(+，-，\*，/，! 等)  
![在这里插入图片描述][3cbddb96bcb94e12acf2a650a96dc5e8.png]

（5）违背最左匹配原则会导致索引失效:大家可以参考一下这篇文章写的特别详细：  
https://blog.csdn.net/dout123/article/details/110578068?utm\_medium=distribute.pc\_relevant.none-task-blog-2defaultbaidujs\_baidulandingword~default-0-110578068-blog-115899479.t5\_download\_0\_7w&spm=1001.2101.3001.4242.1&utm\_relevant\_index=3

（6）如果mysql估计全表扫描要比使用索引要快，会不适用索引！

（7）对索引列使用函数，会导致索引失效  
![在这里插入图片描述][0e4bd90c5b864866a08c5bda12a64d7e.png]  
（8）说到这里前面还有一个问题正好补充：就是对索引隐式类型转换  
![在这里插入图片描述][67b3ab9bfae847778d9405c7dd2e8e01.png]  
![在这里插入图片描述][b8386056944949b18c10685b21b28dd4.png]  
![在这里插入图片描述][bc45527c3cdc4e20947dca9e6ff7ebd5.png]  
![在这里插入图片描述][aef22bb62f224e24b7c617cdf003310c.png]  
（9）应尽量避免在 where 子句中使用 != 操作符，否则引擎将放弃使用索引而进行全表扫描。  
![在这里插入图片描述][13a61fbd4ade4c14b10b5b3d90508df1.png]  
（10）in 和 not in 也要慎用，否则会导致全表扫描(能用 between 就不要用 in)  
![在这里插入图片描述][65c1483db6d44f6a9b0ed137b52e4695.png]  
![在这里插入图片描述][2ca17580f53a4a20840ce034dace1ccc.png]

(11)很多时候用 exists 代替 in 是一个好的选择：exists用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值true或false。

select num from a where num in(select num from b)

用下面的语句替换：select num from a where exists (select 1 from b where num=a.num)  
![在这里插入图片描述][2e173b4377f74ba1ac114113ce41c7f3.png]

（12）任何地方都不要使用 select from t ，用具体的字段列表代替“”，不要返回用不到的任何字段。

（13）10.用>=替代>

高效： SELECT \* FROM EMP WHERE DEPTNO >=4

低效： SELECT \* FROM EMP WHERE DEPTNO >3

两者的区别在于， 前者DBMS将直接跳到第一个DEPT等于4的记录，而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。

（14）用Where子句替换having子句  
![在这里插入图片描述][3494979418dc46d3bfc1e08745f1624c.png]

总结：  
（1）利用like进行模糊查询，并且用%开头  
（2）采用or查询，且查询条件中包含非索引列  
（3）对索引列进行计算操作  
（4）对索引列进行函数查询  
（5）对于联合查询，违背最左匹配原则  
（6）索引隐式类型转换  
（7）避免使用 != 操作符进行查询  
（8）in 和 not in 也要慎用，否则会导致全表扫描(能用 between 就不要用 in)  
（9）根据具体的情况合理选择exists和in  
（10）用>=代替>  
（11）任何地方都不要使用 select from t  
（12）用Where子句替换having子句，主要节省了分组时间，因为having语句是对分组后的数据记性过滤！！

[166e5ace2da949b793b1e5f6fb773e29.png]: https://img-blog.csdnimg.cn/166e5ace2da949b793b1e5f6fb773e29.png
[99812dd2d91341f781ce72d366fb2255.png]: https://img-blog.csdnimg.cn/99812dd2d91341f781ce72d366fb2255.png
[a0b350840e5a4de19d4b80a5bfbef96b.png]: https://img-blog.csdnimg.cn/a0b350840e5a4de19d4b80a5bfbef96b.png
[e357df38d5714f5098b984172ee9da7d.png]: https://img-blog.csdnimg.cn/e357df38d5714f5098b984172ee9da7d.png
[2c8e7640da8845ee81e4f446d8fc7bdd.png]: https://img-blog.csdnimg.cn/2c8e7640da8845ee81e4f446d8fc7bdd.png
[66ff1eacb09641cea0797c8c0a7fbc9c.png]: https://img-blog.csdnimg.cn/66ff1eacb09641cea0797c8c0a7fbc9c.png
[6bb59d14a9d042568fad8c5c9007f919.png]: https://img-blog.csdnimg.cn/6bb59d14a9d042568fad8c5c9007f919.png
[9333ae3695564454b0b5ab2bc3df3eb3.png]: https://img-blog.csdnimg.cn/9333ae3695564454b0b5ab2bc3df3eb3.png
[3cbddb96bcb94e12acf2a650a96dc5e8.png]: https://img-blog.csdnimg.cn/3cbddb96bcb94e12acf2a650a96dc5e8.png
[0e4bd90c5b864866a08c5bda12a64d7e.png]: https://img-blog.csdnimg.cn/0e4bd90c5b864866a08c5bda12a64d7e.png
[67b3ab9bfae847778d9405c7dd2e8e01.png]: https://img-blog.csdnimg.cn/67b3ab9bfae847778d9405c7dd2e8e01.png
[b8386056944949b18c10685b21b28dd4.png]: https://img-blog.csdnimg.cn/b8386056944949b18c10685b21b28dd4.png
[bc45527c3cdc4e20947dca9e6ff7ebd5.png]: https://img-blog.csdnimg.cn/bc45527c3cdc4e20947dca9e6ff7ebd5.png
[aef22bb62f224e24b7c617cdf003310c.png]: https://img-blog.csdnimg.cn/aef22bb62f224e24b7c617cdf003310c.png
[13a61fbd4ade4c14b10b5b3d90508df1.png]: https://img-blog.csdnimg.cn/13a61fbd4ade4c14b10b5b3d90508df1.png
[65c1483db6d44f6a9b0ed137b52e4695.png]: https://img-blog.csdnimg.cn/65c1483db6d44f6a9b0ed137b52e4695.png
[2ca17580f53a4a20840ce034dace1ccc.png]: https://img-blog.csdnimg.cn/2ca17580f53a4a20840ce034dace1ccc.png
[2e173b4377f74ba1ac114113ce41c7f3.png]: https://img-blog.csdnimg.cn/2e173b4377f74ba1ac114113ce41c7f3.png
[3494979418dc46d3bfc1e08745f1624c.png]: https://img-blog.csdnimg.cn/3494979418dc46d3bfc1e08745f1624c.png