数据库学习案例20240318-innodb-索引index-mysql数据结构

1 概述

什么是索引？索引就是排好序的数据结构，可以快速的查找我们想到的数据，而mysql数据存储在B树索引对应的叶节点上面。（都是主键组织的，如果不存在主键则包含隐藏主键，或者唯一键组织）

mysql> show variables like '%page_size%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
| large_page_size  | 0     |
+------------------+-------+
2 rows in set, 1 warning (0.00 sec)

关键总结：

1 树的level越低则产生io读取的次数越低。

2 索引就是排序好的结构。

3 page是mysql分配存储结构最小的单位。一般大小为16kb

2 聚集索引和非聚集索引

聚集索引：叶子节点包含完整的数据记录。

非聚集索引：叶子节点只包含了索引key值+直针。

2 数据结构类型：

mysql数据结构大概可以分为：

二叉树、红黑树、hash表、b tree、b+tree

1、二叉树:

它是单边增长的链表结构，这样会造成树的高度比较大、如下图

有一个原则：大的树放在右边，小的放在左边。

 
 这个时候，如果我们要搜索6，就需要从1~6走一遍，需要走6个节点才能查到我们想要的数据产生6次IO。，太浪费查询时间，

 
 2、红黑树：

在二叉树的基础上多了树平衡，也叫二叉平衡树，不像二叉树那样极端的情况会往一个方向发展。

 
同样的我们查找6，红黑树中我们只需要3个节点(2-4-6),但是mysql索引的数据结构并不是红黑树，而是 b+tree，因为如果数据量大了之后，树的高度就会很大

3 、b tree

在红黑树的基础上，每个节点可以多存放数据

 
相比于红黑树，我们同样的查询6，b 树只需要2个节点(4-6)，时间又快了一点

oracle索引结构

目标：查询Frank的工资salary

　　建立索引：create index emp_name_idx on emp(name);
 

4、b+tree

表数据文件.ibd本身就是按B+TREE组织的一个索引结构文件。（只有ibd）

在B+树中是只有叶子结点会存储数据，而且所有叶子结点会形成一个链表。而在InnoDB中维护的是一个双向链表

b tree 的增强版、它的结构是这样的

这时候我们会发现，非叶子节点和叶子节点的数字是重复的，这是因为b+tree的数据都是储存在叶子节点上，(Mysql底层用的就是B+tree)，这些数据统一放在了mysql文件下的dev文件中，感兴趣的可以再私下研究，需要注意的一点是，MyISAM引擎和InnnoDB引擎的文件个数是不一样的，前者是三个文件(.frm是存放表结构数据、MYD是表数据、MYI是存放索引)，后者是两个文件(.frm文件时存放表结构数据、.ibd是储存数据和索引)

1 非叶子节点不存储数据，只存储冗余的索引，可以放更多的索引。page。

2 叶子节点包含所有索引字段，并且包含下一个节点的指针。

本文主要探讨 MySQL 的默认存储引擎 InnoDB 的索引结构。

索引常见的类型有哈希索引，有序数组索引，二叉树索引，跳表等等。

InnoDB的索引结构
在InnoDB中是通过一种多路搜索树——B+树实现索引结构的。在B+树中是只有叶子结点会存储数据，而且所有叶子结点会形成一个链表。而在InnoDB中维护的是一个双向链表。

你可能会有一个疑问，为什么使用 B+树 而不使用二叉树或者B树？

   首先，我们知道访问磁盘需要访问到指定块中，而访问指定块是需要 盘片旋转 和 磁臂移动 的，这是一个比较耗时的过程，如果增加树高那么就意味着你需要进行更多次的磁盘访问，所以会采用n叉树。而使用B+树是因为如果使用B树在进行一个范围查找的时候每次都会进行重新检索，而在B+树中可以充分利用叶子结点的链表。

在建表的时候你可能会添加多个索引，而 InnDB 会为每个索引建立一个 B+树 进行存储索引。

比如这个时候我们建立了一个简单的测试表

create table test(
  id int primary key,
  a int not null,
  name varchar,
  index(a)
)engine = InnoDB;

这个时候 InnDB 就会为我们建立两个 B+索引树，

一个是 主键 的 聚簇索引，另一个是 普通索引 的 辅助索引。

可以看到在辅助索引上面的叶子节点的值只是存了主键的值，而在主键的聚簇索引上的叶子节点才是存上了整条记录的值。

回表

所以这里就会引申出一个概念叫回表，比如这个时候我们进行一个查询操作

select name from test where a = 30;
我们知道因为条件 MySQL 是会走 a 的索引的，但是 a 索引上并没有存储 name 的值，此时我们就需要拿到相应 a 上的主键值，然后通过这个主键值去走 聚簇索引 最终拿到其中的name值，这个过程就叫回表。

我们来总结一下回表是什么？MySQL在辅助索引上找到对应的主键值并通过主键值在聚簇索引上查找所要的数据就叫回表。

索引维护

