数据库索引基础.docx

1、数据库索引基础数据库索引基础知识(YLH整理)一、 引言对数据库索引的关注从未淡出我的们的讨论，那么数据库索引是什么样的？聚集索引与非聚集索引有什么不同？希望本文对各位同仁有一定的帮助。有不少存疑的地方，诚心希望各位不吝赐教指正，共同进步。二、 数据结构基础我们常见的数据库系统，其索引使用的数据结构多是B-Tree或者B+Tree。例如，MsSql使用的是B+Tree，Oracle及Sysbase使用的是B-Tree。下面简单介绍一下B树以及相关变体。 B树 即二叉搜索树： 1.所有非叶子结点至多拥有两个儿子（Left和Right）； 2.所有结点存储一个关键字； 3.非叶子结点的左指针指向小

2、于其关键字的子树，右指针指向大于其关键字的子树； 如：B树的搜索，从根结点开始，如果查询的关键字与结点的关键字相等，那么就命中；否则，如果查询关键字比结点关键字小，就进入左儿子；如果比结点关键字大，就进入右儿子；如果左儿子或右儿子的指针为空，则报告找不到相应的关键字； 如果B树的所有非叶子结点的左右子树的结点数目均保持差不多（平衡），那么B树的搜索性能逼近二分查找；但它比连续内存空间的二分查找的优点是，改变B树结构（插入与删除结点）不需要移动大段的内存数据，甚至通常是常数开销； 如：但B树在经过多次插入与删除后，有可能导致不同的结构：右边也是一个B树，但它的搜索性能已经是线性的了；同样的关键字

3、集合有可能导致不同的树结构索引；所以，使用B树还要考虑尽可能让B树保持左图的结构，和避免右图的结构，也就是所谓的“平衡”问题； 实际使用的B树都是在原B树的基础上加上平衡算法，即“平衡二叉树”；如何保持B树结点分布均匀的平衡算法是平衡二叉树的关键；平衡算法是一种在B树中插入和删除结点的策略； B-树 是一种多路搜索树（并不是二叉的）： 1.定义任意非叶子结点最多只有M个儿子；且M2； 2.根结点的儿子数为2, M； 3.除根结点以外的非叶子结点的儿子数为M/2, M； 4.每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字） 5.非叶子结点的关键字个数=指向儿子的指针个

4、数-1； 6.非叶子结点的关键字：K1, K2, , KM-1；且Ki Ki+1； 7.非叶子结点的指针：P1, P2, , PM；其中P1指向关键字小于K1的子树，PM指向关键字大于KM-1的子树，其它Pi指向关键字属于(Ki-1, Ki)的子树； 8.所有叶子结点位于同一层； 如：（M=3）B-树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点； B-树的特性： 1.关键字集合分布在整颗树中； 2.任何一个关键字出现且只出现在一个结点中； 3.搜索有可能在非叶子结点结束；

5、4.其搜索性能等价于在关键字全集内做一次二分查找； 5.自动层次控制； 由于限制了除根结点以外的非叶子结点，至少含有M/2个儿子，确保了结点的至少利用率，其最底搜索性能为：其中，M为设定的非叶子结点最多子树个数，N为关键字总数； 所以B-树的性能总是等价于二分查找（与M值无关），也就没有B树平衡的问题； 由于M/2的限制，在插入结点时，如果结点已满，需要将结点分裂为两个各占M/2的结点；删除结点时，需将两个不足M/2的兄弟结点合并； B+树 B+树是B-树的变体，也是一种多路搜索树： 1.其定义基本与B-树同，除了： 2.非叶子结点的子树指针与关键字个数相同； 3.非叶子结点的子树指针Pi，指

6、向关键字值属于Ki, Ki+1)的子树（B-树是开区间）； 5.为所有叶子结点增加一个链指针； 6.所有关键字都在叶子结点出现； 如：（M=3）B+的搜索与B-树也基本相同，区别是B+树只有达到叶子结点才命中（B-树可以在非叶子结点命中），其性能也等价于在关键字全集做一次二分查找； B+的特性： 1.所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的； 2.不可能在非叶子结点命中； 3.非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层； 4.更适合文件索引系统； B*树 是B+树的变体，在B+树的非根和非叶子结点再增加指向兄弟的

7、指针；B*树定义了非叶子结点关键字个数至少为(2/3)*M，即块的最低使用率为2/3（代替B+树的1/2）； B+树的分裂：当一个结点满时，分配一个新的结点，并将原结点中1/2的数据复制到新结点，最后在父结点中增加新结点的指针；B+树的分裂只影响原结点和父结点，而不会影响兄弟结点，所以它不需要指向兄弟的指针； B*树的分裂：当一个结点满时，如果它的下一个兄弟结点未满，那么将一部分数据移到兄弟结点中，再在原结点插入关键字，最后修改父结点中兄弟结点的关键字（因为兄弟结点的关键字范围改变了）；如果兄弟也满了，则在原结点与兄弟结点之间增加新结点，并各复制1/3的数据到新结点，最后在父结点增加新结点的指

