B树，B+树的详细MYSQL索引

纯情程序猿

已于 2022-04-19 20:28:05 修改

阅读量687

点赞数

分类专栏： DataBase 文章标签：数据结构 mysql

于 2022-04-19 20:26:45 首次发布

本文链接：https://blog.csdn.net/weixin_46075832/article/details/124282529

版权

DataBase 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

1.索引定义

官方定义: 一种帮助MySQL提高查询效率的数据结构
索引的优点:
1、大大加快数据查询速度
索引的缺点:
1、维护索引需要耗费数据库资源
2、索引需要占用磁盘空间
3、当对表的数据进行增删改的时候，因为要维护索引，速度会受到影响

2.索引分类

- a.主键索引 
		设定为主键后数据库会自动建立索引，innodb为聚簇索引 主键索索引值不能为空
 
- b.普通索引 单值索引 单列索引
		即一个索引只包含单个列，一个表可以有多个单列索引 

- c.唯一索引   
		索引列的值必须唯一，但允许有空值

- d.复合索引
		即一个索引包含多个列

- e.Full Text 全文索引 (My5.7版本之前 只能由于MYISAM引擎)
		全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR、TEXT类型列上创建。MYSQL只有MYISAM存储引擎支持全文索引

3.索引的基本操作

1.主键索引 自动创建

--建表 主键自动创建主键索引
create table t_user(id varchar(20) primary key,name varchar(20));
--查看索引
show index from t_user;

2.单列索引(普通索引|单值索引)
--建表时创建
create table t_user(id varchar(20) primary key,name varchar(20),key(name));  
	'注意:随表一起建立的索引索引名同列名一致'
	  
--建表后创建
create index nameindex on t_user(name);

--删除索引
drop index 索引名 on 表名

3.唯一索引
--建表时创建
 create table t_user(id varchar(20) primary key,name varchar(20),unique(name));
 
--建表后创建
	create unique index nameindex on t_user(name);

4.复合索引
---建表时创建
     create table t_user(id varchar(20) primary key,name varchar(20),age int,key(name,age));
 
--建表后创建
 create index nameageindex on t_user(name,age);

4.索引的底层原理

1.思考
---建表
create table t_emp(id int primary key,name varchar(20),age int);

--插入数据
insert into t_emp values(5,'d',22);
insert into t_emp values(6,'d',22);
insert into t_emp values(7,'e',21);
insert into t_emp values(1,'a',23);
insert into t_emp values(2,'b',26);
insert into t_emp values(3,'c',27);
insert into t_emp values(4,'a',32);
insert into t_emp values(8,'f',53);
insert into t_emp values(9,'v',13);

--查询
select * from t_emp;

4.1B+树

# 1.为什么上面数据明明没有按顺序插入,为什么查询时却是有顺序呢?
- 原因是:mysql底层为主键自动创建索引,一定创建索引会进行排序
- 也就是mysql底层真正存储是这样的
- 为什么要排序呢?因为排序之后在查询就相对比较快了 如查询 id=3的我只需要按照顺序找到3就行啦(如果没有排序大海捞针,全靠运气😸!)

# 2.为了进一步提高效率mysql索引又进行了优化
-  就是基于页的形式进行管理索引
-  如 查询id=4的 直接先比较页 先去页目录中找,再去 数据目录中找

# 3.上面这种索引结构称之为B+树数据结构,那么什么是B+树呢?
- 参考资料: https://www.cnblogs.com/lianzhilei/p/11250589.html

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

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

B+Tree相对于B-Tree有几点不同：

非叶子节点只存储键值信息。
所有叶子节点之间都有一个链指针。
数据记录都存放在叶子节点中。

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为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操作。

B+树相比B树的优势：
　　1.单一节点存储更多的元素，使得查询的IO次数更少；
　　2.所有查询都要查找到叶子节点，查询性能稳定；
　　3.所有叶子节点形成有序链表，便于范围查询。

4.2聚簇与非聚簇

# 聚簇索引和非聚簇索引
- 聚簇索引： 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据
- 非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

注意:在innodb中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找。

InnoDB中

InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）
聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会**隐式定义一个主键（类似oracle中的RowId）**来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

