MySQL细数发生索引失效的情况

前言

在MySQL中，为了加速查询操作，我们通常会通过创建索引来提高查询效率。但是，如果我们不小心创建索引或者索引过期、被删除等情况时，会导致索引失效，查询效率降低，甚至直接影响业务运行。如何防止索引失效？需要从什么方面入手呢？本文将详细讲解MySQL中的索引失效原因和解决方案。

为什么会发生索引失效？

1. 不到万不得已就不要使用LIKE '%XXX%'

使用LIKE '%XXX%'查询时，MySQL无法使用索引，因为这种通配符查询会导致MySQL 无法准确预测需要使用哪种索引。比如下面这个例子：

SELECT * FROM user WHERE name LIKE '%John%';

这条语句虽然会返回符合条件的所有记录，但是由于没有使用索引，所以在数据量较大的情况下，查询速度会非常慢。解决的办法是使用LIKE 'XXX%'模式，这样MySQL可以使用索引进行查询。

2. 索引列类型与查询条件类型不匹配

如果索引列的数据类型与查询条件的数据类型不一致，MySQL无法使用索引进行查询，会导致索引失效。比如下面这个例子：

SELECT * FROM user WHERE id = '1';

如果id是一个整型的自增主键，那么上面的语句会导致索引失效。解决的办法是将查询条件的数据类型改为与索引列相同的数据类型，而非强转或者转为字符类型。

具体案例

1. 非最左前缀索引失效

MySQL的索引是基于B+树实现的，此时只有按照索引定义的层次结构进行查询时，MySQL才会利用索引，如果不是最左匹配，MySQL将无法利用索引。比如下面这个例子：

CREATE TABLE `user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `age` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_age` (`name`,`age`)
);

在上述例子中，我们为user表创建了一个联合索引，索引包含两个列，name和age，如果我们执行下面的语句，MySQL将无法使用该索引：

SELECT * FROM user WHERE age = 18;

这是因为age列并不是最左的索引列。正确的做法是将该语句改为如下格式：

SELECT * FROM user WHERE name = 'John' AND age = 18;

这样，MySQL才会使用联合索引。

2. 使用不等于(!=)、<>或者NOT IN

使用不等于(!=)、<>或者NOT IN时，MySQL无法使用索引，如下所示：

SELECT * FROM user WHERE age != 18;

如果我们使用上述语句，那么MySQL将无法利用索引进行查询，导致索引失效。正确的做法是将该语句改为如下格式：

SELECT * FROM user WHERE age < 18 OR age > 18;

这样，MySQL可以使用索引提高查询效率。

总结

在MySQL中，当索引失效时，查询效率会降低甚至直接影响业务运行。其中主要原因是使用了不适当的查询语句、索引过期、删除等问题。在日常维护中，我们需要注意避免上述情况的发生，尽可能地使用适当的查询语句和索引，保证数据的高效查询。