8、针； 所以，B*树分配新结点的概率比B+树要低，空间使用率更高； 小结 B树：二叉树，每个结点只存储一个关键字，等于则命中，小于走左结点，大于走右结点； B-树：多路搜索树，每个结点存储M/2到M个关键字，非叶子结点存储指向关键字范围的子结点； 所有关键字在整颗树中出现，且只出现一次，非叶子结点可以命中； B+树：在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中； B*树：在B+树基础上，为非叶子结点也增加链表指针，将结点的最低利用率从1/2提高到2/3；三、 数据库索引 什么是索引在数据库中，索引的含义与日常意义上

9、的“索引”一词并无多大区别（想想小时候查字典)，它是用于提高数据库表数据访问速度的数据库对象。A)索引可以避免全表扫描。多数查询可以仅扫描少量索引页及数据页，而不是遍历所有数据页。B)对于非聚集索引，有些查询甚至可以不访问数据页。C)聚集索引可以避免数据插入操作集中于表的最后一个数据页。D)一些情况下，索引还可用于避免排序操作。当然，众所周知虽然索引可以提高查询速度，但是它们也会导致数据库系统更新数据的性能下降，因为大部分数据更新需要同时更新索引。 索引的存储一条索引记录中包含的基本信息包括：键值（即你定义索引时指定的所有字段的值)+逻辑指针（指向数据页或者另一索引页)。当你为一张空表创建索引

10、时，数据库系统将为你分配一个索引页，该索引页在你插入数据前一直是空的。此页此时既是根结点，也是叶结点。每当你往表中插入一行数据，数据库系统即向此根结点中插入一行索引记录。当根结点满时，数据库系统大抵按以下步骤进行分裂：A)创建两个儿子结点B)将原根结点中的数据近似地拆成两半，分别写入新的两个儿子结点C)根结点中加上指向两个儿子结点的指针通常状况下，由于索引记录仅包含索引字段值（以及4-9字节的指针)，索引实体比真实的数据行要小许多，索引页相较数据页来说要密集许多。一个索引页可以存储数量更多的索引记录，这意味着在索引中查找时在I/O上占很大的优势，理解这一点有助于从本质上了解使用索引的优势。 索

11、引的类型A)聚集索引，表数据按照索引的顺序来存储的。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。B)非聚集索引，表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，该层紧邻数据页，其行数量与数据表行数据量一致。在一张表上只能创建一个聚集索引，因为真实数据的物理顺序只可能是一种。如果一张表没有聚集索引，那么它被称为“堆集”（Heap)。这样的表中的数据行没有特定的顺序，所有的新行将被添加的表的末尾位置。 聚集索引在聚集索引中，叶结点也即数据结点，所有数据行的存储顺序与索引的存储顺序一致。1)聚集索引与查询操作如上图，我们在名字字

12、段上建立聚集索引，当需要在根据此字段查找特定的记录时，数据库系统会根据特定的系统表查找的此索引的根，然后根据指针查找下一个，直到找到。例如我们要查询“Green”，由于它介于Bennet,Karsen，据此我们找到了索引页1007，在该页中“Green”介于Greane, Hunter间，据此我们找到叶结点1133（也即数据结点)，并最终在此页中找以了目标数据行。此次查询的IO包括3个索引页的查询（其中最后一次实际上是在数据页中查询)。这里的查找可能是从磁盘读取(Physical Read)或是从缓存中读取(Logical Read)，如果此表访问频率较高，那么索引树中较高层的索引很可能在缓存

13、中被找到。所以真正的IO可能小于上面的情况。2)聚集索引与插入操作最简单的情况下，插入操作根据索引找到对应的数据页，然后通过挪动已有的记录为新数据腾出空间，最后插入数据。如果数据页已满，则需要拆分数据页（页拆分是一种耗费资源的操作，一般数据库系统中会有相应的机制要尽量减少页拆分的次数，通常是通过为每页预留空间来实现)：A)在该使用的数据段（extent)上分配新的数据页，如果数据段已满，则需要分配新段。B)调整索引指针，这需要将相应的索引页读入内存并加锁。C)大约有一半的数据行被归入新的数据页中。D)如果表还有非聚集索引，则需要更新这些索引指向新的数据页。特殊情况：A)如果新插入的一条记录包含

