哪些情况下sql索引会失效

最新推荐文章于 2025-10-19 13:47:05 发布

转载最新推荐文章于 2025-10-19 13:47:05 发布 · 1.7w 阅读

数据库专栏收录该内容

11 篇文章

订阅专栏

本文探讨了在数据库查询中导致索引失效的多种情况，包括使用or条件、未使用多列索引的第一部分、以%开头的like模糊查询、未正确引用字符串数据以及全表扫描可能比索引查询更快等。

原文：http://www.cnblogs.com/hongfei/archive/2012/10/20/2732589.html

面试时被问到这个问题，记录笔记，免得被面试的人鄙视！！

索引什么时候不会生效，以下集中情况会导致索引失效：

1.条件中用or，即使其中有条件带索引，也不会使用索引查询（这就是查询尽量不要用or的原因，用in吧）

注意：使用or，又想索引生效，只能将or条件中的每个列都加上索引

2.对于多列索引，不是使用的第一部分，则不会使用索引。

3.like的模糊查询以%开头，索引失效

4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不会使用索引

5.如果MySQL预计使用全表扫描要比使用索引快，则不使用索引

下面是查看索引使用情况：

show status like ‘Handler_read%’;
大家可以注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数
handler_read_rnd_next:这个值越高，说明查询低效

确定要放弃本次机会？

福利倒计时

: :

立减 ¥

普通VIP年卡可用

立即使用

oOo右右

关注关注

3
点赞
踩
36

收藏

觉得还不错? 一键收藏
0
评论
分享

复制链接

分享到 QQ

分享到新浪微博

扫一扫
举报

举报

专栏目录

【SQL】索引失效的11种情况

null

11-24

7486

数据库调优的大致方向：sql查询优化技术有很多，大体分为物理查询优化和逻辑查询优化:数据准备： 索引失效案例【1】. 全值匹配【2】. 最佳左前缀法则在定义表时，让主键auto_increment，否则，插入一条数据时可能会移动大量数据。如，往 1 5 8 10 15 … 100 中插9，会放在8 10 中间，因为索引默认升序排列。那么10往后的数据都要挪动，页不够时又要放到下一页，每插一条数据都这样挪一次，开销很大我们自定义的主键列id 拥有AUTO_INCREMENT 属性，在插入记录时存

SQL索引失效原因分析与解决方案

jakelihua

04-07

1785

原因：该查询中使用了列，但如果没有为该列建立索引，数据库可能会选择进行全表扫描，而不是利用索引进行快速查询。解决办法：为。

参与评论您还未登录，请先登录后发表或查看评论

MySQL常见优化

爱是与世界平行

09-17

1394

MySQL常见优化1.操作符优化1.1<> 操作符（不等于）1.2LIKE优化1.3in,not in,exists与not exists1.3.1in和exists2.where子句优化2.1第一个原则：在where子句中应把最具限制性的条件放在最前面。2.2第二个原则：where子句中字段的顺序应和索引中字段顺序一致。2.3应尽量避免在 where 子句中使用 or 来连接条件，2...

数据库索引失效

热门推荐

Anna_luo的博客

03-16

1万+

1、带有运算 2、使用函数 3、使用%XXX左模糊查询，因为mysql是最左原则，使用XXX%右模糊查询是可以使用索引的，但是左模糊违背了最左原则所以不行 4、使用范围运算，not in，in > ,<都不行 5、查询的字段不是索引的最左字段，同样是因为最左原则 6、字段类型不匹配，常见的隐式数据类型转换，mobile=1356不会走索引，会转换为字符串可以查询但是，mobile='1356'会走索引 7、or条件左边的是索引字段，右边的不是。也不会走索引，因为or是一个并集

MySQL中有哪些情况下数据库索引会失效详析

12-16

前言 ... 索引会产生相应的碎片，产生维护开销一、explain 用法：explain +查询语句。 id：查询语句的序列号，上面图片中只有一个select 语句，所以只会显示一个序列号。如果有嵌套查询，如下 se

这种sql写法真的会导致索引失效吗

09-09

总结来说，虽然使用`OR`操作符的SQL语句可能在某些情况下导致索引失效，但在MySQL 5.0之后，通过`index_merge`特性，我们可以有效地利用多个索引。然而，这并不意味着所有包含`OR`的查询都能自动优化，数据库管理员...

