MySQL中索引失效的常见场景与规避方法

下面是详细讲解 MySQL 中索引失效的常见场景与规避方法的完整攻略：

索引失效的原因

MySQL 中索引失效的原因有很多，这里我们列举其中一些常见的情况：

预测索引无效

预测索引的含义是：使用非索引列做条件，MySQL 仍然会对该列做全表扫描，而不是使用索引。例如下面这个表：

CREATE TABLE student (
  name VARCHAR(20),
  age INT,
  PRIMARY KEY (name)
);

如果我们要查询年龄为 18 的学生，但是只使用了 age 字段作为条件：

SELECT * FROM student WHERE age=18;

MySQL 会发现没有合适的索引可以使用，于是会做全表扫描。要避免这种情况，我们需要对 age 字段添加索引，这样查询语句就会变成：

SELECT * FROM student WHERE age=18 AND name IS NOT NULL;

这样 MySQL 就会使用 age 和 name 两个字段的组合索引来处理查询请求。

不使用索引列

MySQL 在执行查询语句时，只有在查询条件中出现的列才会被使用到索引。例如下面这个表：

CREATE TABLE student (
  name VARCHAR(20),
  age INT,
  PRIMARY KEY (name)
);

如果我们要查询名字以 T 开头的学生，但是只使用了年龄作为条件：

SELECT * FROM student WHERE age=18;

这时 MySQL 会发现查询条件中没有 name 这个索引列，于是不使用索引，做全表扫描。要避免这种情况，需要对 name 字段添加索引，或者将 name 字段作为查询条件。

索引列使用函数或操作符

如果在查询语句中使用了函数或操作符对索引列进行处理，MySQL 会放弃使用索引。例如下面这个表：

CREATE TABLE student (
  name VARCHAR(20),
  age INT,
  PRIMARY KEY (name),
  INDEX age_index (age)
);

如果我们要查询年龄为 18 的学生，但是使用了函数对 age 字段进行处理：

SELECT * FROM student WHERE age/2=9;

这时 MySQL 会放弃使用 age_index 索引，而是做全表扫描。要解决这个问题，我们需要减少使用函数和操作符，或者对 age 字段进行计算并将运算结果写入另外一个列中，再使用这个新列来进行查询。

索引失效的规避方法

针对上述的情况，我提出一些规避方法，如下所示：

设计合适的索引

在设计数据表时，需要针对具体的查询语句来确定需要哪些索引。一般来说，需要为经常查询和排序的列添加索引。同时，需要避免创建过多的冗余索引，这会导致数据库性能下降。

避免使用函数和操作符

在查询语句中尽量避免使用函数和操作符。如果无法避免，可以考虑使用函数和操作符的逆运算来代替原有的函数和操作符。

善用 Explain

Explain 是 MySQL 中一个用于分析查询语句的工具，可以通过 Explain 分析查询语句的执行计划、索引使用情况等。通过分析 Explain 的输出结果，可以找到查询语句的性能瓶颈和优化方向。

适当调整 MySQL 参数

MySQL 中有很多参数可以配置，例如缓存大小、连接数、查询缓存等。适当调整这些参数可以提高 MySQL 的性能和稳定性。需要注意的是，不同的参数调整适用于不同的场景，需要谨慎评估。

示例说明

为了更好地理解索引失效的原因和规避方法，下面举两个实例进行说明。

实例一

假设有一个表 user，包含了用户 ID、用户名、性别等字段。我们要查询性别为女性的用户名以 W 开头的用户的 ID 和用户名两个字段。为了提高查询速度，我们向表中添加了一个联合索引：

ALTER TABLE user ADD INDEX `idx_gender_username` (`gender`, `username`(8));

然后我们执行了如下这个查询语句：

SELECT id, username FROM user WHERE gender='女' AND LEFT(username, 1)='W';

通过 Explain 分析发现，该查询使用了索引，但是对索引列 username 使用了函数 LEFT，导致索引失效。我们可以将查询语句修改为：

SELECT id, username FROM user WHERE gender='女' AND username LIKE 'W%';

这样就可以避免使用函数，索引得到了正确使用。

实例二

假设有一个表 article，包含了文章 ID、标题、作者、创建时间等字段。我们要查询创建日期为 2019-01-01 的文章标题以 A 开头的文章的 ID 和标题两个字段。为了提高查询速度，我们向表中添加了两个索引：

ALTER TABLE article ADD INDEX `idx_create_time` (`create_time`);
ALTER TABLE article ADD INDEX `idx_title` (`title`(8));