MySql索引底层原理_非叶子结点不存储数据-CSDN博客

本文链接：https://blog.csdn.net/manba123456/article/details/105983035

一、索引

说到索引，大家理解的肯定是把它当做字典的偏旁或者一本书的目录页，更好的指引我们找到内容。可以去这样理解，但这样太表面了。其实，索引是帮助MySql高效获取数据的排好序的数据结构。(如果没有索引则需要一条一条挨个磁盘IO查找)。我们先讲讲数据结构。

二、索引数据结构

2.1 Hash表

Hash索引(hash index)基于哈希表实现，对于每一行数据，存储引擎都会对所有的索引列计算一个hash码(hash code)，哈希码是一个较小的值，哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。如果hash码一样则会采用链表的形式存储，类似于HashMap，Hash索引适用于精准查询。

假如有以下表：

如果我们在name列建立索引，name数据库会使用哈希算法计算name列每一行数据的hash值并进行存储。因为Hash值是随机计算的，所以可能存在冲突，假如计算结果如下:

我们有一条SELECT id,name,age FROM t_user WHERE name=‘石小添’; 这样的一条SQL可以直接对石小添按哈希算法算出来一个Hash值，通过该值找到对应的记录指针，通过记录指针找到表中的哪一行数据，最后比较name是否为石小添，以保证就是要查找的行。
但是如果我们有 SELECT id,name,age FROM t_user WHERE name>‘石小添’; 这样的一条SQL则无能为力，因为Hash表支持快速的精确查询，但是不支持范围查询。

2.1 二叉树

二叉树基本特点：逐条插入数据时，左边的子节点小于父节点，右边的子节点大于父节点，这就是排序。

假设有一张表如上图有两个字段，当前这张表没有索引。如果我们执行一条Sql select * from table where Col2 = 89;它是要从上往下依次查询6次才能找到结果。如果在Col2字段建立索引，将该字段值用二叉树存储，那我们只需要查询2次即可得到结果。每个节点存的值类似于(key，value)，key：该索引字段的值。value：该字段所在行数据存在磁盘上文件地址指针。但是，如果我们拿字段Col1(单边增长)作为索引，一个一个逐渐插入数据(如果有索引，先维护好索引结构再插入数据)时，其得到的结果是这样的：

这时你就会发现成了链表结构，该索引就毫无意义了，跟未建立索引查询次数是一样的。所以，Mysql索引是没有使用二叉树这种数据结构的。

2.2 红黑树(二叉平衡树)

红黑数又叫二叉平衡树。红黑树就很好解决了上面二叉树这种存储单边增长数据出现的情况，当逐条插入时底层的算法会自动平衡，上述Col1字段逐条插入后结果是这样的：

这时候执行 select * from table where Col1 = 6 就只要执行3次，这种情况下的索引，查询性能上比二叉树更加优化了。但是，如果数据量非常庞大，有五百万条，2^n=五百万。n为红黑树高度，而你需要查找的数据刚好在最下面的叶子节点，那效率可想而知。所以MySql索引也没有用这种结构存储索引。

2.3 B-Tree(B树)

data可理解为：该索引字段所在行数据存在磁盘上文件地址指针(Myisam引擎) 或该索引字段所在行所有字段数据。

B-Tree特点：

1，叶节点具有相同深度，叶节点指针为空

2，所有索引元素不重复

3，节点中的数据索引从左到右递增排列

4，也满足左边的子节点小于父节点，右边的子节点大于父节点(排序)

B-Tree结构就解决了红黑树问题，横向扩展，能存储更多的数据，就能控制好树的高度。Mysql索引就是利用B-Tree数据结构，稍微改造了下成为B+Tree。

2.4 B+Tree(Mysql索引所用的数据结构)

B+Tree是由B-Tree改变而来，其结构是这样的：

B+Tree特点：

1，非叶子节点不存储data，只存储索引(冗余)，这样可以放更多的索引

2，叶子节点包含所有索引字段

3，叶子节点用指针连接，提高区间访问的性能

4，也满足左边的子节点小于父节点，右边的子节点大于父节点(排序)

