索引失效了?看看这几个常见的原因!

文章介绍了MySQL数据库中索引失效的几个常见原因,包括联合索引中断、数据类型不匹配、查询条件使用函数、前模糊查询、OR查询、建立索引时使用函数以及索引区分度不高等。通过示例详细解释了这些情况如何导致索引无法用于查询优化。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

索引是 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,说明是全表扫描。

<think>好的,我现在需要帮助用户理解MySQL组合索引失效原因和原理。首先,我得回忆一下组合索引的基本结构。组合索引是多个列组成的索引,比如(a, b, c),这样的索引是按照最左前缀原则来使用的。也就是说,查询条件必须包含索引的最左边的列,否则索引可能不会被使用。 接下来,我需要列举常见索引失效情况。第一个想到的就是不符合最左前缀原则的情况。如果用户查询时没有使用到索引的第一个列,比如只有b和c的条件,那么组合索引可能不会被使用。这时候应该举例说明,比如索引是(a,b,c),但查询条件是WHERE b=1 AND c=2,这样索引就失效了。 第二个情况是范围查询后的列失效。比如,当使用a=1 AND b>10 AND c=3时,范围查询b>10会导致c的条件无法使用索引。这时候需要解释范围查询右边的列无法利用索引,只能用到a和b部分。 第三个情况是使用了函数或表达式。比如对索引列进行运算,如WHERE UPPER(a) = 'VALUE',或者a+1=5,这样会导致索引失效,因为MySQL无法直接使用索引的值。 第四个情况是类型转换。如果索引列是字符串类型,但查询时用了数字,比如a=123,而a是VARCHAR,这会导致隐式类型转换,使索引失效。这时候需要提醒用户注意数据类型的一致性。 第五个情况是OR条件使用不当。如果OR连接的多个条件中有未索引的列,比如WHERE a=1 OR d=2,而d没有索引,那么整个查询可能无法使用索引,导致全表扫描。 第六个情况是索引列参与计算,比如WHERE a * 2 = 4,这同样会导致无法使用索引,应该写成WHERE a = 2。 第七个情况是数据分布问题。当优化器认为全表扫描比使用索引更快时,比如当大部分数据都符合条件,或者索引的选择性很低,这时候可能不会使用索引。需要解释优化器的成本计算机制。 然后,需要结合用户的引用来看看是否有相关内容。用户提供的引用中有提到数据库连接失败的问题,这可能和索引失效无关,但需要确保引用正确。例如,引用[2]提到了数据库连接失败,但这里可能不需要用到。而引用[1]和引用[3]似乎和主题无关,但可能用户希望引用相关的内容。不过根据问题来看,应该主要依赖自己的知识,引用可能作为补充。 接下来,需要生成相关问题。考虑用户可能想进一步了解如何避免索引失效、优化组合索引的设计,或者分析执行计划的方法。比如,如何设计有效的组合索引,如何分析执行计划中的索引使用情况,优化器选择索引的原理等。 最后,要确保回答结构清晰,分点列出失效原因,并给出示例,每个原因都简明扼要。同时,注意使用中文,数学表达式用$...$,独立公式用$$...$$,但在这个问题中可能涉及较多的SQL示例,因此需要注意代码块的格式。不过用户的问题中没有要求代码块,但可能需要用示例来说明情况。比如,在解释范围查询导致失效时,给出具体的SQL语句示例。 总结一下,需要组织回答为几个部分,每个原因一个标题,然后具体解释和示例,最后生成相关问题。同时,检查是否有需要引用的地方,比如如果有提到优化器选择索引的原理,可以引用相关的资料,但用户提供的引用可能不相关,所以可能不需要添加引用标识。根据用户提供的引用内容,引用[3]中有关于数据库设计和微服务的实践,但和索引失效关系不大,所以可能无法引用。因此,可能不需要添加引用,或者仅在需要时提及。但根据用户示例中的引用,可能需要在末尾添加引用标识,但根据用户给的引用内容,这里可能没有直接相关的,所以需要确认。例如,如果提到优化器决策,可能需要引用,但用户提供的引用中没有相关的内容,所以可能不需要。</think>### MySQL组合索引失效原因和原理 组合索引通过将多个字段按顺序存储为B+树结构加速查询,但其有效性依赖于查询条件与索引结构的匹配程度。以下是常见的失效场景及原理分析: --- #### 一、违反最左前缀原则 **原理**:组合索引按照字段顺序构建B+树,若查询未使用索引最左字段,则无法利用树形结构快速定位数据。 **示例**: - 索引:`(a, b, c)` - 失效查询:`WHERE b=1 AND c=2` - 有效查询:`WHERE a=1 AND b=2` --- #### 二、范围查询后的字段失效 **原理**:范围操作(如`>`、`<`、`BETWEEN`)会导致索引后续字段无法继续使用有序性。 **示例**: ```sql -- 仅使用索引(a, b),c无法走索引 SELECT * FROM table WHERE a=1 AND b>10 AND c=3; ``` --- #### 三、函数或表达式操作索引列 **原理**:对索引列进行函数或计算会破坏索引值与存储值的一致性。 **示例**: ```sql -- 失效:使用函数 SELECT * FROM table WHERE UPPER(a) = 'VALUE'; -- 失效:数学计算 SELECT * FROM table WHERE a+1 = 5; ``` --- #### 四、隐式类型转换 **原理**:数据类型不匹配(如字符串与数字比较)会触发隐式转换,导致索引无法匹配。 **示例**: ```sql -- 索引列a为VARCHAR类型,但查询用数字比较 SELECT * FROM table WHERE a = 123; ``` --- #### 五、OR条件使用不当 **原理**:若OR连接的条件中有未索引字段,优化器可能直接选择全表扫描。 **示例**: ```sql -- 若d字段无索引,则整个查询无法使用索引 SELECT * FROM table WHERE a=1 OR d=2; ``` --- #### 六、索引列参与计算 **原理**:索引存储原始值,无法直接匹配计算后的结果。 **示例**: ```sql -- 失效:索引列参与计算 SELECT * FROM table WHERE a*2 = 4; -- 优化:改写为直接值匹配 SELECT * FROM table WHERE a = 2; ``` --- #### 七、数据分布导致优化器弃用索引 **原理**:当索引选择性低(如重复值多)或查询覆盖大部分数据时,优化器可能认为全表扫描更快。 **示例**: ```sql -- 若status=1的数据占90%,优化器可能跳过索引 SELECT * FROM table WHERE status=1; ``` ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值