欢迎大家来到IT世界,在知识的湖畔探索吧!
索引是 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;
欢迎大家来到IT世界,在知识的湖畔探索吧!
1、联合索引中断
在使用联合索引进行查询时,如果联合索引中的某一个列出现了索引中断的情况,那么整个联合索引都会失效,无法继续使用索引来优化查询。
例如:对于联合索引 (name, score),如果条件中如果只有 score,则会导致索引失效。
欢迎大家来到IT世界,在知识的湖畔探索吧!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 将无法使用该索引来优化查询,从而导致索引失效。
例如:如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则会导致索引失效。
欢迎大家来到IT世界,在知识的湖畔探索吧!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`);
然后插入 条数据:
create PROCEDURE `insert_person`() begin declare c_id integer default 3; while c_id <= 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,说明是全表扫描。
免责声明:本站所有文章内容,图片,视频等均是来源于用户投稿和互联网及文摘转载整编而成,不代表本站观点,不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益,请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权,请联系删除,如若转载,请注明出处:https://itzsg.com/94754.html