SQL索引剖析

1. 索引剖析

“索引使得查询更加迅速” ，这个是我见到过的对索引最基本的定义.尽管它非常好地诠释了索引最重要的一面，可是，对于本书而言，它还不够完整。本章从一个既不肤浅又不非常深入的方式，描述了索引的结构，以提供给读者足够的知识来理解本书对于SQL性能方面的探讨。

索引是一种数据库内置的独立的结构，可以用create index来创建。它需要自己独立的硬盘空间，来保存表中被索引的数据。也就是说，索引是纯粹的数据冗余。创建索引并不改变表里的数据，而是仅仅创建了一个数据结构来指向表中的数据。所以，数据库索引很像本书最后的索引，它会占用篇幅，高度冗余，但是它会告诉你本书中有效信息的具体位置。

在数据库中搜索索引，就像是搜索一本打印好的电话号码册，所有的条目都是按照预先约定的顺序排列。在排号序的数据集合里面查找数据非常轻松快速，因为排序决定了每个条目的位置。

可是，数据库索引旺旺比电话号码册要复杂的多，因为它要处理一些数据的变化。更新电话号码册是不可能的，原因很简单，因为在现有条目之间，已经没有足够的空间来存放新的条目。它会在下一次打印的时候添加已经收到的更新。可是将数据库却不能这么做，它必须立即处理insert, delete 和 update 操作，以保证索引新加索引数据时不会有大的数据迁移。

数据库综合了两种数据结构来解决这个问题：双向链表和搜索树，这两种数据结构勾出勒了大多数数据库的性能特性。

1.1 索引叶子节点

索引的初衷是为了让被索引的数据有序地呈现出来。可是，按照（物理磁盘）顺序存储数据似乎是不可能的，因为INSERT操作会导致大量的数据移动来为新数据腾出空间。大批量的数据迁移是非常耗时的，而INSERT操作也会因此变得非常缓慢。这个问题的解决办法，就是在内存中建立一个独立于物理磁盘顺序的逻辑顺序。

逻辑顺序是借助双向链表来实现的。每一个节点都有它的两个相邻节点的链接，很像一条链子。在两个节点之间插入新的节点，是靠更新他们之间的链接来实现。于是新节点的物理地址就不重要了，因为双向链表已经维护了一个逻辑顺序。

这种数据结构之所以被称为“双向链表”，是因为它的每一个节点都指向了它的前一个和后一个节点。数据库借助它能够很好地按需来读取索引。这样，插入新的数据而不做大批量的数据迁移就成为了可能，只需要更新一些指针就行了。

双向链表在很多编程语言中也被当做结合来使用。

数据库用双向链表来存储索引叶子节点。每个叶子节点都被存储在数据库的块（页， 数据库的最小存储单元）当中。所有的索引块大小都是一样的，通常是几KB。数据库充分使用每一个节点来来存储尽量多的索引条目。进一步说，索引的顺序在两个层次上进行维护：每个叶子节点内部的索引条目和双向链表维护的叶子节点。（这句话翻译的不好，不过大致意思应该懂的） That means that the index order is maintained on two different levels: the index entries within each leaf node, and the leaf nodes among each other using a doubly linked list.

Figure 1.1. Index Leaf Nodes and Corresponding Table Data

图1.1展示了索引叶子节点和数据之间的联系。每一个索引节点都包含了被索引列的数据。并且指向数据库对应的行（通过ROWID或RID）。和索引不同，表中的数据是存在堆里面的，完全没有排序，并且数据块之前以及行与行之前是没有关系的。

1.2 搜索树（B树）让索引更加迅速

索引叶子节点是随机顺序存储的，也就是说，磁盘上的位置和逻辑的位置并不是对应的。它就像一个页码错误的电话号码目录一样。如果搜索“Smith”，可是第一次打开的却是由“Robinson”的一页，不要想当然地认为Smith是在Robinson后面的。数据库需要另外一种结构，以便于能够在混乱的页码找找到正确的数据，B树是一种好的选择。

Figure 1.2. B-tree Structure

