一、索引失效的七种情况

在讨论 MySQL 索引失效的几种情况之前，首先需要了解索引失效的七种情况。这七种情况包括：

全值匹配未使用索引
```
SELECT * FROM `users` WHERE `name` = 'John'
```
上面这个例子中，如果用户表的 name 字段没有建立索引，那么查询将会全表扫描，导致索引失效。
最左前缀原则被破坏
```
SELECT * FROM `users` WHERE `name` LIKE '%John%'
```
上面这个例子中，如果用户表的 name 字段建立了索引，但是 LIKE 操作中使用了通配符，那么只有最左前缀能够匹配，索引会失效。
范围查询左侧未使用索引
```
SELECT * FROM `users` WHERE `age` > 20
```
上面这个例子中，如果用户表的 age 字段建立了索引，但是使用了大于号（或小于号），那么左侧的索引将不会被使用，导致索引失效。
使用函数或表达式索引失效
```
SELECT * FROM `users` WHERE YEAR(create_time) = 2021;
```
上面这个例子中，如果用户表的 create_time 字段建立了索引，但是在查询中使用了函数或表达式（如 YEAR 函数），那么索引将不会被使用，导致索引失效。
索引列上有计算
```
SELECT * FROM `users` WHERE `age` * 2 = 40
```
上面这个例子中，如果用户表的 age 字段建立了索引，但是在查询中使用了计算（如乘以 2），那么索引将不会被使用，导致索引失效。
不同类型之间的比较
```
SELECT * FROM `users` WHERE `age` = '20'
```
上面这个例子中，如果用户表的 age 字段是 integer 类型，但是在查询中使用了字符串类型（如 '20'），那么索引将不会被使用，导致索引失效。
索引需要被强制类型转换
```
SELECT * FROM `users` WHERE CAST(`age` AS CHAR) = '20'
```
上面这个例子中，如果用户表的 age 字段是 integer 类型，但是在查询中使用了 char 类型，那么索引将不会被使用，导致索引失效。

二、MySQL 索引失效的情况

在理解索引失效的七种情况的基础上，我们可以来看一些 MySQL 索引失效的情况。

1. 全文检索

MySQL 提供了全文检索（FULLTEXT）功能，但是全文检索不支持 LIKE、通配符、正则等操作，因此如果使用了这些操作，就会导致索引失效。

SELECT * FROM `articles` WHERE MATCH(`title`) AGAINST('hello mysql' IN NATURAL LANGUAGE MODE);

2. 联合索引未使用最左前缀

在使用联合索引时，需要注意最左前缀原则。如果不是按照最左前缀的顺序使用索引，那么将会导致索引失效。

SELECT * FROM `users` WHERE `name` = 'John' AND `age` = 20;

上面这个例子中，如果用户表使用了联合索引（name, age），但是查询时只使用了 name 字段，那么 age 字段的索引将不会被使用，导致索引失效。

3. 多表关联查询

在进行多表关联查询时，如果其中一张表没有建立索引，那么将会导致索引失效。

SELECT `users`.`name`, `articles`.`title` FROM `users` JOIN `articles` ON `users`.`id` = `articles`.`user_id` WHERE `users`.`name` = 'John';

上面这个例子中，如果用户表（users）的 name 字段有索引，但是文章表（articles）的 user_id 字段没有索引，那么查询将会进行全表扫描，导致索引失效。

三、MySQL 中索引失效的情况

除了上面提到的情况外，还有一些特殊情况也会导致 MySQL 中索引失效。

1. 字段类型

在 MySQL 中，字段类型对于索引的使用非常重要。如果使用了不合适的字段类型，那么会导致索引失效。例如，将字符串类型的字段作为联合索引的第一项，将会比整数类型的字段慢很多。

2. 数据分布的不均匀

如果数据分布不均匀，那么索引失效的可能性将会增加。例如，当一个表中有大量的相同数据时，如所有用户的性别都是男性，那么查询性别为女性的记录将会导致索引失效，因为大部分数据都是男性。

3. 表中数据量很少

如果表中的数据量非常少，那么索引失效的可能性也会增加。这是因为当数据量很少时，全表扫描的代价非常小，因此 MySQL 可能会选择进行全表扫描而不是使用索引。

四、MySQL 的索引失效选取

为了避免索引失效，需要合理地选取索引。以下是一些选取索引的方法。

1. 联合索引

在使用联合索引时，需要按照最左前缀的顺序使用索引。例如，对于联合索引 (name, age)，如果只查询 name 字段，那么 age 字段的索引将不会被使用，因为它不是最左前缀。

2. 优先选择前缀索引

如果只需要匹配字符串的前缀，那么建立前缀索引可能会更加高效。

ALTER TABLE `users` ADD INDEX `name` (`name`(10));

上面这个例子中，为 name 字段建立长度为 10 的前缀索引。

3. 扩展索引的使用范围

如果一个索引可以被多个查询所使用，那么可以考虑对这个索引进行扩展，以便可以更加高效地使用。例如，对于索引 (name, age)，如果查询中只使用了 name 字段，那么可以将 age 字段加入到索引中，以便在查询时可以更加高效地使用索引。

ALTER TABLE `users` ADD INDEX `name_age` (`name`, `age`);

4. 避免使用函数或表达式

在查询时，尽量避免使用函数或表达式。如果必须使用，可以考虑将函数或表达式的结果存储到另一个字段中，以便可以使用索引。

5. 避免使用大范围的范围查询

在进行范围查询时，尽量避免大范围的范围查询。如果必须使用，可以对被查询的字段建立单独的索引。

6. 注意选择合适的字段类型

在创建表时，需要注意选择合适的字段类型。如果选择不合适的字段类型，那么可能会导致索引失效。例如，在使用字符串类型时，需要注意字符集、长度等因素。

7. 数据分布均匀

为了避免索引失效，需要保证数据的分布均匀。如果数据分布不均匀，可以考虑将数据拆分到多个表中，或者使用分区表来避免数据倾斜。

8. 避免多表关联查询

在进行多表关联查询时，需要注意多表关联的复杂度。如果关联的表比较多，或者关联的表中有表没有建立索引，那么将会导致索引失效。因此，需要尽量避免多表关联查询，或者将多表关联查询拆分成多次的单表查询。

总结

在 MySQL 中，索引是优化查询性能的重要手段。然而，如果使用不当，也会导致索引失效，进而降低查询性能。因此，在创建索引时需要注意选取合适的字段、遵守最左前缀原则、避免大范围的范围查询、避免使用函数或表达式、保证数据分布均匀等因素。

mysql索引失效的几种情况