数据库索引实现

落日映苍穹つ 2022-04-13 04:22 150阅读 0赞

### 数据库（Mysql）索引实现 ###

#### 索引定义 ####

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

我们知道，数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

#### B-Tree和B+Tree ####

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。

##### B-Tree #####

![在这里插入图片描述][20181127114118346.png]  
由于B-Tree的特性，在B-Tree中按key检索数据的算法非常直观：首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，前者查找成功，后者查找失败。

另外，由于插入删除新的数据记录会破坏B-Tree的性质，因此在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质。

##### B+Tree #####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1aWx6_size_16_color_FFFFFF_t_70]  
B-Tree有许多变种，其中最常见的是B+Tree，MySQL就普遍使用B+Tree实现其索引结构。

与B-Tree相比，B+Tree有以下不同点：

每个节点的指针上限为2d而不是2d+1。
    内节点不存储data，只存储key；叶子节点不存储指针。

由于并不是所有节点都具有相同的域，因此B+Tree中叶节点和内节点一般大小不同。这点与B-Tree不同，虽然B-Tree中不同节点存放的key和指针可能数量不一致，但是每个节点的域和上限是一致的，所以在实现中B-Tree往往对每个节点申请同等大小的空间。  
一般来说，B+Tree比B-Tree更适合实现外存储索引结构。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1aWx6_size_16_color_FFFFFF_t_70 1]  
一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针。  
在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

#### 为什么使用B-Tree/B+Tree ####

为什么使用B-Tree（B+Tree）  
红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

为了提高效率，要尽量减少磁盘I/O，为了达到这个目的，磁盘按需读取，要求每次都会预读的长度一般为页的整数倍。而且数据库系统将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

#### 为什么B+Tree比B-Tree更适合数据库索引 ####

*  不同于B-Tree只适合随机检索，B+Tree同时支持随机检索和顺序检索；
 *  B+Tree的磁盘读写代价更低。B+Tree的内部结点并没有指向关键字具体信息的指针，其内部结点比B-Tree小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素。
 *  B+Tree的查询效率更加稳定。B-Tree搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+Tree中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。
 *  数据库索引采用B+Tree的主要原因是，B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+Tree的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B-Tree不支持这样的操作（或者说效率太低）
 *  增删节点时，B+Tree效率更高,B+Tree的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率，而对于B-Tree，由于插入删除新的数据记录会破坏B-Tree的性质，因此在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质

#### MySQL索引实现 ####

##### MyISAM/非聚簇索引 #####

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。  
MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

##### InnoDB/聚簇索引 #####

InnoDB的数据文件本身就是索引文件。  
从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。  
而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录(所以聚簇索引的key，不能过长)。  
这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

InnoDB主索引（同时也是数据文件），可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。  
因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。  
换句话说，InnoDB的所有辅助索引都引用主键作为data域。

参考资料：  
[http://blog.codinglabs.org/articles/theory-of-mysql-index.html][http_blog.codinglabs.org_articles_theory-of-mysql-index.html]  
[https://www.cnblogs.com/liqiangchn/p/9060521.html][https_www.cnblogs.com_liqiangchn_p_9060521.html]  
[https://blog.csdn.net/mine\_song/article/details/63251546][https_blog.csdn.net_mine_song_article_details_63251546]  
[https://blog.csdn.net/tongdanping/article/details/79878302][https_blog.csdn.net_tongdanping_article_details_79878302]

[20181127114118346.png]: /images/20220413/fb2cb971193a402a915e02989b821ef0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1aWx6_size_16_color_FFFFFF_t_70]: /images/20220413/eb704c46afe94eefbff190deed05c9a4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1aWx6_size_16_color_FFFFFF_t_70 1]: /images/20220413/253cdac5891b415e85918c9f33c3024c.png
[http_blog.codinglabs.org_articles_theory-of-mysql-index.html]: http://blog.codinglabs.org/articles/theory-of-mysql-index.html
[https_www.cnblogs.com_liqiangchn_p_9060521.html]: https://www.cnblogs.com/liqiangchn/p/9060521.html
[https_blog.csdn.net_mine_song_article_details_63251546]: https://blog.csdn.net/mine_song/article/details/63251546
[https_blog.csdn.net_tongdanping_article_details_79878302]: https://blog.csdn.net/tongdanping/article/details/79878302