MYSQL索引失效

李歘歘

已于 2022-11-28 16:50:56 修改

阅读量2.1k

点赞数

分类专栏：好好学习 # 数据库文章标签： mysql java 数据库

于 2022-01-29 21:24:40 首次发布

本文链接：https://blog.csdn.net/qq_42410605/article/details/122676715

版权

好好学习同时被 2 个专栏收录

56 篇文章 4 订阅

订阅专栏

数据库

23 篇文章 2 订阅

订阅专栏

博客主页：🏆看看是李XX还是李歘歘 🏆

🌺每天不定期分享一些包括但不限于计算机基础、算法、后端开发相关的知识点，以及职场小菜鸡的生活。🌺

💗点关注不迷路，总有一些📖知识点📖是你想要的💗

⛽️今天的内容是 MYSQL索引失效 ⛽️💻💻💻

1. MySQL 索引使用有哪些注意事项呢？

哪些情况索引会失效，索引不适合哪些场景以及索引规则

索引哪些情况会失效

like通配符可能导致索引失效。

【当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。】

explain select * from table_name where a like 'As%'; //前缀都是排好序的，走索引查询
explain select * from table_name where  a like '%As'//全表查询——失效
explain select * from table_name where  a like '%As%'//全表查询——失效

在索引列上使用mysql的内置函数，索引失效。

查询条件中对索引字段使用函数，就会导致索引失效。从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。

// name 为二级索引
explain select * from t_user where length(name)=6;    // 全表扫描
//使用下面的语句添加函数索引后，上面的sql则可以使用索引
alter table t_user add key idx_name_length ((length(name)));

对索引列运算（如，+、-、*、/），索引失效。

查询条件中对索引进行表达式计算，也是无法走索引的。

explain select * from t_user where id + 1 = 10; //全表扫描
explain select * from t_user  where id  = 10 - 1;    //索引扫描

查询条件包含or，可能导致索引失效

在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

explain select * from t_user where id = 1 or age = 18; // id 是主键，age 是普通列——全表扫描

联合索引，查询时需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则索引失效。

有一个比较特殊的查询条件：where a = 1 and c = 3 ，符合最左匹配吗？

这种其实严格意义上来说是属于索引截断，不同版本处理方式也不一样。

MySQL 5.5 的话，前面 a 会走索引，在联合索引找到主键值后，开始回表，到主键索引读取数据行，然后再比对 c 字段的值。

从 MySQL5.6 之后，有一个索引下推功能，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

大概原理是：截断的字段会被下推到存储引擎层进行条件判断（因为 c 字段的值是在 (a, b, c) 联合索引里的），然后过滤出符合条件的数据后再返回给 Server 层。由于在引擎层就过滤掉大量的数据，无需再回表读取数据来进行判断，减少回表次数，从而提升了性能。

比如下面这条 where a = 1 and c = 0 语句，我们可以从执行计划中的 Extra=Using index condition 使用了索引下推功能。

为什么联合索引不遵循最左匹配原则就会失效？

原因是，在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。

也就是说，如果我们想使用联合索引中尽可能多的列，查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索，肯定无法走索引。

还有一些其他情况也可能导致索引无效：

索引字段类型不同——如何字段类型是字符串，where时一定用引号括起来，否则索引失效，也就是说索引是string，查询时候使用int。
索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。
索引字段上使用is null， is not null，可能导致索引失效。用or连接两个索引的is null、is not null可能会索引失效。
左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。
mysql估计使用全表扫描要比使用索引快,则不使用索引。
列对比——id字段本身是有主键索引的，同时height字段也建了普通索引的，并且两个字段都是int类型，类型是一样的。如果把两个单独建了索引的列，用来做列对比时索引会失效。

参考：后端程序员必备：索引失效的十大杂症

其他知识点

索引不适合哪些场景

数据量少的不适合加索引
更新比较频繁的也不适合加索引
区分度低的字段不适合加索引（如性别）

索引的一些潜规则

覆盖索引
回表
索引数据结构（B+树）
最左前缀原则
索引下推

InnoDB引擎中的索引策略，了解过吗？

覆盖索引
最左前缀原则
索引下推

索引下推优化是 MySQL 5.6 引入的，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。可以看一下什么是索引下推？ - 知乎

这篇文章非常不错，大家去看一下吧：聊一聊 InnoDB 引擎中的这些索引策略

Hash索引和B+树区别是什么？你在设计索引是怎么抉择的？

B+树可以进行范围查询，Hash索引不能。
B+树支持联合索引的最左侧原则，Hash索引不支持。
B+树支持order by排序，Hash索引不支持。
Hash索引在等值查询上比B+树效率更高。
B+树使用like 进行模糊查询的时候，like后面（比如%开头）的话可以起到优化的作用，Hash索引根本无法进行模糊查询。

索引有哪几种类型？

主键索引: 索引列不允许重复，不允许为NULL，一个表只能有一个主键。
唯一索引: 索引列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
普通索引: 基本的索引类型，没有唯一性的限制，索引列的值可以不唯一，允许为NULL值。
全文索引：是目前搜索引擎使用的一种关键技术，对文本的内容进行分词、搜索。
覆盖索引：查询列要被所建的索引覆盖，不必读取数据行
组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并

创建索引有什么原则呢？

最左前缀匹配原则
频繁作为查询条件的字段才去创建索引
频繁更新的字段不适合创建索引
索引列不能参与计算，不能有函数操作
优先考虑扩展索引，而不是新建索引，避免不必要的索引
在order by或者group by子句中，创建索引需要注意顺序
区分度低的数据列不适合做索引列(如性别）
定义有外键的数据列一定要建立索引。
对于定义为text、image数据类型的列不要建立索引。
删除不再使用或者很少使用的索引

创建索引的注意事项

1.选择合适的字段创建索引：

不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。
被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

2.被频繁更新的字段应该慎重建立索引。

虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

3.尽可能的考虑建立联合索引而不是单列索引。

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

4.注意避免冗余索引 。

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

5.考虑在字符串类型的字段上使用前缀索引代替普通索引。

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。