前言
今天来讲一讲 MySQL 索引的高频面试题。
主要是针对前一篇文章 MySQL索引入门(一文搞定)进行查漏补缺,建议大家看完前一篇再看这一篇。
什么时候需要(不需要)使用索引?
我们先来看看索引的优缺点吧。
优点:可以快速查询的效率,查询是在增删改查中最经常使用。
缺点:创建索引是要用到物理空间的。其次维护索引,当数据库进行增删改的时候,索引也要动态的进行变化。这样子的话过多的索引会影响到数据库的增删改性能。
从优缺点我们来切入什么需要使用索引,什么时候不需要索引
什么时候需要使用索引?
- 当字段经常出现在 where 当做条件中,我们可以考虑根据字段添加索引。
- 当字段经常出现在 order By 和 Group By 时,我们可以考虑根据字段添加索引。
- 当字段频繁的查询,而少更新时,我们可以考虑根据字段添加索引。
什么时候不需要使用索引?
-
反之,将字段频繁的更新,很少查询时,不需要使用索引,因为索引的意义就是加速查询,既然用不上就不需要创建了。
-
当数据量少时,不用因为查询性能慢而烦恼时,就不需要使用索引。
-
当字段经常涉及计算、函数等操作时,不需要使用索引,因为这会造成索引失效。
-
当有 where,order by ,group by 等关键词使用不到的字段,不需要使用索引,因为索引的价值是快速定位,这样子的话提现不出价值而且索引会占用物理空间。
-
当有字段存储了大量的重复数据时,不需要创建索引,比如性别,只有男/女两种值,这时候的索引是起不到作用的。因为MySQL 有个优化器会在执行前将 SQL 进行优化,如果有太多重复的数据,优化器默认是不会使用索引的。
有哪些优化索引的方法
前缀索引优化
前缀索引优化就是当类型为字符串(比如:varchar)的字段,该字段过长时,我们可以使用前缀索引就是将该字段的前几个字符作为索引。
这样子既可以节省空间,又可以起到索引优化查询的性能。
当然这种也有缺点。
- order by 和 group by 无法使用前缀索引,因为order by 是根据整个字段进行排序的,而前缀索引只有字段的前几个字符。
- 前缀索引无法起到索引覆盖优化。
覆盖索引优化
索引覆盖优化一般是使用联合索引,就是B+树的叶子结点是包含索引(key)和数据(data)的。当使用联合索引时
,如果 B+ 树的叶子节点的值包含了我们需要查询的值,那么就不用进行回表查询了。回表查询就是根据这次查询到的主键索引再进行一次 B+ 树查询。
比如学生表,我们要查询出studentName,studentPhone,其中 card(身份证卡号)是唯一值,我们创建联合索引(card、studentName、studentPhone)。然后我们使用SQL语句进行查询
select card,studentName, studentPhone from student where card = '445122335464654654463'
这时候,我们在使用 SQL 语句查询的时候,在叶子节点就会包含 card ,studentName,studentPhone,三个值。索引包含需要的时候,直接返回数据,不会造成回表查询,这种叫做覆盖索引。
索引失效场景
- 当like关键字进行左模糊(%xx)查询和左右模糊(%xx%)查询时会失效。因为索引是整个值,进行左模糊的话无法匹配出来。
- 当索引字段进行计算,函数,类型转化时,会出现索引失效。
- 当联合索引不符合最左匹配原则时会出现索引失效。
- where 条件字段中,如果 OR 前字段是索引列,后字段不是,会索引失效。
总结
总的来说,我们只要符合索引的优缺点就可以知道索引该如何使用拉。优点:快速查询,缺点:浪费空间,影响数据更新性能。
有启发点个赞 🌹
我是小辉,正在应届找工作中,第二版修改于20240612