索引失效了？看看这几个常见的原因！

偏执的太偏执、 2023-09-26 20:41 124阅读 0赞

索引是 MySQL 数据库中优化查询性能的重要工具，通过对查询条件和表数据的索引，MySQL可以快速定位数据，提高查询效率。但是，在实际的数据库开发和维护中，我们经常会遇到一些情况，导致索引失效，从而使得查询变得非常缓慢，甚至无法使用索引来优化查询，这会严重影响系统的性能。那么，是什么原因导致了索引失效呢？

常见的情况有：

*  索引中断
 *  数据类型不匹配
 *  查询条件使用函数操作
 *  前模糊查询
 *  OR 查询
 *  建立索引时使用函数
 *  索引区分度不高

下面我通过实际的例子来具体说说。假设现在我们有一张人物表，建表语句如下：

CREATE TABLE `person` (
       `id` bigint(20) NOT NULL AUTO_INCREMENT,
       `name` varchar(64) NOT NULL,
       `score` int(11) NOT NULL,
       `age` int(11) NOT NULL,
       `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
       PRIMARY KEY (`id`)
     ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
    复制代码

## 1、联合索引中断 ##

在使用联合索引进行查询时，如果联合索引中的某一个列出现了索引中断的情况，那么整个联合索引都会失效，无法继续使用索引来优化查询。

例如：对于联合索引 `（name, score）`，如果条件中如果只有 `score`，则会导致索引失效。

CREATE INDEX idx_name_score ON person  (`name`,`score`);
     select * from person where score = 90
    复制代码

而下面的情况都会使用索引：

select * from person where name = '31a'
     select * from person where score = 90 and name = '31a'
     select * from person where name = '31a' and score = 90
    复制代码

## 2、数据类型不匹配 ##

如果我们在查询条件中使用了一个不匹配索引的数据类型的值，那么 MySQL 将无法使用该索引来优化查询，从而导致索引失效。

例如：如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则会导致索引失效。

CREATE INDEX idx_name ON person (`name`);
     -- 这里 name 是 varchar 类型
     select * from person where name = 31
    复制代码

但是如果索引是 int 类型，而查询参数是 varchar 类型，因为字符串隐式转为数值，不存在歧义，所以会走索引。

CREATE INDEX idx_age ON person (`age`);
     -- 这里 age 是 int 类型
     select * from person where age = '90'
    复制代码

MySQL 为什么不把 31 隐式转换字符串呢？这个问题在 [MySQL 官方文档][MySQL]中给出了答案。

![format_png][]

针对数值1，与字符串'1', '1a', '001', '1 '等多种情况均相等，会存在歧义。不妨看个例子：

我们插入两条数据：

INSERT INTO test.person (id, name, score, age, create_time) VALUES(1, '00031', 90, 18, '2023-04-15 16:29:39');
     INSERT INTO test.person (id, name, score, age, create_time) VALUES(2, '31a', 96, 19, '2023-04-15 16:29:39');
    复制代码

然后执行查询操作：

select * from persion where name = 31;
    复制代码

![format_png 1][]

## 3、查询条件使用函数操作 ##

当我们在查询条件中使用函数操作时，这将导致索引失效。例如：

CREATE INDEX idx_name ON person (`name`);
     select * from person where UPPER(name) = '31A';
    复制代码

## 4、前模糊查询 ##

如果我们在查询条件中使用了前模糊查询，那么 MySQL 将无法使用 B-Tree 索引的前缀匹配查询，从而导致索引失效。例如：

CREATE INDEX idx_name ON person (`name`);
     select * from person where name LIKE '%a';
    复制代码

## 5、OR 查询 ##

当我们在查询条件中使用 OR 连接多个条件时，OR 前后条件都包含索引则走索引，OR 前后有一个不包含索引则索引失效。例如：

CREATE INDEX idx_age ON person (`age`);
     select * from person where name = 'John' OR age > 20;
    复制代码

## 6、建立索引时使用函数 ##

如果在建立索引时使用了函数操作，即使使用了索引列，索引也不会生效。例如：

CREATE INDEX idx_name ON person (LOWER(name));
     -- 如果使用 LOWER(name) 函数建立索引，那么下面查询将导致索引失效
     select * from person where name = 'John';
    复制代码

## 7、索引区分度不高 ##

如果索引列的值区分度不高，MySQL 可能会放弃使用索引，选择全表扫描，导致索引失效。例如我们创建了下面两条索引：

CREATE INDEX idx_name ON person (`name`);
     CREATE INDEX idx_create_time ON person (`create_time`);
    复制代码

然后插入 100000 条数据：

create PROCEDURE `insert_person`()
     begin
         declare c_id integer default 3;
         while c_id <= 100000 do
             insert into person values(c_id, concat('name',c_id), c_id + 100, c_id + 10, date_sub(NOW(), interval c_id second));
             set c_id = c_id + 1;
         end while;
     end;
     CALL insert_person();
    复制代码

接着执行：

explain select * from person where NAME>'name84059' and create_time>'2023-04-15 13:00:00'
    复制代码

结果如下：

![format_png 2][]

通过上面的执行计划可以看到：type=All，说明是全表扫描。

[MySQL]: https://link.juejin.cn?target=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F8.0%2Fen%2Ftype-conversion.html
[format_png]: https://img-blog.csdnimg.cn/img_convert/88ec6851a39ace1bceabe8c797fa4904.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/1e0411542fefc638242aea5ab300a506.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/95ffa27128bb1592c4e101aee8e1504b.webp?x-oss-process=image/format,png