MYISAM

MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

# 1.使用聚簇索引的优势
- 问题: 每次使用辅助索引检索都要经过两次B+树查找，看上去聚簇索引的效率明显要低于非聚簇索引，这不是多此一举吗？聚簇索引的优势在哪？
	
- 1.由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中（缓存器），再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。

- 2.辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。

# 2.聚簇索引需要注意什么?
- 当使用主键为聚簇索引时，主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出线新增加记录的uuid，会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源。
- 建议使用int类型的自增，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到IO操作读取到的数据量。

# 3. 为什么主键通常建议使用自增id
- 聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

# 3. 什么情况下无法利用索引呢?
- 1. 查询语句中使用LIKE关键字
			在查询语句中使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用。

- 2.查询语句中使用多列索引 
			多列索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。(遵循最左匹配原则) 官方为了更好提高查询效率会动态调整索引位置以便利用索引
	对于 id name age的复合索引， where (id age name) 通过动态调整索引的位置可以使用到索引		


- 3.查询语句中使用OR关键字
			查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那么查询中将使用索引。如果OR前后有一个条件的列不是索引，那么查询中将不使用索引。
			
- 4.查询语句中使用AND关键字			
		查询语句And关键字时，如果AND前后的两个条件的列只要有一个是索引，查询中将使用索引。

后面这部分主要转载自： https://www.cnblogs.com/lianzhilei/p/11250589.html

★B-Tree,B+Tree

1.前言

B树的出现是为了弥合不同的存储级别之间的访问速度上的巨大差异，实现高效的 I/O。平衡二叉树的查找效率是非常高的，并可以通过降低树的深度来提高查找的效率。但是当数据量非常大，树的存储的元素数量是有限的，这样会导致二叉查找树结构由于树的深度过大而造成磁盘I/O读写过于频繁，进而导致查询效率低下。另外数据量过大会导致内存空间不够容纳平衡二叉树所有结点的情况。B树是解决这个问题的很好的结构

概念

首先，B树不要和二叉树混淆，在计算机科学中，B树是一种自平衡树数据结构，它维护有序数据并允许以对数时间进行搜索，顺序访问，插入和删除。B树是二叉搜索树的一般化，因为节点可以有两个以上的子节点。[1]与其他自平衡二进制搜索树不同，B树非常适合读取和写入相对较大的数据块（如光盘）的存储系统。它通常用于数据库和文件系统。

2.B树定义

**B树是一种平衡的多分树，通常我们说m阶的B树，它必须满足如下条件： **

- 1. 根节点的子节点数量：[2,m]
- 2. 内部节点的子节点数量：[ceil(m/2), m]
- 3. **(根节点、内部节点的键的数量：键 = 子节点 - 1)**

- 4. 一个结点当中：指向子节点指针和关键字的位置关系是：(指针1) 关键字A (指针2) 关键字B (指针3)
   	每个键的值 > 左指针的结尾孩子的键的值  
   	每个键的值 < 右指针的开头孩子的键的值 
   	
- 5. 叶子结点位于同一层
- 6. 结点中的关键字从小到大排列
- 7. 结点中关键字不重复

2.1什么是B树的阶

B树中一个节点的子节点数目的最大值，用m表示，假如最大值为10，则为10阶，如图

所有节点中，节点【13,16,19】拥有的子节点数目最多，四个子节点（灰色节点），所以可以定义上面的图片为4阶B树，现在懂什么是阶了吧

2.2什么是根节点？

节点【10】即为根节点，特征：根节点拥有的子节点数量的上限和内部节点相同，如果根节点不是树中唯一节点的话，至少有俩个子节点（不然就变成单支了）。

在m阶B树中（根节点非树中唯一节点），那么有关系式2<= M <=m，M为子节点数量；

包含的元素数量 1<= K <=m-1,K为元素数量。

2.3什么是内部节点？

节点【13,16,19】、节点【3,6】都为内部节点，特征：内部节点是除叶子节点和根节点之外的所有节点，拥有父节点和子节点。

假定m阶B树的内部节点的子节点数量为M，则一定要符合（m/2）<= M <=m关系式，包含元素数量M-1；