总结：相对于B-Tree。B+Tree，叶子节点存储了完整的索引，而非叶子节点只存储了索引(没存data)，而且索引还是有重复的，这样做的目的是为了一个节点上存储更多索引。

为什么B+Tree非叶子结点不存data，只存索引(冗余)？

因为MySql在设计的时候，每个节点存储大小为16KB，可以通过语句 SHOW GLOBAL STATUS LIKE 'InnoDb_page_size 查询

假设我们用一个bigint类型的作为索引，接下来我们分析下：

一个bigint类型占8个字节，旁边的为指向下一个节点的指针地址值，MySql底层设计为6B左右以内，这一个索引占14B，

根节点就能存储16KB/14B=1170个索引，假设我们树的高度为3，则一共能存储 1170*1170*16条数据，这相对于B-Tree来说，在相同高度下，明显存储的数据多得多，这样做性能也明显提高了，mysql中用的B+Tree结构叶子节点是双向链表且头尾也存有互相指向对方的指针，这些指针的存在大大增加了查找性能(比如范围查找，排序查找)。

三、常用MySqly存储引擎

3.1 Myisam存储引擎索引实现

3.1.1 创建一个 Myisam存储引擎表

CREATE TABLE `tb_myisam` (
  `id` int(11) NOT NULL,
  `col1` varchar(255) DEFAULT NULL,
  `col2` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

大家都知道，数据库中数据是存在磁盘上的，tb_myisam表在磁盘上对应了三个文件：

一个myisam表创建之后在磁盘上会有三个文件frm，MYD，MYI维护：
frm：存储表结构
MYD：存储表数据
MYI：存储表中索引

非聚集索引：索引和数据分开两个文件存储

3.1.2 Myisam索引维护:

主键索引

Col1是主键，在Col1上建立索引。如果现在执行语句:select * from table where Col1=49;从根节点开始找，右子节点>=父节点。Myisam引擎data存储的是该索引字段所在行数据存在磁盘上文件地址指针,就这样快速找到数据。

非主键索引和主键索引差不多

3.2 InnoDB存储引擎索引实现(MySql默认)

3.2.1 创建一个 InnoDB存储引擎表

CREATE TABLE `tb_innodb` (
  `id` int(11) NOT NULL,
  `col1` varchar(255) DEFAULT NULL,
  `col2` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

InnoDB是MySql默认存储引擎，tb_InnoDB表在磁盘上对应了两个文件：

一个InnoDB表创建之后在磁盘上会有两个文件frm，ibd维护：
frm：存储表结构
ibd：存储表索引和数据

聚集索引：索引和数据存在同一个文件

3.2.2 InnoDB索引维护(主键索引)：我们在插入数据之前需要对索引维护之后才能插入成功

InnoDB存储引擎索引特点：

1，表数据文件本身就是按B+Tree组织的一个索引结构文件

2，InnoDB索引也叫聚集索引(索引和数据存在一个文件)，都存在存在tb_innodb.ibd文件，查询数据是只要过滤tb_innodb.ibd一个文件，其效率高于Myisam存储引擎。

3，InnoDB索引叶节点包含了完整的数据记录

4，为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？

因为Mysql在设计时表数据文件本身就是按B+Tree组织的一个索引结构文件，如果没有主键这个数据文件是组织不起来的。如果在建InnoDB表时没给主键，系统会自动从表字段选择一个唯一值的作为主键，如果没有，系统默认加一列(Rowid)作为主键，但我们是看不到的。那为什么推荐使用整型的自增主键呢？因为我们在用索引查数据时，会从根节点往下找，要比较左右节点的大小，很明显整型的比较效率要高于字符串(因为字符串在比较时还要先转换成ACSii码)，这样效率高得多。而且整型所占空间较小，节约空间。自增主键是为了在插入数据时更好的维护索引，不用大范围改变索引数据结构(B+Tree节点都是逐渐递增的，如果当前节点已经存满16KB，则会结构发生很大改变，会有性能开销)。如果索引是逐渐递增的插入，则不会发生这种情况。

3.2.3 InnoDB非主键索引