我们知道索引是需要占用空间的，索引虽能提升我们的查询速度但是也是不能滥用。

1. 比如我们在用户表里用身份证号做主键，
2. 那么每个二级索引的叶子节点占用约20个字节，
3. 而如果用整型做主键，则只要4个字节，
4. 如果是长整型（bigint）则是8个字节。
5. 也就是说如果我用整型后面维护了4个g的索引列表，那么用身份证将会是20个g。
6. 所以我们可以通过缩减索引的大小来减少索引所占空间。

当然B+树为了维护索引的有序性会在删除，插入的时候进行一些必要的维护(在InnoDB中删除会将节点标记为“可复用”以减少对结构的变动)。

page分裂

    比如在增加一个节点的时候可能会遇到数据页满了的情况，这个时候就需要做页的分裂，这是一个比较耗时的工作，而且页的分裂还会导致数据页的利用率变低，比如原来存放三个数据的数据页再次添加一个数据的时候需要做页分裂，这个时候就会将现有的四个数据分配到两个数据页中，这样就减少了数据页利用率。

覆盖索引

上面提到了 回表，而有时候我们查辅助索引的时候就已经满足了我们需要查的数据，这个时候 InnoDB 就会进行一个叫 覆盖索引 的操作来提升效率，减少回表。

比如这个时候我们进行一个 select 操作

select id from test where a = 1;
这个时候很明显我们走了 a 的索引直接能获取到 id 的值，这个时候就不需要进行回表，我们这个时候就使用了 覆盖索引。

简单来说 覆盖索引 就是当我们走辅助索引的时候能获取到我们所需要的数据的时候不需要再次进行回表操作的操作。

联合索引

这个时候我们新建一个学生表

CREATE TABLE `stu` (
  `id` int(11) NOT NULL,
  `class` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `class_name` (`class`,`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

我们使用 class(班级号) 和 name 做一个 联合索引，你可能会问这个联合索引有什么用呢？我们可以结合着上面的 覆盖索引 去理解，比如这个时候我们有一个需求，我们需要通过班级号去找对应的学生姓名 。

select name from stu where class = 102;
这个时候我们就可以直接在 辅助索引 上查找到学生姓名而不需要再次回表。

总的来说，设计好索引，充分利用覆盖索引能很大提升检索速度。

最左前缀原则

这个是以 联合索引 作为基础的，是一种联合索引的匹配规则。

这个时候，我们将上面的需求稍微变动一下，这时我们有个学生迟到，但是他在门卫记录信息的时候只写了自己的名字张三而没有写班级，所以我们需要通过学生姓名去查找相应的班级号。

select class from stu where name = '张三';

这个时候我们就不会走我们的联合索引了，而是进行了全表扫描。

为什么？因为 最左匹配原则。我们可以画一张简单的图来理解一下。

我们可以看到整个索引设计就是这么设计的，所以我们需要查找的时候也需要遵循着这个规则，如果我们直接使用name，那么InnoDB是不知道我们需要干什么的。

当然最左匹配原则还有这些规则：

全值匹配的时候优化器会改变顺序，也就是说你全值匹配时的顺序和原先的联合索引顺序不一致没有关系，优化器会帮你调好。
索引匹配从最左边的地方开始，如果没有则会进行全表扫描，比如你设计了一个(a,b,c)的联合索引，然后你可以使用(a),(a,b),(a,b,c) 而你使用 (b),(b,c),(c)就用不到索引了。
遇到范围匹配会取消索引。比如这个时候你进行一个这样的 select 操作
select * from stu where class > 100 and name = '张三';
这个时候 InnoDB 就会放弃索引而进行全表扫描，因为这个时候 InnoDB 会不知道怎么进行遍历索引，所以进行全表扫描。

索引下推

我给你挖了个坑。刚刚的操作在 MySQL5.6 版本以前是需要进行回表的，但是5.6之后的版本做了一个叫 索引下推 的优化。

select * from stu where class > 100 and name = '张三';
如何优化的呢？因为刚刚的最左匹配原则我们放弃了索引，后面我们紧接着会通过回表进行判断 name，这个时候我们所要做的操作应该是这样的

但是有了索引下推之后就变成这样了，此时 "李四" 和 "小明" 这两个不会再进行回表。

因为这里匹配了后面的name = 张三，也就是说，如果最左匹配原则因为范围查询终止了，InnoDB还是会索引下推来优化性能。

一些最佳实践

哪些情况需要创建索引？
频繁作为查询条件的字段应创建索引。
多表关联查询的时候，关联字段应该创建索引。
查询中的排序字段，应该创建索引。
统计或者分组字段需要创建索引。
哪些情况不需要创建索引？
尽量选择区分度高的列作为索引。
不要对索引进行一些函数操作，还应注意隐式的类型转换和字符编码转换。
尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。
多考虑覆盖索引，索引下推，最左匹配。
————————————————

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。原文链接：https://blog.csdn.net/weixin_39668571/article/details/111669730