数据库索引（一）

1.概述

大量的结构化数据通常被存储在数据库中，通过简单的SQL语句，我们可以方便的查询、修改、插入、删除这些数据。例如当我们执行：

SELECT * FROM USER WHERE userName = 'A';

DBMS会将表中所有用户名等于A的用户的记录返回给我们，但随着表中的记录越来越多，比如说从10000增加到1000万，这时候我们往往会发现查询的速度会变慢很多。这时候，通常的做法就是在userName列上面创建索引，以加快数据的查询。

那么什么是数据库索引、为什么数据库索引可以加快查询以及如何正确的使用数据库索引呢？以下将从这个几个方面做一下简单的总结。

2. 数据库索引

2.1 改善数据库查询

2.1.1 用树形数据结构进行查找

通常来讲，我们在对数据库中数据最频繁的操作便是查询，比如我们给定一定的查询条件将某个表的数据记录查询出来，因此这实际就是一个查找问题。

查找问题最简单直接的就是顺序查找，其时间复杂度为O(N)。

例如第1节中的：

SELECT * FROM USER WHERE userName = 'A';
  • 假设userName列中不存在重复值，很显然我们在查找这条记录的时候需要对数据表中数据记录一行一行的扫描，然后直到找到我们需要的那条记录，这种方式的平均时间复杂度是O(N/2)
  • 假设userName列中存在重复值，这时情况更加糟糕，我们需要进行所谓的全表扫描找到所有的记录，这种方式的时间复杂度是O(N)

即便是在内存中对大量数据进行O(N)的查询我们也很难接受，更何况是这些数据是存在外部存储设备上（常见便是磁盘）。磁盘的一次访问时间大约是8ms，而内存的一次访问约50ns。

因此当数据量特别大的时候，数据库用顺序查找的方式进行数据查找几乎是不可接受的。

在众多的用于查找的数据结构中，平衡的树形结构是十分合适被用来进行查找，其查找的时间复杂度可以降到O(logN)，当然某些数据库索引便是采用树形结构来组织的。

到现在，我们总结了为什么要使用数据库索引的原因，并且我们总结了数据库索引可以使用树形数据结构来加快数据的查询。下面给出维基百科的定义:

数据库索引是一种花费额外存储空间以加快数据库数据检索的数据结构。

我们知道我们可以采用平衡二叉排序树来组织数据库索引，每个节点存储关键字和其对应的数据（或者数据所在的磁盘地址）。这样在进行查询的时候，我们可以将时间复杂度降低到我们需要的O(logN)。但是实际上数据库索引并不是采用这种二叉树的数据结构，而是采用B-Tree来实现数据库索引以加快数据查询。关于为什么不使用二叉搜索树的原因以及B-Tree是什么，2.1.2会进行总结。

2.1.2 B-Tree

上图为磁盘的某个盘面的解剖示意图，由于一次寻道操作很慢，为了数据读取的有效性，磁盘是按扇区为单位存储和访问数据的。关于磁盘的工作原理，参见这篇博文。

  1. 如果我们用二叉搜索树组织10000个关键字，那么我们的树的高度约为：14。因此如果我们要查询叶节点上的关键字则需要14次磁盘访问，大约耗时:8ms*14=112ms。虽然这个结果对于顺序查找好了不少，但实际上为了降低磁盘访问次数，我们可以进一步将树变得矮一点便可以降低磁盘访问次数，从而提高查询速度。
  2. 为了降低树的高度，我们将二叉树中的每个节点最多俩孩子变为多个孩子，便可以将树的高度大大降低。

而这种平衡的、每个节点多与2个孩子节点的树形数据结构便是B-Tree。上图是一个简单的对比图，从图中我们可以看出B Tree相比二叉树，其每一个结点里面含有多个关键字，从而大大降低了树的高度。当每个结点允许存放100个关键字的时候，10000个关键字的B Tree树高大约为2，因此仅需要2次磁盘访问便可以访问到数据，大约耗时8ms*2=16ms，相比于前面的二叉树来讲，性能又提升了不少。B Tree的结点的大小通常被设置为一个扇区的大小（假如说是4KB），这样一次磁盘访问便可以读取到该节点所有关键字。

简单总结一下：由于数据存储在磁盘上，考虑到磁盘的特性，因此通过将结点的大小增加到一个扇区的大小，从而降低查找树的高度以减少耗时的磁盘访问次数，这就是B Tree适合用来查找基于外部存储数据的主要原因。

到这里我们总结了数据库索引为什么不采用二叉搜索树实现的原因。接下来我们稍微简单的总结下B Tree相关的内容。

来自维基的定义：

在计算机科学中，B树是一棵自平衡树，它允许在logN的时间内完成查找、顺序访问、插入和删除。B树是一种泛化的二叉搜索树，其结点可以拥有超过2个孩子结点。

一棵5阶的B树大概长这样（5阶指的是孩子节点数量最多为5）：

B树中的结点通常分为三类：

  • 根结点：包含关键字，包含关键字的个数有上界无下界，有孩子结点，包含指向孩子结点的指针
  • 内部结点：包含关键字，且包含关键字的个数有上下界，有孩子结点，包含指向孩子结点的指针
  • 叶子结点：包含关键字，且包含关键字的个数有上下界，没有孩子结点，不包含指向孩子结点的指针

关于B树的性质的描写有很多种，以下是从《算法导论》总结出来的：

