Mysql（1）之深入理解Mysql索引底层数据结构与算法

一、前言

在这里我们主要是介绍一下几个知识点：

1. 索引数据结构、红黑树、Hash、B+树详解；
2. 索引是怎么支撑千万级表的快速查找；
3. 面试常问B+树索引面试题
4. 联合索引底层结构又是怎么样的；

二、索引的本质

        索引是帮助Mysql高效获取数据的排序好的数据结构。这种数据结构MySQL中最常用的就是B+树(B+Tree)。

        就好比给你一本书和一篇文章标题，如果没有目录，让你找此标题对应的文章，可能需要从第一页翻到最后一页；如果有目录大纲，你可能只需要在目录页寻找此标题，然后迅速定位文章。

        这里我们可以把书(book)看成是MySQL中的table，把文章(article)看成是table中的一行记录，即row，文章标题(title)看成row中的一列column，目录自然就是对title列建立的索引index了，这样根据文章标题从书中检索文章就对应sql语句select * from book where title = ?，相应的，书中每增加一篇文章(即insert into book (title, ...) values ('华山论剑', ...))，都需要维护一下目录，这样才能从目录中找到新增的文章华山论剑，这一操作对应的是MySQL中每插入(insert)一条记录需要维护title列的索引树(B+Tree)。

        提到索引我们可能想到一下集中数据结构来实现索引，例如：二叉树、红黑树、hash、B-Tree。下来我们来介绍一下如果用介绍一下如果用这集中数据结构实现索引的话会有哪些弊端。

三、二叉树

        简单地理解，满足以下两个条件的树就是二叉树：

        二叉树的结构有三个要点：

1. 每个节点最多有两个子节点，分别称作左子节点和右子节点。
2. 每个节点的左子节点的值比它小，右子节点的值比它大。
3. 每个节点的左子树每个节点的值都比它小，右子树每个节点的值都比它大。

下面我们来模拟一下 二叉树来生成索引，国外有个网站可以模拟可以数据算法的插入：Data Structure Visualization https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

        然后我们现在来模拟一下插入，1，2，3，4，5，6，7来看下如果是使用二叉树来维护索引会有哪些弊端； 

        我们可以看得到在极端的情况下，二叉树已经退化成链表，我们检索数据假如数据在尾端，那我们就需要遍历所有的数据，性能就非常的差。 

四、红黑树

        红黑树(Red Black Tree)是一种自平衡二叉查找树，在进行插入和删除操作时通过特定操作保持二叉查找树的平衡，从而获得较高的查找性能。

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

  下面我们也来模拟用红黑树来维护1-7的记录数据的数据结构：

      

        这样子咋一看是不是没啥问题，但是其实问题非常的大，首先这里只是7条数据，我们我们数据量级差不多到几百w的数据，那么这课红黑树的高度就非常的高，在这里，磁盘I/O的次数取决于树的高度，所以，在数据量较大时，红黑树会因树的高度较大而造成磁盘IO较多，从而影响查询效率。

五、B树

         B树中的B代表平衡(Balance)，而不是二叉(Binary)，B树是从平衡二叉树演化而来的。为了降低树的高度(也就是减少磁盘I/O次数)，把原来瘦高的树结构变得矮胖，B树会在每个节点存储多个元素(红黑树每个节点只会存储一个元素)，并且节点中的元素从左到右递增排列。如下图所示：

        我们来看下模拟1-7的数据，B数的索引结构：

 

        这样子看树的高度问题是解决了，但是会有什么问题呢？

         B-Tree在查询的时候比较次数其实不比二叉查找树少，但在内存中的大小比较、二分查找的耗时相比磁盘IO耗时几乎可以忽略。 B-Tree大大降低了树的高度，所以也就极大地提升了查找性能。

        但是B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个节点的存储空间是有限的，如果data值较大时将会导致每个节点能存储的key的数量很小，这样会导致B-Tree的高度变大，增加了查询时的磁盘I/O次数，进而影响查询性能。Mysql默认每一页的大小是16K如果单个节点的数据量过大，导致每一层能够存储的节点个数就会少，那么树的高度将会增加，那么就增加了IO次数。

六、B+树

        B+Tree是在B-Tree基础上进一步优化，使其更适合实现存储索引结构。InnoDB存储引擎就是用B+Tree实现其索引结构。

