Mysql索引失效的情况

最新推荐文章于 2025-05-25 08:55:42 发布
原创 最新推荐文章于 2025-05-25 08:55:42 发布 · 214 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #数据库

本文总结了MySQL中可能导致索引失效的几种情况：1) 使用OR操作符时；2) like查询以%开头的模糊匹配；3) 多列索引仅第一列有效；4) 字符串类型查询未正确引用或转换；5) MySQL认为全表扫描更快时不使用索引。了解这些情况有助于优化数据库查询性能。

前提：

开发中遇到索引失效，遂查阅相关博客学习了解，得出如下Mysql索引失效情况。

情况：

1、条件中带有or，则条件中有条件带索引都会失效。

2、使用模糊查询中，like后模糊查询字符串以%开头，则查询字段索引失效。如‘%thh’。

3、多列索引中，只有第一部分的索引生效，其余部分失效。

4、查询字段是字符串类型，则若转换了查询字段类型或查询数据未用‘ ’引用，则索引失效。

如Date(str_time)、str_name=123，索引失效。

5、mysql认为全表查询比索引快，则不使用索引。

参考博客：

参考博客：MySQL索引失效原理是什么？

【MySQL 进阶之路】索引失效的11种情况
ajsbxi的博客
12-09 3087
在MySQL的查询优化中，索引是一项至关重要的技术，它能够大大提升数据检索的效率。本文将讨论这11种常见情况，帮助开发者更好地理解索引的使用及优化。
04-MySql 索引失效情况和优化
每天码一点
01-18 952
准备数据 CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `stuno` int(11) NOT NULL, `name` varchar(20) DEFAULT NULL, `age` int(3) DEFAULT NULL, `classId` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_age_classId_name` (`age`,`c
MySQL高级篇——索引失效的11种情况
热门推荐
种一棵树最好的时间是十年前，其次是现在
05-21 3万+
索引优化思路、要尽量满足全值匹配、最佳左前缀法则、主键插入顺序尽量自增、计算、函数导致索引失效、类型转换（手动或自动）导致索引失效、范围条件右边的列索引失效、不等于符号导致索引失效、is not null、not like无法使用索引、左模糊查询导致索引失效、“OR”前后存在非索引列，导致索引失效、不同字符集导致索引失败，建议utf8mb4
mysql索引失效的常见9种原因详解
qq_63815371的博客
04-22 3万+
目录 前言： 1.最佳左前缀法则 2.主键插入顺序 3.计算、函数、类型转换(自动或手动)导致索引失效 4.范围条件右边的列索引失效 5.不等于(!= 或者<>)导致索引失效 6.is null可以使用索引，is not null无法使用索引 7.like以通配符%开头索引失效 8.OR 前后只要存在非索引的列，都会导致索引失效 9.数据库和表的字符集统一使用utf8mb4 特别鸣谢： 前言： MySQL中提高性能的一个最有效的方式是对数据表设计合...
MySQL索引失效的12种场景及解决方案
风象南的专栏
05-25 3680
索引优化是一个持续的过程，需要结合具体的业务场景和数据特点。通过了解这些索引失效的场景和原理，你可以更有针对性地设计索引策略，显著提升数据库性能。没有一劳永逸的索引方案，随着数据量的增长和业务的变化，索引策略也需要不断调整和优化。持续监控、分析和优化是保持高性能数据库的关键。
索引失效，或者一个字段走索引，一个字段没走索引
Chen4852010的博客
03-10 884
1.只要 有 没走 索引表的字段（不管是什么原因，多列索引失效也好，其他方面也好，一定会回表 2.只要走了索引，回表后，再不会走索引，会从 回表的记录中，作全表扫描过滤
索引知识一：索引失效的情况
weixin_40650387的博客
05-07 458
众所周知，同一个市洲大部分人员身份证前6位是相同的，比如黄石，前六位420222。这个时候你就可以通过reverse（）字段，创建身份证后6位的前缀索引进行查询。9、条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引。= 或者<>)的时候，无法使用索引，会导致索引失效。可以看到可以使用的索引是page，实际使用索引page。使用%3查询走的是全表扫描，并没有用到page索引。3. 使用%3% 进行查询索引会失效。
导致MySQL索引失效的一些常见写法总结
09-14
在MySQL数据库中，索引是一种重要的优化手段，它能够显著...以上就是关于导致MySQL索引失效的一些常见情况及其避免方法。在实际工作中，我们需要结合具体业务场景和查询需求，合理设计和使用索引，以提高数据库性能。
MySQL索引失效的几种情况汇总
12-14
下面将详细探讨几种常见的索引失效情况及其原因。 首先，索引不存储`NULL`值。在MySQL中，单列索引不会存储`NULL`值，而复合索引则不存储所有列都是`NULL`的值。这是因为`NULL`值在比较操作中具有特殊性，不适用于...
mysql索引失效的五种情况分析
12-15
以下就是五种可能导致MySQL索引失效的情况： 1. **OR条件**：当查询语句中包含OR操作符时，即使部分条件涉及到带有索引的列，MySQL也可能不会使用索引。为了使索引生效，每个OR子句中的列都需要有独立的索引。例如...
Mysql索引失效的几种情况总结
情深深几许
07-20 4535
总结了Mysql索引失效的几种情况，对于新手好上理解，对于老手相当于总结。
15个必知的Mysql索引失效场景，别再踩坑了
程序新视界
02-28 4631
背景 无论你是技术大佬，还是刚入行的小白，时不时都会踩到Mysql数据库不走索引的坑。常见的现象就是：明明在字段上添加了索引，但却并未生效。 前些天就遇到一个稍微特殊的场景，同一条SQL语句，在某些参数下生效，在某些参数下不生效，这是为什么呢？ 另外，无论是面试或是日常，Mysql索引失效的通常情况都应该了解和学习。 为了方便学习和记忆，这篇文件将常见的15种不走索引情况进行汇总，并以实例展示，帮助大家更好地避免踩坑。建议收藏，以备不时之需。 数据库及索引准备 创建表结构 为了逐项验证索引的使用情况，我们先
详解MySQL索引失效的几种情况
m0_57614677的博客
02-28 1万+
MySQL索引失效的几种情况
MySQL数据库中的索引失效原因分析和解决
vipfanxu的博客
11-01 1913
我们可以通过避免在索引列上使用函数、使用合适的条件表达式、确保查询条件与索引列类型匹配、使用合适的索引、表结构优化以及使用索引提示等方法来解决索引失效的问题，进而提高数据库查询的性能。如果确实需要使用不等于操作符，可以考虑通过拆分查询，使用UNION操作符来减少不等于操作符的使用次数，提高性能。如果有必要，我们可以将函数操作提前，将结果存入一个新的列中，并在新列上建立索引，以提高查询效率。例如，索引列是字符串类型，但在查询时使用了数值类型，MySQL会放弃使用索引。在这种情况下，索引可能会被认为是无效的。
MySQL索引失效的原因及应对策略
沐雨风栉
06-30 2097
索引是提高MySQL查询性能的重要工具，但在某些情况下，索引可能会失效。了解索引失效的原因并找到对应的优化策略，能够帮助我们在实际应用中更好地使用索引，从而提高数据库的性能。同时，我们也需要注意，过多的索引会对数据库的写操作带来影响，因此，要在提高查询效率和保持写操作性能之间找到一个平衡。
Mysql索引失效
qianpc的博客
07-26 3560
针对Mysql数据库索引失效问题网上，有很多见解和答案。最近面试也是时常会被问道的一个知识点，没有太深的原理简单的整理一下，方便面试,加深印象。
MySQL之索引失效情况
上善若泪
02-08 8599
文章目录1 MySQL索引1.1 简介1.1.1 索引创建1.1.2 索引类型1.1.3 索引优缺点1.2 SQL优化1.2.1 查看执行计划1.2.2 show profile分析1.2.3 trace2 索引失效2.1 问题引入2.2 准备工作2.2.1 创建user表2.2.2 插入数据2.2.3 查看数据库版本2.3 联合索引2.3.1 联合索引中索引有效2.3.2 联合索引中索引失效2.4 select *2.5 索引列上有计算2.6 索引列用了函数2.7 字段类型不同2.8 like左边包含%2
MySQL索引失效原因及解决方案
weixin_45626288的博客
07-27 3919
在使用MySQL数据库时，索引是一种重要的性能优化工具。然而，有时候我们可能会遇到索引失效的情况。本文将介绍几种常见的MySQL索引失效原因以及相应的解决方案，并提供SQL语句的错误示例和正确示例。
MySQL索引失效有哪些情况
最新发布
09-19
<think> 我们正在回答用户关于MySQL索引失效情况的问题。根据引用内容，我们可以总结一些常见的索引失效情况，并补充其他常见情况。 注意：引用[1]和引用[2]提供了部分索引失效的场景，我们需要结合这些引用并补充其他常见情况。 常见的索引失效情况包括但不限于以下情况： 1. 使用否定条件：当使用`!=`、`<>`、`NOT IN`、`NOT LIKE`等否定操作符时，索引可能会失效。 示例：`SELECT * FROM orders WHERE status != 'completed';` [^1] 2. 使用函数：在WHERE子句中对索引列使用函数时，索引失效。 示例：`EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';` [^2] 但可以优化为使用LIKE：`EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';`（此时会使用索引）[^2] 3. 使用计算：在WHERE子句中对索引列进行算术运算或其他计算时，索引失效。 示例：`EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;` [^2] 4. 类型转换：如果索引列是字符串类型，而查询条件中使用了数字（或者反之），会导致索引失效。 示例：`SELECT * FROM user WHERE phone = 13800138000;`（phone是varchar类型） 5. OR条件使用不当：如果OR条件中有一个列没有索引，那么即使其他列有索引，整个查询也不会使用索引。 示例：`SELECT * FROM table WHERE column1 = 'value' OR column2 = 'value';` 如果column2没有索引，则整个查询不会使用索引。 6. LIKE以通配符开头：当使用LIKE且通配符在开头时，索引失效。 示例：`SELECT * FROM user WHERE name LIKE '%abc';` 7. 复合索引未遵循最左前缀原则：对于复合索引，如果查询条件不是从索引的最左列开始，或者跳过了中间列，索引可能失效。 示例：有复合索引`(a, b, c)`，查询条件为`b=1`或`c=1`，或者`a=1 and c=1`（跳过了b）等。 8. 使用IS NULL或IS NOT NULL：在某些情况下，使用IS NULL或IS NOT NULL可能会导致索引失效。但这取决于表数据的具体情况（例如，如果该列不允许为NULL，则优化器可能使用索引）。 9. 范围条件右边的列：在复合索引中，如果某个列使用了范围条件（比如>、<、between等），则其右边的列无法使用索引。 示例：复合索引`(a, b, c)`，条件`a=1 and b>10 and c=2`，那么c列不能使用索引。 10. 数据分布影响：如果MySQL优化器发现全表扫描比使用索引更快（例如，表中数据量很小，或者满足条件的行数占大部分），则可能不使用索引。 11. 使用IN：在MySQL中，使用IN通常还是会使用索引的，但如果IN中的值过多，优化器可能会认为全表扫描更快，从而不使用索引。不过这不是绝对的失效，需要根据具体情况而定。 12. 使用连接查询时，连接条件字符集不一致：当两个表进行连接时，如果连接条件的字符集不同，会导致索引失效。 我们可以根据这些情况来组织回答，并给出相应的示例和解决方案。 注意：引用[3]是关于EXPLAIN语句结果列的解释，我们在解释索引失效时，可以通过EXPLAIN来查看是否使用了索引（key列显示为NULL表示未使用索引）。 此外，我们还需要生成相关问题。 </think> ### MySQL索引失效的常见情况及解决方案 #### 一、操作符导致失效 1. **否定操作符失效** 在使用`!=`, `<>`, `NOT IN`, `NOT LIKE`等否定条件时，索引通常失效： ```sql -- 索引失效示例（status字段有索引idx_status） SELECT * FROM orders WHERE status != 'completed'; SELECT * FROM orders WHERE status NOT IN ('completed', 'shipped'); -- [^1] ``` **解决方案**： - 改为正向条件：`status IN ('pending', 'processing')` - 使用覆盖索引：`SELECT status FROM orders WHERE status != 'completed'` 2. **范围查询右侧失效** 复合索引中，范围查询（`>`, `<`, `BETWEEN`）之后的列无法使用索引： ```sql -- 复合索引 (a,b,c) SELECT * FROM table WHERE a=1 AND b>10 AND c=2; /* 仅用到a,b */ ``` #### 二、表达式处理导致失效 3. **函数操作失效** 对索引列使用函数会使索引失效： ```sql -- 失效（LEFT函数） SELECT * FROM student WHERE LEFT(name,3) = 'abc'; -- [^2] ``` **解决方案**： ```sql -- 改为LIKE前缀匹配（利用索引） SELECT * FROM student WHERE name LIKE 'abc%'; -- [^2] ``` 4. **计算表达式失效** 索引列参与计算会失效： ```sql -- 失效（stuno字段有索引） SELECT * FROM student WHERE stuno+1 = 900001; -- [^2] ``` **解决方案**： ```sql -- 隔离计算部分 SELECT * FROM student WHERE stuno = 900001 - 1; ``` #### 三、数据结构导致失效 5. **隐式类型转换失效** 数据类型不匹配触发隐式转换： ```sql /* phone是varchar类型 */ SELECT * FROM users WHERE phone = 13800138000; /* 失效 */ ``` **解决方案**： ```sql SELECT * FROM users WHERE phone = '13800138000'; /* 有效 */ ``` 6. **LIKE通配符前缀失效** 前导通配符使索引失效： ```sql SELECT * FROM products WHERE name LIKE '%apple%'; /* 全表扫描 */ ``` **解决方案**： - 使用后缀匹配：`name LIKE 'apple%'` - 添加全文索引：`ALTER TABLE products ADD FULLTEXT(name)` #### 四、优化器决策失效 7. **数据倾斜导致失效** 当满足条件的数据超过总量的$20\% \sim 30\%$时，优化器可能选择全表扫描： ```sql -- 订单表90%状态为'completed' SELECT * FROM orders WHERE status = 'completed'; /* 可能全表扫描 */ ``` **解决方案**： - 强制使用索引：`SELECT * FROM orders FORCE INDEX(idx_status) WHERE ...` - 使用覆盖索引减少IO 8. **OR条件部分失效** 若OR条件中有非索引列，整个查询失效： ```sql -- name有索引，address无索引 SELECT * FROM users WHERE name='Alice' OR address='Beijing'; ``` **解决方案**： ```sql -- 拆分为UNION查询 SELECT * FROM users WHERE name='Alice' UNION ALL SELECT * FROM users WHERE address='Beijing' AND name!='Alice'; ``` #### 五、验证索引使用情况 使用`EXPLAIN`分析执行计划（关键字段）： - **possible_keys**：可能使用的索引 - **key**：实际使用的索引（若为NULL则索引失效） - **type**：访问类型（const > ref > range > index > ALL） -- [^3] ![](https://via.placeholder.com/600x200?text=EXPLAIN输出示例图示) ---
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

浅尝即止何来突破

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值