MySQL高级之索引失效与优化详解

叁歲伎倆 2022-10-17 00:38 237阅读 0赞

# 案例所用的表结构、索引、与数据如下： #

![这里写图片描述][SouthEast]  
![这里写图片描述][SouthEast 1]

# 索引失效与优化 #

![这里写图片描述][SouthEast 2]

## **1、全值匹配我最爱** ##

![这里写图片描述][SouthEast 3]

## **2、最佳左前缀法则（带头索引不能死，中间索引不能断）** ##

如果索引了多个列，要遵守最佳左前缀法则。指的是查询从索引的最左前列开始 并且 不跳过索引中的列。  
正确的示例参考上图。

**错误的示例：**  
带头索引死：  
![这里写图片描述][SouthEast 4]  
中间索引断（带头索引生效，其他索引失效）：  
![这里写图片描述][SouthEast 5]

## **3、不要在索引上做任何操作（计算、函数、自动/手动类型转换），不然会导致索引失效而转向全表扫描** ##

![这里写图片描述][SouthEast 6]

## **4、mysql存储引擎不能继续使用索引中范围条件（bettween、<、>、in等）右边的列** ##

![这里写图片描述][SouthEast 7]

## **5、尽量使用覆盖索引（只查询索引的列（索引列和查询列一致）），减少select \*** ##

![这里写图片描述][SouthEast 8]

## **6、索引字段上使用（！= 或者 < >）判断时，会导致索引失效而转向全表扫描** ##

![这里写图片描述][SouthEast 9]

## **7、索引字段上使用 is null / is not null 判断时，会导致索引失效而转向全表扫描** ##

![这里写图片描述][SouthEast 10]

## **8、索引字段使用like以通配符开头（‘%字符串’）时，会导致索引失效而转向全表扫描** ##

![这里写图片描述][SouthEast 11]  
由结果可知，like以通配符结束相当于范围查找，索引不会失效。与范围条件（bettween、<、>、in等）不同的是：不会导致右边的索引失效。

**问题：解决like ‘%字符串%’时，索引失效问题的方法？**  
使用覆盖索引可以解决。  
![这里写图片描述][SouthEast 12]  
![这里写图片描述][SouthEast 13]

## **9、索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全表扫描** ##

![这里写图片描述][SouthEast 14]

## **10、索引字段使用 or 时，会导致索引失效而转向全表扫描** ##

![这里写图片描述][SouthEast 15]

# 小总结 #

![这里写图片描述][SouthEast 16]

![这里写图片描述][SouthEast 17]

[SouthEast]: /images/20221014/5db0ef7fab464323934e653e2b65c07b.png
[SouthEast 1]: /images/20221014/91abfecbbd524fafaf959836190051d3.png
[SouthEast 2]: /images/20221014/efe56e83f0d1460b93ff1521bc0fc08b.png
[SouthEast 3]: /images/20221014/1e4393d0ba6d4f3f9656581b6bb5a41a.png
[SouthEast 4]: /images/20221014/d4bad16118fb47e08fdac3b6219aa5c6.png
[SouthEast 5]: /images/20221014/08c93a3fc665474f8d0e6d4094b6f18d.png
[SouthEast 6]: /images/20221014/a2d9f0942b334712b42f30afb680a61d.png
[SouthEast 7]: /images/20221014/5e1b8c7ea03d41a5b3ca5a1bd86f7582.png
[SouthEast 8]: /images/20221014/3f90fc94ba4a4028b3794c52941e520f.png
[SouthEast 9]: /images/20221014/516bf350a7e3438ca4eec294900c1667.png
[SouthEast 10]: /images/20221014/fd90f4ffe7b9463a919506dc6e703e69.png
[SouthEast 11]: /images/20221014/f07c96c8e37e4ab9ada705330deeb89c.png
[SouthEast 12]: /images/20221014/f58c2309dcf54a519efbbd0e6f83758d.png
[SouthEast 13]: /images/20221014/e419a1589d0643e99fdd681c48160373.png
[SouthEast 14]: /images/20221014/34343fdc156945cea670452791ce0ba4.png
[SouthEast 15]: /images/20221014/0cb74e7b407d4217b7dab33512027a2d.png
[SouthEast 16]: /images/20221014/11b36c3b97c14ca784e6bf2bbc6ce183.png
[SouthEast 17]: /images/20221014/15a95c7f4e13479c9d036cae9d8fabf8.png