Mysql底层索引原理（一）索引数据结构

最新推荐文章于 2024-10-14 00:50:09 发布

月下星望

最新推荐文章于 2024-10-14 00:50:09 发布

阅读量161

点赞数

分类专栏： sql 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/qq826303461/article/details/121030822

版权

sql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

介绍

首先，我们都知道索引能加快我们的查询数据速度，而快速索引的本质就是数据结构。通过不同的数据结构选择，实现各种数据的快速检索。

我们先看如果没有索引的话，我们是怎么查询数据的，如果我们要查询 id=5 的数据，只能通过遍历比较查询 5 次，最后得到 id=5 的数据。如果表内存储了1000W条数据，要查询 id=1000W 数据，就要遍历查询 1000W次。这是非常低效率的方法。

Mysql底层索引数据结构选型

一.Hash表(哈希表)

Hash(哈希)表是做数据检索的有效利器。

Hash(哈希)算法：散列算法，通过把任意值(key)经哈希函数变换为固定长度的 key 地址，通过这个地址进行具体数据的数据结构。

从算法复杂度方面来看，哈希算法的时间复杂度 O (1) ，检索速度十分的快，比如查找 id=1000W 的数据，只要查询一次就能获取到结果。但是，考虑到数据检索有个常用的手段就是有范围的条件查询。

例如：查询 id < 1000W。

针对上述的例子，使用哈希算法的话就无法高效范围查找数据了。

哈希算法实现的索引虽然可以做到快速检索数据，但是没办法做数据高效范围查找，因此哈希索引是不适合作为 Mysql 的底层索引的数据结构。

二.二叉查找树(BST)

二叉查找树是一种支持数据快速查找的数据结构。如下图

二叉查找树的时间复杂度是 O(lgn)，比如针对上面这个二叉树结构，我们需要计算比较 4 次就可以检索到 id=9 的数据，相对于直接遍历查询省了一半的时间，从检索效率上看来是能做到高速检索的。解决了哈希索引不能范围查找的问题。

但是普通的二叉查找树有个致命缺点：极端情况下会退化为线性链表，二分查找也会退化为遍历查找，时间复杂退化为 O（N），检索性能急剧下降。比如以下这个情况，二叉树已经极度不平衡了，已经退化为链表了，检索速度大大降低。

在数据库中，数据的自增是一个很常见的形式，比如一个表的主键是 id，而主键一般默认都是自增的，如果采取二叉树这种数据结构作为索引，那上面介绍到的不平衡状态导致的线性查找的问题必然出现。因此，简单的二叉查找树存在不平衡导致的检索性能降低的问题。

三.AVL 树和红黑树

二叉查找树存在不平衡问题，因此学者提出通过树节点的自动旋转和调整，让二叉树始终保持基本平衡的状态，就能保持二叉查找树的最佳查找性能了。基于这种思路的自调整平衡状态的二叉树有 AVL 树和红黑树。

首先简单介绍红黑树，这是一颗会自动调整树形态的树结构，比如当二叉树处于一个不平衡状态时，红黑树就会自动左旋右旋节点以及节点变色，调整树的形态，使其保持基本的平衡状态（时间复杂度为 O（logn）），也就保证了查找效率不会明显减低。比如从 1 到 7 升序插入数据节点，如果是普通的二叉查找树则会退化成链表，但是红黑树则会不断调整树的形态，使其保持基本平衡状态，如下图所示。下面这个红黑树下查找 id=7 的所要比较的节点数为 4，依然保持二叉树不错的查找效率。

红黑树拥有不错的平均查找效率，也不存在极端的 O(n)情况，那红黑树作为 Mysql 底层索引实现是否可以呢？其实红黑树也存在一些问题，观察下面这个例子。

红黑树顺序插入 1~7 个节点，查找 id=7 时需要计算的节点数为 4。

观察一下这个树的形态，是不是当数据是顺序插入时，树的形态一直处于“右倾”的趋势呢？从根本上上看，红黑树并没有完全解决二叉查找树虽然这个“右倾”趋势远没有二叉查找树退化为线性链表那么夸张，但是数据库中的基本主键自增操作，主键一般都是数百万数千万的，如果红黑树存在这种问题，对于查找性能而言也是巨大的消耗。

另一种更为严格的自平衡二叉树 AVL 树。因为 AVL 树是个绝对平衡的二叉树，因此他在调整二叉树的形态上消耗的性能会更多。

从查找效率而言，AVL 树查找的速度要高于红黑树的查找效率（AVL 树是 4 次比较，红黑树是 6 次比较）。从树的形态看来，AVL 树不存在红黑树的“右倾”问题。也就是说，大量的顺序插入不会导致查询性能的降低，这从根本上解决了红黑树的问题。

AVL树的优点总结：

1.不错的查找性能(O(long)), 不存在极端的低效查找的情况。

2.可以实现范围查找、数据排序

AVL树作为数据查询的数据结构确实不错，但是作为Mysql数据库的索引数据结构并不适合。

每次插入平衡调整二叉树十分消耗性能不说，要知道数据库查询数据的瓶颈在于磁盘 IO，如果使用的是 AVL 树，我们每一个树节点只存储了一个数据，我们一次磁盘 IO 只能取出来一个节点上的数据加载到内存里，那比如查询 id=7 这个数据我们就要进行磁盘 IO 三次，这是多么消耗时间的。

所以我们设计数据库索引时需要首先考虑怎么尽可能减少磁盘 IO 的次数。

