深入理解MySql索引底层数据结构与算法

深入理解MySql索引底层数据结构与算法

2021-05-16 10:59:06

一、索引本质

1、索引的本质

select * from t where col2 = 89 

我们插入到数据表中的数据，有可能分布存储在磁盘不同的空间中，而我们的数据库查询的时候每次都要进行磁盘IO。当我们不加索引的时候，此时查询就是全表扫描，即一行一行的比对数据，多次进行磁盘IO，直到查询到我们所需的数据。

而我们所能想到的优化，自然是减少磁盘IO。而减少磁盘IO，我们自然是要减少数据比对的次数，即我们要在尽可能少的比对次数中，查询出我们需要的数据。

所以，我们可以考虑，能不能使用一种数据结构来存储数据，使得我们的查询效率更高呢？这时候我们是不是就想到了树这种数据结构？

如果我们可以使用二叉树存储数据，可以用key存储数据，用value存储磁盘地址，这样查询某个数据时候性能就会大大提升。比如我们查找col2=89的数据，如果我们不适用数据结构存储，那么需要8次才能找到。而如果我们使用二叉树来存储，只需要两次就能查询到数据。

hash 索引

存储数据的时候会根据mysql自己实现的hash算法对索引值进行hash，然后将索引和内存地址进行存储。如果发生了hash碰撞，使用链表连接。

查询的时候先根据索引的hash定位位置，然后遍历链表找到具体的索引值，最后根据索引值存储的磁盘文件地址找到具体的数据。

在某些情况下，hash的效率要优于B+树，因为只需要进行一次磁盘IO就能查询到数据。而早期的B+树，只把根节点放到了内存中，可能需要多次磁盘IO才能定位到具体数据。

但是Hash存在的问题就是如果数据量较大的时候，较多的hash冲突导致链表查询数据较慢。而最主要的问题是，hash仅能满足等值运算，对于范围查找并不支持。

MySQL底层为什么不使用二叉树

如果我们需要查询：

select * from t where col1 = 6 

数据结构可视化网站：Data Structure Visualization

如果我们使用二叉树给表t中的col1加了索引，如下：

我们可以看到，当我们的数据是顺序增加的时候，二叉树编程了一个类似链表的树。此时当我们要查询6的时候，仍然需要查询6次，这和全表扫描查询有什么区别吗？

这就是mysql不适用二叉树的原因，对于单边增长的数据，适用二叉树存储导致二叉树过高，形成一个链表，对我们的查询几乎没有帮助，反而mysql需要维护这个索引。

MySQL底层为什么不使用红黑树（二叉平衡树）

什么是红黑树
（1）每个节点或者是黑色，或者是红色。
（2）根节点是黑色。
（3）每个叶子节点（NIL）是黑色。 [注意：这里叶子节点，是指为空(NIL或NULL)的叶子节点！]
（4）如果一个节点是红色的，则它的子节点必须是黑色的。
（5）从一个节点到该节点的子孙节点的所有路径上包含相同数目的黑节点。

我们插入1,2

我们来观察一下，当我们继续添加这种单边增长数据，添加3：

我们发现它不像二叉树一样严格的按照左子树小于根节点、右子数大于根节点的规律进行存储，而是会自动进行平衡。
插入的时候，如果一个红色节点上有了两个黑色节点，就会将该红节点转化成黑节点，同时将该节点两侧的黑节点转化成红节点，同时将上一个黑节点指向该次生成的黑色节点。



即红黑树的特性就是，当树的一边比另一边更高的时候，就会自动进行一次平衡。

我们可以看到，红黑树相对于二叉树确实有所提高，整个树的高度小于二叉树，不会在单边增长的情况下退化为链表。但是当我们数据比较多的时候，红黑树依然会出现“树太高”的问题。

我们需要理解到，树太深的根本原因就是每个根节点只储存了两个子树，这就导致数据量大的时候，树的深度必然太大。所以我们要考虑是否可以增加同一个节点上存储的数据的个数，使得每一个节点可以有更多的子节点，从而存储更多的数据，以此来减少树高度的快速增大。

MySQL底层为什么不使用B Tree

B树的每个节点都存储了索引和对应的数据。
B树确实极大的降低了树的高度，但是由于每个节点都存储了数据，导致每一次磁盘IO时候所能加载的数据比较少，这无疑是增加了磁盘IO的次数，严重影响性能。而且，B树叶节点之间没有指针连接，这样范围查询的时候就需要不断的从根节点向下遍历，直至匹配到所有条件。

B+Tree

mysql底层对B+树做了一些修改，B+树的叶子节点之间是单向指针，而mysql中的B+树叶子节点之间是双向指针。
B+树中有一个max-degree属性，即表示B+数的节点不能超过该数。

