MySQL索引失效-KeepStudy

MySQL索引失效

但很多小伙伴在平时写SQL的时候，一般只追求实现业务功能，只要能够查询出相应的数据即可，压根不会过度考虑这条SQL应用到索引，那么这里就是给出一些经验之谈，讲清楚几点写SQL时的方法论。

其实索引本身是一把双刃剑，用的好能够给我们带来异乎寻常的查询效率，用的不好则反而会带来额外的磁盘占用及写入操作时的维护开销。因此大家一定要切记，既然选择建了索引，那一定要利用它，否则还不如干脆别建，既能节省磁盘空间，又能提升写入效率。

### 执行分析工具 - ExPlain
但想要查看一条SQL是否使用了索引，需要用到一个自带的分析工具ExPlain。先来看看这个工具/命令的作用，当在一条SQL前加上explain命令，执行这条SQL后会列出所有的执行方案：
![](/uploads/20230215/2023021512425411480.png)

![](/uploads/20230215/2023021512434942200.png)

对于上述这么多的字段，其实目前不需要完全弄懂，本文只需要记住里面的type字段即可，all表示走全表扫描，const、ref...表示通过索引查询。

### 查询中带有OR会导致索引失效
即使一条SQL包含了主键索引的字段，或包含了联合索引的第一个字段，若使用了OR，最终导致索引失效。

### 模糊查询中like以%开头导致索引失效
```sql
--索引失效
EXPLAIN SELECT * FROM `zz_users` WHERE user_name LIKE "%熊";

--使用了索引
EXPLAIN SELECT * FROM `zz_users` WHERE user_name LIKE "熊%";
```
>以%结尾，其实可以使用联合索引来检索数据，并不会导致索引失效。

### 字符类型查询时不带引号导致索引失效
```sql
-- 先插入一条user_name = 1111 的数据
INSERT INTO `zz_users` VALUES(4,"1111","男","4321","2022-09-17 23:48:29");

EXPLAIN SELECT * FROM `zz_users` WHERE user_name = 111;
```
上述这条SQL按理来说是没有半点问题的，目前是符合联合索引的最左匹配原则的，但来看看结果：

![](/uploads/20230215/2023021512500539422.png)

从结果中很明显的可以看出，由于user_name是字符串类型的，因此查询时没带引号，竟然直接未使用索引，导致了索引失效。

### 索引字段参与计算导致索引失效
大家要切记，千万不要让索引字段在SQL中参与计算，也包括使用一些聚合函数时也会导致索引失效，其根本原因就在于索引字段参与了计算导致的.

这里的运算也包括+、-、*、/、!.....等一系列涉及字段计算的逻辑。

### 字段被用于函数计算导致索引失效
```sql
EXPLAIN SELECT * FROM `zz_users` WHERE SUBSTRING(user_name,0,1) = "MySQL";
```
上述中，我们使用SUBSTRING函数对user_name字段进行了截取，然后再用于条件查询，会导致未使用索引查询，这也和上一条的原因大致相同，索引字段参与计算导致失效。

### 违背最左前缀原则导致索引失效

### 不同字段值对比导致索引失效
从一张表中查询出一些值，然后根据这些值去其他表中筛选数据，这个业务也是实际项目中较为常见的场景，下面为了简单实现，就简单用姓名和性别模拟一下字段对比的场景：
```sql
EXPLAIN SELECT * FROM `zz_users` WHERE user_name = user_sex;
```

按理来说，因为user_name属于联合索引的第一个字段，所以上述这条SQL中规中矩，理论上会走索引的，但看看结果：
![](/uploads/20230215/2023021512581285317.png)
显然，这个场景也会导致索引无法使用，因此之后也要切记这点。

### 反向范围操作导致索引失效
一般来说，如果SQL属于正向范围查询，例如>、<、between、like、in...等操作时，索引是可以正常生效的，但如果SQL执行的是反向范围操作，例如NOT IN、NOT LIKE、IS NOT NULL、!=、<>...等操作时，就会出现问题。

并非所有的正向范围操作都会走索引，例如IS NULL就不会走，它的反向操作：IS NOT NULL同样不会走。

### 索引失效小结
在MySQL中还有一种特殊情况会导致索引失效，也就是当走索引扫描的行数超过表行数的30%时，MySQL会默认放弃索引查询，转而使用全表扫描的方式检索数据，因此这种情况下走索引的顺序磁盘IO，反而不一定有全表的随机磁盘IO快。

还有一点要牢记：关于索引是否会失效，实际上也跟索引的数据结构、MySQL的版本、存储引擎的不同有关，例如一条SQL语句在B+Tree索引中会导致索引失效，但在哈希索引中却不会（好比IS NULL/IS NOT NULL），这种情况在不同版本、不同引擎中都有可能会体现出来。

顶部

MySQL索引

目录

相关推荐