MySQL索引底层数据结构与算法

目录

一、MySQL数据结构几个概念

二、存储引擎

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

2. 为什么非主键索引结构叶子节点存储的是主键值？

3. 聚集索引与非聚集索引，哪一个查询更快？

        三、索引常用数据结构

3.1 二叉树

3.2 红黑树

3.3 Hash表

3.4 B-Tree

3.5 B+Tree

3.6 联合索引

---

MySql学习专栏

1. MySQL基础架构详解

2. MySQL索引底层数据结构与算法

3. MySQL5.7开启binlog日志，及数据恢复简单示例

4. MySQL日志模块

5. MySQL的MVCC及实现原理

6. MySQL索引优化

7. MySQL——通过EXPLAIN分析SQL的执行计划

8. MySQL执行语句性能优化

9. MySQL中的关联查询(内连接，外连接，自连接)

10. MySQL中复杂的增删改语句

11. 面试必问的 MySQL，你懂了吗？

 

 

 

 

一、MySQL数据结构几个概念

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

I/O：从磁盘读取数据的一次操作叫做一次I/O，整个查询过程最耗费性能的步骤（检验数据结构性能）。

数据页：它是InnoDB管理存储空间的基本单位，数据页是数据文件中的最小存储单元，每个数据页保存一个数据库节点。一个页的大小Mysql一般默认是16KB。

聚簇索引：对于我们的数据库存储结构中，如果叶子节点保存了完整的数据，那么这个就叫做聚簇索引（一次IO查询）。

非聚簇索引：对于我们的数据库存储结构中，如果叶子节点只保存了真实数据的内存地址，那么这个就叫做非聚簇索引（二次IO查询）。

 

二、存储引擎

存储引擎：MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。存储引擎是针对于表的。常用的有：

          MyISAM： 拥有较高的插入，查询速度，但不支持事务，一个数据库表将会产生三个文件，分别对应数据表结构（FRM）、数据库索引（MYI）、数据（MYD）。

 

 

MyISAM存储引擎采用的是非聚集索引，每次查找数据都会先从MYI文件中查找索引，之后根据索引叶子节点保存的磁盘文件地址再到MYD中对应磁盘地址找到对应数据，返回出来，这也是回表的概念，如下图：

 

 

         InnoDB ：5.5版本后Mysql的默认数据库，事务型数据库的首选引擎，支持ACID事务，支持行级锁定。一个数据库表将会产生两个文件，分别对应数据表结构（FRM）以及索引数据（IBD）

InnoDB索引实现(聚集)

表数据文件本身就是按 B+Tree 组织的一个索引结构文件

聚集索引-叶节点包含了完整的数据记录

InnoDB每次查找数据会从IBD文件中直接找到对应数据取出，如下图：

 

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

         为什么必须建主键呢？因为我们Mysql数据库在构建B+树的时候，需要有一列数据用来构建索引，这个值的要求是每个数据都不同。如果我们有主键，那就可以直接拿主键索引来构建这个B+树，非常方便。有的人可能会说，我如果不建立主键，也可以创建表呀？这种情况下，数据库会从第一列开始，找到一列每一行数据都不同的列，用来构建B+树，或者采用隐藏列（就像RowNum/RowId）来构建B+树。虽然我们不建主键也可以，但是这样会耗费MySQL的性能，MySQL的资源是很宝贵的，所以如果我们可以做的事情，就尽量我们来做，减小数据库压力。

        那为什么我们要使用整型来当做主键呢？因为整型比较起来非常的快，非常的方便。与uuid相比，每次比较值的时候都需要对每一个字符都进行比较，显然整型数据更方便，比较起来更快更好。而且对于生产环境，每一块磁盘空间都是异常宝贵的，所以与uuid相比，整型显然更小更省资源。

       那为什么又要用自增呢？我们来举个例子，当我们使用B+树插入12345687的时候，我们看下效果：

 

       可以看到，当我们插入最后一个7的时候，B+树首先将大节点拆分为2个节点，其次又将7 8两个节点进行顺序的互换，并且将节点7上浮到父亲节点当做指针指向叶子节点。与正常顺序相比，这里多做了顺序互换操作。因为索引的概念就是排好序的数据结构，本身索引就需要顺序，所以此处需要由索引来维护数据的正确顺序。那么这时候应该清晰了，也就是说，如果我们插入的数据不是按照自增顺序，B+树的维护成本将会上升，尤其当数据量大的时候，插入一条数据可能对树结构造成了非常大的影响，所耗费的性能和时间也是非常高的。

 

2. 为什么非主键索引结构叶子节点存储的是主键值？

一致性

当数据库表进行DML（对数据库中表记录进行修改）操作时，同一行记录的页地址会发生改变，因非主键索引保存的是主键的值，无需进行更改。而且无需进行两个索引数据的变更，只需要维护主键索引树的数据即可。

节省存储空间

因为所有数据只需要在主键索引树上维护一次即可，不需要在二级索引树上再进行维护一次节约存储空间。

3. 聚集索引与非聚集索引，哪一个查询更快？

      聚集索引，因为聚集索引不需要回表。

三、索引常用数据结构

3.1 二叉树

         二叉树（binary tree）是指树中节点的度不大于2的有序树，它是一种最简单且最重要的树。二叉树的递归定义为：二叉树是一棵空树，或者是一棵由一个根节点和两棵互不相交的，分别称作根的左子树和右子树组成的非空树；左子树和右子树又同样都是二叉树，左侧数据小于根节点，根节点小于右侧数据。

 

