索引失效的7中情况

最新推荐文章于 2024-12-09 17:55:08 发布
转载 最新推荐文章于 2024-12-09 17:55:08 发布 · 256 阅读 · 0 ·
CC 4.0 BY-SA版权
原文链接：https://blog.youkuaiyun.com/u010675669/article/details/108797691
文章标签：

#mysql #索引

本文介绍了几种常见的SQL查询性能优化技巧，包括确保OR条件两侧都有索引、避免在复合索引查询时跳过左侧字段、避免以%开头的LIKE查询、保持数据类型一致性、不在索引列上进行运算及函数调用等。此外还提到了MySQL在某些情况下会选择全表扫描而非使用索引。

目录

1.  有or必全有索引

2.  复合索引未用左列字段

3.  like以%开头

4.  需要类型转换,比如字符串没有加‘’或“”(mysql5.7会直接报错，如果不加‘’)

​         5.  where中索引列有运算

​         6.  where中索引列使用了函数

​         7.  如果mysql觉得全表扫描更快时（数据少的表）

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) COLLATE utf8mb4_bin NOT NULL,
  `email` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `phone` varchar(11) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name_email_phone` (`name`,`email`,`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
 
SET FOREIGN_KEY_CHECKS = 1;

1.有or必全有索引

 

 


2.复合索引未用左列字段


3.like以%开头

 

 

 

 

4.需要类型转换,比如字符串没有加‘’或“”(mysql5.7会直接报错，如果不加‘’)

5.where中索引列有运算

6.where中索引列使用了函数

7.如果mysql觉得全表扫描更快时（数据少的表）

 

 

 

 

 

 

 

【Mysql索引在什么情况下会失效？】
Roc的博客
02-17 420
MySQL索引在多种情况下可能会失效，导致查询性能下降。
数据库索引失效的11种情况
qq_39964887的博客
06-26 1098
数据库索引失效的11种情况，大家在软件开发中应尽量避免
MySQL——什么情况下索引会失效
Lemon_man_的博客
05-26 688
4、索引字段未添加单引号（会在类型转换时报错）5、模糊查询‘%’放在了索引字段的左边。2、范围查询右边的列不能使用索引。1、不符合最左前缀法则。3、索引列添加了运算符。
【MySQL 进阶之路】索引失效的11种情况
ajsbxi的博客
12-09 3088
在MySQL的查询优化中，索引是一项至关重要的技术，它能够大大提升数据检索的效率。本文将讨论这11种常见情况，帮助开发者更好地理解索引的使用及优化。
慢查询-索引失效的7种情况
liehen2046的专栏
06-19 786
简述 什么时候没用 1.有or必全有索引;2.复合索引未用左列字段;3.like以%开头;4.需要类型转换;5.where中索引列有运算;6.where中索引列使用了函数;7.如果mysql觉得全表扫描更快时（数据少）; 什么时没必要用 1.唯一性差;2.频繁更新的字段不用（更新索引消耗）;3.where中不用的字段;4.索引使用<>时，效果一般; 详述（转） 索...
索引失效的7种情况
weixin_30824277的博客
06-19 1349
简述 什么时候没用 1.有or必全有索引;2.复合索引未用左列字段;3.like以%开头;4.需要类型转换;5.where中索引列有运算;6.where中索引列使用了函数;7.如果mysql觉得全表扫描更快时（数据少）; 什么时没必要用 1.唯一性差;2.频繁更新的字段不用（更新索引消耗）;3.where中不用的字段;4.索引使用<>时，效果一般; 详述（转） 索引并不是时...
导致MySQL索引失效的一些常见写法总结
09-14
在MySQL数据库中，索引是一种重要的优化手段，它能够显著...以上就是关于导致MySQL索引失效的一些常见情况及其避免方法。在实际工作中，我们需要结合具体业务场景和查询需求，合理设计和使用索引，以提高数据库性能。
MySQL索引失效的几种情况汇总
12-14
下面将详细探讨几种常见的索引失效情况及其原因。 首先，索引不存储`NULL`值。在MySQL中，单列索引不会存储`NULL`值，而复合索引则不存储所有列都是`NULL`的值。这是因为`NULL`值在比较操作中具有特殊性，不适用于...
数据库索引失效的几种情况
12-14
然而，尽管索引在多数情况下能提升查询效率，但存在一些情况可能导致索引失效或无法充分利用： 1. **OR条件**：当查询条件中包含多个字段使用OR操作符时，即使其中某些字段有索引，MySQL也可能不会使用这些索引。...
MySQL中有哪些情况下数据库索引会失效详析
09-09
本文将深入探讨MySQL中导致数据库索引失效的一些常见情况。 首先，我们来看一下`EXPLAIN`命令的用法。`EXPLAIN`是MySQL提供的一种查询分析工具，它可以显示MySQL如何执行SELECT语句，包括哪些索引被使用，数据是...
索引失效的七种情况
Ecloss
08-30 1万+
以上这些情况都可能导致数据库查询时无法有效地使用索引，从而影响查询性能。为了避免索引失效，需要优化查询语句，合理设计索引，尽量避免上述情况的出现。
索引失效的7个原因
热门推荐
王飞的博客
04-05 1万+
实际工作以及面试中，应该经常会遇到SQL相关的问题，而这些问题中，索引失效的场景又是一个常客。下面总结一下索引失效的场景，一共7种，索引失效的原因逃不过这7个。
索引失效的几种情况
small_engineer的博客
04-02 3174
索引失效案例 表结构如下： 全值匹配我最爱 没有创建任何索引的情况下： explain select * from student_info where student_id=25 and name='jksf'; 发现它没有使用任何索引 给前面一个字段建立索引 create index X on student_info(student_id); explain select * from student_info where student_id=25 and name='jksf'; 发现它使
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
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值