sql索引失效？详细介绍常见原因和如何用好索引

Libigtong的博客

10-16

2155

sql索引失效？详细介绍常见原因和如何用好索引。EXPLAIN select * from test where code = '001' and age > 18 and name = '张三'执行结果:从上图中看出索引长度变成： 96 ， 96 = 30*3 + 2 + 4 ，只用到了两个索引字段 code 和age ，而 name 字段的索引失效了。

数据库-sql-索引失效

xycxycooo的博客

07-28

583

索引失效的场景多种多样，了解这些场景并采取相应的优化措施，可以显著提高数据库查询性能。通过避免在查询条件中使用函数或表达式、确保数据类型匹配、合理使用不等号和范围查询、关注数据分布、避免使用OR条件以及考虑数据量大小，可以最大限度地利用索引，提高查询效率。

哪些情况下sql的索引会失效呢

欢迎来到可乐的IT博客

05-12

1681

索引什么时候不会生效，以下集中情况会导致索引失效： 1.条件中用or，那么就算其中条件带索引，也不会使用索引查询这里就要注意查询时尽量用in，避免用or 关于explain 请见本博另一篇博文：https://blog.youkuaiyun.com/yxd504/article/details/106080546 此处我们在将realname设为索引，此处需要索引生效表内数据必须大于等于三条则看到key，索引生效，先使用主键索引，在使用索引，按照顺序。 Type ：index_mer

哪些情况会让SQL索引失效？

Spring_Lws的博客

04-08

560

SQL索引失效

MySQL使用explains优化慢查询

weixin_30607029的博客

10-09

444

MySQL的Explain命令用于查看执行效果，显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。explain的语法如下，在select语句前加上explain就可以： 1 explainselectxxfromtbwherexx； ...

【SQL】索引失效的几种情况

hui_zai_的博客

07-02

1824

索引失效的几种情况

SQL索引失效情况

bigBbug的博客

04-03

1460

SQL索引失效汇总

MySQL 面试题（一）：索引失效的几种情况

水滴的博客

02-01

4602

又到了跳槽季，今天我们来看一个被高频问到的面试题：MySQL 索引失效有哪些情况？我列举了下面几种，如果小伙伴们有补充的，欢迎给我留言哦。

SQL索引失效的常见情况

TeAmo_Fa的博客

03-26

1051

索引类似于书的目录，能够快速定位所需数据，而不需要扫描整个表。索引通常不会存储 NULL 值，导致数据库可能执行全表扫描。：数据库可以直接按索引顺序返回数据，而不需要额外排序。号出现在开头，数据库无法使用 B+ 树索引，必须。各自有单独的索引，数据库通常不会使用索引，而是。：数据库可以使用索引快速匹配两张表。，找出未使用索引的 SQL 并优化。排序，数据库可以直接进行分组。，导致数据库无法直接使用索引。，然后进行匹配，导致全表扫描。的值，索引无法高效筛选数据。（主键、联合索引、覆盖索引）。

MySQL查询不使用索引汇总

aaron1059的博客

09-16

314

　　众所周知，增加索引是提高查询速度的有效途径，但是很多时候，即使增加了索引，查询仍然不使用索引，这种情况严重影响性能，这里就简单总结几条MySQL不使用索引的情况　　如果MySQL估计使用索引比全表扫描更慢，则不使用索引。例如，如果列key均匀分布在1和100之间，下面的查询使用索引就不是很好：select * from table_name where key>1 and ...

Sql 中哪些情况可能不会走索引

iemsadw的博客

02-23

2096

索引

什么情况下mysql索引会失效

09-25

