索引可以理解为一种特殊的目录结构。
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)”
待续。