3.2 红黑树

                 红黑树是一种特化的AVL树（平衡二叉树），都是在进行插入和删除操作时通过特定操作保持二叉查找树的平衡，从而获得较高的查找性能。

                 在插入数据的时候，二叉树会依次比较已经存在的节点，然后插入到适合自己的位置，但是这样会导致树结构异常不平衡。

比如我们依次插入1,2,3,4,5,6这几个数据，那么出现的将是如下效果：

      

          从上方数据可以看出，二叉树插入数据不会保证数据的平衡性，如果此时我们需要查找数据6，那么我们将进行6次I/O，才能找到数据，可见这种数据结构在某些情况下并没有对我们的查询过程产生很大的帮助。

而我们的红黑树，如果存储相同的数据，整个过程如下：

      

            可以看出来，与二叉树相比，我们在插入数据的时候，红黑树有了一个平衡的过程，此时我们如果再去查找数据6，那么我们只需要进行3次I/O即可拿到6，与二叉树相比，性能高了很多。但是红黑树有一个很严重的问题，那就是树的高度问题，我们在数据量如果很大的时候，他的高度将会不可控，比如我们存储千万级别的数据，那么树的高度也将达到非常恐怖的程度，这种情况下，红黑树给我们的帮助也将会非常有限。

3.3 Hash表

           Hash数据结构指的是在存储数据的时候，对每一行数据的索引进行一次hash运算，产生一个hash值，然后存储在hash桶中，当做key，value就是数据的真实磁盘地址。

          比如下图，左侧是我们的表结构。我们存储了三个数据，分别是Alice、Jim以及Tom，分别对他们进行hash运算，加入我们得到的哈希值刚好是2,2,4，那么存储的结构将会如下。这种存储方式在查找单个数据的时候会非常高效，因为只需要进行一次Hash运算即可获取到数据的真实地址，只需要进行一次磁盘I/O即可拿到数据。但是Hash有一个非常严重的问题，那就是他只能满足“=”，“IN”，不能支持范围查询。比如下图，我们如果要查找，col3大于Alice的数据结构，那么hash表将无法支持这种查询，他会对整个表的磁盘地址进行遍历，找到所有满足条件的数据，再返回。

  

3.4 B-Tree

B树是二叉树的一种拓展，B-Tree也是一种自平衡的树（所有的叶子节点拥有相同的高度，叶子节点指针为空）类型的数据结构。但是和其它树比如红黑树只有两个孩子且左孩子和右孩子不同，B-Tree 的子节点多于或者等于2两个孩子，而且B树每个节点保存了完整的数据，并且节点中的数据索引从左到右依次递增排列，如下：

 

但是对于这种数据结构，假设我们每条数据大概1kb，Mysql每个数据页大小默认为16kb，我们每个节点只能存储16条数据，假设我们存储千万级别的数据，那么就需要16的X次方等于1000w，这个X就是树的高度，这个高度大概是5-6，那么我们有没有办法在千万级别的数据情况下，把树的高度限制在3或者4呢？

 

3.5 B+Tree

            B+ 树是一种树数据结构，是一个n叉树，每个节点通常有多个孩子，一颗B+树包含根节点、内部节点和叶子节点。

            与B+树相比，B+树（变种）只是叶子节点之间的指针由单向变为双向。Mysql就是采用B+树（变种）的数据结构。

特点：

非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引

叶子节点包含所有索引字段

叶子节点用指针连接，提高区间访问的性能

 

         假设我们使用BigInt类型来当做主键，每个主键8个字节，每个内存地址是6个字节，加起来一个数据14个字节，那么一个数据页16k，对于非叶子节点，将可以存储大概1170条数据，而叶子节点，假设每条数据是1kb，那么将可以存储16条数据。如果采用这种数据结构，存储千万级别的数据的话，只需要3行：1170 * 1170 * 16 =2190w。

        并且由于叶子节点之间有指针相连，此时如果我们查找大于20的数据，只需要经过两次内存比较，找到20节点，依次向后将所有数据取出即可。

       对于高版本Mysql，他可能将根节点甚至所有非叶子节点全部放到常驻内存中，再次减少了我们磁盘I/O，加快查找速度。

3.6 联合索引

会按照索引创建的先后顺序来维护数据结构。比如我们下图中的HanMeimei和Jeff两条数据，首先根据name来维护，再根据age来维护，再根据position来维护，因为name属性上HanMeimei小于Jeff，所以HanMeimei数据放到前面。

再比如三个Bill数据，age顺序由小到大。

再比如两个Lilei数据，age也一样，但是dev不一样，由小到大。

 

最左前缀原则：当使用联合索引的时候，无法跳过最左边的列而使用后面的列去使用索引

此处需要问大家一个问题，下面三条语句哪一个会走索引？

select * from employees where name = 'Bill' and age = 31;

select * from employees where age = 31 and position = 'dev';

select * from employees where position = 'dev';

 

根据最左前缀原则，只有第一条会走索引，因为联合索引无法跳过第一个索引列去查找数据，一定要依次排列条件，才能使用索引。

因为我们联合索引树构建的时候，就是根据列的顺序来构建的，如果查找的时候不按照顺序去查找，那么结果是无序的，也就无法使用这个索引树。

 

参考文档

https://www.pianshen.com/article/92211655669/

数据结构演示地址

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html