MySQL索引优化之不适合构建索引及索引失效的几种情况详解

MySQL索引优化是提高MySQL查询效率的重要手段之一。然而，在实际应用中，我们会遇到不适合构建索引、索引失效等问题，从而造成查询效率的下降。本文将针对这些问题进行详细讲解。

一、不适合构建索引的情况

当表中的数据行数过少时，构建索引的意义不大，反而会增加查询开销。因此，在数据量较少的情况下，我们不建议构建索引。一般认为，数据量超过1000行时，才有必要构建索引。

如果索引列中的重复率较高，那么建立索引就不太划算了。比如，如果一个表中有一个名为“gender”的列，只包含“男”和“女”两个值，那么对其建立索引就无意义。因为在这种情况下，大部分查询只能返回“男”或“女”，而这两个值几乎已经占了整个表的一半。因此，建立索引对查询的优化作用大打折扣。

当查询条件中使用了函数、表达式等，会导致MySQL无法使用索引加速查询。比如，下面的查询语句：

SELECT * FROM student WHERE YEAR(birthday) = 1990;

这条语句中使用了YEAR函数，会导致MySQL无法使用索引，因为无法对函数的结果建立索引。因此，我们需要对查询条件进行调整，改写成可以使用索引的形式，比如：

SELECT * FROM student WHERE birthday BETWEEN '1990-01-01' AND '1990-12-31';

如果查询条件中使用了!=或<>操作符，MySQL就无法使用索引优化查询。因为在这种情况下，MySQL需要扫描整张表才能确定哪些行符合查询条件。比如：

SELECT * FROM student WHERE age != 18;

这条语句中，如果age列建立了索引，MySQL将不会使用该索引优化查询。

如果查询条件中使用了IS NULL或IS NOT NULL操作符，MySQL同样无法使用索引优化查询。因为在这种情况下，MySQL需要扫描整张表才能确定哪些行符合查询条件。比如：

SELECT * FROM student WHERE name IS NULL;

这条语句中，如果name列建立了索引，MySQL将不会使用该索引优化查询。

如果查询条件中使用了LIKE操作符，并且以通配符（%或_）开头，MySQL同样无法使用索引优化查询。因为在这种情况下，MySQL需要扫描整张表才能确定哪些行符合查询条件。比如：

SELECT * FROM student WHERE name LIKE '%jack';

这条语句中，如果name列建立了索引，MySQL将不会使用该索引优化查询。

示例1：

假设我们有一张用户反馈表feedback，其中有一个status列记录反馈状态，只有两个值：0表示未处理，1表示已处理。现在要查询所有未处理的反馈：

SELECT * FROM feedback WHERE status = 0;

由于status列的值仅有两种，如果建立索引，可以极大地提高查询效率。

示例2：

假设我们有一张订单表order，其中有一个create_time列记录订单创建时间。现在要查询2019年8月所有的订单：

SELECT * FROM order WHERE create_time BETWEEN '2019-08-01 00:00:00' AND '2019-08-31 23:59:59';

由于使用了BETWEEN操作符，可以将该查询转化为范围查询，可以使用create_time列上的索引优化查询。