当我们插入3的时候：

B树和B树的不同点

1、B+树非叶子节点存储的是冗余索引，存储的是每一页的第一个索引，用来构建整个B+树，只有叶子节点存储了索引和数据。而B数，每个节点都存储了索引和数据。
2、B+数每连个叶子节点之间都有一个双向指针，将所有的叶子节点连了起来。指针的作用是：不同的叶子节点存储了其左右相邻的叶子节点的内存地址，这样可以非常快速的去访问相邻的叶子节点。 而B数中，叶子节点之前是没有整个指针的。

B数是如何查询数据的

首先会将B+树的根节点加载到内存中，然后用需要查询的索引值去一一比对。假如我们想查询30的记录，这时候mysql会根据二分查找算法，首先计算出30大概在左边还是右边，此时会确定出，30在15和56之间的节点上。而这个节点上存储了15到56之间的页数据的内存地址，所以可以很快的访问到15到56之间的子节点数据。然后继续采用二分查找，确定出30正在30~40之间的节点上，取到内存地址后访问，得到20和30的叶子节点数据。然后再进行一次查询，就可以找到30，然后取出30所在节点的内存地址，就可以访问到要查询的数据了。

B+树叶子节点之间为何使用双向指针互相连接

在进行范围查询的时候，我们知道Hash索引并不支持范围查询，仅支持等只查询。

而B+数中叶子节点间本来就是依次递增排好序的，所以我们范围查询的时候就可以根据第一个叶子节点以及指针，快速的定位到其他的叶子节点，直到完成范围查询。这样范围查询的效率是非常高的。如果是增量的范围查询20~50，我们只需要定位两个节点20和50即可，因为叶子节点就是按照增序排列的。

而B树中叶子节点之间并没有指针，这样就需要不断的从根节点向叶子节点查询，直到查询到复合条件的数据。这样的效率是比较低的。

MySql一页的大小是16k

Page是Innodb存储的最基本结构，也是Innodb磁盘管理的最小单位，与数据库相关的所有内容都存储在Page结构里。Page分为几种类型：数据页（B-Tree Node），Undo页（Undo Log Page），系统页（System Page），事务数据页（Transaction System Page）等；每个数据页的大小为16kb，每个Page使用一个32位（一位表示的就是0或1）的int值来表示，正好对应Innodb最大64TB的存储容量(16kb * 2^32=64tib)

mysql中InnoDB引擎中页的概念

SHOW GLOBAL STATUS LIKE 'Innodb_page_size'; 

这个值可以修改，但是不建议修改。这里的16Kb是经过计算得到的较优的数据大小。

比如我们使用bigint(mysql中占8B)存储自增主键，每个内存地址大概占用6个B。那么我们来算算，一页可以加载多少个索引：

16 * 1024 / (8 + 6) = 1170

我们知道，叶子节点还存储了数据，假设是每行记录1KB，那么一页就是18行记录。那么整个B+树存满的时候，共有多少个叶子节点呢？
1170 * 1170 * 16 = 21902400

所以说，如果使用B+树存储两千万行数据，树的高度也只有3. 整个查询是相当快的。而且，对于根节点，mysql是将放到内存的，是常驻内存的。对于较高版本的mysql，会将所有的非叶子节点都放入到内存中！

mysql每次和磁盘进行IO，都会load大概16k的数据。其实在查询的过程中，比对数据消耗的实现是非常短暂的，而每次加载一页的数据，这个磁盘IO过程是比较耗时的。所以说我们要尽可能减少磁盘IO的时间。 这里优化的思路就是每一次磁盘IO的时候，都尽可能多的加载出数据，这就是为什么Mysql不使用B树的一个原因，因为B树每个节点都存储了索引和数据，这就使得每一页只能加载较少的数据记录。所以B+数的非叶子节点只存了索引，这就使得每一页上能查询出更多的数据记录，相当于是减少了磁盘IO。

注意，其实B+树的树高度和和数据的大小是有关的，当数据超过两千万行，甚至更多的时候，我们可以增加树的高度，来存储更多的数据。

二、索引存储

索引存储的位置

mysql的数据默认都存储在mysql服务的data目录下，我们可以看到每个数据库都是一个文件夹：

我们进入到某个文件夹下，可以看到数据对应的是该数据库中的数据表。一张表对应两个文件

注意：Innodb或者MyISAM存储引擎是数据表级别的，并不是数据库级别的。我们可以针对一张表设置存储引擎。

MyISAM索引文件和数据文件存储

我们来看看My_SIAM存储的文件记录：

1. *.frm:存储和表结构相关的数据
2. *.MYD：MY就是MySIAM缩写，D是data，存储的是数据表数据；
3. *.MYI:存储索引。

MyISAM数据查询

