深入理解Mysql索引底层数据结构

目录

 前言

一：索引的引入

二：索引的数据结构

        1、二叉树与AVL平衡二叉树

                1.1、二叉树介绍

                1.2、平衡二叉树介绍

        2、红黑树

        3、B-Tree

        4、B+Tree

三：不同存储引擎存储磁盘的文件类型

四：关于索引相关的问题

        1、为什么建议主键为int类型递增？

        2、为什么非聚簇索引叶子节点存放的是聚簇索引？

总结

---

 前言

mysql作为主流数据库，目前很多公司都在使用，那么在日常开发中，也会每天都在与数据库打交道，SQL优化不仅是DBA要掌握的，也是每位开发人员所必备的技能之一，本文主要讲解mysql的索引结构，以便帮助大家更好的理解SQL优化为什么优先优化索引，后续将为讲解更多的SQL优化实战，尤其是工作中经常遇到的问题

一：索引的引入

如下图，加入存在一张表，有col1和col2两个字段，假如图左表示这些数据都是存放在表中，图右表示将col2设置为索引，使用二叉树存放，value对应的是磁盘地址。

假设现在有一条sql：select * from table where col2=89；

分析下：如果查找表，将会进行6次查询才会将这条数据找到，但是如果查找二叉树，第二次就会确定89的位置，假设再根据地址去磁盘查询，也就3次就会将这条数据找到。所以索引确实能提高很大的的效率，对于索引大家要理解下面这句话中的关键字。

总结：索引是帮助MySQL高效获取数据的排好序的数据结构

二：索引的数据结构

    在说数据结构之前，先提供一个数据结构的可视化工具，Data Structure Visualization，这里包含了堆栈、二叉树、红黑树、排序等很多数据结构以及算法，是个很好用的工具。

 

        1、二叉树与AVL平衡二叉树

                1.1、二叉树介绍

 二叉查找树也称为有序二叉查找树，满足二叉查找树的一般性质，是指一棵空树具有如下性质：   

1.  任意节点左子树不为空,则左子树的值均小于根节点的值
2.  任意节点右子树不为空,则右子树的值均大于根节点的值
3. 任意节点的左右子树也分别是二叉查树

那么假设例子中的col2字段为索引，利用上面的工具可查看它的存储结构如下图：

 那么寻找89的这条数据，那么会进行4次查找 34->77->91->89。这样大家觉得效率还是挺高的，那么假如上面的col1也是索引（在实际中，主键所有都是递增的值存储），那么它的存储结构如下图：

 很明显，二叉树的弊端就体现出来了，像这种的数据结构，二叉树就会退化成一个N个节点的线性链，完成体现不出二叉树的优势。为了解决这个问题，需要一种可以自动平衡的数据结构，此时就有了AVL平衡二叉树，同样，我们先看下平衡二叉树的结构。（PS：图片中的水印可能会影响，毕竟没有买会员，也不好去掉）

                1.2、平衡二叉树介绍

AVL树是带有平衡条件的二叉查找树，一般是用平衡因子差值判断是否平衡并通过旋转来实现平衡，左右子树树高不超过1，它是严格的平衡二叉树，不管我们是执行插入还是删除操作，只要不满足上面的条件，就要通过旋转来保持平衡，而旋转是非常耗时的，由此我们可以知道AVL树适合用于插入删除次数比较少，但查找多的情况。 

此时我们继续寻找89这条数据，只需要经过两次查找即可，34->89 ，效率比二叉树搞了很多，同样，对应col1这种类型的索引，同样也适用，如下图：

 

 因此，平衡二叉树比二叉树更适于索引存储的数据结构，但是同样的，当数据量很大的时候，平衡二叉树也会面临一个问题，就是树的高度会变得非常高，那么查询效率就会非常低下

        2、红黑树

一种二叉查找树，但在每个节点增加一个存储位表示节点的颜色，可以是red或black。通过对任何一条从根到叶子的路径上各个节点着色的方式的限制，红黑树确保没有一条路径会比其它路径长出两倍。它是一种弱平衡二叉树(由于是弱平衡，可以推出，相同的节点情况下，AVL树的高度低于红黑树)，相对于要求严格的AVL树来说，它的旋转次数变少，所以对于搜索、插入、删除操作多的情况下，我们就用红黑树。

平衡二叉树和红黑树的一个节点对应一个键值和数据，我们每次查找数据就需要从磁盘中读取一个节点，也就是我们说的磁盘块，一个节点对应一个磁盘块。当存储海量数据时，树的节点会非常多，会进行很多次的磁盘I/O，查找效率仍是极低的。这就需要一个单节点能存储多个键值和数据的一种平衡树了，所以B-Tree和B+Tree就是为了解决这点。

        3、B-Tree

B树和B+树是为了磁盘而设计的一种平衡多路查找树(相对于二叉，B树每个内节点有多个分支)，与红黑树相比，在相同的的节点的情况下，一颗B/B+树的高度远远小于红黑树的高度（具体原因后面会降到），B树和B+树操作的时间通常由磁盘I/O的时间和CPU计算时间这两部分构成，磁盘I/O成本主要取决于此次查询所需要的几次读取磁盘，CPU成本主要取决于查询语句的计算逻辑和在多个查询共同情况下存在线程争夺CPU时间片，目前CPU的速度非常快，所以B树的操作效率取决于访问磁盘的次数，B树的高度越小，磁盘I/O所花的时间越少。

B-Tree的特性如下：

1. 每个节点存放多个数据（索引值），每两个数据之间存在指向下个节点的指针
2. 所有叶子节点与非叶子节点都存放数据
3. 非叶子结点的索引个数=指向儿子的指针个数-1

