MySQL索引失效场景及解决方案

下面是“MySQL索引失效场景及解决方案”的完整攻略。

什么是MySQL索引

MySQL索引是指在MySQL数据库表中，通过物理文件及相关数据结构的方式快速地访问表中特定的数据方式。

索引失效场景

在使用索引时，有时候我们会发现索引失效了，也就是说MySQL没有使用索引来查询数据，这种情况经常发生在以下几个场景中：

1. 不在索引列上使用函数或操作符

如果在查询中使用了函数或操作符，而这些函数和操作符不是索引列的一部分，那么MySQL就无法使用索引来加速查询，例如：

SELECT * FROM users WHERE YEAR(create_time) = '2021'

在上面的查询中，虽然create_time字段有索引，但是使用了YEAR函数将其转换成了一个值，因此MySQL无法使用索引来加速查询。解决方案是将YEAR函数转换成一个范围：

SELECT * FROM users WHERE create_time >= '2021-01-01 00:00:00' AND create_time < '2022-01-01 00:00:00'

2.索引列上使用函数或操作符

另外一种情况是在索引列上使用了函数或操作符，例如：

SELECT * FROM users WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2021-01-01'

在这个查询中，虽然使用了create_time列，但是在查询时使用了DATE_FORMAT函数，因此MySQL无法使用索引。解决方案是在查询之前将create_time转换成一个更恰当的格式，例如：

SELECT * FROM users WHERE create_time >= '2021-01-01 00:00:00' AND create_time < '2021-01-02 00:00:00'

3. LIKE语句以%开头

如果在LIKE语句中以%开头，则MySQL无法使用索引，例如：

SELECT * FROM users WHERE name LIKE '%john'

这个查询中，尽管name字段有索引，但是由于LIKE语句以%开头，MySQL无法使用索引。解决方案是将LIKE语句改写成一个范围：

SELECT * FROM users WHERE name >= 'john' AND name < 'joao'

示例说明

示例一

表结构：id, name, create_time，其中id为主键，create_time有索引。

CREATE TABLE users (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  create_time DATETIME NOT NULL,
  PRIMARY KEY (id),
  INDEX (create_time)
);

调用了以下查询

SELECT * FROM users WHERE YEAR(create_time) = '2021' AND name = 'john'

这个查询中，虽然在索引列create_time上使用了YEAR函数，但是还在另一个没有索引的列name上查询，因此MySQL无法使用索引。解决方案是：

SELECT * FROM users WHERE name = 'john' AND create_time >= '2021-01-01 00:00:00' AND create_time < '2022-01-01 00:00:00'

示例二

表结构：id, name, create_time，其中id为主键，name和create_time都有索引。

CREATE TABLE users (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  create_time DATETIME NOT NULL,
  PRIMARY KEY (id),
  INDEX (name),
  INDEX (create_time)
);

调用了以下查询

SELECT * FROM users WHERE name LIKE '%john' AND create_time >= '2021-01-01 00:00:00' AND create_time < '2022-01-01 00:00:00'

这个查询中，虽然在索引列name上使用了LIKE语句，但是LIKE语句以%开头，MySQL无法使用索引。解决方案是：

SELECT * FROM users WHERE name >= 'john' AND name < 'joao' AND create_time >= '2021-01-01 00:00:00' AND create_time < '2022-01-01 00:00:00'

通过以上两个示例，可以看出索引失效的常见场景以及相应的解决方案，在实际应用中可以根据具体情况进行调整。