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

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树的性质

  1. 每个节点中的键值对,根据键的大小,从左至右非递减。
  2. 指针左边的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为辅助索引。

idnamegender
1cxymale
2lhwfemale
3tyyfemale
4dsxmale

2.1 InooDB引擎

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

  • 对于主键索引,其存储结构可能如下(仅做示意):
    在这里插入图片描述
  • 因此若执行select * from this_table where id = 3,就会执行
    在这里插入图片描述
  • 辅助索引结构示意图:顺序是ASCII码
    在这里插入图片描述
    ·  可见,辅助索引中存储的是主键索引,而不是真正的数据值。
  • 因此若执行select * from this_table where name = cxy,就会执行
    在这里插入图片描述
  • 注意
  1. InooDB引擎必须要有主键,因为整个引擎都是以主键构成的B+Tree结构而存在的。如果我们不指定主键,mysql会自动给我们创建一个隐藏的主键。
  2. InooDB的主键最好设置成:与业务无关且自增。 我们观察BTree的结构,一旦插入值,这个结构就要重建,很费时,如果主键是自增的,可以减少插入数据时BTree树的更新时间(降低数据写入的时间)。
  3. 辅助索引如果不手动创建,MySQL是不会帮我们创建的,如果没有辅助索引,我们通过name来找数据的时候,搜索复杂度是O(n),非常耗时。
  4. 辅助索引并不是越多越好。 我们观察整个搜索流程会发现,辅助索引通过牺牲空间的方式来提升查询速度,如果辅助索引过多,必然会造成空间的浪费。

2.2 MyISAM引擎

·  这个引擎我还没用过,因为其不支持事务,但是其查询速度非常快!因为,不管是主键索引还是辅助索引,其叶子节点都存放着数据的地址值,这表示,在MyISAM引擎中,主键并不是必须的,我们可以通过任何索引在一个logN时间内访问到数据。(但本质上,这还是一个通过空间换时间的算法)

  • 简要画一下MyISAM的索引结构示意图
    在这里插入图片描述
    ·  辅助索引的结构是一样的。

2.3 聚集索引和非聚集索引

·  这个概念我是这么理解的,如果数据和索引在整个结构中不分离,则为聚集索引,因此在InooDB中主键索引是聚集索引,而辅助索引是非聚集索引;而在MyISAM中,所有的索引都是非聚集的。

3. 总结

·  用了一晚上来写这个博客,还算是有所收获,也终于知道了MySQL数据库的存储结构,以及要在什么时候使用辅助索引。

  • 2
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值