在上图中，如果找49这个值，第一次读取上面的节点，发现49大于15，小于56，所以通过之间的指针读取到49所在的节点，那么B-Tree虽然解决了树高的问题（每个节点存放更多的数据，减少了磁盘I/O次数），但是当数据量很大的时候，还会存在问题。

        4、B+Tree

B+Tree是对B-Tree的一种优化，简单说就是B+树的非叶子节点是不存储数据的，仅存放键值，为什么呢？这就要说下mysql存储单元了，以Mysql的InnoDB存储引擎为例，最小的存储单元是以页为单位，一个页的大小是固定的，大小为16K，这个值也是mysql建议的大小，其实也是可以设置的，正常情况下还是不建议修改。可以通过以下命令查看页的大小。

B+Tree的特性如下：

1. 非叶子节点不存储数据，只存储索引，这样可以放更多的索引数据
2. 叶子节点包含所有的索引字段（聚簇索引）
3. 叶子节点都是用指针相连（双向链表，这也是mysql对B+Tree的优化），提高区间访问的性能

上图是一个聚簇索引的存储结构图（非聚簇索引的结构图在下面会讲解），在叶子节点的数据从左到右是按照数据从小到大排序的。每个节点都是有双向指针的，并且最后一个节点的尾指针指向第一个节点的地址。下面从两个方面说下B+Tree的索引

• 一个索引表能存放多少数据？

从上图可以看出，一个非叶子节点存放的数据由两部分组成=索引值+指针，并且每两个索引值之间都会有一个指针，那么假设索引是int类型（8个字节）,指针占用6个字节，所以假设一个索引占8个字节，那么第一个节点包含的索引个数 ： 16*1024/14=1170，那么第一个节点存放1170个索引，对应的也存在1170个指向下个节点的指针，那么假设第二层节点都满了，一共有1170个节点，假设表中的每行数据为1k(正常互联网中的每行数据要小于等于1K),那么叶子节点每个节点能存16条数据，那么对于一个2层高度的树，可以存放的数据数量：1170*1170*16=2千万，通过这个数据，我们可以得到，对于一个2千万数据的表，查找一个数据，最多进行3次IO,这样的效率还是很高的。

• 为什么 索引都是从小到大排序，并且每个叶子节点都是由双向指针连接？

  在实际应用中，经常会出现返回查询，例如上图中，我们要查询  15-30之间的数据，那么此时第一次读取到15,18的这个节点，此时如果没有这个双向指针，mysql只能重新从第一层节点读取，这样又要进行3次I/O。可想而知这样的相率会大大降低，但是如果有了这个指针（索引按照顺序排序的），就可以直接读取下个节点的指针，这样的效率可想而知

• 聚簇索引与非聚簇索引

聚簇索引与非聚簇索引的最大区别就是，聚簇索引的叶子节点存放的是数据，而聚簇索引的叶子节点存放的是聚簇索引的值，这样就表明二级索引查询到的值是主键索引的值，还需要进行一次回表查询。如下图，聚簇索引与非聚簇索引的存储结构

• 联合索引的存储结构

联合索引也是在工作中必须要掌握的一个创建索引的技巧，能使用联合索引的尽量创建联合索引，如下图，可见联合索引的叶子节点也是按照顺序排序的，排序的规则是按照联合索引的索引顺序依次比较。关于联合索引的其它知识点，后续会在讲解。

• hash索引

在创建索引的时候，可以选择hash索引，但是正常情况下都是btree，hash索引是在存储的时候，经过hash计算的到的值，那么在查询时，只要得到hash后的值，那么只要进行一次查询即可，所以查询的效率是非常高的。但是hash索引是存在弊端的，首先忽略hash冲突的问题（mysql底层对hash计算做了处理，基本上不会出现hash冲突的），主要是hash索引不是按照循序存储的，所以是不支持范围查询的，在建立索引的时候需要注意，如果索引的字段确定是等值查询，那么是可以建立hash索引

三：不同存储引擎存储磁盘的文件类型

在mysql数据库中，存在不同的存储引擎，常用的是InnoDB和MyISAM

图为MyISAM的存储在磁盘的文件，MYI存放的是索引文件，MYD存放的数据文件，

因为MyISAM的索引和文件存储是分开的，如下图

图为InnoDB的存储文件

四：关于索引相关的问题

        1、为什么建议主键为int类型递增？

在建立表的时候，都会建议指定主键，并且建议使用int类型递增的，即使没有指定主键索引，mysql也会指定一个rowId作为主键，主要有以下几点（与UUID作为主键的比较）：

• 索引都是按照顺序排序存储的，int类型的主键在比较过程中，效率要比UUID高
• UUID的存储空间要比int的大
• UUID在创建的时候不是递增的，索引都是按照顺序存放的，这样就导致后面的id要小于前面的，那么在存储时就会导致索引分裂重组，这是非常耗时的操作。在新增修改数据，都是先维护好对应的的索引，然后再修改对应的数据

        2、为什么非聚簇索引叶子节点存放的是聚簇索引？

从上面的内容可知，非聚簇索引的叶子结点存放的是主键值，这样无疑会损失1、2次的磁盘I/O回表查询的性能，但是收益是很多的，主要有以下两点：

• 一致性，如果每个索引表的叶子节点都存放数据，那么就需要考虑每个索引表的数据一致性，这样肯定会增加维护的成本和性能
• 节省空间，如果每个索引表的叶子结点都存放数据，那么相当于有N索引表，相比现在多了N倍的存储空间，成本也是很大的

总结

上文介绍了索引的结构，包括mysql为什么选择B+Tree作为索引存储结构，通过本文，我相信大家对mysql的索引有了全面的了解，后面会介绍更多的mysql知识