<think> 首先，用户的问题是：“我想查找MySQL索引失效的情况请问MySQL索引失效的情况有哪些”，这是基于之前的引用内容。引用提供了MySQL索引失效的场景，具体包括： - 引用[1]：在WHERE子句中对索引列使用函数时，索引失效。例如：`SELECT * FROM orders WHERE YEAR(create_time) = 2023;`，即使有`idx_create_time`索引在`create_time`上。 - 引用[2]：走了联合索引索引没有失效的情况，但强调如果索引可用并能有效满足查询条件，MySQL会优先使用索引。 - 引用[3]：列出了6种常见场景：联合索引不满足最左匹配原则；模糊查询最前面为不确定匹配字符；索引列参与了运算；索引列使用了函数；索引列存在类型转换；索引列使用 is not null 查询。用户要求参考这些引用，并基于上一次的问题和回答。回顾对话历史： - 系统指令：要求使用特定的LaTeX格式，结构清晰，生成相关问题等。 - 用户上一次的问题是关于MySQL索引失效的情况，我有生成回答和相关问题的义务。在我的角色中，我必须遵循系统级指令： - 行内数学表达式用`$...$`。 - 独立公式用`$$...$$`。 - LaTeX语法正确。 - 使用中文回答。 - 生成相关问题。 - 回答中引用的段落末尾添加引用标识，如[^1]。所以，我需要构建一个结构清晰的回答： 1. 介绍主题：MySQL索引失效的情况。 2. 列出失效场景，基于引用内容。 3. 解释每个场景的原理或示例。 4. 确保语言中文。 5. 结束时生成相关问题。列出索引失效的主要场景，从引用中提取： - 从引用[1]：使用函数在索引列上，如`YEAR(create_time)`。 - 从引用[3]：6种场景： - 联合索引不满足最左匹配原则。 - 模糊查询最前面为不确定匹配字符（如`LIKE '%abc'`）。 - 索引列参与了运算（如`WHERE column + 1 = 10`）。 - 索引列使用了函数（与引用[1]一致）。 - 索引列存在类型转换（如`WHERE int_column = '123'`，因为类型不匹配）。 - 索引列使用 is not null 查询。引用[2]提到索引没有失效的情况，但强调的是当索引可用时使用索引。用户问题聚焦于失效情况，所以我不需要着重那个。组织回答结构： - 开头：简要介绍索引失效的概念。 - 主体：列出并解释失效场景。每个场景用一个点描述，包括示例代码（如果需要）。 - 结尾：总结并生成相关问题。确保示例代码使用正确格式。系统指令说“生成相关问题”，所以我必须在结束时添加相关问题部分。引用添加：在解释每个场景时，如果基于引用，在末尾添加[^n]。例如，提到函数使用时添加[^1]。使用中文。数学表达式：虽然主题是MySQL，但如果有数学内容，用LaTeX。但这里可能没有，所以可能不需要数学格式。如果有计算或表达式，用`$...$`。例如，如果有表达式如索引扫描，但通常不需要。现在，草拟回答： 1. **介绍**：MySQL索引失效是指查询语句无法有效利用索引进行优化，导致全表扫描，影响性能。常见的失效场景包括以下几种。 2. **失效场景**： - **场景1：在索引列上使用函数**。例如：`SELECT * FROM orders WHERE YEAR(create_time) = 2023;` 无法使用`idx_create_time`索引[^1]。原因为MySQL无法直接索引函数结果。 - **场景2：联合索引不满足最左匹配原则**。如果联合索引是`(col1, col2)`，查询只使用`col2`，索引可能失效[^3]。 - **场景3：模糊查询以通配符开头**。如`WHERE name LIKE '%abc'`，而不是`'abc%'`[^3]。因为索引是基于开头字符的。 - **场景4：索引列参与运算**。如`WHERE salary + 100 > 500`，索引在salary上无法使用[^3]。 - **场景5：索引列存在类型转换**。如`WHERE int_id = '123'`，字符串转整数可能导致索引失效[^3]。 - **场景6：使用IS NOT NULL查询**。在某些情况下，`WHERE column IS NOT NULL`可能不使用索引[^3]。还有一些其他情况，但这些是常见的。 3. **总结**：避免这些场景可以提升查询效率。 4. **相关问题**： - 1. 如何优化MySQL查询以避免索引失效？ - 2. 索引失效如何诊断？ - 3. MySQL索引的工作原理是什么？确保引用标识：在场景描述后添加[^n]。用户真正的需求是查找索引失效的情况，所以列表要全面。 </think> ### MySQL索引失效的常见场景在MySQL中，索引失效是指查询语句无法有效利用B-tree索引进行优化，导致数据库执行全表扫描（Full Table Scan），从而降低查询性能。索引失效通常发生在特定SQL写法或数据类型不匹配的情况下。基于参考内容，我总结了以下常见失效场景及其原因和解决方案。每个场景均基于引用原理进行解释，并附上示例查询。 #### 1. **在索引列上使用函数** - **场景描述**：在WHERE子句中对索引列应用函数（如日期函数、字符串函数等），MySQL无法直接使用索引树定位数据。 - **原因**：索引树存储原值，而函数计算改变了值，破坏了索引的有序性[^1]。 - **示例**： ```sql -- 创建索引 CREATE INDEX idx_create_time ON orders(create_time); -- 以下查询索引失效：YEAR()函数作用于create_time列 SELECT * FROM orders WHERE YEAR(create_time) = 2023; ``` - **解决方案**：改写查询避免函数，例如改用范围查询：`WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'`。 #### 2. **联合索引不满足最左匹配原则** - **场景描述**：针对联合索引（Composite Index），查询条件未从最左侧列开始使用，导致索引部分或完全失效。 - **原因**：联合索引遵循前缀匹配规则，如果跳过左侧列，索引树无法高效定位数据行[^3]。 - **示例**： ```sql -- 创建联合索引 CREATE INDEX idx_name_age ON users(name, age); -- 以下查询索引失效：未使用name列（最左侧列） SELECT * FROM users WHERE age > 30; -- 有效查询：使用name列（满足最左原则） SELECT * FROM users WHERE name = 'John' AND age > 30; ``` - **解决方案**：重组查询条件，确保优先使用索引最左列。 #### 3. **模糊查询以通配符开头** - **场景描述**：在LIKE子句中，通配符（%）出现在开头（如`'%abc'`），索引无法使用。 - **原因**：索引基于列值前缀构建，开头通配符破坏了前缀匹配机制[^3]。 - **示例**： ```sql -- 创建索引 CREATE INDEX idx_product_name ON products(name); -- 以下查询索引失效：%在开头 SELECT * FROM products WHERE name LIKE '%apple'; -- 有效查询：%在结尾，可利用前缀索引 SELECT * FROM products WHERE name LIKE 'apple%'; ``` - **解决方案**：避免开头通配符；或使用全文索引（Full-Text Index）优化模糊查询。 #### 4. **索引列参与运算** - **场景描述**：WHERE子句中索引列参与算术或逻辑运算（如加减、比较），MySQL无法直接使用索引。 - **原因**：运算改变了列值，索引树无法映射到原始数据[^3]。 - **示例**： ```sql -- 创建索引 CREATE INDEX idx_salary ON employees(salary); -- 以下查询索引失效：salary列参与加法运算 SELECT * FROM employees WHERE salary + 1000 > 5000; ``` - **解决方案**：分离运算，例如改写为`WHERE salary > 4000`。 #### 5. **索引列存在类型转换** - **场景描述**：查询条件中索引列与值类型不匹配（如字符串与数字比较），导致隐式类型转换，索引失效。 - **原因**：类型转换（如字符串转整数）破坏了索引值的精确匹配[^3]。 - **示例**： ```sql -- 创建索引（假设id列为INT类型） CREATE INDEX idx_user_id ON users(id); -- 以下查询索引失效：字符串'123'隐式转换为整数 SELECT * FROM users WHERE id = '123'; ``` - **解决方案**：确保比较数据类型一致，例如使用`WHERE id = 123`。 #### 6. **使用IS NOT NULL查询** - **场景描述**：在WHERE子句中使用`IS NOT NULL`条件时，MySQL可能忽略索引。 - **原因**：NULL值在索引中通常不存储或单独处理，优化器可能判断全表扫描效率更高[^3]。 - **示例**： ```sql -- 创建索引 CREATE INDEX idx_email ON users(email); -- 以下查询可能索引失效：尤其在NULL值较多时 SELECT * FROM users WHERE email IS NOT NULL; ``` - **解决方案**：结合其他条件缩小范围，或确保索引覆盖查询（Covering Index）。 #### 其他潜在失效场景 - **OR条件导致索引合并失败**：当OR连接的条件涉及多个索引列时，优化器可能放弃索引扫描。 - **数据分布不均**：如果索引列值重复率高（如性别列），优化器可能选择全表扫描（Cardinality过低问题）。 - **索引列使用IN子句或范围查询**：大范围的IN列表或范围查询（如BETWEEN）可能降低索引效率，但不一定完全失效[^2]。 ### 总结 MySQL索引失效主要由查询写法不当或数据类型问题引发，核心是破坏了索引的**有序性**或**前缀匹配**特性。优化时，应确保WHERE条件直接使用索引列原值，并遵循索引设计原则。实际诊断可通过EXPLAIN命令分析执行计划（如查看type列是否为index或range）。