包含的元素数量（m/2）-1<= K <=m-1,K为元素数量。m/2向上取整。

2.4什么是叶子节点？

节点【1,2】、节点【11,12】等最后一层都为叶子节点，叶子节点对元素的数量有相同的限制，但是没有子节点，也没有指向子节点的指针。特征：在m阶B树中叶子节点的元素符合（m/2）-1<= K <=m-1。

3.插入

针对m阶高度h的B树，插入一个元素时，首先在B树中是否存在，如果不存在，即在叶子结点处结束，然后在叶子结点中插入该新的元素。

若该节点元素个数小于m-1，直接插入；
若该节点元素个数等于m-1，引起节点分裂；以该节点中间元素为分界，取中间元素（偶数个数，中间两个随机选取）插入到父节点中；
重复上面动作，直到所有节点符合B树规则；最坏的情况一直分裂到根节点，生成新的根节点，高度增加1；

上面三段话为插入动作的核心，接下来以5阶B树为例，详细讲解插入的动作；

5阶B树关键点:

2<=根节点子节点个数<=5
3<=内节点子节点个数<=5
1<=根节点元素个数<=4
2<=非根节点元素个数<=4

图（1）插入元素【8】后变为图（2），此时根节点元素个数为5，不符合 1<=根节点元素个数<=4，进行分裂（真实情况是先分裂，然后插入元素，这里是为了直观而先插入元素，下面的操作都一样，不再赘述），取节点中间元素【7】，加入到父节点，左右分裂为2个节点，如图（3）

接着插入元素【5】，【11】，【17】时，不需要任何分裂操作，如图（4）

插入元素【13】

节点元素超出最大数量，进行分裂，提取中间元素【13】，插入到父节点当中，如图（6）

接着插入元素【6】，【12】，【20】，【23】时，不需要任何分裂操作，如图（7）

插入【26】时，最右的叶子结点空间满了，需要进行分裂操作，中间元素【20】上移到父节点中，注意通过上移中间元素，树最终还是保持平衡，分裂结果的结点存在2个关键字元素。

插入【4】时，导致最左边的叶子结点被分裂，【4】恰好也是中间元素，上移到父节点中，然后元素【16】,【18】,【24】,【25】陆续插入不需要任何分裂操作

最后，当插入【19】时，含有【14】,【16】,【17】,【18】的结点需要分裂，把中间元素【17】上移到父节点中，但是情况来了，父节点中空间已经满了，所以也要进行分裂，将父节点中的中间元素【13】上移到新形成的根结点中，这样具体插入操作的完成。

4.删除

首先查找B树中需删除的元素,如果该元素在B树中存在，则将该元素在其结点中进行删除；删除该元素后，首先判断该元素是否有左右孩子结点，如果有，则上移孩子结点中的某相近元素(“左孩子最右边的节点”或“右孩子最左边的节点”)到父节点中，然后是移动之后的情况；如果没有，直接删除。

某结点中元素数目小于（m/2）-1,(m/2)向上取整，则需要看其某相邻兄弟结点是否丰满；
如果丰满（结点中元素个数大于(m/2)-1），则向父节点借一个元素来满足条件；
如果其相邻兄弟都不丰满，即其结点数目等于(m/2)-1，则该结点与其相邻的某一兄弟结点进行“合并”成一个结点；

接下来还以5阶B树为例，详细讲解删除的动作；

关键要领，元素个数小于 2（m/2 -1）就合并，大于4（m-1）就分裂

如图依次删除依次删除【8】,【20】,【18】,【5】

首先删除元素【8】，当然首先查找【8】，【8】在一个叶子结点中，删除后该叶子结点元素个数为2，符合B树规则，操作很简单，咱们只需要移动【11】至原来【8】的位置，移动【12】至【11】的位置（也就是结点中删除元素后面的元素向前移动）

下一步，删除【20】,因为【20】没有在叶子结点中，而是在中间结点中找到，咱们发现他的继承者【23】(字母升序的下个元素)，将【23】上移到【20】的位置，然后将孩子结点中的【23】进行删除，这里恰好删除后，该孩子结点中元素个数大于2，无需进行合并操作。

