MySQL的数据结构以及辅助索引的使用

最新推荐文章于 2024-04-22 13:55:58 发布

MemoForward

最新推荐文章于 2024-04-22 13:55:58 发布

阅读量1.7k

点赞数 2

分类专栏：数据库 Java后台文章标签： mysql Innodb B-Tree

本文链接：https://blog.csdn.net/qq_34294121/article/details/101632347

版权

Java后台同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

数据库

1 篇文章 0 订阅

订阅专栏

文章目录

0. 前言
1. BTree 和 B+Tree
2. MySQL中的数据结构（索引实现）
3. 总结

0. 前言

·　　之前学习MySQL都是一直半解，以为建立主键索引只是为了约束数据。直到自己学着做一些开源项目的时候，才发现有辅助索引这个神奇的设定，对应的SQL语句：KEY aux_index(col_name)，上网一搜才发现这是为了加快MySQL查询速度。于是我就对MySQL的数据存储结构产生了兴趣，在这里做一个小小的总结。
查看了官方的文档和许多博客，觉得这篇博客写的是真的好，分享给大家：MySQL索引背后的数据结构及算法原理

1. BTree 和 B+Tree

·　　B+Tree是BTree的扩展，而常用的MySQL引擎MyISAM和InnoDB都是用B+Tree作为数据结构存储的（在参考博客里有使用BTree的原因，涉及计算机磁盘的读写，本文不讨论这部分内容），因此在这里介绍一下这两个数据结构。

1.1 索引的价值

·　　索引顾名思义就是目录，我们需要的内容可能是庞大的，但是索引都是很简洁的，所以索引的存储相比内容而言更具灵活性，也更容易被应用于复杂的数据结构。我们把庞大的内容放在内存里，通过索引去找到这些内容，不仅可以利用索引的灵活性构成高效的数据结构来提高查找效率，也可以一定程度下减少查找时所用的I/O读写时间。

1.2 B-Tree

·　　BTree有一个重要的概念：度。
·　　这个概念似乎和正常的树不太一样，一般说来树节点的“度”表示此节点下拥有的子树的个数，是针对一个节点而言的，而BTree的度，表示这颗树的度。
·　　在介绍这个概念之前，我们要搞清楚BTree的节点中存的是什么。BTree的每个节点都储存了至少一个键值对，在每个键值对的前后都有一个指针。 具体的图如下：
在这里插入图片描述
·　　上图中根节点储存有：三个键值对，四个指针。 每一个指针都会指向下一个节点，或者指向null。
·　　如果规定：一个节点中拥有n个指针和n-1个键值对，那么BTree的度d则约束了：d ≤ n ≤ 2d。 上图就是一个d = 2的BTree结构。
·　　根据上图我们显然可以猜到BTree树的性质

每个节点中的键值对，根据键的大小，从左至右非递减。
指针左边的key都小于等于指针指向的子节点的所有key，右边的key都大于等于。

·　　查找的伪代码如下：

BTree_Search(node, m_key){
	if(node == null) return null;
	foreach(k : node.key){
		if(m_key == k) return node.get(k);
		if(m_key < k) return BTree_Search(node.leftPointer(k), m_key);
	}
	return BTree_Search(node.rightPointer(last_key), m_key);
}

BTree_Search(root, m_key);

·　　这里有一个有趣的小细节：BTree在最简单的情况下就是一个二叉树，因此BTree树的高最高是logN。

1.2 B+Tree

·　　通过上面的BTree我们注意到，每个节点都会存数据，为了加快读写的速度，引入了B+Tree，舍去了非叶子节点的数据存储，仅在叶子结点中存储所有的value；同时，其对指针的数量进行了一些调整。概念图如下：
在这里插入图片描述
·　　可见，在B+Tree中，每个节点中指针的数量和键的数量相同，并满足：d ≤ n < 2d。而且叶子节点不存储指针。
·　　查找的伪代码如下：

BTree_Search(node, m_key){
	if(node.isLeaf()){
		foreach(k : node.key){
			if(m_key == k) return node.get(k);
		}
		return null;
	}
	foreach(k : node.key){
		if(m_key == k) return BTree_Search(node.rightPointer(k), m_key);
		if(m_key < k) return node.havaLeftPointer() ? BTree_Search(node.leftPointer(k), m_key) : null;
	}
	return BTree_Search(node.rightPointer(last_key), m_key);
}

2. MySQL中的数据结构（索引实现）

·　　空谈结构没有数据就是耍流氓，因此这里提供一个数据库表。我们假设：id为主键索引，name为辅助索引。

id	name	gender
1	cxy	male
2	lhw	female
3	tyy	female
4	dsx	male

2.1 InooDB引擎

·　　鄙人不才，只用过这一个引擎，主要是因为InooDB支持事务和行级锁。InooDB索引结构是B+Tree，其索引就是一个个key。但是主键索引和辅助索引在叶子节点的存储结构上有一点不同。

对于主键索引，其存储结构可能如下（仅做示意）：
因此若执行select * from this_table where id = 3，就会执行：
辅助索引结构示意图：顺序是ASCII码

·　　可见，辅助索引中存储的是主键索引，而不是真正的数据值。
因此若执行select * from this_table where name = cxy，就会执行
注意

InooDB引擎必须要有主键，因为整个引擎都是以主键构成的B+Tree结构而存在的。如果我们不指定主键，mysql会自动给我们创建一个隐藏的主键。
InooDB的主键最好设置成：与业务无关且自增。 我们观察BTree的结构，一旦插入值，这个结构就要重建，很费时，如果主键是自增的，可以减少插入数据时BTree树的更新时间(降低数据写入的时间)。
辅助索引如果不手动创建，MySQL是不会帮我们创建的，如果没有辅助索引，我们通过name来找数据的时候，搜索复杂度是O(n)，非常耗时。
辅助索引并不是越多越好。 我们观察整个搜索流程会发现，辅助索引通过牺牲空间的方式来提升查询速度，如果辅助索引过多，必然会造成空间的浪费。