乐趣区

关于mysql:索引失效了看看这几个常见的原因

jiezi

索引是 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 官网文档中给出了答案。

针对数值 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;

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'

后果如下：

通过下面的执行打算能够看到：type=All，阐明是全表扫描。

退出移动版