mysql 1级2级索引 mysql 二级索引原理

目录

• 索引的概念
• 索引的各种实现及优缺点
• Mysql中存储引擎的索引实现
• 联合索引数据结构

索引的概念

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

所以说索引是一种数据结构，用于帮助Mysqll高效查询的数据结构，就像是我们常提到的数组、链表等。

如下图所示，索引就像平常使用字典查询一样：

索引的各种实现及优缺点

可以实现的高效查询的数据结构有以下几种：

• 二叉树
• 红黑树
• Hash表
• B-Tree

接下来来逐个了解一下上述各个数据结构的优缺点，在介绍之前，先介绍个网页，该网页可以帮助学习数据结构和算法，其可以实现动态的数据结构变更，网址如下：

• 网址1
• 网址2

这里我们使用网址2，其打开界面在索引目录下有以下一些条目：

接下来就通过其提供工具来演示各个索引数据结构的缺点。

2. 二叉树。二叉树（Binary Tree）：每个结点至多只有两颗子树（即二叉树中不存在度大于2的结点），并且，二叉树的子树有左右之分，其次序不能任意颠倒。接下来我们依次向其二叉树中添加如下数据：8，6，9，4，2，11，7，其结果如下：



3. 每个节点都有左右子树之分，当查找一个数值时通过与每个节点的判断从而查找到指定数值，查询的时间复杂度为O(logn)。但是问题来了，假设我们的索引字段为递增的等差说数列，如：1，2，3，4 …，那么二叉树的结构如下：



4. 这里示例为5个数值，如果我们查询5那么需要比较5次，但是实际情况是表中数据会使成千上万，那么查询效率会明显降低（此时会由树结构退化成链表结构，其查询时间复杂度变为O(n)）。所以说使用二叉树这个数据结构作为索引结构明显是不合理的。
5. 红黑树。
   主要特征：

在每个节点上增加一个属性来表示节点的颜色，可以是红色，也可以是黑色。红黑树与AVL树类似，都是在插入和删除元素时，通过特定的旋转保持自身平衡，从而获得较高查找性能。

1. 约束条件：

节点只能是红色或者黑色；
根节点必须是黑色；
所有NIL节点都是黑色；
一条路径上不能出现相邻的两个红色节点；
在任何递归子树内，根节点到叶子节点的所有路径上包含的相同数目的黑色节点。

2. 接下来将1，2，3…9依次添加至红黑树中，如下：



3. 红黑树的特性解决了对于递增的数据导致成为线程查询，确实提高了查询效率，但是问题又来了，当数据量过大，那么红黑树的深度就变得很大(二叉树也存在这种问题)，其查询效率就会随着数据的量的增大而降低。问题也是随着数据的增加，查询的时间负责度也响应的增加。
5. Hash表。
   Hash表通过维护一个hash值与数据存储地址映射的关系表，在需要查询数据时，对查询的索引字段进行hash算法取值，通过hash表的映射关系直接定位数据的存储地址从而获取数据。



6. 举例说明:
   假如上图是某表的col1字段的索引，那么在查询col1字段值为36的数据，对365进行hash计算，其结果为0，那么通过Hash表的映射其数据地址为0x8F,通过存储地址获取到数据。需要注意的是hash值的查询时间复杂度是1，这个可以参照Java中数组，通过下标索引查找时间复杂度为1，因为数组在内存中是连续内存空间。
   固然，Hash表在查询数据时基本为O(1)(当然会有hash冲突问题)，但是这只是针对查找一个固定值，如果我们要查询A表中col1字段值>=36的呢？这时Hash表的查找效率就会大打折扣。所以hash表在固定值查找效率是非常高的，但当进行范围查找时就很鸡肋了。
7. B-Tree。
   约束：

叶节点具有相同的深度，叶节点的指针为空；
所有索引元素不重复；
节点中的数据索引从左到右递增排列。

2. 其结构如下：



3. B-Tree如上图所示，该树结构中节点不再是存储一个数据，而是存储的索引数据(15、56等数据)、索引对应的行数据(data)以及子节点的地址指针信息。相对于二叉树和红黑树，通过在节点中尽可能多的存储索引数据，从而可以减小树的深度值，查询效果会提升很多，但是还那个问题，当数据量多大，并且每行的数据也较大时，那么每个节点存储索引数据就不会太多，这样也会导致索引树的深度值过大，其查询效率也会随着数据量的增大而降低。
4. B+Tree。
   B+Tree(B-Tree变种)：

