mysql索引的原理和底层结构

野性酷女 2022-04-08 11:43 321阅读 0赞

索引的底层结构使用的B+树和hash，但是我们一般推荐使用B+树，原因可能很少有人知道。

### 为什么索引的实现是B+树,而不是散列表，二叉树，B-等 ###

### B+B-相对于二叉树： ###

首先需要明确索引也是需要存储到磁盘的，然后B+-树相对于二叉树，最明显存在两点区别

1 二叉树深度较深，而深度越大，就说明指针的移动越多，需要读取的节点越多，这样就会导致磁盘IO是寻道时间多，IO次数增多耗时

2 B+-树的单个节点占用内存较大，而我们知道磁盘上的存储是按块存储，大小一般为4k（innodb为16kb），而且磁盘读取默认使用预读技术（局部性原理），所以B+-能充分利用磁盘读取的上面两个特点，加快读取速度。

### B+相对于B-树： ###

（1）B+树空间利用率更高，可减少I/O次数，

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。而因为B+树的内部节点只是作为索引使用，而不像B-树那样每个节点都需要存储硬盘指针。

也就是说：B+树中所有非叶节点没有指向某个关键字具体信息的指针，仅有指向树结构的指针，所以每一个节点可以存放更多的关键字数量，即一次性读入内存所需要查找的关键字也就越多，减少了I/O操作。

e.g.假设磁盘中的一个盘块容纳16bytes，而一个关键字2bytes，一个关键字具体信息指针2bytes。一棵9阶B-tree(一个结点最多8个关键字)的内   部结点需要2个盘快。而B+ 树内部结点只需要1个盘快。当需要把内部结点读入内存中的时候，B 树就比B+ 树多一次盘块查找时间(在磁盘中就   是         盘片旋转的时间)。

（2）增删文件（节点）时，效率更高，

因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。

（3）B+树的查询效率更加稳定，

因为B+树的每次查询过程中，都需要遍历从根节点到叶子节点的某条路径。所有关键字的查询路径长度相同，导致每一次查询的效率相当。

下面是B+和B-结构示意图：

B-树：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70][]

或者下面图更有助于理解：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 1][]

B+树：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 2][]

B+树另一种示意图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 3][]

一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 4][]

在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如图4中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

### 为什么不是红黑树 ###

红黑树与B树最大的区别是儿子节点只能有两个，决定了大量数据的时候深度仍然过大。

在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。为什么会出现这样的情况，我们知道要获取磁盘上数据，必须先通过磁盘移动臂移动到数据所在的柱面，然后找到指定盘面，接着旋转盘面找到数据所在的磁道，最后对数据进行读写。磁盘IO代价主要花费在查找所需的柱面上，树的深度过大会造成磁盘IO频繁读写。

### 不推荐用哈希表的原因如下: ###

哈希索引中存储的是：索引列哈希值+数据行指针 ,所以会导致以下问题

第一 hash表第一存在散列冲突问题、利用率为50%、装填到一定程度需要rehash；

第二 使用上，因为存的key是hash后的值，所以不能直接使用范围查询和排序；不能使用覆盖索引；不适用联合索引的最左前缀原则。

但是存在hash索引，关于hash索引和b+tree索引区别可参考：

[https://blog.csdn.net/h2604396739/article/details/82748423][https_blog.csdn.net_h2604396739_article_details_82748423]

# MySQL索引实现 #

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

## MyISAM索引的B+实现 ##

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

![20181218180009156][]

图8

这里设表一共有三列，假设我们以Col1为主键，则图8是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

![format_png][]

图9

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

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

## InnoDB索引实现 ##

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

![format_png 1][]

图10

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

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，图11为定义在Col3上的一个辅助索引：

![20181218180009247][]

图11

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

Hash索引：哈希索引基于哈希表实现，只有精确索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希存储在索引中，同时在哈希表中保存指向每个数据的指针。

推荐个mysql索引底层结构讲的比较好的博客，上面部分内容也是借鉴与下面：

[http://blog.codinglabs.org/articles/theory-of-mysql-index.html][http_blog.codinglabs.org_articles_theory-of-mysql-index.html]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70]: /images/20220405/4a54cb92a94d44128993d413b0033227.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 1]: /images/20220405/5e1fd62624f84a9da76ee058e8e7a5c6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 2]: /images/20220405/7dbe3afc16d94e28a1a46fd439b4205d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 3]: /images/20220405/962a15b11e84403b88842904f0d31a65.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 4]: /images/20220405/f152f434b2fc48788a213f7fde068dad.png
[https_blog.csdn.net_h2604396739_article_details_82748423]: https://blog.csdn.net/h2604396739/article/details/82748423
[20181218180009156]: /images/20220405/d96e598aa0d245ea981e9411e0ece96d.png
[format_png]: /images/20220405/49483e9a42e043fc9d27681d77942238.png
[format_png 1]: /images/20220405/46f2672f005d4e1aa49f3c48b47674bd.png
[20181218180009247]: /images/20220405/d70ad2d16d2f45b98f4013183200ff15.png
[http_blog.codinglabs.org_articles_theory-of-mysql-index.html]: http://blog.codinglabs.org/articles/theory-of-mysql-index.html