如果col1是索引，要查询col1=30的数据：

1. 首先从根节点开始查询
2. 定位到20~49之间
3. 找到30以及30中存储的内存地址
4. 去磁盘上的*.MYD文件中，根据刚才取出的内存地址获取数据

Innodb索引文件和数据文件存储

我们来看看Innodb引擎M存储的文件记录

Innodb表对应的文件只有两个：

1. *.frm：存储和表结构相关的数据
2. *.ibd：内存采用一个B+树来维护索引和表记录

MyISAM和Innodb引擎存储数据的区别

B树：

B+树：

• MyISAM使用三个文件来存储，将表记录单独存储在*.MYD文件中
• Innodb使用两个文件存储，索引和表记录被维护在一个B+树中，叶子节点存储索引和整个表记录。索引和表记录存储在*.ibd文件中
• MyISAM存储引擎中的主键索引是一个非聚集索引，叶子节点中只包含了索引，不包含数据，存储的是内存地址。数据存储在另一个文件*.MYD中。

聚集索引（聚簇索引）

聚集索引： 就是索引和数据存储在一起的索引
非聚集索引： 就是索引和数据分开存储

Innodb存储引擎的主键索引就是一个聚集索引。叶节点包含了完整的数据记录的索引。

MyISAM存储引擎中的主键索引是一个非聚集索引，叶子节点中只包含了索引，不包含数据，存储的是内存地址。数据存储在另一个文件中。

从存储结构上来讲，聚集索引查询更快。因为非聚集索引需要回表操作。

为什么InnoDB表必须建立主键？并且推荐使用整型的自增主键？

1、InnoDB表对应的ibd文件是一个B+树，如果表中有主键索引的话，就会使用这个主键索引来构建B+树。如果我们不建立主键索引，mysql会从该表的所有列中选择数据不重复的一列来组织B+数。如果表中没有符合条件的列，mysql会自己创建一个隐藏列，类似row_id，用这一列构建B+树。

2、为什么我们使用整型的数据作为自增索引呢？因为整型的数据更好比较大小，计较速度比较快。而如果使用UUID，就需要逐位去比较大小。而且整型数据占用的内存空间更小。

为什么推荐使用自增主键

我们创建的索引，在B+树中是按照顺序排列的。所以即使我们创建的时候不是按照顺序插入的，mysql也会根据大小进行排序。即，mysql需要维护叶子节点中的数据顺序。

而当我们不是顺序插入的时候，如主键插入是8,6。这样，mysql就需要修改整个叶子节点的顺序，移动8整个节点，将6节点插入到8节点之前。这个修改过程是比较耗费时间的，所以说我们最好使用自增主键，这样mysql就不需要频繁的移动和修改叶子节点的顺序。

我们在5后面插入8：

然后再8后面插入6：

可以看到，整个B+树的结构发生了平衡。这个平衡是会消耗时间的。如果是递增，就会创建新的节点，而不是插入到已有的节点再进行分裂和平衡。

为什么非主键索引（二级索引）结构叶子节点存储的是主键值

MyISAM的主键和非主键索引存储都是一样的。

而InnoDB的非主键索引和主键索引是不同的，非主键索引的叶子节点存储的是聚集索引的值。InnoDB表中只有一个聚集索引。当使用二级索引查询数据的时候，如果查询的字段包括在索引中，直接返回数据。否则就会根据叶子节点中存储的聚集索引的值，回到聚集索引中查询，然后在聚集索引的叶子节点查询出完整数据（回表操作）。

为什么二级索引不做成聚集索引呢？
1、解决存储空间
2、一致性：如果同一张表有多个聚集索引，必然要维护数据的一致性，复杂度较高。

三、联合索引（复合索引：a, b, c）

现在有一个联合索引（name, age, position），那么我们怎样维护这个联合索引呢？

索引最左前缀原理

现在有联合索引（name, age, position）

会按照索引字段的顺序排序，先比较name，再比较age，再比较position.

下面的三条SQL语句，哪几条会走索引？

只有第一条SQL语句才会走索引。根据左前缀匹配原理，如果我们想使用联合索引，一定要按照联合索引中字段的创建顺序去过滤。那么为什么呢？

最左前缀原理

我们知道，建立了索引之后，叶子节点是按照顺序排列的。如果是联合索引，是根据联合字段进行综合排序的。

如果我们不根据联合索引字段去查询，即跳过左边的某个字段去查询，此时剩下的右边的字段并不是按照顺序排序的。因为前提是如果左边的索引字段相同的情况下，右边的索引字段才会按照顺序排列。 所以说，如果不按照最左匹配原则来查询，联合索引就会失效，因为叶子节点是按照联合字段综合排序的，即此时的排序不适用于不满足最左匹配原则的查询。