非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引;
叶子节点包含所有索引字段;
叶子节点用指针连接，提高区间访问的性能。

如上图所示：B+Tree通过在B-Tree数据结构的基础上进行改进，在树的非叶子节点只存储索引值和指向子节点的存储地址，并且允许值重复，而在叶子节点存储索引值对应的行数据，并且在叶子节点中使用双向循环链表(可用于范围查找，向前或者向后遍历)，这样在非叶子节点就可以存储更多的索引值，从而减小因数据量的增大从而导致树的深度过大带来查找效率降低的问题。

这里我们可以举个例子说明一下：

在mysql中每个节点的存储空间默认为16kb，可通过如下命令查看：

SHOW GLOBAL STATUS like 'Innodb_page_size’;

假设我们一行数据大小为1kb，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；再看非叶子节点，假设主键ID为bigint类型，那么长度为8字节，指针大小在Innodb源码中为6字节，一共就是14字节，那么个节点就可以存储

16kb * 1024 /14=1170

1170个(主键+指针)，那么一颗高度为2的B+树能存储的数据为：117016=18720条，一颗高度为3的B+树能存储的数据为：11701170*16=21902400（千万级条）。
其规律为高度为n，则存储的数量为:

存储数量 = 1170^（n-1） * 16; n为B+Tree的高度

Mysql中存储引擎的索引实现

接下来来着重介绍一下Mysql中MyISAM存储引擎和InnoDB存储引擎的索引实现。

1. MyISAM索引实现。
   MyISAM索引文件和数据文件是分离的(非聚集)：MyISAM索引文件和数据文件是分离的(非聚集)：
   
   其索引文件和数据文件是两个不同的文件，索引结构中叶子节点所以对应的数据是行数据的存储地址。所以称之为非聚集的。一下是以MyISAM搜索引擎建的表(myisam)的文件：

2020/01/02  14:26             8,614 myisamdemo.frm
2020/01/02  14:26                 0 myisamdemo.MYD
2020/01/02  14:26             1,024 myisamdemo.MYI

文件说明：

myisamdemo.frm：表定义文件。
miisamdemo.MYD：表数据文件。
myisamdemo.MYI：表索引文件。

可以看到表的定义语句、数据和索引均位于不同的文件中。其查找流程大致如下：
给定索引值 —> 通过索引文件查找行数据地址 —> 通过行数据地址从数据文件中查找行数据。

2. InnoDB索引实现。
   表数据文件本身就是按B+Tree组织的一个索引结构文件。聚集索引-叶节点包含了完整的数据记录。
   主键索引：



3. 非主键索引：



4. InnoDB的索引数据与表数据存在于同一文件中，并且主键索引的叶子节点中存储的就是表的数据，所有InnoDB搜索引擎的表数据就是以一个B+Tree结构存在文件中的，而InnoDB的非主键索引存储的则是对应行数据的主键。
   那么有如下两个问题：
   为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？

InnoDB的表数据就是一个以B+Tree结构存储的，即使没有指定主键，其也会为表指定一个默认主键为ROWID,推荐使用整型自增，首先来说整型，对于整型在比较时效率会更高，如果使用UUID这种字符串需要每个字符进行比较，效率低下，使用自增时在添加数据时减少B+Tree的结构调整(自旋等调整树的平衡)。

1. 为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)？

一致性：因为在主键索引中已经存储了数据，如果再在非主键索引中存储数据，那么在添加、修改和删除数据时，就需要两处或者 多处的数据要么全修改，要么全部修改(回滚)，就需要串行或者事务来保证数据的一致性，大大降低运行效率，如果非主键索引只保存数据的主键，那么在数据的修改是不需要对非主键索引的数据进行变更，可提升效率并且保证数据的一致性。
节省空间：很明显如果非主键索引的叶子节点也存储表中数据，那么会存在重复数据，浪费存储空间。

联合索引数据结构

对于联合索引其数据结构也是B+Tree，只是每个节点的索引值变成了多个字段的值，如下：

假设上图的联合索引的字段为(id,name,birthday)，那么其比较规则如下：

首先比较id，id小的证明这个索引值是较小的，如果id相同再比较name，name较小的则证明这个索引值是较小的，如果id，name均相同则比较birthday，birthday较小的证明这个索引值较小。这种比较方式就类似于java中String字符串的比较，对字符串中的每一个字符进行比较，只不过这里的“字符”是每一个字段，而且比较顺序又创建索引时指定的从左到右。