【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)”

待续。

上一篇:【转载】Java并发编程:volatile关键字解析(写的非常好的一篇文章)


下一篇:jquery.validate和jquery.form配合实现验证表单后AJAX提交