最后一步删除【5】，删除后会导致很多问题，因为【5】所在的结点数目刚好达标，刚好满足最小元素个数（ceil(5/2)-1=2）,而相邻的兄弟结点也是同样的情况，删除一个元素都不能满足条件，所以需要该节点与某相邻兄弟结点进行合并操作；首先移动父结点中的元素（该元素在两个需要合并的两个结点元素之间）下移到其子结点中，然后将这两个结点进行合并成一个结点。所以在该实例中，咱们首先将父节点中的元素【4】下移到已经删除【5】而只有【6】的结点中，然后将含有【4】和【6】的结点和含有【1】,【3】的相邻兄弟结点进行合并成一个结点。

也许你认为这样删除操作已经结束了，其实不然，在看看上图，对于这种特殊情况，你立即会发现父节点只包含一个元素【7】，没达标（因为非根节点包括叶子结点的元素K必须满足于2=<K<=4，而此处的K=1），这是不能够接受的。如果这个问题结点的相邻兄弟比较丰满，则可以向父结点借一个元素。而此时兄弟节点元素刚好为2，刚刚满足，只能进行合并，而根结点中的唯一元素【13】下移到子结点，这样，树的高度减少一层。

6.磁盘IO与预读

计算机存储设备一般分为两种：**内存储器(main memory)和外存储器(external memory)。 **

内存储器为内存，内存存取速度快，但容量小，价格昂贵，而且不能长期保存数据(在不通电情况下数据会消失)。

外存储器即为磁盘读取，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家参考：

millisecond           1ms (毫秒)         1毫秒=0.001秒=10-3秒
microsecond           1μs (微秒)         1微秒=0.000001=10-6秒
nanosecond            1ns (纳秒)         1纳秒=0.0000000001秒=10-9秒

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

事实1 ：不同容量的存储器，访问速度差异悬殊。

磁盘(ms级别) << 内存(ns级别)， 100000倍
若内存访问需要1s，则一次外存访问需要一天
为了避免1次外存访问，宁愿访问内存100次...所以将最常用的数据存储在最快的存储器中

事实2 ： 从磁盘中读 1 B，与读写 1KB 的时间成本几乎一样

从以上数据中可以总结出一个道理，索引查询的数据主要受限于硬盘的I/O速度，查询I/O次数越少，速度越快，所以B树的结构才应需求而生；B树的每个节点的元素可以视为一次I/O读取，树的高度表示最多的I/O次数，在相同数量的总元素个数下，每个节点的元素个数越多，高度越低，查询所需的I/O次数越少；假设，一次硬盘一次I/O数据为8K，索引用int(4字节)类型数据建立，理论上一个节点最多可以为2000个元素，2000 * 2000 * 2000 = 8000000000，80亿条的数据只需3次I/O（理论值），可想而知，B树做为索引的查询效率有多高；

另外也可以看出同样的总元素个数，查询效率和树的高度密切相关

B树的高度

一棵含有N个总关键字数的m阶的B树的最大高度是多少？

log（m/2）(N+1)/2 + 1 ，log以（m/2）为低，(N+1)/2的对数再加1

算法如下

7.B+树定义

B+树的特征：

# 定义
- 1. 有m个子树的中间节点包含有m个元素（B树中是m-1个元素），每个元素不保存数据，只用来索引；

- 2. 所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，
	 且叶子结点本身依关键字的大小自小而大的顺序链接。 (B树的叶子节点并没有包括全部需要查找的信息)；
	 
- 3. 所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。 
	(而B 树的非终节点也包含需要查找的有效信息)；

为什么说B+树比B树更适合数据库索引？

1）B+树的磁盘读写代价更低

B+树的内部结点并没有指向关键字具体信息的指针(叶节点除外)。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了；

2）B+树查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当；

3）B+树便于范围查询（最重要的原因，范围查找是数据库的常态）

B树在提高了IO性能的同时并没有解决元素遍历的我效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低；不懂可以看看这篇解读 -> 范围查找

补充：B树的范围查找用的是中序遍历，而B+树用的是在链表上遍历；

B+树如下：