关于索引

排序与索引

SQL中很多操作需要排序，最明显的就是ORDER BY 了。ORDER BY 通常会有两种实现方法，一个是利用有序索引自动实现，也就是说利用有序索引的有序性就不再另做排序操作了。另一个是把结果选好之后再排序。用有序索引这种，当然是最快的，不过有一些限制条件，当order by 字段出现在where条件中时，才会利用索引而无需排序操作。其他情况，order by不会出现排序操作。更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作。

为什么只有order by 字段出现在where条件中时,才会利用该字段的索引而避免排序。这要说到数据库如何取到我们需要的数据了。

一条SQL实际上可以分为四步。

1.寻找数据（根据WHERE条件）

2.得到数据（select中的列）

3.处理数据 （对需要的数据进行处理，如GROUP BY 、ORDER BY、 SUM 、union等）

4.返回处理后的数据

事实上，索引只在第一步得到数据，也就是查找数据时有用。也就是，只有当寻找数据时才会访问索引，当处理数据时，并不会访问索引。但是为什么索引会使ORDER BY、GROUP BY、UNION等变快呢？显然ORDER BY、GROUP BY、UNION都是需要排序的操作，如果之前第二步得到的结果集已经是有序的了，那么就不需要排序了。可以大大提高效率。可是如何知道第二步时结果集是有序的呢？就是看在第一步时是否利用了要排序字段的索引。那么什么样的情况下会利用索引呢。也就是第一步根据WHERE条件寻找满足条件数据时，是否利用了索引。一般来说，当ORDER BY中的字段在WHERE条件中且该字段有索引时，ORDER BY或者或其他需要排序的字段（GROUP BY、UNION等）就不需要排序了。

函数与索引

为什么当WHERE条件中的左边列利用了函数时，就不会利用索引了呢？

比如一个学生表有姓名、学号、性别，出生日期等列。这些信息放在磁盘的某个地方。这里我暂时称为表A。现在为了查询方便建立出生日期列的索引。这就相当于，建立了只有两列表，这两列为出生日期和该出生日期值对应的完整记录在磁盘（表A存储信息的位置）中位置。并且这个表是根据出生日期排列的，它也放在磁盘的某个地方。这里我称它为表B。

当我们查询学生信息时，如果WHERE条件中有出生日期的条件，那么会先查询这个表B。比如条件为WHERE 出生日期 >1990.1.1，那么就先查询表B中那些行数据的出生日期大于这个值。然后看这些数据对应的完整记录的位置。然后从这些位置读取需要的数据。这样就利用索引来提高了查询速度。

当我们在WHERE条件的左边使用了函数时，比如where  substr('出生日期',0,4) > 1990时，我们知道出生日期建立了一个索引，也就是表B。我们在表B中取出出生日期的值，然后经过函数运算，再看是否满足条件。注意，经过函数运算后的值我们不能保证是有序的。所以，必须B表中每一列都要经过函数运算再看是否满足条件。这样表B就进行了全表扫描。可是这就失去了利用索引的有序性来筛选提高查询速度的效果，和直接扫描表A得到数据相比，会更慢。有一个经验，如果索引的筛选率小于30%，那么不走索引就更快了。

假如我们将条件变成where  出生日期 >1990.12.31，那么就可以利用索引了。

所以，尽量避免在WHERE条件表达式的左边使用函数。

如果必须使用函数，那么可以考虑建立函数索引。比如，之前的where  substr('出生日期',0,4) > 1990。如果建立了substr('出生日期',0,4)这个函数索引，也就是建立了一个表C，其中有两列信息，一列是substr('出生日期',0,4)的值，一列是出生日期对应的完整记录的位置。这样就可以利用索引了。