mysql数据库索引底层原理---------B+树(内附原理图)

mysql索引底层原理

B+树的原理

这里主要介绍的是B+树中，两种存储引擎MyISAM和InnoDB的区别（需要具备一定的数据结构知识）。

1.什么是B+树

B+树是一种树的数据结构，通常用于数据库和操作系统的文件系统中。B+树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。
1.首先插入第一个元素为4，再插入第二个元素5，因为树形结构的特性，5大于4，所以5是存储在右边的。
2.接着再插入第三个元素3，因为3小于4，所以存储在左边，当插入第四个元素6的时候，会进行向上分裂。下面会提到为什么会分裂。
3.分裂后5的索引信息就被向上分裂。

2.阶数m(分裂原因)

阶数m，阶数代表每个节点能存放多少个key，也就是索引元素，通常是主键，每个节点最多能有m-1个key，当节点的key满了时候，再存放下一个key的时候，节点就会从左往右数第(m/2)向下取整后+1 的那个位置的索引会被向上分裂。
比如：m=5，当一个节点要添加第五个key的时候，本来应该放在第三个位置的key会被向上分裂，但是元数据信息还是存放在叶子结点，只是索引信息被向上分裂而已。
(而上图所示的阶数为4)

3.节点

节点就是一组索引信息，比如上图，3和4为一个节点，5和6为一个节点。

4.叶子结点

叶子结点存放索引信息以及data域。data域就是存放一些和数据相关的信息

5.非叶子节点

非叶子节点只存放索引信息，就是在搜索数据的时候使用。
比如：像上图所示，我要找到3，首先从根节点开始找，就是从5开始找，然后比较，因为3比5小，所以去它的左子节点找，也就是3和4所在的节点，然后再进行比较，就找到了3这个索引信息。

6.MyISAM:

树的叶子节点中的data域存放的是数据记录的地址。
如开头那幅图所示：
在索引搜索的时候,首先按照B+Tree搜索算法找到索引,如果找到了指定的key,则取出其data域的值,然后以data域的值，也就是地址去读取相应的数据记录，如果没有主键，key就是由地址转换过来的。这被称为"非聚簇索引"，因此 MyISAM,索引文件和数据文件是分离的。
因为叶子节点中只存储着地址（也就是该值所对应的那条数据所在地址），找到对应的地址，然后去地址中取出数据。所以在这主键索引和辅助索引并没有太多区别，就算没有主键也是一样的。

7.InnoDB:

1.其数据文件本身就是索引文件。它的表数据文件本身就是按B+树组织的一个索引结构,叶子节点的data域保存了完整的数据记录。这个索引的key通常是表的主键。这被称为聚簇索引，也叫主键索引。InnoDB存储引擎会自动选一列作为聚簇索引,也叫主键索引。
关于 InnoDB 选择哪个列作为聚簇索引存储，大概的优先级为：

1. 如果定义了主键（ primary key ），则使用主键作为聚簇索引
2. 如果没有定义主键，则选择第一个不包含 NULL的唯一键的列做聚簇索引。
3. 如果都没有，系统则会生成一个6字节的列叫row_id做聚簇索引。(会影响插入效率，下面“不设置主键会怎么样”中会提到)

2.而其余的索引，比如：普通索引都作为辅助索引
辅助索引它的叶子节点只保存了行的主键值和指向对应行的 “书签” , 一般指向的是主键索引，然后再根据主键索引来搜索,直接找到key所在的节点就能取出数据;也就是说在根据辅助索引查找时,则需要先找到索引元素，然后取出主键值,再走一遍主键索引，最后得到数据，这叫做回表操作。
如图：InnoDB的索引类型

3.此外 innoDB 实现了覆盖索引, 就是说在树的叶子节点除了保存该行的键值以外还保存了对应索引列的值，如果不需要额外数据的话则不需要另外对聚簇索引中的数据进行磁盘 IO。
例如说：现在我在user表中为这两个字段创建了普通索引(username,age),在查询数据的时侯: select username, age from user where username='Java’and age=22。就是说所要查询的列数据都是保存在叶子节点中的!所以不需要进行回表操作。
4.还有在设计表的时候,不建议使用非单调递增的字段作为主键,这样会造成主键索引节点的频繁分裂

8.为什么是整型主键？

在聚簇索引中。如果碰到不规则数据插入时，会造成频繁的节点分裂(因为索引要排好序)，插入速度比较慢。所以聚簇索引的主键值应尽量是连续增长的值，而不是随机值，否则会造成大量的节点分裂与节点移动。
故对于InnoDB的主键，尽量用整型，而且是递增的整型。这样在存储/查询上都是非常高效的，
所以说索引是需要排好序的，相较于整型类型的比较，字符串类型的大小比较还要进行ASCII码转换。

9.不设置主键会怎么样？

在关于innoDB的段落中提到——如果都没有，系统则会生成一个6字节的列叫row_id做聚簇索引，在所有用ROW_ID列的表，共享同一个被保存在数据字典中的全局序列数，且下一个将要使用的值被保存在系统表空间的page 7。 而且这个全局序列计数器被InnoDB的系统级数据字典锁，保护它的线程安全问题，那么这个是全局锁，所有操作都是串行操作，就非常的影响插入性能。
但是对于MyISAM存储引擎来说就无所谓了（上面关于MyISAM段落中有提到）

10.主键索引

如开头图的第四步所示，那个就是主键索引。索引信息存放的是主键

11.辅助索引

如图所示，辅助索引它的叶子节点只保存了行的主键值和指向对应行的 “书签” , 一般指向的是主键索引