关于MySQL 5.7及8.0版本索引失效情况汇总

索引失效的概念

在MySQL中，查询的速度与所涉及的数据量成正比，当数据量变大时，查询效率会明显下降，影响系统的性能。为了提高查询的效率，需要通过创建索引来实现。索引是一种数据结构，能够快速定位数据所在的位置，类似于书籍的目录，可以快速找到需要的内容。

但是，当数据量不断增加时，索引的维护成本也会随之增加，这时就需要注意索引失效的问题。索引失效指的是在数据查询时，MySQL无法使用索引来加速查询，而只能通过全表扫描来查找数据，导致查询效率显著下降。

导致索引失效的原因

1. 对索引字段进行运算或函数操作

索引中存储的是数据的拷贝，因此对索引字段进行运算或函数操作会导致MySQL无法使用索引，例如：

SELECT * FROM users WHERE YEAR(register_time) = 2021;

由于对register_time字段进行了函数操作，MySQL无法使用该字段的索引，只能通过全表扫描来查找符合条件的数据。

1. 对字段进行类型转换

MySQL会根据数据类型进行索引的创建，如果查询时对该字段进行类型转换，MySQL就无法使用索引，例如：

SELECT * FROM users WHERE CONVERT(id, CHAR) = '123';

由于将id字段转换为CHAR类型，MySQL无法使用该字段的索引，只能通过全表扫描来查找符合条件的数据。

1. LIKE查询时，通配符在开头或两侧

LIKE查询时，如果通配符在开头或两侧，MySQL无法使用索引，例如：

SELECT * FROM users WHERE name LIKE '%张%';

由于通配符在两侧，MySQL无法使用name字段的索引，只能通过全表扫描来查找符合条件的数据。

1. 查询条件中使用了OR

使用OR时，MySQL无法使用多个索引，也无法对多个索引进行合并，只能通过全表扫描来查找符合条件的数据，例如：

SELECT * FROM users WHERE province = '广东' OR city = '深圳';

MySQL无法使用province和city字段的索引，只能通过全表扫描来查找符合条件的数据。

1. 查询条件中使用了NOT

使用NOT时，MySQL无法使用索引，只能通过全表扫描来查找符合条件的数据，例如：

SELECT * FROM users WHERE NOT province = '广东';

MySQL无法使用province字段的索引，只能通过全表扫描来查找符合条件的数据。

更多导致索引失效的原因可以参考：《MySQL 行为和优化》第五章

索引失效示例

以下是两个导致索引失效的示例：

示例一：对索引字段进行运算或函数操作

假设有一张用户表users，表结构如下：

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `register_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `register_time` (`register_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

此时如果查询register_time为2021年的所有用户，使用以下语句：

SELECT * FROM users WHERE YEAR(register_time) = 2021;

MySQL无法使用register_time字段的索引，只能通过全表扫描来查找符合条件的数据。

为了解决这个问题，可以使用以下语句：

SELECT * FROM users WHERE register_time BETWEEN '2021-01-01 00:00:00' AND '2021-12-31 23:59:59';

这样就可以使用register_time字段的索引来加速查询。

示例二：LIKE查询时，通配符在两侧

假设有一张用户表users，表结构如下：

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `register_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

此时如果查询username中包含张的所有用户，使用以下语句：

SELECT * FROM users WHERE username LIKE '%张%';

MySQL无法使用username字段的索引，只能通过全表扫描来查找符合条件的数据。

为了解决这个问题，在设计表结构时，可以考虑将username字段使用全文索引来优化查询效率，使用以下语句创建全文索引：

CREATE FULLTEXT INDEX `username_fulltext` ON `users` (`username`);

然后使用以下语句来查询符合条件的数据：

SELECT * FROM users WHERE MATCH(username) AGAINST('张');

使用全文索引可以大幅提高查询效率。

总结

索引失效是MySQL中常见的问题，需要注意在查询时尽量避免上述导致索引失效的情况。如果无法避免，可以通过优化查询语句、重新设计表结构等方式来解决问题，从而提高查询效率，提升系统性能。