索引失效的情况有哪些

发布网友 发布时间：2022-05-02 19:14

我来回答

共4个回答

懂视网 时间：2022-05-02 23:35

哈希索引的缺点：
　　优点：在寻找值时哈希表效率极高，如果使用哈希索引，对于比较字符串是否相等的查询能够极快的检索出的值。
　　缺点：哈希表是无顺的数据结构，对于很多类型的查询语句哈希索引都无能为力。比如无法查询所有小于40岁的员工。因为哈希表只适合查询键值对-也就是说查询相等的查询（例：like “WHERE name = ‘Jesus’）。哈希表的键值映射也暗示其键的存储是无序的。这就是为什么哈希索引通常不是数据库索引的默认数据结构-因为在作为索引的数据结构时，其不像B-Tree那么灵活。

首先先通过4个问题介绍一下索引：

1、为什么要给表加上主键？

2、为什么加索引后会使查询变快？

3、为什么加索引后会使写入、修改、删除变慢？

4、什么情况下要同时在两个字段上建索引？

问题一：
　　数据库索引通常使用的数据结构是「平衡树」(非二叉)，也就是btree或者 b+tree。有的数据库也使用哈希表作为索引的数据结构，然而， 主流的数据库管理系统RDBMS都是把平衡树当做数据表默认的索引数据结构的。我们平时建表的时候都会为表加上主键，在某些关系数据库中，如果建表时不指定主键，数据库会拒绝建表的语句执行。 事实上， 一个加了主键的表，并不能被称之为「表」。一个没加主键的表，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐，跟我们认知中的「表」很接近。如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是上面说的「平衡树」结构，换句话说，就是整个表就变成了一个索引。也就是所谓的「聚集索引」。这就是为什么一个表只能有一个主键，一个表只能有一个「聚集索引」，因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。

问题二：
　　如图：其中树的所有结点（底部除外）的数据都是由主键字段中的数据构成，也就是通常我们指定主键的id字段。最下面部分是真正表中的数据。

　　假如我们执行一个SQL语句：select * from table where id = 1256;首先根据索引定位到1256这个值所在的叶结点，然后再通过叶结点取到id等于1256的数据行。 如果树一共有三层，从根节点至叶节点只需要经过三次查找就能得到结果。 

　　假如一张表有一亿条数据 ，需要查找其中某一条数据，按照常规逻辑， 一条一条的去匹配的话， 最坏的情况下需要匹配一亿次才能得到结果，用大O标记法就是O(n)最坏时间复杂度，这是无法接受的，而且这一亿条数据显然不能一次性读入内存供程序使用， 因此， 这一亿次匹配在不经缓存优化的情况下就是一亿次IO开销，以现在磁盘的IO能力和CPU的运算能力， 有可能需要几个月才能得出结果 。如果把这张表转换成平衡树结构（一棵非常茂盛和节点非常多的树），假设这棵树有10层，那么只需要10次IO开销就能查找到所需要的数据， 速度以指数级别提升，用大O标记法就是O(log n)，n是记录总树，底数是树的分叉数，结果就是树的层次数。
换言之，查找次数是以树的分叉数为底，记录总数的对数，这里的结果从亿降到了个位数。因此，利用索引会使数据库查询有惊人的性能提升。

问题三：
　　然而，事物都是有两面的，索引能让数据库查询数据的速度上升，而使写入数据的速度下降，因为平衡树这个结构必须一直维持在一个正确的状态，增删改数据都会改变平衡树各节点中的索引数据内容，破坏树结构。 因此，在每次数据改变时， DBMS必须去重新梳理树（索引）的结构以确保它的正确，这会带来不小的性能开销， 也就是为什么索引会给查询以外的操作带来副作用的原因。

问题四：
　　讲完聚集索引，接下来聊一下非聚集索引，也就是我们平时经常提起和使用的常规索引。
　　非聚集索引和聚集索引一样， 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段， 假如给user表的name字段加上索引，那么索引就是由name字段中的值构成，在数据改变时，DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引，那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。

