数据库第四章索引与查询优化

索引

索引：

• 一种辅助的数据结构，记录了快速数据存取路径；
• 可以极大加快主表数据存取速度；
• 索引文件记录的结构：索引值+指针。

多级索引：数据记录数量庞大的情况下，单级索引效率太低，采用多级索引可以大大提高速度

B+树索引

数据库系统中使用最广泛的多级索引；

特点：

• 将数据存储块组织成一棵树；
• 这棵树是平衡的，即从树根到树叶的所有路径一样长；
• 通常B+数有三层：根、中间层和叶，当然也可以为任意层；
• 最底层的叶节点包含每个索引键和指向被索引行的行id；
• 叶节点之间有通道可供平行查询；
• 每一个叶节点都和磁盘页面大小一致。

B+树索引实例

B+树插入操作

B+树的插入删除不会引起过多的I/O操作；

插入之后B+树依然是平衡的，并保持了很好的性能；

插入过程：

• 设法再适当的叶结点中为新键找到空闲空间，如果有的话，就把键放在那里；
• 如果在适当的叶结点中没有空间，就把该叶结点分裂成两个，并且把其中的键分到这两个新结点中，使每个新节点有一半或刚好超过一半的键；
• 某一层的结点分裂从上一层的角度看来，相当于是要在这一较高的层次上插入一个新的键-指针对；如果有空间，则插入，没有空间就分裂这个父结点并且继续向树的高层推进；
• 例外的情况是，如果试图插入键到根结点中并且根节点没有空间，那么我们就分裂根节点成两个结点，且在更上一层创建一个新的结点，这个新的根结点有两个刚分裂成的结点作为他的子结点。

插入实例：

插入之前

插入之初

 

插入之后

B+树用一个链表存储重复的键值对应记录行的RID的值：

 

B+树索引特点

 效率：

• 一般B+树保持在3层，这意味着只需三次磁盘I/O即可获得数据的物理存储地址；
• 若将B+树的根结点和中间节点存入缓存，则只需一次磁盘I/O就能读取数据。

使用场景：

• 大部分情况下B+树索引都能工作得很好；
• 当要访问的记录数战记录总数的百分比非常大的时候，不用索引将比用索引更快。

散列索引

B+树索引需要三次左右磁盘I/O才能查到数据记录；

散列索引只需一次磁盘I/O就可以查到数据记录；

散列索引：根据给定索引值，用一种算法将记录分散存储在多个桶中（一般一个桶就是一个数据块，块中内容用一次磁盘操作就可以读取到内存中），当腰查找记录时，用相同算法算出该记录所在的桶，读取整个桶的数据到内存中，然后再桶中顺序查找要找的记录。

散列索引实例

如果桶的数量足够多，则每个桶通常占用一个磁盘页面（块）；

如果记录数很多，则会出现一个块中容纳不下新纪录的情况，这时可以增加一个溢出块到桶的链上。

散列索引特征 

特点：

• 散列索引是CPU密集型的，B+索引是I/O密集型的；
• 散列索引很紧凑，速度很快；
• 散列索引无法用于排序；
• 只支持等值查找，不支持范围查找；
• 不适合在重复值很多的列上建立哈希索引。

聚簇索引

聚簇索引：大多数关系表以堆组织表的形式存放；

• 堆：一块大的空间，供随机存放数据；
• 对组织表：一个大而无序的数据记录行的集合：① 行随机存放在任意一个能容纳他的位置上；② 行读取的顺序是不可预测的；

建立聚簇索引后，数据在物理文件中的存放位置不再是无序的，而是根据索引中键值的逻辑顺序决定了表中相应行的物理顺序。

非聚簇索引和聚簇索引的比较

聚簇索引特点

特点：

• 物理顺序只有一个，因此一张表只有一个聚簇索引；
• 在聚簇索引列上的查询速度比B+索引快；
• 数据在物理上按顺序排在数据页上，重复值页排在一起，因而在使用包含范围检查或使用group by或order by的查询时，可以大大提高查询速度；
• DML频繁的表不要建立聚簇索引，因为会带来大量数据维护的开销。

其他索引

其他索引类型：位图索引、函数索引、反序索引...

索引会带来维护开销，因此只在经常被作为查询条件的字段上或是确实需要提升查询性能的字段上建立索引。

针对不同的数据情况选择合适的索引类型，考虑因素如：

• 重复值占比；
• 列值是否被频繁更新；
• 是否范围查询或分组查询

查询优化

我们先来看一下SQL语句的执行过程，就能理解查询优化起作用的范围和作用。

优化准则

查询优化一般准则： 

• 选择运算尽可能先做（减少中间关系）
• 在执行连接操作前对关系适当进行预处理：按连接属性排序，在连接属性上建立索引
• 投影运算和选择运算同时做（避免重复扫描关系）
• 将投影运算与其前面或后面的双目运算结合（减少扫描关系的次数）
• 提取公共子表达式

优化过程

查询优化的一般过程：

• 将查询转换成某种内部表示，通常是语法树；
• 根据一定的等价变换规则把语法树转换成标准（优化）形式（代数优化）
• 选择低层的操作算法（物理优化）：① 对于语法树的每一个操作计算各种执行算法的执行代价；② 选择代价小的执行算法；
• 生成查询计划（查询执行方案）

优化实例

例：求选修了课程2的学生姓名

SELECT Student.Sname
FROM Student, SC
WHERE Student.Sno=SC.Sno
AND SC.Cno='2';

（1）把查询转换成某种内部表示

语法树

 

（2）代数优化

利用优化算法把语法树转换成标准（优化）形式

 （3）物理优化

选择低层的存取路径：

• 优化器查找数据字典获得当前数据库状态信息：① 选择字段上是否有索引，② 连接的两个表是否有序，③ 连接字段上是否有索引；
• 然后根据一定的优化规则选择存取路径；

（4）生成查询计划

选取代价最小的查询计划：

• 在做连接运算时，若两个表（设为R1,R2）均无序，连接属性上页没有索引，则可以有下面几种查询计划：① 对两个表做排序预处理，② 对R1在连接属性上建索引，③ 对R2在连接属性上建索引，④在R1,R2的连接属性上均建索引；
• 对不同的查询计划计算代价，选择代价最小的一个。