mysql索引失效的几种情况

一、索引失效的七种情况

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

1、全值匹配未使用索引

SELECT * FROM `users` WHERE `name` = 'John'

上面这个例子中，如果用户表的name字段没有建立索引，那么查询将会全表扫描，导致索引失效。

2、最左前缀原则被破坏

SELECT * FROM `users` WHERE `name` LIKE '%John%'

上面这个例子中，如果用户表的name字段建立了索引，但是like操作中使用了通配符，那么只有最左前缀能够匹配，索引会失效。

3、范围查询左侧未使用索引

SELECT * FROM `users` WHERE `age` > 20

上面这个例子中，如果用户表的age字段建立了索引，但是使用了大于号（或小于号），那么左侧的索引将不会被使用，导致索引失效。

4、使用函数或表达式索引失效

SELECT * FROM `users` WHERE YEAR(create_time) = 2021;

上面这个例子中，如果用户表的create_time字段建立了索引，但是在查询中使用了函数或表达式（如YEAR函数），那么索引将不会被使用，导致索引失效。

5、索引列上有计算

SELECT * FROM `users` WHERE `age` * 2 = 40

上面这个例子中，如果用户表的age字段建立了索引，但是在查询中使用了计算（如乘以2），那么索引将不会被使用，导致索引失效。

6、不同类型之间的比较

SELECT * FROM `users` WHERE `age` = '20'

上面这个例子中，如果用户表的age字段是integer类型，但是在查询中使用了字符串类型（如’20’），那么索引将不会被使用，导致索引失效。

7、索引需要被强制类型转换

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

原创文章，作者：小蓝，如若转载，请注明出处：https://www.506064.com/n/258235.html