B树应该满足的性质：

  1. 每个叶结点具有相同的深度，即树的高度h
  2. 每个结点所包含的关键字个数都有个下界（根节点没有下界）和上界，上下界通过一个被称为B树的最小度数的t>=2来表示
    • 除了根节点以外，每个结点至少含有t-1个关键字；因此除了根节点以外的每个内部结点至少含有t个孩子。如果树非空，根节点至少能含有1个关键字以保证树的最小度t>=2
    • 每个结点最多包含2t-1个关键字。因此一个内部结点最多有2t个孩子，当一个结点恰好有2t-1个关键字的时候，称该结点是满的

另外，有的地方存在阶的概念，指的是B树允许非叶子结点的最大孩子结点个数，关于B Tree的基本操作Search、Insertion、Delete等将在下一篇博文介绍，这里不做介绍。

数据库索引中的B树中，每个结点包含如下内容：

  • 存储代表被索引数据库列的关键字
  • 指向孩子结点的指针
  • 指向数据库记录的指针

B Tree虽然能很好的帮助我们改善诸如WHERE userName = 'A'之类的查询，但是B树还存在以下两个缺点：

  1. 但是对于一些常见的范围查询，例如WHERE age > 10等，我们就需要中序遍历B树，这会增加缓存miss率，从而增加我们的磁盘访问次数。
  2. 与此同时，由于我们在结点中不仅仅存储了指向孩子结点的指针，而且还存储了指向数据库记录的指针，因此每个结点的大小就会较大，又由于扇区的大小通常是固定的，因此每个结点包含的关键字的个数就会减少，也就是说B的阶会降低（树会变高）。

因此为避免上述两个缺点，B树的变体B+树便应运而生。

2.1.3 B+树

同B树不同，B+树主要有如下不同：

  • B+树中关键字和指向数据记录的指针仅仅存放在叶子结点；
  • B+树中同一关键字可能出现在多个结点中，但是仅仅叶子结点还有指向数据记录的指正；而B树同一关键字仅仅可能出现在一个结点中；
  • 由于B+树的关键字和数据记录的信息仅仅保存在叶子结点中，因此无论查找是否成功，都会深入到B+树的叶子结点，因此查询时间基本上是稳定的；而B树的查询时间和关键字在树中的位置有关，因此查询时间不是稳定的；

除此之外，B+树将叶结点通过链表的形式连接起来，这样避免了范围查询或者全表扫描的时候，对B树的中序遍历。

下图是B树和B+树的对比图：

2.2 数据库索引的类型

由于不同数据库提供的索引分类不同，因此很多时候索引分类是混乱的。但是常见的数据库索引主要有如下几种：

  1. 聚集索引（Clustered Index）：数据表中的记录的物理顺序和索引关键字的逻辑顺序是一致的。由于这个特性，聚集索引能够大大加快类似顺序检索、反序检索和范围查询。由于聚集索引的逻辑顺序和记录的物理顺序是一致的，显然一张数据表聚集索引只能有一个，通常建立在主键上。
  2. 非聚集索引（Non-Clustered Index）：和聚集索引相反，记录的物理顺序和索引关键字的逻辑顺序不是一致的。因此一个数据表可以有0个或者多个非聚集索引。
  3. 唯一索引（Unique Index）：唯一索引是不允许其中任何两行具有相同索引值的索引。
  4. 非唯一索引（Non-Unique Index）：允许任何两行具有相同索引值的索引，仅仅是用来加快查询。
  5. 主键索引（Primary Index）：建立在主键上的索引，由于主键具有唯一性，因此从某种意义上来讲，主键索引也是唯一索引，又因为主键列具有不经常修改等特性，因此主键索引通常也设置为聚集索引。由于主键索引的特殊性，大多数数据库都会默认在主键上自动创建主键索引。

3. 创建数据库索引

虽然索引会加快数据的查询，但是维护索引需要额外的空间和时间。因此需要在合适的列上创建合适的索引的，否则只会盲目的创建索引只会适得其反。

适合创建索引的列具有如下特点：

  • 对于经常处于WHERE、JOIN、OrderBy字句中的列应该创建索引，以加快查询
  • 对于需要保持唯一性的列，需要在创建唯一索引
  • 主键列默认是创建主键索引
  • 聚集索引应该创建在很少修改的列

不适合创建索引的列具有如下特点：

  • 查询中很少使用或者参考的列不应该创建索引；

  • 对于取值很少的列，例如性别列没必要创建索引；

  • 定义为text、image、bit的列不适和创建索引；

  • 频繁修改的列不适合创建索引，维护索引的代价会很大；

    ​

因为物理顺序和逻辑顺序是一致的，如果被索引的列频繁更新，为了维护逻辑顺序很物理顺序的一致性，会付出很大的代价，因此聚集索引应该建立在很少和不更新的列上面，

4. References

  1. https://stackoverflow.com/questions/1108/how-does-database-indexing-work
  2. https://stackoverflow.com/questions/107132/what-columns-generally-make-good-indexes/8937872#8937872
  3. https://stackoverflow.com/questions/870218/differences-between-b-trees-and-b-trees
  4. http://blog.csdn.net/yongheng_1999/article/details/53678814
  5. http://blog.csdn.net/kennyrose/article/details/7532032

 

posted @ 2018-01-11 08:51  Spground  阅读(375)  评论(0)  编辑  收藏  举报