图 1.2 展示了一个有30个条目的索引。双向链表建立了节点之间的逻辑顺序，根节点和枝干节点支持叶子节点之间的快速查询。

这张图突出显示了一个枝干节点和一些它指向的叶子节点。每个枝干节点数据都对应着每个块中的最大的值。也就是说，46，在第一个节点中是最大的，所以枝干节点中第一个值就是46。同理，枝干节点中海油53,57,83。根据要求，一个枝干层在包含了所有的叶子节点之后就建立好了。

下一层也是按照类似的逻辑基于上一层枝干节点来建立的。这个过程一直重复，直到所有的键值能够被容纳在一个节点内，这个节点就是根节点。这种结构被称为是平衡树，是因为树的深度在每个节点都相等，叶子节点到根的距离都相同。

索引一旦被建立，数据库就会一直自动维护它。数据库会在每一个insert, delete 和update来更新索引，以保持它的平衡，这就带来了写操作的维护开销。

Figure 1.3. B-Tree Traversal

图 1.3展示了一个索引片段，来说明如何查找键值57。从根节点开始遍历，每一个条目是按照升序排列的，一直到找到一个值大于或等于要搜索的条目。在本图中是83。数据库会赞着这种方式重复这样的操作，一直到遍历到叶子节点为止。

树的遍历是非常迅速的操作，这种迅速被我称为索引的第一种力量。它基本上是即时的，即使数据量很大。究其原因,其一是因为树是平衡的，它允许在相同的步数内访问所有的元素；其二是因为，树的深度是对数增长的，也就是说，树的高度增加和叶子节点的数量相比是非常缓慢的。真实环境中，数百万量的记录，索引树的深度只有4或者5。树的深度达到6的就很少见了。

1.3 缓慢的索引（第一部分）

尽管树的遍历是非常有效的，有些时候，用索引来查找仍然不如预期中的快。在很长一段时间里，这个矛盾让“不建立索引”的错误看法很吃香。这种错误看法认为，重建索引有点不合逻辑。现在看来，你可以认为重建一个新的索引对于一个大sql来说是没有帮助是理所当然的。而一些sql语句慢的真正原因（即使用了索引）可以用前一节的理论来解释。

索引查找缓慢的第一个原因是叶子节点链。在图1.3中，试着查找57。很明显，有2条同样记录命中了。至少有两条记录是一样的，进一步而言，后面的叶子节点，也有可能是57。数据库必须读取下一个叶子节点，看是否有更多的条目能够满足要求。也就是说，索引查询不仅需要遍历树，而且需要查询节点链。

索引查找缓慢的第二个因素是访问表。每一个叶子节点可能包含很多个命中的记录，而对应的表的数据经常分布在很多表的块中，也就是说，对于每一个命中的记录需要额外的表访问。

索引查询需要三步：1. 遍历索引树，2. 查询叶子节点链，3. 获取表表中的数据。其中只有第一个步，对应访问数据块的数量才有上限。其余两步可能需要访问很多块，他们才是导致索引查询缓慢的元凶。

缓慢索引的错误观点源于，认为索引查询只需要遍历索引树，而由此推出，缓慢索引是由于树的损坏。真相是，你可以了解到大多数数据库是怎么样使用索引的。ORACLE在这方面做的就非常繁琐，仅仅索引查询就有三种不同的操作方式：

INDEX UNIQUE SCAN

INDEX UNIQUE SCAN仅仅遍历索引树。ORACLE数据库只用这种操作方式，只有当唯一约束保证了不会有多条记录满足同一个搜索条件。

INDEX RANGE SCAN

INDEX RANGE SCAN 执行前两部来找到所有符合条件的条目。当多条记录有可能同时满足查询条件时，这种做法是可靠。

TABLE ACCESS BY INDEX ROWID

TABLE ACCESS BY INDEX ROWID 会从表中回去一行记录。这种操作通常会在前面的索引扫描操作之后被每一个符合条件的记录执行。

重要的一点是，INDEX RANGE SCAN 可能会读取索引的很大一部分。而当每条记录需要不止一次的表访问时，即使使用索引，整个查询也会变的很慢。