数据库索引失效问题

MySQL索引的概念：

通俗的说，索引的作用就像目录一样，是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。索引中包含由表或视图中的一列或多列生成的键。这些键存储在一个结构（BTree）中，使SQL可以快速有效地查找与键值关联的行。

 
为什么要创建索引呢？

这是因为，创建索引可以大大提高系统的性能。

1. 通过创建唯一性索引，可以保证给数据库表中每一行数据的唯一性。
2. 可以大大加快数据检索速度，也就是创建索引的最主要原因
3. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义
4. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
5. 通过使用检索，可以在查询的过程中，使用优化隐藏器，提高系统的性能

为什么不每一列都增加一个索引呢？

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果简历聚簇索引，那么需要的空间就会更大
3. 当对表中的数据进行增删改的时候，索引也要动态的维护，索引数量少的时候，就降低了数据的维护成本

索引的种类：

索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引

唯一索引

唯一索引是不允许其中任何两行具有相同索引值的索引。当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。

主键索引

数据库表经常有一列或多列组合，其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

聚集索引

在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。聚集索引和非聚集索引的区别，如字典默认按字母顺序排序，读者如知道某个字的读音可根据字母顺序快速定位。因此聚集索引和表的内容是在一起的。如读者需查询某个生僻字，则需按字典前面的索引，举例按偏旁进行定位，找到该字对应的页数，再打开对应页数找到该字。这种通过两个地方而查询到某个字的方式就如非聚集索引。

数据库中索引什么时候会失效呢?

首先，所谓失效。并不真的就是这个索引被删除了。而是在某些情况下，DBMS不会检索索引列表了。执行速度和没有这个索引时的速度一样。但是再执行另外的一条语句。同样的索引又正常起作用。所以索引的失效是针对某条sql语句，某个查询条件的，而不是针对索引本身的。

　　哪类语句执行时索引不起作用呢。总结如下：

1.索引字段进行判空查询时。也就是对索引字段判断是否为NULL时。语句为is null 或is not null。

select * from SoftWareDetailInfo where CreateTime is null

此时就不检索time字段上的索引表了。也就是索引在这条语句执行时失效了。接着再执行

select * from SoftWareDetailInfo where CreateTime = '2015-04-11 00:00:00'

此时就会检索索引表了。索引又起作用了。

 

2. like查询是以%开头

select * from SoftWareDetailInfo where SoftUseLine like '%OQC%'

 

3. 判断索引列是否不等于某个值时。‘!= ,< >’操作符。

select * from SoftWareDetailInfo where SoftUseLine != 0

　　

4. 对索引列进行运算。这里运算包括+-*/等运算。也包括使用函数。比如：

select * from SoftWareDetailInfo where SoftUseLine +0= 0

　　此时索引不起作用。

select * from SoftWareDetailInfo where count(SoftUseLine) = 0

　　此时索引也不起作用。

　　也就是说如果不是直接判断索引字段列，而是判断运算或其它函数处理后的索引列索引均不起作用。

 

5.在where子句中使用or来连接条件，因为如果两个字段中有一个没有索引的话，引擎就会放弃索引。

SELECT id FROM table WHERE num1 = 0 OR num2 = 1

 

6.可以考虑使用between，但是只能是连续的数值

SELECT id FROM table WHERE num BETWEEN 0 AND 1

 

7.避免在where子句中使用in关于网上说的MySQL在使用in不走索引的问题,严谨的来说的话分为俩种情况

走索引的

 

SELECT id FROM table WHERE num IN (1)

 

不走索引的

SELECT id FROM table WHERE num IN (1,2)
其实在in里面,如果只有一个值的话是等价于num = 1的

 

8.避免在where子句中=的左边使用表达式操作或者函数操作

表达式

SELECT id FROM table WHERE num / 2 = 1

 

函数操作

SELECT id FROM table WHERE SUBSTRING(name,1,2) = 'wise'