14、很大的数据，可能会分配两个新数据页，其中之一用来存储新记录，另一存储从原页中拆分出来的数据。B)通常数据库系统中会将重复的数据记录存储于相同的页中。C)类似于自增列为聚集索引的，数据库系统可能并不拆分数据页，页只是简单的新添数据页。3)聚集索引与删除操作删除行将导致其下方的数据行向上移动以填充删除记录造成的空白。如果删除的行是该数据页中的最后一行，那么该数据页将被回收，相应的索引页中的记录将被删除。如果回收的数据页位于跟该表的其它数据页相同的段上，那么它可能在随后的时间内被利用。如果该数据页是该段的唯一一个数据页，则该段也被回收。对于数据的删除操作，可能导致索引页中仅有一条记录，这时，该记录可

15、能会被移至邻近的索引页中，原索引页将被回收，即所谓的“索引合并”。 非聚集索引非聚集索引与聚集索引相比：A)叶子结点并非数据结点;B)叶子结点为每一真正的数据行存储一个“键-指针”对(SQL Server的情况是：当表上含有聚集索引时,非聚集索引的叶子节点存储聚集索引键；当表为无序堆时非聚集索引的叶子节点存储行号RID,RID的表现形式为： 文件号：页号：槽号);C)叶子结点中还存储了一个指针偏移量，根据页指针及指针偏移量可以定位到具体的数据行;D)类似的，在除叶结点外的其它索引结点，存储的也是类似的内容，只不过它是指向下一级的索引页的;聚集索引是一种稀疏索引，数据页上一级的索引页存储的是页指

16、针，而不是行指针。而对于非聚集索引，则是密集索引，在数据页的上一级索引页它为每一个数据行存储一条索引记录。对于根与中间级的索引记录，它的结构包括：A)索引字段值B)RowId（即对应数据页的页指针+指针偏移量)。在高层的索引页中包含RowId是为了当索引允许重复值时，当更改数据时精确定位数据行。C)下一级索引页的指针对于叶子层的索引对象，它的结构包括：A)索引字段值B)RowId1)非聚集索引与查询操作针对上图，如果我们同样查找“Green”，那么一次查询操作将包含以下IO：3个索引页的读取+1个数据页的读取。同样，由于缓存的关系，真实的IO实际可能要小于上面列出的。2)非聚集索引与插入操作如

17、果一张表包含一个非聚集索引但没有聚集索引，则新的数据将被插入到最末一个数据页中，然后非聚集索引将被更新。如果也包含聚集索引，该聚集索引将被用于查找新行将要处于什么位置，随后，聚集索引、以及非聚集索引将被更新。3)非聚集索引与删除操作如果在删除命令的Where子句中包含的列上，建有非聚集索引，那么该非聚集索引将被用于查找数据行的位置，数据删除之后，位于索引叶子上的对应记录也将被删除。如果该表上有其它非聚集索引，则它们叶子结点上的相应数据也要删除。如果删除的数据是该数所页中的唯一一条，则该页也被回收，同时需要更新各个索引树上的指针。由于没有自动的合并功能，如果应用程序中有频繁的随机删除操作，最后可

18、能导致表包含多个数据页，但每个页中只有少量数据。 索引覆盖索引覆盖是这样一种索引策略：当某一查询中包含的所需字段皆包含于一个索引中，此时索引将大大提高查询性能。（SQL SERVER 2005中的相应解决方案为索引包含列的形式）包含多个字段的索引，称为复合索引。索引最多可以包含31个字段，索引记录最大长度为600B。如果你在若干个字段上创建了一个复合的非聚集索引，且你的查询中所需Select字段及Where,Order By,Group By,Having子句中所涉及的字段都包含在索引中，则只搜索索引页即可满足查询，而不需要访问数据页。由于非聚集索引的叶结点包含所有数据行中的索引列值，使用这些

19、结点即可返回真正的数据，这种情况称之为“索引覆盖”。在索引覆盖的情况下，包含两种索引扫描：A)匹配索引扫描B)非匹配索引扫描1)匹配索引扫描此类索引扫描可以让我们省去访问数据页的步骤，当查询仅返回一行数据时，性能提高是有限的，但在范围查询的情况下，性能提高将随结果集数量的增长而增长。针对此类扫描，索引必须包含查询中涉及的的所有字段，另外，还需要满足：Where子句中包含索引中的“引导列”（Leading Column)，例如一个复合索引包含A,B,C,D四列，则A为“引导列”。如果Where子句中所包含列是BCD或者BD等情况，则只能使用非匹配索引扫描。2)非配置索引扫描正如上述，如果Where子句中不包含索引的导引列，那么将使用非配置索引扫描。这最终导致扫描索引树上的所有叶子结点，当然，它的性能通常仍强于扫描所有的数据页。