【sql server】索引详解

索引可以理解为一种特殊的目录结构。

sql server提供两种索引形式： 聚集索引和非聚集索引。

怎么理解这两种形式。

拿我们常用的字典举例来说， 一个字典好比数据库中的一个表。那么当我们想从字典中查某个汉字比如“璐”的时候， 我们会从目录里面翻到L开头的部分， 在这一部分中去查找“璐”这个字。 这种方式就属于通过聚集索引查询数据。

那么当你对一个汉字不认识或者不知道叫什么时候， 比如“钰”， 那么就得通过这个汉字的偏旁部首“钅”通过部首目录在部首目录中找到这个字， 然后根据这个字对应的实际页码， 去字典中查到这个字。但是“钰”这个字在部首目录的上下数据可能对应的页码， 一个在天南，一个在海北。 这种方式的查询就属于非聚集索引查询。

上面的栗子只是辅助我们对索引查询的作用有一个大致的概念。然而我们在实际对数据库进行操作的时候， 实际上要有充分的经验才能判断出如何使用索引， 使我们的查询速度得到显著地改善。

对于同一个表， 只允许有一个聚集索引。 因为这就好比字典的目录， 只能通过一种顺序进行排序。所以聚集索引显得很珍贵， 在实际工作和项目中， 遇到什么样case使用什么样的索引， 可以参考下面表：

动作描述	使用聚集索引	使用非聚集索引
列经常被分组排序	应	应
返回某范围内的数据	应	不应
一个或极少不同值	不应	不应
小数目的不同值	应	不应
大数目的不同值	不应	应
频繁更新的列	不应	应
外键列	应	应
主键列	应	应
频繁修改索引列	不应	应

下面我们会通过实际情况，对照表中的情况进行索引设计练习。

1. “主键 就是聚集索引。”

虽然SQL server默认就是对主键添加的聚集索引。但是我认为这是对资源的一种浪费。

项目中设计表结构的时候， 通常会为表添加一个ID列。以方便于区分每条数据。这种ID列很多都是自增长的，并且步长为1。此时如果将这个列设置为主键， SQL server就会将这个列设置聚集索引。 这样做的好处就是可以让数据在数据库中按照ID进行物理排序，但是这么做的意义其实不会很大。

我们之所以要对表设计一个聚集索引， 目的就是为了在查询这个表的时候能够迅速缩小查询范围， 而不必对全表进行扫描。而在实际的应用当中， 我们的id一般是自动生成的，所以很难在实践中用ID号去查询。这就让ID这个主键作为聚焦索引成为一个浪费。

如果可以根据项目的需求， 比如项目是一个办公自动化系统， 系统首页需要显示的数据比如用户的文件， 会议。这种情况下对数据进行查询都离不开字段是“日期”以及“用户名”， 通常，办公自动化的首页会显示每个用户尚未签收的文件或会议。虽然我们的where语句可以仅仅限制当前用户尚未签收的情况，但如果系统已建立了很长时间，并且数据量很大，那么，每次每个用户打开首页的时候都进行一次全表扫描，这样做意义是不大的，绝大多数的用户1个月前的文件都已经浏览过了，这样做只能徒增数据库的开销而已。事实上，我们完全可以让用户打开系统首页时，数据库仅仅查询这个用户近3个月来未阅览的文件，通过“日期”这个字段来限制表扫描，提高查询速度。如果办公自动化系统已经建立的2年，那么首页显示速度理论上将是原来速度8倍，甚至更快。

下面是我在测试环境中测试的case

从建表的语句中，我们可以看到这个有着1000万数据的表中fariqi字段有5003个不同记录。在此字段上建立聚合索引是再合适不过了。在现实中，我们每天都会发几个文件，这几个文件的发文日期就相同，这完全符合建立聚集索引要求的：“既不能绝大多数都相同，又不能只有极少数相同”的规则。由此看来，我们建立“适当”的聚合索引对于我们提高查询速度是非常重要的。

2. “只要建立索引就能显著提高查询速度。”这个说法也不对

应该是在合适的字段上加上合适的索引。

3. “把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度”

上面提到进行数据查询时候都离不开的字段是：“日期”和“用户名”。 既然这两个字段都很重要， 我们可以合并这两个字段建立一个“复合索引（Compound index）”

待续。