• 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引；
• 叶子节点包含所有索引字段；
• 叶子节点用指针连接，提高区间访问的性能；

  B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个节点的存储空间是有限的，如果data值较大时将会导致每个节点能存储的key的数量很小，这样会导致B-Tree的高度变大，增加了查询时的磁盘I/O次数，进而影响查询性能。在B+Tree中，所有data值都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以增大每个非叶子节点存储的key值数量，降低B+Tree的高度，提高效率。如下图：

        我们来看下对应的B+树的索引结构：

 

        只不过在msyql中，他对B+树有进行了优化，增加一个指向前节点的指针，最后一个指针又指向回头指针，这样子叶子节点就形成了一个循环链表。

七、其他知识

        这里补充一点相关知识 在计算机中，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：

        由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。预读的长度一般为页(page)的整数倍。

        页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页(许多操作系统的页默认大小为4KB)，主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时操作系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。(如下命令可以查看操作系统的默认页大小)；

$ getconf PAGE_SIZE
4096

         数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为操作系统的页大小的整数倍，这样每个节点只需要一次I/O就可以完全载入。

        InnoDB存储引擎中也有页(Page)的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB。

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.01 sec)

        一般表的主键类型为INT（占4个字节）或BIGINT（占8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为10^3）。也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿条记录。

        B+Tree的高度一般都在2到4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1到3次磁盘I/O操作；随机I/O对于MySQL的查询性能影响会非常大，而顺序读取磁盘中的数据会很快，由此我们也应该尽量减少随机I/O的次数，这样才能提高性能。在B-Tree中由于所有的节点都可能包含目标数据，我们总是要从根节点向下遍历子树查找满足条件的数据行，这会带来大量的随机I/O，而B+Tree所有的数据行都存储在叶子节点中，而这些叶子节点通过双向链表依次按顺序连接，当我们在B+树遍历数据(比如说范围查询)时可以直接在多个叶子节点之间进行跳转，保证顺序、倒序遍历的性能。

八、MyISAM索引实现

        MyISAM索引文件和数据文件是分离的(非聚集)，MyISAM引擎使用B+Tree作为索引结构时叶子节点的data域存放的是数据记录的地址。如下图所示：

        由上图可以看出：MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址，因此MyISAM的索引方式也叫做非聚集的，之所以这么称呼是为了与InnoDB的聚集索引区分。这个也是面试经常会问到的。

8.1非主键索引-MyISAM索引实现

        MyISAM中，主键索引和非主键索引（Secondary key，也有人叫做辅助索引）在结构上没有任何区别，只是主键索引要求key是唯一的，而辅助索引的key可以重复。这里不再多加叙述。

九、 InnoDB索引实现

        InnoDB的主键索引也使用B+Tree作为索引结构时的实现方式却与MyISAM截然不同。InnoDB的数据文件本身就是索引文件。在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶子节点data域保存了完整的数据记录，这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。如下图：

         InnoDB存储引擎中的主键索引(primary key)又叫做聚集索引(clustered index)。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

        在InnoDB中，用非单调递增的字段作为主键不是个好主意，因为InnoDB数据文件本身是一棵B+Tree，非单增的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，因而使用递增字段作为主键则是一个很好的选择。

        9.1非主键索引

        InnoDB的非主键索引data域存储相应记录主键的值。换句话说，InnoDB的所有非主键索引都引用主键的值作为data域。如下图所示：

        由上图可知：使用非主键索引搜索时需要检索两遍索引，首先检索非主键索引获得主键(primary key)，然后用主键到主键索引树中检索获得完整记录。

        那么为什么非主键索引结构叶子节点存储的是主键值，而不像主键索引那样直接存储完整的一行数据，这样就能避免回表二次检索？显然，这样做一方面节省了大量的存储空间，另一方面多份冗余数据，更新数据的效率肯定低下，另外保证数据的一致性是个麻烦事。

 9.2联合索引

       联合索引是多列按照次序一列一列比较大小，拿idx_book_id_hero_name这个联合索引来说，先比较book_id，book_id小的排在左边，book_id大的排在右边，book_id相同时再比较hero_name。如下图所示：

                 就是说联合索引中的多列是按照列的次序排列的，如果查询的时候不能满足列的次序，比如说where条件中缺少col1 = ?，直接就是col2 = ? and col3 = ?，那么就走不了联合索引，从上面联合索引的结构图应该能明显看出，只有col2列无法通过索引树检索符合条件的数据。

        好了，到这里只是大体的介绍了各种数据结构实现索引的弊端，但是每种数据结构的具体实现方式但是并没有说明，大家有兴趣的可以自己去详细学习一下。