磁盘 IO 有个有个特点，就是从磁盘读取 1B 数据和 1KB 数据所消耗的时间是基本一样的，我们就可以根据这个思路，我们可以在一个树节点上尽可能多地存储数据，一次磁盘 IO 就多加载点数据到内存，这就是 B 树，B+树的的设计原理了。

四.B树

下面这个B树，每个节点限制最多存储两个key，一个节点如果超过连个key就可以自动分裂。比如下面这个存储了7个数据 B数，只需要查询两个节点就可以知道 id=7 这个数据的具体位置，也就是两次磁盘IO就可以查询到指定数据，优于AVL树。

下面这个 B 树，每个节点限制最多存储两个 key，一个节点如果超过两个 key 就会自动分裂。比如下面这个存储了 7 个数据 B 树，只需要查询两个节点就可以知道 id=7 这数据的具体位置，也就是两次磁盘 IO 就可以查询到指定数据，优于 AVL 树。

例子：

下面这张是这是一个节点限制为 3 的 B树分裂的过程

所以数据库索引数据结构的选型而言，B 树是一个很不错的选择。总结来说，B 树用作数据库索引有以下优点

1.优秀的检索速度，时间复杂度： B书的查找性能等于 O(h * logn)，其中 h 为树高，n为每个节点关键字的个数；

2.尽可能少的磁盘 IO，加快了检索速度；

3.可以支持范围查找。

五.B+树

第一，B 树一个节点里存的是数据，而 B+树存储的是索引（地址），所以 B 树里一个节点存不了很多个数据，但是 B+树一个节点能存很多索引，B+树叶子节点存所有的数据。

第二，B+树的叶子节点是数据阶段用了一个链表串联起来，便于范围查找。

通过 B 树和 B+树的对比我们看出，B+树节点存储的是索引，在单个节点存储容量有限的情况下，单节点也能存储大量索引，使得整个 B+树高度降低，减少了磁盘 IO。其次，B+树的叶子节点是真正数据存储的地方，叶子节点用了链表连接起来，这个链表本身就是有序的，在数据范围查找时，更具备效率。因此 Mysql 的索引用的就是 B+树，B+树在查找效率、范围查找中都有着非常不错的性能。

Innodb 引擎和 Myisam 引擎的实现

Mysql 底层数据引擎以插件形式设计，最常见的是 Innodb 和 MyIsam。用户可以根据需求来选择不同的引擎作为 Mysql 数据表的底层引擎。

MyISAM 虽然数据查找性能极佳，但是不支持事务处理。Innodb 最大的特色就是支持了 ACID 兼容的事务功能，而且他支持行级锁。

Innodb 创建表后生成的文件有：

frm:创建表的语句
idb:表里面的数据+索引文件

Myisam 创建表后生成的文件有

frm:创建表的语句
MYD:表里面的数据文件
MYI:表里面的索引文件

从生成的文件看来，这两个引擎底层数据和索引的组织方式并不一样，MyISAM 引擎把数据和索引分开了，一人一个文件，这叫做非聚集索引方式；Innodb 引擎把数据和索引放在同一个文件里了，这叫做聚集索引方式。

1.MyISAM引擎的底层实现(非聚集索引方式)

MyISAM 用的是非聚集索引方式。即数据和所以落在不同的两个文件上。MyISAM 在建表时以主键作为 KEY 来建立主索引 B+树，树的叶子节点存的是对应数据的物理地址。我们拿到这个物理地址后，就可以到 MyISAM 数据文件中直接定位到具体的数据记录了。

当我们为某个字段添加索引时，我们同样会生成对应字段的索引树，该字段的索引树的叶子节点同样是记录了对应数据的物理地址，然后也是拿着这个物理地址去数据文件里定位到具体的数据记录。

2.Innodb引擎的底层实现(聚集索引方式)

InnoDB 是聚集索引方式，因此数据和索引都存储在同一个文件里。首先 InnoDB 会根据主键 ID 作为 KEY 建立索引 B+树，如左下图所示，而 B+树的叶子节点存储的是主键 ID 对应的数据，比如在执行 select * from user_info where id = 15 这个语句时，InnoDB 就会查询这颗主键 ID 索引 B+树，找到对应的 user_name='Bob'。

为什么 InnoDB 只在主键索引树的叶子节点存储了具体数据，但是其他索引树却不存具体数据呢，而要多此一举先找到主键，再在主键索引树找到对应的数据呢?

其实很简单，因为 InnoDB 需要节省存储空间。一个表里可能有很多个索引，InnoDB 都会给每个加了索引的字段生成索引树，如果每个字段的索引树都存储了具体数据，那么这个表的索引数据文件就变得非常巨大（数据极度冗余了）。从节约磁盘空间的角度来说，真的没有必要每个字段索引树都存具体数据，通过这种看似“多此一举”的步骤，在牺牲较少查询的性能下节省了巨大的磁盘空间

在进行 InnoDB 和 MyISAM 特点对比时谈到，MyISAM 查询性能更好，从上面索引文件数据文件的设计来看也可以看出原因：MyISAM 直接找到物理地址后就可以直接定位到数据记录，但是 InnoDB 查询到叶子节点后，还需要再查询一次主键索引树，才可以定位到具体数据。等于 MyISAM 一步就查到了数据，但是 InnoDB 要两步，那当然 MyISAM 查询性能更高。

最后总结一下什么时候需要给数据表里的字段加索引。