数据库原理之一（存储与索引）

一、基本概念

在磁盘上，存储信息的单位是页，页的大小是DBMS的一个参数，典型的值是4KB或者8KB.

聚簇索引：数据记录的顺序与某一索引的数据项顺序相同或类似，否则就是非聚簇索引。

索引为什么可以提高性能:因为可以迅速缩小查找范围！！！

索引数据结构：

a)基于哈希的索引：

文件中的记录分别放在不同的桶中，其中一个桶有一个或多个页组成。一个记录属于哪个桶可以由一个特殊的函数用于搜索码来决定，这个函数成为哈希函数。给出一个桶号，一个基于哈希的索引结构允许我们一两次I/O就能检索到该桶的主页。

b)基于树的索引：

数据项按照搜索码值进行排列，并且维护一个层次化的搜索数据结构，以便将搜索定向到数据项所属的页面。

非聚簇的哈希索引和树索引提供高速的插入，删除性能，但是处理扫描和有较多匹配结果的范围查询却比较慢。在等值搜索方面，哈希索引要略快一点，但是哈希索引却不支持范围搜索条件的选择。

二、索引案例：

a)聚簇索引的设计举例：

select E.do

from Employees E

where E.age>40

分析一下在年龄上使用B+树索引，这样的索引是否值得建立，首先依赖于条件的选择性（普及性）。如果实际上每个人都大于40岁的话，这样的索引优势就会很小。假设只有10％的雇员大于40岁，那么这样的索引是否有效呢？答案依赖于索引是否是聚簇的，如果是非聚簇的，每个满足条件的雇员都要花费一次I/O。如果是聚簇的，就只需要扫描10％的I/O了。

select E.dno,count(*)

from Employees E

where E.age>10

Group by E.dno

假设在年龄上有一个B+树索引，对取出的记录在dno上排序，就得到了该查询的结果了。如果几乎所有的雇员都大于10岁，这就不是一个号的执行计划，要是这个索引是非聚簇的，那么这个计划就可能是最糟糕的。

假设在dno上索引是否能满足我们的目标：是用该索引来检索所有记录，按照dno的值进行分组，然后对每一个dno值计算年龄大于10的记录个数（这个策略只需要记录被分组，并不需要记录被排序）。所以该方法的有效性十分依赖于索引是否是聚簇的。如果是非聚簇的，可能对每条记录都要进行一次I/O，这个方案的代价就非常的惊人了。

结论：如果age上的条件不是很有选择性（即符合该条件的记录占总记录数高达80％以上），就应该在dno上建立一个聚簇索引。如果age上的条件很有选择性，就应该考虑在age上建立索引（不必是聚簇的），以迅速缩小查找范围。

聚簇性对于在不包含候选码的搜索码上建立索引也是很重要的。

select E.dno

from Employees E

where E.hobby='Stamps'

如果很多人都集邮，那么通过一个非聚簇索引来检索记录将是十分低效的。即使没有索引（简单的扫描所有记录可能更廉价些），因此，应该考虑hobby上建立一个聚簇索引。

再来看看聚集操作如何影响索引的选择：

select E.dno,count(*)

from Employees E

Group by E.dno

最直接的查询方案：将Employees按照dno进行排序，然后对每一个dno值计算雇员记录的个数。然而，如果有一个dno索引的话，则可以只通过扫描索引而无需扫描记录来回答查询。

b)复合码设计举例

一个复合搜索码支持更广范围的查询，因为它可以和更多条件匹配。

考虑如下查询，要求返回20<age<30且3000<sal<5000的雇员

select E.eid

from Employees E

where E.age between 20 and 30

and E.sal between 3000 and 5000

如果where条件选择性好的话，在<age,sal>或者<sal,age>的复合码对于查询可能有帮助。而且显然这个索引是B+树索引而非哈希索引，因为哈希索引无法回答范围查询。

然后搜索码的顺序有时也回带来很大不同：

select E.eid

from Employees E

where E.age =25

and E.sal between 3000 and 5000

在<age,sal>的B+树索引将拥有良好的性能，因为记录先age排序后sal排序。在<sal,age>上性能就没那么好了，就算两个拥有相同的age值的，也回离得很远。

在处理聚集查询时，复合索引也很有用：

select Avg(E.sal)

from Employees E

where E.age =25

and E.sal between 3000 and 5000

在<age,sal>或<sal,age>的索引，允许仅须扫描索引就可以回答查询