　　每次给字段建一个新索引，字段中的数据就会被复制一份出来，用于生成索引。因此，给表添加索引，会增加表的体积，占用磁盘存储空间。非聚集索引和聚集索引的区别在于，通过聚集索引可以查到需要查找的数据，而通过非聚集索引可以查到记录对应的主键值id，再使用主键的值通过聚集索引查找到需要的数据。不管以任何方式查询表，最终都会利用主键通过聚集索引来定位到数据，聚集索引（主键）是通往真实数据所在的唯一路径。然而，有一种例外可以不使用聚集索引就能查询出所需要的数据，这种非主流的方法 称之为「覆盖索引」查询，也就是平时所说的复合索引或者多字段索引查询。文章上面的内容已经指出，当为字段建立索引以后，字段中的内容会被同步到索引之中，如果为一个索引指定两个字段，那么这个两个字段的内容都会被同步至索引之中。

先看下面这个SQL语句：
//建立索引
create index index_birthday on user_info(birthday);

//查询生日在1991年11月1日出生用户的用户名
select user_name from user_info where birthday = ‘1991-11-1‘

这句SQL语句的执行过程如下：

首先，通过非聚集索引index_birthday查找birthday等于1991-11-1的所有记录的主键ID值
然后，通过得到的主键ID值执行聚集索引查找，找到主键ID值对应的真实数据（数据行）存储的位置
最后，从得到的真实数据中取得user_name字段的值返回，也就是取得最终的结果

//我们把birthday字段上的索引改成双字段的覆盖索引
create index index_birthday_and_user_name on user_info(birthday, user_name);

这句SQL语句的执行过程就会变为：

通过非聚集索引index_birthday_and_user_name查找birthday等于1991-11-1的叶节点的内容，然而，叶节点中除了有user_name表主键ID的值以外，user_name字段的值也在里面。因此不需要通过主键ID值的查找数据行的真实所在，直接取得叶节点中user_name的值返回即可。
通过这种覆盖索引直接查找的方式，可以省略不使用覆盖索引查找的后面两个步骤，大大的提高了查询性能。

什么情况下需要添加索引：

　　基本原则是只如果表中某列在查询过程中使用的非常频繁，那就在该列上创建索引。 

哪些情况下索引会失效：

1.where子句的查询条件里有where（column！=XXX），MySQL将无法使用索引；
2.where子句的查询条件中使用了函数，MySQL将无法使用索引；
3.如果条件有or，即使其中有条件带索引也不会使用(这也是为什么建议少使用or的原因)，如果想使用or，又想索引有效，只能将or条件中的每个列加上索引；
4.对于多列索引，不是使用的第一部分，则不会使用索引；
5.like查询以%开头；
6.如果列类型是字符串，那一定要在条件中数据使用引号，否则不使用索引；
7.如果MySQL估计使用全表扫描要比索引快，则不使用索引。

参考：https://www.cnblogs.com/aspwebchh/p/6652855.html

https://blog.csdn.net/yanshuanche3765/article/details/80064405

数据库----问题1:数据库索引底层是怎样实现的，哪些情况下索引会失效？

标签：asp   之间   删除   bms   无法   tree   ima   建立   提高   

热心网友 时间：2022-05-02 20:43

原因有如下：

1、最佳左前缀原则——如果索引了多列，要遵守最左前缀原则。指的是查询要从索引的最左前列开始并且不跳过索引中的列。

2、不在索引列上做任何操作，会导致索引失效而导致全表扫描。

3、存储引擎不能使用索引中范围条件右边的列，范围之后索引失效。这写条件判断最后放到后面，先定位到小的范围再开始。

4、mysql使用不等于(!= 或者<>)的时候，无法使用索引，会导致索引失效。

5、mysql中使用is not null 或者 is null会导致无法使用索引。

6、mysql中like查询是以%开头，索引会失效变成全表扫描，覆盖索引。

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

8、如果mysql使用全表扫描要比使用索引快,则不会使用到索引。

注意事项

1、索引列有函数处理或隐式转换，不走索引。

2、索引列倾斜，个别值查询时，走索引代价比走全表扫描高，所以不走索引。

3、索引列没有* not null，索引不存储空值，如果不*索引列是not null，oracle会认为索引列有可能存在空值，所以不会按照索引计算。

热心网友 时间：2022-05-02 22:01

索引失效的情况有：

1、如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。

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

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

3、like查询是以%开头。

索引

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

热心网友 时间：2022-05-02 23:36