欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

面试-4.索引失效问题

程序员文章站 2024-03-16 21:42:46
...

索引失效的情况:###

1.有or必全有索引;

  • 如果条件中有or,即使其中有部分条件带索引也不会使用(这也是为什么尽量少用or的原因)。
    注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引

2.复合索引未用左列字段;(索引的最左前缀原则)

  • B+ 树这种索引结构,可以利用索引的“最左前缀”,来定位记录。假设我们有(name,age)这样一个联合索引。

面试-4.索引失效问题
可以看到,索引项是按照索引定义里面出现的字段顺序排序的。
     因为可以支持最左前缀,所以当已经有了 (a,b) 这个联合索引后,一般就不需要单独在 a 上建立索引了。因此,第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。那么,如果既有联合查询,又有基于 a、b 各自的查询呢?查询条件里面只有 b 的语句,是无法使用 (a,b) 这个联合索引的,这时候就不得不维护另外一个索引,也就是说你需要同时维护 (a,b)、(b) 这两个索引。

3.like以%开头;

如果like查询是以%开头,那么索引将失效。如果是 '查询内容%‘ ,这种形式,索引不会失效。

4.产生了隐式类型转换;

select * from test where userId=123456;

userId的字段类型是 varchar(16),而输入的参数却是整型,所以需要做类型转换。此时就会需要走全表扫描,用不上索引。因为MySQL 中,字符串和数字做比较的话,是将字符串转换成数字。所以语句会变成下面这样,相当于给了索引上加了函数。

select * from test where  CAST(userIdAS signed int) = 123456;

5.where中索引列有运算;

修改前:
 select *
  from app a
 where 
   (a.END_DATE - sysdate) < 7
   and (a.END_DATE - sysdate) >= 0

修改后:

select *
  from app a
 where 
   a.END_DATE < sysdate + 7
   and a.END_DATE >= sysdate

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

select * from t_user where ABS(account) = 1;

可以考虑修改为

select * from t_user where (account = 1 or account = -1);

7.产生了隐式字符编码转换;

a 表的编码是 utf8mb4, b 表是utf8。

select b.* from a, b where a.userId = b.userId and a.id=2; 

因为这两个表的字符集不同,一个是 utf8,一个是 utf8mb4,所以做表连接查询的时候用不上关联字段的索引。

MySQL 内部的操作是,先把 utf8 字符串转成 utf8mb4 字符集,再做比较。这个设定很好理解,utf8mb4 是 utf8 的超集。连接过程中要求在被驱动表的索引字段上加函数操作,是直接导致对被驱动表做全表扫描的原因。

8.如果mysql觉得全表扫描更快时(数据少);

    选择索引是优化器的工作。而优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。在数据库里面,扫描行数是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的 CPU 资源越少。MySQL 在真正开始执行语句之前,并不能精确地知道满足这个条件的记录有多少条,而只能根据统计信息来估算记录数。在频繁修改表数据的场景下,可能会出现扫描行数估计不准确的情况,可以使用analyze table t 命令,可以用来重新统计索引信息。在实践中,如果发现 explain 的结果预估的 rows 值跟实际情况差距比较大,可以采用这个方法来处理。

索引可能失效的情况:

1.唯一性差;
2.频繁更新的字段不用(更新索引消耗);
3.where中不用的字段;
4.索引使用<>时,效果一般;

什么情况下不推荐使用索引?###

  1. 数据唯一性差(一个字段的取值只有几种时)的字段不要使用索引

比如性别,只有两种可能数据。意味着索引的二叉树级别少,多是平级。这样的二叉树查找无异于全表扫描。

  1. 频繁更新的字段仔细考虑使用索引

表中数据的频繁变化导致索引也频繁变化,增大数据库工作量,降低效率。.

  1. 字段不在where语句出现时不要添加索引,如果where后含IS NULL /IS NOT NULL/ like ‘%输入符%’等条件,不建议使用索引

只有在where语句出现,mysql才会去使用索引。

  1. where 子句里对索引列使用不等于(<>),使用索引效果一般